-- Database Alter Queries for Autonomous College System
-- These queries ensure the database structure matches the SQL file exactly

-- =====================================================
-- ALTER QUERIES FOR student_registrations TABLE
-- =====================================================

-- Note: Based on analysis, the current database structure is already optimal
-- These queries are provided for reference only

-- 1. Rename unique constraint to match SQL file (if needed)
-- ALTER TABLE `student_registrations` DROP INDEX `uq_unique_registration`;
-- ALTER TABLE `student_registrations` ADD UNIQUE KEY `uq_student_exam_subject` (`student_id`,`exam_subject_id`);

-- 2. Ensure created_at column exists (already exists)
-- ALTER TABLE `student_registrations` ADD COLUMN `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- 3. Ensure updated_at column exists (already exists)
-- ALTER TABLE `student_registrations` ADD COLUMN `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- =====================================================
-- VERIFICATION QUERIES
-- =====================================================

-- Check current table structure
-- DESCRIBE `student_registrations`;

-- Check indexes and constraints
-- SHOW INDEX FROM `student_registrations`;

-- Check foreign key constraints
-- SELECT 
--     CONSTRAINT_NAME,
--     COLUMN_NAME,
--     REFERENCED_TABLE_NAME,
--     REFERENCED_COLUMN_NAME
-- FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
-- WHERE TABLE_SCHEMA = DATABASE() 
-- AND TABLE_NAME = 'student_registrations' 
-- AND REFERENCED_TABLE_NAME IS NOT NULL;

-- =====================================================
-- OPTIONAL ENHANCEMENTS (if needed in future)
-- =====================================================

-- Add index for better performance on fee queries
-- ALTER TABLE `student_registrations` ADD INDEX `idx_sr_fee_status` (`fee_status`);

-- Add index for better performance on arrear queries
-- ALTER TABLE `student_registrations` ADD INDEX `idx_sr_is_arrear` (`is_arrear`);

-- Add composite index for common queries
-- ALTER TABLE `student_registrations` ADD INDEX `idx_sr_student_arrear` (`student_id`, `is_arrear`);

-- =====================================================
-- CLEANUP QUERIES (if needed)
-- =====================================================

-- Remove duplicate exams (if any exist)
-- DELETE e1 FROM exams e1
-- INNER JOIN exams e2 
-- WHERE e1.exam_id > e2.exam_id 
-- AND e1.exam_type_id = e2.exam_type_id 
-- AND e1.academic_year = e2.academic_year 
-- AND e1.semester = e2.semester;

-- Clean up orphaned exam_subjects
-- DELETE es FROM exam_subjects es
-- LEFT JOIN exams e ON es.exam_id = e.exam_id
-- WHERE e.exam_id IS NULL;

-- =====================================================
-- STATUS CHECK QUERIES
-- =====================================================

-- Check for duplicate student registrations
-- SELECT student_id, exam_subject_id, COUNT(*) as count
-- FROM student_registrations
-- GROUP BY student_id, exam_subject_id
-- HAVING COUNT(*) > 1;

-- Check for orphaned registrations
-- SELECT sr.*
-- FROM student_registrations sr
-- LEFT JOIN exam_subjects es ON sr.exam_subject_id = es.exam_subject_id
-- WHERE es.exam_subject_id IS NULL;

-- Check exam generation status
-- SELECT 
--     e.exam_id,
--     e.exam_name,
--     e.academic_year,
--     e.semester,
--     COUNT(es.exam_subject_id) as subject_count
-- FROM exams e
-- LEFT JOIN exam_subjects es ON e.exam_id = es.exam_id
-- WHERE e.academic_year LIKE '%2000-2001%'
-- GROUP BY e.exam_id
-- ORDER BY e.academic_year, e.semester;