---

DELIMITER //
CREATE FUNCTION Nazv_Pr(sh INT)
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    DECLARE pr_name VARCHAR(100);
    SELECT project INTO pr_name 
    FROM PROJECT 
    WHERE shift = sh 
    LIMIT 1;
    RETURN COALESCE(pr_name, 'Проект не найден');
END //
DELIMITER ;


DELIMITER //
CREATE FUNCTION KolRabot(team_name VARCHAR(50))
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE cnt INT;
    SELECT COUNT(*) INTO cnt
    FROM WORK w
    JOIN TEAM t ON w.team = t.id
    WHERE t.team = team_name;
    RETURN cnt;
END //
DELIMITER ;


DELIMITER //
CREATE FUNCTION Pr_mes(mes INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE cnt INT;
    SELECT COUNT(*) INTO cnt
    FROM PROJECT
    WHERE MONTH(dateStart) = mes;
    RETURN cnt;
END //
DELIMITER ;


DELIMITER //
CREATE FUNCTION Nazv_MaxPr(team_name VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    DECLARE pr_name VARCHAR(100);
    SELECT p.project INTO pr_name
    FROM PROJECT p
    JOIN WORK w ON p.shift = w.project
    JOIN TEAM t ON w.team = t.id
    WHERE t.team = team_name
    ORDER BY p.cost DESC
    LIMIT 1;
    RETURN COALESCE(pr_name, 'Нет проектов');
END //
DELIMITER ;


DELIMITER //
CREATE FUNCTION Dni_pr(sh INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE start_date DATE;
    SELECT dateStart INTO start_date 
    FROM PROJECT 
    WHERE shift = sh 
    LIMIT 1;
    
    IF start_date IS NULL THEN
        RETURN 0;
    END IF;
    
    RETURN Kol_Dney(start_date, CURRENT_DATE());
END //
DELIMITER ;


DELIMITER //
CREATE PROCEDURE PrF()
BEGIN
    SELECT 
        ROUTINE_TYPE AS 'Тип',
        ROUTINE_NAME AS 'Название',
        ROUTINE_DEFINITION AS 'Определение'
    FROM information_schema.ROUTINES
    WHERE ROUTINE_SCHEMA = DATABASE()
    ORDER BY ROUTINE_TYPE, ROUTINE_NAME;
END //
DELIMITER ;

-- Вызов
CALL PrF();