---

-- 1. Таблица кабинетов
CREATE TABLE Cabinets (
    cabinet_id INT PRIMARY KEY AUTO_INCREMENT,
    room_number VARCHAR(20) NOT NULL
);

-- 2. Таблица учителей
CREATE TABLE Teachers (
    teacher_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    cabinet_id INT NULL, -- У некоторых учителей может не быть собственного кабинета
    FOREIGN KEY (cabinet_id) REFERENCES Cabinets(cabinet_id) ON DELETE SET NULL
);

-- 3. Таблица предметов
CREATE TABLE Subjects (
    subject_id INT PRIMARY KEY AUTO_INCREMENT,
    subject_name VARCHAR(100) NOT NULL
);

-- 4. Связь учителей и предметов (многие ко многим)
CREATE TABLE Teacher_Subjects (
    teacher_id INT,
    subject_id INT,
    PRIMARY KEY (teacher_id, subject_id),
    FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES Subjects(subject_id) ON DELETE CASCADE
);

-- 5. Таблица классов
CREATE TABLE Classes (
    class_id INT PRIMARY KEY AUTO_INCREMENT,
    class_num INT NOT NULL,               -- Параллель (например, 5, 10)
    class_letter VARCHAR(5) NOT NULL,     -- Буква (например, 'А', 'Б')
    class_teacher_id INT NOT NULL,        -- Классный руководитель
    FOREIGN KEY (class_teacher_id) REFERENCES Teachers(teacher_id)
);

-- 6. Таблица учеников
CREATE TABLE Students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,      -- Фамилия и имя
    class_id INT NOT NULL,
    FOREIGN KEY (class_id) REFERENCES Classes(class_id) ON DELETE CASCADE
);

-- 7. Таблица четвертных оценок
CREATE TABLE Grades (
    grade_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    subject_id INT NOT NULL,
    quarter INT NOT NULL CHECK (quarter BETWEEN 1 AND 4),
    grade_value INT NOT NULL CHECK (grade_value BETWEEN 2 AND 5),
    FOREIGN KEY (student_id) REFERENCES Students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES Subjects(subject_id) ON DELETE CASCADE
);

-- 8. Таблица расписания занятий
CREATE TABLE Schedule (
    schedule_id INT PRIMARY KEY AUTO_INCREMENT,
    class_id INT NOT NULL,
    day_of_week VARCHAR(15) NOT NULL,     -- Например, 'Понедельник', 'Среда'
    lesson_number INT NOT NULL,           -- Номер урока (1, 2, 3...)
    subject_id INT NOT NULL,
    teacher_id INT NOT NULL,
    cabinet_id INT NULL,                  -- Кабинет, в котором идет урок
    FOREIGN KEY (class_id) REFERENCES Classes(class_id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES Subjects(subject_id) ON DELETE CASCADE,
    FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id) ON DELETE CASCADE,
    FOREIGN KEY (cabinet_id) REFERENCES Cabinets(cabinet_id) ON DELETE SET NULL
);