BEGIN;

-- =========================
-- DROP ALL TABLES
-- =========================
DROP TABLE IF EXISTS
    exam_answers,
    exam_attempts,
    exam_package_questions,
    exam_packages,
    exams,
    question_options,
    questions,
    students,
    classes,
    token_usages,
    token_transactions,
    token_packages,
    sales_withdrawals,
    sales_commissions,
    sales_profiles,
    lbb_settings,
    lbbs,
    users,
    cache,
    cache_locks,
    failed_jobs,
    job_batches,
    jobs,
    migrations,
    password_reset_tokens,
    sessions,
    settings
CASCADE;

-- =========================
-- DROP TYPES
-- =========================
DROP TYPE IF EXISTS exam_option_enum CASCADE;
DROP TYPE IF EXISTS exam_status_enum CASCADE;
DROP TYPE IF EXISTS exam_state_enum CASCADE;
DROP TYPE IF EXISTS lbb_status_enum CASCADE;
DROP TYPE IF EXISTS withdrawal_status_enum CASCADE;
DROP TYPE IF EXISTS token_tx_status_enum CASCADE;
DROP TYPE IF EXISTS user_role_enum CASCADE;
DROP TYPE IF EXISTS user_status_enum CASCADE;
DROP TYPE IF EXISTS student_status_enum CASCADE;

-- =========================
-- ENUM TYPES
-- =========================
CREATE TYPE exam_option_enum AS ENUM ('A','B','C','D','E');
CREATE TYPE exam_status_enum AS ENUM ('in_progress','finished');
CREATE TYPE exam_state_enum AS ENUM ('active','inactive');
CREATE TYPE lbb_status_enum AS ENUM ('pending','active','suspend');
CREATE TYPE withdrawal_status_enum AS ENUM ('pending','approved','rejected');
CREATE TYPE token_tx_status_enum AS ENUM ('waiting_payment','waiting_verification','approved','rejected');
CREATE TYPE user_role_enum AS ENUM ('super_admin','sales','admin_lbb','siswa');
CREATE TYPE user_status_enum AS ENUM ('active','inactive','suspend');
CREATE TYPE student_status_enum AS ENUM ('active','inactive');

-- =========================
-- CORE TABLES
-- =========================

CREATE TABLE users (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(255) UNIQUE,
    email_verified_at TIMESTAMP,
    password VARCHAR(255) NOT NULL,
    role user_role_enum NOT NULL DEFAULT 'siswa',
    lbb_id BIGINT,
    status user_status_enum NOT NULL DEFAULT 'active',
    remember_token VARCHAR(100),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

CREATE INDEX users_role_index ON users(role);
CREATE INDEX users_lbb_id_index ON users(lbb_id);
CREATE INDEX users_status_index ON users(status);

CREATE TABLE lbbs (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    admin_user_id BIGINT NOT NULL,
    sales_id BIGINT,
    token_balance INTEGER NOT NULL DEFAULT 0,
    status lbb_status_enum NOT NULL DEFAULT 'pending',
    suspend_reason TEXT,
    deleted_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE INDEX lbbs_sales_id_index ON lbbs(sales_id);
CREATE INDEX lbbs_status_index ON lbbs(status);
CREATE INDEX lbbs_admin_user_id_index ON lbbs(admin_user_id);

-- Add FK after both tables exist
ALTER TABLE users
ADD CONSTRAINT users_lbb_id_foreign
FOREIGN KEY (lbb_id) REFERENCES lbbs(id) ON DELETE CASCADE;

ALTER TABLE lbbs
ADD CONSTRAINT lbbs_admin_user_id_foreign
FOREIGN KEY (admin_user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE lbbs
ADD CONSTRAINT lbbs_sales_id_foreign
FOREIGN KEY (sales_id) REFERENCES users(id) ON DELETE SET NULL;

-- =========================
-- CLASS & STUDENT
-- =========================

CREATE TABLE classes (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    lbb_id BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP,
    CONSTRAINT classes_lbb_id_foreign
        FOREIGN KEY (lbb_id) REFERENCES lbbs(id) ON DELETE CASCADE
);

CREATE TABLE students (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL,
    class_id BIGINT NOT NULL,
    lbb_id BIGINT NOT NULL,
    status student_status_enum NOT NULL DEFAULT 'active',
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP,
    CONSTRAINT students_user_id_foreign
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT students_class_id_foreign
        FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE RESTRICT,
    CONSTRAINT students_lbb_id_foreign
        FOREIGN KEY (lbb_id) REFERENCES lbbs(id) ON DELETE CASCADE
);

-- =========================
-- EXAM SYSTEM
-- =========================

CREATE TABLE exams (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    lbb_id BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL,
    exam_code VARCHAR(255) NOT NULL UNIQUE,
    start_date TIMESTAMP NOT NULL,
    end_date TIMESTAMP NOT NULL,
    max_attempt INTEGER NOT NULL DEFAULT 1,
    status exam_state_enum NOT NULL DEFAULT 'active',
    deleted_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    CONSTRAINT exams_lbb_id_foreign
        FOREIGN KEY (lbb_id) REFERENCES lbbs(id) ON DELETE CASCADE
);

CREATE INDEX exams_lbb_id_exam_code_index ON exams(lbb_id, exam_code);
CREATE INDEX exams_status_index ON exams(status);

CREATE TABLE exam_packages (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    exam_id BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL,
    random_question BOOLEAN NOT NULL DEFAULT FALSE,
    show_discussion BOOLEAN NOT NULL DEFAULT FALSE,
    is_active BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    CONSTRAINT exam_packages_exam_id_foreign
        FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE
);

CREATE TABLE questions (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    lbb_id BIGINT NOT NULL,
    content TEXT NOT NULL,
    category VARCHAR(255),
    discussion TEXT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    CONSTRAINT questions_lbb_id_foreign
        FOREIGN KEY (lbb_id) REFERENCES lbbs(id) ON DELETE CASCADE
);

CREATE TABLE question_options (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    question_id BIGINT NOT NULL,
    option_key exam_option_enum NOT NULL,
    content TEXT NOT NULL,
    is_correct BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    CONSTRAINT question_options_question_id_foreign
        FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
);

CREATE TABLE exam_package_questions (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    exam_package_id BIGINT NOT NULL,
    question_id BIGINT NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    CONSTRAINT exam_package_questions_exam_package_id_foreign
        FOREIGN KEY (exam_package_id) REFERENCES exam_packages(id) ON DELETE CASCADE,
    CONSTRAINT exam_package_questions_question_id_foreign
        FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
);

CREATE TABLE exam_attempts (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    exam_id BIGINT NOT NULL,
    exam_package_id BIGINT NOT NULL,
    student_id BIGINT NOT NULL,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP,
    score NUMERIC(5,2),
    status exam_status_enum NOT NULL DEFAULT 'in_progress',
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    CONSTRAINT exam_attempts_exam_id_foreign
        FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
    CONSTRAINT exam_attempts_exam_package_id_foreign
        FOREIGN KEY (exam_package_id) REFERENCES exam_packages(id) ON DELETE CASCADE,
    CONSTRAINT exam_attempts_student_id_foreign
        FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);

CREATE TABLE exam_answers (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    exam_attempt_id BIGINT NOT NULL,
    question_id BIGINT NOT NULL,
    selected_option exam_option_enum,
    is_correct BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    CONSTRAINT exam_answers_exam_attempt_id_foreign
        FOREIGN KEY (exam_attempt_id) REFERENCES exam_attempts(id) ON DELETE CASCADE,
    CONSTRAINT exam_answers_question_id_foreign
        FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
);

-- =========================
-- OTHER SUPPORT TABLES
-- =========================

CREATE TABLE cache (
    key VARCHAR(255) PRIMARY KEY,
    value TEXT NOT NULL,
    expiration INTEGER NOT NULL
);

CREATE TABLE cache_locks (
    key VARCHAR(255) PRIMARY KEY,
    owner VARCHAR(255) NOT NULL,
    expiration INTEGER NOT NULL
);

CREATE TABLE settings (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    key_name VARCHAR(255) UNIQUE NOT NULL,
    value TEXT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

COMMIT;