RelationalDesign: create.sql

DROP TABLE IF EXISTS Review CASCADE;
DROP TABLE IF EXISTS AppointmentService CASCADE;
DROP TABLE IF EXISTS PackageService CASCADE;
DROP TABLE IF EXISTS UserRole CASCADE;
DROP TABLE IF EXISTS Payment CASCADE;
DROP TABLE IF EXISTS LoyaltyCard CASCADE;
DROP TABLE IF EXISTS Appointment CASCADE;
DROP TABLE IF EXISTS Service CASCADE;
DROP TABLE IF EXISTS Package CASCADE;
DROP TABLE IF EXISTS Status CASCADE;
DROP TABLE IF EXISTS Category CASCADE;
DROP TABLE IF EXISTS Role CASCADE;
DROP TABLE IF EXISTS "User" CASCADE;

DROP TYPE IF EXISTS appointment_type CASCADE;


CREATE TYPE appointment_type AS ENUM ('pre-booked', 'walk-in');



-- User table
CREATE TABLE "User" (
    user_id SERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(20),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Role table
CREATE TABLE Role (
    role_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);

-- Category table
CREATE TABLE Category (
    category_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);

-- Status table
CREATE TABLE Status (
    status_id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- Service table
CREATE TABLE Service (
    service_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    duration_minutes INTEGER NOT NULL CHECK (duration_minutes > 0),
    category_id INTEGER NOT NULL,
    CONSTRAINT fk_service_category FOREIGN KEY (category_id) 
        REFERENCES Category(category_id) ON DELETE RESTRICT
);

-- Package table
CREATE TABLE Package (
    package_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    max_usage INTEGER NOT NULL CHECK (max_usage > 0)
);

-- Appointment table
CREATE TABLE Appointment (
    appointment_id SERIAL PRIMARY KEY,
    appointment_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    notes TEXT,
    type appointment_type NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL CHECK (total_price >= 0),
    user_id INTEGER NOT NULL,
    status_id INTEGER NOT NULL,
    CONSTRAINT fk_appointment_user FOREIGN KEY (user_id) 
        REFERENCES "User"(user_id) ON DELETE CASCADE,
    CONSTRAINT fk_appointment_status FOREIGN KEY (status_id) 
        REFERENCES Status(status_id) ON DELETE RESTRICT,
    CONSTRAINT chk_appointment_times CHECK (end_time > appointment_time)
);

-- LoyaltyCard table
CREATE TABLE LoyaltyCard (
    card_id SERIAL PRIMARY KEY,
    points INTEGER NOT NULL DEFAULT 0 CHECK (points >= 0),
    user_id INTEGER NOT NULL UNIQUE,
    CONSTRAINT fk_loyaltycard_user FOREIGN KEY (user_id) 
        REFERENCES "User"(user_id) ON DELETE CASCADE
);

-- Payment table
CREATE TABLE Payment (
    payment_id SERIAL PRIMARY KEY,
    amount DECIMAL(10, 2) NOT NULL CHECK (amount >= 0),
    method VARCHAR(50) NOT NULL,
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(50) NOT NULL,
    appointment_id INTEGER NOT NULL,
    CONSTRAINT fk_payment_appointment FOREIGN KEY (appointment_id) 
        REFERENCES Appointment(appointment_id) ON DELETE CASCADE
);

-- Review table (with UNIQUE constraint on payment_id)
CREATE TABLE Review (
    review_id SERIAL PRIMARY KEY,
    rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
    comment TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    payment_id INTEGER NOT NULL UNIQUE,
    CONSTRAINT fk_review_payment FOREIGN KEY (payment_id) 
        REFERENCES Payment(payment_id) ON DELETE CASCADE
);

-- UserRole table (junction table)
CREATE TABLE UserRole (
    user_id INTEGER NOT NULL,
    role_id INTEGER NOT NULL,
    PRIMARY KEY (user_id, role_id),
    CONSTRAINT fk_userrole_user FOREIGN KEY (user_id) 
        REFERENCES "User"(user_id) ON DELETE CASCADE,
    CONSTRAINT fk_userrole_role FOREIGN KEY (role_id) 
        REFERENCES Role(role_id) ON DELETE CASCADE
);

-- PackageService table (junction table)
CREATE TABLE PackageService (
    package_id INTEGER NOT NULL,
    service_id INTEGER NOT NULL,
    discounted_price DECIMAL(10, 2) NOT NULL CHECK (discounted_price >= 0),
    PRIMARY KEY (package_id, service_id),
    CONSTRAINT fk_packageservice_package FOREIGN KEY (package_id) 
        REFERENCES Package(package_id) ON DELETE CASCADE,
    CONSTRAINT fk_packageservice_service FOREIGN KEY (service_id) 
        REFERENCES Service(service_id) ON DELETE CASCADE
);

-- AppointmentService table (junction table)
CREATE TABLE AppointmentService (
    appointment_id INTEGER NOT NULL,
    service_id INTEGER NOT NULL,
    PRIMARY KEY (appointment_id, service_id),
    CONSTRAINT fk_appointmentservice_appointment FOREIGN KEY (appointment_id) 
        REFERENCES Appointment(appointment_id) ON DELETE CASCADE,
    CONSTRAINT fk_appointmentservice_service FOREIGN KEY (service_id) 
        REFERENCES Service(service_id) ON DELETE CASCADE
);



CREATE INDEX idx_appointment_user ON Appointment(user_id);
CREATE INDEX idx_appointment_status ON Appointment(status_id);
CREATE INDEX idx_appointment_time ON Appointment(appointment_time);
CREATE INDEX idx_payment_appointment ON Payment(appointment_id);
CREATE INDEX idx_service_category ON Service(category_id);
CREATE INDEX idx_user_email ON "User"(email);