العروض (Views)

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

-- الجدول الأساسي
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    salary DECIMAL(10, 2),
    department VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE
);

-- إنشاء عرض للموظفين النشطين فقط
CREATE VIEW active_employees AS
SELECT 
    employee_id,
    first_name,
    last_name,
    email,
    department
FROM employees
WHERE is_active = TRUE;

-- استعلام بسيط من العرض
SELECT * FROM active_employees;

-- استعلام مع شرط إضافي
SELECT * FROM active_employees WHERE department = 'IT';

-- استعلام مع ترتيب
SELECT * FROM active_employees ORDER BY last_name;

-- الجداول الأساسية
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100),
    location VARCHAR(100)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2),
    hire_date DATE,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- عرض يجمع معلومات الموظفين مع أقسامهم
CREATE VIEW employee_details AS
SELECT 
    e.employee_id,
    CONCAT(e.first_name, ' ', e.last_name) AS full_name,
    e.salary,
    e.hire_date,
    d.department_name,
    d.location,
    YEAR(CURRENT_DATE) - YEAR(e.hire_date) AS years_of_service
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- بدلاً من كتابة JOIN في كل مرة
SELECT * FROM employee_details WHERE department_name = 'IT';

-- الموظفون الأقدم
SELECT * FROM employee_details WHERE years_of_service > 5;

-- الموظفون في الرياض
SELECT * FROM employee_details WHERE location = 'الرياض';

-- عرض يعرض إحصائيات كل قسم
CREATE VIEW department_statistics AS
SELECT 
    d.department_name,
    COUNT(e.employee_id) AS total_employees,
    AVG(e.salary) AS average_salary,
    MAX(e.salary) AS highest_salary,
    MIN(e.salary) AS lowest_salary,
    SUM(e.salary) AS total_payroll
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

-- عرض جميع الإحصائيات
SELECT * FROM department_statistics;

-- الأقسام ذات الرواتب الأعلى
SELECT * FROM department_statistics ORDER BY average_salary DESC;

-- الأقسام الكبيرة (أكثر من 10 موظفين)
SELECT * FROM department_statistics WHERE total_employees > 10;

-- عرض للمديرين (بدون رواتب)
CREATE VIEW employees_for_managers AS
SELECT 
    employee_id,
    first_name,
    last_name,
    email,
    department,
    hire_date
FROM employees;

-- منح صلاحية للمديرين على العرض فقط
GRANT SELECT ON employees_for_managers TO manager_role;
-- لا يمكنهم رؤية جدول employees الأساسي الذي يحتوي على الرواتب

-- في MySQL و PostgreSQL
ALTER VIEW active_employees AS
SELECT 
    employee_id,
    first_name,
    last_name,
    email,
    department,
    hire_date  -- إضافة عمود جديد
FROM employees
WHERE is_active = TRUE;

-- إذا كان العرض موجوداً، يتم استبداله. وإلا، يتم إنشاؤه
CREATE OR REPLACE VIEW active_employees AS
SELECT 
    employee_id,
    CONCAT(first_name, ' ', last_name) AS full_name,
    email,
    department
FROM employees
WHERE is_active = TRUE;

-- حذف عرض
DROP VIEW active_employees;

-- حذف آمن (لا يعطي خطأ إذا لم يكن موجوداً)
DROP VIEW IF EXISTS active_employees;

-- حذف عدة عروض
DROP VIEW IF EXISTS view1, view2, view3;

-- في MySQL
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- أو
SELECT TABLE_NAME 
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'your_database_name';

-- عرض تعريف العرض
SHOW CREATE VIEW active_employees;

-- عرض بسيط قابل للتحديث
CREATE VIEW it_employees AS
SELECT 
    employee_id,
    first_name,
    last_name,
    email,
    salary
FROM employees
WHERE department = 'IT';

-- يمكنك التحديث من خلال العرض
UPDATE it_employees 
SET salary = salary * 1.10 
WHERE employee_id = 123;

-- سيتم تحديث جدول employees الأساسي

-- يمكنك أيضاً الإدراج
INSERT INTO it_employees (first_name, last_name, email, salary)
VALUES ('أحمد', 'محمد', 'ahmed@company.com', 5000);

-- ويمكنك الحذف
DELETE FROM it_employees WHERE employee_id = 456;

-- عرض مع حماية
CREATE VIEW it_employees AS
SELECT 
    employee_id,
    first_name,
    last_name,
    email,
    department,
    salary
FROM employees
WHERE department = 'IT'
WITH CHECK OPTION;

-- الآن، هذا سيفشل لأنه يحاول تغيير القسم إلى غير IT
UPDATE it_employees SET department = 'HR' WHERE employee_id = 123;
-- Error: CHECK OPTION failed

-- إنشاء عرض مادي (PostgreSQL)
CREATE MATERIALIZED VIEW sales_summary AS
SELECT 
    DATE(order_date) AS sale_date,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS total_sales,
    AVG(total_amount) AS average_order
FROM orders
GROUP BY DATE(order_date);

-- الاستعلام من العرض المادي (سريع جداً)
SELECT * FROM sales_summary WHERE sale_date >= '2024-01-01';

-- تحديث العرض المادي (يدوياً)
REFRESH MATERIALIZED VIEW sales_summary;

-- تحديث متزامن (لا يقفل العرض)
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    city VARCHAR(50),
    country VARCHAR(50)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(200),
    category VARCHAR(50),
    price DECIMAL(10, 2),
    stock_quantity INT
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATETIME,
    status VARCHAR(20),
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 1. عرض تفاصيل الطلبات الكاملة
CREATE VIEW order_details AS
SELECT 
    o.order_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    c.email,
    c.city,
    o.order_date,
    o.status,
    o.total_amount,
    COUNT(oi.order_item_id) AS total_items
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, c.first_name, c.last_name, c.email, c.city, o.order_date, o.status, o.total_amount;

-- 2. عرض المنتجات الأكثر مبيعاً
CREATE VIEW top_selling_products AS
SELECT 
    p.product_id,
    p.product_name,
    p.category,
    p.price,
    SUM(oi.quantity) AS total_sold,
    COUNT(DISTINCT oi.order_id) AS number_of_orders,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.category, p.price
ORDER BY total_sold DESC;

-- 3. عرض إحصائيات العملاء
CREATE VIEW customer_statistics AS
SELECT 
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    c.email,
    c.city,
    COUNT(o.order_id) AS total_orders,
    SUM(o.total_amount) AS total_spent,
    AVG(o.total_amount) AS average_order_value,
    MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name, c.email, c.city;

-- 4. عرض المنتجات التي تحتاج إعادة تخزين
CREATE VIEW low_stock_products AS
SELECT 
    product_id,
    product_name,
    category,
    stock_quantity,
    price
FROM products
WHERE stock_quantity < 10
ORDER BY stock_quantity ASC;

-- عرض جميع الطلبات المعلقة
SELECT * FROM order_details WHERE status = 'pending';

-- أفضل 10 منتجات مبيعاً
SELECT * FROM top_selling_products LIMIT 10;

-- العملاء الأكثر إنفاقاً
SELECT * FROM customer_statistics ORDER BY total_spent DESC LIMIT 20;

-- المنتجات التي تحتاج طلب فوري
SELECT * FROM low_stock_products WHERE stock_quantity < 5;