DROP PROCEDURE IF EXISTS sp_createbsk_direction;

CREATE PROCEDURE sp_createbsk_direction(
    "ID" uuid
, "QueueName" varchar(100)
, "User" uuid DEFAULT NULL::uuid
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    
    DECLARE
        _newdirid          uuid    := gen_random_uuid();
        _newbenid          uuid    := gen_random_uuid();
        _pac_id            int;
        _patient           uuid;
        _datebeg           timestamp;
        _dateend           timestamp;
        _diagnosis         varchar(255);
        _old               boolean := '0';
        _dregistrationdate timestamp;
        _closedate         timestamp;
    BEGIN
        
        SELECT br.patient
        INTO _patient
        FROM akuz.t_bsk_register_records br
        WHERE br.bskregisterrecordsid = "ID";
        
        SELECT d.pac_id
        INTO _pac_id
        FROM akuz.t_bsk_register_records       br
        JOIN akuz.t_patient_benefits_direction d ON d.patient = br.patient
        JOIN akuz.t_bsk_benefits_direction     bb ON bb.patientbenefitsdirectionid = d.patientbenefitsdirectionid
        WHERE d.pac_id IS NOT NULL
          AND br.bskregisterrecordsid = "ID"
        LIMIT 1;
        
        IF EXISTS(SELECT 1
                  FROM akuz.t_bsk_register_records br
                  WHERE br.patient = _patient
                    AND br.bskregisterrecordsid <> "ID"
                 )
        THEN
            _old := '1';
        ELSE
            _old := '0';
        END IF;
        
        IF (_pac_id IS NOT NULL AND _old = '0' AND _closedate IS NULL AND _dregistrationdate IS NULL)
        THEN
            RAISE EXCEPTION 'Ошибка создания направления из терапии! Обратитесь к администратору!';
            RETURN;
        END IF;
        
        INSERT INTO
            akuz.t_patient_benefits_direction
        (date,
         changetype,
         patientbenefitsdirectionid,
         enterdate,
         lastname,
         firstname,
         fathername,
         birthday,
         kod,
         docseries,
         docnumber,
         users,
         sex,
         snils,
         doctype,
         lpumain,
         pac_id,
         patient,
         type_)
        SELECT NOW()::timestamp
             , CASE WHEN br.dregistrationdate IS NOT NULL OR br.closedate IS NOT NULL OR _old = '1' THEN 2 ELSE 1 END
             , _newdirid
             , NOW()::timestamp
             , p.lastname
             , p.firstname
             , p.fathername
             , p.birthday
             , (SELECT ac.number
                FROM akuz.t_ambulance_card ac
                WHERE ac.patient = p.patientid
                  AND ac.cardlpu = br.lpumain
                LIMIT 1
               )
             , p.docseries
             , p.docnumber
             , "User"
             , p.sex
             , p.snils
             , p.doctype
             , br.lpumain
             , _pac_id
             , p.patientid
             , (SELECT e.entityid FROM vclib.t_entities e WHERE e.entname = 'BSK_BENEFITS_DIRECTION' LIMIT 1)
        FROM akuz.t_bsk_register_records br
        JOIN akuz.t_patient              p ON br.patient = p.patientid
        WHERE br.bskregisterrecordsid = "ID";
        
        INSERT INTO
            akuz.t_bsk_benefits_direction(patientbenefitsdirectionid, excludereason, bskregisterrecords)
        SELECT _newdirid, NULL, "ID";
        
        INSERT INTO
            akuz.t_patient_benefits_direction_data
        (patientbenefitsdirectiondataid,
         diagdate,
         document,
         documentdate,
         patientbenefitsdirection,
         benefits,
         doctor,
         mkb,
         benefitviews,
         changetype,
         type_)
        SELECT _newbenid,
               br.datebegin,
               NULL,
               NULL,
               _newdirid,
               br.benefits,
               br.post,
               br.mkb,
               4,
               CASE
                   WHEN br.closedate IS NOT NULL AND br.exclusionreason IS NOT NULL AND br.exclusionreason <> 0 THEN 3
                   WHEN br.dregistrationdate IS NOT NULL AND br.place = 0 THEN 2
                   ELSE 1
               END,
               (SELECT e.entityid FROM vclib.t_entities e WHERE e.entname = 'BSK_BENEFITS_DIRECTION_DATA' LIMIT 1)
        FROM akuz.t_bsk_register_records br
        JOIN akuz.t_patient              p ON br.patient = p.patientid
        WHERE br.bskregisterrecordsid = "ID";
        
        INSERT INTO
            akuz.t_bsk_benefits_direction_data
        (patientbenefitsdirectiondataid,
         medicalservice,
         datebegin,
         dateend,
         exclusionreason,
         estimatedddate,
         dregistrationdate,
         place,
         lpumainto,
         closedate)
        SELECT _newbenid,
               br.medicalservice,
               br.datebegin,
               br.dateend,
               br.exclusionreason,
               br.estimatedddate,
               br.dregistrationdate,
               br.place,
               br.lpumainto,
               br.closedate
        FROM akuz.t_bsk_register_records br
        JOIN akuz.t_patient              p ON br.patient = p.patientid
        WHERE br.bskregisterrecordsid = "ID";
        
        UPDATE akuz.t_bsk_register_complication_link compl
        SET bskbenefitsdirectiondata = _newbenid
        WHERE compl.bskregisterrecords = "ID";
        
        INSERT INTO
            akuz.t_bsk_benefits_direction_medicaments
        (bskbenefitsdirectionmedicamentsid,
         bskbenefitsdirectiondata,
         cmnn,
         datestart,
         enddate,
         medicationforms,
         regiment,
         dose,
         measureunit,
         prepid,
         daylicoeff,
         doseview,
         singledose,
         multiplicity)
        SELECT gen_random_uuid(),
               _newbenid,
               mnn.cmnn,
               mnn.datestart,
               mnn.enddate,
               mnn.medicationforms,
               mnn.regiment,
               mnn.dose,
               mnn.measureunit,
               mnn.prepid,
               mnn.daylicoeff,
               mnn.doseview,
               mnn.singledose,
               mnn.multiplicity
        FROM akuz.t_bsk_register_link_mnn mnn
        JOIN akuz.t_bsk_register_records  br ON br.bskregisterrecordsid = mnn.bskregisterrecords
        WHERE mnn.bskregisterrecords = "ID";
        
        CALL public.sp_bsk_register_direction(NULL, _newdirid, "QueueName");
        
        SELECT INTO
            _datebeg, _dateend, _diagnosis br.datebegin,
                                           COALESCE(br.closedate, br.dateend),
                                           LEFT(br.diagnosis, 255)
        FROM akuz.t_bsk_register_records br
        JOIN akuz.t_patient              p ON br.patient = p.patientid
        WHERE br.bskregisterrecordsid = "ID";
        
        CALL sp_createpulmo_notification(_patient, _datebeg, _dateend, _diagnosis);
    END;
END;
$$;