DROP PROCEDURE IF EXISTS aps.sp_updaterecipelaststatushistory;

CREATE OR REPLACE PROCEDURE aps.sp_updaterecipelaststatushistory(
    recipeid uuid
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_recipeid uuid := recipeid;
BEGIN
    
    WITH lateststatushistory AS
             (SELECT sh.creationdatetime,
                     sh.recipe,
                     sh.recipestatushistoryid
              FROM aps.t_recipe_status_history sh
              WHERE sh.recipe = p_recipeid
                AND NOT EXISTS (SELECT 1
                                FROM aps.t_recipe_status_history sh2
                                WHERE sh.recipe = sh2.recipe
                                  AND sh2.creationdatetime > sh.creationdatetime
                               )
             )
    UPDATE aps.t_recipe r
    SET laststatushistory = lsh.recipestatushistoryid
    FROM lateststatushistory lsh
    WHERE r.recipeid = lsh.recipe;
END;
$$;