DROP PROCEDURE IF EXISTS public.sp_getsetnosology;

CREATE OR REPLACE PROCEDURE public.sp_getsetnosology(
    "Xml" xml DEFAULT NULL,
    "ID" uuid DEFAULT NULL,
    "IsNew" integer = 1,
    "QueueName" varchar(100) DEFAULT 'RDSSync',
    "dateTime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml           xml          := "Xml";
    p_id            uuid         := "ID";
    p_isnew         boolean      := "IsNew"::boolean;
    p_queuename     varchar(100) := "QueueName";
    p_datetime      timestamp    := COALESCE("dateTime", CURRENT_TIMESTAMP);
    p_user          uuid         := "User";
    v_result        text;
    v_operationtype varchar(255);
    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 NOT NULL) THEN
        SELECT (XPATH('/NOSOLOGY/header/TYPE/text()', x))[1] ::varchar(36)     AS type,
               (XPATH('/NOSOLOGY/header/ID/text()', x))[1] ::varchar(36)::uuid AS id,
               p_xml::text
        INTO v_operationtype,
            v_ownerid,
            v_result
        FROM UNNEST(XPATH('/NOSOLOGY', p_xml)) AS x;
        
        CREATE TEMP TABLE temp_nosology
        (
            id                   uuid,
            dateofchange         TIMESTAMP,
            adress               VARCHAR(256),
            autodateofchange     TIMESTAMP,
            comment              VARCHAR(1024),
            dateofdeclusion      TIMESTAMP,
            dateofinclusion      TIMESTAMP,
            executiveauthority   VARCHAR(256),
            omsnumber            VARCHAR(30),
            omsserial            VARCHAR(30),
            operationcode        VARCHAR(128),
            docseries            VARCHAR(30),
            docnumber            VARCHAR(30),
            birthday             TIMESTAMP,
            firstname            VARCHAR(60),
            lastname             VARCHAR(60),
            fathername           VARCHAR(60),
            docissuedate         TIMESTAMP,
            docissueorg          VARCHAR(256),
            declusionlpu         VARCHAR(6),
            documenttype         INT,
            exemptionby178       INT,
            exemptionby890       INT,
            inclusionlpu         VARCHAR(6),
            region               VARCHAR(13),
            sex                  INT,
            subjectofrfaccounted INT,
            snils                VARCHAR(14),
            birthdaylastname     VARCHAR(60),
            deathdate            TIMESTAMP,
            okato                TEXT,
            f002smo              VARCHAR(5)
        );
        
        CREATE TEMP TABLE temp_nosology_benefits
        (
            nosologyid        uuid,
            mkb               VARCHAR(10),
            cardstatus        VARCHAR(256),
            head              VARCHAR(256),
            diagnosis         VARCHAR(4096),
            disability        BIT,
            therapyresistance BIT,
            benefitcode       VARCHAR(255),
            datebegin         timestamp,
            enddate           timestamp,
            uniquenumber      INT
        );
        
        IF (v_operationtype = c_ftload) THEN
            
            INSERT INTO
                temp_nosology
            (id,
             adress,
             comment,
             dateofchange,
             dateofdeclusion,
             dateofinclusion,
             executiveauthority,
             omsnumber,
             omsserial,
             docseries,
             docnumber,
             birthday,
             firstname,
             lastname,
             fathername,
             docissuedate,
             docissueorg,
             declusionlpu,
             documenttype,
             exemptionby178,
             exemptionby890,
             inclusionlpu,
             region,
             sex,
             snils,
             birthdaylastname,
             deathdate,
             okato,
             f002smo)
            SELECT (XPATH('/NOSOLOGY/ID/text()', x))[1]::text::uuid                  AS id,
                   (XPATH('/NOSOLOGY/ADRESS/text()', x))[1]::text                    AS adress,
                   (XPATH('/NOSOLOGY/COMMENT/text()', x))[1]::text                   AS comment,
                   (XPATH('/NOSOLOGY/CHANGE_DATE/text()', x))[1]::text::timestamp    AS changedate,
                   (XPATH('/NOSOLOGY/DECLUSION_DATE/text()', x))[1]::text::timestamp AS declusiondate,
                   (XPATH('/NOSOLOGY/INCLUSION_DATE/text()', x))[1]::text::timestamp AS inclusiondate,
                   (XPATH('/NOSOLOGY/EXECUTIVE_AUTHORITY/text()', x))[1]::text       AS executive_authority,
                   (XPATH('/NOSOLOGY/OMS_NUMBER/text()', x))[1]::text                AS oms_number,
                   (XPATH('/NOSOLOGY/OMS_SERIAL/text()', x))[1]::text                AS oms_serial,
                   (XPATH('/NOSOLOGY/DOC_SERIES/text()', x))[1]::text                AS doc_series,
                   (XPATH('/NOSOLOGY/DOC_NUMBER/text()', x))[1]::text                AS doc_number,
                   (XPATH('/NOSOLOGY/BIRTHDAY/text()', x))[1]::text::timestamp       AS birthday,
                   (XPATH('/NOSOLOGY/FIRST_NAME/text()', x))[1]::text                AS first_name,
                   (XPATH('/NOSOLOGY/LAST_NAME/text()', x))[1]::text                 AS last_name,
                   (XPATH('/NOSOLOGY/FATHER_NAME/text()', x))[1]::text               AS father_name,
                   (XPATH('/NOSOLOGY/DOC_ISSUE_DATE/text()', x))[1]::text::timestamp AS doc_issue_date,
                   (XPATH('/NOSOLOGY/DOC_ISSUE_ORG/text()', x))[1]::text             AS doc_issue_org,
                   (XPATH('/NOSOLOGY/DECLUSION_LPU/text()', x))[1]::text             AS declusion_lpu,
                   (XPATH('/NOSOLOGY/DOC_TYPE/text()', x))[1]::text::int             AS doc_type,
                   (XPATH('/NOSOLOGY/EX178/text()', x))[1]::text::int                AS ex178,
                   (XPATH('/NOSOLOGY/EX890/text()', x))[1]::text::int                AS ex890,
                   (XPATH('/NOSOLOGY/INCLUSION_LPU/text()', x))[1]::text             AS inclusion_lpu,
                   (XPATH('/NOSOLOGY/REGION/text()', x))[1]::text                    AS region,
                   (XPATH('/NOSOLOGY/SEX/text()', x))[1]::text::int                  AS sex,
                   (XPATH('/NOSOLOGY/SNILS/text()', x))[1]::text                     AS snils,
                   (XPATH('/NOSOLOGY/BIRTHDAY_LAST_NAME/text()', x))[1]::text        AS birthday_last_name,
                   (XPATH('/NOSOLOGY/DEATH_DATE/text()', x))[1]::text::timestamp     AS death_date,
                   (XPATH('/NOSOLOGY/OKATO/text()', x))[1]::text                     AS okato,
                   (XPATH('/NOSOLOGY/SMO/text()', x))[1]::text                       AS f002smo
            FROM UNNEST(XPATH('/NOSOLOGY/body/NOSOLOGYS/NOSOLOGY', p_xml)) AS x;
            
            INSERT INTO
                temp_nosology_benefits
            (nosologyid,
             cardstatus,
             therapyresistance,
             head,
             mkb,
             diagnosis,
             uniquenumber,
             disability,
             benefitcode,
             datebegin,
             enddate)
            SELECT (XPATH('/NOSOLOGY_BENEFIT/NOSOLOGYID/text()', x))[1]::text::uuid        AS nosologyid,
                   (XPATH('/NOSOLOGY_BENEFIT/CARD_STATUS/text()', x))[1]::text             AS card_status,
                   (XPATH('/NOSOLOGY_BENEFIT/THERAPY_RESISTANCE/text()', x))[1]::text::int AS therapy_resistance,
                   (XPATH('/NOSOLOGY_BENEFIT/HEAD/text()', x))[1]::text                    AS head,
                   (XPATH('/NOSOLOGY_BENEFIT/MKB/text()', x))[1]::text                     AS mkb,
                   (XPATH('/NOSOLOGY_BENEFIT/DIAGNOSIS/text()', x))[1]::text               AS diagnosis,
                   (XPATH('/NOSOLOGY_BENEFIT/UNIQUE_NUMBER/text()', x))[1]::text           AS unique_number,
                   (XPATH('/NOSOLOGY_BENEFIT/DIASBILITY/text()', x))[1]::text::int         AS disability,
                   (XPATH('/NOSOLOGY_BENEFIT/BENEFIT_CODE/text()', x))[1]::text            AS benefit_code,
                   (XPATH('/NOSOLOGY_BENEFIT/DATE_BEGIN/text()', x))[1]::text::timestamp   AS date_begin,
                   (XPATH('/NOSOLOGY_BENEFIT/END_DATE/text()', x))[1]::text::timestamp     AS end_date
            FROM UNNEST(XPATH('/NOSOLOGY/body/NOSOLOGYS/NOSOLOGY/NOSOLOGY_BENEFITS/NOSOLOGY_BENEFIT', p_xml)) AS x;
        
        ELSE
            
            INSERT INTO
                temp_nosology
            (id,
             adress,
             comment,
             dateofchange,
             dateofdeclusion,
             dateofinclusion,
             executiveauthority,
             omsnumber,
             omsserial,
             docseries,
             docnumber,
             birthday,
             firstname,
             lastname,
             fathername,
             docissuedate,
             docissueorg,
             declusionlpu,
             documenttype,
             exemptionby178,
             exemptionby890,
             inclusionlpu,
             region,
             sex,
             snils,
             birthdaylastname,
             deathdate,
             okato,
             f002smo)
            SELECT (XPATH('/NOSOLOGY/body/ID/text()', x))[1]::text::uuid                  AS id,
                   (XPATH('/NOSOLOGY/body/ADRESS/text()', x))[1]::text                    AS adress,
                   (XPATH('/NOSOLOGY/body/COMMENT/text()', x))[1]::text                   AS comment,
                   (XPATH('/NOSOLOGY/body/CHANGE_DATE/text()', x))[1]::text::timestamp    AS changedate,
                   (XPATH('/NOSOLOGY/body/DECLUSION_DATE/text()', x))[1]::text::timestamp AS declusiondate,
                   (XPATH('/NOSOLOGY/body/INCLUSION_DATE/text()', x))[1]::text::timestamp AS inclusiondate,
                   (XPATH('/NOSOLOGY/body/EXECUTIVE_AUTHORITY/text()', x))[1]::text       AS executive_authority,
                   (XPATH('/NOSOLOGY/body/OMS_NUMBER/text()', x))[1]::text                AS oms_number,
                   (XPATH('/NOSOLOGY/body/OMS_SERIAL/text()', x))[1]::text                AS oms_serial,
                   (XPATH('/NOSOLOGY/body/DOC_SERIES/text()', x))[1]::text                AS doc_series,
                   (XPATH('/NOSOLOGY/body/DOC_NUMBER/text()', x))[1]::text                AS doc_number,
                   (XPATH('/NOSOLOGY/body/BIRTHDAY/text()', x))[1]::text::timestamp       AS birthday,
                   (XPATH('/NOSOLOGY/body/FIRST_NAME/text()', x))[1]::text                AS first_name,
                   (XPATH('/NOSOLOGY/body/LAST_NAME/text()', x))[1]::text                 AS last_name,
                   (XPATH('/NOSOLOGY/body/FATHER_NAME/text()', x))[1]::text               AS father_name,
                   (XPATH('/NOSOLOGY/body/DOC_ISSUE_DATE/text()', x))[1]::text::timestamp AS doc_issue_date,
                   (XPATH('/NOSOLOGY/body/DOC_ISSUE_ORG/text()', x))[1]::text             AS doc_issue_org,
                   (XPATH('/NOSOLOGY/body/DECLUSION_LPU/text()', x))[1]::text             AS declusion_lpu,
                   (XPATH('/NOSOLOGY/body/DOC_TYPE/text()', x))[1]::text::int             AS doc_type,
                   (XPATH('/NOSOLOGY/body/EX178/text()', x))[1]::text::int                AS ex178,
                   (XPATH('/NOSOLOGY/body/EX890/text()', x))[1]::text::int                AS ex890,
                   (XPATH('/NOSOLOGY/body/INCLUSION_LPU/text()', x))[1]::text             AS inclusion_lpu,
                   (XPATH('/NOSOLOGY/body/REGION/text()', x))[1]::text                    AS region,
                   (XPATH('/NOSOLOGY/body/SEX/text()', x))[1]::text::int                  AS sex,
                   (XPATH('/NOSOLOGY/body/SNILS/text()', x))[1]::text                     AS snils,
                   (XPATH('/NOSOLOGY/body/BIRTHDAY_LAST_NAME/text()', x))[1]::text        AS birthday_last_name,
                   (XPATH('/NOSOLOGY/body/DEATH_DATE/text()', x))[1]::text::timestamp     AS death_date,
                   (XPATH('/NOSOLOGY/body/OKATO/text()', x))[1]::text                     AS okato,
                   (XPATH('/NOSOLOGY/body/SMO/text()', x))[1]::text                       AS f002smo
            FROM UNNEST(XPATH('/NOSOLOGY', p_xml)) AS x;
            
            
            INSERT INTO
                temp_nosology_benefits
            (nosologyid,
             cardstatus,
             therapyresistance,
             head,
             mkb,
             diagnosis,
             uniquenumber,
             disability,
             benefitcode,
             datebegin,
             enddate)
            SELECT (XPATH('/NOSOLOGY_BENEFIT/NOSOLOGYID/text()', x))[1]::text::uuid        AS nosologyid,
                   (XPATH('/NOSOLOGY_BENEFIT/CARD_STATUS/text()', x))[1]::text             AS card_status,
                   (XPATH('/NOSOLOGY_BENEFIT/THERAPY_RESISTANCE/text()', x))[1]::text::int AS therapy_resistance,
                   (XPATH('/NOSOLOGY_BENEFIT/HEAD/text()', x))[1]::text                    AS head,
                   (XPATH('/NOSOLOGY_BENEFIT/MKB/text()', x))[1]::text                     AS mkb,
                   (XPATH('/NOSOLOGY_BENEFIT/DIAGNOSIS/text()', x))[1]::text               AS diagnosis,
                   (XPATH('/NOSOLOGY_BENEFIT/UNIQUE_NUMBER/text()', x))[1]::text           AS unique_number,
                   (XPATH('/NOSOLOGY_BENEFIT/DIASBILITY/text()', x))[1]::text::int         AS disability,
                   (XPATH('/NOSOLOGY_BENEFIT/BENEFIT_CODE/text()', x))[1]::text            AS benefit_code,
                   (XPATH('/NOSOLOGY_BENEFIT/DATE_BEGIN/text()', x))[1]::text::timestamp   AS date_begin,
                   (XPATH('/NOSOLOGY_BENEFIT/END_DATE/text()', x))[1]::text::timestamp     AS end_date
            FROM UNNEST(XPATH('/NOSOLOGY/body/NOSOLOGY_BENEFITS/NOSOLOGY_BENEFIT', p_xml)) AS x;
        
        END IF;
        
        IF (v_operationtype = c_ftload) THEN
            
            DELETE FROM aps.t_nosology;
            
            
            INSERT INTO
                aps.t_nosology(nosologyid,
                               adress,
                               autodateofchange,
                               cardstatus,
                               comment,
                               dateofchange,
                               dateofdeclusion,
                               dateofinclusion,
                               executiveauthority,
                               head,
                               omsnumber,
                               omsserial,
                               operationcode,
                               uniquenumber,
                               mkb,
                               diagnosis,
                               docseries,
                               docnumber,
                               birthday,
                               firstname,
                               lastname,
                               fathername,
                               docissuedate,
                               docissueorg,
                               declusionlpu,
                               documenttype,
                               exemptionby178,
                               exemptionby890,
                               inclusionlpu,
                               region,
                               sex,
                               subjectofrfaccounted,
                               snils,
                               benefits,
                               birthdaylastname,
                               disability,
                               therapyresistance,
                               deathdate,
                               okato,
                               f002smo)
            SELECT gen_random_uuid(),
                   nos.adress,
                   nos.autodateofchange,
                   nb.cardstatus,
                   nos.comment,
                   nos.dateofchange,
                   nos.dateofdeclusion,
                   nos.dateofinclusion,
                   nos.executiveauthority,
                   nb.head,
                   nos.omsnumber,
                   nos.omsserial,
                   nos.operationcode,
                   nb.uniquenumber,
                   nb.mkb,
                   nb.diagnosis,
                   nos.docseries,
                   nos.docnumber,
                   nos.birthday,
                   nos.firstname,
                   nos.lastname,
                   nos.fathername,
                   nos.docissuedate,
                   nos.docissueorg,
                   nos.declusionlpu,
                   edoc.enumid,
                   nos.exemptionby178,
                   nos.exemptionby890,
                   nos.inclusionlpu,
                   (SELECT r.regionid FROM aps.t_region r WHERE r.code = nos.region LIMIT 1),
                   esex.enumid,
                   nos.subjectofrfaccounted,
                   nos.snils,
                   (SELECT b.benefitsid
                    FROM aps.t_benefits b
                    WHERE b.benefitcode = nb.benefitcode
                      AND b.benefitviews = 19006
                    LIMIT 1
                   ),
                   nos.birthdaylastname,
                   COALESCE(nb.disability, 0),
                   nb.therapyresistance,
                   nos.deathdate,
                   nos.okato,
                   nos.f002smo
            FROM temp_nosology nos
            LEFT JOIN vclib.t_enums edoc ON edoc.enumtypeid = 105 AND edoc.code = nos.documenttype
            LEFT JOIN vclib.t_enums esex ON esex.enumtypeid = 103 AND esex.code = nos.sex
            RIGHT JOIN temp_nosology_benefits nb ON nb.nosologyid = nos.id;
        
        
        ELSE
            
            IF (v_operationtype = c_delete) THEN
                
                DELETE
                FROM aps.t_nosology n
                WHERE EXISTS (SELECT 1 FROM temp_nosology nos WHERE nos.snils = n.snils);
            
            ELSE
                
                DELETE
                FROM aps.t_nosology n
                WHERE EXISTS (SELECT 1 FROM temp_nosology nos WHERE nos.snils = n.snils);
                
                INSERT INTO
                    aps.t_nosology (nosologyid,
                                    adress,
                                    autodateofchange,
                                    cardstatus,
                                    comment,
                                    dateofchange,
                                    dateofdeclusion,
                                    dateofinclusion,
                                    executiveauthority,
                                    head,
                                    omsnumber,
                                    omsserial,
                                    operationcode,
                                    uniquenumber,
                                    mkb,
                                    diagnosis,
                                    docseries,
                                    docnumber,
                                    birthday,
                                    firstname,
                                    lastname,
                                    fathername,
                                    docissuedate,
                                    docissueorg,
                                    declusionlpu,
                                    documenttype,
                                    exemptionby178,
                                    exemptionby890,
                                    inclusionlpu,
                                    region,
                                    sex,
                                    subjectofrfaccounted,
                                    snils,
                                    benefits,
                                    birthdaylastname,
                                    disability,
                                    therapyresistance,
                                    deathdate,
                                    okato,
                                    f002smo)
                SELECT gen_random_uuid(),
                       nos.adress,
                       nos.autodateofchange,
                       nb.cardstatus,
                       nos.comment,
                       nos.dateofchange,
                       nos.dateofdeclusion,
                       nos.dateofinclusion,
                       nos.executiveauthority,
                       nb.head,
                       nos.omsnumber,
                       nos.omsserial,
                       nos.operationcode,
                       nb.uniquenumber,
                       nb.mkb,
                       nb.diagnosis,
                       nos.docseries,
                       nos.docnumber,
                       nos.birthday,
                       nos.firstname,
                       nos.lastname,
                       nos.fathername,
                       nos.docissuedate,
                       nos.docissueorg,
                       nos.declusionlpu,
                       edoc.enumid,
                       nos.exemptionby178,
                       nos.exemptionby890,
                       nos.inclusionlpu,
                       (SELECT r.regionid FROM aps.t_region r WHERE r.code = nos.region LIMIT 1),
                       esex.enumid,
                       nos.subjectofrfaccounted,
                       nos.snils,
                       (SELECT b.benefitsid
                        FROM aps.t_benefits b
                        WHERE b.benefitcode = nb.benefitcode
                          AND b.benefitviews = 19006
                        LIMIT 1
                       ),
                       nos.birthdaylastname,
                       COALESCE(nb.disability, 0),
                       nb.therapyresistance,
                       nos.deathdate,
                       nos.okato,
                       nos.f002smo
                FROM temp_nosology nos
                LEFT JOIN vclib.t_enums edoc ON edoc.enumtypeid = 105 AND edoc.code = nos.documenttype
                LEFT JOIN vclib.t_enums esex ON esex.enumtypeid = 103 AND esex.code = nos.sex
                RIGHT JOIN temp_nosology_benefits nb ON nb.nosologyid = nos.id
                WHERE NOT EXISTS
                          (SELECT *
                           FROM aps.t_nosology n
                           WHERE n.snils = nos.snils
                          );
            
            END IF;
        
        END IF;
    
    END IF;
END;
$$