DROP PROCEDURE IF EXISTS sp_createdrugrequestfrompatientdemand;

CREATE PROCEDURE sp_createdrugrequestfrompatientdemand(
    "ID" uuid DEFAULT NULL::uuid
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    DECLARE
        v_type      integer;
        v_datebegin timestamp;
        v_dateend   timestamp;
        v_lpu       uuid;
        v_branch    uuid;
        p_id        uuid := "ID";
    BEGIN
        IF (EXISTS(SELECT * FROM akuz.t_base_drug_request b WHERE b.drugrequestid = p_id AND b.status <> 0))
        THEN
            RAISE EXCEPTION 'Для заполнения заявки она должна быть в статусе - Новый!';
            RETURN;
        END IF;
        
        SELECT dr.requesttype,
               bdr.beginperiod,
               bdr.endperiod,
               bdr.lpumain,
               dr.branch
        INTO
            v_type,
            v_datebegin,
            v_dateend,
            v_lpu,
            v_branch
        FROM akuz.t_drug_request dr
        JOIN akuz.t_base_drug_request bdr ON bdr.drugrequestid = dr.drugrequestid
        WHERE dr.drugrequestid = p_id
        LIMIT 1;
        
        UPDATE akuz.t_doctor_patient_drug_demand_items dpddi
        SET patientdrugdemand = NULL
        FROM akuz.t_patient_drug_demand pdd
        JOIN akuz.t_pharmacy_drug_demand phdd ON phdd.pharmacydrugdemandid = pdd.pharmacydrugdemand
        JOIN akuz.t_drug_demand dd ON dd.drugdemandid = phdd.drugdemand
        WHERE pdd.patientdrugdemandid = dpddi.patientdrugdemand
          AND pdd.patientdrugdemandid = dpddi.patientdrugdemand IS NOT NULL
          AND dd.drugrequest = p_id;
        
        DELETE
        FROM akuz.t_patient_drug_demand pdd
            USING akuz.t_pharmacy_drug_demand phdd
                JOIN akuz.t_drug_demand dd ON dd.drugdemandid = phdd.drugdemand
        WHERE phdd.pharmacydrugdemandid = pdd.pharmacydrugdemand
          AND dd.drugrequest = p_id;
        
        DELETE
        FROM akuz.t_pharmacy_drug_demand phdd
            USING akuz.t_drug_demand dd
        WHERE dd.drugdemandid = phdd.drugdemand
          AND dd.drugrequest = p_id;
        
        DELETE FROM akuz.t_drug_demand WHERE drugrequest = p_id;
        
        IF (EXISTS(SELECT *
                   FROM akuz.t_doctor_patient_drug_demand_items dpddi
                   JOIN akuz.t_doctor_patient_drug_demand dpdd
                       ON dpdd.doctorpatientdrugdemandid = dpddi.doctorpatientdrugdemand
                   JOIN akuz.s_dlo_doseview dv ON dv.id = dpdd.doseview
                   JOIN akuz.t_resource p ON p.resourceid = dpddi.post
                   JOIN akuz.t_division d ON d.divisionid = p.division
                   JOIN akuz.t_branch b ON b.lpuid = d.branch
                   JOIN akuz.t_benefits ben ON ben.benefitsid = dpdd.benefits
                   WHERE ben.benefittype = v_type
                     AND dpddi.beginperiod <= v_datebegin
                     AND dpddi.endperiod >= v_dateend
                     AND dpddi.patientdrugdemand IS NULL
                     AND b.lpumain = v_lpu
                     AND (v_branch IS NULL OR b.lpuid = v_branch)
                     AND NOT EXISTS (SELECT *
                                     FROM akuz.t_base_drug_request_item bdri
                                     LEFT JOIN akuz.t_request_catalog rc
                                         ON rc.basedrugrequestitemid = bdri.basedrugrequestitemid
                                     LEFT JOIN akuz.s_dlo_doseview dvv ON dvv.id = rc.doseview
                                     WHERE (bdri.applistbegindate <= dpddi.beginperiod AND
                                            bdri.applistenddate >= dpddi.endperiod AND
                                            rc.benefittype = ben.benefittype AND
                                            ((rc.cmnn IS NULL AND dpdd.cmnn IS NULL) OR (rc.cmnn = dpdd.cmnn))
                                         AND ((rc.ctrn IS NULL AND dpdd.ctrn IS NULL) OR (rc.ctrn = dpdd.ctrn)) AND
                                            bdri.medicationforms = dpdd.medicationforms AND dvv.value = dv.value AND
                                            rc.isactive = '1')
                                    )
                  ))
        THEN
            RAISE EXCEPTION 'Имеются элементы потребности на пациента для которых не найден элемент заявочного каталога!';
            RETURN;
        END IF;
        
        
        DROP TABLE IF EXISTS temp_doctor_patient_drug_demand_items;
        CREATE TEMP TABLE temp_doctor_patient_drug_demand_items
        (
            requestcode                    integer,
            basedrugrequestitemid          uuid,
            pharmacy                       integer,
            benefits                       uuid,
            comment                        text,
            diagnosis                      varchar(4000),
            mkb                            varchar(10),
            patient                        uuid,
            post                           int,
            signa                          varchar(255),
            drugkekresultmedicament        uuid,
            mont1                          decimal(12, 2),
            mont2                          decimal(12, 2),
            mont3                          decimal(12, 2),
            mont4                          decimal(12, 2),
            mont5                          decimal(12, 2),
            mont6                          decimal(12, 2),
            mont7                          decimal(12, 2),
            mont8                          decimal(12, 2),
            mont9                          decimal(12, 2),
            mont10                         decimal(12, 2),
            mont11                         decimal(12, 2),
            mont12                         decimal(12, 2),
            demandonperiod                 decimal(12, 2),
            requestquantity                decimal(12, 2),
            dd                             uuid,
            pdd                            uuid,
            newid                          uuid,
            doctorpatientdrugdemanditemsid uuid
        );
        
        INSERT INTO
            temp_doctor_patient_drug_demand_items (requestcode,
                                                   basedrugrequestitemid,
                                                   pharmacy,
                                                   benefits,
                                                   comment,
                                                   diagnosis,
                                                   mkb,
                                                   patient,
                                                   post,
                                                   signa,
                                                   drugkekresultmedicament,
                                                   mont1,
                                                   mont2,
                                                   mont3,
                                                   mont4,
                                                   mont5,
                                                   mont6,
                                                   mont7,
                                                   mont8,
                                                   mont9,
                                                   mont10,
                                                   mont11,
                                                   mont12,
                                                   demandonperiod,
                                                   requestquantity,
                                                   dd,
                                                   pdd,
                                                   newid,
                                                   doctorpatientdrugdemanditemsid)
        SELECT bdri.requestcode,
               bdri.basedrugrequestitemid,
               dpdd.pharmacy,
               dpdd.benefits,
               dpdd.comment,
               dpdd.diagnosis,
               dpdd.mkb,
               dpdd.patient,
               dpddi.post,
               dpdd.signa,
               dpdd.drugkekresultmedicament,
               dpddi.mont1,
               dpddi.mont2,
               dpddi.mont3,
               dpddi.mont4,
               dpddi.mont5,
               dpddi.mont6,
               dpddi.mont7,
               dpddi.mont8,
               dpddi.mont9,
               dpddi.mont10,
               dpddi.mont11,
               dpddi.mont12,
               dpddi.demandonperiod,
               dpddi.requestquantity,
               CAST(NULL AS uuid),
               CAST(NULL AS uuid),
               gen_random_uuid(),
               dpddi.doctorpatientdrugdemanditemsid
        FROM akuz.t_doctor_patient_drug_demand_items dpddi
        JOIN akuz.t_doctor_patient_drug_demand dpdd ON dpdd.doctorpatientdrugdemandid = dpddi.doctorpatientdrugdemand
        JOIN akuz.s_dlo_doseview dv ON dv.id = dpdd.doseview
        JOIN akuz.t_resource p ON p.resourceid = dpddi.post
        JOIN akuz.t_division d ON d.divisionid = p.division
        JOIN akuz.t_branch b ON b.lpuid = d.branch
        JOIN akuz.t_benefits ben ON ben.benefitsid = dpdd.benefits
        JOIN LATERAL (
            SELECT bdri.requestcode, bdri.basedrugrequestitemid
            FROM akuz.t_base_drug_request_item bdri
            LEFT JOIN akuz.t_request_catalog rc ON rc.basedrugrequestitemid = bdri.basedrugrequestitemid
            LEFT JOIN akuz.s_dlo_doseview dvv ON dvv.id = rc.doseview
            WHERE (bdri.applistbegindate <= dpddi.beginperiod AND bdri.applistenddate >= dpddi.endperiod AND
                   rc.benefittype = ben.benefittype AND
                   ((rc.cmnn IS NULL AND dpdd.cmnn IS NULL) OR (rc.cmnn = dpdd.cmnn))
                AND ((rc.ctrn IS NULL AND dpdd.ctrn IS NULL) OR (rc.ctrn = dpdd.ctrn)) AND
                   bdri.medicationforms = dpdd.medicationforms AND dvv.value = dv.value AND rc.isactive = TRUE)
            LIMIT 1
            ) bdri ON TRUE
        WHERE ben.benefittype = v_type
          AND dpddi.beginperiod <= v_datebegin
          AND dpddi.endperiod >= v_dateend
          AND dpddi.patientdrugdemand IS NULL
          AND b.lpumain = v_lpu
          AND (v_branch IS NULL OR b.lpuid = v_branch);
        
        DROP TABLE IF EXISTS temp_dd;
        CREATE TEMP TABLE temp_dd
        (
            id                    uuid,
            basedrugrequestitemid uuid
        );
        
        INSERT INTO temp_dd(id, basedrugrequestitemid)
        SELECT gen_random_uuid(), d.basedrugrequestitemid
        FROM temp_doctor_patient_drug_demand_items d
        GROUP BY d.basedrugrequestitemid;
        
        UPDATE temp_doctor_patient_drug_demand_items dpddi
        SET dd = dd.id
        FROM temp_dd dd
        WHERE dpddi.basedrugrequestitemid = dd.basedrugrequestitemid;
        
        DROP TABLE IF EXISTS temp_pdd;
        CREATE TEMP TABLE temp_pdd
        (
            id                    uuid,
            basedrugrequestitemid uuid,
            pharmacy              integer
        );
        
        INSERT INTO temp_pdd
        SELECT gen_random_uuid(), d.basedrugrequestitemid, d.pharmacy
        FROM temp_doctor_patient_drug_demand_items d
        GROUP BY d.basedrugrequestitemid, d.pharmacy;
        
        UPDATE temp_doctor_patient_drug_demand_items dpddi
        SET pdd = t_pdd.id
        FROM temp_pdd t_pdd
        WHERE dpddi.basedrugrequestitemid = t_pdd.basedrugrequestitemid
          AND t_pdd.pharmacy = dpddi.pharmacy;
        
        INSERT INTO
            akuz.t_drug_demand
        (drugdemandid, drugrequest, demandonperiod, requestquantity, requestcode, persrequest, requestcatalog)
        SELECT d.dd,
               p_id,
               0,
               0,
               d.requestcode,
               TRUE,
               d.basedrugrequestitemid
        FROM temp_doctor_patient_drug_demand_items d
        GROUP BY d.dd, d.requestcode, d.basedrugrequestitemid;
        
        INSERT INTO
            akuz.t_pharmacy_drug_demand
        (pharmacydrugdemandid,
         drugdemand,
         demandonperiod,
         pharmacy,
         requestquantity,
         mont1,
         mont2,
         mont3,
         mont4,
         mont5,
         mont6,
         mont7,
         mont8,
         mont9,
         mont10,
         mont11,
         mont12)
        SELECT d.pdd,
               d.dd,
               0,
               d.pharmacy,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0
        FROM temp_doctor_patient_drug_demand_items d
        GROUP BY d.pdd, d.dd, d.pharmacy;
        
        INSERT INTO
            akuz.t_patient_drug_demand
        (patientdrugdemandid,
         demandonperiod,
         patient,
         pharmacydrugdemand,
         comment,
         benefits,
         mkb,
         diagnosis,
         signa,
         requestquantity,
         mont1,
         mont2,
         mont3,
         mont4,
         mont5,
         mont6,
         mont7,
         mont8,
         mont9,
         mont10,
         mont11,
         mont12,
         post,
         drugkekresultmedicaments)
        SELECT d.newid,
               d.demandonperiod,
               d.patient,
               d.pdd,
               d.comment,
               d.benefits,
               d.mkb,
               d.diagnosis,
               d.signa,
               d.requestquantity,
               d.mont1,
               d.mont2,
               d.mont3,
               d.mont4,
               d.mont5,
               d.mont6,
               d.mont7,
               d.mont8,
               d.mont9,
               d.mont10,
               d.mont11,
               d.mont12,
               d.post,
               d.drugkekresultmedicament
        FROM temp_doctor_patient_drug_demand_items d;
        
        UPDATE akuz.t_pharmacy_drug_demand pd
        SET demandonperiod  = ss."DemandOnPeriod",
            requestquantity = ss."RequestQuantity",
            mont1           = ss."Mont1",
            mont2           = ss."Mont2",
            mont3           = ss."Mont3",
            mont4           = ss."Mont4",
            mont5           = ss."Mont5",
            mont6           = ss."Mont6",
            mont7           = ss."Mont7",
            mont8           = ss."Mont8",
            mont9           = ss."Mont9",
            mont10          = ss."Mont10",
            mont11          = ss."Mont11",
            mont12          = ss."Mont12"
        
        FROM akuz.t_pharmacy_drug_demand pddd
        JOIN temp_doctor_patient_drug_demand_items dd ON dd.pdd = pddd.pharmacydrugdemandid
        JOIN LATERAL (
            SELECT SUM(d.demandonperiod)  AS "DemandOnPeriod",
                   SUM(d.requestquantity) AS "RequestQuantity",
                   SUM(d.mont1)           AS "Mont1",
                   SUM(d.mont2)           AS "Mont2",
                   SUM(d.mont3)           AS "Mont3",
                   SUM(d.mont4)           AS "Mont4",
                   SUM(d.mont5)           AS "Mont5",
                   SUM(d.mont6)           AS "Mont6",
                   SUM(d.mont7)           AS "Mont7",
                   SUM(d.mont8)           AS "Mont8",
                   SUM(d.mont9)           AS "Mont9",
                   SUM(d.mont10)          AS "Mont10",
                   SUM(d.mont11)          AS "Mont11",
                   SUM(d.mont12)          AS "Mont12"
            FROM temp_doctor_patient_drug_demand_items d
            WHERE d.pdd = pddd.pharmacydrugdemandid
            GROUP BY d.pdd
            ) ss ON TRUE
        WHERE pd.pharmacydrugdemandid = pddd.pharmacydrugdemandid;
        
        UPDATE akuz.t_drug_demand d
        SET demandonperiod  = ss."DemandOnPeriod",
            requestquantity = ss."RequestQuantity"
        FROM akuz.t_drug_demand dd
        JOIN temp_doctor_patient_drug_demand_items ddd ON ddd.dd = dd.drugdemandid
        JOIN LATERAL (
            SELECT SUM(d.demandonperiod) AS "DemandOnPeriod", SUM(d.requestquantity) AS "RequestQuantity"
            FROM temp_doctor_patient_drug_demand_items d
            WHERE d.dd = dd.drugdemandid
            GROUP BY d.dd
            ) ss ON TRUE
        WHERE d.drugdemandid = dd.drugdemandid;
        
        UPDATE akuz.t_doctor_patient_drug_demand_items dpddi
        SET patientdrugdemand = dd.newid
        FROM temp_doctor_patient_drug_demand_items dd
        WHERE dd.doctorpatientdrugdemanditemsid = dpddi.doctorpatientdrugdemanditemsid;
    END;
END;
$$;

