-- ═══════════════════════════════════════════════════════════
-- تحديثات نظام النقل
-- تاريخ: 7 أكتوبر 2025
-- ═══════════════════════════════════════════════════════════

-- 1. إضافة حقول الأسعار لجدول transport_types
ALTER TABLE `transport_types` 
ADD COLUMN `price_per_student` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'سعر النقل لكل طالب',
ADD COLUMN `driver_commission_rate` DECIMAL(5,2) NOT NULL DEFAULT 100.00 COMMENT 'نسبة السائق من سعر النقل (%) - افتراضي 100%',
ADD COLUMN `description` VARCHAR(255) NULL COMMENT 'وصف نوع النقل';

-- 2. تحديث أنواع النقل الموجودة بالأسعار
UPDATE `transport_types` SET 
    `price_per_student` = 150000.00,
    `driver_commission_rate` = 100.00,
    `description` = 'نقل داخلي ضمن المدينة'
WHERE `id` = 1 AND `name` = 'داخلي';

UPDATE `transport_types` SET 
    `price_per_student` = 250000.00,
    `driver_commission_rate` = 100.00,
    `description` = 'نقل خارجي خارج المدينة'
WHERE `id` = 2 AND `name` = 'خارجي';

-- 3. إنشاء جدول دفتر أستاذ السائقين (Driver Ledger)
CREATE TABLE IF NOT EXISTS `driver_ledger` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `driver_id` INT(11) NOT NULL COMMENT 'معرف السائق',
  `transaction_type` ENUM('credit','debit') NOT NULL COMMENT 'نوع العملية: credit=إيداع, debit=سحب',
  `amount` DECIMAL(12,2) NOT NULL COMMENT 'المبلغ',
  `description` VARCHAR(255) NOT NULL COMMENT 'الوصف',
  `student_id` INT(11) NULL COMMENT 'معرف الطالب (إن كان متعلق بطالب)',
  `transport_line_id` INT(11) NULL COMMENT 'معرف خط النقل',
  `transport_type_id` INT(11) NULL COMMENT 'معرف نوع النقل',
  `payment_id` INT(11) NULL COMMENT 'معرف عملية الدفع (عند سحب المبلغ)',
  `balance_after` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'الرصيد بعد العملية',
  `created_by` INT(11) NULL COMMENT 'المستخدم الذي أنشأ العملية',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'تاريخ العملية',
  `notes` TEXT NULL COMMENT 'ملاحظات إضافية',
  PRIMARY KEY (`id`),
  KEY `idx_driver_id` (`driver_id`),
  KEY `idx_student_id` (`student_id`),
  KEY `idx_transport_line` (`transport_line_id`),
  KEY `idx_transaction_type` (`transaction_type`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_driver_ledger_driver` FOREIGN KEY (`driver_id`) REFERENCES `drivers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_driver_ledger_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_driver_ledger_line` FOREIGN KEY (`transport_line_id`) REFERENCES `transport_lines` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_driver_ledger_type` FOREIGN KEY (`transport_type_id`) REFERENCES `transport_types` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='دفتر أستاذ السائقين - تتبع الإيداعات والسحوبات';

-- 4. إنشاء جدول سحوبات السائقين (Driver Withdrawals)
CREATE TABLE IF NOT EXISTS `driver_withdrawals` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `driver_id` INT(11) NOT NULL COMMENT 'معرف السائق',
  `amount` DECIMAL(12,2) NOT NULL COMMENT 'المبلغ المسحوب',
  `payment_date` DATE NOT NULL COMMENT 'تاريخ الدفع',
  `payment_method` ENUM('cash','bank_transfer','check') NOT NULL DEFAULT 'cash' COMMENT 'طريقة الدفع',
  `check_number` VARCHAR(50) NULL COMMENT 'رقم الشيك (إن وجد)',
  `notes` TEXT NULL COMMENT 'ملاحظات',
  `status` ENUM('pending','completed','cancelled') NOT NULL DEFAULT 'completed' COMMENT 'الحالة',
  `created_by` INT(11) NULL COMMENT 'المستخدم الذي أنشأ العملية',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_driver_id` (`driver_id`),
  KEY `idx_payment_date` (`payment_date`),
  KEY `idx_status` (`status`),
  CONSTRAINT `fk_driver_withdrawals_driver` FOREIGN KEY (`driver_id`) REFERENCES `drivers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='سحوبات السائقين';

-- 5. إنشاء VIEW لحساب رصيد كل سائق
CREATE OR REPLACE VIEW `v_driver_balances` AS
SELECT 
    d.id AS driver_id,
    d.full_name AS driver_name,
    d.phone,
    d.status,
    COALESCE(SUM(CASE WHEN dl.transaction_type = 'credit' THEN dl.amount ELSE 0 END), 0) AS total_credits,
    COALESCE(SUM(CASE WHEN dl.transaction_type = 'debit' THEN dl.amount ELSE 0 END), 0) AS total_debits,
    COALESCE(SUM(CASE WHEN dl.transaction_type = 'credit' THEN dl.amount ELSE -dl.amount END), 0) AS current_balance,
    COUNT(DISTINCT CASE WHEN s.status = 'active' AND s.driver_employee_id = d.id THEN s.id END) AS active_students_count
FROM drivers d
LEFT JOIN driver_ledger dl ON dl.driver_id = d.id
LEFT JOIN students s ON s.driver_employee_id = d.id
GROUP BY d.id, d.full_name, d.phone, d.status;

-- 6. تحديث حقل commission_rate في جدول drivers ليكون افتراضي 100
ALTER TABLE `drivers` 
MODIFY COLUMN `commission_rate` DECIMAL(5,2) NOT NULL DEFAULT 100.00 COMMENT 'نسبة العمولة من رسوم النقل (%) - افتراضي 100%';

-- 7. إضافة triggers لحساب balance_after تلقائياً
DELIMITER $$

CREATE TRIGGER `before_insert_driver_ledger` 
BEFORE INSERT ON `driver_ledger`
FOR EACH ROW
BEGIN
    DECLARE current_bal DECIMAL(12,2);
    
    -- حساب الرصيد الحالي
    SELECT COALESCE(SUM(CASE WHEN transaction_type = 'credit' THEN amount ELSE -amount END), 0)
    INTO current_bal
    FROM driver_ledger
    WHERE driver_id = NEW.driver_id;
    
    -- حساب الرصيد الجديد بعد العملية
    IF NEW.transaction_type = 'credit' THEN
        SET NEW.balance_after = current_bal + NEW.amount;
    ELSE
        SET NEW.balance_after = current_bal - NEW.amount;
    END IF;
END$$

DELIMITER ;

-- ═══════════════════════════════════════════════════════════
-- ملاحظات التنفيذ:
-- ═══════════════════════════════════════════════════════════
-- 1. عند إضافة طالب مع نقل:
--    - يتم حساب المبلغ = price_per_student × driver_commission_rate / 100
--    - يتم إضافة سجل credit في driver_ledger
--
-- 2. عند حذف/إلغاء طالب:
--    - يتم إضافة سجل debit في driver_ledger بنفس المبلغ
--
-- 3. عند محاسبة السائق:
--    - يتم إنشاء سجل في driver_withdrawals
--    - يتم إضافة سجل debit في driver_ledger
--
-- 4. الرصيد الحالي = مجموع credits - مجموع debits
-- ═══════════════════════════════════════════════════════════

