DROP PROCEDURE IF EXISTS rds.sp_lloelectroniccertificate;

CREATE OR REPLACE PROCEDURE rds.sp_lloelectroniccertificate(
    "id" uuid,
    "mcod" varchar(6)
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_id   uuid       := id;
    p_mcod varchar(6) := mcod;
    v_xml  text;
BEGIN
    
    SELECT XMLELEMENT
           (
               NAME "ELECTRONIC_CERTIFICATE_APPLICATION",
               XMLELEMENT(NAME "header", XMLFOREST('Update' as "TYPE")),
               XMLELEMENT
               (
                   NAME "body",
                   XMLFOREST
                   (
                       TO_CHAR(ap.applicationdate, 'DD.MM.YYYY') AS "applicationDate",
                       SPLIT_PART(ap.recipeserialnumber, ' ', 1) AS "serial",
                       SPLIT_PART(ap.recipeserialnumber, ' ', 2) AS "number",
                       ap.status AS "status",
                       ap.recipeid AS "recipeID"
                   )
               )
           )
    INTO v_xml
    FROM rds.t_electronic_certificate_application ap
    WHERE ap.electroniccertificateapplicationid = p_id;
    
    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(),
           p_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 = p_mcod
                     AND sl.syncservers = s.syncserversid
                  ));


END;
$$