---

-- ============================================================
-- БАЗА ДАННЫХ: УЧЕБНОЕ РАСПИСАНИЕ
-- СУБД: PostgreSQL
-- ============================================================


-- ============================================================
-- 1. СОЗДАНИЕ БАЗЫ ДАННЫХ
-- ============================================================

-- Выполняется отдельно, если база ещё не существует:
-- CREATE DATABASE "учебное_расписание";

-- После создания необходимо подключиться к базе
-- "учебное_расписание" и выполнить оставшуюся часть скрипта.


-- ============================================================
-- 2. ФАКУЛЬТЕТ
-- ============================================================

CREATE TABLE факультет (
    id BIGSERIAL PRIMARY KEY,

    название VARCHAR(200) NOT NULL UNIQUE
);


-- ============================================================
-- 3. ПРЕПОДАВАТЕЛЬ
-- ============================================================

CREATE TABLE преподаватель (
    id BIGSERIAL PRIMARY KEY,

    факультет_id BIGINT NOT NULL,

    фамилия VARCHAR(100) NOT NULL,
    имя VARCHAR(100) NOT NULL,
    отчество VARCHAR(100),

    email VARCHAR(255) UNIQUE,

    CONSTRAINT fk_преподаватель_факультет
        FOREIGN KEY (факультет_id)
        REFERENCES факультет(id)
        ON DELETE RESTRICT
);


-- ============================================================
-- 4. ДИСЦИПЛИНА
-- ============================================================

CREATE TABLE дисциплина (
    id BIGSERIAL PRIMARY KEY,

    код VARCHAR(50) NOT NULL UNIQUE,

    название VARCHAR(255) NOT NULL,

    тип VARCHAR(30) NOT NULL,

    количество_часов INTEGER,

    CONSTRAINT проверка_типа_дисциплины
        CHECK (
            тип IN (
                'лекция',
                'практика',
                'лабораторная',
                'семинар',
                'экзамен',
                'зачёт'
            )
        ),

    CONSTRAINT проверка_часов
        CHECK (
            количество_часов IS NULL
            OR количество_часов > 0
        )
);


-- ============================================================
-- 5. УЧЕБНАЯ ГРУППА
-- ============================================================

CREATE TABLE группа (
    id BIGSERIAL PRIMARY KEY,

    название VARCHAR(100) NOT NULL UNIQUE,

    курс INTEGER NOT NULL,

    количество_студентов INTEGER NOT NULL DEFAULT 0,

    CONSTRAINT проверка_курса
        CHECK (курс BETWEEN 1 AND 6),

    CONSTRAINT проверка_количества_студентов
        CHECK (количество_студентов >= 0)
);


-- ============================================================
-- 6. ПОДГРУППА
-- ============================================================

CREATE TABLE подгруппа (
    id BIGSERIAL PRIMARY KEY,

    группа_id BIGINT NOT NULL,

    название VARCHAR(100) NOT NULL,

    количество_студентов INTEGER,

    CONSTRAINT fk_подгруппа_группа
        FOREIGN KEY (группа_id)
        REFERENCES группа(id)
        ON DELETE CASCADE,

    CONSTRAINT проверка_студентов_подгруппы
        CHECK (
            количество_студентов IS NULL
            OR количество_студентов >= 0
        ),

    CONSTRAINT уникальная_подгруппа
        UNIQUE (группа_id, название)
);


-- ============================================================
-- 7. СВЯЗЬ ДИСЦИПЛИНЫ И ПРЕПОДАВАТЕЛЯ
-- ============================================================

CREATE TABLE дисциплина_преподаватель (
    дисциплина_id BIGINT NOT NULL,

    преподаватель_id BIGINT NOT NULL,

    PRIMARY KEY (
        дисциплина_id,
        преподаватель_id
    ),

    CONSTRAINT fk_дп_дисциплина
        FOREIGN KEY (дисциплина_id)
        REFERENCES дисциплина(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_дп_преподаватель
        FOREIGN KEY (преподаватель_id)
        REFERENCES преподаватель(id)
        ON DELETE CASCADE
);


-- ============================================================
-- 8. СВЯЗЬ ДИСЦИПЛИНЫ И ГРУППЫ
-- ============================================================

CREATE TABLE дисциплина_группа (
    дисциплина_id BIGINT NOT NULL,

    группа_id BIGINT NOT NULL,

    PRIMARY KEY (
        дисциплина_id,
        группа_id
    ),

    CONSTRAINT fk_дг_дисциплина
        FOREIGN KEY (дисциплина_id)
        REFERENCES дисциплина(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_дг_группа
        FOREIGN KEY (группа_id)
        REFERENCES группа(id)
        ON DELETE CASCADE
);


-- ============================================================
-- 9. АУДИТОРИЯ
-- ============================================================

CREATE TABLE аудитория (
    id BIGSERIAL PRIMARY KEY,

    корпус VARCHAR(100) NOT NULL,

    номер VARCHAR(30) NOT NULL,

    вместимость INTEGER NOT NULL,

    тип VARCHAR(50) NOT NULL,

    CONSTRAINT проверка_вместимости
        CHECK (вместимость > 0),

    CONSTRAINT уникальная_аудитория
        UNIQUE (корпус, номер)
);


-- ============================================================
-- 10. СЕМЕСТР
-- ============================================================

CREATE TABLE семестр (
    id BIGSERIAL PRIMARY KEY,

    название VARCHAR(100) NOT NULL,

    дата_начала DATE NOT NULL,

    дата_окончания DATE NOT NULL,

    CONSTRAINT проверка_дат_семестра
        CHECK (дата_начала < дата_окончания)
);


-- ============================================================
-- 11. ВРЕМЕННОЙ СЛОТ
-- ============================================================

CREATE TABLE временной_слот (
    id BIGSERIAL PRIMARY KEY,

    номер_пары INTEGER NOT NULL,

    день_недели INTEGER NOT NULL,

    время_начала TIME NOT NULL,

    время_окончания TIME NOT NULL,

    CONSTRAINT проверка_номера_пары
        CHECK (номер_пары > 0),

    CONSTRAINT проверка_дня_недели
        CHECK (день_недели BETWEEN 1 AND 7),

    CONSTRAINT проверка_времени
        CHECK (время_начала < время_окончания),

    CONSTRAINT уникальный_временной_слот
        UNIQUE (
            день_недели,
            номер_пары
        )
);


-- ============================================================
-- 12. ДОСТУПНОСТЬ ПРЕПОДАВАТЕЛЯ
-- ============================================================

CREATE TABLE доступность_преподавателя (
    id BIGSERIAL PRIMARY KEY,

    преподаватель_id BIGINT NOT NULL,

    день_недели INTEGER NOT NULL,

    время_начала TIME NOT NULL,

    время_окончания TIME NOT NULL,

    доступен BOOLEAN NOT NULL DEFAULT TRUE,

    CONSTRAINT fk_доступность_преподавателя
        FOREIGN KEY (преподаватель_id)
        REFERENCES преподаватель(id)
        ON DELETE CASCADE,

    CONSTRAINT проверка_дня_преподавателя
        CHECK (день_недели BETWEEN 1 AND 7),

    CONSTRAINT проверка_времени_преподавателя
        CHECK (время_начала < время_окончания)
);


-- ============================================================
-- 13. ДОСТУПНОСТЬ АУДИТОРИИ
-- ============================================================

CREATE TABLE доступность_аудитории (
    id BIGSERIAL PRIMARY KEY,

    аудитория_id BIGINT NOT NULL,

    день_недели INTEGER NOT NULL,

    время_начала TIME NOT NULL,

    время_окончания TIME NOT NULL,

    доступна BOOLEAN NOT NULL DEFAULT TRUE,

    CONSTRAINT fk_доступность_аудитории
        FOREIGN KEY (аудитория_id)
        REFERENCES аудитория(id)
        ON DELETE CASCADE,

    CONSTRAINT проверка_дня_аудитории
        CHECK (день_недели BETWEEN 1 AND 7),

    CONSTRAINT проверка_времени_аудитории
        CHECK (время_начала < время_окончания)
);


-- ============================================================
-- 14. РАСПИСАНИЕ
-- ============================================================

CREATE TABLE расписание (
    id BIGSERIAL PRIMARY KEY,

    семестр_id BIGINT NOT NULL,

    дисциплина_id BIGINT NOT NULL,

    преподаватель_id BIGINT NOT NULL,

    группа_id BIGINT NOT NULL,

    подгруппа_id BIGINT,

    аудитория_id BIGINT NOT NULL,

    временной_слот_id BIGINT NOT NULL,

    тип_занятия VARCHAR(30) NOT NULL,

    тип_недели VARCHAR(20) NOT NULL DEFAULT 'каждая',

    дата_создания TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_расписание_семестр
        FOREIGN KEY (семестр_id)
        REFERENCES семестр(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_расписание_дисциплина
        FOREIGN KEY (дисциплина_id)
        REFERENCES дисциплина(id)
        ON DELETE RESTRICT,

    CONSTRAINT fk_расписание_преподаватель
        FOREIGN KEY (преподаватель_id)
        REFERENCES преподаватель(id)
        ON DELETE RESTRICT,

    CONSTRAINT fk_расписание_группа
        FOREIGN KEY (группа_id)
        REFERENCES группа(id)
        ON DELETE RESTRICT,

    CONSTRAINT fk_расписание_подгруппа
        FOREIGN KEY (подгруппа_id)
        REFERENCES подгруппа(id)
        ON DELETE RESTRICT,

    CONSTRAINT fk_расписание_аудитория
        FOREIGN KEY (аудитория_id)
        REFERENCES аудитория(id)
        ON DELETE RESTRICT,

    CONSTRAINT fk_расписание_слот
        FOREIGN KEY (временной_слот_id)
        REFERENCES временной_слот(id)
        ON DELETE RESTRICT,

    CONSTRAINT проверка_типа_занятия
        CHECK (
            тип_занятия IN (
                'лекция',
                'практика',
                'лабораторная',
                'семинар',
                'экзамен',
                'зачёт'
            )
        ),

    CONSTRAINT проверка_типа_недели
        CHECK (
            тип_недели IN (
                'каждая',
                'чётная',
                'нечётная'
            )
        )
);


-- ============================================================
-- 15. ЗАМЕНЫ ЗАНЯТИЙ
-- ============================================================

CREATE TABLE замена_занятия (
    id BIGSERIAL PRIMARY KEY,

    расписание_id BIGINT NOT NULL,

    старая_дата DATE NOT NULL,

    новая_дата DATE,

    старый_преподаватель_id BIGINT,

    новый_преподаватель_id BIGINT,

    старая_аудитория_id BIGINT,

    новая_аудитория_id BIGINT,

    причина TEXT,

    CONSTRAINT fk_замена_расписание
        FOREIGN KEY (расписание_id)
        REFERENCES расписание(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_замена_старый_преподаватель
        FOREIGN KEY (старый_преподаватель_id)
        REFERENCES преподаватель(id)
        ON DELETE RESTRICT,

    CONSTRAINT fk_замена_новый_преподаватель
        FOREIGN KEY (новый_преподаватель_id)
        REFERENCES преподаватель(id)
        ON DELETE RESTRICT,

    CONSTRAINT fk_замена_старая_аудитория
        FOREIGN KEY (старая_аудитория_id)
        REFERENCES аудитория(id)
        ON DELETE RESTRICT,

    CONSTRAINT fk_замена_новая_аудитория
        FOREIGN KEY (новая_аудитория_id)
        REFERENCES аудитория(id)
        ON DELETE RESTRICT
);


-- ============================================================
-- 16. ОГРАНИЧЕНИЯ РАСПИСАНИЯ
-- ============================================================

CREATE TABLE ограничение_расписания (
    id BIGSERIAL PRIMARY KEY,

    название VARCHAR(255) NOT NULL,

    описание TEXT,

    тип VARCHAR(50) NOT NULL,

    обязательное BOOLEAN NOT NULL DEFAULT TRUE,

    вес INTEGER NOT NULL DEFAULT 1,

    CONSTRAINT проверка_веса
        CHECK (вес > 0)
);


-- ============================================================
-- 17. ИНДЕКСЫ
-- ============================================================

CREATE INDEX индекс_преподаватель_факультет
ON преподаватель(факультет_id);

CREATE INDEX индекс_подгруппа_группа
ON подгруппа(группа_id);

CREATE INDEX индекс_дисциплина_преподаватель_преподаватель
ON дисциплина_преподаватель(преподаватель_id);

CREATE INDEX индекс_дисциплина_группа_группа
ON дисциплина_группа(группа_id);

CREATE INDEX индекс_расписание_семестр
ON расписание(семестр_id);

CREATE INDEX индекс_расписание_дисциплина
ON расписание(дисциплина_id);

CREATE INDEX индекс_расписание_преподаватель
ON расписание(преподаватель_id);

CREATE INDEX индекс_расписание_группа
ON расписание(группа_id);

CREATE INDEX индекс_расписание_подгруппа
ON расписание(подгруппа_id);

CREATE INDEX индекс_расписание_аудитория
ON расписание(аудитория_id);

CREATE INDEX индекс_расписание_слот
ON расписание(временной_слот_id);

CREATE INDEX индекс_доступность_преподавателя
ON доступность_преподавателя(
    преподаватель_id,
    день_недели
);

CREATE INDEX индекс_доступность_аудитории
ON доступность_аудитории(
    аудитория_id,
    день_недели
);


-- ============================================================
-- 18. ПРЕДСТАВЛЕНИЕ ПОЛНОГО РАСПИСАНИЯ
-- ============================================================

CREATE OR REPLACE VIEW полное_расписание AS
SELECT
    р.id AS расписание_id,

    с.название AS семестр,

    г.название AS группа,

    пг.название AS подгруппа,

    д.код AS код_дисциплины,

    д.название AS дисциплина,

    CONCAT_WS(
        ' ',
        п.фамилия,
        п.имя,
        п.отчество
    ) AS преподаватель,

    CONCAT(
        а.корпус,
        '-',
        а.номер
    ) AS аудитория,

    вс.номер_пары,

    CASE вс.день_недели
        WHEN 1 THEN 'Понедельник'
        WHEN 2 THEN 'Вторник'
        WHEN 3 THEN 'Среда'
        WHEN 4 THEN 'Четверг'
        WHEN 5 THEN 'Пятница'
        WHEN 6 THEN 'Суббота'
        WHEN 7 THEN 'Воскресенье'
    END AS день,

    вс.время_начала,

    вс.время_окончания,

    р.тип_занятия,

    р.тип_недели

FROM расписание р

JOIN семестр с
    ON с.id = р.семестр_id

JOIN группа г
    ON г.id = р.группа_id

LEFT JOIN подгруппа пг
    ON пг.id = р.подгруппа_id

JOIN дисциплина д
    ON д.id = р.дисциплина_id

JOIN преподаватель п
    ON п.id = р.преподаватель_id

JOIN аудитория а
    ON а.id = р.аудитория_id

JOIN временной_слот вс
    ON вс.id = р.временной_слот_id;


-- ============================================================
-- 19. НАЧАЛЬНЫЕ ВРЕМЕННЫЕ СЛОТЫ
-- ============================================================

INSERT INTO временной_слот
    (номер_пары, день_недели, время_начала, время_окончания)
VALUES
    (1, 1, '08:30', '10:00'),
    (2, 1, '10:10', '11:40'),
    (3, 1, '11:50', '13:20'),
    (4, 1, '13:50', '15:20'),
    (5, 1, '15:30', '17:00'),
    (6, 1, '17:10', '18:40'),

    (1, 2, '08:30', '10:00'),
    (2, 2, '10:10', '11:40'),
    (3, 2, '11:50', '13:20'),
    (4, 2, '13:50', '15:20'),
    (5, 2, '15:30', '17:00'),
    (6, 2, '17:10', '18:40'),

    (1, 3, '08:30', '10:00'),
    (2, 3, '10:10', '11:40'),
    (3, 3, '11:50', '13:20'),
    (4, 3, '13:50', '15:20'),
    (5, 3, '15:30', '17:00'),
    (6, 3, '17:10', '18:40'),

    (1, 4, '08:30', '10:00'),
    (2, 4, '10:10', '11:40'),
    (3, 4, '11:50', '13:20'),
    (4, 4, '13:50', '15:20'),
    (5, 4, '15:30', '17:00'),
    (6, 4, '17:10', '18:40'),

    (1, 5, '08:30', '10:00'),
    (2, 5, '10:10', '11:40'),
    (3, 5, '11:50', '13:20'),
    (4, 5, '13:50', '15:20'),
    (5, 5, '15:30', '17:00'),
    (6, 5, '17:10', '18:40'),

    (1, 6, '08:30', '10:00'),
    (2, 6, '10:10', '11:40'),
    (3, 6, '11:50', '13:20'),
    (4, 6, '13:50', '15:20'),
    (5, 6, '15:30', '17:00'),
    (6, 6, '17:10', '18:40');


-- ============================================================
-- 20. БАЗОВЫЕ ОГРАНИЧЕНИЯ
-- ============================================================

INSERT INTO ограничение_расписания
    (название, описание, тип, обязательное, вес)
VALUES
(
    'Один преподаватель — одно занятие',
    'Преподаватель не может вести два занятия одновременно.',
    'преподаватель',
    TRUE,
    100
),
(
    'Одна группа — одно занятие',
    'Группа не может посещать два занятия одновременно.',
    'группа',
    TRUE,
    100
),
(
    'Одна аудитория — одно занятие',
    'Аудитория не может использоваться для двух занятий одновременно.',
    'аудитория',
    TRUE,
    100
),
(
    'Вместимость аудитории',
    'Количество студентов не должно превышать вместимость аудитории.',
    'вместимость',
    TRUE,
    100
),
(
    'Доступность преподавателя',
    'Занятие должно находиться в доступное для преподавателя время.',
    'доступность_преподавателя',
    TRUE,
    90
),
(
    'Доступность аудитории',
    'Аудитория должна быть доступна в выбранное время.',
    'доступность_аудитории',
    TRUE,
    90
),
(
    'Лабораторная аудитория',
    'Лабораторное занятие должно проходить в подходящей аудитории.',
    'тип_аудитории',
    TRUE,
    90
);


-- ============================================================
-- КОНЕЦ СКРИПТА
-- ============================================================