---

-- =========================================================
-- СОЗДАНИЕ СХЕМЫ
-- =========================================================
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 INDEX uq_parents_phone (phone),
    UNIQUE INDEX uq_parents_email (email),
    UNIQUE INDEX 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 INDEX uq_children_snils (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. ПРОГРАММЫ
-- =========================================================
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 INDEX 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 INDEX uq_teachers_phone (phone),
    UNIQUE INDEX uq_teachers_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 5. ГРУППЫ
-- =========================================================
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),
    INDEX idx_groups_program (program_id),
    INDEX 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),
    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;

-- =========================================================
-- 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),
    INDEX 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),
    INDEX 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;