-- School Results & Payments System Database Schema
-- Created for MySQL 8.0+

-- Create database if not exists
CREATE DATABASE IF NOT EXISTS student1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE student1;

-- Academic Years
CREATE TABLE academic_years (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    starts_on DATE NULL,
    ends_on DATE NULL,
    status ENUM('open', 'closed') DEFAULT 'open',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Terms
CREATE TABLE terms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    sort_order INT NOT NULL,
    visible_to_students BOOLEAN DEFAULT FALSE,
    publish_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Grades
CREATE TABLE grades (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) NOT NULL UNIQUE,
    title VARCHAR(100) NOT NULL,
    sort_order INT NOT NULL,
    enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Classes (year-scoped)
CREATE TABLE classes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    year_id INT NOT NULL,
    grade_id INT NOT NULL,
    code VARCHAR(10) NOT NULL,
    enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (year_id) REFERENCES academic_years(id) ON DELETE CASCADE,
    FOREIGN KEY (grade_id) REFERENCES grades(id) ON DELETE CASCADE,
    UNIQUE KEY unique_class_per_year (year_id, grade_id, code)
);

-- Subjects
CREATE TABLE subjects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Grade Subject Rules (min/max per grade & subject)
CREATE TABLE grade_subject_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    grade_id INT NOT NULL,
    subject_id INT NOT NULL,
    min_score DECIMAL(5,2) NOT NULL,
    max_score DECIMAL(5,2) NOT NULL,
    enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (grade_id) REFERENCES grades(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
    UNIQUE KEY unique_grade_subject (grade_id, subject_id)
);

-- Students
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    login_code CHAR(8) NOT NULL UNIQUE,
    full_name VARCHAR(200) NOT NULL,
    national_id VARCHAR(50) NULL UNIQUE,
    gender ENUM('M', 'F') NULL,
    birthdate DATE NULL,
    enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_login_code (login_code),
    INDEX idx_national_id (national_id)
);

-- Enrollments (student ↔ year/grade/class)
CREATE TABLE enrollments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    year_id INT NOT NULL,
    grade_id INT NOT NULL,
    class_id INT NOT NULL,
    status ENUM('active', 'transferred', 'graduated', 'withdrawn') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (year_id) REFERENCES academic_years(id) ON DELETE CASCADE,
    FOREIGN KEY (grade_id) REFERENCES grades(id) ON DELETE CASCADE,
    FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
    UNIQUE KEY unique_student_year (student_id, year_id)
);

-- Exams
CREATE TABLE exams (
    id INT AUTO_INCREMENT PRIMARY KEY,
    year_id INT NOT NULL,
    term_id INT NOT NULL,
    grade_id INT NOT NULL,
    subject_id INT NOT NULL,
    title VARCHAR(100) NOT NULL,
    exam_date DATE NOT NULL,
    min_score DECIMAL(5,2) NOT NULL,
    max_score DECIMAL(5,2) NOT NULL,
    enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (year_id) REFERENCES academic_years(id) ON DELETE CASCADE,
    FOREIGN KEY (term_id) REFERENCES terms(id) ON DELETE CASCADE,
    FOREIGN KEY (grade_id) REFERENCES grades(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
);

-- Exam Results
CREATE TABLE exam_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    exam_id INT NOT NULL,
    student_id INT NOT NULL,
    score DECIMAL(5,2) NOT NULL,
    percentage DECIMAL(5,2) AS (score / (SELECT max_score FROM exams WHERE id = exam_id) * 100) STORED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    UNIQUE KEY unique_exam_student (exam_id, student_id)
);

-- Payments
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    enrollment_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    paid_on DATE NOT NULL,
    method ENUM('cash', 'card', 'bank', 'wallet') NOT NULL,
    notes TEXT NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (enrollment_id) REFERENCES enrollments(id) ON DELETE CASCADE
);

-- Settings Tables
CREATE TABLE academic_visibility (
    id INT AUTO_INCREMENT PRIMARY KEY,
    current_year_id INT NOT NULL,
    first_term_visible BOOLEAN DEFAULT FALSE,
    first_term_publish_at DATETIME NULL,
    second_term_visible BOOLEAN DEFAULT FALSE,
    second_term_publish_at DATETIME NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (current_year_id) REFERENCES academic_years(id)
);

CREATE TABLE display_options (
    id INT AUTO_INCREMENT PRIMARY KEY,
    display_mode ENUM('color_only', 'color_score', 'color_score_percent') DEFAULT 'color_only',
    percent_rounding TINYINT DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE pass_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    per_subject_threshold_mode ENUM('useGradeSubjectMin', 'fixed') DEFAULT 'useGradeSubjectMin',
    subject_pass_min_percent DECIMAL(5,2) NULL,
    overall_pass_rule ENUM('none', 'avgAtLeast') DEFAULT 'none',
    overall_pass_min_percent DECIMAL(5,2) NULL,
    pass_condition ENUM('allSubjectsOrOverall', 'allSubjectsAndOverall', 'allSubjectsOnly', 'overallOnly') DEFAULT 'allSubjectsOnly',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE grade_costs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    year_id INT NOT NULL,
    grade_id INT NOT NULL,
    fee_amount DECIMAL(10,2) NOT NULL,
    enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (year_id) REFERENCES academic_years(id) ON DELETE CASCADE,
    FOREIGN KEY (grade_id) REFERENCES grades(id) ON DELETE CASCADE,
    UNIQUE KEY unique_year_grade_cost (year_id, grade_id)
);

CREATE TABLE percentage_bands (
    id INT AUTO_INCREMENT PRIMARY KEY,
    start_percent DECIMAL(5,2) NOT NULL,
    end_percent DECIMAL(5,2) NOT NULL,
    label VARCHAR(50) NOT NULL,
    color_hex VARCHAR(7) NOT NULL,
    sort_order INT NOT NULL,
    enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE security_policy (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mask_national_id_in_admin BOOLEAN DEFAULT FALSE,
    student_login_requires_unique_and_national_id BOOLEAN DEFAULT TRUE,
    session_timeout_minutes INT DEFAULT 60,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Audit Log
CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    actor_id INT NULL,
    actor_type ENUM('admin', 'student') NOT NULL,
    role VARCHAR(20) NULL,
    action VARCHAR(50) NOT NULL,
    entity VARCHAR(50) NOT NULL,
    entity_id INT NULL,
    before_data JSON NULL,
    after_data JSON NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default data
INSERT INTO terms (name, sort_order, visible_to_students) VALUES 
('First Term', 1, FALSE),
('Second Term', 2, FALSE);

INSERT INTO grades (code, title, sort_order) VALUES 
('KG1', 'Kindergarten 1', 1),
('KG2', 'Kindergarten 2', 2),
('Prim1', 'Primary 1', 3),
('Prim2', 'Primary 2', 4),
('Prim3', 'Primary 3', 5),
('Prim4', 'Primary 4', 6),
('Prim5', 'Primary 5', 7),
('Prim6', 'Primary 6', 8);

INSERT INTO subjects (name) VALUES 
('Mathematics'),
('English'),
('Science'),
('Social Studies'),
('Arabic'),
('Art'),
('Physical Education');

INSERT INTO percentage_bands (start_percent, end_percent, label, color_hex, sort_order) VALUES 
(0, 49, 'Fail', '#F44336', 1),
(50, 65, 'Pass', '#FF9800', 2),
(66, 79, 'Good', '#2196F3', 3),
(80, 100, 'Excellent', '#4CAF50', 4);

-- Initialize settings
INSERT INTO display_options (display_mode, percent_rounding) VALUES ('color_only', 0);
INSERT INTO pass_rules () VALUES ();
INSERT INTO security_policy () VALUES ();