DROP PROCEDURE IF EXISTS public.sp_getsetpackagetype;

CREATE OR REPLACE PROCEDURE public.sp_getsetpackagetype(
    "Xml" text DEFAULT NULL,
    "IsNew" integer = 1,
    "ID" uuid DEFAULT NULL,
    "QueueName" varchar(100) DEFAULT NULL,
    "dateTime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml             xml          := "Xml"::xml;
    p_isnew           boolean      := "IsNew"::boolean;
    p_id              uuid         := "ID";
    p_queuename       varchar(100) := "QueueName";
    p_datetime        timestamp    := COALESCE("dateTime", CURRENT_TIMESTAMP);
    p_user            uuid         := "User";
    v_result          text;
    v_contentencoding varchar(255) := 'utf-8';
    v_type            varchar(255) := 'PACKAGE_TYPE';
    v_operationtype   varchar(6);
    v_ownerid         uuid;
    c_insert          varchar(6)   := 'Insert';
    c_update          varchar(6)   := 'Update';
    c_delete          varchar(6)   := 'Delete';
    c_ftload          varchar(6)   := 'FTLoad';
BEGIN
    IF (p_xml IS NULL) THEN
        v_ownerid := p_id;
        
        IF (p_id IS NULL) THEN
            v_operationtype := c_ftload;
        ELSIF (p_isnew = TRUE) THEN
            v_operationtype := c_insert;
        ELSIF (p_isnew = FALSE AND EXISTS(SELECT 1 FROM pharm.t_package_type pt WHERE pt.packagetypeid = p_id)) THEN
            v_operationtype := c_update;
        ELSIF (p_isnew = FALSE AND
               NOT EXISTS (SELECT 1 FROM pharm.t_package_type pt WHERE pt.packagetypeid = p_id)) THEN
            v_operationtype := c_delete;
            SELECT XMLELEMENT(
                       NAME "PACKAGE_TYPE",
                       XMLELEMENT(
                           NAME "header",
                           XMLFOREST(
                               v_operationtype AS "TYPE",
                               p_id AS "ID"
                           )
                       )
                   )::text
            INTO v_result;
        END IF;
        
        IF (v_operationtype = c_ftload) THEN
            
            SELECT '<PACKAGE_TYPE><header><TYPE>' || c_ftload || '</TYPE></header><body><PACKAGE_TYPES>' ||
                   COALESCE(STRING_AGG(
                                XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                                ''), '')
                       ||
                   '</PACKAGE_TYPES></body></PACKAGE_TYPE>'
            INTO v_result
            FROM (SELECT XMLELEMENT(
                             NAME "PACKAGE_TYPE",
                             XMLFOREST(
                                 pt.packagetypeid AS "ID",
                                 pt.code AS "Code",
                                 pt.name AS "Name",
                                 pt.shortname AS "ShortName",
                                 pt.regcode AS "RegCode",
                                 pt.fedcode AS "FedCode"
                             )
                         ) AS xml_elem
                  FROM pharm.t_package_type pt
                 ) tt;
        
        ELSIF (v_operationtype <> c_delete) THEN
            SELECT XMLELEMENT(
                       NAME "PACKAGE_TYPE",
                       XMLELEMENT(
                           NAME "header",
                           XMLFOREST(
                               v_operationtype AS "TYPE",
                               p_id AS "ID"
                           )
                       ),
                       XMLELEMENT(
                           NAME "body",
                           XMLFOREST(
                               pt.code AS "Code",
                               pt.name AS "Name",
                               pt.shortname AS "ShortName",
                               pt.regcode AS "RegCode",
                               pt.fedcode AS "FedCode"
                           )
                       )
                   )::text
            INTO v_result
            FROM pharm.t_package_type pt
            WHERE pt.packagetypeid = p_id;
        END IF;
        
        INSERT INTO
            rds.t_rabbit_send_object (rabbitsendobjectid,
                                      queuename,
                                      content,
                                      messageid,
                                      headers,
                                      contentencoding,
                                      type,
                                      userid,
                                      state,
                                      createdatetime,
                                      ownerid)
        SELECT gen_random_uuid(),
               s.servername,
               public.converttexttobinaryzip(v_result, ''),
               NULL,
               NULL,
               v_contentencoding,
               v_type,
               p_user,
               0,
               p_datetime,
               v_ownerid
        FROM rds.t_sync_servers s
        WHERE (p_queuename IS NOT NULL AND s.servername = p_queuename)
           OR (p_queuename IS NULL AND s.servertype IN (0, 1));
    ELSE
        SELECT (XPATH('/PACKAGE_TYPE/header/TYPE/text()', p_xml))[1]::text,
               (XPATH('/PACKAGE_TYPE/header/ID/text()', p_xml))[1]::text::uuid,
               p_xml::text
        INTO v_operationtype, v_ownerid, v_result;
        
        IF (v_operationtype = c_ftload) THEN
            CREATE TEMP TABLE temp_package_types AS
                SELECT (XPATH('/PACKAGE_TYPE/ID/text()', x))[1]::text::uuid  AS id,
                       (XPATH('/PACKAGE_TYPE/Code/text()', x))[1]::integer   AS code,
                       (XPATH('/PACKAGE_TYPE/Name/text()', x))[1]::text      AS name,
                       (XPATH('/PACKAGE_TYPE/ShortName/text()', x))[1]::text AS shortname,
                       (XPATH('/PACKAGE_TYPE/RegCode/text()', x))[1]::text   AS regcode,
                       (XPATH('/PACKAGE_TYPE/FedCode/text()', x))[1]::text   AS fedcode
                FROM UNNEST(XPATH('/PACKAGE_TYPE/body/PACKAGE_TYPES/PACKAGE_TYPE', p_xml)) x;
            
            UPDATE pharm.t_package_type pt
            SET name      = nmf.name,
                code      = nmf.code,
                shortname = nmf.shortname,
                regcode   = nmf.regcode,
                fedcode   = nmf.fedcode
            FROM temp_package_types nmf
            WHERE nmf.id = pt.packagetypeid;
            
            INSERT INTO
                pharm.t_package_type (packagetypeid, name, code, shortname, regcode, fedcode)
            SELECT nmf.id,
                   nmf.name,
                   nmf.code,
                   nmf.shortname,
                   nmf.regcode,
                   nmf.fedcode
            FROM temp_package_types nmf
            WHERE NOT EXISTS (SELECT 1
                              FROM pharm.t_package_type pt
                              WHERE pt.packagetypeid = nmf.id
                             );
        ELSE
            CREATE TEMP TABLE temp_package_type AS
                SELECT (XPATH('/PACKAGE_TYPE/header/ID/text()', p_xml))[1]::text::uuid AS id,
                       (XPATH('/PACKAGE_TYPE/body/Code/text()', p_xml))[1]::integer    AS code,
                       (XPATH('/PACKAGE_TYPE/body/Name/text()', p_xml))[1]::text       AS name,
                       (XPATH('/PACKAGE_TYPE/body/ShortName/text()', p_xml))[1]::text  AS shortname,
                       (XPATH('/PACKAGE_TYPE/body/RegCode/text()', p_xml))[1]::text    AS regcode,
                       (XPATH('/PACKAGE_TYPE/body/FedCode/text()', p_xml))[1]::text    AS fedcode;
            
            IF (v_operationtype = c_delete) THEN
                DELETE
                FROM pharm.t_package_type pt
                WHERE pt.packagetypeid = (SELECT id FROM temp_package_type);
            ELSIF (v_operationtype = c_update) THEN
                UPDATE pharm.t_package_type pt
                SET name      = nmf.name,
                    code      = nmf.code,
                    shortname = nmf.shortname,
                    regcode   = nmf.regcode,
                    fedcode   = nmf.fedcode
                FROM temp_package_type nmf
                WHERE pt.packagetypeid = nmf.id;
            ELSIF (v_operationtype = c_insert) THEN
                INSERT INTO
                    pharm.t_package_type (packagetypeid, name, code, shortname, regcode, fedcode)
                SELECT id,
                       name,
                       code,
                       shortname,
                       regcode,
                       fedcode
                FROM temp_package_type nmf
                WHERE NOT EXISTS (SELECT 1
                                  FROM pharm.t_package_type pt
                                  WHERE pt.packagetypeid = nmf.id
                                 );
            END IF;
            
            INSERT INTO
                rds.t_rabbit_send_object (rabbitsendobjectid,
                                          queuename,
                                          content,
                                          messageid,
                                          headers,
                                          contentencoding,
                                          type,
                                          userid,
                                          state,
                                          createdatetime,
                                          ownerid)
            SELECT gen_random_uuid(),
                   s.servername,
                   public.converttexttobinaryzip(v_result, ''),
                   NULL,
                   NULL,
                   v_contentencoding,
                   v_type,
                   p_user,
                   0,
                   p_datetime,
                   v_ownerid
            FROM rds.t_sync_servers s
            WHERE s.servername <> p_queuename;
        END IF;
    
    END IF;
END;
$$;