delimiter $$

DROP PROCEDURE IF EXISTS remove_diagnostic_from_student_history $$
DROP FUNCTION IF EXISTS can_diagnostic_be_removed_from_student_history $$

CREATE FUNCTION can_diagnostic_be_removed_from_student_history(
    input_activity_id_to_remove VARCHAR(32),
    input_score_to_remove INT(10),
    input_user_name VARCHAR(50),
    input_state_id VARCHAR(2),
    input_school_name VARCHAR(100),
    input_subject_id VARCHAR(32),
    input_completion_time TIMESTAMP,
    input_activity_id_of_replacement VARCHAR(32),
    input_score_of_replacement INT(10)
)
    RETURNS VARCHAR(255)
BEGIN

    DECLARE activity_type VARCHAR(32);
    DECLARE activity_status TINYINT(3);
    DECLARE replacement_activity_type VARCHAR(32);
    DECLARE replacement_activity_status TINYINT(3);
    DECLARE sched_id VARCHAR(32);
    DECLARE activity_id_of_most_recent_diag VARCHAR(32);
    DECLARE user_name VARCHAR(50);
    DECLARE state_id VARCHAR(2);
    DECLARE school_name VARCHAR(100);
    DECLARE subject_id VARCHAR(32);
    DECLARE completion_time TIMESTAMP;
    DECLARE score INT(10);

    -- Gather details about the activity

    SELECT act.activity_type, act.status, sched.schedule_id, u.user_name, u.state_id, inst.LEARNING_INSTITUTION_NAME, sched.subject_id, fact.completion_time, cat_fact.score
    FROM irp_activity act
    INNER JOIN irp_assignment assmt ON act.assignment_id = assmt.assignment_id
    INNER JOIN irp_schedule sched ON assmt.schedule_id = sched.schedule_id
    LEFT JOIN irs_assessment_fact fact ON fact.activity_id = act.activity_id
    LEFT JOIN irs_assessment_category_fact cat_fact ON cat_fact.assessment_fact_id = fact.activity_id
    LEFT JOIN ira_score_category cat ON cat.id = cat_fact.score_category_id
    INNER JOIN irp_student s ON s.id = sched.student_id
    INNER JOIN irp_user u ON u.id = s.id
    INNER JOIN irp_student_school_enrollment enroll ON enroll.id = s.current_school_enrollment_id
    INNER JOIN lyc_learning_institution inst ON inst.LEARNING_INSTITUTION_ID = enroll.school_id
    INNER JOIN irp_academic_year_for_onboard ayfo ON ayfo.onboard_id = u.current_onboard_id and ayfo.is_current=1
        WHERE act.activity_id = input_activity_id_to_remove
        AND ayfo.academic_year_id = sched.academic_year_id
        AND (cat.score_type = 'OVERALL' OR cat.score_type IS NULL)
    INTO
        activity_type, activity_status, sched_id, user_name, state_id, school_name, subject_id, completion_time, score;

    -- Validate that the acitivity is a completed diagnostic

    IF (activity_type IS NULL) THEN
        RETURN 'invalid_activity_id_or_not_from_current_academic_year';
    END IF;

    IF (activity_type != 'instruction_tests') THEN
        RETURN 'not_a_diagnostic_assessment';
    END IF;

    IF (activity_status != 2) THEN
        RETURN 'not_a_completed_diagnostic_assessment';
    END IF;

    -- Validate the corroborating details supplied for the diagnostic

    IF (user_name != input_user_name) THEN
        RETURN 'user_name_mismatch';
    END IF;

    IF (state_id != input_state_id) THEN
        RETURN 'state_id_mismatch';
    END IF;

    IF (school_name != input_school_name) THEN
        RETURN 'school_name_mismatch';
    END IF;

    IF (subject_id != input_subject_id) THEN
        RETURN 'subject_mismatch';
    END IF;

    IF (completion_time != input_completion_time) THEN
        RETURN 'completion_time_mismatch';
    END IF;

    IF (score != input_score_to_remove) THEN
        RETURN 'score_mismatch';
    END IF;

    -- Validate that the diagnostic assessment is not the student's most recent

    SELECT act.activity_id
    FROM irp_activity act
    INNER JOIN irp_assignment assmt ON act.assignment_id = assmt.assignment_id
    INNER JOIN irs_assessment_fact fact ON fact.activity_id = act.activity_id
    WHERE act.activity_type = 'instruction_tests'
        AND act.status = 2
        AND assmt.schedule_id = sched_id
    ORDER BY assmt.order_index DESC, fact.completion_time DESC
    LIMIT 1
    INTO activity_id_of_most_recent_diag;

    IF (input_activity_id_to_remove = activity_id_of_most_recent_diag) THEN
        RETURN 'diagnostic_assessment_to_remove_is_most_recent';
    END IF;

    -- Validate that an appropriate activity was specified as the replacement diagnostic assessment

    IF (input_activity_id_to_remove = input_activity_id_of_replacement) THEN
        RETURN 'replacement_diagnostic_assessment_is_same_as_one_being_removed';
    END IF;

    SELECT act.activity_type, act.status, cat_fact.score
    FROM irp_activity act
    INNER JOIN irp_assignment assmt ON act.assignment_id = assmt.assignment_id
    LEFT JOIN irs_assessment_category_fact cat_fact ON cat_fact.assessment_fact_id = act.activity_id
    LEFT JOIN ira_score_category cat ON cat.id = cat_fact.score_category_id
        WHERE act.activity_id = input_activity_id_of_replacement
        AND assmt.schedule_id = sched_id
        AND (cat.score_type = 'OVERALL' OR cat.score_type IS NULL)
    INTO replacement_activity_type, replacement_activity_status, score;

    IF (replacement_activity_type IS NULL) THEN
        RETURN 'replacement_diagnostic_assessment_does_not_exist_or_is_for_different_student_subject_or_year';
    END IF;

    IF (replacement_activity_type != 'instruction_tests') THEN
        RETURN 'replacement_activity_not_a_diagnostic_assessment';
    END IF;

    IF (replacement_activity_status != 2) THEN
        RETURN 'replacement_diagnostic_assessment_was_not_completed';
    END IF;

    IF (score != input_score_of_replacement) THEN
        RETURN 'replacement_score_mismatch';
    END IF;

    RETURN 'OK';

END $$


-- Removes the results of a completed diagnostic assessment from the history of a student (see IPA-2128).
-- Only a historical assessment (i.e. not the student's most recently completed diagnostic) may be removed.
-- Because the assessment is historical, the stduent's current lesson placement and assignments remain
-- unchanged.
--
-- In addition to specifying the ID of the activity to remove, additional corroborating details must also
-- be supplied to this procedure as a safeguard against the results of the wrong assessment from being purged..
-- The ID of a subsequent (replacement) diagnostic assessment must also be supplied and will be used as the
-- student's new base assessment in the case where the assessment being removed is currently acting as the
-- student's base assessment.
--
CREATE PROCEDURE remove_diagnostic_from_student_history(
    input_activity_id_to_remove VARCHAR(32),
    input_score_to_remove INT(10),
    input_user_name VARCHAR(50),
    input_state_id VARCHAR(2),
    input_school_name VARCHAR(100),
    input_subject_id VARCHAR(32),
    input_completion_time TIMESTAMP,
    input_activity_id_of_replacement VARCHAR(32),
    input_score_of_replacement INT(10)
)
BEGIN
    DECLARE change_base_diagnostic TINYINT(1);
    DECLARE error_text VARCHAR(255);

    -- Perform all updates within a single transaction

    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;

    START TRANSACTION;

    -- Validate that the specified activity is eligible to be removed

    SELECT can_diagnostic_be_removed_from_student_history(input_activity_id_to_remove, input_score_to_remove,
                input_user_name, input_state_id, input_school_name, input_subject_id, input_completion_time,
                input_activity_id_of_replacement, input_score_of_replacement)
        INTO error_text;
    IF (error_text != 'OK') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = error_text;
    END IF;

    -- Update the student's base diagnostic if the current base is the assessment that is being removed

    SELECT base_diagnostic FROM irp_activity where activity_id = input_activity_id_to_remove INTO change_base_diagnostic;
    IF (change_base_diagnostic = 1) THEN
        UPDATE irp_activity SET base_diagnostic = 1 WHERE activity_id = input_activity_id_of_replacement;
    END IF;

    -- Mark the activity as cancelled

    UPDATE irp_activity
    SET status = 4, status_detail = 'CANCELLED_INVALID_RESULT', completion_time = NULL, base_diagnostic = NULL
    WHERE activity_id = input_activity_id_to_remove;

    -- Remove the assessment facts for the completed assessment

    DELETE cat_calc
    FROM irs_assessment_category_calc cat_calc
    INNER JOIN irs_assessment_category_fact cat_fact ON cat_fact.id = cat_calc.assessment_category_fact_id
    WHERE cat_fact.assessment_fact_id = input_activity_id_to_remove;

    DELETE FROM irs_assessment_category_fact
    WHERE assessment_fact_id = input_activity_id_to_remove;

    DELETE FROM irs_assessment_fact
    WHERE activity_id = input_activity_id_to_remove;

    COMMIT;

END $$
