-- Create database if not exists
CREATE DATABASE IF NOT EXISTS student_management;

-- Use the student_management database
USE student_management;
-- Create Courses table (linked to instructors)
CREATE TABLE IF NOT EXISTS Courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(255) NOT NULL,
    course_code VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    capacity INT NOT NULL DEFAULT 8,
    enrolled INT DEFAULT 0,
    instructor_id INT,
    FOREIGN KEY (instructor_id) REFERENCES Instructors(instructor_id) ON DELETE SET NULL
);
--Create Instructors Course table
CREATE TABLE IF NOT EXISTS instructor_courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    instructor_id INT NOT NULL,
    course_id INT NOT NULL,
    FOREIGN KEY (instructor_id) REFERENCES instructors(instructor_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);

-- Create Registration (Enrollment) table
CREATE TABLE IF NOT EXISTS Registration (
    registration_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES Students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES Courses(course_id) ON DELETE CASCADE
);

-- Create Grades table
CREATE TABLE IF NOT EXISTS Grades (
    grade_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    grade INT NOT NULL,
    remarks TEXT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES Courses(course_id) ON DELETE CASCADE
);

-- Add foreign key relationship
ALTER TABLE Courses ADD CONSTRAINT fk_instructor
    FOREIGN KEY (instructor_id) REFERENCES Instructors(instructor_id)
    ON DELETE SET NULL;
UPDATE Courses SET instructor_id = 1 WHERE course_code IN ('CST101', 'CST154');
UPDATE Courses SET instructor_id = 2 WHERE course_code IN ('CST221', 'CST310');
UPDATE Courses SET instructor_id = 3 WHERE course_code IN ('CST415', 'CST530');
UPDATE Courses SET instructor_id = 4 WHERE course_code IN ('CST615', 'CST735');
UPDATE Courses SET instructor_id = 5 WHERE course_code IN ('CST824', 'CST920');
