-- Fix Foreign Key Issue for Exam Attendance Module
-- This script creates the missing student_registrations table and then creates the exam attendance table

-- Step 1: Create the missing student_registrations table
CREATE TABLE IF NOT EXISTS `student_registrations` (
  `registration_id` INT NOT NULL AUTO_INCREMENT,
  `student_id` INT NOT NULL,
  `exam_subject_id` INT NOT NULL,
  `is_arrear` TINYINT(1) NOT NULL DEFAULT 0,
  `fee_structure_id` INT NULL,
  `fee_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `fee_status` ENUM('Paid','Pending') NOT NULL DEFAULT 'Pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`registration_id`),
  UNIQUE KEY `uq_student_exam_subject` (`student_id`,`exam_subject_id`),
  KEY `idx_sr_student_id` (`student_id`),
  KEY `idx_sr_exam_subject_id` (`exam_subject_id`),
  KEY `idx_sr_fee_structure_id` (`fee_structure_id`),
  CONSTRAINT `fk_sr_student` FOREIGN KEY (`student_id`) REFERENCES `students`(`student_id`),
  CONSTRAINT `fk_sr_exam_subject` FOREIGN KEY (`exam_subject_id`) REFERENCES `exam_subjects`(`exam_subject_id`),
  CONSTRAINT `fk_sr_fee_structure` FOREIGN KEY (`fee_structure_id`) REFERENCES `course_fee_structure`(`fee_structure_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 2: Create the exam attendance table
CREATE TABLE IF NOT EXISTS `tbl_exam_attendance` (
  `attendance_id` INT NOT NULL AUTO_INCREMENT,
  `registration_id` INT NOT NULL COMMENT 'Links to student_registrations for student and exam details',
  `timetable_id` INT NOT NULL COMMENT 'Links to exam_timetable for exam session details',
  `attendance_status` ENUM('Present','Absent') NOT NULL DEFAULT 'Present' COMMENT 'Student attendance status',
  `marked_by` INT NULL COMMENT 'Staff member who marked the attendance',
  `marked_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'When attendance was marked',
  `remarks` TEXT NULL COMMENT 'Additional remarks for absence',
  `is_late` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Whether student was late',
  `late_minutes` INT NULL COMMENT 'Minutes late if applicable',
  PRIMARY KEY (`attendance_id`),
  UNIQUE KEY `uq_attendance_registration` (`registration_id`) COMMENT 'One attendance record per registration',
  KEY `idx_ea_timetable_id` (`timetable_id`),
  KEY `idx_ea_attendance_status` (`attendance_status`),
  KEY `idx_ea_marked_by` (`marked_by`),
  KEY `idx_ea_marked_at` (`marked_at`),
  CONSTRAINT `fk_ea_registration` FOREIGN KEY (`registration_id`) REFERENCES `student_registrations`(`registration_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ea_timetable` FOREIGN KEY (`timetable_id`) REFERENCES `exam_timetable`(`timetable_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ea_marked_by` FOREIGN KEY (`marked_by`) REFERENCES `staff`(`staff_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Exam attendance tracking for students';

-- Step 3: Create triggers for automatic marks handling
DELIMITER $$

CREATE TRIGGER `update_marks_on_absent` 
AFTER INSERT ON `tbl_exam_attendance`
FOR EACH ROW
BEGIN
    -- If student is marked absent, update their marks summary
    IF NEW.attendance_status = 'Absent' THEN
        UPDATE student_marks_summary 
        SET 
            attendance_status = 'Absent',
            result = 'Absent',
            marks_external = 0,
            marks_total = COALESCE(marks_internal, 0),
            grade_id = NULL,
            grade_point = NULL
        WHERE registration_id = NEW.registration_id;
        
        -- Also update any existing question marks to 0
        UPDATE student_question_marks 
        SET marks_obtained = 0
        WHERE registration_id = NEW.registration_id;
    END IF;
END$$

CREATE TRIGGER `update_marks_on_present` 
AFTER UPDATE ON `tbl_exam_attendance`
FOR EACH ROW
BEGIN
    -- If student status changes from Absent to Present
    IF OLD.attendance_status = 'Absent' AND NEW.attendance_status = 'Present' THEN
        UPDATE student_marks_summary 
        SET attendance_status = 'Present'
        WHERE registration_id = NEW.registration_id;
    END IF;
    
    -- If student status changes from Present to Absent
    IF OLD.attendance_status = 'Present' AND NEW.attendance_status = 'Absent' THEN
        UPDATE student_marks_summary 
        SET 
            attendance_status = 'Absent',
            result = 'Absent',
            marks_external = 0,
            marks_total = COALESCE(marks_internal, 0),
            grade_id = NULL,
            grade_point = NULL
        WHERE registration_id = NEW.registration_id;
        
        -- Also update any existing question marks to 0
        UPDATE student_question_marks 
        SET marks_obtained = 0
        WHERE registration_id = NEW.registration_id;
    END IF;
END$$

DELIMITER ;

-- Step 4: Create indexes for better performance
CREATE INDEX idx_ea_exam_date ON tbl_exam_attendance (marked_at);
CREATE INDEX idx_ea_status_date ON tbl_exam_attendance (attendance_status, marked_at);

-- Success message
SELECT 'Exam Attendance Module tables and triggers created successfully!' as message;