/* 1 */
use school_sport_clubs;
DELIMITER $
DROP PROCEDURE IF EXISTS GetCoachDetails;
CREATE PROCEDURE GetCoachDetails(IN coach_name VARCHAR(255))
BEGIN
    SELECT
        sg.location AS training_location,
        sg.dayOfWeek AS training_day,
        sg.hourOfTraining AS training_hour,
        s.name AS sport_name,
        st.name AS student_name,
        st.phone AS student_phone
    FROM
        coaches c
    JOIN
        sportGroups sg ON c.id = sg.coach_id
    JOIN
        sports s ON sg.sport_id = s.id
    JOIN
        student_sport ss ON sg.id = ss.sportGroup_id
    JOIN
        students st ON ss.student_id = st.id
    WHERE
        c.name = coach_name;
END $

DELIMITER ;

CALL GetCoachDetails('Ivan Todorov Petkov');

/* 2 */

DELIMITER $
DROP PROCEDURE IF EXISTS GetSportDetails;
CREATE PROCEDURE GetSportDetails(IN sport_id INT)
BEGIN
    SELECT
        s.name AS sport_name,
        st.name AS student_name,
        c.name AS coach_name
    FROM
        sports s
    JOIN
        sportGroups sg ON s.id = sg.sport_id
    JOIN
        coaches c ON sg.coach_id = c.id
    JOIN
        student_sport ss ON sg.id = ss.sportGroup_id
    JOIN
        students st ON ss.student_id = st.id
    WHERE
        s.id = sport_id;
END $

DELIMITER ;

CALL GetSportDetails(1);

/* 3 */
DELIMITER $
DROP PROCEDURE IF EXISTS GetAveragePaymentsByStudent;
CREATE PROCEDURE GetAveragePaymentsByStudent(IN student_name VARCHAR(255), IN payment_year YEAR)
BEGIN
    SELECT
        AVG(tp.paymentAmount) AS average_payment
    FROM
        students as st
    JOIN
        taxesPayments as tp ON st.id = tp.student_id
    WHERE
        st.name = student_name
        AND YEAR(tp.dateOfPayment) = payment_year;
END $

DELIMITER ;

CALL GetAveragePaymentsByStudent('Iliyan Ivanov', 2024);

/* 4 */
DELIMITER $

CREATE PROCEDURE GetCoachGroupsCount(IN coach_name VARCHAR(255))
BEGIN
    DECLARE groups_count INT;

    SELECT COUNT(*) INTO groups_count
    FROM sportGroups sg
    JOIN coaches c ON sg.coach_id = c.id
    WHERE c.name = coach_name;

    IF groups_count > 0 THEN
        SELECT groups_count AS coach_groups_count;
    ELSE
        SELECT 'Този треньор не води никакви групи.' AS message;
    END IF;
END $

DELIMITER ;

CALL GetCoachGroupsCount('Ivan Todorov Petkov');