CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

FOREIGN KEY (department_id) REFERENCES departments(department_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    salary DECIMAL(10,2) CHECK (salary > 0),
    age INT CHECK (age >= 18 AND age <= 65),
    gender CHAR(1) CHECK (gender IN ('M', 'F'))
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'active',
    hire_date DATE DEFAULT CURRENT_DATE,
    salary DECIMAL(10,2) DEFAULT 5000.00
);

-- تحديد القيود عند إنشاء جدول
CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype NOT NULL,
    column3 datatype UNIQUE,
    column4 datatype CHECK (condition),
    column5 datatype DEFAULT value,
    column6 datatype,
    FOREIGN KEY (column6) REFERENCES other_table(column)
);

-- إضافة قيد إلى جدول موجود
ALTER TABLE table_name ADD CONSTRAINT constraint_name
    UNIQUE (column_name);

ALTER TABLE table_name ADD CONSTRAINT constraint_name
    CHECK (condition);

-- حذف قيد
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

CREATE TABLE employees (
    employee_id   INT           PRIMARY KEY,
    first_name    VARCHAR(50)   NOT NULL,
    last_name     VARCHAR(50)   NOT NULL,
    email         VARCHAR(100)  UNIQUE NOT NULL,
    phone         VARCHAR(20),
    hire_date     DATE          NOT NULL DEFAULT CURRENT_DATE,
    salary        DECIMAL(10,2) NOT NULL CHECK (salary > 0),
    department_id INT,
    CONSTRAINT fk_emp_dept FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
        ON DELETE SET NULL
);

-- إدراج بيانات اختبار
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department_id)
VALUES (1, 'John', 'Doe', 'johndoe@company.com', '2026-01-15', 8500.00, 1);

-- البيانات التالية ستفشل:
-- INSERT ... salary = -100    -- ❌ قيد CHECK
-- INSERT ... first_name NULL  -- ❌ قيد NOT NULL
-- INSERT ... duplicate email  -- ❌ قيد UNIQUE

-- إضافة قيد CHECK: الراتب يجب ألا يتجاوز 500000
ALTER TABLE employees ADD CONSTRAINT chk_salary_max
    CHECK (salary <= 500000);

-- إضافة قيد UNIQUE: رقم الهاتف يجب أن يكون فريدًا
ALTER TABLE employees ADD CONSTRAINT uq_emp_phone
    UNIQUE (phone);

-- حذف قيد
ALTER TABLE employees DROP CONSTRAINT chk_salary_max;

-- إضافة قيد مفتاح خارجي
ALTER TABLE employees ADD CONSTRAINT fk_emp_dept
    FOREIGN KEY (department_id) REFERENCES departments(department_id);

-- جدول المنتجات
CREATE TABLE products (
    product_id   INT           PRIMARY KEY,
    product_name VARCHAR(100)  NOT NULL,
    price        DECIMAL(10,2) NOT NULL CHECK (price > 0),
    stock        INT           NOT NULL DEFAULT 0 CHECK (stock >= 0),
    status       VARCHAR(20)   DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'discontinued'))
);

-- جدول الطلبات
CREATE TABLE orders (
    order_id     INT           PRIMARY KEY,
    customer_id  INT           NOT NULL,
    order_date   DATE          NOT NULL DEFAULT CURRENT_DATE,
    total_amount DECIMAL(12,2) CHECK (total_amount > 0),
    status       VARCHAR(20)   DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'shipped', 'cancelled')),
    CONSTRAINT fk_order_customer FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

-- جدول عناصر الطلب (مفتاح أساسي مركب)
CREATE TABLE order_items (
    order_id   INT,
    product_id INT,
    quantity   INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price > 0),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- الجدول الحالي يفتقر إلى القيود؛ أضفها خطوة بخطوة

-- 1. تأكد من صيغة البريد الإلكتروني
ALTER TABLE employees ADD CONSTRAINT chk_email_format
    CHECK (email LIKE '%@%.%');

-- 2. تأكد من معقولية تاريخ التوظيف
ALTER TABLE employees ADD CONSTRAINT chk_hire_date
    CHECK (hire_date >= '2000-01-01' AND hire_date <= CURRENT_DATE);

-- 3. تأكد من علاقة المفتاح الخارجي للقسم
ALTER TABLE employees ADD CONSTRAINT fk_emp_dept
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
    ON DELETE SET NULL;

-- 4. عرض جميع القيود على جدول (SQL Server)
SELECT name, type_desc
FROM sys.objects
WHERE parent_object_id = OBJECT_ID('employees')
  AND type IN ('C', 'F', 'UQ', 'PK');