-- =====================================================
-- MARKSHEET MODULE DATABASE ALTERATIONS
-- =====================================================

-- Create marksheet table for storing generated marksheets
CREATE TABLE IF NOT EXISTS `student_marksheets` (
  `marksheet_id` int(11) NOT NULL AUTO_INCREMENT,
  `folio_number` varchar(50) NOT NULL UNIQUE,
  `student_id` int(11) NOT NULL,
  `academic_year` varchar(50) NOT NULL,
  `semester` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `generated_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `generated_by` int(11) DEFAULT NULL,
  `status` enum('Generated','Printed','Reissued') DEFAULT 'Generated',
  `print_count` int(11) DEFAULT 0,
  `last_printed` datetime DEFAULT NULL,
  `marksheet_data` longtext DEFAULT NULL COMMENT 'JSON data of marksheet content',
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`marksheet_id`),
  UNIQUE KEY `uq_folio_number` (`folio_number`),
  KEY `idx_student_academic` (`student_id`, `academic_year`, `semester`),
  KEY `idx_course_semester` (`course_id`, `semester`),
  KEY `idx_folio_number` (`folio_number`),
  FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE CASCADE,
  FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ON DELETE CASCADE,
  FOREIGN KEY (`generated_by`) REFERENCES `staff` (`staff_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create folio number sequence table
CREATE TABLE IF NOT EXISTS `folio_sequence` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `academic_year` varchar(50) NOT NULL,
  `course_id` int(11) NOT NULL,
  `last_folio_number` int(11) DEFAULT 0,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_academic_course` (`academic_year`, `course_id`),
  FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add folio number to existing tables if needed
ALTER TABLE `students` 
ADD COLUMN IF NOT EXISTS `folio_prefix` varchar(10) DEFAULT NULL COMMENT 'Folio prefix for marksheet generation';

-- Create index for better performance
CREATE INDEX IF NOT EXISTS `idx_marksheet_search` ON `student_marksheets` (`student_id`, `academic_year`, `semester`, `course_id`);
CREATE INDEX IF NOT EXISTS `idx_marksheet_folio` ON `student_marksheets` (`folio_number`, `status`);

-- =====================================================
-- CONSOLIDATED MARKSHEET TABLES
-- =====================================================

CREATE TABLE IF NOT EXISTS `consolidated_folio_sequence` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `seq_year` varchar(9) NOT NULL,
  `course_code` varchar(20) NOT NULL,
  `next_number` int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_year_course` (`seq_year`, `course_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `consolidated_marksheets` (
  `consolidated_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `folio_number` varchar(50) NOT NULL,
  `marksheet_data` longtext NOT NULL COMMENT 'JSON data of consolidated marksheet',
  `generated_by` int(11) DEFAULT NULL,
  `generated_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`consolidated_id`),
  UNIQUE KEY `uq_student_course` (`student_id`, `course_id`),
  UNIQUE KEY `uq_consol_folio` (`folio_number`),
  FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE CASCADE,
  FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ON DELETE CASCADE,
  FOREIGN KEY (`generated_by`) REFERENCES `staff` (`staff_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- RESULT ANNOUNCEMENT WINDOWS
-- =====================================================

CREATE TABLE IF NOT EXISTS `result_announcements` (
  `announcement_id` int(11) NOT NULL AUTO_INCREMENT,
  `course_id` int(11) NOT NULL,
  `academic_year` varchar(50) NOT NULL,
  `exam_type_id` int(11) NOT NULL,
  `start_at` datetime NOT NULL,
  `end_at` datetime NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`announcement_id`),
  KEY `idx_course_year_type` (`course_id`,`academic_year`,`exam_type_id`,`is_active`),
  CONSTRAINT `fk_ra_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- REVALUATION APPLICATIONS
-- =====================================================

CREATE TABLE IF NOT EXISTS `revaluation_applications` (
  `application_id` int(11) NOT NULL AUTO_INCREMENT,
  `academic_year` varchar(50) NOT NULL,
  `course_id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `exam_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `applied_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('Applied','Processing','Completed','Rejected') NOT NULL DEFAULT 'Applied',
  `remarks` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`application_id`),
  KEY `idx_ay_student_subject` (`academic_year`,`student_id`,`subject_id`),
  CONSTRAINT `fk_rev_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rev_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rev_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rev_exam` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`exam_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Safe alters for existing installations (run only if needed)
-- NOTE: Some MySQL/MariaDB versions do not support IF NOT EXISTS for ADD COLUMN.
-- If columns already exist, these may error; you can skip the statements that fail with "Duplicate column".

-- Columns
ALTER TABLE `revaluation_applications` ADD COLUMN `student_id` int(11) NOT NULL AFTER `course_id`;
ALTER TABLE `revaluation_applications` ADD COLUMN `exam_id` int(11) NOT NULL AFTER `student_id`;
ALTER TABLE `revaluation_applications` ADD COLUMN `subject_id` int(11) NOT NULL AFTER `exam_id`;

-- Constraints
ALTER TABLE `revaluation_applications` ADD CONSTRAINT `fk_rev_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE CASCADE;
ALTER TABLE `revaluation_applications` ADD CONSTRAINT `fk_rev_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ON DELETE CASCADE;
ALTER TABLE `revaluation_applications` ADD CONSTRAINT `fk_rev_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`) ON DELETE CASCADE;
ALTER TABLE `revaluation_applications` ADD CONSTRAINT `fk_rev_exam` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`exam_id`) ON DELETE CASCADE;