CREATE OR REPLACE PROCEDURE rds.sp_importremains(
    pharmacy int,
    xml xml DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml      xml := xml;
    p_pharmacy int := pharmacy;
BEGIN
    
    CREATE TEMP TABLE temp_remains AS
        SELECT (XPATH('/REMAIN/PHARMACY/text()', x))[1]::VARCHAR(10)::INT       AS pharmacy,
               (XPATH('/REMAIN/DRUG/text()', x))[1]::VARCHAR(20)                AS drug,
               (XPATH('/REMAIN/AMOUNT/text()', x))[1]::text::decimal(12, 2)     AS amount,
               (XPATH('/REMAIN/EXPIRATION_DATE/text()', x))[1]::text::timestamp AS expirationdate,
               (XPATH('/REMAIN/COMMENT/text()', x))[1]::VARCHAR(4000)           AS comment,
               (XPATH('/REMAIN/SERIES/text()', x))[1]::VARCHAR(100)             AS series,
               (XPATH('/REMAIN/LOT_NUMBER/text()', x))[1]::VARCHAR(10)::INT     AS lotnumber,
               (XPATH('/REMAIN/PRICE/text()', x))[1]::TEXT::DECIMAL(12, 2)      AS price
        FROM UNNEST(XPATH('REMAINS/REMAIN', p_xml)) AS x;
    
    UPDATE rds.t_remains r
    SET lotnumber        = rr.lotnumber,
        series           = rr.series,
        comment          = rr.comment,
        expirationdate   = rr.expirationdate,
        amount           = rr.amount,
        price            = rr.price,
        lastmodifieddate = CURRENT_TIMESTAMP
    FROM rds.t_pharmacy p
    JOIN temp_remains rr ON rr.pharmacy = p.code AND rr.drug = r.drugcode
    WHERE r.pharmacy = p.code;
    
    INSERT INTO
        rds.t_remains
    (remainsid,
     storage,
     lotnumber,
     series,
     billofgoods,
     comment,
     expirationdate,
     amount,
     price,
     pharmacy,
     finance,
     druglinkbillofgoods,
     drugcode,
     lastmodifieddate)
    SELECT gen_random_uuid(),
           p.name,
           r.lotnumber,
           r.series,
           dl.billofgoods,
           r.comment,
           r.expirationdate,
           r.amount,
           r.price,
           r.pharmacy,
           24209,
           dl.druglinkbillofgoodsid,
           r.drug,
           CURRENT_TIMESTAMP
    FROM temp_remains r
    JOIN rds.t_pharmacy p ON p.code = r.pharmacy
    LEFT JOIN rds.t_drug_link_bill_of_goods dl ON dl.code = r.drug AND r.pharmacy = dl.pharmacy
    WHERE r.pharmacy = p_pharmacy
      AND p.usepharmacyproduct = TRUE
      AND NOT EXISTS (SELECT *
                      FROM rds.t_remains rr
                      JOIN rds.t_pharmacy p1 ON p1.code = rr.pharmacy
                      WHERE rr.drugcode = r.drug
                        AND rr.pharmacy = r.pharmacy
                        AND p1.usepharmacyproduct = TRUE
                     );
    
    INSERT INTO
        rds.t_remains
    (remainsid,
     storage,
     lotnumber,
     series,
     billofgoods,
     comment,
     expirationdate,
     amount,
     price,
     pharmacy,
     finance,
     druglinkbillofgoods,
     drugcode,
     lastmodifieddate)
    SELECT gen_random_uuid(),
           p.name,
           r.lotnumber,
           r.series,
           b.billofgoodsid,
           r.comment,
           r.expirationdate,
           r.amount,
           r.price,
           r.pharmacy,
           24209,
           NULL,
           r.drug,
           CURRENT_TIMESTAMP
    FROM temp_remains r
    JOIN rds.t_pharmacy p ON p.code = r.pharmacy
    LEFT JOIN pharm.t_bill_of_goods b ON b.otherid = r.drug
    WHERE r.pharmacy = p_pharmacy
      AND p.usepharmacyproduct = FALSE
      AND NOT EXISTS (SELECT *
                      FROM rds.t_remains rr
                      JOIN rds.t_pharmacy p1 ON p1.code = rr.pharmacy
                      WHERE rr.drugcode = r.drug
                        AND rr.pharmacy = r.pharmacy
                        AND p1.usepharmacyproduct = FALSE
                     );

END;
$$