DROP PROCEDURE IF EXISTS dbo.sp_pall_register_direction;

CREATE OR REPLACE PROCEDURE dbo.sp_pall_register_direction(
    xml text DEFAULT NULL,
    id uuid DEFAULT NULL,
    queuename varchar(100) DEFAULT 'rds.registry',
    datetime timestamp DEFAULT NULL,
    "user" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml             xml          := xml::xml;
    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) := 'PALLIATIVE_DIRECTION';
    v_ownerid         uuid         := p_id;

BEGIN
    
    IF (p_xml IS NULL) THEN
        
        SELECT XMLELEMENT
               (
                   NAME "PALLIATIVE_DIRECTION",
                   XMLFOREST
                   (
                       XMLELEMENT(NAME "ID", p_id),
                       XMLELEMENT(NAME "LPU", lm.lpucode),
                       XMLELEMENT(NAME "EnterDate", TO_CHAR(pbd.enterdate, 'YYYY.MM.DD')),
                       XMLELEMENT(NAME "LastName", pbd.lastname),
                       XMLELEMENT(NAME "FirstName", pbd.firstname),
                       XMLELEMENT(NAME "FatherName", pbd.fathername),
                       XMLELEMENT(NAME "Birthday", TO_CHAR(pbd.birthday, 'YYYY.MM.DD')),
                       XMLELEMENT(NAME "Date", TO_CHAR(pbd.date, 'YYYY.MM.DD')),
                       XMLELEMENT(NAME "Sex", e.code),
                       XMLELEMENT(NAME "Kod", pbd.kod),
                       XMLELEMENT(NAME "DocSeries", pbd.docseries),
                       XMLELEMENT(NAME "DocNumber", pbd.docnumber),
                       XMLELEMENT(NAME "DocType", edt.code),
                       XMLELEMENT(NAME "SNILS", pbd.changetype),
                       XMLELEMENT(NAME "ChangeType", pbd.changetype),
                       XMLELEMENT(NAME "ExcludeReason", rbd.excludereason),
                       XMLELEMENT(NAME "BirthSurname", pbd.birthsurname),
                       XMLELEMENT(NAME "DocIssueDate", TO_CHAR(pbd.docissuedate, 'YYYY.MM.DD')),
                       XMLELEMENT(NAME "DocIssueOrg", pbd.docissueorg),
                       XMLELEMENT(NAME "Enp", pbd.enp),
                       XMLELEMENT(NAME "RealFiasAddress", pbd.realfiasaddress),
                       XMLELEMENT(NAME "RealFiasHome", pbd.realfiashome),
                       XMLELEMENT(NAME "RealCity", pbd.realcity),
                       XMLELEMENT(NAME "RealCityPrefix", pbd.realcityprefix),
                       XMLELEMENT(NAME "RealHouse", pbd.realhouse),
                       XMLELEMENT(NAME "RealBuild", pbd.realbuild),
                       XMLELEMENT(NAME "RealStruc", pbd.realstruc),
                       XMLELEMENT(NAME "RealRoom", pbd.realroom),
                       XMLELEMENT(NAME "RealLiveStartDate", TO_CHAR(pbd.reallivestartdate, 'YYYY.MM.DD')),
                       XMLELEMENT(NAME "RealLiveEndDate", TO_CHAR(pbd.realliveenddate, 'YYYY.MM.DD')),
                       XMLELEMENT(NAME "LegalFiasAddress", pbd.legalfiasaddress),
                       XMLELEMENT(NAME "LegalFiasHome", pbd.legalfiashome),
                       XMLELEMENT(NAME "LegalCity", pbd.legalcity),
                       XMLELEMENT(NAME "LegalCityPrefix", pbd.legalcityprefix),
                       XMLELEMENT(NAME "LegalStreet", pbd.legalstreet),
                       XMLELEMENT(NAME "LegalStreetPrefix", pbd.legalstreetprefix),
                       XMLELEMENT(NAME "LegalHouse", pbd.legalhouse),
                       XMLELEMENT(NAME "LegalBuild", pbd.legalbuild),
                       XMLELEMENT(NAME "LegalStruc", pbd.legalstruc),
                       XMLELEMENT(NAME "LegalRoom", pbd.legalroom),
                       XMLELEMENT(NAME "LegalLiveStartDate", TO_CHAR(pbd.legallivestartdate, 'YYYY.MM.DD')),
                       XMLELEMENT(NAME "LegalLiveEndDate", TO_CHAR(pbd.legalliveenddate, 'YYYY.MM.DD')),
                       XMLELEMENT(NAME "CitizenShip", cs.code),
                       COALESCE((SELECT XMLAGG(
                                            XMLELEMENT
                                            (
                                                NAME "PALLIATIVE_DIRECTION_BENEFIT",
                                                XMLFOREST
                                                (
                                                    XMLELEMENT(NAME "DirectionID", rbd.patientbenefitsdirectionid),
                                                    XMLELEMENT(NAME "DiagDate", TO_CHAR(pbdd.diagdate, 'YYYY.MM.DD')),
                                                    XMLELEMENT(NAME "Document", pbdd.document),
                                                    XMLELEMENT(NAME "DocumentDate",
                                                               TO_CHAR(pbdd.documentdate, 'YYYY.MM.DD')),
                                                    XMLELEMENT(NAME "BenefitCode", b.benefitcode),
                                                    XMLELEMENT(NAME "BenefitViews", ebb.code),
                                                    XMLELEMENT(NAME "PostCode", post.code),
                                                    XMLELEMENT(NAME "ChangeType", pbdd.changetype)
                                                )
                                            )
                                        )
                                 FROM akuz.t_patient_benefits_direction_data      rbdd
                                 JOIN      akuz.t_patient_benefits_direction_data pbdd
                                               ON pbdd.patientbenefitsdirectiondataid =
                                                  rbdd.patientbenefitsdirectiondataid
                                 JOIN      akuz.t_benefits                        b ON b.benefitsid = pbdd.benefits
                                 JOIN      vclib.t_enums                          ebb ON ebb.enumid = b.benefitviews
                                 LEFT JOIN akuz.t_post                            post ON post.resourceid = pbdd.doctor
                                 WHERE pbdd.patientbenefitsdirection = rbd.patientbenefitsdirectionid
                                ), ''
                       ) AS "PALLIATIVE_DIRECTION_BENEFITS"
                   ))::TEXT
        INTO v_result
        FROM akuz.t_palliative_benefits_direction   rbd
        JOIN      akuz.t_patient_benefits_direction pbd
                      ON pbd.patientbenefitsdirectionid = rbd.patientbenefitsdirectionid
        JOIN      vclib.t_enums                     e ON e.enumid = pbd.sex
        JOIN      akuz.t_lpu_main                   lm ON lm.lpuid = pbd.lpumain
        JOIN      akuz.t_lpu_settings               ls ON ls.lpumain = lm.lpuid
        LEFT JOIN akuz.t_policy                     p ON p.policyid = fn_getomspolicy(pbd.patient, p_datetime)
        JOIN      vclib.t_enums                     edt ON edt.enumid = pbd.doctype
        LEFT JOIN ehr.t_ehr_rus_o00015              cs ON cs.ehrrusid = pbd.citizenship
        WHERE pbd.patientbenefitsdirectionid = p_id;
        
        
        INSERT INTO
            akuz.t_rabbit_send_object
        (rabbitsendobjectid,
         queuename,
         content,
         messageid,
         headers,
         contentencoding,
         type,
         userid,
         state,
         createdatetime,
         ownerid)
        SELECT gen_random_uuid(),
               p_queuename,
               public.converttexttobinaryzip(v_result, ''),
               NULL,
               NULL,
               v_contentencoding,
               v_type,
               p_user,
               0,
               p_datetime,
               v_ownerid;
    
    ELSE
        CREATE TEMP TABLE temp_rr_direction AS
            SELECT (XPATH('/PALLIATIVE_REGISTER/ID/text()', x))[1]::text::uuid               AS id,
                   (XPATH('/PALLIATIVE_REGISTER/RegisterID/text()', x))[1]::varchar(10)::int AS registerid
            FROM UNNEST(XPATH('//PALLIATIVE_REGISTER', p_xml)) AS x;
        
        IF NOT EXISTS (SELECT 1
                       FROM akuz.t_patient_benefits_direction pbd
                       JOIN temp_rr_direction                 rd ON rd.id = pbd.patientbenefitsdirectionid
                      ) THEN
            
            RAISE EXCEPTION 'Не найдены данные по направлению';
            RETURN;
        END IF;
        
        UPDATE akuz.t_patient_benefits_direction pbd
        SET pac_id = rd.registerid
        FROM temp_rr_direction rd
        WHERE rd.id = pbd.patientbenefitsdirectionid;
        
        INSERT INTO
            akuz.t_patient_benefits
        (patientbenefitsid,
         patient,
         benefits,
         benefitdocumentnumber,
         begindate,
         mkb,
         active,
         deleted,
         createtype,
         lpu_inf,
         regdate,
         pac_id,
         enddate)
        SELECT gen_random_uuid(),
               pbd.patient,
               pbdd.benefits,
               pbdd.document,
               pbd.date,
               pbdd.mkb,
               TRUE,
               FALSE,
               2,
               lm.lpucode,
               p_datetime,
               pbd.pac_id,
               CASE
                   WHEN (b.duration IS NOT NULL AND b.periodofact IS NOT NULL AND b.closetype IS NOT NULL)
                       THEN (CASE
                                 WHEN b.closetype = 0 AND b.periodofact = 0 THEN pbd.birthday + (b.duration || ' days')::interval
                                 WHEN b.closetype = 0 AND b.periodofact = 1
                                     THEN pbd.birthday + (b.duration || ' months')::interval
                                 WHEN b.closetype = 0 AND b.periodofact = 2 THEN pbd.birthday + (b.duration || ' years')::interval
                                 WHEN b.closetype = 1 AND b.periodofact = 0 THEN pbd.date + (b.duration || ' days')::interval
                                 WHEN b.closetype = 1 AND b.periodofact = 1 THEN pbd.date + (b.duration || ' months')::interval
                                 WHEN b.closetype = 1 AND b.periodofact = 2
                                     THEN pbd.date + (b.duration || ' years')::interval
                             END)
                   ELSE NULL
               END
        FROM akuz.t_patient_benefits_direction_data pbdd
        JOIN akuz.t_patient_benefits_direction      pbd
                 ON pbd.patientbenefitsdirectionid = pbdd.patientbenefitsdirection
        JOIN temp_rr_direction                      rd ON rd.id = pbd.patientbenefitsdirectionid
        JOIN akuz.t_lpu_main                        lm ON lm.lpuid = pbd.lpumain
        JOIN akuz.t_lpu_settings                    ls ON ls.lpumain = lm.lpuid
        JOIN akuz.t_benefits                        b ON b.benefitsid = pbdd.benefits
        WHERE pbdd.changetype = 1
          AND NOT EXISTS (SELECT 1
                          FROM akuz.t_patient_benefits pb
                          WHERE pb.patient = pbd.patient
                            AND pb.benefits = pbdd.benefits
                            AND (pb.enddate IS NULL OR pb.enddate > CURRENT_TIMESTAMP)
                         );
        
        UPDATE akuz.t_patient_benefits pb
        SET enddate = pbd.date,
            active  = FALSE,
            deleted = TRUE
        FROM akuz.t_patient_benefits_direction_data pbdd
        LEFT JOIN akuz.t_mkb                        mkb ON mkb.mcbcode = pbdd.mkb
        JOIN      akuz.t_patient_benefits_direction pbd
                      ON pbd.patientbenefitsdirectionid = pbdd.patientbenefitsdirection
        JOIN      temp_rr_direction                 rd ON rd.id = pbd.patientbenefitsdirectionid
        WHERE pb.patient = pbd.patient
          AND pb.benefits = pbdd.benefits
          AND (pb.enddate IS NULL OR pb.enddate >= CURRENT_TIMESTAMP);
        
        UPDATE akuz.t_patient_benefits pb
        SET enddate = pbd.date,
            active  = FALSE,
            deleted = TRUE
        FROM akuz.t_patient_benefits_direction pbd
        JOIN temp_rr_direction                 rd ON rd.id = pbd.patientbenefitsdirectionid
        JOIN akuz.t_benefits                   b ON b.benefitsid = pb.benefits
        JOIN akuz.t_lpu_main                   lp ON lp.lpuid = pbd.lpumain
        WHERE pb.patient = pbd.patient
          AND pbd.changetype = 3
          AND (b.private = FALSE OR (b.private = TRUE AND (lp.lpucode = pb.lpu_inf OR lp.tfomscode = pb.lpu_inf)))
          AND (pb.enddate IS NULL OR pb.enddate >= CURRENT_TIMESTAMP)
          AND b.benefitviews = 19008;
    
    
    END IF;
END;
$$
