-- Direct SQL to create exam attendance table
-- Copy and paste this into phpMyAdmin or MySQL command line

-- First create student_registrations table if it doesn't exist
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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create the main attendance table
CREATE TABLE IF NOT EXISTS `tbl_exam_attendance` (
  `attendance_id` INT NOT NULL AUTO_INCREMENT,
  `registration_id` INT NOT NULL,
  `timetable_id` INT NOT NULL,
  `attendance_status` ENUM('Present','Absent') NOT NULL DEFAULT 'Present',
  `marked_by` INT NULL,
  `marked_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `remarks` TEXT NULL,
  `is_late` TINYINT(1) NOT NULL DEFAULT 0,
  `late_minutes` INT NULL,
  PRIMARY KEY (`attendance_id`),
  UNIQUE KEY `uq_attendance_registration` (`registration_id`),
  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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Verify the table was created
SELECT 'SUCCESS: tbl_exam_attendance table created' as status;
SHOW TABLES LIKE 'tbl_exam_attendance';
DESCRIBE tbl_exam_attendance;