---

USE it_cube;

-- #########################################################
-- #   БЛОК 1. ЗАПРОСЫ НА СОЕДИНЕНИЕ ТАБЛИЦ (5 запросов)   #
-- #########################################################


-- =========================================================
-- ЗАПРОС 1. INNER JOIN + LEFT JOIN
-- Что делает: выводит список всех заявок с ФИО ребёнка,
-- ФИО и телефоном родителя, названием программы и статусом.
-- =========================================================
SELECT 
    a.application_id,
    CONCAT(c.last_name, ' ', c.first_name, ' ', IFNULL(c.middle_name, '')) AS child_fio,
    c.birth_date,
    CONCAT(p.last_name, ' ', p.first_name) AS parent_fio,
    p.phone AS parent_phone,
    pr.name AS program_name,
    a.status,
    a.submitted_at
FROM `applications` a
INNER JOIN `children` c   ON a.child_id = c.child_id
INNER JOIN `parents`  p   ON c.parent_id = p.parent_id
LEFT  JOIN `groups`   g   ON a.group_id = g.group_id
LEFT  JOIN `programs` pr  ON g.program_id = pr.program_id
ORDER BY a.submitted_at DESC;


-- =========================================================
-- ЗАПРОС 2. LEFT JOIN + GROUP BY
-- Что делает: выводит список всех групп с названием программы,
-- ФИО преподавателя, аудиторией и количеством заявок.
-- =========================================================
SELECT 
    g.group_id,
    pr.name AS program_name,
    CONCAT(t.last_name, ' ', t.first_name) AS teacher_fio,
    t.specialization,
    g.room,
    g.academic_year,
    g.max_seats,
    g.free_seats,
    COUNT(a.application_id) AS applications_count
FROM `groups` g
LEFT JOIN `programs` pr      ON g.program_id = pr.program_id
LEFT JOIN `teachers` t       ON g.teacher_id = t.teacher_id
LEFT JOIN `applications` a   ON g.group_id = a.group_id
GROUP BY g.group_id, pr.name, teacher_fio, t.specialization,
         g.room, g.academic_year, g.max_seats, g.free_seats
ORDER BY applications_count DESC;


-- =========================================================
-- ЗАПРОС 3. МНОГОТАБЛИЧНЫЙ JOIN (4 таблицы)
-- Что делает: выводит расписание с программой и преподавателем.
-- =========================================================
SELECT 
    s.lesson_id,
    s.weekday,
    s.start_time,
    s.end_time,
    g.room,
    pr.name AS program_name,
    CONCAT(t.last_name, ' ', t.first_name, ' ', IFNULL(t.middle_name, '')) AS teacher_fio,
    g.academic_year
FROM `schedule` s
INNER JOIN `groups`   g   ON s.group_id = g.group_id
INNER JOIN `programs` pr  ON g.program_id = pr.program_id
INNER JOIN `teachers` t   ON g.teacher_id = t.teacher_id
ORDER BY FIELD(s.weekday, 'Пн','Вт','Ср','Чт','Пт','Сб','Вс'), s.start_time;


-- =========================================================
-- ЗАПРОС 4. JOIN + АГРЕГАЦИЯ + CASE
-- Что делает: статистика заявок по каждому родителю.
-- =========================================================
SELECT 
    p.parent_id,
    CONCAT(p.last_name, ' ', p.first_name) AS parent_fio,
    p.phone,
    p.email,
    COUNT(DISTINCT c.child_id) AS children_count,
    COUNT(a.application_id)    AS total_applications,
    SUM(CASE WHEN a.status = 'зачислен'  THEN 1 ELSE 0 END) AS enrolled,
    SUM(CASE WHEN a.status = 'одобрена'  THEN 1 ELSE 0 END) AS approved,
    SUM(CASE WHEN a.status = 'отклонена' THEN 1 ELSE 0 END) AS rejected
FROM `parents` p
LEFT JOIN `children` c      ON p.parent_id = c.parent_id
LEFT JOIN `applications` a  ON c.child_id = a.child_id
GROUP BY p.parent_id, parent_fio, p.phone, p.email
ORDER BY total_applications DESC;


-- =========================================================
-- ЗАПРОС 5. JOIN (3 таблицы)
-- Что делает: история изменения статусов заявок.
-- =========================================================
SELECT 
    h.history_id,
    h.application_id,
    CONCAT(c.last_name, ' ', c.first_name) AS child_fio,
    h.old_status,
    h.new_status,
    h.changed_at,
    a.status AS current_status
FROM `application_history` h
INNER JOIN `applications` a ON h.application_id = a.application_id
INNER JOIN `children` c     ON a.child_id = c.child_id
ORDER BY h.changed_at DESC;



-- #########################################################
-- #   БЛОК 2. CRUD-ЗАПРОСЫ (6 запросов: 2+2+2)            #
-- #########################################################


-- =========================================================
-- ЗАПРОС 6. INSERT — ДОБАВЛЕНИЕ
-- Новый родитель + его ребёнок.
-- =========================================================
INSERT INTO `parents` (last_name, first_name, middle_name, phone, email, snils, address)
VALUES ('Сидоров', 'Пётр', 'Николаевич', '+79161234567', 'sidorov@mail.ru',
        '12345678901', 'г. Москва, ул. Ленина, д. 10, кв. 5');

INSERT INTO `children` (last_name, first_name, middle_name, birth_date, snils, parent_id, gender)
VALUES ('Сидоров', 'Артём', 'Петрович', '2014-05-12', '12345678902',
        LAST_INSERT_ID(), 'М');


-- =========================================================
-- ЗАПРОС 7. INSERT — ДОБАВЛЕНИЕ
-- Новая заявка для только что созданного ребёнка.
-- =========================================================
INSERT INTO `applications` (child_id, group_id, status, comment)
VALUES (
    (SELECT child_id FROM `children` WHERE snils = '12345678902' LIMIT 1),
    (SELECT group_id FROM `groups` WHERE academic_year = '2025-2026' LIMIT 1),
    'новая',
    'Заявка подана через сайт'
);


-- =========================================================
-- ЗАПРОС 8. DELETE — УДАЛЕНИЕ
-- Удаление старых отклонённых заявок (> 6 месяцев).
-- =========================================================
DELETE FROM `applications`
WHERE status = 'отклонена'
  AND submitted_at < DATE_SUB(NOW(), INTERVAL 6 MONTH);


-- =========================================================
-- ЗАПРОС 9. DELETE — УДАЛЕНИЕ
-- Удаление пустых групп за 2023-2024 учебный год.
-- =========================================================
DELETE FROM `groups`
WHERE academic_year = '2023-2024'
  AND group_id NOT IN (
      SELECT group_id FROM `applications` WHERE group_id IS NOT NULL
  );


-- =========================================================
-- ЗАПРОС 10. UPDATE + INSERT — ИЗМЕНЕНИЕ
-- Смена статуса заявки №1 на "одобрена" + запись в историю.
-- =========================================================
UPDATE `applications`
SET status     = 'одобрена',
    comment    = CONCAT(IFNULL(comment, ''), ' | Одобрено администратором'),
    updated_at = NOW()
WHERE application_id = 1;

INSERT INTO `application_history` (application_id, old_status, new_status)
VALUES (1, 'новая', 'одобрена');


-- =========================================================
-- ЗАПРОС 11. UPDATE × 3 — ИЗМЕНЕНИЕ
-- Перевод заявки №2 в группу №2 + корректировка мест.
-- =========================================================
UPDATE `applications`
SET group_id   = 2,
    status     = 'зачислен',
    updated_at = NOW()
WHERE application_id = 2;

UPDATE `groups` SET free_seats = free_seats + 1 WHERE group_id = 1;
UPDATE `groups` SET free_seats = free_seats - 1 WHERE group_id = 2;



-- #########################################################
-- #   БЛОК 3. МАССОВАЯ ВСТАВКА (1 запрос)                 #
-- #########################################################


-- =========================================================
-- ЗАПРОС 12. МНОЖЕСТВЕННЫЙ INSERT
-- 5 преподавателей + 5 занятий одним запросом.
-- =========================================================

-- 12.1 Пять преподавателей одним INSERT
INSERT INTO `teachers` 
    (last_name, first_name, middle_name, specialization, phone, email, hire_date)
VALUES
    ('Иванов',   'Иван',   'Иванович',   'Программирование Python', '+79001110001', 'ivanov@itcube.ru',    '2024-09-01'),
    ('Петрова',  'Мария',  'Сергеевна',  'Робототехника',           '+79001110002', 'petrova@itcube.ru',   '2024-09-01'),
    ('Кузнецов', 'Алексей','Дмитриевич', 'Веб-разработка',          '+79001110003', 'kuznetsov@itcube.ru', '2024-10-15'),
    ('Смирнова', 'Ольга',  'Андреевна',  '3D-моделирование',        '+79001110004', 'smirnova@itcube.ru',  '2024-11-01'),
    ('Фёдоров',  'Денис',  'Олегович',   'Кибербезопасность',       '+79001110005', 'fedorov@itcube.ru',   '2025-01-10');

-- 12.2 Пять занятий в расписание одним INSERT
INSERT INTO `schedule` (group_id, weekday, start_time, end_time)
VALUES
    (1, 'Пн', '15:00:00', '16:30:00'),
    (1, 'Ср', '15:00:00', '16:30:00'),
    (1, 'Пт', '15:00:00', '16:30:00'),
    (2, 'Вт', '16:45:00', '18:15:00'),
    (2, 'Чт', '16:45:00', '18:15:00');