-- ====================================================================================
-- SIMPLE VALUATION ALLOCATION SYSTEM - DATABASE TABLES
-- ====================================================================================

-- Enhanced Valuation Rooms Table with Paper Capacity Settings
CREATE TABLE IF NOT EXISTS `valuation_rooms_enhanced` (
  `room_id` INT NOT NULL AUTO_INCREMENT,
  `room_name` VARCHAR(100) NOT NULL,
  `room_code` VARCHAR(20) NOT NULL,
  `incharge_id` INT NOT NULL COMMENT 'Supervisor/Incharge staff member',
  `max_papers_per_day` INT NOT NULL DEFAULT 50 COMMENT 'Maximum papers that can be evaluated per day',
  `current_paper_count` INT NOT NULL DEFAULT 0 COMMENT 'Current papers allocated to this room',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Room availability status',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`room_id`),
  UNIQUE KEY `uq_room_code` (`room_code`),
  KEY `idx_vre_incharge_id` (`incharge_id`),
  KEY `idx_vre_active` (`is_active`),
  CONSTRAINT `fk_vre_incharge` FOREIGN KEY (`incharge_id`) REFERENCES `staff`(`staff_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Enhanced valuation rooms with paper capacity management';

-- Valuation Allocation Sessions Table
CREATE TABLE IF NOT EXISTS `valuation_sessions` (
  `session_id` INT NOT NULL AUTO_INCREMENT,
  `exam_id` INT NOT NULL COMMENT 'Exam for which valuation is being conducted',
  `session_name` VARCHAR(100) NOT NULL COMMENT 'Name of the valuation session',
  `start_date` DATE NOT NULL COMMENT 'Valuation start date',
  `end_date` DATE NOT NULL COMMENT 'Valuation end date',
  `total_papers` INT NOT NULL DEFAULT 0 COMMENT 'Total papers to be evaluated',
  `allocated_papers` INT NOT NULL DEFAULT 0 COMMENT 'Papers already allocated to rooms',
  `status` ENUM('Planning','In Progress','Completed','Cancelled') NOT NULL DEFAULT 'Planning',
  `created_by` INT NOT NULL COMMENT 'Staff member who created the session',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`session_id`),
  KEY `idx_vs_exam_id` (`exam_id`),
  KEY `idx_vs_status` (`status`),
  KEY `idx_vs_dates` (`start_date`, `end_date`),
  CONSTRAINT `fk_vs_exam` FOREIGN KEY (`exam_id`) REFERENCES `exams`(`exam_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_vs_created_by` FOREIGN KEY (`created_by`) REFERENCES `staff`(`staff_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Valuation sessions for exam papers';

-- Enhanced Valuator Allocation Table
CREATE TABLE IF NOT EXISTS `valuator_allocations_enhanced` (
  `allocation_id` INT NOT NULL AUTO_INCREMENT,
  `session_id` INT NOT NULL COMMENT 'Valuation session this allocation belongs to',
  `exam_subject_id` INT NOT NULL COMMENT 'Subject being evaluated',
  `valuator_id` INT NOT NULL COMMENT 'Staff member assigned as valuator',
  `room_id` INT NOT NULL COMMENT 'Room where valuation will take place',
  `supervisor_id` INT NOT NULL COMMENT 'Supervisor for this room (usually room incharge)',
  `num_scripts` INT NOT NULL COMMENT 'Number of scripts allocated to this valuator',
  `num_scripts_completed` INT NOT NULL DEFAULT 0 COMMENT 'Scripts completed by valuator',
  `allocation_date` DATE NOT NULL COMMENT 'Date when papers are allocated',
  `expected_completion_date` DATE NOT NULL COMMENT 'Expected completion date',
  `rate_per_script` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Rate per script for valuation',
  `total_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Total amount for this allocation',
  `status` ENUM('Allocated','In Progress','Completed','Delayed') NOT NULL DEFAULT 'Allocated',
  `remarks` TEXT NULL COMMENT 'Additional remarks or notes',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`allocation_id`),
  KEY `idx_vae_session_id` (`session_id`),
  KEY `idx_vae_exam_subject_id` (`exam_subject_id`),
  KEY `idx_vae_valuator_id` (`valuator_id`),
  KEY `idx_vae_room_id` (`room_id`),
  KEY `idx_vae_supervisor_id` (`supervisor_id`),
  KEY `idx_vae_status` (`status`),
  CONSTRAINT `fk_vae_session` FOREIGN KEY (`session_id`) REFERENCES `valuation_sessions`(`session_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_vae_exam_subject` FOREIGN KEY (`exam_subject_id`) REFERENCES `exam_subjects`(`exam_subject_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_vae_valuator` FOREIGN KEY (`valuator_id`) REFERENCES `staff`(`staff_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_vae_room` FOREIGN KEY (`room_id`) REFERENCES `valuation_rooms_enhanced`(`room_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_vae_supervisor` FOREIGN KEY (`supervisor_id`) REFERENCES `staff`(`staff_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Enhanced valuator allocations with session management';

-- Paper Allocation Details Table
CREATE TABLE IF NOT EXISTS `paper_allocations` (
  `allocation_detail_id` INT NOT NULL AUTO_INCREMENT,
  `valuator_allocation_id` INT NOT NULL COMMENT 'Parent valuator allocation',
  `registration_id` INT NOT NULL COMMENT 'Student registration for the paper',
  `paper_number` VARCHAR(20) NOT NULL COMMENT 'Paper number or identifier',
  `allocation_date` DATE NOT NULL COMMENT 'Date when paper was allocated',
  `status` ENUM('Allocated','In Progress','Completed','Returned') NOT NULL DEFAULT 'Allocated',
  `marks_entered_at` TIMESTAMP NULL COMMENT 'When marks were entered',
  `marks_verified_at` TIMESTAMP NULL COMMENT 'When marks were verified',
  `remarks` TEXT NULL COMMENT 'Valuation remarks',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`allocation_detail_id`),
  UNIQUE KEY `uq_paper_registration` (`registration_id`),
  KEY `idx_pa_valuator_allocation_id` (`valuator_allocation_id`),
  KEY `idx_pa_status` (`status`),
  CONSTRAINT `fk_pa_valuator_allocation` FOREIGN KEY (`valuator_allocation_id`) REFERENCES `valuator_allocations_enhanced`(`allocation_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pa_registration` FOREIGN KEY (`registration_id`) REFERENCES `student_registrations`(`registration_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Individual paper allocation details';

-- Valuation Progress Tracking Table
CREATE TABLE IF NOT EXISTS `valuation_progress` (
  `progress_id` INT NOT NULL AUTO_INCREMENT,
  `session_id` INT NOT NULL COMMENT 'Valuation session',
  `room_id` INT NOT NULL COMMENT 'Room being tracked',
  `tracking_date` DATE NOT NULL COMMENT 'Date of progress tracking',
  `papers_allocated` INT NOT NULL DEFAULT 0 COMMENT 'Papers allocated to room on this date',
  `papers_completed` INT NOT NULL DEFAULT 0 COMMENT 'Papers completed on this date',
  `papers_pending` INT NOT NULL DEFAULT 0 COMMENT 'Papers pending in room',
  `valuators_present` INT NOT NULL DEFAULT 0 COMMENT 'Number of valuators present',
  `supervisor_present` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Whether supervisor was present',
  `remarks` TEXT NULL COMMENT 'Progress remarks',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`progress_id`),
  UNIQUE KEY `uq_progress_session_room_date` (`session_id`, `room_id`, `tracking_date`),
  KEY `idx_vp_session_id` (`session_id`),
  KEY `idx_vp_room_id` (`room_id`),
  CONSTRAINT `fk_vp_session` FOREIGN KEY (`session_id`) REFERENCES `valuation_sessions`(`session_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_vp_room` FOREIGN KEY (`room_id`) REFERENCES `valuation_rooms_enhanced`(`room_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Daily progress tracking for valuation sessions';

-- ====================================================================================
-- INDEXES FOR PERFORMANCE OPTIMIZATION
-- ====================================================================================

-- Additional indexes for better query performance
CREATE INDEX `idx_vae_allocation_date` ON `valuator_allocations_enhanced`(`allocation_date`);
CREATE INDEX `idx_vae_completion_date` ON `valuator_allocations_enhanced`(`expected_completion_date`);
CREATE INDEX `idx_pa_allocation_date` ON `paper_allocations`(`allocation_date`);
CREATE INDEX `idx_pa_marks_entered` ON `paper_allocations`(`marks_entered_at`);

-- ====================================================================================
-- SAMPLE DATA FOR TESTING
-- ====================================================================================

-- Insert sample valuation rooms
INSERT INTO `valuation_rooms_enhanced` (`room_name`, `room_code`, `incharge_id`, `max_papers_per_day`, `is_active`) VALUES
('Valuation Room 1', 'VR001', 1, 50, 1),
('Valuation Room 2', 'VR002', 2, 75, 1),
('Valuation Room 3', 'VR003', 3, 60, 1),
('Main Hall Valuation', 'VR004', 4, 100, 1),
('Computer Lab Valuation', 'VR005', 5, 40, 1);

-- ====================================================================================
-- VIEWS FOR REPORTING
-- ====================================================================================

-- View for valuation session summary
CREATE VIEW `valuation_session_summary` AS
SELECT 
    vs.session_id,
    vs.session_name,
    e.exam_name,
    e.academic_year,
    vs.start_date,
    vs.end_date,
    vs.total_papers,
    vs.allocated_papers,
    (vs.total_papers - vs.allocated_papers) as pending_papers,
    vs.status,
    CONCAT(s.first_name, ' ', s.last_name) as created_by_name,
    COUNT(DISTINCT vae.room_id) as rooms_used,
    COUNT(DISTINCT vae.valuator_id) as valuators_assigned
FROM valuation_sessions vs
JOIN exams e ON vs.exam_id = e.exam_id
JOIN staff s ON vs.created_by = s.staff_id
LEFT JOIN valuator_allocations_enhanced vae ON vs.session_id = vae.session_id
GROUP BY vs.session_id;

-- View for room allocation summary
CREATE VIEW `room_allocation_summary` AS
SELECT 
    vre.room_id,
    vre.room_name,
    vre.room_code,
    vre.max_papers_per_day,
    vre.current_paper_count,
    (vre.max_papers_per_day - vre.current_paper_count) as available_capacity,
    CONCAT(s.first_name, ' ', s.last_name) as supervisor_name,
    s.staff_code as supervisor_code,
    COUNT(DISTINCT vae.allocation_id) as active_allocations,
    SUM(vae.num_scripts) as total_papers_allocated,
    SUM(vae.num_scripts_completed) as total_papers_completed
FROM valuation_rooms_enhanced vre
JOIN staff s ON vre.incharge_id = s.staff_id
LEFT JOIN valuator_allocations_enhanced vae ON vre.room_id = vae.room_id 
    AND vae.status IN ('Allocated', 'In Progress')
GROUP BY vre.room_id;

-- ====================================================================================
-- COMMENTS AND DOCUMENTATION
-- ====================================================================================

/*
SIMPLE VALUATION ALLOCATION SYSTEM OVERVIEW:

This system provides comprehensive management of exam paper valuation allocation
with the following key features:

1. ROOM MANAGEMENT:
   - Enhanced valuation rooms with paper capacity settings
   - Supervisor assignment for each room
   - Real-time paper count tracking (handled in application code)

2. SESSION MANAGEMENT:
   - Valuation sessions linked to specific exams
   - Start and end date tracking
   - Progress monitoring

3. ALLOCATION MANAGEMENT:
   - Automatic paper allocation to rooms (handled in PHP)
   - Valuator assignment with supervisor oversight
   - Rate and payment tracking

4. PROGRESS TRACKING:
   - Daily progress monitoring
   - Completion status tracking
   - Performance metrics

5. REPORTING:
   - Session summaries
   - Room utilization reports
   - Valuator performance tracking

USAGE WORKFLOW:
1. Create valuation rooms with capacity settings
2. Assign supervisors to rooms
3. Create valuation session for an exam
4. Auto-allocate papers to rooms based on capacity (via PHP)
5. Assign valuators to rooms
6. Track daily progress
7. Generate reports

TABLES RELATIONSHIPS:
- valuation_sessions (1) ←→ (N) valuator_allocations_enhanced
- valuation_rooms_enhanced (1) ←→ (N) valuator_allocations_enhanced
- valuator_allocations_enhanced (1) ←→ (N) paper_allocations
- staff (1) ←→ (N) valuator_allocations_enhanced (as valuator)
- staff (1) ←→ (N) valuator_allocations_enhanced (as supervisor)
- staff (1) ←→ (N) valuation_rooms_enhanced (as incharge)

PERFORMANCE OPTIMIZATION:
- Indexed on allocation_id, timetable_id, hall_id
- Indexed on registration_id, seat_number
- CASCADE DELETE for data integrity
- Optimized queries for large datasets

NOTE: This simplified version removes complex triggers and stored procedures.
All business logic is handled in the PHP application code for better maintainability.
*/