-- Rockview University Student Performance / HOD assignment tables
CREATE TABLE IF NOT EXISTS student_performance_hod_assignments (
    id BIGSERIAL PRIMARY KEY,
    department_id BIGINT NOT NULL,
    department_name VARCHAR(255) NOT NULL,
    campus VARCHAR(255) NOT NULL,
    school VARCHAR(255) NULL,
    staff_user_id BIGINT NOT NULL,
    assigned_by BIGINT NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    assigned_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ended_at TIMESTAMPTZ NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_active_hod_department_campus
ON student_performance_hod_assignments (campus, department_id)
WHERE is_active = TRUE;

CREATE INDEX IF NOT EXISTS idx_hod_staff_user
ON student_performance_hod_assignments (staff_user_id);

CREATE TABLE IF NOT EXISTS student_promotion_performance (
    id BIGSERIAL PRIMARY KEY,
    department_id BIGINT NOT NULL,
    department_name VARCHAR(255) NOT NULL,
    campus VARCHAR(255) NOT NULL,
    school VARCHAR(255) NULL,
    academic_year VARCHAR(20) NOT NULL,
    exam_session VARCHAR(150) NOT NULL,
    level_name VARCHAR(120) NOT NULL,
    sat_count INTEGER NOT NULL CHECK (sat_count >= 0),
    passed_count INTEGER NOT NULL CHECK (passed_count >= 0),
    notes TEXT NULL,
    hod_assignment_id BIGINT NOT NULL,
    submitted_by BIGINT NOT NULL,
    submitted_by_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (passed_count <= sat_count)
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_promotion_performance_record
ON student_promotion_performance
(campus, department_id, academic_year, exam_session, level_name);

CREATE INDEX IF NOT EXISTS idx_promotion_perf_department
ON student_promotion_performance (department_id, campus);

CREATE INDEX IF NOT EXISTS idx_promotion_perf_year
ON student_promotion_performance (academic_year);
