-- Grade Result Visibility Settings
-- This table controls which grades can see results in the student portal

CREATE TABLE IF NOT EXISTS grade_result_visibility (
    id INT AUTO_INCREMENT PRIMARY KEY,
    grade_id INT NOT NULL,
    year_id INT NOT NULL,
    results_visible TINYINT(1) DEFAULT 1,
    hide_reason VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_grade_year (grade_id, year_id),
    FOREIGN KEY (grade_id) REFERENCES grades(id) ON DELETE CASCADE,
    FOREIGN KEY (year_id) REFERENCES academic_years(id) ON DELETE CASCADE
);

-- Insert default visibility settings for current grades and year
INSERT IGNORE INTO grade_result_visibility (grade_id, year_id, results_visible)
SELECT g.id, 6, 1 FROM grades g; -- Year ID 6 = 2025-2026, all grades visible by default