LogicalPhysicalDesign: OLPMS-create-DDL.sql

CREATE TABLE user_entity (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL CHECK (role IN ('USER', 'ADMIN', 'INSTRUCTOR'))
);

CREATE TABLE users(
    id INT PRIMARY KEY,
    FOREIGN KEY (id) REFERENCES user_entity(id)
);

CREATE TABLE administrators(
    id INT PRIMARY KEY,
    FOREIGN KEY (id) REFERENCES user_entity(id)
);

CREATE TABLE instructors(
    id INT PRIMARY KEY,
    FOREIGN KEY (id) REFERENCES user_entity(id)
);

CREATE TABLE subscription_plan (
    plan_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    duration_months INT NOT NULL,
    description TEXT,
    access_type VARCHAR(50)
);

CREATE TABLE user_subscription (
    subscription_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    plan_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status VARCHAR(30) NOT NULL,

    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (plan_id) REFERENCES subscription_plan(plan_id)
);

CREATE TABLE payment (
    payment_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    subscription_id INT NOT NULL,
    amount NUMERIC(10,2) NOT NULL,

    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (subscription_id) REFERENCES user_subscription(subscription_id)
);

CREATE TABLE support_ticket (
    ticket_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    admin_id INT NOT NULL,
    subject VARCHAR(200),
    description TEXT,
    status VARCHAR(30),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (admin_id) REFERENCES administrators(id)
);

CREATE TABLE category (
    category_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT
);

CREATE TABLE course (
    course_id SERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    price NUMERIC(10,2),
    status VARCHAR(30),
    instructor_id INT NOT NULL,

    FOREIGN KEY (instructor_id) REFERENCES instructors(id)
);

CREATE TABLE course_category (
    course_id BIGINT NOT NULL,
    category_id BIGINT NOT NULL,

    PRIMARY KEY (course_id, category_id),

    CONSTRAINT fk_cc_course
        FOREIGN KEY (course_id) REFERENCES course(course_id)
        ON DELETE CASCADE,

    CONSTRAINT fk_cc_category
        FOREIGN KEY (category_id) REFERENCES category(category_id)
        ON DELETE CASCADE
);

CREATE TABLE module (
    module_id SERIAL PRIMARY KEY,
    course_id INT NOT NULL,
    title VARCHAR(150) NOT NULL,
    description TEXT,

    FOREIGN KEY (course_id) REFERENCES course(course_id)
);

CREATE TABLE lesson (
    lesson_id SERIAL PRIMARY KEY,
    module_id INT NOT NULL,
    title VARCHAR(150) NOT NULL,
    material TEXT,

    FOREIGN KEY (module_id) REFERENCES module(module_id)
);

CREATE TABLE quiz (
    quiz_id SERIAL PRIMARY KEY,
    total_points INT NOT NULL,
    passing_score INT NOT NULL,
    lesson_id INT NOT NULL,

    FOREIGN KEY (lesson_id) REFERENCES lesson(lesson_id)
);

create table quiz_attempt (
    attempt_id SERIAL PRIMARY KEY,
    score INT,
    attempt_date DATE,
    user_id INT,
    quiz_id INT,

    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (quiz_id) REFERENCES quiz(quiz_id)
);

CREATE TABLE enrollment (
    enrollment_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    course_id INT NOT NULL,
    enroll_date DATE NOT NULL,
    completion_status VARCHAR(30),
    progress_percentage INT CHECK (progress_percentage BETWEEN 0 AND 100),

    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (course_id) REFERENCES course(course_id)
);

CREATE TABLE certificate (
    certificate_id SERIAL PRIMARY KEY,
    enrollment_id INT UNIQUE NOT NULL,
    issue_date DATE NOT NULL,
    certificate_code VARCHAR(100) UNIQUE NOT NULL,
    status VARCHAR(30),

    FOREIGN KEY (enrollment_id) REFERENCES enrollment(enrollment_id)
);