---

-- 1. Таблица групп
CREATE TABLE Groups (
    group_id INT PRIMARY KEY AUTO_INCREMENT,
    group_name VARCHAR(50) UNIQUE NOT NULL
);

-- 2. Таблица студентов
CREATE TABLE Students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    group_id INT,
    FOREIGN KEY (group_id) REFERENCES Groups(group_id) ON DELETE SET NULL
);

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

-- 4. Таблица оценок (журнал)
CREATE TABLE Grades (
    grade_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    subject_id INT NOT NULL,
    grade INT CHECK (grade BETWEEN 1 AND 5),
    date DATE NOT NULL,
    FOREIGN KEY (student_id) REFERENCES Students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES Subjects(subject_id) ON DELETE CASCADE,
    UNIQUE KEY unique_grade (student_id, subject_id, date) -- чтобы не было дублей за один день
);


-- Добавляем группы
INSERT INTO Groups (group_name) VALUES ('РИУПО-24');

-- Добавляем студентов (привязываем к group_id=1)
INSERT INTO Students (first_name, last_name, group_id) VALUES 
('Станислав', 'Гурин', 1),
('Давид', 'Геворкян', 1);

-- Добавляем предметы
INSERT INTO Subjects (subject_name, teacher_name) VALUES 
('ОАИПР', 'Назина В.Е.'),
('История', 'Николаев П.О.');

-- Добавляем оценки (subject_id: 1 - ОАИПР, 2 - История)
INSERT INTO Grades (student_id, subject_id, grade, date) VALUES 
(1, 1, 4, '2025-10-23'), -- Станислав, ОАИПР, 4
(2, 1, 5, '2025-10-23'), -- Давид, ОАИПР, 5
(1, 2, 5, '2025-10-27'); -- Станислав, История, 5