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('/body/applicationDate/text()', x))[1]::timestamp     AS applicationdate,
                   (XPATH('/body/serial/text()', x))[1]::varchar(128)           AS serial,
                   (XPATH('/body/number/text()', x))[1]::varchar(128)           AS number,
                   (XPATH('/body/status/text()', x))[1]::integer                AS status,
                   (XPATH('/body/certStatus/text()', x))[1]::integer            AS certStatus,
                   (XPATH('/body/recipeID/text()', x))[1]::uuid                 AS recipeid,
                   (XPATH('/body/refusalReason/text()', x))[1]::varchar(300)    AS refusalreason
            FROM UNNEST(XPATH('/ELECTRONIC_CERTIFICATE_APPLICATION/body', 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,
            certificateStatus = coalesce(t.certStatus, ap.CertificateStatus)
        FROM temp t
        WHERE r.RecipeDate = t.applicationdate
          AND r.serial = t.serial
          AND r.number = t.number;
    
    END IF;
END;
$$