-- Add session_id column to students table
-- This allows filtering students by academic session

-- Check if column doesn't exist and add it
ALTER TABLE `students` 
ADD COLUMN `session_id` INT NULL 
COMMENT 'Academic session/year' 
AFTER `class_id`;

-- Add foreign key constraint
ALTER TABLE `students` 
ADD CONSTRAINT `fk_students_session` 
FOREIGN KEY (`session_id`) REFERENCES `academic_sessions`(`id`) 
ON DELETE SET NULL 
ON UPDATE CASCADE;

-- Add index for better query performance
ALTER TABLE `students` 
ADD INDEX `idx_session_id` (`session_id`);

-- Optional: Set current session for existing students
-- Update students to use the current active session
UPDATE `students` 
SET `session_id` = (
    SELECT `id` FROM `academic_sessions` 
    WHERE `is_current` = 1 
    LIMIT 1
)
WHERE `session_id` IS NULL AND `status` = 'active';

-- Verify the changes
SELECT 
    s.id,
    s.student_id,
    s.first_name,
    s.last_name,
    c.name as class_name,
    ses.session_name,
    s.status
FROM students s
LEFT JOIN classes c ON s.class_id = c.id
LEFT JOIN academic_sessions ses ON s.session_id = ses.id
LIMIT 10;

-- Show success message
SELECT 'session_id column added successfully to students table!' as message;
