USE school_management;

CREATE TABLE IF NOT EXISTS student_subjects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    subject_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT 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)
);

-- Insert some sample subjects if they don't exist
INSERT IGNORE INTO subjects (name, code) VALUES 
('বাংলা', 'BAN'),
('ইংরেজি', 'ENG'),
('গণিত', 'MATH'),
('বিজ্ঞান', 'SCI'),
('সমাজবিজ্ঞান', 'SOC'),
('ধর্ম', 'REL'),
('শারীরিক শিক্ষা', 'PE'),
('কম্পিউটার', 'COMP');

-- Insert some sample class-subject mappings
INSERT IGNORE INTO class_subjects (class_id, subject_id) VALUES 
(1, 1), (1, 2), (1, 3), (1, 7),
(2, 1), (2, 2), (2, 3), (2, 7),
(3, 1), (3, 2), (3, 3), (3, 4), (3, 7),
(4, 1), (4, 2), (4, 3), (4, 4), (4, 5), (4, 7),
(5, 1), (5, 2), (5, 3), (5, 4), (5, 5), (5, 6), (5, 7);