CREATE TABLE IF NOT EXISTS japp_subjects (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    subject_code VARCHAR(32) NOT NULL UNIQUE,
    subject_name VARCHAR(128) NOT NULL,
    subject_type ENUM('CORE', 'ELECTIVE', 'OTHER') NOT NULL DEFAULT 'ELECTIVE',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS japp_programs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    institution_code VARCHAR(16) NOT NULL,
    program_code VARCHAR(32) NOT NULL UNIQUE,
    program_name VARCHAR(255) NOT NULL,
    selection_logic ENUM('BEST_5', 'BEST_6', '4C_2X') NOT NULL DEFAULT 'BEST_5',
    conversion_scale ENUM('DEFAULT', 'UST_HKU_CUHK') NOT NULL DEFAULT 'DEFAULT',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS japp_program_thresholds (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    program_id INT UNSIGNED NOT NULL,
    admission_year YEAR NOT NULL,
    uq_score DECIMAL(6,2) NOT NULL,
    median_score DECIMAL(6,2) NOT NULL,
    lq_score DECIMAL(6,2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_japp_threshold_program FOREIGN KEY (program_id) REFERENCES japp_programs(id) ON DELETE CASCADE,
    UNIQUE KEY uq_program_year (program_id, admission_year)
);

CREATE TABLE IF NOT EXISTS japp_program_weightings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    program_id INT UNSIGNED NOT NULL,
    subject_id INT UNSIGNED NOT NULL,
    weighting_multiplier DECIMAL(5,2) NOT NULL DEFAULT 1.00,
    is_required TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_japp_weight_program FOREIGN KEY (program_id) REFERENCES japp_programs(id) ON DELETE CASCADE,
    CONSTRAINT fk_japp_weight_subject FOREIGN KEY (subject_id) REFERENCES japp_subjects(id) ON DELETE CASCADE,
    UNIQUE KEY uq_program_subject (program_id, subject_id)
);

CREATE TABLE IF NOT EXISTS japp_students (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    student_sid VARCHAR(10) NULL UNIQUE,
    full_name VARCHAR(128) NOT NULL,
    password_hash VARCHAR(255) NULL,
    is_admin TINYINT(1) NOT NULL DEFAULT 0,
    csd_status ENUM('ATTAINED', 'NOT_ATTAINED') NOT NULL DEFAULT 'ATTAINED',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS japp_student_scores (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    student_id INT UNSIGNED NOT NULL,
    subject_id INT UNSIGNED NOT NULL,
    grade_raw VARCHAR(8) NOT NULL,
    grade_points DECIMAL(5,2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_japp_score_student FOREIGN KEY (student_id) REFERENCES japp_students(id) ON DELETE CASCADE,
    CONSTRAINT fk_japp_score_subject FOREIGN KEY (subject_id) REFERENCES japp_subjects(id) ON DELETE CASCADE,
    UNIQUE KEY uq_student_subject (student_id, subject_id)
);

CREATE TABLE IF NOT EXISTS japp_student_watchlist (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    student_id INT UNSIGNED NOT NULL,
    program_id INT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_japp_watchlist_student FOREIGN KEY (student_id) REFERENCES japp_students(id) ON DELETE CASCADE,
    CONSTRAINT fk_japp_watchlist_program FOREIGN KEY (program_id) REFERENCES japp_programs(id) ON DELETE CASCADE,
    UNIQUE KEY uq_student_program (student_id, program_id)
);

CREATE TABLE IF NOT EXISTS japp_log_db (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    log_type VARCHAR(64) NOT NULL,
    log_message TEXT NOT NULL,
    payload JSON NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO japp_subjects (subject_code, subject_name, subject_type) VALUES
('CHI', 'Chinese Language', 'CORE'),
('ENG', 'English Language', 'CORE'),
('MATH', 'Mathematics', 'CORE'),
('CSD', 'Citizenship and Social Development', 'CORE'),
('M0', 'Mathematics, Compulsory Part (M0)', 'ELECTIVE'),
('M1', 'Mathematics Extended Part, Module 1 (Calculus and Statistics)', 'ELECTIVE'),
('M2', 'Mathematics Extended Part, Module 2 (Algebra and Calculus)', 'ELECTIVE'),
('BIO', 'Biology', 'ELECTIVE'),
('CHEM', 'Chemistry', 'ELECTIVE'),
('PHY', 'Physics', 'ELECTIVE'),
('BAFS', 'BAFS', 'ELECTIVE'),
('ECON', 'Economics', 'ELECTIVE'),
('ICT', 'ICT', 'ELECTIVE'),
('CHIST', 'Chinese History', 'ELECTIVE'),
('CHLIT', 'Chinese Literature', 'ELECTIVE'),
('GEO', 'Geography', 'ELECTIVE'),
('HIST', 'History', 'ELECTIVE'),
('LITENG', 'Literature in English', 'ELECTIVE'),
('VA', 'Visual Arts', 'ELECTIVE')
ON DUPLICATE KEY UPDATE subject_name = VALUES(subject_name);

INSERT INTO japp_programs (institution_code, program_code, program_name, selection_logic, conversion_scale) VALUES
('HKU', 'JS6004', 'Bachelor of Engineering', 'BEST_6', 'UST_HKU_CUHK'),
('CUHK', 'JS4212', 'Bachelor of Science', 'BEST_6', 'UST_HKU_CUHK'),
('UST', 'JS5200', 'School of Science', 'BEST_5', 'UST_HKU_CUHK'),
('CITYU', 'JS1200', 'College of Engineering', '4C_2X', 'DEFAULT')
ON DUPLICATE KEY UPDATE program_name = VALUES(program_name);

INSERT INTO japp_program_thresholds (program_id, admission_year, uq_score, median_score, lq_score)
SELECT p.id, 2025, t.uq_score, t.median_score, t.lq_score
FROM japp_programs p
JOIN (
    SELECT 'JS6004' AS program_code, 43.00 AS uq_score, 39.00 AS median_score, 35.00 AS lq_score
    UNION ALL SELECT 'JS4212', 42.00, 38.00, 34.00
    UNION ALL SELECT 'JS5200', 36.00, 32.00, 28.00
    UNION ALL SELECT 'JS1200', 30.00, 27.00, 24.00
) t ON t.program_code = p.program_code
ON DUPLICATE KEY UPDATE
    uq_score = VALUES(uq_score),
    median_score = VALUES(median_score),
    lq_score = VALUES(lq_score);

INSERT INTO japp_program_weightings (program_id, subject_id, weighting_multiplier, is_required)
SELECT p.id, s.id, w.weighting_multiplier, w.is_required
FROM (
    SELECT 'JS6004' AS program_code, 'MATH' AS subject_code, 2.00 AS weighting_multiplier, 1 AS is_required
    UNION ALL SELECT 'JS6004', 'PHY', 1.50, 0
    UNION ALL SELECT 'JS6004', 'CHEM', 1.30, 0
    UNION ALL SELECT 'JS4212', 'MATH', 1.50, 1
    UNION ALL SELECT 'JS4212', 'BIO', 1.40, 0
    UNION ALL SELECT 'JS4212', 'CHEM', 1.40, 0
    UNION ALL SELECT 'JS5200', 'MATH', 1.80, 1
    UNION ALL SELECT 'JS5200', 'PHY', 1.50, 0
    UNION ALL SELECT 'JS1200', 'MATH', 1.70, 1
    UNION ALL SELECT 'JS1200', 'ICT', 1.30, 0
) w
JOIN japp_programs p ON p.program_code = w.program_code
JOIN japp_subjects s ON s.subject_code = w.subject_code
ON DUPLICATE KEY UPDATE
    weighting_multiplier = VALUES(weighting_multiplier),
    is_required = VALUES(is_required);