DROP PROCEDURE IF EXISTS public.sp_getsetrdsnosology;

CREATE OR REPLACE PROCEDURE public.sp_getsetrdsnosology(
    "Xml" xml DEFAULT NULL,
    "ID" uuid DEFAULT NULL,
    "IsNew" boolean DEFAULT TRUE,
    "QueueName" varchar(100) = '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";
    p_queuename       varchar(100) := "QueueName";
    p_datetime        timestamp    := COALESCE("dateTime", CURRENT_TIMESTAMP);
    p_user            uuid         := "User";
    v_result          text ;
    v_contentencoding varchar(255);
    v_type            varchar(255);
    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]::text     AS type,
               (XPATH('/NOSOLOGY/header/ID/text()', x))[1]::text::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,
            adress             VARCHAR(256),
            cardstatus         VARCHAR(256),
            comment            VARCHAR(1024),
            dateofdeclusion    timestamp,
            dateofinclusion    timestamp,
            executiveauthority VARCHAR(256),
            head               VARCHAR(256),
            omsnumber          VARCHAR(30),
            omsserial          VARCHAR(30),
            uniquenumber       VARCHAR(128),
            mkb                VARCHAR(10),
            diagnosis          VARCHAR(4096),
            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,
            snils              VARCHAR(14),
            birthdaylastname   VARCHAR(60),
            disability         INT,
            therapyresistance  INT,
            deathdate          timestamp,
            okato              text,
            f002smo            VARCHAR(5),
            patient            uuid,
            dateofchange       timestamp
        );
        
        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      VARCHAR(128)
        );
        
        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;
        
        UPDATE temp_nosology nos
        SET patient = p.patientid
        FROM akuz.t_patient p
        WHERE p.snils = nos.snils
          AND p.birthday = nos.birthday
          AND nos.patient IS NULL
          AND nos.snils IS NOT NULL
          AND nos.birthday IS NOT NULL;
        
        UPDATE temp_nosology nos
        SET patient = p.patientid
        FROM akuz.t_patient p
        WHERE LEFT(p.docseries, 8) = nos.docseries
          AND LEFT(p.docnumber, 8) = nos.docnumber
          AND p.birthday = nos.birthday
          AND nos.patient IS NULL
          AND nos.docseries IS NOT NULL
          AND nos.docnumber IS NOT NULL
          AND nos.birthday IS NOT NULL;
        
        UPDATE temp_nosology nos
        SET patient = p.patientid
        FROM akuz.t_patient p
        WHERE p.lastname = nos.lastname
          AND p.firstname = nos.firstname
          AND (p.fathername = nos.fathername OR p.fathername IS NULL AND nos.fathername IS NULL)
          AND p.birthday = nos.birthday
          AND nos.patient IS NULL;
        
        IF (v_operationtype = c_ftload) THEN
            
            DELETE
            FROM akuz.t_patient_benefits pd
            WHERE EXISTS (SELECT 1
                          FROM akuz.t_benefits b
                          WHERE b.benefitsid = pd.benefits
                            AND b.benefitviews = 19006
                         )
              AND pd.createtype = 2;
            
            
            INSERT INTO
                akuz.t_patient_benefits
            (patientbenefitsid,
             patient,
             benefits,
             benefitdocument,
             benefitdocumentnumber,
             begindate,
             enddate,
             mkb,
             active,
             deleted,
             createtype,
             pac_id,
             lpu_inf,
             sost_r,
             reasonregistration,
             reasonderegistration,
             benefitdocumentseries,
             benefitdoctype,
             regdate)
            SELECT gen_random_uuid(),
                   nos.patient,
                   (SELECT b.benefitsid
                    FROM akuz.t_benefits b
                    WHERE b.benefitcode = nb.benefitcode
                      AND b.benefitviews = 19006
                    LIMIT 1
                   ),
                   NULL,
                   NULL,
                   nb.datebegin,
                   nb.enddate,
                   (SELECT m.mkbid
                    FROM akuz.t_mkb m
                    WHERE nb.mkb = m.mcbcode
                    LIMIT 1
                   ),
                   TRUE,
                   FALSE,
                   2,
                   nos.uniquenumber,
                   nos.inclusionlpu,
                   NULL,
                   NULL,
                   NULL,
                   NULL,
                   nos.dateofinclusion
            FROM temp_nosology nos
            RIGHT JOIN temp_nosology_benefits nb ON nb.nosologyid = nos.id
            WHERE nos.patient IS NOT NULL;
        
        ELSE
            
            IF (v_operationtype = c_delete) THEN
                
                DELETE
                FROM akuz.t_patient_benefits pd
                    USING
                        akuz.t_benefits b,
                            temp_nosology nos,
                            temp_nosology_benefits nb
                WHERE b.benefitsid = pd.benefits
                  AND nos.patient = pd.patient
                  AND nb.nosologyid = nos.id
                  AND b.benefitviews = 19006
                  AND pd.createtype = 2;
            
            ELSE
                
                DELETE
                FROM akuz.t_patient_benefits pd
                    USING
                        akuz.t_benefits b,
                            temp_nosology nos,
                            temp_nosology_benefits nb
                WHERE b.benefitsid = pd.benefits
                  AND nos.patient = pd.patient
                  AND nb.nosologyid = nos.id
                  AND b.benefitviews = 19006
                  AND pd.createtype = 2;
                
                INSERT INTO
                    akuz.t_patient_benefits
                (patientbenefitsid,
                 patient,
                 benefits,
                 benefitdocument,
                 benefitdocumentnumber,
                 begindate,
                 enddate,
                 mkb,
                 active,
                 deleted,
                 createtype,
                 pac_id,
                 lpu_inf,
                 sost_r,
                 reasonregistration,
                 reasonderegistration,
                 benefitdocumentseries,
                 benefitdoctype,
                 regdate)
                SELECT gen_random_uuid(),
                       nos.patient,
                       (SELECT b.benefitsid
                        FROM akuz.t_benefits b
                        WHERE b.benefitcode = nb.benefitcode
                          AND b.benefitviews = 19006
                        LIMIT 1
                       ),
                       NULL,
                       NULL,
                       nb.datebegin,
                       nb.enddate,
                       (SELECT m.mkbid FROM akuz.t_mkb m WHERE nb.mkb = m.mcbcode LIMIT 1),
                       TRUE,
                       FALSE,
                       2,
                       nos.uniquenumber,
                       nos.inclusionlpu,
                       NULL,
                       NULL,
                       NULL,
                       NULL,
                       null,
                       nos.dateofinclusion
                FROM temp_nosology nos
                RIGHT JOIN temp_nosology_benefits nb ON nb.nosologyid = nos.id
                WHERE nos.patient IS NOT NULL;
            END IF;
        
        
        END IF;
    END IF;


END;
$$;