USE demo;

DROP PROCEDURE IF EXISTS demo.p_dog_save_assessment_distraction_and_response;
DROP PROCEDURE IF EXISTS demo.p_dog_save_assessment_command_distraction_and_response;

DELIMITER //
CREATE PROCEDURE demo.p_dog_save_assessment_command_distraction_and_response (
    IN a_comment VARCHAR(500),
	IN a_guid BINARY(36),
    IN a_id_user INT,
    IN a_debug BIT
)
BEGIN
    DECLARE v_can_admin BIT;
    DECLARE v_can_create BIT;
	DECLARE v_code_type_error_bad_data VARCHAR(100);
    DECLARE v_id_access_level_edit INT;
    DECLARE v_id_change_set INT;
    DECLARE v_id_permission_dog_new INT;
    DECLARE v_id_type_error_bad_data INT;
    DECLARE v_time_start TIMESTAMP(6);
    
    DECLARE exit handler for SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
            @sqlstate = RETURNED_SQLSTATE
            , @errno = MYSQL_ERRNO
            , @text = MESSAGE_TEXT
		;
        
        ROLLBACK;
        
		CREATE TEMPORARY TABLE IF NOT EXISTS tmp_Msg_Error_Combined (
			  id_error INT NOT NULL PRIMARY KEY AUTO_INCREMENT
			, id_type INT
			, code VARCHAR(250)
			, msg TEXT NOT NULL
		);

        INSERT INTO tmp_Msg_Error_Combined (
			  id_type
			, code
            , msg
		)
        SELECT 
			  MET.id_type
            , @errno
            , @text
		FROM demo.CORE_Msg_Error_Type MET
        WHERE MET.code = 'MYSQL_ERROR'
		;

        SELECT 
            t_ERROR.id_error
            , t_ERROR.id_type
            , t_ERROR.code
            , ERROR_TYPE.name
            , ERROR_TYPE.description
            , ERROR_TYPE.is_breaking_error
            , ERROR_TYPE.background_colour
            , ERROR_TYPE.text_colour
            , t_ERROR.msg
        FROM tmp_Msg_Error_Combined t_ERROR
        INNER JOIN demo.CORE_Msg_Error_Type ERROR_TYPE ON t_ERROR.id_type = ERROR_TYPE.id_type
        ;

		DROP TABLE IF EXISTS tmp_Msg_Error_Combined;
    END;
    
    SET SESSION group_concat_max_len=15000;

    SET v_time_start := CURRENT_TIMESTAMP(6);
    SET v_code_type_error_bad_data := 'BAD_DATA';
    SET v_id_type_error_bad_data := (SELECT ERROR_TYPE.id_type FROM demo.CORE_Msg_Error_Type ERROR_TYPE WHERE ERROR_TYPE.code = v_code_type_error_bad_data LIMIT 1);
    SET v_id_permission_dog_new := (SELECT PERMISSION.id_permission FROM demo.DOG_Permission PERMISSION WHERE PERMISSION.code = 'DOG_EDIT' LIMIT 1);
    SET v_id_access_level_edit := (SELECT ACCESS_LEVEL.id_access_level FROM demo.DOG_Access_Level ACCESS_LEVEL WHERE ACCESS_LEVEL.code = 'EDIT' LIMIT 1);
    
    CALL demo.p_core_validate_guid ( a_guid );
    
    DROP TABLE IF EXISTS tmp_Assessment_Response_Save_ACDR;
    DROP TABLE IF EXISTS tmp_Distraction_Save_ACDR;
    DROP TABLE IF EXISTS tmp_Assessment_Command_Technique_Link_Save_ACDR;
    DROP TABLE IF EXISTS tmp_Assessment_Command_Modality_Link_Save_ACDR;
    DROP TABLE IF EXISTS tmp_Assessment_Command_Link_Save_ACDR;
    DROP TABLE IF EXISTS tmp_Assessment_Save_ACDR;
    
    CREATE TEMPORARY TABLE tmp_Assessment_Save_ACDR (
          id_temp INT
		, id_assessment INT 
		, id_assessment_old INT 
        , active BIT
        , is_new BIT
    );

    CREATE TEMPORARY TABLE tmp_Assessment_Command_Link_Save_ACDR (
          id_temp INT
		, id_link INT
		, id_link_old INT
        , id_assessment INT
        , active BIT
        , is_new BIT
    );

    CREATE TEMPORARY TABLE tmp_Assessment_Command_Modality_Link_Save_ACDR (
          id_temp INT
		, id_link INT
		, id_link_old INT
        , id_assessment_command_link INT
        , active BIT
        , is_new BIT
    );

    CREATE TEMPORARY TABLE tmp_Assessment_Command_Technique_Link_Save_ACDR (
          id_temp INT
		, id_link INT
		, id_link_old INT
        , id_assessment_command_link INT
        , active BIT
        , is_new BIT
    );
    
    CREATE TEMPORARY TABLE tmp_Distraction_Save_ACDR (
          id_temp INT
		, id_distraction INT 
		, id_distraction_old INT
        , id_assessment_command_link INT
        , active BIT
        , is_new BIT
    );
    
    CREATE TEMPORARY TABLE tmp_Assessment_Response_Save_ACDR (
          id_temp INT
		, id_response INT
		, id_response_old INT
        , id_assessment_command_link INT
        , active BIT
        , is_new BIT
    );
    
	CREATE TEMPORARY TABLE IF NOT EXISTS tmp_Msg_Error_Combined (
		  id_error INT NOT NULL PRIMARY KEY AUTO_INCREMENT
		, id_type INT
        , code VARCHAR(250)
        , msg TEXT NOT NULL
	);
        
    
    -- Get data from Temp table
    INSERT INTO tmp_Assessment_Save_ACDR (
		  id_temp
        , id_assessment
        , id_assessment_old
        , active
        , is_new
	)
    SELECT 
		  ASSESSMENT_T.id_temp
		, ASSESSMENT_T.id_assessment
		, ASSESSMENT_T.id_assessment
        , COALESCE(
              ASSESSMENT_T.active
            , ASSESSMENT.active
            , 1
        ) AS active
        , CASE WHEN COALESCE(ASSESSMENT_T.id_assessment, 0) < 1 THEN 1 ELSE 0 END AS is_new
	FROM demo.DOG_Assessment_Temp ASSESSMENT_T
    LEFT JOIN demo.DOG_Assessment ASSESSMENT ON ASSESSMENT_T.id_assessment = ASSESSMENT.id_assessment
    WHERE ASSESSMENT_T.guid = a_guid
    ;

    INSERT INTO tmp_Assessment_Command_Link_Save_ACDR (
		  id_temp
        , id_link
        , id_link_old
        , id_assessment
        , active
        , is_new
	)
    SELECT 
		  ASSESSMENT_COMMAND_LINK_T.id_temp
		, ASSESSMENT_COMMAND_LINK_T.id_link
		, ASSESSMENT_COMMAND_LINK_T.id_link
		, ASSESSMENT_COMMAND_LINK_T.id_assessment
        , COALESCE(
              ASSESSMENT_COMMAND_LINK_T.active
            , ASSESSMENT_COMMAND_LINK.active
            , 1
        ) AS active
        , CASE WHEN COALESCE(ASSESSMENT_COMMAND_LINK_T.id_link, 0) < 1 THEN 1 ELSE 0 END AS is_new
	FROM demo.DOG_Assessment_Command_Link_Temp ASSESSMENT_COMMAND_LINK_T
    LEFT JOIN demo.DOG_Assessment_Command_Link ASSESSMENT_COMMAND_LINK ON ASSESSMENT_COMMAND_LINK_T.id_link = ASSESSMENT_COMMAND_LINK.id_link
    WHERE ASSESSMENT_COMMAND_LINK_T.guid = a_guid
    ;

    INSERT INTO tmp_Assessment_Command_Modality_Link_Save_ACDR (
		  id_temp
        , id_link
        , id_link_old
        , id_assessment_command_link
        , active
        , is_new
	)
    SELECT 
		  ASSESSMENT_COMMAND_MODALITY_LINK_T.id_temp
		, ASSESSMENT_COMMAND_MODALITY_LINK_T.id_link
		, ASSESSMENT_COMMAND_MODALITY_LINK_T.id_link
		, ASSESSMENT_COMMAND_MODALITY_LINK_T.id_assessment_command_link
        , COALESCE(
              ASSESSMENT_COMMAND_MODALITY_LINK_T.active
            , ASSESSMENT_COMMAND_MODALITY_LINK.active
            , 1
        ) AS active
        , CASE WHEN COALESCE(ASSESSMENT_COMMAND_MODALITY_LINK_T.id_link, 0) < 1 THEN 1 ELSE 0 END AS is_new
	FROM demo.DOG_Assessment_Command_Modality_Link_Temp ASSESSMENT_COMMAND_MODALITY_LINK_T
    LEFT JOIN demo.DOG_Assessment_Command_Modality_Link ASSESSMENT_COMMAND_MODALITY_LINK ON ASSESSMENT_COMMAND_MODALITY_LINK_T.id_link = ASSESSMENT_COMMAND_MODALITY_LINK.id_link
    WHERE ASSESSMENT_COMMAND_MODALITY_LINK_T.guid = a_guid
    ;

    INSERT INTO tmp_Assessment_Command_Technique_Link_Save_ACDR (
		  id_temp
        , id_link
        , id_link_old
        , id_assessment_command_link
        , active
        , is_new
	)
    SELECT 
		  ASSESSMENT_COMMAND_TECHNIQUE_LINK_T.id_temp
		, ASSESSMENT_COMMAND_TECHNIQUE_LINK_T.id_link
		, ASSESSMENT_COMMAND_TECHNIQUE_LINK_T.id_link
		, ASSESSMENT_COMMAND_TECHNIQUE_LINK_T.id_assessment_command_link
        , COALESCE(
              ASSESSMENT_COMMAND_TECHNIQUE_LINK_T.active
            , ASSESSMENT_COMMAND_TECHNIQUE_LINK.active
            , 1
        ) AS active
        , CASE WHEN COALESCE(ASSESSMENT_COMMAND_TECHNIQUE_LINK_T.id_link, 0) < 1 THEN 1 ELSE 0 END AS is_new
	FROM demo.DOG_Assessment_Command_Technique_Link_Temp ASSESSMENT_COMMAND_TECHNIQUE_LINK_T
    LEFT JOIN demo.DOG_Assessment_Command_Technique_Link ASSESSMENT_COMMAND_TECHNIQUE_LINK ON ASSESSMENT_COMMAND_TECHNIQUE_LINK_T.id_link = ASSESSMENT_COMMAND_TECHNIQUE_LINK.id_link
    WHERE ASSESSMENT_COMMAND_TECHNIQUE_LINK_T.guid = a_guid
    ;

    INSERT INTO tmp_Distraction_Save_ACDR (
		  id_temp
        , id_distraction
        , id_distraction_old
        , id_assessment_command_link
        , active
        , is_new
	)
    SELECT 
		  DISTRACTION_T.id_temp
		, DISTRACTION_T.id_distraction
		, DISTRACTION_T.id_distraction
		, DISTRACTION_T.id_assessment_command_link
        , COALESCE(
              DISTRACTION_T.active
            , DISTRACTION.active
            , 1
        ) AS active
        , CASE WHEN COALESCE(DISTRACTION_T.id_distraction, 0) < 1 THEN 1 ELSE 0 END AS is_new
	FROM demo.DOG_Distraction_Temp DISTRACTION_T
    LEFT JOIN demo.DOG_Distraction DISTRACTION ON DISTRACTION_T.id_distraction = DISTRACTION.id_distraction
    WHERE DISTRACTION_T.guid = a_guid
    ;

    INSERT INTO tmp_Assessment_Response_Save_ACDR (
		  id_temp
        , id_response
        , id_response_old
        , id_assessment_command_link
        , active
        , is_new
	)
    SELECT 
		  ASSESSMENT_RESPONSE_T.id_temp
		, ASSESSMENT_RESPONSE_T.id_response
		, ASSESSMENT_RESPONSE_T.id_response
		, ASSESSMENT_RESPONSE_T.id_assessment_command_link
        , COALESCE(
              ASSESSMENT_RESPONSE_T.active
            , ASSESSMENT_RESPONSE.active
            , 1
        ) AS active
        , CASE WHEN COALESCE(ASSESSMENT_RESPONSE_T.id_response, 0) < 1 THEN 1 ELSE 0 END AS is_new
	FROM demo.DOG_Assessment_Response_Temp ASSESSMENT_RESPONSE_T
    LEFT JOIN demo.DOG_Assessment_Response ASSESSMENT_RESPONSE ON ASSESSMENT_RESPONSE_T.id_response = ASSESSMENT_RESPONSE.id_response
    WHERE ASSESSMENT_RESPONSE_T.guid = a_guid
    ;

    IF a_debug = 1 THEN
        SELECT 'Temp records';
        SELECT * FROM tmp_Assessment_Save_ACDR;
        SELECT COUNT(*) FROM tmp_Assessment_Save_ACDR;
        SELECT * FROM tmp_Assessment_Command_Link_Save_ACDR;
        SELECT COUNT(*) FROM tmp_Assessment_Command_Link_Save_ACDR;
        SELECT * FROM tmp_Assessment_Command_Modality_Link_Save_ACDR;
        SELECT COUNT(*) FROM tmp_Assessment_Command_Modality_Link_Save_ACDR;
        SELECT * FROM tmp_Assessment_Command_Technique_Link_Save_ACDR;
        SELECT COUNT(*) FROM tmp_Assessment_Command_Technique_Link_Save_ACDR;
        SELECT * FROM tmp_Distraction_Save_ACDR;
        SELECT COUNT(*) FROM tmp_Distraction_Save_ACDR;
        SELECT * FROM tmp_Assessment_Response_Save_ACDR;
        SELECT COUNT(*) FROM tmp_Assessment_Response_Save_ACDR;
    END IF;
    
    -- Permissions
    -- Can Create
	CALL demo.p_dog_calc_user_access(
          a_guid
        , 0 -- get_all_user
        , 0 -- get_inactive_user
        , a_id_user -- ids_user
        , '' -- a_auth0_ids_user
        , '' -- a_names_user
        , '' -- a_emails_user
        , 1 -- a_require_all_id_search_filters_met
        , 1 -- a_require_any_id_search_filters_met
        , 0 -- a_require_all_non_id_search_filters_met
        , 0 -- a_require_any_non_id_search_filters_met
        , v_id_permission_dog_new -- ids_permission
        , v_id_access_level_edit -- ids_access_level
        , 0 -- a_show_errors
        , 0 -- a_debug
    );
	
    SELECT
          IFNULL(CU_T.has_access, 0)
    INTO
          v_can_create
    FROM demo.DOG_Calc_User_Access_Temp CU_T
    WHERE CU_T.GUID = a_guid
    LIMIT 1
    ;
	
	CALL demo.p_dog_clear_calc_user_access(
		  a_guid
        , 0 -- a_debug
	);
    
    IF v_can_create = 0 THEN
		DELETE t_ME
        FROM tmp_Msg_Error_Combined t_ME
        WHERE t_ME.id_type <> v_id_type_error_no_permission
        ;
		INSERT INTO tmp_Msg_Error_Combined (
			  id_type
            , code
			, msg
		)
		VALUES (
			  v_id_type_error_no_permission
            , v_code_type_error_no_permission
			, 'You do not have permission to edit Assessment_Distraction_And_Responses.'
		)
        ;
    END IF;

    -- DELETE FROM tmp_Msg_Error_Combined_Save;
    -- Save Assessments
    -- CREATE TEMPORARY TABLE tmp_Assessment_Save_ACDR_Save_Error AS
    -- INSERT INTO tmp_Msg_Error_Combined_Save
    CALL demo.p_dog_save_assessment (
          a_comment
        , a_guid
        , a_id_user
        , 0 -- debug
    );

    /*
    INSERT INTO tmp_Msg_Error_Combined (
        id_type
        , code
        , msg
    )
    SELECT
          t_ASSESSMENT_SAVE_ERROR.id_type
        , t_ASSESSMENT_SAVE_ERROR.code
        , t_ASSESSMENT_SAVE_ERROR.msg
    FROM tmp_Assessment_Save_ACDR_Save_Error t_ASSESSMENT_SAVE_ERROR
    ;
    */

    -- Get New Assessment Ids
    IF NOT EXISTS (SELECT * FROM tmp_Msg_Error_Combined t_ERROR INNER JOIN demo.CORE_Msg_Error_Type ERROR_TYPE ON t_ERROR.id_type = ERROR_TYPE.id_type WHERE ERROR_TYPE.is_breaking_error = 1 LIMIT 1) THEN

        -- Update Temporary tables with new Ids
        UPDATE tmp_Assessment_Save_ACDR t_ASSESSMENT
        INNER JOIN demo.DOG_Assessment ASSESSMENT ON t_ASSESSMENT.id_temp = ASSESSMENT.id_temp
        -- INNER JOIN tmp_Distraction_Save_ACDR t_DISTRACTION ON t_ASSESSMENT.id_assessment_old = t_DISTRACTION.id_assessment
        LEFT JOIN tmp_Assessment_Command_Link_Save_ACDR t_ASSESSMENT_COMMAND_LINK ON t_ASSESSMENT.id_assessment_old = t_ASSESSMENT_COMMAND_LINK.id_assessment
        SET 
              t_ASSESSMENT.id_assessment = ASSESSMENT.id_assessment
            -- , t_DISTRACTION.id_assessment = ASSESSMENT.id_assessment
            , t_ASSESSMENT_COMMAND_LINK.id_assessment = ASSESSMENT.id_assessment
        WHERE 
                t_ASSESSMENT.active = 1
            AND t_ASSESSMENT.is_new = 1
        ;

        -- Update Staging tables with new Ids
        IF EXISTS (SELECT * FROM tmp_Assessment_Save_ACDR t_ASSESSMENT WHERE t_ASSESSMENT.id_assessment <> t_ASSESSMENT.id_assessment_old LIMIT 1) THEN

            START TRANSACTION;
                
                /*
                UPDATE demo.DOG_Distraction_Temp DISTRACTION_T
                INNER JOIN tmp_Distraction_Save_ACDR t_DISTRACTION ON DISTRACTION_T.id_temp = t_DISTRACTION.id_temp
                INNER JOIN tmp_Assessment_Save_ACDR t_ASSESSMENT ON t_DISTRACTION.id_assessment = t_ASSESSMENT.id_assessment
                SET DISTRACTION_T.id_assessment = t_DISTRACTION.id_assessment
                WHERE t_ASSESSMENT.id_assessment <> t_ASSESSMENT.id_assessment_old
                ;
                */
                
                UPDATE demo.DOG_Assessment_Command_Link_Temp ASSESSMENT_COMMAND_LINK_T
                INNER JOIN tmp_Assessment_Command_Link_Save_ACDR t_ASSESSMENT_COMMAND_LINK 
                    ON  t_ASSESSMENT_COMMAND_LINK.id_temp = ASSESSMENT_COMMAND_LINK_T.id_temp
                    AND ASSESSMENT_COMMAND_LINK_T.guid = a_guid
                INNER JOIN tmp_Assessment_Save_ACDR t_ASSESSMENT ON t_ASSESSMENT.id_assessment = t_ASSESSMENT_COMMAND_LINK.id_assessment
                SET ASSESSMENT_COMMAND_LINK_T.id_assessment = t_ASSESSMENT_COMMAND_LINK.id_assessment
                WHERE t_ASSESSMENT.id_assessment <> t_ASSESSMENT.id_assessment_old
                ;

		    COMMIT;
        END IF;
        
    END IF;

    IF a_debug = 1 THEN
        SELECT 'After align assessment Ids';
        SELECT * FROM tmp_Assessment_Save_ACDR;
        SELECT * FROM tmp_Assessment_Command_Link_Save_ACDR;
        SELECT * FROM tmp_Assessment_Command_Modality_Link_Save_ACDR;
        SELECT * FROM tmp_Assessment_Command_Technique_Link_Save_ACDR;
        SELECT * FROM tmp_Distraction_Save_ACDR;
        SELECT * FROM tmp_Assessment_Response_Save_ACDR;
    END IF;

    /*
    -- DELETE FROM tmp_Msg_Error_Combined_Save;
    -- Save Distractions
    -- CREATE TEMPORARY TABLE tmp_Distraction_Save_ACDR_Save_Error AS
    -- INSERT INTO tmp_Msg_Error_Combined_Save
    CALL demo.p_dog_save_distraction (
          a_comment
        , a_guid
        , a_id_user
        , 0 -- debug
    );

    / *
    INSERT INTO tmp_Msg_Error_Combined (
        id_type
        , code
        , msg
    )
    SELECT
          t_DISTRACTION_SAVE_ERROR.id_type
        , t_DISTRACTION_SAVE_ERROR.code
        , t_DISTRACTION_SAVE_ERROR.msg
    FROM tmp_Distraction_Save_ACDR_Save_Error t_DISTRACTION_SAVE_ERROR
    ;
    * /
    */

    -- DELETE FROM tmp_Msg_Error_Combined_Save;
    -- Save Assessment Command Links
    -- CREATE TEMPORARY TABLE tmp_Assessment_Command_Link_Save_ACDR_Save_Error AS
    -- INSERT INTO tmp_Msg_Error_Combined_Save
    CALL demo.p_dog_save_assessment_command_link (
          a_comment
        , a_guid
        , a_id_user
        , 0 -- debug
    );

    /*
    INSERT INTO tmp_Msg_Error_Combined (
        id_type
        , code
        , msg
    )
    SELECT
          t_ACML_SAVE_ERROR.id_type
        , t_ACML_SAVE_ERROR.code
        , t_ACML_SAVE_ERROR.msg
    FROM tmp_Assessment_Command_Link_Save_ACDR_Save_Error t_ACML_SAVE_ERROR
    ;
    */

    -- Get New Assessment Command Link Ids
    IF EXISTS (SELECT * FROM tmp_Msg_Error_Combined t_ERROR INNER JOIN demo.CORE_Msg_Error_Type ERROR_TYPE ON t_ERROR.id_type = ERROR_TYPE.id_type WHERE ERROR_TYPE.is_breaking_error = 1 LIMIT 1) THEN
        START TRANSACTION;
        
            DELETE ASSESSMENT
            FROM demo.DOG_Assessment ASSESSMENT
            INNER JOIN tmp_Assessment_Save_ACDR t_ASSESSMENT ON ASSESSMENT.id_assessment = t_ASSESSMENT.id_assessment
            WHERE t_ASSESSMENT.is_new = 1
            ;

        COMMIT;
    ELSE
        -- Update Temporary tables with new Ids
        UPDATE tmp_Assessment_Command_Link_Save_ACDR t_ASSESSMENT_COMMAND_LINK
        INNER JOIN demo.DOG_Assessment_Command_Link ASSESSMENT_COMMAND_LINK ON t_ASSESSMENT_COMMAND_LINK.id_temp = ASSESSMENT_COMMAND_LINK.id_temp
        LEFT JOIN tmp_Assessment_Command_Modality_Link_Save_ACDR t_ASSESSMENT_COMMAND_MODALITY_LINK ON t_ASSESSMENT_COMMAND_LINK.id_link_old = t_ASSESSMENT_COMMAND_MODALITY_LINK.id_assessment_command_link
        LEFT JOIN tmp_Assessment_Command_Technique_Link_Save_ACDR t_ASSESSMENT_COMMAND_TECHNIQUE_LINK ON t_ASSESSMENT_COMMAND_LINK.id_link_old = t_ASSESSMENT_COMMAND_TECHNIQUE_LINK.id_assessment_command_link
        LEFT JOIN tmp_Distraction_Save_ACDR t_DISTRACTION ON t_ASSESSMENT_COMMAND_LINK.id_link_old = t_DISTRACTION.id_assessment_command_link
        LEFT JOIN tmp_Assessment_Response_Save_ACDR t_ASSESSMENT_RESPONSE ON t_ASSESSMENT_COMMAND_LINK.id_link_old = t_ASSESSMENT_RESPONSE.id_assessment_command_link
        SET 
              t_ASSESSMENT_COMMAND_LINK.id_link = ASSESSMENT_COMMAND_LINK.id_link
            , t_ASSESSMENT_COMMAND_MODALITY_LINK.id_assessment_command_link = ASSESSMENT_COMMAND_LINK.id_link
            , t_ASSESSMENT_COMMAND_TECHNIQUE_LINK.id_assessment_command_link = ASSESSMENT_COMMAND_LINK.id_link
            , t_DISTRACTION.id_assessment_command_link = ASSESSMENT_COMMAND_LINK.id_link
            , t_ASSESSMENT_RESPONSE.id_assessment_command_link = ASSESSMENT_COMMAND_LINK.id_link
        WHERE 
                t_ASSESSMENT_COMMAND_LINK.active = 1
            AND t_ASSESSMENT_COMMAND_LINK.is_new = 1
        ;

        -- Update Staging tables with new Ids
        IF EXISTS (SELECT * FROM tmp_Assessment_Command_Link_Save_ACDR t_ASSESSMENT_COMMAND_LINK WHERE t_ASSESSMENT_COMMAND_LINK.id_link <> t_ASSESSMENT_COMMAND_LINK.id_link_old LIMIT 1) THEN

            START TRANSACTION;
                
                UPDATE tmp_Assessment_Command_Link_Save_ACDR t_ASSESSMENT_COMMAND_LINK
                /* demo.DOG_Assessment_Command_Link_Temp ASSESSMENT_COMMAND_LINK_T
                INNER JOIN tmp_Assessment_Command_Link_Save_ACDR t_ASSESSMENT_COMMAND_LINK ON ASSESSMENT_COMMAND_LINK_T.id_temp = t_ASSESSMENT_COMMAND_LINK.id_temp
                */
                LEFT JOIN tmp_Assessment_Command_Modality_Link_Save_ACDR t_ASSESSMENT_COMMAND_MODALITY_LINK ON t_ASSESSMENT_COMMAND_LINK.id_link = t_ASSESSMENT_COMMAND_MODALITY_LINK.id_assessment_command_link
                LEFT JOIN demo.DOG_Assessment_Command_Modality_Link_Temp ASSESSMENT_COMMAND_MODALITY_LINK_T 
                    ON  t_ASSESSMENT_COMMAND_MODALITY_LINK.id_temp = ASSESSMENT_COMMAND_MODALITY_LINK_T.id_temp
                    AND ASSESSMENT_COMMAND_MODALITY_LINK_T.guid = a_guid
                LEFT JOIN tmp_Assessment_Command_Technique_Link_Save_ACDR t_ASSESSMENT_COMMAND_TECHNIQUE_LINK ON t_ASSESSMENT_COMMAND_LINK.id_link = t_ASSESSMENT_COMMAND_TECHNIQUE_LINK.id_assessment_command_link
                LEFT JOIN demo.DOG_Assessment_Command_Technique_Link_Save_ACDR ASSESSMENT_COMMAND_TECHNIQUE_LINK_T 
                    ON  t_ASSESSMENT_COMMAND_TECHNIQUE_LINK.id_temp = ASSESSMENT_COMMAND_TECHNIQUE_LINK_T.id_temp
                    AND ASSESSMENT_COMMAND_TECHNIQUE_LINK_T.guid = a_guid
                LEFT JOIN tmp_Distraction_Save_ACDR t_DISTRACTION ON t_ASSESSMENT_COMMAND_LINK.id_link = t_DISTRACTION.id_assessment_command_link
                LEFT JOIN demo.DOG_Distraction_Save_ACDR DISTRACTION_T 
                    ON  t_DISTRACTION.id_temp = DISTRACTION_T.id_temp
                    AND DISTRACTION_T.guid = a_guid
                LEFT JOIN tmp_Assessment_Response_Save_ACDR t_ASSESSMENT_RESPONSE ON t_ASSESSMENT_COMMAND_LINK.id_link = t_ASSESSMENT_RESPONSE.id_assessment_command_link
                LEFT JOIN demo.DOG_Assessment_Response_Save_ACDR ASSESSMENT_RESPONSE_T 
                    ON  t_ASSESSMENT_RESPONSE.id_temp = ASSESSMENT_RESPONSE_T.id_temp
                    AND ASSESSMENT_RESPONSE_T.guid = a_guid
                SET 
                    ASSESSMENT_COMMAND_MODALITY_LINK_T.id_assessment_command_link = t_ASSESSMENT_COMMAND_LINK.id_link
                    , ASSESSMENT_COMMAND_TECHNIQUE_LINK_T.id_assessment_command_link = t_ASSESSMENT_COMMAND_LINK.id_link
                    , DISTRACTION_T.id_assessment_command_link = t_ASSESSMENT_COMMAND_LINK.id_link
                    , ASSESSMENT_RESPONSE_T.id_assessment_command_link = t_ASSESSMENT_COMMAND_LINK.id_link
                WHERE t_ASSESSMENT_COMMAND_LINK.id_link <> t_ASSESSMENT_COMMAND_LINK.id_link_old
                ;
                /*
                UPDATE demo.DOG_Assessment_Command_Modality_Link_Temp ASSESSMENT_COMMAND_MODALITY_LINK_T
                INNER JOIN tmp_Assessment_Command_Modality_Link_Save_ACDR t_ASSESSMENT_COMMAND_MODALITY_LINK ON ASSESSMENT_COMMAND_MODALITY_LINK_T.id_temp = t_ASSESSMENT_COMMAND_MODALITY_LINK.id_temp
                INNER JOIN tmp_Assessment_Command_Link_Save_ACDR t_ASSESSMENT_COMMAND_LINK ON t_ASSESSMENT_COMMAND_MODALITY_LINK.id_assessment_command_link = t_ASSESSMENT_COMMAND_LINK.id_link
                SET ASSESSMENT_COMMAND_MODALITY_LINK_T.id_assessment_command_link = t_ASSESSMENT_COMMAND_MODALITY_LINK.id_assessment_command_link
                WHERE t_ASSESSMENT_COMMAND_LINK.id_link <> t_ASSESSMENT_COMMAND_LINK.id_link_old
                ;

                UPDATE demo.DOG_Assessment_Command_Technique_Link_Temp ASSESSMENT_COMMAND_TECHNIQUE_LINK_T
                INNER JOIN tmp_Assessment_Command_Technique_Link_Save_ACDR t_ASSESSMENT_COMMAND_TECHNIQUE_LINK ON ASSESSMENT_COMMAND_TECHNIQUE_LINK_T.id_temp = t_ASSESSMENT_COMMAND_TECHNIQUE_LINK.id_temp
                INNER JOIN tmp_Assessment_Command_Link_Save_ACDR t_ASSESSMENT_COMMAND_LINK ON t_ASSESSMENT_COMMAND_TECHNIQUE_LINK.id_assessment_command_link = t_ASSESSMENT_COMMAND_LINK.id_link
                SET ASSESSMENT_COMMAND_TECHNIQUE_LINK_T.id_assessment_command_link = t_ASSESSMENT_COMMAND_TECHNIQUE_LINK.id_assessment_command_link
                WHERE t_ASSESSMENT_COMMAND_LINK.id_link <> t_ASSESSMENT_COMMAND_LINK.id_link_old
                ;

                UPDATE demo.DOG_Distraction_Temp DISTRACTION_T
                INNER JOIN tmp_Distraction_Save_ACDR t_DISTRACTION ON DISTRACTION_T.id_temp = t_DISTRACTION.id_temp
                INNER JOIN tmp_Assessment_Command_Link_Save_ACDR t_ASSESSMENT_COMMAND_LINK ON t_DISTRACTION.id_assessment_command_link = t_ASSESSMENT_COMMAND_LINK.id_link
                SET DISTRACTION_T.id_assessment_command_link = t_DISTRACTION.id_assessment_command_link
                WHERE t_ASSESSMENT_COMMAND_LINK.id_link <> t_ASSESSMENT_COMMAND_LINK.id_link_old
                ;
                
                UPDATE demo.DOG_Assessment_Response ASSESSMENT_RESPONSE
                INNER JOIN tmp_Assessment_Response t_ASSESSMENT_RESPONSE ON ASSESSMENT_RESPONSE.id_response = t_ASSESSMENT_RESPONSE.id_response
                INNER JOIN tmp_Assessment_Command_Link_Save_ACDR t_ASSESSMENT_COMMAND_LINK ON t_ASSESSMENT_RESPONSE.id_assessment_command_link = t_ASSESSMENT_COMMAND_LINK.id_link
                SET ASSESSMENT_COMMAND_LINK_T.id_assessment_command_link = t_ASSESSMENT_COMMAND_LINK.id_link
                WHERE t_ASSESSMENT_COMMAND_LINK.id_link <> t_ASSESSMENT_COMMAND_LINK.id_link_old
                ;
                */

		    COMMIT;
        END IF;
        
    END IF;

    IF a_debug = 1 THEN
        SELECT 'After align assessment Ids';
        SELECT * FROM tmp_Assessment_Save_ACDR;
        SELECT * FROM tmp_Assessment_Command_Link_Save_ACDR;
        SELECT * FROM tmp_Assessment_Command_Modality_Link_Save_ACDR;
        SELECT * FROM tmp_Assessment_Command_Technique_Link_Save_ACDR;
        SELECT * FROM tmp_Distraction_Save_ACDR;
        SELECT * FROM tmp_Assessment_Response_Save_ACDR;
    END IF;
    

    CALL demo.p_dog_save_assessment_command_modality_link (
          a_comment
        , a_guid
        , a_id_user
        , 0 -- debug
    );

    CALL demo.p_dog_save_assessment_command_technique_link (
          a_comment
        , a_guid
        , a_id_user
        , 0 -- debug
    );

    CALL demo.p_dog_save_distraction (
          a_comment
        , a_guid
        , a_id_user
        , 0 -- debug
    );

    -- DELETE FROM tmp_Msg_Error_Combined_Save;
    -- Save Assessment Responses
    -- CREATE TEMPORARY TABLE tmp_Assessment_Response_Save_ACDR_Save_Error AS
    -- INSERT INTO tmp_Msg_Error_Combined_Save
    CALL demo.p_dog_save_assessment_response (
          a_comment
        , a_guid
        , a_id_user
        , 0 -- debug
    );

    /*
    INSERT INTO tmp_Msg_Error_Combined (
        id_type
        , code
        , msg
    )
    SELECT
          t_ASSESSMENT_RESPONSE_SAVE_ERROR.id_type
        , t_ASSESSMENT_RESPONSE_SAVE_ERROR.code
        , t_ASSESSMENT_RESPONSE_SAVE_ERROR.msg
    FROM tmp_Assessment_Response_Save_ACDR_Save_Error t_ASSESSMENT_RESPONSE_SAVE_ERROR
    ;
    * /

    -- Get New Assessment Command Link Ids
    IF EXISTS (SELECT * FROM tmp_Msg_Error_Combined t_ERROR INNER JOIN demo.CORE_Msg_Error_Type ERROR_TYPE ON t_ERROR.id_type = ERROR_TYPE.id_type WHERE ERROR_TYPE.is_breaking_error = 1 LIMIT 1) THEN
        START TRANSACTION;
        
            DELETE ASSESSMENT_COMMAND_LINK
            FROM demo.DOG_Assessment_Command_Link ASSESSMENT_COMMAND_LINK
            INNER JOIN tmp_Assessment_Command_Link_Save_ACDR t_ASSESSMENT_COMMAND_LINK ON ASSESSMENT_COMMAND_LINK.id_link = t_ASSESSMENT_COMMAND_LINK.id_assessment_command_link
            INNER JOIN tmp_Assessment_Save_ACDR t_ASSESSMENT ON ASSESSMENT_COMMAND_LINK.id_assessment = t_ASSESSMENT.id_assessment
            WHERE 
                    t_ASSESSMENT_COMMAND_LINK.is_new = 1
                OR  t_ASSESSMENT.is_new = 1
            ;
        
            DELETE ASSESSMENT
            FROM demo.DOG_Assessment ASSESSMENT
            INNER JOIN tmp_Assessment_Save_ACDR t_ASSESSMENT ON ASSESSMENT.id_assessment = t_ASSESSMENT.id_assessment
            WHERE t_ASSESSMENT.is_new = 1
            ;

        COMMIT;
    END IF;
    */
    
    -- Errors
    SELECT 
          t_ERROR.id_error
		, t_ERROR.id_type
        , t_ERROR.code
        , ERROR_TYPE.name
        , ERROR_TYPE.description
        , ERROR_TYPE.is_breaking_error
        , ERROR_TYPE.background_colour
        , ERROR_TYPE.text_colour
		, t_ERROR.msg
    FROM tmp_Msg_Error_Combined t_ERROR
	INNER JOIN demo.CORE_Msg_Error_Type ERROR_TYPE ON t_ERROR.id_type = ERROR_TYPE.id_type
	;
    
	IF a_debug = 1 THEN
        SELECT * FROM tmp_Assessment_Save_ACDR;
        SELECT * FROM tmp_Assessment_Command_Link_Save_ACDR;
        SELECT * FROM tmp_Assessment_Command_Modality_Link_Save_ACDR;
        SELECT * FROM tmp_Assessment_Command_Technique_Link_Save_ACDR;
        SELECT * FROM tmp_Distraction_Save_ACDR;
        SELECT * FROM tmp_Assessment_Response_Save_ACDR;
	END IF;

    CALL demo.p_dog_clear_msg_error (
        a_guid
        , 0 -- debug
    );

    DROP TABLE IF EXISTS tmp_Assessment_Response_Save_ACDR_Save_Error;
    DROP TABLE IF EXISTS tmp_Distraction_Save_ACDR_Save_Error;
    DROP TABLE IF EXISTS tmp_Assessment_Command_Link_Save_ACDR_Save_Error;
    DROP TABLE IF EXISTS tmp_Assessment_Save_ACDR_Save_Error;
    DROP TABLE IF EXISTS tmp_Assessment_Response_Save_ACDR;
    DROP TABLE IF EXISTS tmp_Distraction_Save_ACDR;
    DROP TABLE IF EXISTS tmp_Assessment_Command_Technique_Link_Save_ACDR;
    DROP TABLE IF EXISTS tmp_Assessment_Command_Modality_Link_Save_ACDR;
    DROP TABLE IF EXISTS tmp_Assessment_Command_Link_Save_ACDR;
    DROP TABLE IF EXISTS tmp_Assessment_Save_ACDR;
    DROP TEMPORARY TABLE IF EXISTS tmp_Msg_Error_Combined;
    
	IF a_debug = 1 THEN
		CALL demo.p_core_debug_timing_reporting ( v_time_start );
	END IF;
END //
DELIMITER ;



/*

CALL demo.p_dog_save_assessment_command_distraction_and_response (
	'nipples'
    , 'ripplesipplenipplytippledipplykipple'
    , 1
    , 1
);


*/