DROP PROCEDURE IF EXISTS public.sp_getsetmedicamentproducer;

CREATE OR REPLACE PROCEDURE public.sp_getsetmedicamentproducer(
    "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) := 'MEDICAMENT_PRODUCER';
    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';
    v_entid           integer;
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_medicament_producer mp WHERE mp.medicamentproducerid = p_id)) THEN
            v_operationtype := c_update;
        ELSE
            v_operationtype := c_delete;
            v_result := XMLELEMENT(
                NAME "MEDICAMENT_PRODUCER",
                XMLELEMENT(
                    NAME "header",
                    XMLFOREST(
                        v_operationtype AS "TYPE",
                        p_id AS "ID"
                    )
                )
                        )::text;
        END IF;
        
        IF (v_operationtype = c_ftload) THEN
            
            SELECT
                '<MEDICAMENT_PRODUCER><header><TYPE>' || c_ftload || '</TYPE></header><body><MEDICAMENT_PRODUCERS>' ||
                COALESCE(STRING_AGG(
                             XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                             ''), '')
                    ||
                '</MEDICAMENT_PRODUCERS></body></MEDICAMENT_PRODUCER>'
            INTO v_result
            FROM (SELECT XMLELEMENT(
                             NAME "MEDICAMENT_PRODUCER",
                             XMLFOREST(
                                 mp.medicamentproducerid AS "ID",
                                 mp.inn AS "Inn",
                                 mp.name AS "Name",
                                 mp.address AS "Address",
                                 mp.active AS "Active",
                                 c.code AS "Country",
                                 mp.regcode AS "RegCode",
                                 mp.fedcode AS "FedCode",
                                 mp.code AS "Code"
                             )
                         ) AS xml_elem
                  FROM pharm.t_medicament_producer mp
                  LEFT JOIN pharm.t_country        c ON c.countryid = mp.country
                 ) tt;
        
        ELSIF (v_operationtype <> c_delete) THEN
            v_result := (SELECT XMLELEMENT(
                                    NAME "MEDICAMENT_PRODUCER",
                                    XMLELEMENT(
                                        NAME "header",
                                        XMLFOREST(
                                            v_operationtype AS "TYPE",
                                            p_id AS "ID"
                                        )
                                    ),
                                    XMLELEMENT(
                                        NAME "body",
                                        XMLFOREST(
                                            mp.inn AS "Inn",
                                            mp.name AS "Name",
                                            mp.address AS "Address",
                                            mp.active AS "Active",
                                            c.code AS "Country",
                                            mp.regcode AS "RegCode",
                                            mp.fedcode AS "FedCode",
                                            mp.code AS "Code"
                                        )
                                    )
                                )::text
                         FROM pharm.t_medicament_producer mp
                         LEFT JOIN pharm.t_country        c ON c.countryid = mp.country
                         WHERE mp.medicamentproducerid = p_id
                        );
        END IF;
        
        INSERT INTO
            rds.t_rabbit_send_object (rabbitsendobjectid,
                                      queuename,
                                      content,
                                      contentencoding,
                                      type,
                                      userid,
                                      state,
                                      createdatetime,
                                      ownerid)
        SELECT gen_random_uuid(),
               s.servername,
               public.converttexttobinaryzip(v_result, ''),
               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('/MEDICAMENT_PRODUCER/header/TYPE/text()', p_xml))[1]::text,
               (XPATH('/MEDICAMENT_PRODUCER/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_medicament_producers AS
                SELECT (XPATH('/MEDICAMENT_PRODUCER/ID/text()', x))[1]::text::uuid        AS id,
                       (XPATH('/MEDICAMENT_PRODUCER/Inn/text()', x))[1]::text             AS inn,
                       (XPATH('/MEDICAMENT_PRODUCER/Name/text()', x))[1]::text            AS name,
                       (XPATH('/MEDICAMENT_PRODUCER/Address/text()', x))[1]::text         AS address,
                       (XPATH('/MEDICAMENT_PRODUCER/Active/text()', x))[1]::text::boolean AS active,
                       (XPATH('/MEDICAMENT_PRODUCER/Country/text()', x))[1]::text         AS country,
                       (XPATH('/MEDICAMENT_PRODUCER/RegCode/text()', x))[1]::text         AS regcode,
                       (XPATH('/MEDICAMENT_PRODUCER/FedCode/text()', x))[1]::text         AS fedcode,
                       (XPATH('/MEDICAMENT_PRODUCER/Code/text()', x))[1]::text::integer   AS code
                FROM UNNEST(XPATH('/MEDICAMENT_PRODUCER/body/MEDICAMENT_PRODUCERS/MEDICAMENT_PRODUCER', p_xml)) x;
            
            UPDATE pharm.t_medicament_producer mp
            SET active = FALSE
            WHERE NOT EXISTS (SELECT 1
                              FROM temp_medicament_producers nmp
                              WHERE nmp.id = mp.medicamentproducerid
                             );
            
            UPDATE pharm.t_medicament_producer mp
            SET inn     = nmp.inn,
                name    = nmp.name,
                address = nmp.address,
                country = (SELECT c.countryid FROM pharm.t_country c WHERE c.code = nmp.country LIMIT 1),
                active  = nmp.active,
                regcode = nmp.regcode,
                fedcode = nmp.fedcode,
                code    = nmp.code
            FROM temp_medicament_producers nmp
            WHERE nmp.id = mp.medicamentproducerid;
            
            INSERT INTO
                pharm.t_medicament_producer (medicamentproducerid,
                                             name,
                                             inn,
                                             address,
                                             country,
                                             active,
                                             regcode,
                                             fedcode,
                                             code)
            SELECT nmp.id,
                   nmp.name,
                   nmp.inn,
                   nmp.address,
                   (SELECT c.countryid FROM pharm.t_country c WHERE c.code = nmp.country LIMIT 1),
                   nmp.active,
                   nmp.regcode,
                   nmp.fedcode,
                   nmp.code
            FROM temp_medicament_producers nmp
            WHERE NOT EXISTS (SELECT 1
                              FROM pharm.t_medicament_producer mp
                              WHERE mp.medicamentproducerid = nmp.id
                             );
            
            DELETE
            FROM pharm.t_medicament_producer mp
            WHERE mp.active = FALSE
              AND NOT EXISTS (SELECT 1
                              FROM pharm.t_bill_of_goods bg
                              WHERE bg.medicamentproducer = mp.medicamentproducerid
                             );
        
        ELSE
            CREATE TEMP TABLE temp_medicament_producer AS
                SELECT (XPATH('/MEDICAMENT_PRODUCER/header/ID/text()', x))[1]::text::uuid      AS id,
                       (XPATH('/MEDICAMENT_PRODUCER/body/Inn/text()', x))[1]::text             AS inn,
                       (XPATH('/MEDICAMENT_PRODUCER/body/Name/text()', x))[1]::text            AS name,
                       (XPATH('/MEDICAMENT_PRODUCER/body/Address/text()', x))[1]::text         AS address,
                       (XPATH('/MEDICAMENT_PRODUCER/body/Active/text()', x))[1]::text::boolean AS active,
                       (XPATH('/MEDICAMENT_PRODUCER/body/Country/text()', x))[1]::text         AS country,
                       (XPATH('/MEDICAMENT_PRODUCER/body/RegCode/text()', x))[1]::text         AS regcode,
                       (XPATH('/MEDICAMENT_PRODUCER/body/FedCode/text()', x))[1]::text         AS fedcode,
                       (XPATH('/MEDICAMENT_PRODUCER/body/Code/text()', x))[1]::text::integer   AS code
                FROM UNNEST(XPATH('/MEDICAMENT_PRODUCER', p_xml)) AS x;
            
            
            IF (v_operationtype = c_delete) THEN
                UPDATE pharm.t_medicament_producer mp
                SET active = FALSE
                WHERE mp.medicamentproducerid = (SELECT id FROM temp_medicament_producer);
            
            
            ELSE
                IF (v_operationtype = c_update) THEN
                    UPDATE pharm.t_medicament_producer mp
                    SET inn     = nmp.inn,
                        name    = nmp.name,
                        address = nmp.address,
                        country = (SELECT c.countryid FROM pharm.t_country c WHERE c.code = nmp.country LIMIT 1),
                        active  = nmp.active,
                        regcode = nmp.regcode,
                        fedcode = nmp.fedcode,
                        code    = nmp.code
                    FROM temp_medicament_producer nmp
                    WHERE mp.medicamentproducerid = nmp.id;
                
                ELSIF (v_operationtype = c_insert) THEN
                    INSERT INTO
                        pharm.t_medicament_producer (medicamentproducerid,
                                                     name,
                                                     inn,
                                                     address,
                                                     country,
                                                     active,
                                                     regcode,
                                                     fedcode,
                                                     code)
                    SELECT id,
                           name,
                           inn,
                           address,
                           (SELECT c.countryid FROM pharm.t_country c WHERE c.code = country LIMIT 1),
                           active,
                           regcode,
                           fedcode,
                           code
                    FROM temp_medicament_producer nmp
                    WHERE NOT EXISTS (SELECT 1
                                      FROM pharm.t_medicament_producer mp
                                      WHERE mp.medicamentproducerid = nmp.id
                                     );
                END IF;
            END IF;
            
            
            INSERT INTO
                rds.t_rabbit_send_object (rabbitsendobjectid,
                                          queuename,
                                          content,
                                          contentencoding,
                                          type,
                                          userid,
                                          state,
                                          createdatetime,
                                          ownerid)
            SELECT gen_random_uuid(),
                   s.servername,
                   public.converttexttobinaryzip(v_result, ''),
                   v_contentencoding,
                   v_type,
                   p_user,
                   0,
                   p_datetime,
                   v_ownerid
            FROM rds.t_sync_servers s
            WHERE s.servername <> p_queuename;
        END IF;
        
        SELECT entityid
        INTO v_entid
        FROM vclib.t_entities
        WHERE entname = 'MEDICAMENT_PRODUCER'
        LIMIT 1;
        
        IF EXISTS (SELECT 1
                   FROM vclib.t_gen_numbers
                   WHERE entityid = v_entid
                     AND numbername = 'PHARM.MEDICAMENT_PRODUCER'
                  ) THEN
            UPDATE vclib.t_gen_numbers gn
            SET nextnumber = (SELECT MAX(code) FROM pharm.t_medicament_producer)
            WHERE gn.entityid = v_entid
              AND gn.numbername = 'PHARM.MEDICAMENT_PRODUCER';
        ELSE
            INSERT INTO
                vclib.t_gen_numbers (gennumbersid, entityid, numbername, nextnumber)
            VALUES (gen_random_uuid(),
                    v_entid,
                    'PHARM.MEDICAMENT_PRODUCER',
                    COALESCE((SELECT MAX(code) FROM pharm.t_medicament_producer), 1));
        END IF;
    END IF;
END;
$$;