DROP PROCEDURE IF EXISTS sp_updaterecipelsname;

CREATE OR REPLACE PROCEDURE aps.sp_updaterecipelsname(
    recipeid uuid
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_recipeid uuid := recipeid;
    v_agg      VARCHAR(1000);
    v_typerec  INTEGER;

BEGIN
    
    SELECT r.status,
           COALESCE(trn.namerus, mnn.namerus)
    INTO
        v_typerec,
        v_agg
    FROM aps.t_recipe r
    LEFT JOIN pharm.t_c_trn trn ON trn.ctrnid = r.ctrn
    LEFT JOIN pharm.t_c_mnn mnn ON mnn.cmnnid = r.cmnn
    WHERE r.recipeid = p_recipeid;
    
    IF v_typerec = 1 THEN
        SELECT TRIM(SUBSTRING((SELECT STRING_AGG
                                      (TRIM(CASE
                                                WHEN trn.producttype = 0 THEN
                                                    COALESCE(trn.namerus, '') || ' ' ||
                                                    COALESCE(mf.name, '') || ' ' ||
                                                    COALESCE(dv.value, '') || ', ' ||
                                                    COALESCE(mp.name, '')
                                                ELSE
                                                    COALESCE(trn.namerus, '') || ' ' ||
                                                    COALESCE(dv.value, '') || ', ' ||
                                                    COALESCE(mp.name, '')
                                            END
                                       ),
                                       ', '
                                      )
                               FROM pharm.t_bill_of_goods bg
                               JOIN pharm.t_c_trn trn ON trn.ctrnid = bg.ctrn
                               LEFT JOIN pharm.t_medication_forms mf ON mf.medicationformsid = bg.medicationforms
                               LEFT JOIN pharm.s_dlo_doseview dv ON dv.id = bg.doseview
                               LEFT JOIN pharm.t_medicament_producer mp
                                   ON mp.medicamentproducerid = bg.medicamentproducer
                               WHERE EXISTS (SELECT 1
                                             FROM aps.t_recipe_elements re
                                             JOIN stor.t_remains rem ON rem.remainsid = re.remains
                                             WHERE re.recipe = p_recipeid
                                               AND rem.billofgoods = bg.billofgoodsid
                                            )
                              )::TEXT,
                              1,
                              NULL
                    )
               )::varchar(1000)
        INTO v_agg;

    END IF;
    
    UPDATE aps.t_recipe
    SET lsname = v_agg
    WHERE recipeid = p_recipeid;

END
$$