---

CREATE DATABASE IF NOT EXISTS university
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE university;


-- =========================================
-- ГРУППА
-- =========================================
CREATE TABLE group_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    course INT,
    quantity_students INT
);


-- =========================================
-- ПРЕПОДАВАТЕЛЬ
-- =========================================
CREATE TABLE teacher (
    id INT AUTO_INCREMENT PRIMARY KEY,
    surname VARCHAR(100) NOT NULL,
    name VARCHAR(100) NOT NULL,
    patronymic VARCHAR(100),
    position VARCHAR(100),
    degree VARCHAR(100)
);


-- =========================================
-- ФАКУЛЬТЕТ
-- =========================================
CREATE TABLE faculty (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    type VARCHAR(100)
);


-- =========================================
-- ДИСЦИПЛИНА
-- =========================================
CREATE TABLE discipline (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    direction VARCHAR(150),
    hours INT
);


-- =========================================
-- РАСПИСАНИЕ
-- =========================================
CREATE TABLE schedule (
    id INT AUTO_INCREMENT PRIMARY KEY,

    semester_id INT,
    discipline_id INT NOT NULL,
    teacher_id INT NOT NULL,
    group_id INT NOT NULL,

    external_slot_id INT,
    lesson_type VARCHAR(50),
    auditorium_id INT,

    FOREIGN KEY (discipline_id)
        REFERENCES discipline(id),

    FOREIGN KEY (teacher_id)
        REFERENCES teacher(id),

    FOREIGN KEY (group_id)
        REFERENCES group_table(id)
);


-- =========================================
-- СЕМЕСТР
-- =========================================
CREATE TABLE semester (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    date_start DATE,
    date_end DATE
);


-- =========================================
-- ВНЕШНИЙ СЛОТ
-- =========================================
CREATE TABLE external_slot (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pair_number INT,
    weekday VARCHAR(20),
    time_start TIME,
    time_end TIME,
    week_number INT
);


-- =========================================
-- АУДИТОРИЯ
-- =========================================
CREATE TABLE auditorium (
    id INT AUTO_INCREMENT PRIMARY KEY,
    number VARCHAR(20) NOT NULL,
    capacity INT,
    type VARCHAR(50)
);


-- =========================================
-- СВЯЗИ РАСПИСАНИЯ
-- =========================================

ALTER TABLE schedule
    ADD CONSTRAINT fk_schedule_semester
    FOREIGN KEY (semester_id)
    REFERENCES semester(id);

ALTER TABLE schedule
    ADD CONSTRAINT fk_schedule_external_slot
    FOREIGN KEY (external_slot_id)
    REFERENCES external_slot(id);

ALTER TABLE schedule
    ADD CONSTRAINT fk_schedule_auditorium
    FOREIGN KEY (auditorium_id)
    REFERENCES auditorium(id);