-- ============================================
-- SQL Script للتحديثات على نظام الأقساط
-- ============================================
-- التاريخ: 13 مارس 2026
-- الإصدار: 1.0
-- ============================================

-- ============================================
-- 1. إضافة حقول الدفع الجزئي لجدول الأقساط
-- ============================================

-- التحقق من وجود الجدول
SELECT 'جدول installments موجود' AS status 
FROM information_schema.tables 
WHERE table_name = 'installments';

-- إضافة حقل paid_amount (المبلغ المدفوع)
ALTER TABLE `installments`
ADD COLUMN `paid_amount` DECIMAL(10, 2) NULL DEFAULT 0 AFTER `value`;

-- إضافة حقل payment_date (تاريخ السداد)
ALTER TABLE `installments`
ADD COLUMN `payment_date` DATE NULL AFTER `paid_amount`;

-- إضافة حقل notes (ملاحظات)
ALTER TABLE `installments`
ADD COLUMN `notes` TEXT NULL AFTER `is_paid`;

-- التحقق من إضافة الحقول
DESCRIBE `installments`;

-- ============================================
-- 2. جعل installment_id اختياري في جدول المدفوعات
-- ============================================

-- التحقق من وجود الجدول
SELECT 'جدول payments موجود' AS status 
FROM information_schema.tables 
WHERE table_name = 'payments';

-- جعل installment_id يقبل NULL
ALTER TABLE `payments`
MODIFY COLUMN `installment_id` BIGINT UNSIGNED NULL;

-- التحقق من التعديل
SHOW COLUMNS FROM `payments` LIKE 'installment_id';

-- ============================================
-- 3. تحديث البيانات الموجودة (اختياري)
-- ============================================

-- تعيين paid_amount = value للأقساط المدفوعة بالكامل
UPDATE `installments`
SET `paid_amount` = `value`
WHERE `is_paid` = 1 AND `paid_amount` IS NULL;

-- تعيين paid_amount = 0 للأقساط غير المدفوعة
UPDATE `installments`
SET `paid_amount` = 0
WHERE `is_paid` = 0 AND `paid_amount` IS NULL;

-- ============================================
-- 4. فهارس للأداء (اختياري لكن موصى به)
-- ============================================

-- فهرس على paid_amount للاستعلامات السريعة
CREATE INDEX `idx_installments_paid_amount` 
ON `installments` (`paid_amount`);

-- فهرس على is_paid و paid_amount معاً
CREATE INDEX `idx_installments_payment_status` 
ON `installments` (`is_paid`, `paid_amount`);

-- فهرس على contract_id و due_date للتوزيع التلقائي
CREATE INDEX `idx_installments_contract_due` 
ON `installments` (`contract_id`, `due_date`, `is_paid`);

-- ============================================
-- 5. استعلامات للتحقق من التحديثات
-- ============================================

-- عرض هيكل جدول installments بعد التحديث
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'installments'
AND TABLE_SCHEMA = DATABASE()
ORDER BY ORDINAL_POSITION;

-- عرض هيكل جدول payments بعد التحديث
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'payments'
AND COLUMN_NAME = 'installment_id'
AND TABLE_SCHEMA = DATABASE();

-- ============================================
-- 6. إحصائيات ما بعد التحديث
-- ============================================

-- عدد الأقساط المدفوعة كلياً
SELECT 
    'أقساط مدفوعة كلياً' AS type,
    COUNT(*) AS count,
    SUM(paid_amount) AS total_amount
FROM installments
WHERE is_paid = 1;

-- عدد الأقساط المدفوعة جزئياً
SELECT 
    'أقساط مدفوعة جزئياً' AS type,
    COUNT(*) AS count,
    SUM(paid_amount) AS total_paid,
    SUM(value - COALESCE(paid_amount, 0)) AS total_remaining
FROM installments
WHERE is_paid = 0 
AND COALESCE(paid_amount, 0) > 0;

-- عدد الأقساط غير المدفوعة
SELECT 
    'أقساط غير مدفوعة' AS type,
    COUNT(*) AS count,
    SUM(value) AS total_amount
FROM installments
WHERE is_paid = 0 
AND COALESCE(paid_amount, 0) = 0;

-- ============================================
-- 7. Views مفيدة (اختياري)
-- ============================================

-- View لعرض الأقساط مع حالتها وتفاصيلها
CREATE OR REPLACE VIEW `vw_installments_detailed` AS
SELECT 
    i.id,
    i.contract_id,
    c.customer_id,
    cu.name AS customer_name,
    i.due_date,
    i.value,
    COALESCE(i.paid_amount, 0) AS paid_amount,
    (i.value - COALESCE(i.paid_amount, 0)) AS remaining_amount,
    CASE 
        WHEN i.is_paid = 1 THEN 'مدفوع كلياً'
        WHEN COALESCE(i.paid_amount, 0) > 0 THEN 'مدفوع جزئياً'
        ELSE 'غير مدفوع'
    END AS payment_status,
    CASE 
        WHEN COALESCE(i.paid_amount, 0) = 0 THEN 0
        ELSE ROUND((COALESCE(i.paid_amount, 0) / i.value) * 100, 2)
    END AS payment_percentage,
    i.payment_date,
    i.is_paid,
    i.notes
FROM installments i
INNER JOIN contracts c ON i.contract_id = c.id
INNER JOIN customers cu ON c.customer_id = cu.id;

-- View للأقساط المتأخرة
CREATE OR REPLACE VIEW `vw_overdue_installments` AS
SELECT 
    i.id,
    i.contract_id,
    c.customer_id,
    cu.name AS customer_name,
    cu.phone AS customer_phone,
    i.due_date,
    DATEDIFF(CURDATE(), i.due_date) AS days_overdue,
    i.value,
    COALESCE(i.paid_amount, 0) AS paid_amount,
    (i.value - COALESCE(i.paid_amount, 0)) AS remaining_amount,
    CASE 
        WHEN COALESCE(i.paid_amount, 0) > 0 THEN 'مدفوع جزئياً'
        ELSE 'متأخر'
    END AS status,
    CASE 
        WHEN COALESCE(i.paid_amount, 0) = 0 THEN 0
        ELSE ROUND((COALESCE(i.paid_amount, 0) / i.value) * 100, 2)
    END AS payment_percentage
FROM installments i
INNER JOIN contracts c ON i.contract_id = c.id
INNER JOIN customers cu ON c.customer_id = cu.id
WHERE i.due_date < CURDATE()
AND COALESCE(i.paid_amount, 0) < i.value
AND c.status = 'active'
ORDER BY i.due_date ASC;

-- ============================================
-- 8. Stored Procedures مفيدة (اختياري)
-- ============================================

-- Procedure لحساب إحصائيات العقد
DELIMITER //

CREATE PROCEDURE `sp_get_contract_stats`(IN contract_id_param BIGINT)
BEGIN
    SELECT 
        -- الإجماليات
        COUNT(*) AS total_installments,
        SUM(value) AS total_value,
        SUM(COALESCE(paid_amount, 0)) AS total_collected,
        SUM(value - COALESCE(paid_amount, 0)) AS total_remaining,
        
        -- أقساط مدفوعة كلياً
        SUM(CASE WHEN is_paid = 1 THEN 1 ELSE 0 END) AS paid_count,
        SUM(CASE WHEN is_paid = 1 THEN value ELSE 0 END) AS paid_value,
        
        -- أقساط مدفوعة جزئياً
        SUM(CASE WHEN is_paid = 0 AND COALESCE(paid_amount, 0) > 0 THEN 1 ELSE 0 END) AS partial_count,
        SUM(CASE WHEN is_paid = 0 AND COALESCE(paid_amount, 0) > 0 THEN paid_amount ELSE 0 END) AS partial_paid,
        SUM(CASE WHEN is_paid = 0 AND COALESCE(paid_amount, 0) > 0 THEN (value - paid_amount) ELSE 0 END) AS partial_remaining,
        
        -- أقساط غير مدفوعة
        SUM(CASE WHEN is_paid = 0 AND COALESCE(paid_amount, 0) = 0 THEN 1 ELSE 0 END) AS unpaid_count,
        SUM(CASE WHEN is_paid = 0 AND COALESCE(paid_amount, 0) = 0 THEN value ELSE 0 END) AS unpaid_value,
        
        -- نسبة السداد
        ROUND((SUM(COALESCE(paid_amount, 0)) / SUM(value)) * 100, 2) AS payment_percentage
        
    FROM installments
    WHERE contract_id = contract_id_param;
END //

DELIMITER ;

-- ============================================
-- 9. استعلامات اختبارية
-- ============================================

-- اختبار 1: عرض جميع الأقساط مع تفاصيلها
SELECT * FROM vw_installments_detailed LIMIT 10;

-- اختبار 2: عرض الأقساط المتأخرة
SELECT * FROM vw_overdue_installments LIMIT 10;

-- اختبار 3: إحصائيات عقد معين (استبدل 1 برقم العقد)
CALL sp_get_contract_stats(1);

-- اختبار 4: المدفوعات العامة (بدون قسط محدد)
SELECT 
    id,
    customer_id,
    contract_id,
    amount,
    payment_date,
    installment_id
FROM payments
WHERE installment_id IS NULL
ORDER BY payment_date DESC
LIMIT 10;

-- ============================================
-- 10. تنظيف (Rollback) - استخدم بحذر!
-- ============================================

-- في حالة الحاجة لعكس التغييرات:
/*
-- حذف الحقول المضافة
ALTER TABLE `installments`
DROP COLUMN `paid_amount`,
DROP COLUMN `payment_date`,
DROP COLUMN `notes`;

-- إرجاع installment_id للحالة السابقة
ALTER TABLE `payments`
MODIFY COLUMN `installment_id` BIGINT UNSIGNED NOT NULL;

-- حذف الفهارس
DROP INDEX `idx_installments_paid_amount` ON `installments`;
DROP INDEX `idx_installments_payment_status` ON `installments`;
DROP INDEX `idx_installments_contract_due` ON `installments`;

-- حذف Views
DROP VIEW IF EXISTS `vw_installments_detailed`;
DROP VIEW IF EXISTS `vw_overdue_installments`;

-- حذف Procedures
DROP PROCEDURE IF EXISTS `sp_get_contract_stats`;
*/

-- ============================================
-- النهاية
-- ============================================

SELECT '✅ تم تطبيق جميع التحديثات بنجاح!' AS status;

-- ملاحظة: بعد تطبيق هذا الـ SQL:
-- 1. قم بتشغيل: php artisan optimize:clear
-- 2. تأكد من إنشاء PaymentObserver
-- 3. سجل Observer في AppServiceProvider
-- 4. اختبر النظام بإضافة دفعة جديدة