-- جدول الأقسام
CREATE TABLE departments (
    department_id   INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL,
    city            VARCHAR(50),
    budget          DECIMAL(12,2)
);

-- جدول الموظفين
CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL,
    email         VARCHAR(100),
    salary        DECIMAL(10,2),
    department_id INT,
    hire_date     DATE,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- جدول الطلبات
CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,
    employee_id  INT,
    order_date   DATE,
    total_amount DECIMAL(12,2),
    status       VARCHAR(20),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

-- جدول المنتجات
CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price        DECIMAL(10,2),
    stock        INT,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- بيانات الأقسام
INSERT INTO departments VALUES (1, 'Technology', 'Beijing', 500000);
INSERT INTO departments VALUES (2, 'Sales', 'Shanghai', 300000);
INSERT INTO departments VALUES (3, 'Marketing', 'Guangzhou', 200000);
INSERT INTO departments VALUES (4, 'Finance', 'Shenzhen', 150000);

-- بيانات الموظفين
INSERT INTO employees VALUES (1, 'Zhang', 'Wei', 'zhangwei@co.com', 12000, 1, '2023-03-15');
INSERT INTO employees VALUES (2, 'Li', 'Na', 'lina@co.com', 9500, 1, '2023-06-20');
INSERT INTO employees VALUES (3, 'Wang', 'Qiang', 'wangqiang@co.com', 8000, 2, '2022-11-10');
INSERT INTO employees VALUES (4, 'Zhao', 'Min', 'zhaomin@co.com', 11000, 2, '2024-01-05');
INSERT INTO employees VALUES (5, 'Liu', 'Yang', 'liuyang@co.com', 7500, 3, '2024-04-18');
INSERT INTO employees VALUES (6, 'Chen', 'Jing', 'chenjing@co.com', 9000, NULL, '2025-02-28');

-- بيانات الطلبات
INSERT INTO orders VALUES (1001, 101, 3, '2026-01-10', 2500.00, 'completed');
INSERT INTO orders VALUES (1002, 102, 3, '2026-02-15', 1800.00, 'completed');
INSERT INTO orders VALUES (1003, 103, 4, '2026-02-20', 3200.00, 'shipped');
INSERT INTO orders VALUES (1004, 101, 1, '2026-03-05', 950.00, 'pending');
INSERT INTO orders VALUES (1005, 104, 4, '2026-03-10', 4100.00, 'completed');
INSERT INTO orders VALUES (1006, 105, 3, '2026-04-01', 2800.00, 'cancelled');
INSERT INTO orders VALUES (1007, 101, 1, '2026-04-15', 5600.00, 'completed');
INSERT INTO orders VALUES (1008, 106, 4, '2026-05-02', 1500.00, 'completed');
INSERT INTO orders VALUES (1009, 102, 3, '2026-05-20', 3800.00, 'shipped');
INSERT INTO orders VALUES (1010, 107, 1, '2026-06-01', 2200.00, 'pending');
INSERT INTO orders VALUES (1011, 103, 4, '2026-06-10', 6300.00, 'completed');
INSERT INTO orders VALUES (1012, 108, 3, '2026-06-15', 1900.00, 'completed');

-- بيانات المنتجات
INSERT INTO products VALUES (1, 'Laptop', 6999.00, 50, 1);
INSERT INTO products VALUES (2, 'Wireless Mouse', 129.00, 200, 1);
INSERT INTO products VALUES (3, 'Office Desk', 899.00, 30, 2);
INSERT INTO products VALUES (4, 'Projector', 3500.00, 15, 3);
INSERT INTO products VALUES (5, 'Printer', 2200.00, 25, 1);
INSERT INTO products VALUES (6, 'Meeting Chair', 599.00, 40, 2);
INSERT INTO products VALUES (7, 'Whiteboard', 450.00, 60, 3);

SELECT 
    CONCAT(e.first_name, e.last_name) AS employee_name,
    d.department_name AS department,
    COUNT(o.order_id) AS total_orders,
    COALESCE(SUM(o.total_amount), 0) AS total_sales,
    COALESCE(ROUND(AVG(o.total_amount), 2), 0) AS avg_order_amount,
    SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) AS completed,
    SUM(CASE WHEN o.status = 'shipped'   THEN 1 ELSE 0 END) AS shipped,
    SUM(CASE WHEN o.status = 'pending'   THEN 1 ELSE 0 END) AS pending,
    SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
    CASE
        WHEN COALESCE(SUM(o.total_amount), 0) >= 10000 THEN 'S - متميز'
        WHEN COALESCE(SUM(o.total_amount), 0) >= 5000  THEN 'A - ممتاز'
        WHEN COALESCE(SUM(o.total_amount), 0) >= 2000  THEN 'B - جيد'
        WHEN COUNT(o.order_id) > 0                      THEN 'C - يحتاج تحسين'
        ELSE 'لا يوجد أداء'
    END AS performance_rating
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN orders o ON e.employee_id = o.employee_id
GROUP BY e.employee_id, e.first_name, e.last_name, d.department_name
ORDER BY total_sales DESC;

employee_name | department | total_orders | total_sales | avg_order_amount | completed | shipped | pending | cancelled | performance_rating
--------------+------------+--------------+-------------+------------------+-----------+---------+---------+-----------+-------------------
ZhaoMin       | Sales      |            4 |    15100.00 |          3775.00 |         3 |       1 |       0 |         0 | S - متميز
WangQiang     | Sales      |            4 |    10900.00 |          2725.00 |         2 |       1 |       0 |         1 | S - متميز
ZhangWei      | Technology |            3 |     8750.00 |          2916.67 |         1 |       0 |       2 |         0 | A - ممتاز
LiNa          | Technology |            0 |        0.00 |             0.00 |         0 |       0 |       0 |         0 | لا يوجد أداء
LiuYang       | Marketing  |            0 |        0.00 |             0.00 |         0 |       0 |       0 |         0 | لا يوجد أداء
ChenJing      | NULL       |            0 |        0.00 |             0.00 |         0 |       0 |       0 |         0 | لا يوجد أداء

SELECT 
    d.department_name AS department,
    CONCAT('Q', QUARTER(o.order_date)) AS quarter,
    COUNT(o.order_id) AS order_count,
    SUM(o.total_amount) AS quarterly_sales,
    ROUND(
        SUM(o.total_amount) / 
        SUM(SUM(o.total_amount)) OVER (PARTITION BY QUARTER(o.order_date)) * 100, 
    2) AS percentage,
    RANK() OVER (
        PARTITION BY QUARTER(o.order_date) 
        ORDER BY SUM(o.total_amount) DESC
    ) AS quarter_rank
FROM orders o
INNER JOIN employees e ON o.employee_id = e.employee_id
INNER JOIN departments d ON e.department_id = d.department_id
WHERE o.status != 'cancelled'
GROUP BY d.department_name, QUARTER(o.order_date)
ORDER BY quarter, quarterly_sales DESC;

department | quarter | order_count | quarterly_sales | percentage | quarter_rank
-----------+---------+-------------+-----------------+------------+-------------
Sales      | Q1      |           2 |         4300.00 |      69.35 |            1
Technology | Q1      |           1 |          950.00 |      15.32 |            2
Sales      | Q2      |           2 |         5600.00 |      26.54 |            1
Technology | Q2      |           1 |         5600.00 |      26.54 |            1
Marketing  | Q2      |           1 |         3800.00 |      18.01 |            3

WITH customer_stats AS (
    SELECT 
        customer_id,
        MAX(order_date) AS last_purchase_date,
        COUNT(order_id) AS purchase_count,
        SUM(total_amount) AS total_spend
    FROM orders
    WHERE status != 'cancelled'
    GROUP BY customer_id
)
SELECT 
    customer_id,
    last_purchase_date,
    purchase_count,
    total_spend,
    CASE
        WHEN purchase_count >= 3 AND total_spend >= 5000 THEN 'عميل VIP'
        WHEN purchase_count >= 2 AND total_spend >= 3000 THEN 'عميل رئيسي'
        WHEN purchase_count >= 2                       THEN 'عميل نشط'
        WHEN total_spend >= 2000                       THEN 'عميل محتمل'
        ELSE 'عميل عادي'
    END AS customer_tier
FROM customer_stats
ORDER BY total_spend DESC;

customer_id | last_purchase_date | purchase_count | total_spend | customer_tier
------------+-------------------+----------------+-------------+---------------
        103 | 2026-06-10        |              2 |     9500.00 | عميل VIP
        101 | 2026-04-15        |              2 |     3450.00 | عميل رئيسي
        104 | 2026-03-10        |              1 |     4100.00 | عميل محتمل
        102 | 2026-05-20        |              2 |     5600.00 | عميل VIP
        106 | 2026-05-02        |              1 |     1500.00 | عميل عادي
        105 | 2026-04-01        |              0 |        0.00 | عميل عادي
        107 | 2026-06-01        |              1 |     2200.00 | عميل محتمل
        108 | 2026-06-15        |              1 |     1900.00 | عميل عادي

SELECT 
    d.department_name AS department,
    COUNT(DISTINCT p.product_id) AS product_count,
    SUM(p.stock) AS total_stock,
    COALESCE(SUM(p.price * p.stock), 0) AS inventory_value,
    COUNT(DISTINCT o.order_id) AS related_orders,
    COALESCE(SUM(o.total_amount), 0) AS order_total,
    CASE 
        WHEN SUM(p.price * p.stock) > 0 
        THEN ROUND(COALESCE(SUM(o.total_amount), 0) / SUM(p.price * p.stock) * 100, 2)
        ELSE 0
    END AS turnover_rate_pct
FROM departments d
LEFT JOIN products p ON d.department_id = p.department_id
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
ORDER BY inventory_value DESC;

department | product_count | total_stock | inventory_value | related_orders | order_total | turnover_rate_pct
-----------+---------------+-------------+-----------------+----------------+-------------+------------------
Technology |             3 |         275 |       369945.00 |              4 |      8750.00 |             2.36
Sales      |             2 |          70 |        68930.00 |              8 |     26000.00 |            37.71
Marketing  |             2 |          75 |        86250.00 |              0 |         0.00 |             0.00

WITH emp_performance AS (
    SELECT 
        e.employee_id,
        CONCAT(e.first_name, e.last_name) AS name,
        d.department_name AS department,
        e.salary AS base_salary,
        COUNT(o.order_id) AS order_count,
        COALESCE(SUM(o.total_amount), 0) AS sales_amount,
        COALESCE(AVG(o.total_amount), 0) AS avg_order_amount
    FROM employees e
    LEFT JOIN departments d ON e.department_id = d.department_id
    LEFT JOIN orders o ON e.employee_id = o.employee_id AND o.status != 'cancelled'
    GROUP BY e.employee_id, e.first_name, e.last_name, d.department_name, e.salary
)
SELECT 
    name,
    department,
    base_salary,
    order_count,
    sales_amount,
    ROUND(avg_order_amount, 2) AS avg_order_amount,
    RANK() OVER (ORDER BY sales_amount DESC) AS company_rank,
    RANK() OVER (PARTITION BY department ORDER BY sales_amount DESC) AS dept_rank,
    ROUND(sales_amount - AVG(sales_amount) OVER (PARTITION BY department), 2) AS vs_dept_avg
FROM emp_performance
ORDER BY sales_amount DESC;

name      | department | base_salary | order_count | sales_amount | avg_order_amount | company_rank | dept_rank | vs_dept_avg
----------+------------+-------------+-------------+--------------+------------------+--------------+-----------+------------
ZhaoMin   | Sales      |    11000.00 |           4 |     15100.00 |          3775.00 |            1 |         1 |     3550.00
WangQiang | Sales      |     8000.00 |           3 |     10900.00 |          3633.33 |            2 |         2 |     -650.00
ZhangWei  | Technology |    12000.00 |           2 |      8750.00 |          4375.00 |            3 |         1 |     8750.00
LiNa      | Technology |     9500.00 |           0 |         0.00 |             0.00 |            4 |         2 |    -8750.00
LiuYang   | Marketing  |     7500.00 |           0 |         0.00 |             0.00 |            4 |         1 |        0.00
ChenJing  | NULL       |     9000.00 |           0 |         0.00 |             0.00 |            4 |         1 |        0.00