DROP TABLE IF EXISTS exam_results;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS subjects;
DROP TABLE IF EXISTS teachers;
DROP TABLE IF EXISTS groups_table;

CREATE TABLE groups_table (
    group_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    group_name VARCHAR(20) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE students (
    student_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    surname VARCHAR(50) NOT NULL,
    group_id INT UNSIGNED NOT NULL,
    CONSTRAINT fk_students_group
        FOREIGN KEY (group_id) REFERENCES groups_table(group_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE teachers (
    teacher_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    teacher_name VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE subjects (
    subject_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    subject_name VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE exam_results (
    result_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    student_id INT UNSIGNED NOT NULL,
    subject_id INT UNSIGNED NOT NULL,
    teacher_id INT UNSIGNED NOT NULL,
    ticket_number INT UNSIGNED NOT NULL,
    grade TINYINT UNSIGNED NOT NULL,
    exam_date DATE DEFAULT (CURRENT_DATE),
    CONSTRAINT fk_exam_student
        FOREIGN KEY (student_id) REFERENCES students(student_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_exam_subject
        FOREIGN KEY (subject_id) REFERENCES subjects(subject_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_exam_teacher
        FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT chk_grade CHECK (grade BETWEEN 2 AND 5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO groups_table (group_name) VALUES
('ИС-11'),
('ИС-12'),
('ПИ-21');

INSERT INTO students (surname, group_id) VALUES
('Иванов', 1),
('Петров', 1),
('Сидоров', 2),
('Кузнецов', 3);

INSERT INTO teachers (teacher_name) VALUES
('Смирнов А.В.'),
('Козлова И.П.'),
('Орлов С.Н.');

INSERT INTO subjects (subject_name) VALUES
('Математика'),
('Информатика'),
('Базы данных');

INSERT INTO exam_results (student_id, subject_id, teacher_id, ticket_number, grade, exam_date) VALUES
(1, 1, 1, 12, 5, '2026-01-15'),
(2, 1, 1, 18, 4, '2026-01-15'),
(3, 2, 2, 7,  5, '2026-01-16'),
(4, 3, 3, 21, 3, '2026-01-17');