-- PostgreSQL schema for Business Department intake tracking.
-- Run this script in the target PostgreSQL database (for example, pgAdmin).

BEGIN;

CREATE TABLE IF NOT EXISTS biz_schools (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    school_name VARCHAR(150) NOT NULL UNIQUE,
    school_code VARCHAR(30) UNIQUE,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_by BIGINT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS biz_programs (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    school_id BIGINT NOT NULL REFERENCES biz_schools(id) ON DELETE RESTRICT,
    program_name VARCHAR(150) NOT NULL,
    program_code VARCHAR(30),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_by BIGINT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT biz_programs_name_per_school UNIQUE (school_id, program_name),
    CONSTRAINT biz_programs_code_per_school UNIQUE (school_id, program_code)
);

CREATE TABLE IF NOT EXISTS biz_enrollments (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    program_id BIGINT NOT NULL REFERENCES biz_programs(id) ON DELETE RESTRICT,
    intake_year SMALLINT NOT NULL CHECK (intake_year BETWEEN 2000 AND 2200),
    intake_name VARCHAR(50) NOT NULL,
    male_count INTEGER NOT NULL DEFAULT 0 CHECK (male_count >= 0),
    female_count INTEGER NOT NULL DEFAULT 0 CHECK (female_count >= 0),
    created_by BIGINT,
    updated_by BIGINT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT biz_enrollments_program_intake_unique
        UNIQUE (program_id, intake_year, intake_name)
);

-- Promotion examination results are keyed to an existing program intake.
-- Promotion year is constrained to exactly one year after intake year.
CREATE TABLE IF NOT EXISTS biz_student_performance (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    program_id BIGINT NOT NULL,
    intake_year SMALLINT NOT NULL CHECK (intake_year BETWEEN 2000 AND 2200),
    intake_name VARCHAR(50) NOT NULL,
    promotion_year SMALLINT NOT NULL,
    students_sat INTEGER NOT NULL DEFAULT 0 CHECK (students_sat >= 0),
    students_passed INTEGER NOT NULL DEFAULT 0 CHECK (students_passed >= 0),
    students_failed INTEGER NOT NULL DEFAULT 0 CHECK (students_failed >= 0),
    created_by BIGINT,
    updated_by BIGINT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT biz_student_performance_year_check
        CHECK (promotion_year = intake_year + 1),
    CONSTRAINT biz_student_performance_totals_check
        CHECK (students_passed + students_failed = students_sat),
    CONSTRAINT biz_student_performance_intake_fk
        FOREIGN KEY (program_id, intake_year, intake_name)
        REFERENCES biz_enrollments(program_id, intake_year, intake_name)
        ON DELETE RESTRICT,
    CONSTRAINT biz_student_performance_intake_unique
        UNIQUE (program_id, intake_year, intake_name)
);

CREATE INDEX IF NOT EXISTS biz_programs_school_idx ON biz_programs(school_id);
CREATE INDEX IF NOT EXISTS biz_enrollments_program_year_idx
    ON biz_enrollments(program_id, intake_year);
CREATE INDEX IF NOT EXISTS biz_student_performance_promotion_year_idx
    ON biz_student_performance(promotion_year, program_id);

COMMIT;
