USE `gateguard`;

DROP PROCEDURE IF EXISTS `sp_deleteUser_roles`;

DELIMITER $$

CREATE PROCEDURE `sp_deleteUser_roles` (
    IN p_USER_ROLE_ID INT,
    IN p_MODIFIED_BY INT,
	OUT v_ERROR_CODE VARCHAR(255),
	OUT v_ERROR_MESSAGE VARCHAR(255)
)
BEGIN
	DECLARE v_ID INT(11);
    DECLARE v_USER_ID INT(11);
    DECLARE v_ROLE_ID INT(11);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
		SET v_ERROR_MESSAGE = @text;
		SET v_ERROR_CODE = CONCAT(@errno, '|', @sqlstate);
		ROLLBACK;
    END;

    SET AUTOCOMMIT=0;
    START TRANSACTION;

    -- Fetch the user details before deletion
    SELECT ID, USER_ID, ROLE_ID
    INTO v_ID, v_USER_ID, v_ROLE_ID
    FROM user_roles
    WHERE ID = p_USER_ROLE_ID;

    -- Delete the user
    DELETE FROM user_roles
    WHERE ID = p_USER_ROLE_ID;

    -- Insert the audit record
    INSERT INTO user_roles_audit (
        MODIFIED_BY, 
        MODIFIED_ON, 
        MODIFICATION,
		ID,		
        USER_ID, 
        ROLE_ID
    ) VALUES (
        p_MODIFIED_BY,
        NOW(),
        'DELETED',
		v_ID, -- Use the fetched variable
        v_USER_ID, -- Use the fetched variable
        v_ROLE_ID  -- Use the fetched variable
    );

    COMMIT;
END$$

DELIMITER ;
