9 lab

-- 1 зад
DELIMITER //
CREATE TRIGGER salarypayments_delete_trigger
AFTER DELETE ON salaryPayments
FOR EACH ROW
BEGIN
    INSERT INTO salarypayments_log (
        operation,
        old_coach_id,
        old_month,
        old_year,
        old_salaryAmount,
        old_dateOfPayment,
        dateOfLog
    )
    VALUES (
        'DELETE',
        OLD.coach_id,
        OLD.month,
        OLD.year,
        OLD.salaryAmount,
        OLD.dateOfPayment,
        NOW()
    );
END;
//
DELIMITER ;


-- 2 зад
DELETE FROM salaryPayments;
INSERT INTO salaryPayments (coach_id, month, year, salaryAmount, dateOfPayment)
SELECT old_coach_id, old_month, old_year, old_salaryAmount, old_dateOfPayment
FROM salarypayments_log
WHERE operation = 'DELETE';


-- 3 зад
DELIMITER //
CREATE TRIGGER before_insert_student_sport_trigger
BEFORE INSERT ON student_sport
FOR EACH ROW
BEGIN
    DECLARE group_count INT;

    SELECT COUNT(*) INTO group_count
    FROM student_sport
    WHERE student_id = NEW.student_id;

    IF group_count >= 2 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Ученикът вече участва в две групи. Не може да бъде добавен в още една група.';
    END IF;
END;
//
DELIMITER ;


-- 4 зад
CREATE VIEW students_info AS
SELECT s.name AS student_name, COUNT(ss.student_id) AS group_count
FROM students s
JOIN student_sport ss ON s.id = ss.student_id
GROUP BY s.id;
SELECT * FROM students_info;


-- 5 зад
DELIMITER //
CREATE PROCEDURE GetStudentsByCoachName(IN coachName VARCHAR(255))
BEGIN
    SELECT st.name AS student_name, sg.id AS group_id, sp.name AS sport_name
    FROM students st
    JOIN student_sport ss ON st.id = ss.student_id
    JOIN sportGroups sg ON ss.sportGroup_id = sg.id
    JOIN sports sp ON sg.sport_id = sp.id
    JOIN coaches c ON sg.coach_id = c.id
    WHERE c.name = coachName;
END//
DELIMITER ;
CALL GetStudentsByCoachName("Ivan Todorov Petkov");


-- 6 зад
DELIMITER //
CREATE PROCEDURE GetCoachesBySportName(IN sportName VARCHAR(255))
BEGIN
    SELECT c.name AS coach_name, sg.location, sg.hourOfTraining, sg.dayOfWeek
    FROM sportGroups sg
    JOIN coaches c ON sg.coach_id = c.id
    JOIN sports s ON sg.sport_id = s.id
    WHERE s.name = sportName;
END//
DELIMITER ;
CALL GetCoachesBySportName("Football");