-- Diagnostic script: Check current structure of exams table
-- Run this first to see what already exists

-- Check if academic_year_id column exists
SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    IS_NULLABLE, 
    COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'exams' 
AND COLUMN_NAME = 'academic_year_id';

-- Check all columns in exams table
SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    IS_NULLABLE, 
    COLUMN_DEFAULT,
    ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'exams' 
ORDER BY ORDINAL_POSITION;

-- Check existing indexes on exams table
SELECT 
    INDEX_NAME, 
    INDEX_TYPE, 
    COLUMN_NAME
FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'exams' 
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

-- Check existing 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 = 'exams' 
AND CONSTRAINT_NAME != 'PRIMARY';

-- Check sample data in exams table
SELECT 
    exam_id, 
    exam_name, 
    academic_year, 
    academic_year_id,
    semester 
FROM exams 
LIMIT 5;