---

USE db_4991_19_pr;

DELIMITER \[ -- ====================== 1.1.3 Процедуры для работы со STUDENTS ======================

-- 1. Добавление студента
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 \[ -- 2. Редактирование студента
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_rows;
END \[ -- 3. Удаление студента с проверкой
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 t.id IN (SELECT team FROM STUDENTS WHERE id = p_id);
    
    IF cnt > 0 THEN
        SET result_msg = 'Ошибка! Невозможно удалить — есть связанные записи!';
    ELSE
        DELETE FROM STUDENTS WHERE id = p_id;
        SET result_msg = 'Студент успешно удалён';
    END IF;
END \[ DELIMITER ;