-- إنشاء عرض: عرض معلومات الموظفين
CREATE VIEW v_employee_info AS
SELECT 
    e.employee_id,
    CONCAT(e.first_name, e.last_name) AS name,
    e.salary,
    d.department_name AS department,
    d.city
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- الاستعلام عن العرض
SELECT * FROM v_employee_info WHERE salary > 10000;

CREATE VIEW view_name AS
SELECT statement;

-- إنشاء عرض إحصائيات الأقسام
CREATE VIEW v_department_stats AS
SELECT 
    d.department_id,
    d.department_name AS department,
    COUNT(e.employee_id) AS employee_count,
    AVG(e.salary) AS avg_salary,
    SUM(e.salary) AS total_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

-- الطريقة 1: CREATE OR REPLACE (مُوصى بها)
CREATE OR REPLACE VIEW v_employee_info AS
SELECT 
    e.employee_id,
    CONCAT(e.first_name, e.last_name) AS name,
    e.salary,
    e.hire_date,
    d.department_name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- الطريقة 2: ALTER VIEW
ALTER VIEW v_employee_info AS
SELECT employee_id, first_name, last_name, salary FROM employees;

DROP VIEW IF EXISTS v_employee_info;

-- بدون فهرس: مسح الجدول بالكامل (بحث صف بصف)
SELECT * FROM employees WHERE last_name = 'Zhang';

-- مع فهرس: بحث مباشر (عبر الفهرس)
CREATE INDEX idx_last_name ON employees(last_name);
SELECT * FROM employees WHERE last_name = 'Zhang';

-- فهرس عادي (يسمح بقيم مكررة)
CREATE INDEX idx_column ON table_name(column_name);

-- فهرس فريد (لا يسمح بقيم مكررة)
CREATE UNIQUE INDEX idx_column ON table_name(column_name);

-- فهرس نصي كامل (للبحث النصي الكامل)
CREATE FULLTEXT INDEX idx_column ON table_name(column_name);

-- فهرس مركب (تركيبة أعمدة متعددة)
CREATE INDEX idx_columns ON table_name(column1, column2);

-- صيغة MySQL
DROP INDEX idx_column ON table_name;

-- الصيغة المعيارية
ALTER TABLE table_name DROP INDEX idx_column;

-- إنشاء فهرس مركب
CREATE INDEX idx_name_age ON employees(last_name, first_name, salary);

-- ✅ يمكن استخدام الفهرس (يطابق العمود الأيسر)
SELECT * FROM employees WHERE last_name = 'Zhang';
SELECT * FROM employees WHERE last_name = 'Zhang' AND first_name = 'Wei';

-- ❌ لا يمكن استخدام الفهرس (يتخطى العمود الأيسر)
SELECT * FROM employees WHERE first_name = 'Wei';
SELECT * FROM employees WHERE salary > 10000;

-- إنشاء عرض
CREATE [OR REPLACE] VIEW view_name AS SELECT statement;

-- الاستعلام عن العرض (مثل الجدول العادي)
SELECT * FROM view_name WHERE condition;

-- حذف عرض
DROP VIEW [IF EXISTS] view_name;

-- إنشاء فهرس
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name(column1, column2, ...);

-- حذف فهرس
DROP INDEX index_name ON table_name;

-- عرض فهارس جدول
SHOW INDEX FROM table_name;

-- إنشاء عرض تفاصيل الطلبات
CREATE OR REPLACE VIEW v_order_detail AS
SELECT 
    o.order_id,
    CONCAT(e.first_name, e.last_name) AS owner,
    d.department_name AS department,
    o.order_date,
    o.total_amount AS amount,
    o.status
FROM orders o
LEFT JOIN employees e ON o.employee_id = e.employee_id
LEFT JOIN departments d ON e.department_id = d.department_id;

-- الاستعلام باستخدام العرض
SELECT * FROM v_order_detail WHERE status = 'completed';

order_id | owner      | department | order_date | amount   | status
---------+------------+------------+------------+----------+-----------
    1001 | WangQiang  | Sales      | 2026-05-10 | 2500.00  | completed
    1002 | WangQiang  | Sales      | 2026-05-15 | 1800.00  | completed
    1005 | ZhaoMin    | Sales      | 2026-06-10 | 4100.00  | completed

-- استخدام العرض لتجميع مبالغ الطلبات حسب القسم
SELECT department, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM v_order_detail
GROUP BY department;

-- عرض خطة تنفيذ الاستعلام الحالي
EXPLAIN SELECT * FROM employees WHERE last_name = 'Zhang';

+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+

-- إنشاء فهرس
CREATE INDEX idx_last_name ON employees(last_name);

-- عرض خطة التنفيذ مرة أخرى
EXPLAIN SELECT * FROM employees WHERE last_name = 'Zhang';

+----+-------------+-----------+------+---------------+---------------+---------+-------+------+-------+
| id | select_type | table     | type | possible_keys | key           | key_len | ref   | rows | Extra |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+-------+
|  1 | SIMPLE      | employees | ref  | idx_last_name | idx_last_name | 152     | const |    1 |       |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+-------+

-- إنشاء فهرس مركب
CREATE INDEX idx_status_date ON orders(status, order_date);

-- استعلام يمكنه استخدام الفهرس
EXPLAIN SELECT * FROM orders WHERE status = 'completed' AND order_date > '2026-06-01';

-- عرض جميع الفهارس على جدول
SHOW INDEX FROM employees;

-- إنشاء عرض يحتوي فقط على معلومات غير حساسة
CREATE OR REPLACE VIEW v_employee_public AS
SELECT 
    employee_id,
    CONCAT(first_name, last_name) AS name,
    d.department_name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- المستخدمون العاديون يمكنهم الوصول إلى هذا العرض فقط، وليس الراتب أو معلومات حساسة أخرى
SELECT * FROM v_employee_public;

-- إنشاء عرض تقرير المبيعات الشهري
CREATE OR REPLACE VIEW v_monthly_sales AS
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales,
    AVG(total_amount) AS avg_order_amount,
    COUNT(DISTINCT customer_id) AS customer_count
FROM orders
WHERE status != 'cancelled'
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

-- الاستعلام عن التقرير
SELECT * FROM v_monthly_sales ORDER BY month;

month   | order_count | total_sales | avg_order_amount | customer_count
--------+-------------+-------------+------------------+---------------
2026-05 |           2 |     4300.00 |          2150.00 |              2
2026-06 |           3 |     8250.00 |          2750.00 |              3