Database Stuff/DB_Creation_postgres.sql

/* ----------------------
   Enum/type definitions
   ---------------------- */
CREATE TYPE role_enum AS ENUM ('student','sheikh','admin');
CREATE TYPE gender_enum AS ENUM ('male','female');
CREATE TYPE quraan_level_enum AS ENUM ('beginner','intermediate','advanced');
CREATE TYPE specialization_enum AS ENUM ('tajweed','qiraat','tafseer','general');
CREATE TYPE live_status_enum AS ENUM ('online','offline','busy','away');
CREATE TYPE day_of_week_enum AS ENUM ('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday');
CREATE TYPE session_status_enum AS ENUM ('scheduled','completed','cancelled','missed');
CREATE TYPE payment_method_enum AS ENUM ('credit_card','paypal','vodafone_cash','wallet');
CREATE TYPE payment_status_enum AS ENUM ('pending','paid','failed','refunded');
CREATE TYPE transaction_type_enum AS ENUM ('deposit','withdraw','session_payment','refund','bonus');

/* ----------------------
   Trigger to update updatedAt columns
   ---------------------- */
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updatedAt = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

/* ----------------------
   Users table (was `user` in MySQL)
   ---------------------- */
CREATE TABLE users (
    userId INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(150) NOT NULL UNIQUE,
    passHash VARCHAR(255) NOT NULL,
    fullName VARCHAR(100) NOT NULL,
    isActive BOOLEAN NOT NULL DEFAULT TRUE,
    phone VARCHAR(20) UNIQUE,
    role role_enum NOT NULL,
    createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

/* ----------------------
   Student profile (1-to-1 mandatory)
   studentId references users.userId
   ---------------------- */
CREATE TABLE student_profile (
    studentId INT PRIMARY KEY REFERENCES users(userId)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    gender gender_enum NOT NULL,
    birthdate DATE NOT NULL,
    quraan_level quraan_level_enum NOT NULL
);

/* ----------------------
   Sheikh profile (1-to-1 mandatory)
   ---------------------- */
CREATE TABLE sheikh_profile (
    sheikhId INT PRIMARY KEY REFERENCES users(userId)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    bio TEXT NOT NULL,
    specialization specialization_enum NOT NULL,
    yearsOfExperience INT NOT NULL,
    certificatesURL VARCHAR(255),
    hourlyRate DECIMAL(10,2) NOT NULL,
    ratingAVG DECIMAL(3,2) DEFAULT 0,
    liveStatus live_status_enum NOT NULL DEFAULT 'offline',
    lastActiveAt TIMESTAMP
);

/* ----------------------
   Availability
   ---------------------- */
CREATE TABLE availability (
    availabilityId INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    dayOfWeek day_of_week_enum NOT NULL,
    startTime TIME NOT NULL,
    endTime TIME NOT NULL,
    sheikhId INT NOT NULL REFERENCES users(userId)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

/* ----------------------
   Recitations
   ---------------------- */
CREATE TABLE recitations (
    recitationId INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    surahStart VARCHAR(25) NOT NULL,
    ayahtStart INT NOT NULL,
    surahEnd VARCHAR(25) NOT NULL,
    ayahtEnd INT NOT NULL,
    mistakesReport JSONB NOT NULL,
    tajweedScore DECIMAL(4,2) NOT NULL,
    createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    studentId INT NOT NULL REFERENCES users(userId)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

/* ----------------------
   Session
   ---------------------- */
CREATE TABLE session (
    sessionId INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    scheduledStart TIMESTAMP NOT NULL,
    scheduledEnd TIMESTAMP NOT NULL,
    actualStart TIMESTAMP,
    actualEnd TIMESTAMP,
    meetingLink VARCHAR(255),
    status session_status_enum NOT NULL DEFAULT 'scheduled',
    sessionNotes TEXT,
    createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    studentId INT NOT NULL REFERENCES users(userId)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    sheikhId INT NOT NULL REFERENCES users(userId)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- trigger to auto-update updatedAt on session
CREATE TRIGGER trg_session_updated_at
BEFORE UPDATE ON session
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

/* ----------------------
   Payment (1-to-1 with session)
   Note: corrected typo "shiekhId" -> "sheikhId"
   ---------------------- */
CREATE TABLE payment (
    paymentId INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(10) NOT NULL,
    paymentMethod payment_method_enum NOT NULL,
    paymentStatus payment_status_enum NOT NULL DEFAULT 'pending',
    transaction_id VARCHAR(100) UNIQUE,
    paymentDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sheikhId INT NOT NULL REFERENCES users(userId)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    studentId INT NOT NULL REFERENCES users(userId)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    sessionId INT NOT NULL UNIQUE REFERENCES session(sessionId)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

/* ----------------------
   Review (1-to-1 with session)
   ---------------------- */
CREATE TABLE review (
    reviewId INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    rate INT NOT NULL CHECK (rate BETWEEN 1 AND 5),
    comment TEXT,
    createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sessionId INT NOT NULL UNIQUE REFERENCES session(sessionId)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    studentId INT NOT NULL REFERENCES users(userId)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

/* ----------------------
   Wallet (0–1 optional)
   ---------------------- */
CREATE TABLE wallet (
    walletId INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    balance DECIMAL(10,2) NOT NULL DEFAULT 0,
    createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    userId INT UNIQUE REFERENCES users(userId)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- trigger to auto-update updatedAt on wallet
CREATE TRIGGER trg_wallet_updated_at
BEFORE UPDATE ON wallet
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

/* ----------------------
   Wallet Transaction (1-to-many with wallet)
   ---------------------- */
CREATE TABLE wallet_transaction (
    transactionId INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    amount DECIMAL(10,2) NOT NULL,
    transactionType transaction_type_enum NOT NULL,
    createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    paymentId INT REFERENCES payment(paymentId)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    sessionId INT REFERENCES session(sessionId)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    walletId INT NOT NULL REFERENCES wallet(walletId)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

/* ----------------------
   End of schema
   ---------------------- */