fix data stock product status

/*
SASA-489:
Yang perlu di ubah
1. update in_product_balance_stock
  1. update qty untuk data yang asli
  1. record yang dobel dihapus (product_status <> 'GOOD')

2. update in_log_product_balance_stock
  1. update product_status
*/

--Preparation:
--1. backup data table in_product_balance_stock dan in_log_product_balance_stock
CREATE TABLE bak_in_product_balance_stock_20210428 AS
TABLE in_product_balance_stock;

CREATE TABLE bak_in_log_product_balance_stock_20210428 AS
TABLE in_log_product_balance_stock;

--Langkah update:
--0. buat temp table
    DROP TABLE IF EXISTS temp_table_fix_product_balance_stock;

    CREATE TABLE temp_table_fix_product_balance_stock(
        product_balance_stock_id bigint,
        tenant_id bigint,
        warehouse_id bigint,
        product_id bigint,
        product_balance_id bigint,
        product_status character varying(50),
        qty numeric
    );

--1. get daftar product di in_product_balance_stock yang status <> 'GOOD'
    SELECT A.*
    FROM in_product_balance_stock A
    WHERE A.product_status <> 'GOOD'
    ORDER BY A.create_datetime; -- (ada 6512 record)


--2. get daftar product yg dipakai, di in_log_product_balance_stock
    SELECT A.*
    FROM in_log_product_balance_stock A
    WHERE create_datetime > '20210424000000' -- kasusnya terjadi setelah tgl 24 April 2021
    AND warehouse_id IN (20, 15) -- kasusnya hanya untuk WH 2809 dan 2802
    AND product_status <> 'GOOD'
    ORDER BY create_datetime ASC; -- (ada 6512 record)

--3. update status product pada in_log_product_balance_stock
    UPDAET in_log_product_balance_stock
    SET product_status = 'GOOD'
    WHERE warehouse_id IN (20, 15)
    AND product_status <> 'GOOD'
    AND create_datetime > '20210424000000';

--4. simpan data poin 1 ke temp table
    INSERT INTO temp_table_fix_product_balance_stock(
        product_balance_stock_id, tenant_id, warehouse_id,
        product_id, product_balance_id, product_status, qty
    ) SELECT A.product_balance_stock_id, A.tenant_id, A.warehouse_id,
        A.product_id, A.product_balance_id, A.product_status, A.qty
    FROM in_product_balance_stock A
    WHERE A.product_status <> 'GOOD'
    ORDER BY A.create_datetime;

--5. update data in_product_balance_stock
    WITH table_prepare_update AS (
        SELECT A.tenant_id, A.warehouse_id,
        A.product_id, A.product_balance_id, SUM(A.qty) AS qty_add
        FROM temp_table_fix_product_balance_stock A
        GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id
    )
    UPDAXTE in_product_balance_stock A
        SET qty = qty + X.qty_add, version = + 1,
        update_datetime = to_char(NOW(), 'YYYYMMDDHH24MIss'), update_user_id = -1
    FROM table_prepare_update X
    WHERE A.tenant_id = X.tenant_id
    AND A.product_id = X.product_id
    AND A.warehouse_id = B.warehouse_id
    AND A.product_balance_id = X.product_balance_id
    AND A.product_status = 'GOOD';

--6. Update data in_product_balance_stock yang
--  1. product status <> 'GOOD'
--  2. tidak ada record lain yang serupa (data product_id, product_balance_id, warehouse_id sama) dengan product_status = 'GOOD'
    WITH table_prepare_update AS (
        SELECT A.product_balance_stock_id, A.tenant_id, A.warehouse_id,
        A.product_id, A.product_balance_id
        FROM temp_table_fix_product_balance_stock A
        WHERE NOT EXISTS (
            SELECT 1
            FROM in_product_balance_stock B
            WHERE A.product_id = B.product_id
            AND A.product_balance_id = B.product_balance_id
            AND A.warehouse_id = B.warehouse_id
            AND A.product_status <> B.product_status
        ) --GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id
    )UPDAXTE in_product_balance_stock A
        SET product_status = 'GOOD', version = + 1,
        update_datetime = to_char(NOW(), 'YYYYMMDDHH24MIss'), update_user_id = -1
    FROM table_prepare_update X
    WHERE A.product_balance_stock_id = X.product_balance_stock_id;

--7. Hapus data in_product_balance_stock yang tidak seharusnya ada (product_status <> 'GOOD')
    DELETE FROM in_product_balance_stock A
    WHERE EXISTS (
        SELECT 1
        FROM temp_table_fix_product_balance_stock B
        WHERE A.product_balance_stock_id = B.product_balance_stock_id
    ) AND A.product_status <> 'GOOD';

/*
Fakta baru:
1. Ada satu record in_product_balance_stock dengan status <> 'GOOD', tanpa record lain yang serupa (data product_id, product_balance_id, warehouse_id sama) dengan product_status = 'GOOD'
*/