---

USE db_4991_19_pr;

-- =============================================
-- ПРАКТИЧЕСКАЯ РАБОТА №8 — ХРАНИМЫЕ ПРОЦЕДУРЫ
-- =============================================

DELIMITER \[ -- ====================== 1.1.1 и 1.1.2 Prepared Statements ======================
PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS "Гипотенуза"';
SET @a = 3;
SET @b = 4;
EXECUTE stmt1 USING @a, @b;

PREPARE stmt2 FROM 'SELECT id, lastName, name, yearBirth, gender, team 
                    FROM STUDENTS WHERE team = ?';
SET @c = 1;                    -- изменяйте номер команды
EXECUTE stmt2 USING @c;

DEALLOCATE PREPARE stmt1;
DEALLOCATE PREPARE stmt2;

-- ====================== 1.1.3 Процедуры для Students ======================

-- Добавление студента
DROP PROCEDURE IF EXISTS Student_Add \]
CREATE PROCEDURE Student_Add(
    IN p_lastName VARCHAR(50),
    IN p_name VARCHAR(50),
    IN p_yearBirth YEAR,
    IN p_gender CHAR(1),
    IN p_team INT
)
BEGIN
    INSERT INTO STUDENTS (lastName, name, yearBirth, gender, team)
    VALUES (p_lastName, p_name, p_yearBirth, p_gender, p_team);
    SELECT LAST_INSERT_ID() AS new_id;
END \[ -- Редактирование студента
DROP PROCEDURE IF EXISTS Student_Update \]
CREATE PROCEDURE Student_Update(
    IN p_id INT,
    IN p_lastName VARCHAR(50),
    IN p_name VARCHAR(50),
    IN p_yearBirth YEAR,
    IN p_gender CHAR(1),
    IN p_team INT
)
BEGIN
    UPDATE STUDENTS 
    SET lastName = p_lastName,
        name = p_name,
        yearBirth = p_yearBirth,
        gender = p_gender,
        team = p_team
    WHERE id = p_id;
    SELECT ROW_COUNT() AS updated;
END \[ -- Удаление студента с проверкой
DROP PROCEDURE IF EXISTS Student_Delete \]
CREATE PROCEDURE Student_Delete(IN p_id INT, OUT result_msg VARCHAR(150))
BEGIN
    DECLARE cnt INT DEFAULT 0;
    
    SELECT COUNT(*) INTO cnt 
    FROM WORK w 
    JOIN TEAM t ON w.teamid = t.id 
    WHERE t.lider = p_id 
       OR EXISTS (SELECT 1 FROM STUDENTS s WHERE s.id = p_id AND s.team = t.id);
    
    IF cnt > 0 THEN
        SET result_msg = 'Ошибка! Есть связанные записи в WORK.';
    ELSE
        DELETE FROM STUDENTS WHERE id = p_id;
        SET result_msg = 'Студент успешно удалён.';
    END IF;
END \[ -- ====================== 1.2 Count_rabota ======================
DROP PROCEDURE IF EXISTS Count_rabota \]
CREATE PROCEDURE Count_rabota(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM WORK;
END \[ -- ====================== 1.3 PRnm ======================
DROP PROCEDURE IF EXISTS PRnm \]
CREATE PROCEDURE PRnm(IN shift VARCHAR(10), OUT nm VARCHAR(100))
BEGIN
    SELECT project INTO nm FROM PROJECT WHERE shifr = shift LIMIT 1;
END \[ -- ====================== 1.4 Get_Project_By_Work ======================
DROP PROCEDURE IF EXISTS Get_Project_By_Work \]
CREATE PROCEDURE Get_Project_By_Work(IN work_id INT, OUT project_name VARCHAR(100))
BEGIN
    DECLARE pr_shifr VARCHAR(10);
    SELECT projectid INTO pr_shifr FROM WORK WHERE id = work_id LIMIT 1;
    IF pr_shifr IS NOT NULL THEN
        CALL PRnm(pr_shifr, project_name);
    ELSE
        SET project_name = 'Не найдено';
    END IF;
END \[ -- ====================== 1.5.1 Count по команде ======================
DROP PROCEDURE IF EXISTS Count_rabota_by_team \]
CREATE PROCEDURE Count_rabota_by_team(IN team_name VARCHAR(50), OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total 
    FROM WORK w JOIN TEAM t ON w.teamid = t.id 
    WHERE t.team = team_name;
END \[ -- ====================== 1.5.2 selectd (руководители по полу) ======================
DROP PROCEDURE IF EXISTS selectd \]
CREATE PROCEDURE selectd(IN p_gender CHAR(1))
BEGIN
    IF p_gender NOT IN ('м','ж') THEN
        SELECT 'Ошибка: укажите "м" или "ж"' AS error;
    ELSE
        SELECT lastName, name, gender FROM MENTOR WHERE gender = p_gender;
    END IF;
END \[ -- ====================== 1.5.3 GetInf ======================
DROP PROCEDURE IF EXISTS GetInf \]
CREATE PROCEDURE GetInf(IN p_shifr VARCHAR(10), OUT p_topic VARCHAR(50))
BEGIN
    SELECT topic INTO p_topic FROM PROJECT WHERE shifr = p_shifr LIMIT 1;
END \[ -- ====================== 1.5.4 ZU ======================
DROP PROCEDURE IF EXISTS ZU \]
CREATE PROCEDURE ZU(IN p_team_id INT, OUT cnt INT, OUT msg VARCHAR(100))
BEGIN
    IF (SELECT COUNT(*) FROM TEAM WHERE id = p_team_id) = 0 THEN
        SET cnt = 0;
        SET msg = 'Команда не найдена';
    ELSE
        SELECT COUNT(*) INTO cnt FROM STUDENTS WHERE team = p_team_id;
        SET msg = 'Успешно';
    END IF;
END \[ -- ====================== 1.6 Дата n раз ======================
DROP PROCEDURE IF EXISTS Print_Date_N \]
CREATE PROCEDURE Print_Date_N(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= n DO
        SELECT NOW() AS current_date, i AS iteration;
        SET i = i + 1;
    END WHILE;
END \[ -- ====================== 1.7 Склонение "задача" ======================
DROP PROCEDURE IF EXISTS Task_Form \]
CREATE PROCEDURE Task_Form(IN num INT, OUT result VARCHAR(50))
BEGIN
    CASE 
        WHEN (num % 10 = 1 AND num % 100 != 11) THEN SET result = CONCAT(num, ' задача');
        WHEN (num % 10 IN (2,3,4) AND num % 100 NOT IN (12,13,14)) THEN SET result = CONCAT(num, ' задачи');
        ELSE SET result = CONCAT(num, ' задач');
    END CASE;
END \[ -- ====================== 1.8 Проект + стоимость ======================
DROP PROCEDURE IF EXISTS Project_Info \]
CREATE PROCEDURE Project_Info(IN p_shifr VARCHAR(10), IN c INT, OUT p_name VARCHAR(100), OUT p_cost DECIMAL(12,2))
BEGIN
    SELECT project, cost INTO p_name, p_cost 
    FROM PROJECT WHERE shifr = p_shifr LIMIT 1;
    IF c = 1 THEN
        SET p_cost = p_cost * 2;
    END IF;
END \[ -- ====================== 1.9.1 Курсор — проекты команды ======================
DROP PROCEDURE IF EXISTS Team_Projects \]
CREATE PROCEDURE Team_Projects(IN team_name VARCHAR(50))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE prj VARCHAR(100);
    DECLARE cnt INT;
    
    DECLARE cur CURSOR FOR 
        SELECT p.project, COUNT(w.id)
        FROM PROJECT p
        JOIN WORK w ON p.shifr = w.projectid
        JOIN TEAM t ON w.teamid = t.id
        WHERE t.team = team_name
        GROUP BY p.project;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO prj, cnt;
        IF done THEN LEAVE read_loop; END IF;
        SELECT prj AS Проект, cnt AS Количество_работ;
    END LOOP;
    CLOSE cur;
END $$

DELIMITER ;

-- =============================================
-- ПРИМЕРЫ ВЫЗОВА ПРОЦЕДУР
-- =============================================

CALL Count_rabota(@total); SELECT @total;
CALL PRnm('101', @name); SELECT @name;
CALL Get_Project_By_Work(11, @pname); SELECT @pname;
CALL selectd('м');
CALL ZU(1, @cnt, @msg); SELECT @cnt, @msg;
CALL Print_Date_N(3);
CALL Task_Form(5, @word); SELECT @word;
CALL Project_Info('101', 1, @n, @c); SELECT @n, @c;
CALL Team_Projects('A&B');