DROP PROCEDURE IF EXISTS rds.sp_drugstoreloading;

CREATE OR REPLACE PROCEDURE rds.sp_drugstoreloading(
    pharmacy int,
    xml xml DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_pharmacy int := pharmacy;
    p_xml      xml := xml;
BEGIN
    
    CREATE TEMP TABLE temp_drug AS
        SELECT (XPATH('/DRUG/CODE/text()', x))[1]::VARCHAR(20)                  AS code,
               (XPATH('/DRUG/NAME/text()', x))[1]::VARCHAR(4000)                AS name,
               (XPATH('/DRUG/MedificationForms/text()', x))[1]::VARCHAR(1000)   AS medicationforms,
               (XPATH('/DRUG/Dose/text()', x))[1]::VARCHAR(255)                 AS dose,
               (XPATH('/DRUG/MEDICAMENT_PROCUDER/text()', x))[1]::VARCHAR(4000) AS medicamentproducer,
               (XPATH('/DRUG/EAN/text()', x))[1]::VARCHAR(255)                  AS ean,
               (XPATH('/DRUG/PHARMACY/text()', x))[1]::VARCHAR(10)::INT         AS pharmacy
        FROM UNNEST(XPATH('DRUGS/DRUG', p_xml)) AS x;
    
    INSERT INTO rds.s_dlo_doseview(value)
    SELECT d.dose
    FROM temp_drug d
    WHERE NOT EXISTS (SELECT 1 FROM rds.s_dlo_doseview dv WHERE dv.value = d.dose)
    GROUP BY d.dose;
    
    UPDATE dl
    SET dose               = d.dose,
        ean                = d.ean,
        medicamentproducer = d.medicamentproducer,
        medicationforms    = d.medicationforms,
        name               = d.name
    FROM rds.t_drug_link_bill_of_goods dl
    JOIN temp_drug d ON d.code = dl.code AND d.pharmacy = dl.pharmacy;
    
    INSERT INTO
        rds.t_drug_link_bill_of_goods(druglinkbillofgoodsid,
                                      code,
                                      dose,
                                      ean,
                                      name,
                                      medicamentproducer,
                                      medicationforms,
                                      billofgoods,
                                      pharmacy)
    SELECT gen_random_uuid(),
           d.code,
           dv.id,
           d.ean,
           d.name,
           d.medicamentproducer,
           (SELECT mf.medicationformsid FROM pharm.t_medication_forms mf WHERE mf.code = d.medicationforms LIMIT 1),
           (SELECT b.billofgoodsid FROM pharm.t_bill_of_goods b WHERE b.ean = d.ean),
           d.pharmacy
    FROM temp_drug d
    JOIN rds.s_dlo_doseview dv ON dv.value = dose
    WHERE d.pharmacy = p_pharmacy
      AND NOT EXISTS (SELECT 1
                      FROM rds.t_drug_link_bill_of_goods dl
                      WHERE dl.code = d.code
                        AND dl.pharmacy = d.pharmacy
                     );
END;
$$