-- ========================================
-- ADD MISSING INDEXES FOR PERFORMANCE
-- ========================================
-- These indexes optimize the most frequently queried columns
-- Run this script to improve query performance

-- Attendance table indexes
ALTER TABLE `attendance` ADD INDEX idx_student_id (student_id);
ALTER TABLE `attendance` ADD INDEX idx_student_session_term (student_id, session_year, term);
ALTER TABLE `attendance` ADD INDEX idx_attendance_date (attendance_date);

-- Results table indexes
ALTER TABLE `results` ADD INDEX idx_student_id (student_id);
ALTER TABLE `results` ADD INDEX idx_student_session_term (student_id, session_id, session_year, term);
ALTER TABLE `results` ADD INDEX idx_subject_id (subject_id);
ALTER TABLE `results` ADD INDEX idx_session_id (session_id);

-- Fees table indexes
ALTER TABLE `fees` ADD INDEX idx_student_id (student_id);
ALTER TABLE `fees` ADD INDEX idx_student_session_term (student_id, session_year, term);
ALTER TABLE `fees` ADD INDEX idx_session_year_term (session_year, term);

-- Discipline records table indexes
ALTER TABLE `discipline_records` ADD INDEX idx_student_id (student_id);
ALTER TABLE `discipline_records` ADD INDEX idx_incident_date (incident_date);
ALTER TABLE `discipline_records` ADD INDEX idx_reported_by (reported_by);

-- Assignments table indexes
ALTER TABLE `assignments` ADD INDEX idx_class_id (class_id);
ALTER TABLE `assignments` ADD INDEX idx_subject_id (subject_id);
ALTER TABLE `assignments` ADD INDEX idx_teacher_id (teacher_id);
ALTER TABLE `assignments` ADD INDEX idx_due_date (due_date);

-- Assignment submissions table indexes
ALTER TABLE `assignment_submissions` ADD INDEX idx_student_id (student_id);
ALTER TABLE `assignment_submissions` ADD INDEX idx_assignment_id (assignment_id);
ALTER TABLE `assignment_submissions` ADD INDEX idx_student_assignment (student_id, assignment_id);
ALTER TABLE `assignment_submissions` ADD INDEX idx_submission_date (submission_date);

-- Library borrowing table indexes
ALTER TABLE `library_borrowing` ADD INDEX idx_student_id (student_id);
ALTER TABLE `library_borrowing` ADD INDEX idx_book_id (book_id);
ALTER TABLE `library_borrowing` ADD INDEX idx_borrow_date (borrow_date);
ALTER TABLE `library_borrowing` ADD INDEX idx_status (status);

-- Messages table indexes
ALTER TABLE `messages` ADD INDEX idx_sender_id (sender_id);
ALTER TABLE `messages` ADD INDEX idx_receiver_id (receiver_id);
ALTER TABLE `messages` ADD INDEX idx_receiver_sender (receiver_id, sender_id);
ALTER TABLE `messages` ADD INDEX idx_sent_date (sent_date);

-- Timetable indexes
ALTER TABLE `timetable` ADD INDEX idx_class_id (class_id);
ALTER TABLE `timetable` ADD INDEX idx_teacher_id (teacher_id);
ALTER TABLE `timetable` ADD INDEX idx_subject_id (subject_id);
ALTER TABLE `timetable` ADD INDEX idx_day_time (day_of_week, start_time);

-- Subjects table indexes
ALTER TABLE `subjects` ADD INDEX idx_class_id (class_id);
ALTER TABLE `subjects` ADD INDEX idx_teacher_id (teacher_id);

-- Students table indexes
ALTER TABLE `students` ADD INDEX idx_class_id (class_id);
ALTER TABLE `students` ADD INDEX idx_admission_number (admission_number);

-- Academic sessions indexes
ALTER TABLE `academic_sessions` ADD INDEX idx_session_year_term (session_year, term);
ALTER TABLE `academic_sessions` ADD INDEX idx_is_current (is_current);

-- Extracurricular activities indexes
ALTER TABLE `extracurricular_activities` ADD INDEX idx_session_year (session_year);
ALTER TABLE `extracurricular_activities` ADD INDEX idx_is_active (is_active);

-- Activity participation indexes
ALTER TABLE `activity_participation` ADD INDEX idx_student_id (student_id);
ALTER TABLE `activity_participation` ADD INDEX idx_activity_id (activity_id);
ALTER TABLE `activity_participation` ADD INDEX idx_student_activity (student_id, activity_id);

-- Events table indexes
ALTER TABLE `events` ADD INDEX idx_event_date (event_date);
ALTER TABLE `events` ADD INDEX idx_session_year_term (session_year, term);
ALTER TABLE `events` ADD INDEX idx_is_public (is_public);

-- Announcements table indexes
ALTER TABLE `announcements` ADD INDEX idx_target_class_id (target_class_id);
ALTER TABLE `announcements` ADD INDEX idx_expires_at (expires_at);
ALTER TABLE `announcements` ADD INDEX idx_class_expires (target_class_id, expires_at);

-- ========================================
-- COMPOSITE INDEXES FOR COMPLEX QUERIES
-- ========================================

-- For filtering by student and date range
ALTER TABLE `attendance` ADD INDEX idx_student_date_range (student_id, attendance_date, status);

-- For getting student assignments with status
ALTER TABLE `assignment_submissions` ADD INDEX idx_student_status (student_id, submission_status, submission_date);

-- For library overdue books lookup
ALTER TABLE `library_borrowing` ADD INDEX idx_due_and_status (due_date, status);

-- For message filtering
ALTER TABLE `messages` ADD INDEX idx_user_date (receiver_id, sent_date DESC);

-- ========================================
-- VERIFICATION QUERIES
-- ========================================
-- Run these to verify indexes were created:
-- SHOW INDEX FROM attendance;
-- SHOW INDEX FROM results;
-- SHOW INDEX FROM fees;
-- SELECT COUNT(*) as index_count FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'leotwcun_leotrix';