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;

-- Combined Master Dynamic Fallback Settings Data Initialization
INSERT INTO system_settings (setting_key, setting_value) VALUES
('school_name', 'St. Andrews National School'),
('school_motto', 'Moulding Visionary Leaders for Tomorrow'),
('vision', 'To be a premier educational institution producing globally competitive citizens.'),
('mission', 'To provide a holistic learning environment utilizing modern methodologies.'),
('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', 'Established with a vision to nurture outstanding leaders, our institution has stood as a beacon of academic excellence and character development for generations.'),
('contact_phone', '+94 11 234 5678'),
('contact_address', '123 High School Road, Colombo, Sri Lanka'),
('school_email', 'info@school.edu'),
('social_facebook', 'https://facebook.com'),
('social_youtube', 'https://youtube.com'),
('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 VARCHAR(10) 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,
    term_name VARCHAR(20) NOT NULL,
    is_active TINYINT(1) DEFAULT 0,
    FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON DELETE CASCADE
) 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',
    UNIQUE KEY uq_grade_class (grade_number, class_name)
) 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,
    nic_number VARCHAR(20) UNIQUE NOT NULL,
    gender ENUM('Male', 'Female') NOT NULL,
    date_of_birth DATE NOT NULL,
    date_joined_service DATE NOT NULL,
    date_joined_school DATE NOT 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',
    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,
    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. 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,
    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,
    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;

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

CREATE TABLE IF NOT EXISTS student_class_allocations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    grade_class_id INT,
    academic_year_id INT,
    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
) 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,
    is_al_elective TINYINT(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subject_assignments_teachers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    grade_class_id INT,
    subject_id INT,
    teacher_id INT,
    academic_year_id INT,
    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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS student_subject_selections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    subject_id INT,
    academic_year_id INT,
    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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

CREATE TABLE IF NOT EXISTS student_daily_attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    academic_year_id INT,
    term_id INT,
    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 (academic_year_id) REFERENCES academic_years(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,
    subject_id INT,
    academic_year_id INT,
    term_id INT,
    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 (academic_year_id) REFERENCES academic_years(id) ON DELETE CASCADE,
    FOREIGN KEY (term_id) REFERENCES school_terms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fee_payments_ledger (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    academic_year_id INT,
    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;

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

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

CREATE TABLE IF NOT EXISTS school_administration (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_name VARCHAR(150) NOT NULL,
    designation VARCHAR(100) NOT NULL,
    email_address VARCHAR(150) NOT NULL,
    phone_extension VARCHAR(50) DEFAULT NULL,
    display_order INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO school_administration (staff_name, designation, email_address, phone_extension, display_order) 
VALUES ('Prof. Maxwell Sterling', 'Principal / Head of School', 'principal@school.edu', 'Ext. 101', 1)
ON DUPLICATE KEY UPDATE staff_name=VALUES(staff_name);

CREATE TABLE IF NOT EXISTS societies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    logo VARCHAR(255) DEFAULT NULL,
    master_in_charge VARCHAR(255) DEFAULT NULL,
    president VARCHAR(255) DEFAULT NULL,
    in_charge_name VARCHAR(100) DEFAULT NULL,
    student_president VARCHAR(100) DEFAULT NULL,
    image VARCHAR(255) DEFAULT 'default-society.jpg'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS clubs_societies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_name VARCHAR(100) NOT NULL UNIQUE,
    teacher_in_charge_id INT 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,
    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;

-- =========================================================================
-- 8. 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,
    transfer_certificate_no VARCHAR(50) NOT NULL UNIQUE,
    destination_school TEXT NOT NULL,
    issue_date DATE NOT NULL,
    reason_for_migration TEXT,
    FOREIGN KEY (student_id) REFERENCES student_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

COMMIT;
ALTER TABLE teacher_master_profiles 
  ADD COLUMN marital_status ENUM('Unmarried', 'Married', 'Widowed', 'Other') NOT NULL DEFAULT 'Unmarried' AFTER gender,
  ADD COLUMN appointment_nature ENUM('Permanent', 'Attached') NOT NULL DEFAULT 'Permanent' AFTER current_status,
  ADD COLUMN attachment_school_name TEXT NULL AFTER appointment_nature,
  ADD COLUMN attachment_release_order VARCHAR(100) NULL AFTER attachment_school_name;

-- Extended Educational Profiles for Degree/Main Subject tracking
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, -- e.g., "B.Sc. in Computer Science"
    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;

-- Family Dependents Profile Tracking
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, -- Stores high school subjects if applicable e.g. ["ICT", "Physics", "Combined Maths"]
    FOREIGN KEY (teacher_id) REFERENCES teacher_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Granular Extra Operational Institutional Responsibilities
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 DEFAULT NULL,
    FOREIGN KEY (teacher_id) REFERENCES teacher_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE curriculum_subjects 
DROP COLUMN IF EXISTS is_al_elective,
ADD COLUMN target_cycle VARCHAR(100) DEFAULT 'Secondary (6-11)' AFTER subject_name,
ADD COLUMN linked_stream VARCHAR(100) DEFAULT 'Common Core' AFTER target_cycle;
ALTER TABLE curriculum_subjects 
ADD COLUMN IF NOT EXISTS target_cycle VARCHAR(100) DEFAULT 'Secondary (6-11)' AFTER subject_name,
ADD COLUMN IF NOT EXISTS linked_stream VARCHAR(100) DEFAULT 'Common Core' AFTER target_cycle;
ALTER TABLE grade_classes ADD COLUMN academic_year INT(4) NOT NULL AFTER stream;
ALTER TABLE grade_classes DROP INDEX uq_grade_class;
ALTER TABLE grade_classes ADD UNIQUE KEY uq_grade_class (grade_number, class_name, academic_year);
ALTER TABLE student_master_profiles 
ADD COLUMN school_house VARCHAR(50) NULL AFTER birth_certificate_no;
-- 1. Adding Salary/Bank Details to Contact Details (or a new table)
CREATE TABLE IF NOT EXISTS teacher_financial_records (
    teacher_id INT PRIMARY KEY,
    salary_number VARCHAR(50),
    salary_scale VARCHAR(50),
    bank_name VARCHAR(100),
    bank_branch VARCHAR(100),
    account_number VARCHAR(50),
    epf_etf_number VARCHAR(50),
    FOREIGN KEY (teacher_id) REFERENCES teacher_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE teacher_master_profiles 
ADD COLUMN permanent_address TEXT NULL,
ADD COLUMN residential_address TEXT NULL;

-- 2. Expanding Master Profile for missing fields
ALTER TABLE teacher_master_profiles 
ADD COLUMN nationality VARCHAR(50) DEFAULT 'Sri Lankan' AFTER marital_status,
ADD COLUMN religion VARCHAR(50) AFTER nationality,

ADD COLUMN retirement_date DATE AFTER date_joined_service,
ADD COLUMN house_assignment VARCHAR(50) AFTER current_status;

-- 3. Training/Qualification Registry (Expanded)
CREATE TABLE IF NOT EXISTS teacher_training_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT NOT NULL,
    training_name VARCHAR(255),
    institution VARCHAR(255),
    completed_date DATE,
    certificate_file_path VARCHAR(255),
    FOREIGN KEY (teacher_id) REFERENCES teacher_master_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 1. Ensure Teacher Master Profile has all required fields
ALTER TABLE teacher_master_profiles 
ADD COLUMN IF NOT EXISTS marital_status ENUM('Unmarried', 'Married', 'Widowed', 'Other') DEFAULT 'Unmarried',
ADD COLUMN IF NOT EXISTS appointment_nature ENUM('Permanent', 'Attached') DEFAULT 'Permanent',
ADD COLUMN IF NOT EXISTS current_status ENUM('Active', 'On Leave', 'Released/Transferred', 'Retired') DEFAULT 'Active';

-- 2. Ensure Contact Details has the permanent_address column
-- (This resolves your #1054 error)
ALTER TABLE teacher_contact_details 
ADD COLUMN IF NOT EXISTS permanent_address TEXT NOT NULL;

-- 3. Verify Academic Profiles has the required columns
ALTER TABLE teacher_academic_profiles 
ADD COLUMN IF NOT EXISTS weekly_periods INT NOT NULL DEFAULT 0;
-- Extend Master Profile
ALTER TABLE teacher_master_profiles 
ADD COLUMN name_with_initials VARCHAR(255) AFTER full_name,
ADD COLUMN email_address VARCHAR(255) AFTER residential_address,
ADD COLUMN emergency_contact VARCHAR(255) AFTER email_address,
ADD COLUMN photo_path VARCHAR(255) DEFAULT NULL,
ADD COLUMN appointment_type ENUM('Permanent', 'Temporary', 'Volunteer', 'Contract') DEFAULT 'Permanent',
ADD COLUMN class_teacher_assignment VARCHAR(50) DEFAULT 'None',
ADD COLUMN service_confirmation_date DATE DEFAULT NULL,
ADD COLUMN salary_number VARCHAR(100) DEFAULT NULL,
ADD COLUMN salary_scale VARCHAR(100) DEFAULT NULL,
ADD COLUMN bank_name VARCHAR(150) DEFAULT NULL,
ADD COLUMN bank_branch VARCHAR(150) DEFAULT NULL,
ADD COLUMN account_number VARCHAR(50) DEFAULT NULL,
ADD COLUMN epf_etf_number VARCHAR(50) DEFAULT NULL,
ADD COLUMN next_increment_date DATE DEFAULT NULL;

-- Dynamic History Table
CREATE TABLE IF NOT EXISTS teacher_service_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT,
    school_name VARCHAR(255),
    start_date DATE,
    end_date DATE,
    designation_held VARCHAR(150),
    FOREIGN KEY (teacher_id) REFERENCES teacher_master_profiles(id) ON DELETE CASCADE
);

-- Leave Ledger
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
);

-- Document Vault Ledger
CREATE TABLE IF NOT EXISTS teacher_document_vault (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT,
    document_type VARCHAR(100),
    file_path VARCHAR(255),
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (teacher_id) REFERENCES teacher_master_profiles(id) ON DELETE CASCADE
- 4. NEW: Secure Financial Operations Vault Table 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, `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;
[ Edit inline ] [ Edit ] [ Create PHP code ]
-- 5. NEW: Educational & Institutional Training Registry 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, FOREIGN KEY (`teacher_id`) REFERENCES `teacher_master_profiles`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;