-- PostgreSQL-only migration for the four additional academic school modules.
-- Run once in the same database used by the PHP API (for example, pgAdmin).

BEGIN;


-- PostgreSQL schema for School of Health Sciences intake tracking.
-- Run this script in the target PostgreSQL database (for example, pgAdmin).



CREATE TABLE IF NOT EXISTS hs_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 hs_programs (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    school_id BIGINT NOT NULL REFERENCES hs_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 hs_programs_name_per_school UNIQUE (school_id, program_name),
    CONSTRAINT hs_programs_code_per_school UNIQUE (school_id, program_code)
);

CREATE TABLE IF NOT EXISTS hs_enrollments (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    program_id BIGINT NOT NULL REFERENCES hs_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 hs_enrollments_program_intake_unique
        UNIQUE (program_id, intake_year, intake_name)
);

-- Promotion exam counts attached to an existing Education program intake.
-- Promotion year must be exactly one year after intake year.
CREATE TABLE IF NOT EXISTS hs_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 hs_student_performance_year_check
        CHECK (promotion_year = intake_year + 1),
    CONSTRAINT hs_student_performance_totals_check
        CHECK (students_passed + students_failed = students_sat),
    CONSTRAINT hs_student_performance_intake_fk
        FOREIGN KEY (program_id, intake_year, intake_name)
        REFERENCES hs_enrollments(program_id, intake_year, intake_name)
        ON DELETE RESTRICT,
    CONSTRAINT hs_student_performance_intake_unique
        UNIQUE (program_id, intake_year, intake_name)
);

CREATE INDEX IF NOT EXISTS hs_programs_school_idx ON hs_programs(school_id);
CREATE INDEX IF NOT EXISTS hs_enrollments_program_year_idx
    ON hs_enrollments(program_id, intake_year);
CREATE INDEX IF NOT EXISTS hs_student_performance_promotion_year_idx
    ON hs_student_performance(promotion_year, program_id);

INSERT INTO hs_schools (school_name, school_code)
VALUES ('School of Health Sciences', 'HSC')
ON CONFLICT (school_name) DO NOTHING;

-- PostgreSQL schema for School of Engineering intake tracking.
-- Run this script in the target PostgreSQL database (for example, pgAdmin).



CREATE TABLE IF NOT EXISTS eng_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 eng_programs (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    school_id BIGINT NOT NULL REFERENCES eng_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 eng_programs_name_per_school UNIQUE (school_id, program_name),
    CONSTRAINT eng_programs_code_per_school UNIQUE (school_id, program_code)
);

CREATE TABLE IF NOT EXISTS eng_enrollments (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    program_id BIGINT NOT NULL REFERENCES eng_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 eng_enrollments_program_intake_unique
        UNIQUE (program_id, intake_year, intake_name)
);

-- Promotion exam counts attached to an existing Education program intake.
-- Promotion year must be exactly one year after intake year.
CREATE TABLE IF NOT EXISTS eng_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 eng_student_performance_year_check
        CHECK (promotion_year = intake_year + 1),
    CONSTRAINT eng_student_performance_totals_check
        CHECK (students_passed + students_failed = students_sat),
    CONSTRAINT eng_student_performance_intake_fk
        FOREIGN KEY (program_id, intake_year, intake_name)
        REFERENCES eng_enrollments(program_id, intake_year, intake_name)
        ON DELETE RESTRICT,
    CONSTRAINT eng_student_performance_intake_unique
        UNIQUE (program_id, intake_year, intake_name)
);

CREATE INDEX IF NOT EXISTS eng_programs_school_idx ON eng_programs(school_id);
CREATE INDEX IF NOT EXISTS eng_enrollments_program_year_idx
    ON eng_enrollments(program_id, intake_year);
CREATE INDEX IF NOT EXISTS eng_student_performance_promotion_year_idx
    ON eng_student_performance(promotion_year, program_id);

INSERT INTO eng_schools (school_name, school_code)
VALUES ('School of Engineering', 'ENG')
ON CONFLICT (school_name) DO NOTHING;

-- PostgreSQL schema for School of Natural Sciences intake tracking.
-- Run this script in the target PostgreSQL database (for example, pgAdmin).



CREATE TABLE IF NOT EXISTS ns_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 ns_programs (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    school_id BIGINT NOT NULL REFERENCES ns_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 ns_programs_name_per_school UNIQUE (school_id, program_name),
    CONSTRAINT ns_programs_code_per_school UNIQUE (school_id, program_code)
);

CREATE TABLE IF NOT EXISTS ns_enrollments (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    program_id BIGINT NOT NULL REFERENCES ns_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 ns_enrollments_program_intake_unique
        UNIQUE (program_id, intake_year, intake_name)
);

-- Promotion exam counts attached to an existing Education program intake.
-- Promotion year must be exactly one year after intake year.
CREATE TABLE IF NOT EXISTS ns_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 ns_student_performance_year_check
        CHECK (promotion_year = intake_year + 1),
    CONSTRAINT ns_student_performance_totals_check
        CHECK (students_passed + students_failed = students_sat),
    CONSTRAINT ns_student_performance_intake_fk
        FOREIGN KEY (program_id, intake_year, intake_name)
        REFERENCES ns_enrollments(program_id, intake_year, intake_name)
        ON DELETE RESTRICT,
    CONSTRAINT ns_student_performance_intake_unique
        UNIQUE (program_id, intake_year, intake_name)
);

CREATE INDEX IF NOT EXISTS ns_programs_school_idx ON ns_programs(school_id);
CREATE INDEX IF NOT EXISTS ns_enrollments_program_year_idx
    ON ns_enrollments(program_id, intake_year);
CREATE INDEX IF NOT EXISTS ns_student_performance_promotion_year_idx
    ON ns_student_performance(promotion_year, program_id);

INSERT INTO ns_schools (school_name, school_code)
VALUES ('School of Natural Sciences', 'NSC')
ON CONFLICT (school_name) DO NOTHING;

-- PostgreSQL schema for School of Tourism and Hospitality intake tracking.
-- Run this script in the target PostgreSQL database (for example, pgAdmin).



CREATE TABLE IF NOT EXISTS th_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 th_programs (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    school_id BIGINT NOT NULL REFERENCES th_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 th_programs_name_per_school UNIQUE (school_id, program_name),
    CONSTRAINT th_programs_code_per_school UNIQUE (school_id, program_code)
);

CREATE TABLE IF NOT EXISTS th_enrollments (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    program_id BIGINT NOT NULL REFERENCES th_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 th_enrollments_program_intake_unique
        UNIQUE (program_id, intake_year, intake_name)
);

-- Promotion exam counts attached to an existing Education program intake.
-- Promotion year must be exactly one year after intake year.
CREATE TABLE IF NOT EXISTS th_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 th_student_performance_year_check
        CHECK (promotion_year = intake_year + 1),
    CONSTRAINT th_student_performance_totals_check
        CHECK (students_passed + students_failed = students_sat),
    CONSTRAINT th_student_performance_intake_fk
        FOREIGN KEY (program_id, intake_year, intake_name)
        REFERENCES th_enrollments(program_id, intake_year, intake_name)
        ON DELETE RESTRICT,
    CONSTRAINT th_student_performance_intake_unique
        UNIQUE (program_id, intake_year, intake_name)
);

CREATE INDEX IF NOT EXISTS th_programs_school_idx ON th_programs(school_id);
CREATE INDEX IF NOT EXISTS th_enrollments_program_year_idx
    ON th_enrollments(program_id, intake_year);
CREATE INDEX IF NOT EXISTS th_student_performance_promotion_year_idx
    ON th_student_performance(promotion_year, program_id);

INSERT INTO th_schools (school_name, school_code)
VALUES ('School of Tourism and Hospitality', 'THS')
ON CONFLICT (school_name) DO NOTHING;

COMMIT;
