-- Add fourth subject (optional subject) support for higher classes
-- This script adds a column to track which subject is the student's fourth/optional subject

USE school_management;

-- Check if student_subjects table exists, if not create it
CREATE TABLE IF NOT EXISTS student_subjects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    subject_id INT NOT NULL,
    is_fourth_subject TINYINT(1) DEFAULT 0 COMMENT 'Marks subject as fourth/optional - failure in this subject does not cause overall failure',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
    UNIQUE KEY unique_student_subject (student_id, subject_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- If the table exists but doesn't have the column, add it
ALTER TABLE student_subjects 
ADD COLUMN IF NOT EXISTS is_fourth_subject TINYINT(1) DEFAULT 0 COMMENT 'Marks subject as fourth/optional - failure in this subject does not cause overall failure';

-- Add updated_at if it doesn't exist
ALTER TABLE student_subjects 
ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Create index for faster queries
CREATE INDEX IF NOT EXISTS idx_fourth_subject ON student_subjects(student_id, is_fourth_subject);

-- Display success message
SELECT 'Fourth subject column added successfully!' as message;
