---

USE db_4991_19_pr;

-- 1.1 Простое представление
CREATE VIEW All_Projects AS SELECT * FROM PROJECT;
SELECT * FROM All_Projects;

-- 1.2 PR_30+
CREATE VIEW PR_30_plus AS
SELECT p.project AS project_name, t.team AS team_name, p.cost
FROM PROJECT p JOIN WORK w ON p.shifr = w.projectid 
JOIN TEAM t ON w.teamid = t.id
WHERE p.duration > 30;
SELECT * FROM PR_30_plus;

-- 1.3 ST
CREATE VIEW ST AS
SELECT s.id, s.lastName, s.name, s.yearBirth, s.gender, t.team AS team_name
FROM STUDENTS s JOIN TEAM t ON s.team = t.id;
SELECT * FROM ST;

-- 1.4 Kom_ruk
CREATE VIEW Kom_ruk AS
SELECT CONCAT(m.lastName, ' ', m.name) AS Руководитель,
       t.team AS Команда,
       CONCAT(s.lastName, ' ', s.name) AS Лидер
FROM TEAM t
JOIN MENTOR m ON t.mentor = m.id
LEFT JOIN STUDENTS s ON t.lider = s.id;
SELECT * FROM Kom_ruk;

-- 1.5 Kol_kom
CREATE VIEW Kol_kom AS
SELECT Руководитель, COUNT(*) AS kol_vo_komand
FROM Kom_ruk
GROUP BY Руководитель;
SELECT * FROM Kol_kom;

-- 1.6 Pr_stat
CREATE VIEW Pr_stat AS
SELECT team_name, MIN(cost) AS min_cost, MAX(cost) AS max_cost,
       ROUND(AVG(cost), 2) AS avg_cost, COUNT(*) AS projects_count
FROM PR_30_plus
GROUP BY team_name;
SELECT * FROM Pr_stat;

-- 2.1 Tek_z
CREATE VIEW Tek_z AS
SELECT shifr, project, dateStart, dateFinish, duration, cost
FROM PROJECT WHERE dateFinish < CURDATE();
SELECT * FROM Tek_z;

-- 2.2 Добавление поля Zadol
ALTER TABLE PROJECT ADD COLUMN Zadol DECIMAL(10,2) NULL;

-- 2.3 Заполнение Zadol
UPDATE Tek_z SET Zadol = DATEDIFF(CURDATE(), dateFinish) * 1.5 * cost;

-- 3.1 About (CASE)
CREATE VIEW About AS
SELECT shifr, project, topic,
    CASE 
        WHEN topic = 'science' THEN 'Учащиеся, учителя, студенты, научные работники'
        WHEN topic = 'education' THEN 'Учащиеся, учителя, родители'
        WHEN topic = 'sport' THEN 'Спортсмены, тренеры'
        ELSE 'Общая аудитория'
    END AS target_audience
FROM PROJECT;
SELECT * FROM About;

-- 3.2 Project_Debt (CASE)
CREATE VIEW Project_Debt AS
SELECT shifr, project,
    CASE 
        WHEN DATEDIFF(dateFinish, dateStart) <= duration THEN 'Нет задолженности'
        ELSE 'Есть задолженность'
    END AS debt_status
FROM PROJECT;
SELECT * FROM Project_Debt;

-- 5. Индексы
CREATE INDEX ind_kom ON TEAM(team);

EXPLAIN SELECT * FROM TEAM WHERE team LIKE 'A%';
EXPLAIN SELECT s.lastName, t.team FROM STUDENTS s JOIN TEAM t ON s.team = t.id WHERE t.team = 'A&B';