-- Chronelva Timetabling Software: MySQL-Compatible SQL Schema
-- Designed for a single institution with campuses, faculties, and departments
-- Supports K-12, high schools, universities, and junior colleges
-- Features: Campus-based logins (tertiary), no ENUMs, JSON for flexibility, SA CAPS compliance
-- Enhanced: InstitutionInfo includes departmental/state logos, watermark, and additional images

-- =========================
-- Core Tables
-- =========================

-- InstitutionInfo: Stores single-institution details (single record)
CREATE TABLE InstitutionInfo (
    institution_id INTEGER PRIMARY KEY CHECK (institution_id = 1), -- Unique ID for the single institution, fixed at 1
    name VARCHAR(255) NOT NULL, -- Name of the institution (e.g., Springfield High School)
    address VARCHAR(500), -- Physical address of the institution
    time_zone VARCHAR(50) NOT NULL, -- Time zone for scheduling (e.g., Africa/Johannesburg)
    institution_type VARCHAR(20) NOT NULL CHECK (institution_type IN ('k12', 'highschool', 'university', 'junior')), -- Type of institution
    academic_calendar JSON, -- JSON for term/semester dates (e.g., {"term1": {"start": "2025-01-15", "end": "2025-03-28"}})
    logo_url VARCHAR(255), -- URL to the main institution logo
    departmental_logo_url VARCHAR(255), -- URL to a logo shared across departments (e.g., school crest)
    state_logo_url VARCHAR(255), -- URL to state/regional logo (e.g., Gauteng Education Department)
    watermark_url VARCHAR(255), -- URL to watermark image for reports/schedules
    additional_images JSON, -- JSON for extra images (e.g., {"banner": "url", "campus_photo": "url"})
    contact_email VARCHAR(255), -- Contact email for the institution
    scheduling_policies JSON, -- JSON for scheduling rules (e.g., {"max_teacher_hours": 30, "min_subjects_per_student": 7})
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when record is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- Timestamp when record is last updated
);

-- Users: Stores all users with campus-based access
CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the user
    username VARCHAR(50) NOT NULL UNIQUE, -- Unique username for login
    password_hash VARCHAR(255) NOT NULL, -- Hashed password for security
    role VARCHAR(20) NOT NULL CHECK (role IN ('admin', 'teacher', 'student', 'parent')), -- User role
    email VARCHAR(255) NOT NULL UNIQUE, -- Unique email for communication
    institution_level VARCHAR(20) NOT NULL CHECK (institution_level IN ('k12', 'highschool', 'university', 'junior')), -- Institution level for feature toggling
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    campus_id INTEGER, -- Campus for login (required for tertiary institutions)
    first_name VARCHAR(100), -- User's first name
    last_name VARCHAR(100), -- User's last name
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when user is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp when user is last updated
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT, -- Prevents institution deletion
    CONSTRAINT tertiary_campus_required CHECK (
        (institution_level IN ('university', 'junior') AND campus_id IS NOT NULL) OR
        (institution_level IN ('k12', 'highschool'))
    ) -- Ensures campus_id for tertiary institutions
);

-- Campuses: Represents multiple locations within the institution
CREATE TABLE Campuses (
    campus_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the campus
    name VARCHAR(100) NOT NULL, -- Name of the campus (e.g., Main Campus)
    location VARCHAR(255), -- Physical location of the campus
    time_zone VARCHAR(50) NOT NULL, -- Time zone for campus scheduling
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    description TEXT, -- Description of the campus
    capacity INTEGER, -- Maximum capacity (e.g., number of students/rooms)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when campus is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp when campus is last updated
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT -- Prevents institution deletion
);

-- Add foreign key constraint to Users table after Campuses is created
ALTER TABLE Users ADD FOREIGN KEY (campus_id) REFERENCES Campuses (campus_id) ON DELETE SET NULL;

-- =========================
-- Academic Structure Tables
-- =========================

-- Faculties: Top-level academic divisions (e.g., FET Phase for SA high schools)
CREATE TABLE Faculties (
    faculty_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the faculty
    name VARCHAR(100) NOT NULL, -- Name of the faculty (e.g., FET Phase)
    campus_id INTEGER NOT NULL, -- Campus the faculty belongs to
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    description TEXT, -- Description of the faculty
    head_id INTEGER, -- User ID of the faculty head
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when faculty is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp when faculty is last updated
    FOREIGN KEY (campus_id) REFERENCES Campuses (campus_id) ON DELETE RESTRICT, -- Prevents campus deletion
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT, -- Prevents institution deletion
    FOREIGN KEY (head_id) REFERENCES Users (user_id) ON DELETE SET NULL -- Nullifies head if user deleted
);

-- Departments: Sub-units under faculties (e.g., Mathematics)
CREATE TABLE Departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the department
    name VARCHAR(100) NOT NULL, -- Name of the department (e.g., Mathematics)
    faculty_id INTEGER NOT NULL, -- Faculty the department belongs to
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    parent_dept_id INTEGER, -- Parent department for sub-departments
    description TEXT, -- Description of the department
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when department is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp when department is last updated
    FOREIGN KEY (faculty_id) REFERENCES Faculties (faculty_id) ON DELETE RESTRICT, -- Prevents faculty deletion
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT, -- Prevents institution deletion
    FOREIGN KEY (parent_dept_id) REFERENCES Departments (dept_id) ON DELETE SET NULL -- Nullifies parent if deleted
);

-- Courses: Academic offerings (e.g., English Home Language)
CREATE TABLE Courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the course
    code VARCHAR(20) NOT NULL UNIQUE, -- Unique course code (e.g., ENGHL10)
    name VARCHAR(100) NOT NULL, -- Name of the course (e.g., English Home Language)
    credits INTEGER DEFAULT 0, -- Credits (0 for K-12/high school)
    dept_id INTEGER NOT NULL, -- Department offering the course
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    course_level VARCHAR(20) CHECK (course_level IN ('intro', 'intermediate', 'advanced')), -- Course level
    prerequisites JSON, -- JSON for prerequisite course IDs
    max_enrollment INTEGER NOT NULL, -- Maximum students allowed
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when course is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp when course is last updated
    FOREIGN KEY (dept_id) REFERENCES Departments (dept_id) ON DELETE RESTRICT, -- Prevents department deletion
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT -- Prevents institution deletion
);

-- Teachers: Staff details for scheduling
CREATE TABLE Teachers (
    teacher_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the teacher
    user_id INTEGER NOT NULL UNIQUE, -- Links to Users table
    dept_id INTEGER NOT NULL, -- Department the teacher belongs to
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    availability JSON, -- JSON for available time slots (e.g., {"monday": ["09:00-12:00"]})
    preferences TEXT, -- Teacher scheduling preferences
    max_load INTEGER, -- Maximum teaching hours per week
    qualifications JSON, -- JSON for degrees/certifications
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when teacher is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp when teacher is last updated
    FOREIGN KEY (user_id) REFERENCES Users (user_id) ON DELETE CASCADE, -- Deletes teacher if user deleted
    FOREIGN KEY (dept_id) REFERENCES Departments (dept_id) ON DELETE RESTRICT, -- Prevents department deletion
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT -- Prevents institution deletion
);

-- Students: Learner details
CREATE TABLE Students (
    student_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the student
    user_id INTEGER NOT NULL UNIQUE, -- Links to Users table
    dept_id INTEGER NOT NULL, -- Department/grade the student belongs to
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    enrollment_year INTEGER NOT NULL, -- Year of enrollment (e.g., 2025)
    gpa DECIMAL(4,2), -- Grade point average
    custom_fields JSON, -- JSON for SA-specific data (e.g., {"grade": 10, "stream": "A"})
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when student is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp when student is last updated
    FOREIGN KEY (user_id) REFERENCES Users (user_id) ON DELETE CASCADE, -- Deletes student if user deleted
    FOREIGN KEY (dept_id) REFERENCES Departments (dept_id) ON DELETE RESTRICT, -- Prevents department deletion
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT -- Prevents institution deletion
);

-- Rooms: Physical/virtual spaces tied to campuses
CREATE TABLE Rooms (
    room_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the room
    name VARCHAR(50) NOT NULL, -- Name of the room (e.g., Lecture Hall A)
    capacity INTEGER NOT NULL, -- Maximum capacity of the room
    room_type VARCHAR(20) CHECK (room_type IN ('lecture', 'lab', 'virtual', 'seminar')), -- Type of room
    campus_id INTEGER NOT NULL, -- Campus the room belongs to
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    equipment JSON, -- JSON for room equipment (e.g., {"projector": true})
    availability JSON, -- JSON for room availability (e.g., {"monday": ["09:00-17:00"]})
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when room is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp when room is last updated
    FOREIGN KEY (campus_id) REFERENCES Campuses (campus_id) ON DELETE RESTRICT, -- Prevents campus deletion
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT -- Prevents institution deletion
);

-- TimeSlots: Predefined time blocks for scheduling
CREATE TABLE TimeSlots (
    slot_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the time slot
    day VARCHAR(10) NOT NULL CHECK (day IN ('monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday')), -- Day of the week
    start_time TIME NOT NULL, -- Start time of the slot
    end_time TIME NOT NULL, -- End time of the slot
    campus_id INTEGER NOT NULL, -- Campus the slot applies to
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when slot is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp when slot is last updated
    FOREIGN KEY (campus_id) REFERENCES Campuses (campus_id) ON DELETE RESTRICT, -- Prevents campus deletion
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT, -- Prevents institution deletion
    CONSTRAINT valid_time_range CHECK (start_time < end_time) -- Ensures valid time range
);

-- ClassGroups: Explicit groups for classes (e.g., Grade 10A in SA)
CREATE TABLE ClassGroups (
    class_group_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the class group
    name VARCHAR(50) NOT NULL UNIQUE, -- Name of the class (e.g., Grade 10A)
    dept_id INTEGER NOT NULL, -- Department/grade the class belongs to
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    description TEXT, -- Description of the class group
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when class group is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp when class group is last updated
    FOREIGN KEY (dept_id) REFERENCES Departments (dept_id) ON DELETE RESTRICT, -- Prevents department deletion
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT -- Prevents institution deletion
);

-- ClassGroupMembers: Links students to class groups
CREATE TABLE ClassGroupMembers (
    id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the membership
    class_group_id INTEGER NOT NULL, -- Class group the student belongs to
    student_id INTEGER NOT NULL, -- Student in the class group
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when membership is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp when membership is last updated
    FOREIGN KEY (class_group_id) REFERENCES ClassGroups (class_group_id) ON DELETE CASCADE, -- Deletes membership if class group deleted
    FOREIGN KEY (student_id) REFERENCES Students (student_id) ON DELETE CASCADE, -- Deletes membership if student deleted
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT, -- Prevents institution deletion
    UNIQUE (class_group_id, student_id) -- One class per student
);

-- =========================
-- Timetabling Tables
-- =========================

-- Timetables: Core schedule entries
CREATE TABLE Timetables (
    timetable_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the timetable entry
    course_id INTEGER NOT NULL, -- Course being scheduled
    teacher_id INTEGER NOT NULL, -- Teacher assigned to the course
    room_id INTEGER NOT NULL, -- Room for the course
    slot_id INTEGER NOT NULL, -- Time slot for the course
    semester VARCHAR(50) NOT NULL, -- Semester/term (e.g., Term 1)
    start_date DATE NOT NULL, -- Start date of the timetable
    end_date DATE NOT NULL, -- End date of the timetable
    status VARCHAR(20) NOT NULL CHECK (status IN ('draft', 'published', 'archived')), -- Status of the timetable
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    notes TEXT, -- Additional notes for the timetable
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when timetable is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp when timetable is last updated
    FOREIGN KEY (course_id) REFERENCES Courses (course_id) ON DELETE RESTRICT, -- Prevents course deletion
    FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id) ON DELETE RESTRICT, -- Prevents teacher deletion
    FOREIGN KEY (room_id) REFERENCES Rooms (room_id) ON DELETE RESTRICT, -- Prevents room deletion
    FOREIGN KEY (slot_id) REFERENCES TimeSlots (slot_id) ON DELETE RESTRICT, -- Prevents slot deletion
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT, -- Prevents institution deletion
    CONSTRAINT valid_date_range CHECK (start_date <= end_date) -- Ensures valid date range
);

-- Constraints: Rules for optimization and conflict resolution
CREATE TABLE Constraints (
    constraint_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the constraint
    constraint_type VARCHAR(50) NOT NULL CHECK (constraint_type IN ('teacher_availability', 'room_capacity', 'student_load', 'faculty_policy', 'no_overlap')), -- Type of constraint
    details JSON, -- JSON for constraint details (e.g., {"min_subjects": 7})
    timetable_id INTEGER, -- Optional link to specific timetable
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    priority INTEGER NOT NULL DEFAULT 1, -- Priority for constraint resolution
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when constraint is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp when constraint is last updated
    FOREIGN KEY (timetable_id) REFERENCES Timetables (timetable_id) ON DELETE SET NULL, -- Nullifies timetable link if deleted
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT -- Prevents institution deletion
);

-- Enrollments: Links students to courses/timetables
CREATE TABLE Enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the enrollment
    student_id INTEGER NOT NULL, -- Student enrolled in the course
    course_id INTEGER NOT NULL, -- Course being enrolled
    timetable_id INTEGER NOT NULL, -- Timetable entry for the course
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    enrollment_status VARCHAR(20) NOT NULL CHECK (enrollment_status IN ('enrolled', 'waitlisted', 'dropped')), -- Enrollment status
    grade VARCHAR(10), -- Grade/mark for the course
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when enrollment is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp when enrollment is last updated
    FOREIGN KEY (student_id) REFERENCES Students (student_id) ON DELETE CASCADE, -- Deletes enrollment if student deleted
    FOREIGN KEY (course_id) REFERENCES Courses (course_id) ON DELETE RESTRICT, -- Prevents course deletion
    FOREIGN KEY (timetable_id) REFERENCES Timetables (timetable_id) ON DELETE CASCADE, -- Deletes enrollment if timetable deleted
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT -- Prevents institution deletion
);

-- =========================
-- Extension Tables
-- =========================

-- Attendance: Tracks student presence
CREATE TABLE Attendance (
    attendance_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the attendance record
    timetable_id INTEGER NOT NULL, -- Timetable entry for attendance
    student_id INTEGER NOT NULL, -- Student being tracked
    date DATE NOT NULL, -- Date of attendance
    attendance_status VARCHAR(20) NOT NULL CHECK (attendance_status IN ('present', 'absent', 'late', 'excused')), -- Attendance status
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    notes TEXT, -- Additional notes for attendance
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when attendance is recorded
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp when attendance is last updated
    FOREIGN KEY (timetable_id) REFERENCES Timetables (timetable_id) ON DELETE CASCADE, -- Deletes attendance if timetable deleted
    FOREIGN KEY (student_id) REFERENCES Students (student_id) ON DELETE CASCADE, -- Deletes attendance if student deleted
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT -- Prevents institution deletion
);

-- Notifications: Logs alerts for schedule changes/reminders
CREATE TABLE Notifications (
    notif_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the notification
    user_id INTEGER NOT NULL, -- User receiving the notification
    timetable_id INTEGER, -- Optional timetable linked to notification
    message TEXT NOT NULL, -- Notification message
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when notification is sent
    channel VARCHAR(20) NOT NULL CHECK (channel IN ('email', 'sms', 'app')), -- Notification channel
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    notif_status VARCHAR(20) NOT NULL CHECK (notif_status IN ('sent', 'failed')), -- Notification status
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when notification is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp when notification is last updated
    FOREIGN KEY (user_id) REFERENCES Users (user_id) ON DELETE CASCADE, -- Deletes notification if user deleted
    FOREIGN KEY (timetable_id) REFERENCES Timetables (timetable_id) ON DELETE SET NULL, -- Nullifies timetable link if deleted
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT -- Prevents institution deletion
);

-- Audits: Tracks changes for compliance/debugging
CREATE TABLE Audits (
    audit_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the audit log
    timetable_id INTEGER, -- Optional timetable linked to the change
    user_id INTEGER NOT NULL, -- User making the change
    action VARCHAR(100) NOT NULL, -- Description of the action
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp of the change
    old_value JSON, -- Previous state (if applicable)
    new_value JSON, -- New state (if applicable)
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when audit is created
    FOREIGN KEY (timetable_id) REFERENCES Timetables (timetable_id) ON DELETE SET NULL, -- Nullifies timetable link if deleted
    FOREIGN KEY (user_id) REFERENCES Users (user_id) ON DELETE CASCADE, -- Deletes audit if user deleted
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT -- Prevents institution deletion
);

-- Resources: Additional allocable items (e.g., projectors)
CREATE TABLE Resources (
    resource_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the resource
    name VARCHAR(100) NOT NULL, -- Name of the resource (e.g., Projector A)
    resource_type VARCHAR(50) NOT NULL, -- Type of resource (e.g., equipment)
    quantity INTEGER NOT NULL, -- Available quantity
    campus_id INTEGER NOT NULL, -- Campus the resource belongs to
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    availability JSON, -- JSON for resource availability
    description TEXT, -- Description of the resource
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when resource is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp when resource is last updated
    FOREIGN KEY (campus_id) REFERENCES Campuses (campus_id) ON DELETE RESTRICT, -- Prevents campus deletion
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT -- Prevents institution deletion
);

-- Events: Non-course events (e.g., meetings, exams)
CREATE TABLE Events (
    event_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the event
    name VARCHAR(100) NOT NULL, -- Name of the event (e.g., Parent Meeting)
    description TEXT, -- Description of the event
    slot_id INTEGER NOT NULL, -- Time slot for the event
    room_id INTEGER NOT NULL, -- Room for the event
    organizer_id INTEGER NOT NULL, -- User organizing the event
    start_date DATE NOT NULL, -- Start date of the event
    end_date DATE NOT NULL, -- End date of the event
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    event_type VARCHAR(20) NOT NULL CHECK (event_type IN ('meeting', 'exam', 'workshop', 'other')), -- Type of event
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when event is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp when event is last updated
    FOREIGN KEY (slot_id) REFERENCES TimeSlots (slot_id) ON DELETE RESTRICT, -- Prevents slot deletion
    FOREIGN KEY (room_id) REFERENCES Rooms (room_id) ON DELETE RESTRICT, -- Prevents room deletion
    FOREIGN KEY (organizer_id) REFERENCES Users (user_id) ON DELETE CASCADE, -- Deletes event if organizer deleted
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT, -- Prevents institution deletion
    CONSTRAINT valid_date_range CHECK (start_date <= end_date) -- Ensures valid date range
);

-- Integrations: Tracks external system links
CREATE TABLE Integrations (
    integration_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for the integration
    integration_type VARCHAR(20) NOT NULL CHECK (integration_type IN ('calendar', 'lms', 'hr', 'payment')), -- Type of integration
    config JSON NOT NULL, -- JSON for integration settings (e.g., API keys)
    integration_status VARCHAR(20) NOT NULL CHECK (integration_status IN ('active', 'inactive')), -- Status of integration
    institution_id INTEGER NOT NULL CHECK (institution_id = 1), -- Links to InstitutionInfo (fixed at 1)
    last_sync TIMESTAMP, -- Last sync timestamp
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when integration is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp when integration is last updated
    FOREIGN KEY (institution_id) REFERENCES InstitutionInfo (institution_id) ON DELETE RESTRICT -- Prevents institution deletion
);

-- =========================
-- Sample Data (SA High School Example)
-- =========================
INSERT INTO InstitutionInfo (
    institution_id, name, address, time_zone, institution_type, 
    academic_calendar, logo_url, departmental_logo_url, state_logo_url, watermark_url, 
    additional_images, contact_email, scheduling_policies
) VALUES (
    1, 'Springfield High School', '123 Academic Way, Johannesburg', 'Africa/Johannesburg', 'highschool',
    '{"term1": {"start": "2025-01-15", "end": "2025-03-28"}, "term2": {"start": "2025-04-13", "end": "2025-06-26"}}',
    'https://example.com/logo.png',
    'https://example.com/dept_logo.png',
    'https://example.com/gauteng_edu_logo.png',
    'https://example.com/watermark.png',
    '{"banner": "https://example.com/banner.png", "campus_photo": "https://example.com/campus.jpg"}',
    'admin@springfield.co.za',
    '{"max_teacher_hours": 30, "min_break": 15, "min_subjects_per_student": 7, "sa_caps_room_rules": {"language_subjects": "standard_classroom", "science": "lab"}}'
);

-- Sample Campus
INSERT INTO Campuses (name, location, time_zone, institution_id, description, capacity)
VALUES ('Main Campus', 'Johannesburg', 'Africa/Johannesburg', 1, 'Primary campus for Springfield High', 1000);

-- Sample Faculty (FET Phase for SA high school)
INSERT INTO Faculties (name, campus_id, institution_id, description)
VALUES ('FET Phase', 1, 1, 'Further Education and Training for Grades 10-12');

-- Sample Department (Grade 10)
INSERT INTO Departments (name, faculty_id, institution_id, description)
VALUES ('Grade 10', 1, 1, 'Grade 10 classes');

-- Sample Class Group (Grade 10A)
INSERT INTO ClassGroups (name, dept_id, institution_id, description)
VALUES ('Grade 10A', 1, 1, 'Grade 10A with Science stream');

-- =========================
-- Create Indexes
-- =========================
CREATE INDEX idx_users_institution_id ON Users (institution_id);
CREATE INDEX idx_users_campus_id ON Users (campus_id);
CREATE INDEX idx_campuses_institution_id ON Campuses (institution_id);
CREATE INDEX idx_faculties_institution_id ON Faculties (institution_id);
CREATE INDEX idx_faculties_campus_id ON Faculties (campus_id);
CREATE INDEX idx_departments_institution_id ON Departments (institution_id);
CREATE INDEX idx_departments_faculty_id ON Departments (faculty_id);
CREATE INDEX idx_courses_institution_id ON Courses (institution_id);
CREATE INDEX idx_courses_dept_id ON Courses (dept_id);
CREATE INDEX idx_teachers_institution_id ON Teachers (institution_id);
CREATE INDEX idx_teachers_dept_id ON Teachers (dept_id);
CREATE INDEX idx_students_institution_id ON Students (institution_id);
CREATE INDEX idx_students_dept_id ON Students (dept_id);
CREATE INDEX idx_rooms_institution_id ON Rooms (institution_id);
CREATE INDEX idx_rooms_campus_id ON Rooms (campus_id);
CREATE INDEX idx_timeslots_institution_id ON TimeSlots (institution_id);
CREATE INDEX idx_timeslots_campus_id ON TimeSlots (campus_id);
CREATE INDEX idx_classgroups_institution_id ON ClassGroups (institution_id);
CREATE INDEX idx_classgroups_dept_id ON ClassGroups (dept_id);
CREATE INDEX idx_classgroupmembers_institution_id ON ClassGroupMembers (institution_id);
CREATE INDEX idx_classgroupmembers_class_group_id ON ClassGroupMembers (class_group_id);
CREATE INDEX idx_timetables_institution_id ON Timetables (institution_id);
CREATE INDEX idx_timetables_course_id ON Timetables (course_id);
CREATE INDEX idx_timetables_teacher_id ON Timetables (teacher_id);
CREATE INDEX idx_timetables_room_id ON Timetables (room_id);
CREATE INDEX idx_timetables_slot_id ON Timetables (slot_id);
CREATE INDEX idx_constraints_institution_id ON Constraints (institution_id);
CREATE INDEX idx_constraints_timetable_id ON Constraints (timetable_id);
CREATE INDEX idx_enrollments_institution_id ON Enrollments (institution_id);
CREATE INDEX idx_enrollments_student_id ON Enrollments (student_id);
CREATE INDEX idx_enrollments_timetable_id ON Enrollments (timetable_id);
CREATE INDEX idx_attendance_institution_id ON Attendance (institution_id);
CREATE INDEX idx_attendance_timetable_id ON Attendance (timetable_id);
CREATE INDEX idx_attendance_student_id ON Attendance (student_id);
CREATE INDEX idx_notifications_institution_id ON Notifications (institution_id);
CREATE INDEX idx_notifications_user_id ON Notifications (user_id);
CREATE INDEX idx_audits_institution_id ON Audits (institution_id);
CREATE INDEX idx_audits_timetable_id ON Audits (timetable_id);
CREATE INDEX idx_resources_institution_id ON Resources (institution_id);
CREATE INDEX idx_resources_campus_id ON Resources (campus_id);
CREATE INDEX idx_events_institution_id ON Events (institution_id);
CREATE INDEX idx_events_slot_id ON Events (slot_id);
CREATE INDEX idx_integrations_institution_id ON Integrations (integration_id);