DROP PROCEDURE IF EXISTS rds.sp_lloelectroniccertificate;

CREATE OR REPLACE PROCEDURE rds.sp_lloelectroniccertificate(
    "id" uuid,
    "mcod" varchar(6),
    "applicationdate" TIMESTAMP,
    "recipeserialnumber" VARCHAR(50),
    "status" int,
    "refusalReason" VARCHAR(300),
    "recipeid" uuid,
    "certstatus" int
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    v_xml  text;
BEGIN
    
    SELECT XMLELEMENT
           (
               NAME "ELECTRONIC_CERTIFICATE_APPLICATION",
               XMLELEMENT(NAME "header", XMLFOREST('Update' as "TYPE")),
               XMLELEMENT
               (
                   NAME "body",
                   XMLFOREST
                   (
                       TO_CHAR("applicationdate", 'DD.MM.YYYY') AS "applicationDate",
                       SPLIT_PART("recipeserialnumber", ' ', 1) AS "serial",
                       SPLIT_PART("recipeserialnumber", ' ', 2) AS "number",
                       "status"                                 AS "status",
                       "refusalReason"                          AS "refusalReason",
                       "recipeid"                               AS "recipeID",
                       "certstatus"                             AS "certStatus"
                   )
               )
           )
    INTO v_xml;
    
    INSERT INTO
        rds.t_rabbit_send_object (rabbitsendobjectid,
                                  queuename,
                                  messageid,
                                  headers,
                                  contentencoding,
                                  type,
                                  userid,
                                  createdatetime,
                                  ownerid,
                                  routingkey,
                                  state,
                                  correlationid,
                                  content)
    SELECT gen_random_uuid(),
           s.servername,
           NULL,
           NULL,
           'utf-8',
           'ELECTRONIC_CERTIFICATE_APPLICATION',
           NULL,
           NOW(),
           "id",
           NULL,
           0,
           NULL,
           public.converttexttobinaryzip(v_xml, '')
    FROM rds.t_sync_servers s
    WHERE s.servertype = 1
       OR (EXISTS (SELECT *
                   FROM rds.t_servers_link_lpu sll
                   JOIN rds.t_servers_link sl ON sl.serverslinkid = sll.serverslinkid
                   WHERE sll.lpumain = "mcod"
                     AND sl.syncservers = s.syncserversid
                  ));


END;
$$