---

USE it_cube;

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

-- Запрос 1. Дети и их родители
-- JOIN объединяет таблицы по идентификатору родителя.
SELECT
    c.child_id,
    c.last_name,
    c.first_name,
    p.last_name AS parent_last_name,
    p.phone
FROM children c
JOIN parents p ON c.parent_id = p.parent_id;

-- Запрос 2. Заявки и данные детей
-- Показывает номер заявки, имя ребёнка и статус.
SELECT
    a.application_id,
    c.last_name,
    c.first_name,
    a.status,
    a.submitted_at
FROM applications a
JOIN children c ON a.child_id = c.child_id;

-- Запрос 3. Кружки, программы и преподаватели
-- Объединяет три таблицы для получения общей информации.
SELECT
    g.group_id,
    pr.name AS program_name,
    t.last_name AS teacher_last_name,
    g.room
FROM `groups` g
JOIN programs pr ON g.program_id = pr.program_id
JOIN teachers t ON g.teacher_id = t.teacher_id;

-- Запрос 4. Расписание занятий и названия программ
-- Выводит день, время занятия, кружок и программу.
SELECT
    s.weekday,
    s.start_time,
    s.end_time,
    pr.name AS program_name,
    g.group_id
FROM schedule s
JOIN `groups` g ON s.group_id = g.group_id
JOIN programs pr ON g.program_id = pr.program_id;

-- Запрос 5. Заявки, дети и назначенные программы
-- LEFT JOIN сохраняет заявки, даже если кружок ещё не назначен.
SELECT
    a.application_id,
    c.last_name,
    c.first_name,
    a.status,
    pr.name AS program_name
FROM applications a
JOIN children c ON a.child_id = c.child_id
LEFT JOIN `groups` g ON a.group_id = g.group_id
LEFT JOIN programs pr ON g.program_id = pr.program_id;


-- =====================================================
-- 2. ПЯТЬ ЗАПРОСОВ НА ИЗМЕНЕНИЕ СТРУКТУРЫ ТАБЛИЦ
-- =====================================================

-- Запрос 1. Добавление адреса родителя
-- Добавляет необязательное поле address.
ALTER TABLE parents
ADD COLUMN address VARCHAR(200) NULL;

-- Запрос 2. Добавление пола ребёнка
-- Поле может содержать М, Ж или NULL.
ALTER TABLE children
ADD COLUMN gender ENUM('М', 'Ж') NULL;

-- Запрос 3. Добавление даты приёма преподавателя
-- Поле hire_date хранит дату приёма на работу.
ALTER TABLE teachers
ADD COLUMN hire_date DATE NULL;

-- Запрос 4. Добавление признака активности программы
-- По умолчанию программа считается активной.
ALTER TABLE programs
ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;

-- Запрос 5. Добавление даты последнего изменения заявки
-- Время автоматически обновляется при изменении записи.
ALTER TABLE applications
ADD COLUMN updated_at DATETIME
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP;


-- =====================================================
-- 3. ДВА ЗАПРОСА НА ДОБАВЛЕНИЕ ДАННЫХ
-- =====================================================

-- Запрос 1. Добавление нескольких преподавателей
-- Каждая строка VALUES создаёт отдельную запись.
INSERT INTO teachers
    (last_name, first_name, middle_name,
     specialization, phone, email)
VALUES
    ('Иванов', 'Иван', 'Петрович',
     'Программирование', '+79990000001', 'ivanov@example.com'),
    ('Петрова', 'Анна', 'Сергеевна',
     'Робототехника', '+79990000002', 'petrova@example.com'),
    ('Сидоров', 'Алексей', NULL,
     'Веб-разработка', '+79990000003', 'sidorov@example.com');

-- Запрос 2. Добавление нескольких образовательных программ
-- Для каждой программы указываются возраст и длительность.
INSERT INTO programs
    (name, description, min_age, max_age, duration_hours)
VALUES
    ('Основы Python', 'Изучение языка Python', 10, 17, 72),
    ('Робототехника', 'Создание и программирование роботов', 8, 16, 60),
    ('Веб-разработка', 'Основы HTML, CSS и JavaScript', 12, 17, 80);


-- =====================================================
-- 4. ДВА ЗАПРОСА НА УДАЛЕНИЕ ДАННЫХ
-- =====================================================

-- Запрос 1. Удаление отклонённых заявок
-- Удаляются все записи, соответствующие условию WHERE.
DELETE FROM applications
WHERE status = 'отклонена';

-- Запрос 2. Удаление занятий по воскресеньям
-- Удаляются все занятия, у которых день недели — Вс.
DELETE FROM schedule
WHERE weekday = 'Вс';


-- =====================================================
-- 5. ДВА ЗАПРОСА НА ИЗМЕНЕНИЕ ДАННЫХ
-- =====================================================

-- Запрос 1. Изменение статуса новых заявок
-- Все новые заявки переводятся на рассмотрение.
UPDATE applications
SET status = 'на рассмотрении'
WHERE status = 'новая';

-- Запрос 2. Корректировка количества свободных мест
-- Если свободных мест больше вместимости,
-- значение свободных мест устанавливается равным вместимости.
UPDATE `groups`
SET free_seats = max_seats
WHERE free_seats > max_seats;