-- جدول الأقسام
CREATE TABLE IF NOT EXISTS departments (
    id INTEGER PRIMARY KEY,
    department_name TEXT NOT NULL,
    manager_id INTEGER,
    location TEXT
);

-- جدول الموظفين
CREATE TABLE IF NOT EXISTS employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INTEGER,
    position TEXT,
    salary REAL,
    hire_date TEXT,
    manager_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- جدول المنتجات
CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    category TEXT,
    price REAL,
    stock_quantity INTEGER
);

-- جدول الطلبات
CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY,
    customer_name TEXT,
    product_id INTEGER,
    quantity INTEGER,
    amount REAL,
    order_date TEXT,
    status TEXT,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- إدراج بيانات الاختبار
INSERT OR IGNORE INTO departments VALUES
(1, 'Tech', 1, 'Beijing'),
(2, 'Marketing', 4, 'Shanghai'),
(3, 'Finance', 6, 'Beijing');

INSERT OR IGNORE INTO employees VALUES
(1, 'Alice', 1, 'Senior Engineer', 15000, '2020-01-15', NULL),
(2, 'Bob', 1, 'Mid-level Engineer', 12000, '2021-03-20', 1),
(3, 'Charlie', 1, 'Junior Engineer', 8000, '2022-06-10', 1),
(4, 'Diana', 2, 'Marketing Director', 18000, '2019-05-01', NULL),
(5, 'Eve', 2, 'Marketing Specialist', 9000, '2021-08-15', 4),
(6, 'Frank', 3, 'Finance Manager', 16000, '2020-02-28', NULL),
(7, 'Grace', 3, 'Accountant', 10000, '2021-11-05', 6),
(8, 'Henry', 1, 'Intern Engineer', 5000, '2023-07-01', 2);

INSERT OR IGNORE INTO products VALUES
(1, 'Laptop', 'Electronics', 6999, 50),
(2, 'Wireless Mouse', 'Electronics', 199, 200),
(3, 'Mechanical Keyboard', 'Electronics', 599, 100),
(4, 'Office Chair', 'Furniture', 1299, 30),
(5, 'Monitor', 'Electronics', 2499, 80);

INSERT OR IGNORE INTO orders VALUES
(1, 'CustomerA', 1, 2, 13998, '2024-01-10', 'completed'),
(2, 'CustomerB', 2, 5, 995, '2024-01-15', 'completed'),
(3, 'CustomerA', 3, 3, 1797, '2024-02-01', 'completed'),
(4, 'CustomerC', 1, 1, 6999, '2024-02-15', 'pending'),
(5, 'CustomerB', 4, 2, 2598, '2024-03-01', 'completed'),
(6, 'CustomerD', 5, 4, 9996, '2024-03-10', 'completed'),
(7, 'CustomerA', 2, 10, 1990, '2024-03-15', 'completed'),
(8, 'CustomerC', 3, 1, 599, '2024-04-01', 'cancelled');

-- استعلامات ترتيب مختلفة
WITH salary_ranking AS (
    SELECT 
        e.id,
        e.name,
        d.department_name,
        e.salary,
        e.hire_date,
        -- ترتيب الراتب على مستوى الشركة
        RANK() OVER (ORDER BY e.salary DESC) AS company_rank,
        -- ترتيب الراتب على مستوى الشركة (بدون تعادل)
        ROW_NUMBER() OVER (ORDER BY e.salary DESC) AS company_row_num,
        -- ترتيب الراتب على مستوى القسم
        RANK() OVER (
            PARTITION BY e.department_id 
            ORDER BY e.salary DESC
        ) AS dept_rank,
        -- ترتيب النسبة المئوية للراتب
        PERCENT_RANK() OVER (ORDER BY e.salary DESC) AS percentile,
        -- تجميع الراتب (4 مجموعات)
        NTILE(4) OVER (ORDER BY e.salary DESC) AS salary_quartile
    FROM employees e
    JOIN departments d ON e.department_id = d.id
)
SELECT 
    name,
    department_name,
    salary,
    company_rank AS "ترتيب الشركة",
    dept_rank AS "ترتيب القسم",
    CASE salary_quartile
        WHEN 1 THEN 'المستوى الأعلى'
        WHEN 2 THEN 'أعلى من المتوسط'
        WHEN 3 THEN 'أقل من المتوسط'
        WHEN 4 THEN 'المستوى الأدنى'
    END AS "شريحة الراتب",
    ROUND(percentile * 100, 1) || '%' AS "النسبة المئوية"
FROM salary_ranking
ORDER BY company_rank;

-- ترتيب المبيعات والإيرادات للمنتجات
WITH product_sales AS (
    SELECT 
        p.id,
        p.product_name,
        p.category,
        p.price,
        COALESCE(SUM(o.quantity), 0) AS total_quantity,
        COALESCE(SUM(o.amount), 0) AS total_revenue,
        COUNT(o.id) AS order_count
    FROM products p
    LEFT JOIN orders o ON p.id = o.product_id AND o.status = 'completed'
    GROUP BY p.id, p.product_name, p.category, p.price
)
SELECT 
    product_name AS "المنتج",
    category AS "الفئة",
    price AS "سعر الوحدة",
    total_quantity AS "الكمية الإجمالية",
    total_revenue AS "الإيراد الإجمالي",
    order_count AS "الطلبات",
    RANK() OVER (ORDER BY total_revenue DESC) AS "ترتيب الإيراد",
    RANK() OVER (
        PARTITION BY category 
        ORDER BY total_quantity DESC
    ) AS "ترتيب الكمية حسب الفئة",
    DENSE_RANK() OVER (ORDER BY total_quantity DESC) AS "ترتيب المبيعات"
FROM product_sales
ORDER BY total_revenue DESC;

-- المبيعات الشهرية والحسابات التراكمية
WITH monthly_sales AS (
    SELECT 
        strftime('%Y-%m', order_date) AS month,
        SUM(amount) AS monthly_total,
        COUNT(*) AS order_count
    FROM orders
    WHERE status = 'completed'
    GROUP BY strftime('%Y-%m', order_date)
)
SELECT 
    month AS "الشهر",
    monthly_total AS "المبيعات الشهرية",
    order_count AS "الطلبات",
    -- المبيعات التراكمية
    SUM(monthly_total) OVER (ORDER BY month) AS "المبيعات التراكمية",
    -- الطلبات التراكمية
    SUM(order_count) OVER (ORDER BY month) AS "الطلبات التراكمية",
    -- معدل النمو الشهري
    ROUND(
        (monthly_total - LAG(monthly_total) OVER (ORDER BY month)) 
        / LAG(monthly_total) OVER (ORDER BY month) * 100, 
        2
    ) AS "النمو الشهري %",
    -- الفرق عن الشهر السابق
    monthly_total - LAG(monthly_total) OVER (ORDER BY month) AS "الفرق الشهري"
FROM monthly_sales
ORDER BY month;

-- المتوسط المتحرك لمبالغ الطلبات
WITH order_details AS (
    SELECT 
        o.id,
        o.order_date,
        o.amount,
        o.customer_name,
        -- متوسط متحرك لـ 3 أيام
        AVG(o.amount) OVER (
            ORDER BY o.order_date 
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS avg_3day,
        -- متوسط متحرك لـ 5 أيام
        AVG(o.amount) OVER (
            ORDER BY o.order_date 
            ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
        ) AS avg_5day,
        -- المتوسط التراكمي
        AVG(o.amount) OVER (ORDER BY o.order_date) AS cumulative_avg,
        -- مبلغ الطلب السابق
        LAG(o.amount, 1) OVER (ORDER BY o.order_date) AS prev_amount,
        -- الحد الأقصى التراكمي لمبلغ الطلب
        MAX(o.amount) OVER (ORDER BY o.order_date) AS running_max
    FROM orders o
    WHERE o.status = 'completed'
)
SELECT 
    order_date AS "تاريخ الطلب",
    amount AS "المبلغ",
    ROUND(avg_3day, 2) AS "متوسط 3 أيام",
    ROUND(avg_5day, 2) AS "متوسط 5 أيام",
    ROUND(cumulative_avg, 2) AS "المتوسط التراكمي",
    running_max AS "الحد الأقصى التراكمي"
FROM order_details
ORDER BY order_date;

-- استعلام تكراري للتسلسل الهرمي للمنظمة
WITH RECURSIVE org_hierarchy AS (
    -- المرسلي: المديرين من المستوى الأعلى (موظفون بلا مدير)
    SELECT 
        e.id,
        e.name,
        e.position,
        e.manager_id,
        0 AS level,
        e.name AS path,
        e.salary
    FROM employees e
    WHERE e.manager_id IS NULL
    
    UNION ALL
    
    -- الجزء التكراري: إيجاد المرؤوسين
    SELECT 
        e.id,
        e.name,
        e.position,
        e.manager_id,
        oh.level + 1,
        oh.path || ' -> ' || e.name,
        e.salary
    FROM employees e
    JOIN org_hierarchy oh ON e.manager_id = oh.id
)
SELECT 
    CASE level
        WHEN 0 THEN ''
        WHEN 1 THEN '├─ '
        WHEN 2 THEN '│  ├─ '
        ELSE '│  │  ├─ '
    END || name AS "المخطط التنظيمي",
    position AS "المنصب",
    level AS "المستوى",
    path AS "مسار الإبلاغ",
    salary AS "الراتب"
FROM org_hierarchy
ORDER BY path;

-- إحصائيات التسلسل الهرمي وتوزيع الرواتب لكل قسم
WITH dept_stats AS (
    SELECT 
        d.id,
        d.department_name,
        COUNT(e.id) AS employee_count,
        COALESCE(SUM(e.salary), 0) AS total_salary,
        COALESCE(AVG(e.salary), 0) AS avg_salary,
        COALESCE(MAX(e.salary), 0) AS max_salary,
        COALESCE(MIN(e.salary), 0) AS min_salary
    FROM departments d
    LEFT JOIN employees e ON d.id = e.department_id
    GROUP BY d.id, d.department_name
)
SELECT 
    department_name AS "القسم",
    employee_count AS "الموظفون",
    ROUND(total_salary, 2) AS "إجمالي الراتب",
    ROUND(avg_salary, 2) AS "متوسط الراتب",
    max_salary AS "أعلى راتب",
    min_salary AS "أقل راتب",
    max_salary - min_salary AS "فجوة الراتب",
    -- توزيع الرواتب
    CASE 
        WHEN avg_salary > 15000 THEN 'راتب مرتفع'
        WHEN avg_salary > 10000 THEN 'راتب متوسط'
        ELSE 'راتب أساسي'
    END AS "مستوى الراتب"
FROM dept_stats
ORDER BY total_salary DESC;

-- معالجة الطلبات دفعة واحدة باستخدام المعاملات
BEGIN TRANSACTION;

-- نقطة حفظ: للتراجع بسهولة
SAVEPOINT before_process;

-- 1. تحديث مخزون المنتجات للطلبات المكتملة
UPDATE products 
SET stock_quantity = stock_quantity - (
    SELECT COALESCE(SUM(o.quantity), 0)
    FROM orders o
    WHERE o.product_id = products.id
    AND o.status = 'completed'
    AND o.order_date >= '2024-01-01'
)
WHERE id IN (
    SELECT DISTINCT product_id 
    FROM orders 
    WHERE status = 'completed' 
    AND order_date >= '2024-01-01'
);

-- 2. التحقق من عدم وجود مخزون سالب
SELECT 
    CASE 
        WHEN MIN(stock_quantity) < 0 THEN 'خطأ'
        ELSE 'حسناً'
    END AS فحص_المخزون
FROM products;

-- 3. إذا اجتاز فحص المخزون، إنشاء تقرير شهري
INSERT INTO monthly_report (month, total_revenue, total_orders)
SELECT 
    strftime('%Y-%m', order_date),
    SUM(amount),
    COUNT(*)
FROM orders
WHERE status = 'completed'
GROUP BY strftime('%Y-%m', order_date);

-- الالتزام بالمعاملة
COMMIT;

-- عرض النتائج
SELECT * FROM products ORDER BY id;

-- تحليل شامل لقيمة العميل
WITH customer_analysis AS (
    SELECT 
        customer_name,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spent,
        AVG(amount) AS avg_order_value,
        MIN(order_date) AS first_order,
        MAX(order_date) AS last_order,
        -- حساب عمر العميل (أيام)
        julianday(MAX(order_date)) - julianday(MIN(order_date)) AS customer_lifetime,
        -- الأيام منذ آخر عملية شراء
        julianday('now') - julianday(MAX(order_date)) AS days_since_last_order
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_name
),
customer_rfm AS (
    SELECT 
        *,
        -- درجة R: تقييم الحدوث (1-5، 5 = الأحدث)
        CASE 
            WHEN days_since_last_order <= 30 THEN 5
            WHEN days_since_last_order <= 60 THEN 4
            WHEN days_since_last_order <= 90 THEN 3
            WHEN days_since_last_order <= 180 THEN 2
            ELSE 1
        END AS r_score,
        -- درجة F: تقييم التكرار
        CASE 
            WHEN order_count >= 5 THEN 5
            WHEN order_count >= 3 THEN 4
            WHEN order_count >= 2 THEN 3
            WHEN order_count >= 1 THEN 2
            ELSE 1
        END AS f_score,
        -- درجة M: تقييم القيمة المالية
        NTILE(5) OVER (ORDER BY total_spent) AS m_score
    FROM customer_analysis
)
SELECT 
    customer_name AS "العميل",
    order_count AS "الطلبات",
    ROUND(total_spent, 2) AS "إجمالي الإنفاق",
    ROUND(avg_order_value, 2) AS "متوسط قيمة الطلب",
    first_order AS "أول طلب",
    last_order AS "آخر طلب",
    r_score AS "درجة R",
    f_score AS "درجة F",
    m_score AS "درجة M",
    r_score + f_score + m_score AS "إجمالي RFM",
    CASE 
        WHEN r_score + f_score + m_score >= 12 THEN 'عميل مميز'
        WHEN r_score + f_score + m_score >= 9 THEN 'عميل مهم'
        WHEN r_score + f_score + m_score >= 6 THEN 'عميل عادي'
        ELSE 'عميل منخفض القيمة'
    END AS "فئة العميل"
FROM customer_rfm
ORDER BY r_score + f_score + m_score DESC;

-- تقرير تحليل شرائح الرواتب
WITH salary_bands AS (
    SELECT 
        e.id,
        e.name,
        d.department_name,
        e.salary,
        e.hire_date,
        -- حساب سنوات العمل
        (julianday('now') - julianday(e.hire_date)) / 365.25 AS years_employed,
        -- تصنيف شريحة الراتب
        CASE 
            WHEN e.salary >= 15000 THEN 'شريحة A (15000+)'
            WHEN e.salary >= 12000 THEN 'شريحة B (12000-14999)'
            WHEN e.salary >= 8000 THEN 'شريحة C (8000-11999)'
            WHEN e.salary >= 5000 THEN 'شريحة D (5000-7999)'
            ELSE 'شريحة E (<5000)'
        END AS salary_band
    FROM employees e
    JOIN departments d ON e.department_id = d.id
)
SELECT 
    salary_band AS "شريحة الراتب",
    COUNT(*) AS "الموظفون",
    ROUND(AVG(salary), 2) AS "متوسط الراتب",
    ROUND(AVG(years_employed), 1) AS "متوسط الأقدمية",
    MIN(salary) AS "أقل راتب",
    MAX(salary) AS "أعلى راتب",
    GROUP_CONCAT(name, ', ') AS "الموظفون"
FROM salary_bands
GROUP BY salary_band
ORDER BY salary_band;