---

-- ============================================================
-- БД: IT-куб — автоматизация приёма и распределения заявок
-- СУБД: MySQL 8.0+
-- Нормализация: 3НФ
-- ============================================================

DROP DATABASE IF EXISTS it_cube;
CREATE DATABASE it_cube
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE it_cube;

-- ============================================================
-- 1. ТАБЛИЦА: родители
-- ============================================================
CREATE TABLE родители (
    код_родителя      INT AUTO_INCREMENT PRIMARY KEY,
    фамилия           VARCHAR(50)  NOT NULL,
    имя               VARCHAR(50)  NOT NULL,
    отчество          VARCHAR(50)  NULL,
    телефон           VARCHAR(20)  NOT NULL UNIQUE,
    электронная_почта VARCHAR(100) NOT NULL UNIQUE,
    снилс             CHAR(11)     NULL UNIQUE,
    дата_регистрации  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- 2. ТАБЛИЦА: дети
-- ============================================================
CREATE TABLE дети (
    код_ребёнка    INT AUTO_INCREMENT PRIMARY KEY,
    фамилия        VARCHAR(50) NOT NULL,
    имя            VARCHAR(50) NOT NULL,
    отчество       VARCHAR(50) NULL,
    дата_рождения  DATE        NOT NULL,
    снилс          CHAR(11)    NULL UNIQUE,
    код_родителя   INT         NOT NULL,
    CONSTRAINT fk_дети_родители
        FOREIGN KEY (код_родителя)
        REFERENCES родители(код_родителя)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT chk_дата_рождения
        CHECK (дата_рождения <= CURDATE())
) ENGINE=InnoDB;

-- ============================================================
-- 3. ТАБЛИЦА: преподаватели
-- ============================================================
CREATE TABLE преподаватели (
    код_преподавателя INT AUTO_INCREMENT PRIMARY KEY,
    фамилия           VARCHAR(50)  NOT NULL,
    имя               VARCHAR(50)  NOT NULL,
    отчество          VARCHAR(50)  NULL,
    специализация     VARCHAR(100) NOT NULL,
    телефон           VARCHAR(20)  NOT NULL UNIQUE,
    электронная_почта VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB;

-- ============================================================
-- 4. ТАБЛИЦА: программы
-- ============================================================
CREATE TABLE программы (
    код_программы      INT AUTO_INCREMENT PRIMARY KEY,
    название           VARCHAR(100) NOT NULL UNIQUE,
    описание           TEXT         NULL,
    возраст_мин        TINYINT UNSIGNED NOT NULL,
    возраст_макс       TINYINT UNSIGNED NOT NULL,
    длительность_часов SMALLINT UNSIGNED NOT NULL,
    CONSTRAINT chk_возраст
        CHECK (возраст_мин < возраст_макс AND возраст_мин >= 5)
) ENGINE=InnoDB;

-- ============================================================
-- 5. ТАБЛИЦА: кружки
-- ============================================================
CREATE TABLE кружки (
    код_кружка        INT AUTO_INCREMENT PRIMARY KEY,
    код_программы     INT NOT NULL,
    код_преподавателя INT NOT NULL,
    аудитория         VARCHAR(20) NULL,
    учебный_год       VARCHAR(9)  NOT NULL,
    макс_мест         SMALLINT UNSIGNED NOT NULL,
    свободных_мест    SMALLINT UNSIGNED NOT NULL,
    CONSTRAINT fk_кружки_программы
        FOREIGN KEY (код_программы)
        REFERENCES программы(код_программы)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_кружки_преподаватели
        FOREIGN KEY (код_преподавателя)
        REFERENCES преподаватели(код_преподавателя)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT chk_мест
        CHECK (свободных_мест <= макс_мест AND свободных_мест >= 0)
) ENGINE=InnoDB;

-- ============================================================
-- 6. ТАБЛИЦА: расписание
-- ============================================================
CREATE TABLE расписание (
    код_занятия     INT AUTO_INCREMENT PRIMARY KEY,
    код_кружка      INT NOT NULL,
    день_недели     ENUM('Пн','Вт','Ср','Чт','Пт','Сб','Вс') NOT NULL,
    время_начала    TIME NOT NULL,
    время_окончания TIME NOT NULL,
    CONSTRAINT fk_расписание_кружки
        FOREIGN KEY (код_кружка)
        REFERENCES кружки(код_кружка)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT chk_время
        CHECK (время_начала < время_окончания)
) ENGINE=InnoDB;

-- ============================================================
-- 7. ТАБЛИЦА: заявки
-- ============================================================
CREATE TABLE заявки (
    код_заявки     INT AUTO_INCREMENT PRIMARY KEY,
    код_ребёнка    INT NOT NULL,
    код_кружка     INT NULL,
    статус         VARCHAR(20) NOT NULL DEFAULT 'новая',
    дата_подачи    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    дата_изменения DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
                   ON UPDATE CURRENT_TIMESTAMP,
    комментарий    TEXT NULL,
    CONSTRAINT fk_заявки_дети
        FOREIGN KEY (код_ребёнка)
        REFERENCES дети(код_ребёнка)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_заявки_кружки
        FOREIGN KEY (код_кружка)
        REFERENCES кружки(код_кружка)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT chk_статус
        CHECK (статус IN ('новая','на_проверке','одобрена',
                          'зачислен','отклонена','отменена')),
    CONSTRAINT chk_зачислен_с_кружком
        CHECK (
            (статус = 'зачислен' AND код_кружка IS NOT NULL)
            OR
            (статус <> 'зачислен')
        )
) ENGINE=InnoDB;

-- ============================================================
-- ИНДЕКСЫ
-- ============================================================
CREATE INDEX idx_дети_родитель        ON дети(код_родителя);
CREATE INDEX idx_заявки_ребёнок       ON заявки(код_ребёнка);
CREATE INDEX idx_заявки_кружок        ON заявки(код_кружка);
CREATE INDEX idx_заявки_статус        ON заявки(статус);
CREATE INDEX idx_кружки_программа     ON кружки(код_программы);
CREATE INDEX idx_кружки_преподаватель ON кружки(код_преподавателя);
CREATE INDEX idx_расписание_кружок    ON расписание(код_кружка);

-- ============================================================
-- ТРИГГЕРЫ УЧЁТА СВОБОДНЫХ МЕСТ
-- ============================================================

DELIMITER $$

-- Проверка перед вставкой заявки
CREATE TRIGGER trg_заявки_before_insert
BEFORE INSERT ON заявки
FOR EACH ROW
BEGIN
    IF NEW.статус = 'зачислен' THEN
        IF NEW.код_кружка IS NULL THEN
            SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'Нельзя зачислить без указания кружка';
        END IF;

        IF (SELECT свободных_мест FROM кружки
            WHERE код_кружка = NEW.код_кружка) <= 0 THEN
            SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'Нет свободных мест в выбранном кружке';
        END IF;
    END IF;
END$$

-- Уменьшение мест после вставки зачисленной заявки
CREATE TRIGGER trg_заявки_after_insert
AFTER INSERT ON заявки
FOR EACH ROW
BEGIN
    IF NEW.статус = 'зачислен' AND NEW.код_кружка IS NOT NULL THEN
        UPDATE кружки
        SET свободных_мест = свободных_мест - 1
        WHERE код_кружка = NEW.код_кружка;
    END IF;
END$$

-- Проверка перед обновлением заявки
CREATE TRIGGER trg_заявки_before_update
BEFORE UPDATE ON заявки
FOR EACH ROW
BEGIN
    IF NEW.статус = 'зачислен' AND OLD.статус <> 'зачислен' THEN
        IF NEW.код_кружка IS NULL THEN
            SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'Нельзя зачислить без указания кружка';
        END IF;

        IF (SELECT свободных_мест FROM кружки
            WHERE код_кружка = NEW.код_кружка) <= 0 THEN
            SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'Нет свободных мест в выбранном кружке';
        END IF;
    END IF;

    IF OLD.статус = 'зачислен'
       AND NEW.статус = 'зачислен'
       AND NEW.код_кружка <> OLD.код_кружка THEN
        IF (SELECT свободных_мест FROM кружки
            WHERE код_кружка = NEW.код_кружка) <= 0 THEN
            SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'Нет свободных мест в новом кружке';
        END IF;
    END IF;
END$$

-- Корректировка мест после обновления заявки
CREATE TRIGGER trg_заявки_after_update
AFTER UPDATE ON заявки
FOR EACH ROW
BEGIN
    IF OLD.статус = 'зачислен'
       AND NEW.статус <> 'зачислен'
       AND OLD.код_кружка IS NOT NULL THEN
        UPDATE кружки
        SET свободных_мест = свободных_мест + 1
        WHERE код_кружка = OLD.код_кружка;
    END IF;

    IF OLD.статус <> 'зачислен'
       AND NEW.статус = 'зачислен'
       AND NEW.код_кружка IS NOT NULL THEN
        UPDATE кружки
        SET свободных_мест = свободных_мест - 1
        WHERE код_кружка = NEW.код_кружка;
    END IF;

    IF OLD.статус = 'зачислен'
       AND NEW.статус = 'зачислен'
       AND OLD.код_кружка IS NOT NULL
       AND NEW.код_кружка IS NOT NULL
       AND OLD.код_кружка <> NEW.код_кружка THEN
        UPDATE кружки
        SET свободных_мест = свободных_мест + 1
        WHERE код_кружка = OLD.код_кружка;

        UPDATE кружки
        SET свободных_мест = свободных_мест - 1
        WHERE код_кружка = NEW.код_кружка;
    END IF;
END$$

-- Освобождение места при удалении заявки
CREATE TRIGGER trg_заявки_after_delete
AFTER DELETE ON заявки
FOR EACH ROW
BEGIN
    IF OLD.статус = 'зачислен' AND OLD.код_кружка IS NOT NULL THEN
        UPDATE кружки
        SET свободных_мест = свободных_мест + 1
        WHERE код_кружка = OLD.код_кружка;
    END IF;
END$$

-- Защита от удаления кружка с зачисленными детьми
CREATE TRIGGER trg_кружки_before_delete
BEFORE DELETE ON кружки
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM заявки
               WHERE код_кружка = OLD.код_кружка
                 AND статус = 'зачислен') THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Нельзя удалить кружок: в нём есть зачисленные дети';
    END IF;
END$$

-- Процедура отмены заявки
CREATE PROCEDURE отменить_заявку(IN p_код_заявки INT)
BEGIN
    DECLARE v_статус VARCHAR(20);

    SELECT статус INTO v_статус
    FROM заявки
    WHERE код_заявки = p_код_заявки;

    IF v_статус IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Заявка не найдена';
    END IF;

    IF v_статус = 'отменена' THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Заявка уже отменена';
    END IF;

    UPDATE заявки
    SET статус = 'отменена',
        код_кружка = NULL
    WHERE код_заявки = p_код_заявки;
END$$

-- Процедура физического удаления заявки
CREATE PROCEDURE удалить_заявку(IN p_код_заявки INT)
BEGIN
    DELETE FROM заявки WHERE код_заявки = p_код_заявки;
END$$

DELIMITER ;

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