-- ====================================================================================
-- MARK ENTRY MODULE - SEPARATE ALTER AND CREATE QUERIES
-- ====================================================================================
-- This file contains separate ALTER and CREATE queries for the Mark Entry Module
-- Run these queries to add the mark entry functionality to existing database

-- ====================================================================================
-- CREATE QUERIES (Run these to create new tables)
-- ====================================================================================

-- Module 21: Mark Entry Management System
-- Comprehensive mark entry system based on question patterns
-- Allows entry of marks for each question based on the question pattern defined for each subject

CREATE TABLE IF NOT EXISTS `mark_entry_sessions` (
  `session_id` INT NOT NULL AUTO_INCREMENT,
  `session_name` VARCHAR(255) NOT NULL,
  `exam_subject_id` INT NOT NULL,
  `pattern_id` INT NOT NULL,
  `academic_year` VARCHAR(50) NOT NULL,
  `exam_type_id` INT NOT NULL,
  `session_date` DATE NOT NULL,
  `status` ENUM('Draft','Active','Completed','Locked') NOT NULL DEFAULT 'Draft',
  `created_by` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`session_id`),
  KEY `idx_mes_exam_subject` (`exam_subject_id`),
  KEY `idx_mes_pattern` (`pattern_id`),
  KEY `idx_mes_created_by` (`created_by`),
  CONSTRAINT `fk_mes_exam_subject` FOREIGN KEY (`exam_subject_id`) REFERENCES `exam_subjects`(`exam_subject_id`),
  CONSTRAINT `fk_mes_pattern` FOREIGN KEY (`pattern_id`) REFERENCES `question_patterns`(`pattern_id`),
  CONSTRAINT `fk_mes_created_by` FOREIGN KEY (`created_by`) REFERENCES `staff`(`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mark_entry_assignments` (
  `assignment_id` INT NOT NULL AUTO_INCREMENT,
  `session_id` INT NOT NULL,
  `valuator_id` INT NOT NULL,
  `question_range_start` INT NOT NULL,
  `question_range_end` INT NOT NULL,
  `total_questions` INT NOT NULL,
  `status` ENUM('Assigned','In Progress','Completed','Reviewed') NOT NULL DEFAULT 'Assigned',
  `assigned_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `completed_at` TIMESTAMP NULL,
  PRIMARY KEY (`assignment_id`),
  KEY `idx_mea_session` (`session_id`),
  KEY `idx_mea_valuator` (`valuator_id`),
  CONSTRAINT `fk_mea_session` FOREIGN KEY (`session_id`) REFERENCES `mark_entry_sessions`(`session_id`),
  CONSTRAINT `fk_mea_valuator` FOREIGN KEY (`valuator_id`) REFERENCES `staff`(`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `student_marks_entry` (
  `entry_id` INT NOT NULL AUTO_INCREMENT,
  `session_id` INT NOT NULL,
  `registration_id` INT NOT NULL,
  `question_number` INT NOT NULL,
  `marks_obtained` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `max_marks` DECIMAL(5,2) NOT NULL,
  `valuator_id` INT NOT NULL,
  `entry_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `reviewed_by` INT NULL,
  `review_date` TIMESTAMP NULL,
  `status` ENUM('Entered','Reviewed','Approved','Rejected') NOT NULL DEFAULT 'Entered',
  `remarks` TEXT NULL,
  PRIMARY KEY (`entry_id`),
  UNIQUE KEY `uq_sme_session_registration_question` (`session_id`,`registration_id`,`question_number`),
  KEY `idx_sme_session` (`session_id`),
  KEY `idx_sme_registration` (`registration_id`),
  KEY `idx_sme_valuator` (`valuator_id`),
  KEY `idx_sme_reviewed_by` (`reviewed_by`),
  CONSTRAINT `fk_sme_session` FOREIGN KEY (`session_id`) REFERENCES `mark_entry_sessions`(`session_id`),
  CONSTRAINT `fk_sme_registration` FOREIGN KEY (`registration_id`) REFERENCES `student_registrations`(`registration_id`),
  CONSTRAINT `fk_sme_valuator` FOREIGN KEY (`valuator_id`) REFERENCES `staff`(`staff_id`),
  CONSTRAINT `fk_sme_reviewed_by` FOREIGN KEY (`reviewed_by`) REFERENCES `staff`(`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mark_entry_progress` (
  `progress_id` INT NOT NULL AUTO_INCREMENT,
  `session_id` INT NOT NULL,
  `valuator_id` INT NOT NULL,
  `total_students` INT NOT NULL DEFAULT 0,
  `students_entered` INT NOT NULL DEFAULT 0,
  `students_reviewed` INT NOT NULL DEFAULT 0,
  `total_questions` INT NOT NULL DEFAULT 0,
  `questions_entered` INT NOT NULL DEFAULT 0,
  `progress_percentage` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `last_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`progress_id`),
  UNIQUE KEY `uq_mep_session_valuator` (`session_id`,`valuator_id`),
  KEY `idx_mep_session` (`session_id`),
  KEY `idx_mep_valuator` (`valuator_id`),
  CONSTRAINT `fk_mep_session` FOREIGN KEY (`session_id`) REFERENCES `mark_entry_sessions`(`session_id`),
  CONSTRAINT `fk_mep_valuator` FOREIGN KEY (`valuator_id`) REFERENCES `staff`(`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================================================================
-- SAMPLE DATA INSERT QUERIES
-- ====================================================================================

-- Insert sample mark entry sessions
INSERT INTO `mark_entry_sessions` (`session_name`, `exam_subject_id`, `pattern_id`, `academic_year`, `exam_type_id`, `session_date`, `status`, `created_by`) VALUES
('Data Structures - SEM Exam - 2024', 1, 1, '2000-2001 Odd', 1, '2024-01-15', 'Active', 1),
('Database Systems - SEM Exam - 2024', 2, 2, '2000-2001 Odd', 1, '2024-01-16', 'Active', 1),
('Computer Networks - C1 Exam - 2024', 3, 3, '2000-2001 Odd', 2, '2024-01-17', 'Draft', 1);

-- Insert sample mark entry assignments
INSERT INTO `mark_entry_assignments` (`session_id`, `valuator_id`, `question_range_start`, `question_range_end`, `total_questions`, `status`) VALUES
(1, 1, 1, 5, 5, 'In Progress'),
(1, 2, 6, 10, 5, 'Assigned'),
(2, 1, 1, 8, 8, 'In Progress'),
(2, 3, 9, 16, 8, 'Assigned');

-- Insert sample student marks entry
INSERT INTO `student_marks_entry` (`session_id`, `registration_id`, `question_number`, `marks_obtained`, `max_marks`, `valuator_id`, `status`) VALUES
(1, 1, 1, 8.50, 10.00, 1, 'Entered'),
(1, 1, 2, 9.00, 10.00, 1, 'Entered'),
(1, 1, 3, 7.50, 10.00, 1, 'Entered'),
(1, 2, 1, 9.50, 10.00, 1, 'Entered'),
(1, 2, 2, 8.00, 10.00, 1, 'Entered'),
(1, 2, 3, 9.00, 10.00, 1, 'Entered');

-- Insert sample progress tracking
INSERT INTO `mark_entry_progress` (`session_id`, `valuator_id`, `total_students`, `students_entered`, `students_reviewed`, `total_questions`, `questions_entered`, `progress_percentage`) VALUES
(1, 1, 50, 25, 10, 5, 125, 50.00),
(1, 2, 50, 0, 0, 5, 0, 0.00),
(2, 1, 40, 20, 5, 8, 160, 50.00);

-- ====================================================================================
-- NOTES
-- ====================================================================================
-- 1. Run the CREATE TABLE queries first to create the new tables
-- 2. Run the INSERT queries to add sample data for testing
-- 3. Make sure the referenced tables (exam_subjects, question_patterns, staff, student_registrations) exist
-- 4. Adjust the sample data values according to your actual data
-- 5. The system supports multiple valuators per session with question range assignments
-- 6. Progress tracking is automatically updated when marks are entered
-- ====================================================================================