---

-- ============================================================
-- 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;

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