---

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

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,                          -- учебный год (напр. 2026/2027)
    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,                                      -- код кружка (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 $$

-- ------------------------------------------------------------
-- Триггер 1. Проверка перед вставкой заявки
-- Нельзя зачислить без кружка, нельзя зачислить при отсутствии мест
-- ------------------------------------------------------------
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$$

-- ------------------------------------------------------------
-- Триггер 2. Уменьшение свободных мест после вставки зачисленной заявки
-- ------------------------------------------------------------
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$$

-- ------------------------------------------------------------
-- Триггер 3. Проверка перед обновлением заявки
-- Проверяем: смена статуса на "зачислен" и смену кружка
-- ------------------------------------------------------------
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$$

-- ------------------------------------------------------------
-- Триггер 4. Корректировка свободных мест после обновления заявки
-- Освобождение / занятие места при смене статуса или кружка
-- ------------------------------------------------------------
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;

    -- Зачислена, но сменился кружок: старый +1, новый -1
    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$$

-- ------------------------------------------------------------
-- Триггер 5. Освобождение места при удалении заявки
-- Если удаляется зачисленная заявка — возвращаем место в кружок
-- ------------------------------------------------------------
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$$

-- ------------------------------------------------------------
-- Триггер 6. Защита от удаления кружка с зачисленными детьми
-- ------------------------------------------------------------
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$$

-- ------------------------------------------------------------
-- Процедура 1. Отмена заявки (логическое удаление)
-- Меняет статус на "отменена", триггер сам освободит место
-- ------------------------------------------------------------
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$$

-- ------------------------------------------------------------
-- Процедура 2. Физическое удаление заявки
-- Триггер AFTER DELETE сам освободит место, если заявка была зачислена
-- ------------------------------------------------------------
CREATE PROCEDURE delete_application(IN p_application_id INT)
BEGIN
    DELETE FROM applications WHERE application_id = p_application_id;
END$$

DELIMITER ;

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