---

use db_4991_07_pr;

DELIMITER $$
DROP PROCEDURE IF EXISTS CalcExpression$$
CREATE PROCEDURE CalcExpression(IN a INT, IN b INT, IN c INT, OUT res INT)
BEGIN
    SET res = (a + b) * c;
END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS GetStudentsByTeam$$
CREATE PROCEDURE GetStudentsByTeam(IN team_name VARCHAR(50))
BEGIN
    SELECT s.* 
    FROM STUDENT s
    JOIN TEAM t ON s.team = t.id
    WHERE t.team = team_name;
END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS ManageStudents$$
CREATE PROCEDURE ManageStudents(
    IN operation VARCHAR(10),
    IN student_id INT,
    IN last_name VARCHAR(50),
    IN first_name VARCHAR(50),
    IN birth_year INT,
    IN gender CHAR(1),
    IN team_id INT
)
BEGIN
    IF operation = 'insert' THEN
        INSERT INTO STUDENT (id, lastName, name, yearBirth, gender, team)
        VALUES (student_id, last_name, first_name, birth_year, gender, team_id);
    ELSEIF operation = 'update' THEN
        UPDATE STUDENT 
        SET lastName = last_name, name = first_name, yearBirth = birth_year, 
            gender = gender, team = team_id
        WHERE id = student_id;
    ELSEIF operation = 'delete' THEN
        DELETE FROM STUDENT WHERE id = student_id;
    END IF;
END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS Count_rabota$$
CREATE PROCEDURE Count_rabota(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM WORK;
END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS PR_nm$$
CREATE PROCEDURE PR_nm(IN shifr VARCHAR(20), OUT nm VARCHAR(100))
BEGIN
    SELECT topic INTO nm FROM PROJECT WHERE shifr = shifr;
END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS GetProjectByWorkCode$$
CREATE PROCEDURE GetProjectByWorkCode(IN work_id INT, OUT project_name VARCHAR(100))
BEGIN
    DECLARE proj_shifr VARCHAR(20);
    SELECT project INTO proj_shifr FROM WORK WHERE id = work_id;
    CALL PR_nm(proj_shifr, project_name);
END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS Count_rabota_filtered$$
CREATE PROCEDURE Count_rabota_filtered(
    IN team_code INT, 
    IN project_shifr VARCHAR(20), 
    OUT total INT
)
BEGIN
    SELECT COUNT(*) INTO total 
    FROM WORK 
    WHERE (team = team_code OR team_code IS NULL)
      AND (project = project_shifr OR project_shifr IS NULL);
END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS selectd$$
CREATE PROCEDURE selectd(IN p_gender CHAR(1))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'Ошибка: неверные данные' AS error_message;
    END;
    
    IF p_gender NOT IN ('M', 'F') THEN
        SELECT 'Ошибка: пол должен быть M или F' AS error_message;
    ELSE
        SELECT t.*, s.lastName, s.name
        FROM TEAM t
        JOIN STUDENT s ON t.lider = s.id
        WHERE s.gender = p_gender;
    END IF;
END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS GetInf$$
CREATE PROCEDURE GetInf(IN project_shifr VARCHAR(20))
BEGIN
    SELECT shifr, topic, project, dateStart, dateFinish, duration, cost
    FROM PROJECT
    WHERE shifr = project_shifr;
END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS ZU$$
CREATE PROCEDURE ZU(IN team_code INT)
BEGIN
    DECLARE student_count INT;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'Ошибка: команда не найдена' AS error_message;
    END;
    
    SELECT COUNT(*) INTO student_count
    FROM STUDENT
    WHERE team = team_code;
    
    SELECT team_code AS kod_komandy, student_count AS kolichestvo_studentov;
END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS PrintDateNTimes$$
CREATE PROCEDURE PrintDateNTimes(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    
    WHILE i < n DO
        SELECT NOW() AS current_date;
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS FormatZadacha$$
CREATE PROCEDURE FormatZadacha(IN num INT, OUT result VARCHAR(50))
BEGIN
    DECLARE last_digit INT;
    DECLARE last_two_digits INT;
    
    SET last_digit = num % 10;
    SET last_two_digits = num % 100;
    
    IF last_two_digits >= 11 AND last_two_digits <= 14 THEN
        SET result = CONCAT(num, ' задач');
    ELSEIF last_digit = 1 THEN
        SET result = CONCAT(num, ' задача');
    ELSEIF last_digit >= 2 AND last_digit <= 4 THEN
        SET result = CONCAT(num, ' задачи');
    ELSE
        SET result = CONCAT(num, ' задач');
    END IF;
END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS GetProjectCost$$
CREATE PROCEDURE GetProjectCost(IN proj_shifr VARCHAR(20), IN c INT)
BEGIN
    IF c = 1 THEN
        SELECT project, cost * 2 AS doubled_cost
        FROM PROJECT
        WHERE shifr = proj_shifr;
    ELSE
        SELECT project, cost
        FROM PROJECT
        WHERE shifr = proj_shifr;
    END IF;
END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS TeamProjectsCount$$
CREATE PROCEDURE TeamProjectsCount(IN team_code INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE proj_shifr VARCHAR(20);
    DECLARE task_count INT;
    
    DECLARE cur CURSOR FOR
        SELECT project, COUNT(*)
        FROM WORK
        WHERE team = team_code
        GROUP BY project;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_results (
        project_shifr VARCHAR(20),
        tasks_count INT
    );
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO proj_shifr, task_count;
        IF done THEN
            LEAVE read_loop;
        END IF;
        INSERT INTO temp_results VALUES (proj_shifr, task_count);
    END LOOP;
    
    CLOSE cur;
    
    SELECT * FROM temp_results;
    DROP TEMPORARY TABLE temp_results;
END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS CheckProjectStage$$
CREATE PROCEDURE CheckProjectStage()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE team_name VARCHAR(100);
    DECLARE proj_name VARCHAR(100);
    DECLARE start_date DATE;
    DECLARE finish_date DATE;
    DECLARE duration_days INT;
    DECLARE result VARCHAR(20);
    
    DECLARE cur CURSOR FOR
        SELECT t.team, p.project, p.dateStart, p.dateFinish, p.duration
        FROM WORK w
        JOIN TEAM t ON w.team = t.id
        JOIN PROJECT p ON w.project = p.shifr;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_stage_results (
        team_name VARCHAR(100),
        project_name VARCHAR(100),
        stage_result VARCHAR(20)
    );
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO team_name, proj_name, start_date, finish_date, duration_days;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        IF DATEDIFF(finish_date, start_date) <= duration_days THEN
            SET result = 'Прошёл';
        ELSE
            SET result = 'Не прошёл';
        END IF;
        
        INSERT INTO temp_stage_results VALUES (team_name, proj_name, result);
    END LOOP;
    
    CLOSE cur;
    
    SELECT * FROM temp_stage_results;
    DROP TEMPORARY TABLE temp_stage_results;
END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS GetCompletedProjectsByYear$$
CREATE PROCEDURE GetCompletedProjectsByYear(IN year INT, OUT project_list TEXT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE proj_shifr VARCHAR(20);
    DECLARE start_date DATE;
    DECLARE finish_date DATE;
    DECLARE duration_days INT;
    
    DECLARE cur CURSOR FOR
        SELECT shifr, dateStart, dateFinish, duration
        FROM PROJECT
        WHERE YEAR(dateFinish) = year;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    SET project_list = '';
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO proj_shifr, start_date, finish_date, duration_days;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        IF DATEDIFF(finish_date, start_date) <= duration_days THEN
            IF project_list = '' THEN
                SET project_list = proj_shifr;
            ELSE
                SET project_list = CONCAT(project_list, ', ', proj_shifr);
            END IF;
        END IF;
    END LOOP;
    
    CLOSE cur;
END$$
DELIMITER ;