---

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

-- =========================================================
-- 1. РОДИТЕЛИ
-- =========================================================
CREATE TABLE `parents` (
    `parent_id`     INT NOT NULL AUTO_INCREMENT,
    `last_name`     VARCHAR(50)  NOT NULL,
    `first_name`    VARCHAR(50)  NOT NULL,
    `middle_name`   VARCHAR(50)  NULL DEFAULT NULL,
    `phone`         VARCHAR(20)  NOT NULL,
    `email`         VARCHAR(100) NOT NULL,
    `snils`         CHAR(11)     NULL DEFAULT NULL,
    `registered_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `address`       VARCHAR(200) NULL DEFAULT NULL,
    PRIMARY KEY (`parent_id`),
    UNIQUE KEY `uq_parents_phone` (`phone`),
    UNIQUE KEY `uq_parents_email` (`email`),
    UNIQUE KEY `uq_parents_snils` (`snils`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 2. ДЕТИ
-- =========================================================
CREATE TABLE `children` (
    `child_id`    INT NOT NULL AUTO_INCREMENT,
    `last_name`   VARCHAR(50) NOT NULL,
    `first_name`  VARCHAR(50) NOT NULL,
    `middle_name` VARCHAR(50) NULL DEFAULT NULL,
    `birth_date`  DATE        NOT NULL,
    `snils`       CHAR(11)    NULL DEFAULT NULL,
    `parent_id`   INT         NOT NULL,
    `gender`      ENUM('М','Ж') NULL DEFAULT NULL,
    PRIMARY KEY (`child_id`),
    UNIQUE KEY `uq_children_snils` (`snils`),
    KEY `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. ПРОГРАММЫ
-- =========================================================
CREATE TABLE `programs` (
    `program_id`     INT NOT NULL AUTO_INCREMENT,
    `name`           VARCHAR(100) NOT NULL,
    `description`    TEXT         NULL DEFAULT NULL,
    `min_age`        TINYINT UNSIGNED NOT NULL,
    `max_age`        TINYINT UNSIGNED NOT NULL,
    `duration_hours` SMALLINT UNSIGNED NOT NULL,
    `is_active`      TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`program_id`),
    UNIQUE KEY `uq_programs_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 4. ПРЕПОДАВАТЕЛИ
-- =========================================================
CREATE TABLE `teachers` (
    `teacher_id`     INT NOT NULL AUTO_INCREMENT,
    `last_name`      VARCHAR(50)  NOT NULL,
    `first_name`     VARCHAR(50)  NOT NULL,
    `middle_name`    VARCHAR(50)  NULL DEFAULT NULL,
    `specialization` VARCHAR(100) NOT NULL,
    `phone`          VARCHAR(20)  NOT NULL,
    `email`          VARCHAR(100) NOT NULL,
    `hire_date`      DATE         NULL DEFAULT NULL,
    PRIMARY KEY (`teacher_id`),
    UNIQUE KEY `uq_teachers_phone` (`phone`),
    UNIQUE KEY `uq_teachers_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 5. ГРУППЫ  (groups — зарезервированное слово → кавычки обязательны)
-- =========================================================
CREATE TABLE `groups` (
    `group_id`      INT NOT NULL AUTO_INCREMENT,
    `program_id`    INT NOT NULL,
    `teacher_id`    INT NOT NULL,
    `room`          VARCHAR(20)  NULL DEFAULT NULL,
    `academic_year` VARCHAR(9)   NOT NULL,
    `max_seats`     SMALLINT UNSIGNED NOT NULL,
    `free_seats`    SMALLINT UNSIGNED NOT NULL,
    `notes`         VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY (`group_id`),
    KEY `idx_groups_program` (`program_id`),
    KEY `idx_groups_teacher` (`teacher_id`),
    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. ЗАЯВКИ
-- =========================================================
CREATE TABLE `applications` (
    `application_id` INT NOT NULL AUTO_INCREMENT,
    `child_id`       INT NOT NULL,
    `group_id`       INT NULL DEFAULT NULL,
    `status`         ENUM('новая','на рассмотрении','одобрена','отклонена','зачислен')
                     NOT NULL DEFAULT 'новая',
    `submitted_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `comment`        TEXT     NULL DEFAULT NULL,
    `updated_at`     DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`application_id`),
    KEY `idx_applications_child` (`child_id`),
    KEY `idx_applications_group` (`group_id`),
    KEY `idx_applications_status` (`status`),
    KEY `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;

-- =========================================================
-- 7. ИСТОРИЯ ЗАЯВОК
-- =========================================================
CREATE TABLE `application_history` (
    `history_id`     INT NOT NULL AUTO_INCREMENT,
    `application_id` INT NOT NULL,
    `old_status`     VARCHAR(20) NULL DEFAULT NULL,
    `new_status`     VARCHAR(20) NULL DEFAULT NULL,
    `changed_at`     DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`history_id`),
    KEY `idx_history_application` (`application_id`),
    CONSTRAINT `fk_history_application`
        FOREIGN KEY (`application_id`) REFERENCES `applications` (`application_id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 8. РАСПИСАНИЕ
-- =========================================================
CREATE TABLE `schedule` (
    `lesson_id`  INT NOT NULL AUTO_INCREMENT,
    `group_id`   INT NOT NULL,
    `weekday`    ENUM('Пн','Вт','Ср','Чт','Пт','Сб','Вс') NOT NULL,
    `start_time` TIME NOT NULL,
    `end_time`   TIME NOT NULL,
    PRIMARY KEY (`lesson_id`),
    KEY `idx_schedule_group` (`group_id`),
    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;