---

-- ============================================================
-- DATABASE: it_cube
-- MySQL 8.0.16+
-- Автоматизация приёма и распределения заявок
-- 7 таблиц
-- ============================================================

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

USE it_cube;

-- Для повторного запуска удаляем таблицы в порядке,
-- обратном зависимостям внешних ключей.

DROP TABLE IF EXISTS applications;
DROP TABLE IF EXISTS schedule;
DROP TABLE IF EXISTS `groups`;
DROP TABLE IF EXISTS programs;
DROP TABLE IF EXISTS teachers;
DROP TABLE IF EXISTS children;
DROP TABLE IF EXISTS parents;

-- ============================================================
-- 1. 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) DEFAULT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(100) NOT NULL,
    snils CHAR(11) DEFAULT NULL,
    registered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_parents_phone UNIQUE (phone),
    CONSTRAINT uq_parents_email UNIQUE (email),
    CONSTRAINT uq_parents_snils UNIQUE (snils)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

-- ============================================================
-- 2. 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) DEFAULT NULL,
    birth_date DATE NOT NULL,
    snils CHAR(11) DEFAULT NULL,
    parent_id INT NOT NULL,

    CONSTRAINT uq_children_snils UNIQUE (snils),

    INDEX idx_children_parent (parent_id),

    CONSTRAINT fk_children_parent
        FOREIGN KEY (parent_id)
        REFERENCES parents(parent_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

-- ============================================================
-- 3. 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) DEFAULT NULL,
    specialization VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(100) NOT NULL,

    CONSTRAINT uq_teachers_phone UNIQUE (phone),
    CONSTRAINT uq_teachers_email UNIQUE (email)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

-- ============================================================
-- 4. PROGRAMS: образовательные программы
-- ============================================================

CREATE TABLE programs (
    program_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT DEFAULT NULL,
    min_age TINYINT UNSIGNED NOT NULL,
    max_age TINYINT UNSIGNED NOT NULL,
    duration_hours SMALLINT UNSIGNED NOT NULL,

    CONSTRAINT uq_programs_name UNIQUE (name),

    CONSTRAINT chk_programs_age
        CHECK (min_age <= max_age),

    CONSTRAINT chk_programs_duration
        CHECK (duration_hours > 0)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

-- ============================================================
-- 5. GROUPS: учебные кружки
-- ============================================================

CREATE TABLE `groups` (
    group_id INT AUTO_INCREMENT PRIMARY KEY,
    program_id INT NOT NULL,
    teacher_id INT NOT NULL,
    room VARCHAR(20) DEFAULT NULL,
    academic_year VARCHAR(9) NOT NULL,
    max_seats SMALLINT UNSIGNED NOT NULL,
    free_seats SMALLINT UNSIGNED NOT NULL,

    INDEX idx_groups_program (program_id),
    INDEX idx_groups_teacher (teacher_id),

    CONSTRAINT chk_groups_max_seats
        CHECK (max_seats > 0),

    CONSTRAINT chk_groups_free_seats
        CHECK (free_seats <= max_seats),

    CONSTRAINT fk_groups_program
        FOREIGN KEY (program_id)
        REFERENCES programs(program_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT fk_groups_teacher
        FOREIGN KEY (teacher_id)
        REFERENCES teachers(teacher_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

-- ============================================================
-- 6. 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,

    INDEX idx_schedule_group (group_id),

    CONSTRAINT chk_schedule_time
        CHECK (end_time > start_time),

    CONSTRAINT fk_schedule_group
        FOREIGN KEY (group_id)
        REFERENCES `groups`(group_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

-- ============================================================
-- 7. APPLICATIONS: заявки на обучение
-- ============================================================

CREATE TABLE applications (
    application_id INT AUTO_INCREMENT PRIMARY KEY,
    child_id INT NOT NULL,
    group_id INT DEFAULT NULL,

    status ENUM(
        'новая',
        'на рассмотрении',
        'одобрена',
        'отклонена',
        'зачислен'
    ) NOT NULL DEFAULT 'новая',

    submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    comment TEXT DEFAULT NULL,

    INDEX idx_applications_child (child_id),
    INDEX idx_applications_group (group_id),
    INDEX idx_applications_status (status),
    INDEX idx_applications_submitted (submitted_at),

    CONSTRAINT fk_applications_child
        FOREIGN KEY (child_id)
        REFERENCES children(child_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_applications_group
        FOREIGN KEY (group_id)
        REFERENCES `groups`(group_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

-- ============================================================
-- ПРОВЕРКА СОЗДАНИЯ ТАБЛИЦ
-- ============================================================

SHOW TABLES;

-- Проверка структуры таблиц:
DESCRIBE parents;
DESCRIBE children;
DESCRIBE teachers;
DESCRIBE programs;
DESCRIBE `groups`;
DESCRIBE schedule;
DESCRIBE applications;

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