تعبيرات CASE الشرطية

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN condition3 THEN result3
    ELSE default_result
END

-- تصنيف العملاء حسب إجمالي المشتريات
SELECT
    customer_name,
    total_purchases,
    CASE
        WHEN total_purchases >= 100000 THEN 'بلاتيني'
        WHEN total_purchases >= 50000 THEN 'ذهبي'
        WHEN total_purchases >= 10000 THEN 'فضي'
        WHEN total_purchases >= 1000 THEN 'برونزي'
        ELSE 'عادي'
    END AS customer_tier
FROM customers;

-- النتيجة:
-- customer_name | total_purchases | customer_tier
-- أحمد محمد     | 150000          | بلاتيني
-- سارة علي      | 45000           | فضي
-- خالد عبدالله  | 500             | عادي

SELECT
    order_id,
    order_date,
    ship_date,
    CASE
        WHEN ship_date IS NULL AND DATEDIFF(CURDATE(), order_date) > 7
            THEN 'متأخر - يحتاج متابعة'
        WHEN ship_date IS NULL
            THEN 'قيد المعالجة'
        WHEN DATEDIFF(ship_date, order_date) <= 2
            THEN 'تم الشحن بسرعة'
        WHEN DATEDIFF(ship_date, order_date) <= 5
            THEN 'تم الشحن في الوقت'
        ELSE 'تم الشحن متأخراً'
    END AS shipping_status
FROM orders;

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    WHEN value3 THEN result3
    ELSE default_result
END

-- ترجمة رمز الحالة
SELECT
    order_id,
    status,
    CASE status
        WHEN 'P' THEN 'قيد الانتظار'
        WHEN 'A' THEN 'تمت الموافقة'
        WHEN 'S' THEN 'تم الشحن'
        WHEN 'D' THEN 'تم التوصيل'
        WHEN 'C' THEN 'ملغي'
        WHEN 'R' THEN 'مرتجع'
        ELSE 'غير معروف'
    END AS status_arabic
FROM orders;

-- ترجمة أيام الأسبوع
SELECT
    order_date,
    DAYOFWEEK(order_date) AS day_num,
    CASE DAYOFWEEK(order_date)
        WHEN 1 THEN 'الأحد'
        WHEN 2 THEN 'الاثنين'
        WHEN 3 THEN 'الثلاثاء'
        WHEN 4 THEN 'الأربعاء'
        WHEN 5 THEN 'الخميس'
        WHEN 6 THEN 'الجمعة'
        WHEN 7 THEN 'السبت'
    END AS day_arabic
FROM orders;

SELECT
    product_name,
    price,
    stock_quantity,
    CASE
        WHEN stock_quantity = 0 THEN 'نفد المخزون'
        WHEN stock_quantity < 10 THEN 'مخزون منخفض'
        WHEN stock_quantity < 50 THEN 'مخزون متوسط'
        ELSE 'مخزون كافي'
    END AS stock_status,
    CASE
        WHEN stock_quantity = 0 THEN 'danger'
        WHEN stock_quantity < 10 THEN 'warning'
        ELSE 'success'
    END AS badge_class
FROM products;

-- فلترة حسب متغير
SET @filter_type = 'expensive';

SELECT * FROM products
WHERE
    CASE @filter_type
        WHEN 'expensive' THEN price > 1000
        WHEN 'cheap' THEN price < 100
        WHEN 'in_stock' THEN stock_quantity > 0
        ELSE TRUE
    END;

-- فلترة معقدة
SELECT * FROM orders
WHERE
    CASE
        WHEN @user_role = 'admin' THEN TRUE
        WHEN @user_role = 'manager' THEN department_id = @user_dept
        ELSE customer_id = @user_id
    END;

-- ترتيب حسب الأولوية
SELECT * FROM tickets
ORDER BY
    CASE priority
        WHEN 'urgent' THEN 1
        WHEN 'high' THEN 2
        WHEN 'medium' THEN 3
        WHEN 'low' THEN 4
        ELSE 5
    END,
    created_at;

-- ترتيب مع NULL في النهاية
SELECT * FROM products
ORDER BY
    CASE WHEN price IS NULL THEN 1 ELSE 0 END,
    price;

-- ترتيب بناءً على شرط
SELECT * FROM customers
ORDER BY
    CASE
        WHEN is_vip = 1 THEN 0  -- VIP أولاً
        ELSE 1
    END,
    customer_name;

-- تجميع حسب فئات السعر
SELECT
    CASE
        WHEN price < 100 THEN 'رخيص'
        WHEN price < 500 THEN 'متوسط'
        WHEN price < 1000 THEN 'مرتفع'
        ELSE 'فاخر'
    END AS price_category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
GROUP BY
    CASE
        WHEN price < 100 THEN 'رخيص'
        WHEN price < 500 THEN 'متوسط'
        WHEN price < 1000 THEN 'مرتفع'
        ELSE 'فاخر'
    END;

-- عدد الطلبات حسب الحالة
SELECT
    COUNT(*) AS total_orders,
    COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count,
    COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_count,
    COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered_count,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_count
FROM orders;

-- أو باستخدام SUM
SELECT
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending,
    SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped,
    SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered
FROM orders;

-- مبيعات كل فئة لكل شهر
SELECT
    category,
    SUM(CASE WHEN MONTH(sale_date) = 1 THEN amount ELSE 0 END) AS jan,
    SUM(CASE WHEN MONTH(sale_date) = 2 THEN amount ELSE 0 END) AS feb,
    SUM(CASE WHEN MONTH(sale_date) = 3 THEN amount ELSE 0 END) AS mar,
    SUM(CASE WHEN MONTH(sale_date) = 4 THEN amount ELSE 0 END) AS apr,
    SUM(CASE WHEN MONTH(sale_date) = 5 THEN amount ELSE 0 END) AS may,
    SUM(CASE WHEN MONTH(sale_date) = 6 THEN amount ELSE 0 END) AS jun,
    SUM(amount) AS total
FROM sales
WHERE YEAR(sale_date) = 2025
GROUP BY category;

-- النتيجة:
-- category   | jan    | feb    | mar    | ... | total
-- إلكترونيات | 50000  | 45000  | 60000  | ... | 350000
-- ملابس      | 30000  | 25000  | 35000  | ... | 200000

SELECT
    COUNT(*) AS total,
    ROUND(
        100.0 * SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) / COUNT(*),
        1
    ) AS delivery_rate,
    ROUND(
        100.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*),
        1
    ) AS cancellation_rate,
    ROUND(
        100.0 * SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) / COUNT(*),
        1
    ) AS return_rate
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- تحديث الأسعار حسب الفئة
UPDATE products
SET price = price * CASE category
    WHEN 'electronics' THEN 1.10  -- زيادة 10%
    WHEN 'clothing' THEN 1.05     -- زيادة 5%
    WHEN 'food' THEN 1.03         -- زيادة 3%
    ELSE 1.00                     -- بدون تغيير
END
WHERE active = 1;

-- تحديث حالة الطلب
UPDATE orders
SET status = CASE
    WHEN ship_date IS NOT NULL AND delivery_date IS NOT NULL THEN 'delivered'
    WHEN ship_date IS NOT NULL THEN 'shipped'
    WHEN payment_date IS NOT NULL THEN 'paid'
    ELSE 'pending'
END;

-- تحديث تصنيف العملاء
UPDATE customers
SET tier = CASE
    WHEN total_purchases >= 100000 THEN 'platinum'
    WHEN total_purchases >= 50000 THEN 'gold'
    WHEN total_purchases >= 10000 THEN 'silver'
    ELSE 'bronze'
END;

-- COALESCE: أول قيمة غير فارغة
SELECT
    customer_name,
    COALESCE(phone, mobile, email, 'لا توجد معلومات اتصال') AS contact
FROM customers;

-- IFNULL (MySQL) / ISNULL (SQL Server)
SELECT
    product_name,
    IFNULL(description, 'لا يوجد وصف') AS description
FROM products;

-- NULLIF: يُرجع NULL إذا تساوت القيمتان
SELECT
    product_name,
    NULLIF(discount_price, 0) AS discount_price  -- يحول 0 إلى NULL
FROM products;

-- CASE مع NULL
SELECT
    customer_name,
    CASE
        WHEN last_order_date IS NULL THEN 'لم يطلب أبداً'
        WHEN DATEDIFF(CURDATE(), last_order_date) > 365 THEN 'عميل غير نشط'
        WHEN DATEDIFF(CURDATE(), last_order_date) > 90 THEN 'عميل خامل'
        ELSE 'عميل نشط'
    END AS activity_status
FROM customers;

-- ❌ قد يسبب خطأ
SELECT total_sales / total_orders AS avg_order_value
FROM summary;

-- ✅ باستخدام CASE
SELECT
    CASE
        WHEN total_orders = 0 THEN 0
        ELSE total_sales / total_orders
    END AS avg_order_value
FROM summary;

-- ✅ باستخدام NULLIF
SELECT
    total_sales / NULLIF(total_orders, 0) AS avg_order_value
FROM summary;

-- حساب نسبة التحويل بأمان
SELECT
    campaign_name,
    clicks,
    conversions,
    CASE
        WHEN clicks = 0 THEN 0
        ELSE ROUND(100.0 * conversions / clicks, 2)
    END AS conversion_rate
FROM campaigns;

-- منطق متداخل معقد
SELECT
    product_name,
    category,
    price,
    stock_quantity,
    CASE
        WHEN category = 'electronics' THEN
            CASE
                WHEN price > 5000 THEN 'إلكترونيات فاخرة'
                WHEN price > 1000 THEN 'إلكترونيات متوسطة'
                ELSE 'إلكترونيات اقتصادية'
            END
        WHEN category = 'clothing' THEN
            CASE
                WHEN price > 500 THEN 'ملابس فاخرة'
                ELSE 'ملابس عادية'
            END
        ELSE 'فئة أخرى'
    END AS detailed_category
FROM products;

-- بديل أكثر وضوحاً
SELECT
    product_name,
    CASE
        WHEN category = 'electronics' AND price > 5000 THEN 'إلكترونيات فاخرة'
        WHEN category = 'electronics' AND price > 1000 THEN 'إلكترونيات متوسطة'
        WHEN category = 'electronics' THEN 'إلكترونيات اقتصادية'
        WHEN category = 'clothing' AND price > 500 THEN 'ملابس فاخرة'
        WHEN category = 'clothing' THEN 'ملابس عادية'
        ELSE 'فئة أخرى'
    END AS detailed_category
FROM products;

SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_value,

    -- معدل الإلغاء
    ROUND(100.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*), 1)
        AS cancellation_rate,

    -- تصنيف الأداء
    CASE
        WHEN SUM(total_amount) >= 100000 THEN '🟢 ممتاز'
        WHEN SUM(total_amount) >= 50000 THEN '🟡 جيد'
        WHEN SUM(total_amount) >= 25000 THEN '🟠 مقبول'
        ELSE '🔴 يحتاج تحسين'
    END AS performance_rating
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

SELECT
    p.product_name,
    p.base_price,
    c.tier AS customer_tier,
    o.quantity,

    -- خصم الكمية
    CASE
        WHEN o.quantity >= 100 THEN 0.15
        WHEN o.quantity >= 50 THEN 0.10
        WHEN o.quantity >= 20 THEN 0.05
        ELSE 0
    END AS quantity_discount,

    -- خصم العميل
    CASE c.tier
        WHEN 'platinum' THEN 0.10
        WHEN 'gold' THEN 0.07
        WHEN 'silver' THEN 0.05
        ELSE 0
    END AS tier_discount,

    -- السعر النهائي
    ROUND(
        p.base_price * o.quantity *
        (1 - CASE WHEN o.quantity >= 100 THEN 0.15
                  WHEN o.quantity >= 50 THEN 0.10
                  WHEN o.quantity >= 20 THEN 0.05 ELSE 0 END) *
        (1 - CASE c.tier WHEN 'platinum' THEN 0.10
                         WHEN 'gold' THEN 0.07
                         WHEN 'silver' THEN 0.05 ELSE 0 END),
        2
    ) AS final_price
FROM order_items o
JOIN products p ON o.product_id = p.product_id
JOIN orders ord ON o.order_id = ord.order_id
JOIN customers c ON ord.customer_id = c.customer_id;

WITH customer_rfm AS (
    SELECT
        customer_id,
        DATEDIFF(CURDATE(), MAX(order_date)) AS recency,
        COUNT(*) AS frequency,
        SUM(total_amount) AS monetary
    FROM orders
    GROUP BY customer_id
)
SELECT
    customer_id,
    recency,
    frequency,
    monetary,

    -- تصنيف Recency
    CASE
        WHEN recency <= 30 THEN 5
        WHEN recency <= 60 THEN 4
        WHEN recency <= 90 THEN 3
        WHEN recency <= 180 THEN 2
        ELSE 1
    END AS r_score,

    -- تصنيف Frequency
    CASE
        WHEN frequency >= 20 THEN 5
        WHEN frequency >= 10 THEN 4
        WHEN frequency >= 5 THEN 3
        WHEN frequency >= 2 THEN 2
        ELSE 1
    END AS f_score,

    -- تصنيف Monetary
    CASE
        WHEN monetary >= 50000 THEN 5
        WHEN monetary >= 20000 THEN 4
        WHEN monetary >= 10000 THEN 3
        WHEN monetary >= 5000 THEN 2
        ELSE 1
    END AS m_score,

    -- شريحة العميل
    CASE
        WHEN recency <= 30 AND frequency >= 10 AND monetary >= 20000 THEN 'أبطال'
        WHEN recency <= 60 AND frequency >= 5 THEN 'عملاء مخلصون'
        WHEN recency <= 30 AND frequency < 3 THEN 'جدد واعدون'
        WHEN recency > 180 AND monetary >= 20000 THEN 'بحاجة للتنشيط'
        WHEN recency > 180 THEN 'في خطر الفقدان'
        ELSE 'عادي'
    END AS customer_segment
FROM customer_rfm;