-- =============================================
-- Author:      Vitacore Anatoliy
-- Create date: 2021 03 09
-- =============================================
DROP PROCEDURE IF EXISTS stor.sp_DeleteInvoiceItemProcess;

CREATE OR REPLACE PROCEDURE stor.sp_DeleteInvoiceItemProcess(
    "InvoiceId" uuid, 
    "All" boolean
) 
    language plpgsql
as
$$
BEGIN 
    IF EXISTS(SELECT 1 FROM STOR.T_STORAGE_INVOICE WHERE invoiceid = "InvoiceId" AND State = 0) THEN
        CREATE TEMP TABLE TmpDELETE(ElementID uuid) ON COMMIT DROP;
        
        IF "All" = '1' THEN
            INSERT INTO TmpDELETE(ElementID)
            SELECT ii.ElementID
            FROM STOR.T_INVOICE_ITEM ii
            WHERE ii.StorageInvoice = "InvoiceId";
        ELSE
            INSERT INTO TmpDELETE(ElementID)
            SELECT ii.ElementID
            FROM STOR.T_INVOICE_ITEM ii
            WHERE ii.StorageInvoice = "InvoiceId" 
            AND (ii.Amount IS NULL OR ii.Amount = 0);
        END IF;

		CREATE INDEX IF NOT EXISTS idx_tmp_delete_ElementID ON TmpDELETE(ElementID);
		ANALYZE TmpDELETE;
		
        DELETE FROM STOR.T_DESTRUCTION_ITEM_INFO 
        WHERE InvoiceItem IN (SELECT ElementID FROM TmpDELETE);

        UPDATE STOR.T_MARK_MESSAGE 
        SET ObjId = NULL
        WHERE ObjId IN (SELECT ElementID FROM TmpDELETE);

        UPDATE STOR.T_PATIENT_DRUG_DELIVERY
        SET InvoiceItem = NULL
        WHERE InvoiceItem IN (SELECT ElementID FROM TmpDELETE);
        
        update STOR.T_SGTINS s
        set IdInvoiceItem = null
        FROM TmpDELETE
        WHERE s.IdInvoiceItem = ElementID;

        DELETE FROM STOR.T_INVOICE_ITEM 
        WHERE ElementID IN (SELECT ElementID FROM TmpDELETE);
        
        -- No need for explicit DROP as we used ON COMMIT DROP
    END IF;
END;
$$;