---

-- =============================================
-- База данных: Экзаменационная сессия
-- =============================================

CREATE DATABASE IF NOT EXISTS exam_session;
USE exam_session;

-- =============================================
-- 1. Группа
-- =============================================
CREATE TABLE `group` (
    group_id VARCHAR(10) PRIMARY KEY,           
    group_name VARCHAR(100) NOT NULL,
    course INT NOT NULL,
    specialty VARCHAR(150) NOT NULL,
    student_count INT DEFAULT 0
);

-- =============================================
-- 2. Студент
-- =============================================
CREATE TABLE student (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    birth_date DATE,
    phone VARCHAR(20),
    group_id VARCHAR(10),
    
    FOREIGN KEY (group_id) REFERENCES `group`(group_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

-- =============================================
-- 3. Преподаватель
-- =============================================
CREATE TABLE teacher (
    teacher_id VARCHAR(10) PRIMARY KEY,         
    full_name VARCHAR(150) NOT NULL,
    position VARCHAR(100),
    phone VARCHAR(20)
);

-- =============================================
-- 4. Дисциплина
-- =============================================
CREATE TABLE discipline (
    discipline_id VARCHAR(10) PRIMARY KEY,      
    discipline_name VARCHAR(150) NOT NULL,
    hours INT NOT NULL,
    semester INT NOT NULL,
    control_type ENUM('Экзамен', 'Зачёт', 'Дифференцированный зачёт') NOT NULL
);

-- =============================================
-- 5. Связь Преподаватель ↔ Дисциплина (N:M)
-- =============================================
CREATE TABLE teacher_discipline (
    teacher_id VARCHAR(10),
    discipline_id VARCHAR(10),
    PRIMARY KEY (teacher_id, discipline_id),
    FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (discipline_id) REFERENCES discipline(discipline_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

-- =============================================
-- 6. Сессия
-- =============================================
CREATE TABLE session (
    session_id VARCHAR(10) PRIMARY KEY,         
    academic_year VARCHAR(9) NOT NULL,          
    semester INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL
);

-- =============================================
-- 7. Экзамен
-- =============================================
CREATE TABLE exam (
    exam_id INT AUTO_INCREMENT PRIMARY KEY,
    discipline_id VARCHAR(10) NOT NULL,
    teacher_id VARCHAR(10) NOT NULL,
    session_id VARCHAR(10) NOT NULL,
    exam_date DATE NOT NULL,
    exam_time TIME NOT NULL,
    auditorium VARCHAR(20),
    exam_type ENUM('Экзамен', 'Зачёт') NOT NULL,
    
    FOREIGN KEY (discipline_id) REFERENCES discipline(discipline_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (session_id) REFERENCES session(session_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

-- =============================================
-- 8. Оценка
-- =============================================
CREATE TABLE grade (
    student_id INT,
    exam_id INT,
    grade_value VARCHAR(20) NOT NULL,           
    grade_date DATE,
    form ENUM('Устно', 'Письменно', 'Тест') NOT NULL,
    
    PRIMARY KEY (student_id, exam_id),
    FOREIGN KEY (student_id) REFERENCES student(student_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (exam_id) REFERENCES exam(exam_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

-- =============================================
-- Индексы
-- =============================================
CREATE INDEX idx_student_group ON student(group_id);
CREATE INDEX idx_exam_discipline ON exam(discipline_id);
CREATE INDEX idx_exam_teacher ON exam(teacher_id);
CREATE INDEX idx_grade_student ON grade(student_id);