CREATE DATABASE IF NOT EXISTS school_db;
USE school_db;

START TRANSACTION;

-- =========================================================================
-- 1. AUTHENTICATION & CORE MASTER INFRASTRUCTURE
-- =========================================================================

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    role ENUM('admin', 'editor') NOT NULL DEFAULT 'editor',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS system_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(50) NOT NULL UNIQUE,
    setting_value TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO system_settings (setting_key, setting_value) VALUES
('school_name', 'Hanwella Rajasinghe Central College'),
('school_motto', 'With Faith and Strength'),
('vision', 'To produce excellent, dignified, virtuous and knowledgeable sons and daughters of Mother Rajasinghe.'),
('mission', 'To transform the children of the college into virtuous and capable citizens through high-quality holistic education.'),
('primary_color', '#800020'),
('secondary_color', '#a31d3d'),
('dark_color', '#4a0012'),
('accent_color', '#d4af37'),
('text_color', '#2c3e50'),
('logo_path', 'default-logo.png'),
('hero_bg_path', 'default-hero.jpg'),
('school_history', 'Hanwella Rajasinghe Central College is a premier 1AB National School situated in the historic Seethawaka Kingdom. Founded in 1886 as a bilingual school, it evolved into a Central College under Dr. C.W.W. Kannangara''s free education policy, subsequently celebrating over 80 years of excellence since moving to its current location in 1946.The school''s history is shaped by several major milestones:1886: Established as a bilingual school near the Kelani River bank (the current Divisional Education Office premises) with an initial enrollment of 500 students.Dr. Kannangara Era: As "Free Education" was introduced, the school was designated as Hanwella Madhya Maha Vidyalaya (one of the 54 original central schools), with Mr. Evan Ediriweera serving as its first principal.1946: Outgrowing its original location, the school relocated to a new plot of land locally known as "Lancia meru godalla". Local residents, led by Captain D.S. Gunasekara, raised Rs. 12,000 to purchase the land and gifted it to Minister of Education Dr. C.W.W. Kannangara.1951–1953: The foundation stone for the first two-story building was laid on December 21, 1951. The new school premises were officially opened in May 1953 by Prime Minister Dudley Senanayake.1968: Then-Member of Parliament Mr. Philip Gunawardena proposed renaming the institution to "Hanwella Rajasinghe Central College," honouring the area''s rich royal heritage.Present Day: It is a massive "super grade" national school boasting more than 160 academic staff members and offering education across the Science, Commerce, Arts, and Technology streams. The school motto is "With Faith and Strength".'),
('contact_phone', '+94 36 225 5015 '),
('contact_address', 'Hanwella, Sri Lanka'),
('school_email', 'hanrajacen@gmail.com'),
('social_facebook', 'https://web.facebook.com/hrcc.edu.lk'),
('social_youtube', 'https://youtube.com/@rajasinghecentral'),
('school_map', '<iframe src="https://www.google.com/maps/embed?pb=!1m18!1m12!1m3!1d3960.798412822185!2d79.8510!3d6.9149!2m3!1f0!2f0!3f0!3m2!1i1024!2i768!4f13.1!3m3!1m2!1s0x0%3A0x0!2zNsKwNTQnNTMuNiJOIDc5wrA1MScwMy42IkU!5e0!3m2!1sen!2slk!4v1600000000000!5m2!1sen!2slk" width="100%" height="450" style="border:0;" allowfullscreen="" loading="lazy"></iframe>')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

-- =========================================================================
-- 2. ACADEMIC TIME, SYSTEM STRUCTURES & CORE APP STRATA
-- =========================================================================

CREATE TABLE IF NOT EXISTS academic_years (
    id INT AUTO_INCREMENT PRIMARY KEY,
    year_name YEAR NOT NULL UNIQUE,
    is_active TINYINT(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS school_terms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    academic_year_id INT NOT NULL,
    text_name VARCHAR(20) NOT NULL, 
    is_active TINYINT(1) DEFAULT 0,
    FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON DELETE CASCADE,
    UNIQUE KEY uq_year_term (academic_year_id, text_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS grade_classes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    grade_number INT NOT NULL,
    class_name VARCHAR(15) NOT NULL,
    stream VARCHAR(20) DEFAULT 'Primary',
    academic_year_id INT NOT NULL,
    UNIQUE KEY uq_grade_class (grade_number, class_name, academic_year_id),
    FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    section_name VARCHAR(100) NOT NULL,
    head_name VARCHAR(100) NOT NULL,
    head_photo VARCHAR(255) DEFAULT 'default-avatar.jpg',
    message TEXT NULL,
    display_order INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================================================
-- 3. STAFF LAYER (Unified MoE/NEMIS Structure)
-- =========================================================================

CREATE TABLE IF NOT EXISTS teacher_master_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    registration_number VARCHAR(30) UNIQUE NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    display_name VARCHAR(100) NOT NULL,
    name_with_initials VARCHAR(255) NULL,
    nic_number VARCHAR(20) UNIQUE NOT NULL,
    gender ENUM('Male', 'Female') NOT NULL,
    marital_status ENUM('Unmarried', 'Married', 'Widowed', 'Other') NOT NULL DEFAULT 'Unmarried',
    nationality VARCHAR(50) DEFAULT 'Sri Lankan',
    religion VARCHAR(50) NULL,
    date_of_birth DATE NOT NULL,
    date_joined_service DATE NOT NULL,
    date_joined_school DATE NOT NULL,
    retirement_date DATE NULL,
    service_confirmation_date DATE DEFAULT NULL,
    next_increment_date DATE DEFAULT NULL,
    service_class ENUM('SLEAS', 'SLTES', 'SLTS I', 'SLTS II-I', 'SLTS II-II', 'SLTS III') NOT NULL,
    current_designation VARCHAR(100) DEFAULT 'Graduate Teacher',
    current_status ENUM('Active', 'On Leave', 'Released/Transferred', 'Retired') DEFAULT 'Active',
    appointment_nature ENUM('Permanent', 'Attached') NOT NULL DEFAULT 'Permanent',
    appointment_type ENUM('Permanent', 'Temporary', 'Volunteer', 'Contract') DEFAULT 'Permanent',
    attachment_school_name TEXT NULL,
    attachment_release_order VARCHAR(100) NULL,
    class_teacher_assignment VARCHAR(50) DEFAULT 'None',
    house_assignment VARCHAR(50) NULL,
    photo_path VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS teacher_academic_profiles (
    teacher_id INT PRIMARY KEY,
    highest_qualification VARCHAR(150) NOT NULL,
    appointed_subject VARCHAR(100) NOT NULL,
    teaching_subject_1 VARCHAR(100) NOT NULL,
    teaching_subject_2 VARCHAR(100) DEFAULT 'None',
    medium_of_instruction ENUM('Sinhala', 'Tamil', 'English', 'Bilingual') NOT NULL,
    section_pool ENUM('Primary (Gr. 1-5)', 'Junior (Gr. 6-9)', 'O/L (Gr. 10-11)', 'A/L Arts', 'A/L Commerce', 'A/L Science', 'A/L ICT/Tech') NOT NULL,
    weekly_periods INT NOT NULL DEFAULT 0,
    FOREIGN KEY (teacher_id) REFERENCES teacher_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS teacher_contact_details (
    teacher_id INT PRIMARY KEY,
    permanent_address TEXT NOT NULL,
    residential_address TEXT NULL,
    mobile_number VARCHAR(15) NOT NULL,
    email_address VARCHAR(100) DEFAULT NULL,
    emergency_contact_name VARCHAR(100) DEFAULT NULL,
    emergency_contact_phone VARCHAR(15) DEFAULT NULL,
    FOREIGN KEY (teacher_id) REFERENCES teacher_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================================================
-- 4. OPERATIONAL HISTORY, TRAINING & VAULT SUB-SYSTEMS
-- =========================================================================

CREATE TABLE IF NOT EXISTS teacher_finance_vault (
    teacher_id INT PRIMARY KEY,
    bank_name VARCHAR(150) NULL,
    bank_branch VARCHAR(150) NULL,
    account_number VARCHAR(50) NULL,
    tax_identification_number VARCHAR(50) NULL,
    salary_number VARCHAR(100) DEFAULT NULL,
    salary_scale VARCHAR(100) DEFAULT NULL,
    epf_etf_number VARCHAR(50) DEFAULT NULL,
    basic_salary DECIMAL(12,2) DEFAULT 0.00,
    allowances DECIMAL(12,2) DEFAULT 0.00,
    FOREIGN KEY (teacher_id) REFERENCES teacher_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS teacher_training_registry (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT NOT NULL,
    program_name VARCHAR(255) NOT NULL,
    institution_name VARCHAR(255) NULL,
    duration_weeks INT DEFAULT 0,
    completion_year INT NULL,
    certificate_file_path VARCHAR(255) NULL,
    FOREIGN KEY (teacher_id) REFERENCES teacher_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS teacher_service_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT NOT NULL,
    school_name VARCHAR(255) NOT NULL,
    designation_held VARCHAR(150) NULL,
    start_date DATE NULL,
    end_date DATE NULL,
    FOREIGN KEY (teacher_id) REFERENCES teacher_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS teacher_leave_balances (
    teacher_id INT PRIMARY KEY,
    casual_balance INT DEFAULT 20,
    medical_balance INT DEFAULT 21,
    no_pay_taken INT DEFAULT 0,
    FOREIGN KEY (teacher_id) REFERENCES teacher_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS teacher_document_vault (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT NOT NULL,
    document_type VARCHAR(100) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (teacher_id) REFERENCES teacher_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS teacher_qualifications_registry (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT NOT NULL,
    degree_diploma_name VARCHAR(255) NOT NULL,
    major_subject_1 VARCHAR(100) NOT NULL,
    major_subject_2 VARCHAR(100) DEFAULT 'None',
    major_subject_3 VARCHAR(100) DEFAULT 'None',
    FOREIGN KEY (teacher_id) REFERENCES teacher_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS teacher_family_dependents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT NOT NULL,
    relationship ENUM('Spouse', 'Child') NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    al_subjects_json JSON NULL,
    FOREIGN KEY (teacher_id) REFERENCES teacher_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS teacher_assigned_duties (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT NOT NULL,
    duty_type ENUM('Subject Teaching', 'Medium Specific Assignment', 'Sectional Head Duty', 'Co-Curricular/Other') NOT NULL,
    subject_title VARCHAR(100) DEFAULT NULL,
    medium_title VARCHAR(50) DEFAULT NULL,
    section_title VARCHAR(100) DEFAULT NULL,
    allocated_periods INT DEFAULT 0,
    special_remarks TEXT NULL,
    FOREIGN KEY (teacher_id) REFERENCES teacher_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================================================
-- 5. STUDENT LAYER (Master Matrix Records)
-- =========================================================================

CREATE TABLE IF NOT EXISTS student_master_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admission_number VARCHAR(20) NOT NULL UNIQUE,
    admission_date DATE NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    display_name VARCHAR(150) NOT NULL,
    gender ENUM('Male', 'Female', 'Other') NOT NULL,
    date_of_birth DATE NOT NULL,
    birth_certificate_no VARCHAR(50) NOT NULL UNIQUE,
    school_house VARCHAR(50) NULL,
    birth_cert_file_path VARCHAR(255) DEFAULT NULL,
    profile_photo VARCHAR(255) DEFAULT 'default-student.jpg',
    current_status ENUM('Active', 'Left', 'Alumni', 'Transferred', 'Suspended') DEFAULT 'Active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS student_contact_demographics (
    student_id INT PRIMARY KEY,
    permanent_address TEXT NOT NULL,
    current_address TEXT DEFAULT NULL,
    distance_to_school DECIMAL(5,2) DEFAULT NULL,
    mode_of_transport VARCHAR(50) DEFAULT NULL,
    telephone_landline VARCHAR(15) DEFAULT NULL,
    emergency_mobile VARCHAR(15) NOT NULL,
    religion VARCHAR(30) DEFAULT NULL,
    ethnicity VARCHAR(30) DEFAULT NULL,
    special_needs_notes TEXT DEFAULT NULL,
    FOREIGN KEY (student_id) REFERENCES student_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS student_guardians (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    relationship ENUM('Father', 'Mother', 'Guardian') NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    nic_passport VARCHAR(20) NOT NULL,
    occupation VARCHAR(100) DEFAULT NULL,
    phone_primary VARCHAR(20) NOT NULL,
    phone_secondary VARCHAR(20) DEFAULT NULL,
    email_address VARCHAR(100) DEFAULT NULL,
    is_emergency_contact TINYINT(1) NOT NULL DEFAULT 0,
    KEY idx_guardian_student (student_id),
    FOREIGN KEY (student_id) REFERENCES student_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS student_family_economics (
    student_id INT PRIMARY KEY,
    sibling_details_json JSON DEFAULT NULL,
    family_income_bracket ENUM('Low-Income', 'Middle', 'High-Income') DEFAULT 'Middle',
    samurdhi_beneficiary TINYINT(1) DEFAULT 0,
    FOREIGN KEY (student_id) REFERENCES student_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS student_health_metrics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    recorded_date DATE NOT NULL,
    blood_group ENUM('A+', 'A-', 'B+', 'B-', 'AB+', 'AB-', 'O+', 'O-') NOT NULL,
    height_cm DECIMAL(5,2) DEFAULT NULL,
    weight_kg DECIMAL(5,2) DEFAULT NULL,
    known_allergies TEXT DEFAULT NULL,
    chronic_illnesses TEXT DEFAULT NULL,
    emergency_instructions TEXT DEFAULT NULL,
    FOREIGN KEY (student_id) REFERENCES student_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS student_previous_history (
    student_id INT PRIMARY KEY,
    previous_school_name VARCHAR(255) NOT NULL,
    last_grade_completed INT DEFAULT NULL,
    leaving_certificate_no VARCHAR(100) DEFAULT NULL,
    remarks_achievements TEXT DEFAULT NULL,
    FOREIGN KEY (student_id) REFERENCES student_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS student_leaving_registry (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL UNIQUE,
    certificate_serial_no VARCHAR(50) NOT NULL UNIQUE,
    leaving_date DATE NOT NULL,
    reason_for_leaving VARCHAR(255) NOT NULL,
    academic_conduct VARCHAR(100) DEFAULT 'Exemplary',
    extracurricular_summary TEXT DEFAULT NULL,
    special_remarks TEXT DEFAULT NULL,
    issued_by INT NULL,
    issued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES student_master_profiles(id) ON DELETE RESTRICT,
    FOREIGN KEY (issued_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================================================
-- 6. RELATIONAL TRANSACTION TABLES & LOGISTICS
-- =========================================================================

CREATE TABLE IF NOT EXISTS student_class_allocations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    grade_class_id INT NOT NULL,
    academic_year_id INT NOT NULL,
    allocated_date DATE NOT NULL,
    FOREIGN KEY (student_id) REFERENCES student_master_profiles(id) ON DELETE CASCADE,
    FOREIGN KEY (grade_class_id) REFERENCES grade_classes(id) ON DELETE CASCADE,
    FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON DELETE CASCADE,
    UNIQUE KEY uq_student_year_alloc (student_id, academic_year_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS curriculum_subjects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    subject_code VARCHAR(15) NOT NULL UNIQUE,
    subject_name VARCHAR(100) NOT NULL,
    target_cycle VARCHAR(100) DEFAULT 'Secondary (6-11)',
    linked_stream VARCHAR(100) DEFAULT 'Common Core'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subject_assignments_teachers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    grade_class_id INT NOT NULL,
    subject_id INT NOT NULL,
    teacher_id INT NOT NULL,
    academic_year_id INT NOT NULL,
    FOREIGN KEY (grade_class_id) REFERENCES grade_classes(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES curriculum_subjects(id) ON DELETE CASCADE,
    FOREIGN KEY (teacher_id) REFERENCES teacher_master_profiles(id) ON DELETE CASCADE,
    FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON DELETE CASCADE,
    UNIQUE KEY uq_class_subject_teacher (grade_class_id, subject_id, teacher_id, academic_year_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS student_subject_selections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    subject_id INT NOT NULL,
    academic_year_id INT NOT NULL,
    FOREIGN KEY (student_id) REFERENCES student_master_profiles(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES curriculum_subjects(id) ON DELETE CASCADE,
    FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON DELETE CASCADE,
    UNIQUE KEY uq_student_subject_year (student_id, subject_id, academic_year_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================================================
-- 7. PERIODIC EVENT METRICS (ATTENDANCE, MARKS, LEDGER)
-- =========================================================================

CREATE TABLE IF NOT EXISTS student_daily_attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    term_id INT NOT NULL,
    attendance_date DATE NOT NULL,
    attendance_status ENUM('Present', 'Absent', 'Late', 'Excused') NOT NULL,
    remarks VARCHAR(255) DEFAULT NULL,
    FOREIGN KEY (student_id) REFERENCES student_master_profiles(id) ON DELETE CASCADE,
    FOREIGN KEY (term_id) REFERENCES school_terms(id) ON DELETE CASCADE,
    UNIQUE KEY uq_attendance (student_id, attendance_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS examination_marks_registry (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    subject_id INT NOT NULL,
    term_id INT NOT NULL,
    marks_obtained DECIMAL(5,2) NOT NULL,
    grade_earned VARCHAR(2) NOT NULL,
    is_absent TINYINT(1) DEFAULT 0,
    FOREIGN KEY (student_id) REFERENCES student_master_profiles(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES curriculum_subjects(id) ON DELETE CASCADE,
    FOREIGN KEY (term_id) REFERENCES school_terms(id) ON DELETE CASCADE,
    UNIQUE KEY uq_student_subject_term (student_id, subject_id, term_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fee_payments_ledger (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    academic_year_id INT NOT NULL,
    payment_category VARCHAR(100) NOT NULL,
    amount_paid DECIMAL(10,2) NOT NULL,
    payment_date DATE NOT NULL,
    receipt_number VARCHAR(50) NOT NULL UNIQUE,
    FOREIGN KEY (student_id) REFERENCES student_master_profiles(id) ON DELETE CASCADE,
    FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================================================
-- 8. INSTITUTIONAL PUBLIC ECOSYSTEM (ADMIN, CO-CURRICULAR, PORTAL)
-- =========================================================================

CREATE TABLE IF NOT EXISTS school_administration (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_name VARCHAR(150) NOT NULL,
    designation VARCHAR(100) NOT NULL,
    type ENUM('admin', 'sectional_head') NOT NULL DEFAULT 'admin',
    email_address VARCHAR(150) NOT NULL,
    phone_extension VARCHAR(50) DEFAULT NULL,
    photo VARCHAR(255) DEFAULT 'default-profile.jpg',
    display_order INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO school_administration (staff_name, designation, type, email_address, phone_extension, display_order) 
VALUES ('Mr. Dhammika Bandara', 'Principal', 'admin', 'principal@hrcc.lk', '036-2255015', 1)
ON DUPLICATE KEY UPDATE staff_name=VALUES(staff_name);

CREATE TABLE IF NOT EXISTS clubs_societies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT NULL,
    logo VARCHAR(255) DEFAULT 'default-society.jpg',
    teacher_in_charge_id INT NULL,
    student_president VARCHAR(100) DEFAULT NULL,
    FOREIGN KEY (teacher_in_charge_id) REFERENCES teacher_master_profiles(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sports_teams (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sport_name VARCHAR(100) NOT NULL UNIQUE,
    coach_staff_id INT NULL,
    FOREIGN KEY (coach_staff_id) REFERENCES teacher_master_profiles(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS external_competitions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    competition_name VARCHAR(255) NOT NULL,
    competition_level ENUM('Zonal', 'Divisional', 'Provincial', 'National', 'International') NOT NULL,
    event_date DATE NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS student_achievements_portfolio (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    entity_type ENUM('Club', 'Sport', 'Academic_Competition') NOT NULL,
    entity_reference_id INT NOT NULL,
    achievement_description TEXT NOT NULL,
    award_date DATE NOT NULL,
    certificate_serial_no VARCHAR(100) DEFAULT NULL,
    FOREIGN KEY (student_id) REFERENCES student_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================================================
-- 9. COMMUNICATIONS & MULTIMEDIA
-- =========================================================================

CREATE TABLE IF NOT EXISTS news (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    image VARCHAR(255) DEFAULT NULL,
    author_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS gallery_albums (
    id INT AUTO_INCREMENT PRIMARY KEY,
    album_name VARCHAR(100) NOT NULL,
    cover_image VARCHAR(255) DEFAULT 'default-album.jpg',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS gallery_photos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    album_id INT NOT NULL,
    image_path VARCHAR(255) NOT NULL,
    is_visible TINYINT(1) DEFAULT 1,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (album_id) REFERENCES gallery_albums(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS school_downloads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    category_type ENUM('section_wise', 'year_wise', 'general_document') NOT NULL,
    group_identifier VARCHAR(100) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS institutional_notices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    target_audience ENUM('All', 'Primary', 'O/L', 'A/L', 'Teachers') DEFAULT 'All',
    publish_date DATE NOT NULL,
    expiry_date DATE DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS student_clearance_transfers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    transfer_certificate_no VARCHAR(50) NOT NULL UNIQUE,
    destination_school TEXT NOT NULL,
    issue_date DATE NOT NULL,
    reason_for_migration TEXT NULL,
    FOREIGN KEY (student_id) REFERENCES student_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

COMMIT;
CREATE TABLE IF NOT EXISTS `bulk_class_attendance_summaries` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `total_boys` INT DEFAULT 0,
  `total_girls` INT DEFAULT 0,
  `attendance_date` DATE NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `bulk_class_attendance_summaries` 
ADD COLUMN `boys_present` INT DEFAULT 0,
ADD COLUMN `girls_present` INT DEFAULT 0;
ALTER TABLE `bulk_class_attendance_summaries` 
ADD COLUMN `grade_class_id` INT NOT NULL AFTER `id`,
ADD COLUMN `boys_present` INT DEFAULT 0 AFTER `grade_class_id`,
ADD COLUMN `girls_present` INT DEFAULT 0 AFTER `boys_present`;

CREATE TABLE IF NOT EXISTS attendance_summaries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    class_id INT NOT NULL,
    attendance_date DATE NOT NULL,
    total_boys INT DEFAULT 0,
    total_girls INT DEFAULT 0,
    present_boys INT DEFAULT 0,
    present_girls INT DEFAULT 0,
    UNIQUE KEY unique_class_date (class_id, attendance_date),
    FOREIGN KEY (class_id) REFERENCES grade_classes(id) ON DELETE CASCADE
);