-- جدول الأقسام
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-05-10', 2500.00, 'completed');
INSERT INTO orders VALUES (1002, 102, 3, '2026-05-15', 1800.00, 'completed');
INSERT INTO orders VALUES (1003, 103, 4, '2026-06-01', 3200.00, 'shipped');
INSERT INTO orders VALUES (1004, 101, 1, '2026-06-05', 950.00, 'pending');
INSERT INTO orders VALUES (1005, 104, 4, '2026-06-10', 4100.00, 'completed');
INSERT INTO orders VALUES (1006, 105, 3, '2026-06-15', 2800.00, 'cancelled');

-- بيانات المنتجات
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);

SELECT
    e.employee_id,
    CONCAT(e.first_name, e.last_name) AS name,
    d.department_name AS department,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_sales
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_id | name     | department | order_count | total_sales
-----------+----------+------------+-------------+------------
         4 | Zhao Min | Sales      |           2 |     7300.00
         3 | Wang Qiang| Sales      |           3 |     7100.00
         1 | Zhang Wei | Technology |           1 |      950.00
         2 | Li Na    | Technology |           0 |        0.00
         5 | Liu Yang | Marketing  |           0 |        0.00
         6 | Chen Jing| NULL       |           0 |        0.00

SELECT
    d.department_name AS department,
    d.city AS city,
    COUNT(DISTINCT e.employee_id) AS employee_count,
    COUNT(o.order_id) AS total_orders,
    COALESCE(SUM(o.total_amount), 0) AS total_sales,
    RANK() OVER (ORDER BY COALESCE(SUM(o.total_amount), 0) DESC) AS ranking
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
GROUP BY d.department_id, d.department_name, d.city
ORDER BY total_sales DESC;

SELECT
    e.employee_id,
    CONCAT(e.first_name, e.last_name) AS name,
    e.salary AS salary,
    o.order_id,
    o.order_date AS order_date,
    o.total_amount AS order_amount,
    o.status AS order_status
FROM employees e
INNER JOIN orders o ON e.employee_id = o.employee_id
WHERE e.salary > (
    SELECT AVG(salary) FROM employees
)
ORDER BY e.salary DESC, o.order_date;

SELECT
    o.order_id,
    CONCAT(e.first_name, e.last_name) AS employee_name,
    d.department_name AS department,
    o.total_amount AS order_amount
FROM orders o
INNER JOIN employees e ON o.employee_id = e.employee_id
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id IS NOT NULL
  AND e.department_id NOT IN (
      SELECT DISTINCT department_id FROM products WHERE department_id IS NOT NULL
  );

-- الموظفون الذين لديهم طلبات مكتملة
SELECT
    CONCAT(first_name, last_name) AS name,
    'Employee' AS type,
    CAST(salary AS VARCHAR) AS details
FROM employees
WHERE employee_id IN (
    SELECT DISTINCT employee_id FROM orders WHERE status = 'completed'
)
UNION ALL
-- جميع الأقسام
SELECT
    department_name,
    'Department',
    CONCAT('Budget: ', CAST(budget AS VARCHAR))
FROM departments
ORDER BY type, name;

SELECT
    CONCAT(e.first_name, e.last_name) AS employee_name,
    d.department_name AS department,
    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,
    COUNT(o.order_id) AS total_orders
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
WHERE e.employee_id IN (
    SELECT DISTINCT employee_id FROM orders
)
GROUP BY e.employee_id, e.first_name, e.last_name, d.department_name
ORDER BY total_orders DESC;