PostgreSQL: إنشاء وتعديل الجداول في PostgreSQL

-- إنشاء جدول المستخدمين مع تفرد البريد الإلكتروني وطابع زمني تلقائي
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    password_hash CHAR(60) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- إنشاء جدول المنتجات مع فحص السعر > 0
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    attributes JSONB DEFAULT '{}'::jsonb
);

-- إنشاء جدول الطلبات مع مفاتيح خارجية وفحص الحالة
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name data_type [column_constraint ...],
    ...
    [, table_constraint ...]
);

-- مفتاح أساسي مركب
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

-- مفتاح خارجي مسمى مع تتالي
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    content TEXT,
    CONSTRAINT fk_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE SET NULL
);

-- مفتاح أساسي أحادي العمود (الأكثر شيوعاً)
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- مفتاح أساسي مركب (لجداول الوصل)
CREATE TABLE product_tags (
    product_id INTEGER REFERENCES products(id),
    tag_id INTEGER REFERENCES tags(id),
    PRIMARY KEY (product_id, tag_id)
);

CREATE TABLE

-- عناصر الطلب: حذف الطلب -> حذف جميع عناصره (CASCADE)
-- الم��تج: حذف المنتج -> تعيين product_id إلى NULL (SET NULL)
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id) ON DELETE SET NULL,
    quantity INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE

-- السعر يجب أن يكون موجباً، الخصم لا يمكن أن يتجاوز السعر
CREATE TABLE promotions (
    id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(id),
    discount_price DECIMAL(10, 2) CHECK (discount_price > 0),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    -- CHECK في PG يمكنها الإشارة إلى أعمدة أخرى في نفس الصف!
    CHECK (end_date > start_date)
    -- ملاحظة: CHECK لا يمكنها الإشارة إلى استعلام فرعي عبر الجداول؛ التحقق عبر الجداول يحتاج مشغل
);

CREATE TABLE

-- إضافة عمود جديد
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- إضافة مع قيمة افتراضية
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true;

-- تغيير نوع العمود (يحتاج USING للأنواع غير المتوافقة)
ALTER TABLE users ALTER COLUMN phone TYPE TEXT;

-- إعادة تسمية عمود
ALTER TABLE users RENAME COLUMN phone TO phone_number;

-- تعيين قيمة افتراضية
ALTER TABLE users ALTER COLUMN is_active SET DEFAULT true;

-- إضافة قيد CHECK
ALTER TABLE products ADD CONSTRAINT price_reasonable
    CHECK (price < 100000);

-- حذف عمود
ALTER TABLE users DROP COLUMN IF EXISTS phone_number;

-- إعادة تسمية جدول
ALTER TABLE users RENAME TO customers;

-- SQL statement executed successfully

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

-- حذف متتالي (يحذف أيضاً الكائنات التابعة مثل طرق العرض)
DROP TABLE IF EXISTS users CASCADE;

-- SQL statement executed successfully

# سرد جميع الجداول في قاعدة البيانات الحالية
\dt

# عرض هيكل جدول users
\d users

                                        Table "public.users"
   Column    |          Type          | Collation | Nullable |              Default
-------------+------------------------+-----------+----------+-----------------------------------
 id          | integer                |           | not null | nextval('users_id_seq'::regclass)
 email       | character varying(255) |           | not null |
 name        | character varying(100) |           | not null |
 password_hash | character(60)        |           | not null |
 created_at  | timestamp with time zone |         |          | now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)

-- ============================================
-- مثال كامل: الجداول الأساسية للتجارة الإلكترونية
-- المستخدمون، المنتجات، الطلبات مع القيود المناسبة
-- ============================================

-- 1. جدول المستخدمين
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    password_hash CHAR(60) NOT NULL,
    role VARCHAR(20) DEFAULT 'customer'
        CHECK (role IN ('customer', 'admin', 'manager')),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. جدول المنتجات
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category VARCHAR(50),
    attributes JSONB DEFAULT '{}'::jsonb,
    is_available BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 3. جدول الطلبات مع مفاتيح خارجية
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    total_amount DECIMAL(12, 2) DEFAULT 0 CHECK (total_amount >= 0),
    shipping_address TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 4. جدول عناصر الطلب (جدول وصل)
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price > 0),
    subtotal DECIMAL(12, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

-- 5. إدراج بيانات نموذجية
INSERT INTO users (email, name, password_hash) VALUES
    ('alice@example.com', 'Alice', '$2a$12$dummyhashforalice1234567890abcdefghijklmnopqr'),
    ('bob@example.com', 'Bob', '$2a$12$dummyhashforbob1234567890abcdefghijklmnopqrstuv');

INSERT INTO products (name, price, stock, category, attributes) VALUES
    ('Running Shoes', 89.99, 150, 'Footwear', '{"color": "red", "size": 42}'::jsonb),
    ('Laptop Backpack', 49.99, 300, 'Accessories', '{"color": "black", "material": "nylon"}'::jsonb);

-- 6. التحقق من هيكل الجدول
\d users
\d products
\d orders
\d order_items