---

-- ============================================================
-- DATABASE: 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. TABLE: parents — родители
-- ============================================================
CREATE TABLE parents (
    parent_id       INT AUTO_INCREMENT PRIMARY KEY,               -- код_родителя
    last_name       VARCHAR(50)  NOT NULL,                         -- фамилия
    first_name      VARCHAR(50)  NOT NULL,                         -- имя
    middle_name     VARCHAR(50)  NULL,                             -- отчество
    phone           VARCHAR(20)  NOT NULL UNIQUE,                  -- телефон
    email           VARCHAR(100) NOT NULL UNIQUE,                  -- электронная_почта
    snils           CHAR(11)     NULL UNIQUE,                      -- снилс
    registered_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP -- дата_регистрации
) ENGINE=InnoDB;

-- ============================================================
-- 2. TABLE: children — дети
-- ============================================================
CREATE TABLE children (
    child_id        INT AUTO_INCREMENT PRIMARY KEY,                -- код_ребёнка
    last_name       VARCHAR(50) NOT NULL,                          -- фамилия
    first_name      VARCHAR(50) NOT NULL,                          -- имя
    middle_name     VARCHAR(50) NULL,                              -- отчество
    birth_date      DATE        NOT NULL,                          -- дата_рождения
    snils           CHAR(11)    NULL UNIQUE,                       -- снилс
    parent_id       INT         NOT NULL,                          -- код_родителя
    CONSTRAINT fk_children_parents
        FOREIGN KEY (parent_id)
        REFERENCES parents(parent_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT chk_birth_date
        CHECK (birth_date <= CURDATE())
) ENGINE=InnoDB;

-- ============================================================
-- 3. TABLE: teachers — преподаватели
-- ============================================================
CREATE TABLE teachers (
    teacher_id      INT AUTO_INCREMENT PRIMARY KEY,                -- код_преподавателя
    last_name       VARCHAR(50)  NOT NULL,                         -- фамилия
    first_name      VARCHAR(50)  NOT NULL,                         -- имя
    middle_name     VARCHAR(50)  NULL,                             -- отчество
    specialization  VARCHAR(100) NOT NULL,                         -- специализация
    phone           VARCHAR(20)  NOT NULL UNIQUE,                  -- телефон
    email           VARCHAR(100) NOT NULL UNIQUE                   -- электронная_почта
) ENGINE=InnoDB;

-- ============================================================
-- 4. TABLE: programs — программы
-- ============================================================
CREATE TABLE programs (
    program_id      INT AUTO_INCREMENT PRIMARY KEY,                -- код_программы
    name            VARCHAR(100) NOT NULL UNIQUE,                  -- название
    description     TEXT         NULL,                             -- описание
    min_age         TINYINT UNSIGNED NOT NULL,                     -- возраст_мин
    max_age         TINYINT UNSIGNED NOT NULL,                     -- возраст_макс
    duration_hours  SMALLINT UNSIGNED NOT NULL,                    -- длительность_часов
    CONSTRAINT chk_age
        CHECK (min_age < max_age AND min_age >= 5)
) ENGINE=InnoDB;

-- ============================================================
-- 5. TABLE: groups — кружки (учебные группы)
-- ============================================================
CREATE TABLE groups (
    group_id        INT AUTO_INCREMENT PRIMARY KEY,                -- код_кружка
    program_id      INT NOT NULL,                                  -- код_программы
    teacher_id      INT NOT NULL,                                  -- код_преподавателя
    room            VARCHAR(20) NULL,                              -- аудитория
    academic_year   VARCHAR(9)  NOT NULL,                          -- учебный_год
    max_seats       SMALLINT UNSIGNED NOT NULL,                    -- макс_мест
    free_seats      SMALLINT UNSIGNED NOT NULL,                    -- свободных_мест
    CONSTRAINT fk_groups_programs
        FOREIGN KEY (program_id)
        REFERENCES programs(program_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_groups_teachers
        FOREIGN KEY (teacher_id)
        REFERENCES teachers(teacher_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT chk_seats
        CHECK (free_seats <= max_seats AND free_seats >= 0)
) ENGINE=InnoDB;

-- ============================================================
-- 6. TABLE: schedule — расписание
-- ============================================================
CREATE TABLE schedule (
    lesson_id       INT AUTO_INCREMENT PRIMARY KEY,                -- код_занятия
    group_id        INT NOT NULL,                                  -- код_кружка
    weekday         ENUM('Пн','Вт','Ср','Чт','Пт','Сб','Вс') NOT NULL, -- день_недели
    start_time      TIME NOT NULL,                                 -- время_начала
    end_time        TIME NOT NULL,                                 -- время_окончания
    CONSTRAINT fk_schedule_groups
        FOREIGN KEY (group_id)
        REFERENCES groups(group_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT chk_time
        CHECK (start_time < end_time)
) ENGINE=InnoDB;

-- ============================================================
-- 7. TABLE: applications — заявки
-- ============================================================
CREATE TABLE applications (
    application_id  INT AUTO_INCREMENT PRIMARY KEY,                -- код_заявки
    child_id        INT NOT NULL,                                  -- код_ребёнка
    group_id        INT NULL,                                      -- код_кружка
    status          VARCHAR(20) NOT NULL DEFAULT 'новая',          -- статус
    submitted_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,   -- дата_подачи
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP    -- дата_изменения
                    ON UPDATE CURRENT_TIMESTAMP,
    comment         TEXT NULL,                                     -- комментарий
    CONSTRAINT fk_applications_children
        FOREIGN KEY (child_id)
        REFERENCES children(child_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_applications_groups
        FOREIGN KEY (group_id)
        REFERENCES groups(group_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT chk_status
        CHECK (status IN ('новая','на_проверке','одобрена',
                          'зачислен','отклонена','отменена')),
    CONSTRAINT chk_enrolled_with_group
        CHECK (
            (status = 'зачислен' AND group_id IS NOT NULL)
            OR
            (status <> 'зачислен')
        )
) ENGINE=InnoDB;

-- ============================================================
-- INDEXES — индексы
-- ============================================================
CREATE INDEX idx_children_parent        ON children(parent_id);        -- дети по родителю
CREATE INDEX idx_applications_child     ON applications(child_id);     -- заявки по ребёнку
CREATE INDEX idx_applications_group     ON applications(group_id);     -- заявки по кружку
CREATE INDEX idx_applications_status    ON applications(status);       -- заявки по статусу
CREATE INDEX idx_groups_program         ON groups(program_id);         -- кружки по программе
CREATE INDEX idx_groups_teacher         ON groups(teacher_id);         -- кружки по преподавателю
CREATE INDEX idx_schedule_group         ON schedule(group_id);         -- расписание по кружку

-- ============================================================
-- TRIGGERS — триггеры учёта свободных мест
-- ============================================================

DELIMITER $$

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

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

-- Уменьшение мест после вставки зачисленной заявки
CREATE TRIGGER trg_applications_after_insert
AFTER INSERT ON applications
FOR EACH ROW
BEGIN
    IF NEW.status = 'зачислен' AND NEW.group_id IS NOT NULL THEN
        UPDATE groups
        SET free_seats = free_seats - 1
        WHERE group_id = NEW.group_id;
    END IF;
END$$

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

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

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

-- Корректировка мест после обновления заявки
CREATE TRIGGER trg_applications_after_update
AFTER UPDATE ON applications
FOR EACH ROW
BEGIN
    IF OLD.status = 'зачислен'
       AND NEW.status <> 'зачислен'
       AND OLD.group_id IS NOT NULL THEN
        UPDATE groups
        SET free_seats = free_seats + 1
        WHERE group_id = OLD.group_id;
    END IF;

    IF OLD.status <> 'зачислен'
       AND NEW.status = 'зачислен'
       AND NEW.group_id IS NOT NULL THEN
        UPDATE groups
        SET free_seats = free_seats - 1
        WHERE group_id = NEW.group_id;
    END IF;

    IF OLD.status = 'зачислен'
       AND NEW.status = 'зачислен'
       AND OLD.group_id IS NOT NULL
       AND NEW.group_id IS NOT NULL
       AND OLD.group_id <> NEW.group_id THEN
        UPDATE groups
        SET free_seats = free_seats + 1
        WHERE group_id = OLD.group_id;

        UPDATE groups
        SET free_seats = free_seats - 1
        WHERE group_id = NEW.group_id;
    END IF;
END$$

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

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

-- Процедура отмены заявки
CREATE PROCEDURE cancel_application(IN p_application_id INT)
BEGIN
    DECLARE v_status VARCHAR(20);

    SELECT status INTO v_status
    FROM applications
    WHERE application_id = p_application_id;

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

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

    UPDATE applications
    SET status = 'отменена',
        group_id = NULL
    WHERE application_id = p_application_id;
END$$

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

DELIMITER ;

-- ============================================================
-- END OF SCRIPT
-- ============================================================