-- اختياري: شغّل هذا الملف فقط إذا ظهرت لك رسالة خطأ من نوع:
-- Unknown column 'to_user_id' in 'field list'
-- السبب: بعض القوادح القديمة تستخدم to_user_id بينما جدول notifications الحالي عندك يستخدم user_id.

DROP TRIGGER IF EXISTS trg_orders_after_insert_notify;
DROP TRIGGER IF EXISTS trg_orders_after_update_notify;
DROP TRIGGER IF EXISTS trg_delivery_offers_after_insert_notify;
DROP TRIGGER IF EXISTS trg_delivery_offers_after_update_notify;
DROP TRIGGER IF EXISTS trg_products_after_insert_notify;
DROP TRIGGER IF EXISTS trg_products_after_update_notify;

DELIMITER //

CREATE TRIGGER trg_orders_after_insert_notify
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO notifications
        (user_id, type, title, message, action_url, entity_type, entity_id)
    VALUES
        (NEW.customer_id, 'order_created', 'تم إنشاء طلبك', CONCAT('تم إنشاء الطلب رقم ', NEW.order_number), CONCAT('customer/order_track.php?id=', NEW.id), 'order', NEW.id);

    INSERT INTO notifications
        (user_id, type, title, message, action_url, entity_type, entity_id)
    SELECT f.user_id, 'family_order_new', 'طلب جديد', CONCAT('وصلك طلب جديد رقم ', NEW.order_number), CONCAT('family/orders/show.php?id=', NEW.id), 'order', NEW.id
    FROM families f
    WHERE f.id = NEW.family_id AND f.user_id IS NOT NULL;

    INSERT INTO notifications
        (user_id, type, title, message, action_url, entity_type, entity_id)
    SELECT u.id, 'admin_order_new', 'طلب جديد', CONCAT('تم إنشاء طلب جديد رقم ', NEW.order_number), CONCAT('admin/orders/show.php?id=', NEW.id), 'order', NEW.id
    FROM users u
    WHERE u.role = 'admin';
END//

CREATE TRIGGER trg_orders_after_update_notify
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF OLD.status <> NEW.status THEN
        INSERT INTO notifications
            (user_id, type, title, message, action_url, entity_type, entity_id)
        VALUES
            (NEW.customer_id, 'order_status_changed', 'تحديث حالة الطلب', CONCAT('تم تحديث حالة الطلب رقم ', NEW.order_number, ' إلى ', NEW.status), CONCAT('customer/order_track.php?id=', NEW.id), 'order', NEW.id);

        INSERT INTO notifications
            (user_id, type, title, message, action_url, entity_type, entity_id)
        SELECT f.user_id, 'family_order_status_changed', 'تحديث حالة طلب', CONCAT('تم تحديث حالة الطلب رقم ', NEW.order_number, ' إلى ', NEW.status), CONCAT('family/orders/show.php?id=', NEW.id), 'order', NEW.id
        FROM families f
        WHERE f.id = NEW.family_id AND f.user_id IS NOT NULL;

        IF NEW.driver_id IS NOT NULL THEN
            INSERT INTO notifications
                (user_id, type, title, message, action_url, entity_type, entity_id)
            VALUES
                (NEW.driver_id, 'driver_order_status_changed', 'تحديث حالة طلب', CONCAT('تم تحديث حالة الطلب رقم ', NEW.order_number, ' إلى ', NEW.status), CONCAT('driver/orders/show.php?id=', NEW.id), 'order', NEW.id);
        END IF;
    END IF;

    IF OLD.delivery_dispatch_status <> NEW.delivery_dispatch_status AND NEW.delivery_dispatch_status = 'no_driver' THEN
        INSERT INTO notifications
            (user_id, type, title, message, action_url, entity_type, entity_id)
        SELECT u.id, 'admin_no_driver', 'طلب بدون مندوب', CONCAT('لم يتم العثور على مندوب للطلب رقم ', NEW.order_number), CONCAT('admin/orders/show.php?id=', NEW.id), 'order', NEW.id
        FROM users u
        WHERE u.role = 'admin';
    END IF;
END//

CREATE TRIGGER trg_delivery_offers_after_insert_notify
AFTER INSERT ON delivery_offers
FOR EACH ROW
BEGIN
    IF NEW.status = 'sent' THEN
        INSERT INTO notifications
            (user_id, type, title, message, action_url, entity_type, entity_id)
        SELECT NEW.driver_id, 'delivery_offer_sent', 'عرض توصيل جديد', CONCAT('لديك عرض توصيل للطلب رقم ', o.order_number), 'driver/offers/index.php', 'delivery_offer', NEW.id
        FROM orders o
        WHERE o.id = NEW.order_id;
    END IF;
END//

CREATE TRIGGER trg_delivery_offers_after_update_notify
AFTER UPDATE ON delivery_offers
FOR EACH ROW
BEGIN
    IF OLD.status <> NEW.status AND NEW.status IN ('accepted','rejected','expired') THEN
        INSERT INTO notifications
            (user_id, type, title, message, action_url, entity_type, entity_id)
        SELECT f.user_id,
               'family_delivery_offer_update',
               'تحديث عرض التوصيل',
               CONCAT('تحديث عرض التوصيل للطلب رقم ', o.order_number, ': ', NEW.status),
               CONCAT('family/orders/show.php?id=', o.id),
               'delivery_offer',
               NEW.id
        FROM orders o
        INNER JOIN families f ON f.id = o.family_id
        WHERE o.id = NEW.order_id
          AND f.user_id IS NOT NULL;
    END IF;
END//

CREATE TRIGGER trg_products_after_insert_notify
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    IF NEW.status = 'pending' THEN
        INSERT INTO notifications
            (user_id, type, title, message, action_url, entity_type, entity_id)
        SELECT u.id, 'product_pending', 'منتج بانتظار الاعتماد', CONCAT('يوجد منتج جديد بانتظار الاعتماد: ', NEW.name), CONCAT('admin/products/edit.php?id=', NEW.id), 'product', NEW.id
        FROM users u
        WHERE u.role = 'admin';
    END IF;
END//

CREATE TRIGGER trg_products_after_update_notify
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.status <> NEW.status THEN
        INSERT INTO notifications
            (user_id, type, title, message, action_url, entity_type, entity_id)
        SELECT f.user_id, 'product_status_changed', 'تحديث حالة المنتج', CONCAT('تم تحديث حالة المنتج: ', NEW.name, ' إلى ', NEW.status), CONCAT('family/products/edit.php?id=', NEW.id), 'product', NEW.id
        FROM families f
        WHERE f.id = NEW.family_id
          AND f.user_id IS NOT NULL;

        IF NEW.status = 'pending' THEN
            INSERT INTO notifications
                (user_id, type, title, message, action_url, entity_type, entity_id)
            SELECT u.id, 'product_pending', 'منتج بانتظار الاعتماد', CONCAT('يوجد منتج بانتظار الاعتماد: ', NEW.name), CONCAT('admin/products/edit.php?id=', NEW.id), 'product', NEW.id
            FROM users u
            WHERE u.role = 'admin';
        END IF;
    END IF;
END//

DELIMITER ;