---

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

-- 1.1. Таблица резервных сведений о преподавателях
CREATE TABLE IF NOT EXISTS teachers_backup (
    teacher_id INT AUTO_INCREMENT PRIMARY KEY,
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    specialization VARCHAR(100) NOT NULL
);

-- 1.2. Таблица истории изменений заявок
CREATE TABLE IF NOT EXISTS application_history (
    history_id INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL,
    old_status VARCHAR(20),
    new_status VARCHAR(20),
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- 2. ЗАПРОСЫ НА ИЗМЕНЕНИЕ ГОТОВЫХ ТАБЛИЦ
-- ============================================================

-- 2.1. Добавление поля даты обновления в таблицу заявок
ALTER TABLE applications
    ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- 2.2. Добавление поля примечания в таблицу кружков
ALTER TABLE `groups`
    ADD COLUMN notes VARCHAR(255);

-- ============================================================
-- 3. ВЫБОРКА ДАННЫХ С УСЛОВИЕМ ОТБОРА
-- ============================================================

-- 3.1. Поиск детей по фамилии
SELECT * FROM children WHERE last_name = 'Иванов';

-- 3.2. Поиск программ длительностью более 50 часов
SELECT name, duration_hours FROM programs WHERE duration_hours > 50;

-- 3.3. Поиск кружков со свободными местами
SELECT group_id, program_id, teacher_id, free_seats
FROM `groups`
WHERE free_seats > 0;

-- 3.4. Выборка новых заявок
SELECT application_id, child_id, group_id, submitted_at
FROM applications
WHERE status = 'новая';

-- 3.5. Поиск преподавателей по специализации
SELECT last_name, first_name, specialization
FROM teachers
WHERE specialization LIKE '%программирование%';

-- ============================================================
-- 4. ВЫБОРКА ДАННЫХ С ГРУППИРОВКОЙ И ФИЛЬТРАЦИЕЙ
-- ============================================================

-- 4.1. Количество детей у каждого родителя
SELECT parent_id, COUNT(*) AS children_count
FROM children
GROUP BY parent_id;

-- 4.2. Родители, у которых больше одного ребёнка
SELECT parent_id, COUNT(*) AS children_count
FROM children
GROUP BY parent_id
HAVING COUNT(*) > 1;

-- 4.3. Количество заявок по каждому статусу
SELECT status, COUNT(*) AS applications_count
FROM applications
GROUP BY status;

-- 4.4. Программы, у которых более двух кружков
SELECT program_id, COUNT(*) AS groups_count
FROM `groups`
GROUP BY program_id
HAVING COUNT(*) > 2;

-- 4.5. Программы со средним числом свободных мест больше пяти
SELECT program_id, AVG(free_seats) AS average_free_seats
FROM `groups`
GROUP BY program_id
HAVING AVG(free_seats) > 5;

-- ============================================================
-- 5. ВЫБОРКА ДАННЫХ ИЗ НЕСКОЛЬКИХ ТАБЛИЦ
-- ============================================================

-- 5.1. Дети и сведения об их родителях
SELECT
    c.last_name  AS child_last_name,
    c.first_name AS child_first_name,
    p.last_name  AS parent_last_name,
    p.first_name AS parent_first_name,
    p.phone
FROM children AS c
JOIN parents AS p ON c.parent_id = p.parent_id;

-- 5.2. Заявки с именами детей
SELECT
    a.application_id,
    c.last_name,
    c.first_name,
    a.status,
    a.submitted_at
FROM applications AS a
JOIN children AS c ON a.child_id = c.child_id;

-- 5.3. Кружки, программы и преподаватели
SELECT
    g.group_id,
    p.name       AS program_name,
    t.last_name  AS teacher_last_name,
    t.first_name AS teacher_first_name,
    g.room,
    g.academic_year
FROM `groups` AS g
JOIN programs AS p ON g.program_id = p.program_id
JOIN teachers AS t ON g.teacher_id = t.teacher_id;

-- 5.4. Расписание занятий с названием программы
SELECT
    s.weekday,
    s.start_time,
    s.end_time,
    g.group_id,
    p.name AS program_name
FROM schedule AS s
JOIN `groups` AS g ON s.group_id = g.group_id
JOIN programs AS p ON g.program_id = p.program_id
ORDER BY s.weekday, s.start_time;

-- 5.5. Заявки, дети и назначенные программы
SELECT
    a.application_id,
    c.last_name,
    c.first_name,
    a.status,
    p.name AS program_name,
    a.submitted_at
FROM applications AS a
JOIN children AS c ON a.child_id = c.child_id
LEFT JOIN `groups` AS g ON a.group_id = g.group_id
LEFT JOIN programs AS p ON g.program_id = p.program_id;