use school_sport_clubs;
/* 1 */
DELIMITER $
DROP PROCEDURE IF EXISTS testProc $
CREATE PROCEDURE testProc()
BEGIN

Select c.name, sg.location
FROM coaches as c JOIN sportGroups as sg
ON c.id = sg.coach_id;
END $
DELIMITER ;

CALL testProc();

/* 2 */
DELIMITER $
DROP PROCEDURE IF EXISTS testProc $
CREATE PROCEDURE testProc(INOUT testParam VARCHAR(255))
BEGIN
SELECT testParam;
SET testParam = 'Georgi';
SELECT testParam;
END$
DELIMITER ;

set @test='Ivan';
CALL testProc(@test);
SELECT @test;

/* 3 */

use school_sport_clubs;
#drop procedure checkMothTax;
delimiter |
CREATE procedure checkMothTax(IN studId INT, IN groupId INT, IN paymentMonth INT, IN paymentYear INT)
BEGIN
DECLARE result char(1);
SET result = 0;
	IF( (SELECT paymentAmount
		FROM taxespayments
		WHERE student_id = studId
		AND group_id = groupId
		AND MONTH = paymentMonth
		AND year = paymentYear) IS NOT NULL)
    THEN
		SET result = 1;
	ELSE
		SET result = 0;
    END IF;

SELECT result as IsTaxPayed;
end;
|
delimiter ;

CALL `school_sport_clubs`.`checkMothTax`(1, 1,1,2022);

/* 4 */

use school_sport_clubs;

drop procedure if exists getPaymentPeriod;
delimiter |
CREATE procedure getPaymentPeriod(IN studId INT, IN groupId INT, IN paymentYear INT)
BEGIN
DECLARE countOfMonths tinyint;
DECLARE monthStr VARCHAR(10);
DECLARE yearStr varchar(10);
SET monthStr = 'MONTH';
SET yearStr = 'YEAR';

	SELECT COUNT(*)
    INTO countOfMonths
    FROM taxespayments
    WHERE student_id = studId
    AND group_id = groupId
    AND year = paymentYear;

    CASE countOfMonths
    WHEN 0 THEN SELECT 'This student has not paid for this group/year!' as PAYMENT_PERIOD;
    WHEN 1 THEN SELECT concat('ONE_', monthStr) as PAYMENT_PERIOD;
    WHEN 3 THEN SELECT concat('THREE_',monthStr, 'S') as PAYMENT_PERIOD;
    WHEN 6 THEN SELECT concat('SIX_',monthStr,'S') as PAYMENT_PERIOD;
    WHEN 12 THEN SELECT yearStr as PAYMENT_PERIOD;
    ELSE
		SELECT 	concat(countOfMonths,monthStr,'S') as PAYMENT_PERIOD;
	END CASE;
END;
|
DELIMITER ;

CALL getPaymentPeriod(1,1, 2022);

/* 5 */
use school_sport_clubs;


#drop procedure getAllPaymentsAmount;
delimiter |
CREATE procedure getAllPaymentsAmount(IN firstMonth INT, IN secMonth INT, IN paymentYear INT, IN studId INT)
BEGIN
	DECLARE iterator int;
	IF(firstMonth >= secMonth)
    THEN
		SELECT 'Please enter correct months!' as RESULT;
	ELSE IF((SELECT COUNT(*)
			FROM taxesPayments
			WHERE student_id =studId ) = 0)
        THEN SELECT 'Please enter correct student_id!' as RESULT;
		ELSE

	SET ITERATOR = firstMonth;

		WHILE(iterator >= firstMonth AND iterator <= secMonth)
		DO
			SELECT student_id, group_id, paymentAmount, month
			FROM taxespayments
			WHERE student_id = studId
			AND year = paymentYear
			AND month = iterator;

			SET iterator = iterator + 1;
		END WHILE;
		END IF;

    END IF;
END;
|
DELIMITER ;

CALL getAllPaymentsAmount(1,6,2022,1);

/* 6 */

use school_sport_clubs;
#drop procedure getAllPaymentsAmountOptimized;
delimiter |
CREATE procedure getAllPaymentsAmountOptimized(IN firstMonth INT, IN secMonth INT, IN paymentYear INT, IN studId INT)
BEGIN
    DECLARE iterator int;
    CREATE TEMPORARY TABLE tempTbl(
    student_id int,
    group_id int,
    paymentAmount double,
    month int
    ) ENGINE = Memory;


	IF(firstMonth >= secMonth)
    THEN
		SELECT 'Please enter correct months!' as RESULT;
	ELSE IF((SELECT COUNT(*)
			FROM taxesPayments
			WHERE student_id =studId ) = 0)
        THEN SELECT 'Please enter correct student_id!' as RESULT;
		ELSE

	SET ITERATOR = firstMonth;

		WHILE(iterator >= firstMonth AND iterator <= secMonth)
		DO
			INSERT INTO tempTbl
			SELECT student_id, group_id, paymentAmount, month
			FROM taxespayments
			WHERE student_id = studId
			AND year = paymentYear
			AND month = iterator;

			SET iterator = iterator + 1;
		END WHILE;
		END IF;

    END IF;
		SELECT *
        FROM tempTbl;
        DROP TABLE tempTbl;
END;
|
DELIMITER ;

CALL getAllPaymentsAmountOptimized(1,6,2022,1);

/* zadachi */

/* 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 AS 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');

/* 5 */
use transaction_test;

DROP PROCEDURE IF EXISTS transferFunds;
DELIMITER $
CREATE PROCEDURE transferFunds(in account1 INT, in account2 INT, in sumToTransfer DOUBLE)
BEGIN

DECLARE accountFunds1 double;
DECLARE accountFunds2 double;

SELECT amount INTO accountFunds1 FROM customer_accounts WHERE id = account1;
SELECT amount INTO accountFunds2 FROM customer_accounts WHERE id = account2;

IF(accountFunds1 < sumToTransfer)
THEN
	SELECT "No funds in the account.";
ELSE
	START TRANSACTION;
	UPDATE customer_accounts SET amount = amount - sumToTransfer WHERE id = account1;

    IF (ROW_COUNT() != 1)
	THEN
		SELECT "Error in transferring funds.";
        ROLLBACK;
	ELSE
		UPDATE customer_accounts SET amount = amount + sumToTransfer WHERE id = account2;

		IF (ROW_COUNT() != 1)
		THEN
			SELECT "Error in transferring funds.";
			ROLLBACK;
		END IF;
	END IF;

    SELECT "Success!";

END IF;

END $
DELIMITER ;

CALL transferFunds(3, 1, 5000);