DELIMITER //

CREATE TRIGGER update_results AFTER INSERT ON course_registration
FOR EACH ROW
BEGIN
    DECLARE total_units_registered INT;
    DECLARE total_units_passed INT;
    DECLARE total_cumulative_points DECIMAL(10, 2);
    DECLARE grade_point_average DECIMAL(10, 2);
    DECLARE course_id INT;
    DECLARE units INT;
    DECLARE point DECIMAL(10, 2);
    DECLARE index_counter INT DEFAULT 0;

    -- Initialize variables
    SET total_units_registered = 0;
    SET total_units_passed = 0;
    SET total_cumulative_points = 0;
    SET grade_point_average = 0;

    -- Create or update the results table with the duplicated data
    INSERT INTO results (matric_no, semester, session, registered_courses)
    VALUES (NEW.matric_no, NEW.semester, NEW.session, NEW.registered_courses)
    ON DUPLICATE KEY UPDATE registered_courses = NEW.registered_courses;

    -- Loop through each course in the registered_courses array
    course_loop: LOOP
        -- Get the course_id for the current index in the array
        SELECT JSON_UNQUOTE(JSON_EXTRACT(NEW.registered_courses, CONCAT('$[', index_counter, '].course_id'))) INTO course_id;

        -- If no course_id is found, exit the loop
        IF course_id IS NULL THEN
            LEAVE course_loop;
        END IF;

        -- Get the course_units and point for the current course_id
        SELECT course_units, point INTO units, point FROM courses WHERE course_id = course_id;

        -- Increment total_units_registered
        SET total_units_registered = total_units_registered + units;

        -- Fetch the score for the current course_id from result_upload
        SELECT score INTO @score FROM result_upload WHERE matric_no = NEW.matric_no AND course_id = course_id;

        -- Increment total_units_passed if score is greater than or equal to 40
        IF @score >= 40 THEN
            SET total_units_passed = total_units_passed + units;
        END IF;

        -- Calculate TCP for the current course
        IF point IS NOT NULL THEN
            SET total_cumulative_points = total_cumulative_points + (units * point);
        END IF;

        -- Increment the index counter
        SET index_counter = index_counter + 1;
    END LOOP;

    -- Calculate GPA
    IF total_units_registered > 0 THEN
        SET grade_point_average = total_cumulative_points / total_units_registered;
    END IF;

    -- Update TNUR, TNUP, TCP, and GPA in the results table
    UPDATE results 
    SET TNUR = total_units_registered, TNUP = total_units_passed, TCP = total_cumulative_points, GPA = grade_point_average
    WHERE matric_no = NEW.matric_no AND semester = NEW.semester AND session = NEW.session;
END;
//

DELIMITER ;