-- =====================================================
-- MARKSHEET MODULE DATABASE - SIMPLIFIED VERSION
-- =====================================================

-- 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`)
) 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`)
) 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`);