240 lines
15 KiB
SQL
240 lines
15 KiB
SQL
-- ============================================================
|
||
-- محرك الالتزامات العام — بند 3.4 من دراسة الفرص (اقتصاد السائق)
|
||
--
|
||
-- سبب وجود هذا الملف ليس التوسّع المستقبلي، بل ثغرة قائمة في الإنتاج:
|
||
-- `cron_insurance_premiums.php` يقيّد الأقساط في `insurance_premium_ledger`
|
||
-- منذ إطلاقه، وتعليقه يقول «تقرأه التسوية» — والتسوية غير موجودة. لا
|
||
-- مرجع واحد لذلك الجدول في المشروع كله خارج الكرون والـmigration. أي أن
|
||
-- كل قسط تأمين قُيّد حتى اليوم ما زال `pending` ولم يُحصَّل قرشٌ منه.
|
||
--
|
||
-- فالخيار كان: كتابة تسوية خاصة بالتأمين، أو تعميم النموذج مرة واحدة.
|
||
-- والوقود والصيانة والتمويل — البنود الثلاثة التالية في اقتصاد السائق —
|
||
-- كلها نفس الشكل: التزام دوري أو مقسّط يُخصم من أرباح السائق. كتابة
|
||
-- تسوية لكل واحد منها تعني أربع نسخ من أخطر منطق في المنصة: المنطق
|
||
-- الذي يلمس مال السائق.
|
||
--
|
||
-- الفصل المعتمد:
|
||
-- • مُصدِر استحقاق لكل منتج → يقيّد «على السائق كذا»
|
||
-- • دفتر موحّد → سجل دائم واحد مهما كان المصدر
|
||
-- • محرك تسوية واحد → الجهة الوحيدة التي تلمس الرصيد
|
||
--
|
||
-- جداول التأمين تبقى كما هي — لا تُحذف ولا تُعدَّل. تُنقل بياناتها هنا
|
||
-- في نهاية هذا الملف، وتبقى الأصلية شاهداً تاريخياً.
|
||
-- ============================================================
|
||
|
||
-- ── ١) المنتجات ─────────────────────────────────────────────
|
||
-- يعمّم `insurance_plans`. الفرق الجوهري عن الخطة: `kind` يحدّد سلوك
|
||
-- الاستحقاق نفسه — الدوري يتكرّر بلا نهاية (تأمين)، والمقسّط له أصل
|
||
-- محدود ينتهي بسداده (صيانة، تمويل)، والمسحوب يُقيَّد عند السحب لا
|
||
-- على جدول (وقود).
|
||
CREATE TABLE IF NOT EXISTS `obligation_products` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`code` VARCHAR(60) NOT NULL COMMENT 'معرّف ثابت يُستعمل في الكود',
|
||
`kind` ENUM('recurring','installment','drawdown') NOT NULL,
|
||
`name_ar` VARCHAR(160) NOT NULL,
|
||
`description_ar` TEXT DEFAULT NULL,
|
||
|
||
-- الشريك الخارجي: شركة تأمين، سلسلة محطات، ورشة، أو بنك. عمود نصّي
|
||
-- لا جدول: لا نعرف بعد ما إذا كانت لهذه الجهات دورة حياة تستحق
|
||
-- جدولاً، وجدول فارغ الغرض أسوأ من عمود.
|
||
`partner_name` VARCHAR(160) DEFAULT NULL,
|
||
|
||
`billing_cycle` ENUM('daily','monthly','none') NOT NULL DEFAULT 'daily'
|
||
COMMENT 'none للمنتجات المسحوبة — لا دورة لها',
|
||
`currency` VARCHAR(10) NOT NULL DEFAULT 'JOD',
|
||
|
||
-- ── شروط الأهلية ──
|
||
-- تُخزَّن مع المنتج لا في الكود: الشريك سيغيّرها، والسوق المصري يختلف
|
||
-- عن الأردني، وتغيير رقم في صف أرخص من نشر إصدار. منقولة كما هي من
|
||
-- `insurance_plans` لأن المنطق ذاته ينطبق على الوقود والصيانة: كلاهما
|
||
-- ائتمان يُمنح لسائق قد يختفي.
|
||
`min_completed_rides` INT NOT NULL DEFAULT 200,
|
||
`min_rating` DECIMAL(3,2) NOT NULL DEFAULT 4.50,
|
||
`min_account_days` INT NOT NULL DEFAULT 30,
|
||
|
||
-- سقف الخصم اليومي كنسبة من أرباح اليوم. على مستوى المنتج لا النظام:
|
||
-- قسط التأمين الصغير يحتمل نسبة أعلى من قرض صيانة كبير، والسقف الموحّد
|
||
-- يعني إمّا خنق السائق أو إبطاء التحصيل.
|
||
`daily_cap_percent` DECIMAL(5,2) NOT NULL DEFAULT 25.00,
|
||
|
||
-- ترتيب المزاحمة حين تستحق التزامات عدة في يوم واحد. الأصغر أولاً:
|
||
-- التأمين (١٠) قبل الوقود (٢٠) قبل الصيانة (٣٠) — انقطاع التأمين
|
||
-- يُلغي وثيقةً ويفقد السائق تغطيته، بينما تأخّر قسط صيانة يوماً لا
|
||
-- يكلّف أحداً شيئاً.
|
||
`priority` SMALLINT NOT NULL DEFAULT 50,
|
||
|
||
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
|
||
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uq_code` (`code`),
|
||
KEY `idx_kind_active` (`kind`, `is_active`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
|
||
-- ── ٢) التزامات السائقين ────────────────────────────────────
|
||
-- يعمّم `driver_insurance_policies`: الاشتراك/العقد النشط بين سائق ومنتج.
|
||
CREATE TABLE IF NOT EXISTS `driver_obligations` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`driver_id` VARCHAR(100) NOT NULL,
|
||
`product_id` INT NOT NULL,
|
||
`external_ref` VARCHAR(120) DEFAULT NULL COMMENT 'رقم الوثيقة/العقد لدى الشريك',
|
||
`status` ENUM('active','suspended','completed','cancelled')
|
||
NOT NULL DEFAULT 'active',
|
||
`started_at` DATE NOT NULL,
|
||
`ended_at` DATE DEFAULT NULL,
|
||
|
||
-- مبلغ الدورة الواحدة. منسوخ من المنتج لحظة الاشتراك لا مقروءاً منه:
|
||
-- رفع سعر الخطة غداً يجب ألّا يغيّر قسط من اشترك أمس بأثر رجعي.
|
||
`cycle_amount` DECIMAL(12,3) NOT NULL DEFAULT 0,
|
||
|
||
-- للمقسّط فقط: الأصل وما سُدِّد منه. المنتج الدوري يتركهما صفراً —
|
||
-- لا نهاية له فلا معنى لأصلٍ ينفد.
|
||
`principal_amount` DECIMAL(12,3) NOT NULL DEFAULT 0,
|
||
`principal_paid` DECIMAL(12,3) NOT NULL DEFAULT 0,
|
||
|
||
-- لقطة الأهلية لحظة الاشتراك. بدونها لا يمكن الإجابة لاحقاً على
|
||
-- «لماذا مُنح هذا السائق ائتماناً؟» حين ينخفض تقييمه أو تتغيّر الشروط.
|
||
`rides_at_signup` INT NOT NULL DEFAULT 0,
|
||
`rating_at_signup` DECIMAL(3,2) NOT NULL DEFAULT 0,
|
||
|
||
-- آخر يوم قُيّد عنه استحقاق. هذا العمود — لا التاريخ الحالي — هو
|
||
-- الفلتر الرخيص ضد الاحتساب المزدوج. الحارس الحقيقي هو المفتاح
|
||
-- الفريد في الدفتر أدناه.
|
||
`last_charged_on` DATE DEFAULT NULL,
|
||
`cancel_reason` VARCHAR(255) DEFAULT NULL,
|
||
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_driver_status` (`driver_id`, `status`),
|
||
KEY `idx_charge_sweep` (`status`, `last_charged_on`),
|
||
CONSTRAINT `fk_obligation_product` FOREIGN KEY (`product_id`)
|
||
REFERENCES `obligation_products` (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
|
||
-- ── ٣) الدفتر الموحّد ───────────────────────────────────────
|
||
-- يعمّم `insurance_premium_ledger` بفارق واحد جوهري: `amount_collected`
|
||
-- و`amount_remaining`. القسط يُدفع كاملاً أو لا يُدفع، أما الوقود
|
||
-- والصيانة فتحصيلهما جزئي بطبعه — سقف الخصم اليومي يعني أن قيداً بقيمة
|
||
-- عشرة قد يُحصَّل على ثلاثة أيام. بلا هذين العمودين لا يمكن تمثيل ذلك
|
||
-- إلا بتفتيت القيد، فيضيع أثر الاستحقاق الأصلي.
|
||
CREATE TABLE IF NOT EXISTS `obligation_ledger` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`obligation_id` INT NOT NULL,
|
||
`driver_id` VARCHAR(100) NOT NULL,
|
||
`product_code` VARCHAR(60) NOT NULL COMMENT 'منسوخ للاستعلام بلا JOIN',
|
||
`charge_date` DATE NOT NULL COMMENT 'اليوم أو أول الشهر المحتسَب',
|
||
|
||
`amount` DECIMAL(12,3) NOT NULL COMMENT 'أصل الاستحقاق',
|
||
`amount_collected` DECIMAL(12,3) NOT NULL DEFAULT 0,
|
||
`amount_remaining` DECIMAL(12,3) NOT NULL COMMENT 'amount - amount_collected',
|
||
`currency` VARCHAR(10) NOT NULL DEFAULT 'JOD',
|
||
|
||
-- partial ليست حالة عابرة بل مستقرّة: قيد حُصِّل بعضه ينتظر يوماً
|
||
-- أفضل. waived للإعفاء الإداري — يُغلق القيد بلا مال، ويبقى أثره.
|
||
`status` ENUM('pending','partial','settled','waived','failed')
|
||
NOT NULL DEFAULT 'pending',
|
||
|
||
`attempts` SMALLINT NOT NULL DEFAULT 0,
|
||
`last_attempt_on` DATE DEFAULT NULL,
|
||
`settled_at` DATETIME DEFAULT NULL,
|
||
`note` VARCHAR(255) DEFAULT NULL,
|
||
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
|
||
-- الحارس الحقيقي ضد الاحتساب المزدوج: استحقاق واحد لكل التزام في
|
||
-- اليوم الواحد، مهما تكرّر تشغيل الكرون أو تزامنت نسختان منه.
|
||
UNIQUE KEY `uq_obligation_date` (`obligation_id`, `charge_date`),
|
||
|
||
-- فهرس مسح التسوية: تمرّ على المعلّق والجزئي مرتّباً بأولوية المنتج.
|
||
KEY `idx_settlement_sweep` (`status`, `driver_id`),
|
||
KEY `idx_driver_date` (`driver_id`, `charge_date`),
|
||
CONSTRAINT `fk_ledger_obligation` FOREIGN KEY (`obligation_id`)
|
||
REFERENCES `driver_obligations` (`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
|
||
-- ============================================================
|
||
-- ترحيل التأمين إلى النموذج العام
|
||
--
|
||
-- يعمل هذا القسم أكثر من مرة بلا ضرر: كل إدراج مشروط بعدم وجود ما
|
||
-- يقابله. سبب الحرص أن الترحيل يلمس التزامات مالية قائمة، وتشغيلاً
|
||
-- ثانياً بلا حماية يعني ازدواج كل وثيقة وكل قسط.
|
||
-- ============================================================
|
||
|
||
-- ── خطط التأمين تصير منتجات ──
|
||
-- `code` يُنسخ كما هو ليبقى المعرّف الثابت واحداً بين النموذجين.
|
||
-- الأولوية ١٠: التأمين أوّل من يُحصَّل عند المزاحمة.
|
||
INSERT INTO `obligation_products`
|
||
(`code`, `kind`, `name_ar`, `description_ar`, `partner_name`, `billing_cycle`,
|
||
`currency`, `min_completed_rides`, `min_rating`, `min_account_days`,
|
||
`daily_cap_percent`, `priority`, `is_active`)
|
||
SELECT
|
||
pl.`code`, 'recurring', pl.`name_ar`, pl.`description_ar`,
|
||
pr.`name`, pl.`billing_cycle`, pl.`currency`,
|
||
pl.`min_completed_rides`, pl.`min_rating`, pl.`min_account_days`,
|
||
25.00, 10, pl.`is_active`
|
||
FROM `insurance_plans` pl
|
||
JOIN `insurance_providers` pr ON pr.`id` = pl.`provider_id`
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM `obligation_products` op WHERE op.`code` = pl.`code`
|
||
);
|
||
|
||
-- ── الوثائق تصير التزامات ──
|
||
-- `cycle_amount` يُنسخ من قسط الخطة لحظة الترحيل — نفس مبدأ تثبيت السعر
|
||
-- الذي يحكم الاشتراكات الجديدة.
|
||
INSERT INTO `driver_obligations`
|
||
(`driver_id`, `product_id`, `external_ref`, `status`, `started_at`, `ended_at`,
|
||
`cycle_amount`, `rides_at_signup`, `rating_at_signup`, `last_charged_on`,
|
||
`cancel_reason`, `created_at`)
|
||
SELECT
|
||
p.`driver_id`, op.`id`, p.`policy_number`,
|
||
-- 'expired' في التأمين تقابل 'completed' هنا: النموذج العام لا يعرف
|
||
-- انتهاء صلاحية، يعرف التزاماً بلغ نهايته.
|
||
CASE p.`status` WHEN 'expired' THEN 'completed' ELSE p.`status` END,
|
||
p.`started_at`, p.`ended_at`,
|
||
pl.`premium`, p.`rides_at_signup`, p.`rating_at_signup`, p.`last_charged_on`,
|
||
p.`cancel_reason`, p.`created_at`
|
||
FROM `driver_insurance_policies` p
|
||
JOIN `insurance_plans` pl ON pl.`id` = p.`plan_id`
|
||
JOIN `obligation_products` op ON op.`code` = pl.`code`
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM `driver_obligations` o
|
||
WHERE o.`driver_id` = p.`driver_id`
|
||
AND o.`product_id` = op.`id`
|
||
AND o.`started_at` = p.`started_at`
|
||
);
|
||
|
||
-- ── الأقساط المعلّقة تصير قيوداً ──
|
||
-- هذه هي الغاية العملية من الترحيل كله: هذه الصفوف — أقساط حقيقية
|
||
-- تراكمت في الإنتاج بلا تحصيل — تصير مرئية لمحرك التسوية.
|
||
--
|
||
-- `settled` القديمة تُنقل أيضاً رغم أنها لن تُحصَّل: دفتر ناقص التاريخ
|
||
-- لا يُسوّى مع شريك.
|
||
INSERT INTO `obligation_ledger`
|
||
(`obligation_id`, `driver_id`, `product_code`, `charge_date`,
|
||
`amount`, `amount_collected`, `amount_remaining`, `currency`,
|
||
`status`, `settled_at`, `note`, `created_at`)
|
||
SELECT
|
||
o.`id`, l.`driver_id`, op.`code`, l.`charge_date`,
|
||
l.`amount`,
|
||
CASE WHEN l.`status` = 'settled' THEN l.`amount` ELSE 0 END,
|
||
CASE WHEN l.`status` = 'settled' THEN 0 ELSE l.`amount` END,
|
||
l.`currency`,
|
||
-- 'failed' القديمة تعود 'pending': الفشل السابق لم يكن قراراً بل
|
||
-- غياب محرك. حجبها عن التسوية الآن يعني إسقاط مال مستحق فعلاً.
|
||
CASE l.`status` WHEN 'failed' THEN 'pending' ELSE l.`status` END,
|
||
l.`settled_at`, l.`note`, l.`created_at`
|
||
FROM `insurance_premium_ledger` l
|
||
JOIN `driver_insurance_policies` p ON p.`id` = l.`policy_id`
|
||
JOIN `insurance_plans` pl ON pl.`id` = p.`plan_id`
|
||
JOIN `obligation_products` op ON op.`code` = pl.`code`
|
||
JOIN `driver_obligations` o ON o.`driver_id` = p.`driver_id`
|
||
AND o.`product_id` = op.`id`
|
||
AND o.`started_at` = p.`started_at`
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM `obligation_ledger` ol
|
||
WHERE ol.`obligation_id` = o.`id` AND ol.`charge_date` = l.`charge_date`
|
||
);
|