WITH cte_name AS (
    -- استعلام CTE
    SELECT column1, column2 FROM table1
)
-- الاستعلام الرئيسي يشير إلى CTE
SELECT * FROM cte_name;

WITH RECURSIVE cte_name AS (
    -- المرسلي: استعلام أولي
    SELECT ... FROM table WHERE condition
    UNION ALL
    -- التكراري: يشير إلى نفسه
    SELECT ... FROM table JOIN cte_name ON condition
)
SELECT * FROM cte_name;

CREATE TEMPORARY TABLE temp_result (
    id INT,
    name VARCHAR(50),
    amount DECIMAL(10,2)
);

INSERT INTO temp_result SELECT ...;
SELECT * FROM temp_result;

-- إيجاد جميع رؤساء الموظف (صعوداً)
-- إيجاد جميع الأقسام الفرعية تحت قسم (نزولاً)
-- إنشاء سلسلة تواريخ (سلسلة أرقام)

-- CTE أساسي
WITH cte_name AS (
    SELECT ...
)
SELECT ... FROM cte_name;

-- عدة CTEs
WITH cte1 AS (...),
     cte2 AS (...)
SELECT ... FROM cte1 JOIN cte2 ON ...;

-- CTE تكرارية
WITH RECURSIVE cte_name AS (
    -- المرسلي (نقطة البداية)
    SELECT ... FROM table WHERE initial_condition
    UNION ALL
    -- التكراري (يشير إلى نفسه)
    SELECT ... FROM table 
    JOIN cte_name ON join_condition
    WHERE termination_condition
)
SELECT * FROM cte_name;

-- جدول مؤقت
CREATE TEMPORARY TABLE temp_name AS
SELECT ... FROM ...;

-- أو إنشاء يدوي
CREATE TEMPORARY TABLE temp_name (
    col1 INT,
    col2 VARCHAR(50)
);

WITH dept_stats AS (
    SELECT 
        d.department_id,
        d.department_name AS department,
        COUNT(DISTINCT e.employee_id) AS employee_count,
        COUNT(o.order_id) AS order_count,
        COALESCE(SUM(o.total_amount), 0) AS total_sales
    FROM departments d
    LEFT JOIN employees e ON d.department_id = e.department_id
    LEFT JOIN orders o ON e.employee_id = o.employee_id AND o.status != 'cancelled'
    GROUP BY d.department_id, d.department_name
)
SELECT 
    department,
    employee_count,
    order_count,
    total_sales,
    CASE WHEN employee_count > 0 THEN ROUND(total_sales / employee_count, 2) ELSE 0 END AS sales_per_capita,
    CASE WHEN employee_count > 0 THEN ROUND(order_count * 1.0 / employee_count, 2) ELSE 0 END AS orders_per_capita
FROM dept_stats
ORDER BY sales_per_capita DESC;

department | employee_count | order_count | total_sales | sales_per_capita | orders_per_capita
-----------+----------------+-------------+-------------+------------------+------------------
Sales      |              2 |           4 |    15100.00 |          7550.00 |              2.00
Tech       |              2 |           3 |     8750.00 |          4375.00 |              1.50
Marketing  |              1 |           0 |        0.00 |             0.00 |              0.00
Finance    |              0 |           0 |        0.00 |             0.00 |              0.00

-- أولاً أضف عمود manager_id والبيانات
ALTER TABLE employees ADD COLUMN manager_id INT;

UPDATE employees SET manager_id = NULL WHERE employee_id = 1;  -- Zhang Wei: المدير العام
UPDATE employees SET manager_id = 1    WHERE employee_id = 2;  -- Li Na -> Zhang Wei
UPDATE employees SET manager_id = 1    WHERE employee_id = 3;  -- Wang Qiang -> Zhang Wei
UPDATE employees SET manager_id = 3    WHERE employee_id = 4;  -- Zhao Min -> Wang Qiang
UPDATE employees SET manager_id = 3    WHERE employee_id = 5;  -- Liu Yang -> Wang Qiang
UPDATE employees SET manager_id = NULL WHERE employee_id = 6;  -- Chen Jing: بدون مدير

-- استعلام تكراري: بدءاً من Zhang Wei، إيجاد جميع المرؤوسين (نزولاً)
WITH RECURSIVE emp_hierarchy AS (
    -- المرسلي: نقطة البداية (Zhang Wei)
    SELECT 
        employee_id,
        CONCAT(first_name, last_name) AS name,
        manager_id,
        0 AS level,
        CAST(CONCAT(first_name, last_name) AS CHAR(500)) AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- التكراري: إيجاد المرؤوسين
    SELECT 
        e.employee_id,
        CONCAT(e.first_name, e.last_name),
        e.manager_id,
        h.level + 1,
        CAST(CONCAT(h.path, ' → ', e.first_name, e.last_name) AS CHAR(500))
    FROM employees e
    INNER JOIN emp_hierarchy h ON e.manager_id = h.employee_id
)
SELECT 
    CONCAT(REPEAT('  ', level), name) AS org_chart,
    level,
    path
FROM emp_hierarchy
ORDER BY path;

org_chart      | level | path
---------------+-------+----------------------------
Zhang Wei      |     0 | Zhang Wei
  Li Na        |     1 | Zhang Wei → Li Na
  Wang Qiang   |     1 | Zhang Wei → Wang Qiang
    Zhao Min   |     2 | Zhang Wei → Wang Qiang → Zhao Min
    Liu Yang   |     2 | Zhang Wei → Wang Qiang → Liu Yang
Chen Jing      |     0 | Chen Jing

WITH order_summary AS (
    -- الخطوة 1: تجميع بيانات الطلبات لكل عميل
    SELECT 
        customer_id,
        MIN(order_date) AS first_purchase,
        MAX(order_date) AS last_purchase,
        COUNT(order_id) AS order_count,
        SUM(total_amount) AS total_spent
    FROM orders
    WHERE status != 'cancelled'
    GROUP BY customer_id
),
customer_ltv AS (
    -- الخطوة 2: حساب أيام العمر وتردد الإنفاق
    SELECT 
        customer_id,
        first_purchase,
        last_purchase,
        order_count,
        total_spent,
        DATEDIFF(last_purchase, first_purchase) AS lifetime_days,
        CASE 
            WHEN DATEDIFF(last_purchase, first_purchase) > 0 
            THEN ROUND(total_spent / (DATEDIFF(last_purchase, first_purchase) / 30.0), 2)
            ELSE total_spent
        END AS monthly_avg_spent
    FROM order_summary
)
SELECT 
    customer_id,
    first_purchase,
    last_purchase,
    order_count,
    total_spent,
    lifetime_days,
    monthly_avg_spent,
    CASE
        WHEN monthly_avg_spent >= 3000 THEN 'قيمة عالية'
        WHEN monthly_avg_spent >= 1000 THEN 'قيمة متوسطة'
        ELSE 'قيمة منخفضة'
    END AS customer_tier
FROM customer_ltv
ORDER BY total_spent DESC;

WITH RECURSIVE date_series AS (
    -- المرسلي: تاريخ البداية
    SELECT CAST('2026-01-01' AS DATE) AS dt
    UNION ALL
    -- التكراري: إضافة يوم واحد في كل مرة
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM date_series
    WHERE dt < '2026-06-30'
)
SELECT 
    ds.dt AS date,
    COALESCE(COUNT(o.order_id), 0) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS sales
FROM date_series ds
LEFT JOIN orders o ON o.order_date = ds.dt AND o.status != 'cancelled'
GROUP BY ds.dt
ORDER BY ds.dt;