DROP PROCEDURE IF EXISTS akuz.sp_rds_lloelectroniccertificate;

CREATE OR REPLACE PROCEDURE akuz.sp_rds_lloelectroniccertificate(
    "Xml" text DEFAULT NULL,
    "IsNew" integer DEFAULT 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_id        uuid         := "ID";
    p_queuename varchar(128) := "QueueName";
    p_xml       xml          := "Xml"::xml;
    v_content   text;
BEGIN
    
    IF (p_xml IS NULL) THEN
        SELECT ap.signadata
        INTO v_content
        FROM akuz.t_llo_electronic_certificate_application ap
        WHERE ap.lloelectroniccertificateapplicationid = p_id;
        
        INSERT INTO
            akuz.t_rabbit_send_object (rabbitsendobjectid,
                                       queuename,
                                       messageid,
                                       headers,
                                       contentencoding,
                                       type,
                                       userid,
                                       createdatetime,
                                       ownerid,
                                       state,
                                       content)
        SELECT gen_random_uuid(),
               p_queuename,
               gen_random_uuid(),
               NULL,
               'utf-8',
               'LLO_ELECTRONIC_CERTIFICATE_APPLICATION',
               NULL,
               CURRENT_TIMESTAMP,
               p_id,
               0,
               public.converttexttobinaryzip(v_content, '');
    ELSE
        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 akuz.t_llo_electronic_certificate_application ap
        SET status = CASE t.status
                         WHEN 1 THEN 1
                         WHEN 2 THEN 3
                         WHEN 3 THEN 2
                     END,
            certificateStatus = coalesce(t.certStatus, ap.CertificateStatus),
            refusalreason = t.refusalreason
        FROM temp t
        WHERE ap.recipegeneral = t.recipeid;
    
    END IF;

END;
$$

