DROP PROCEDURE IF EXISTS aps.sp_rds_lloelectroniccertificate;

CREATE OR REPLACE PROCEDURE aps.sp_rds_lloelectroniccertificate(
    "Xml" text DEFAULT NULL,
    "IsNew" integer = 1,
    "ID" uuid DEFAULT NULL::uuid,
    "QueueName" character varying DEFAULT 'rds.registry'::character varying,
    "dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone,
    "User" uuid DEFAULT NULL::uuid
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml xml := "Xml"::xml;
BEGIN
    
    IF ("Xml" IS NOT NULL)
    THEN
        DROP TABLE IF EXISTS temp;
        CREATE TEMP TABLE temp AS
            
            SELECT (XPATH('/ELECTRONIC_CERTIFICATE_APPLICATION/applicationdate/text()', x))[1]::timestamp AS applicationdate,
                   (XPATH('/ELECTRONIC_CERTIFICATE_APPLICATION/serial/text()', x))[1]::varchar(128)       AS serial,
                   (XPATH('/ELECTRONIC_CERTIFICATE_APPLICATION/number/text()', x))[1]::varchar(128)       AS number,
                   (XPATH('/ELECTRONIC_CERTIFICATE_APPLICATION/status/text()', x))[1]::integer            AS status,
                   (XPATH('/ELECTRONIC_CERTIFICATE_APPLICATION/recipeid/text()', x))[1]::uuid             AS recipeid
            FROM UNNEST(XPATH('/ELECTRONIC_CERTIFICATE_APPLICATION', p_xml)) AS x;
        
        UPDATE aps.t_recipe r
        SET certificate = CASE t.status
                              WHEN 1 THEN 1
                              WHEN 2 THEN 0
                              WHEN 3 THEN 2
                          END
        FROM temp t
        WHERE r.RecipeDate = t.applicationdate
          AND r.serial = t.serial
          AND r.number = t.number;
    
    END IF;
END;
$$