BEGIN;

ALTER TABLE IF EXISTS inventory.inventory_variance_cases
    ADD COLUMN IF NOT EXISTS stock_adjustment_id bigint,
    ADD COLUMN IF NOT EXISTS stock_adjustment_no varchar(100),
    ADD COLUMN IF NOT EXISTS inventory_movement_id bigint,
    ADD COLUMN IF NOT EXISTS posted_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS posted_by_name varchar(255),
    ADD COLUMN IF NOT EXISTS posting_notes text;

ALTER TABLE IF EXISTS inventory.stock_adjustments
    ADD COLUMN IF NOT EXISTS stock_count_id bigint,
    ADD COLUMN IF NOT EXISTS stock_count_no varchar(100),
    ADD COLUMN IF NOT EXISTS variance_case_id bigint,
    ADD COLUMN IF NOT EXISTS variance_no varchar(100);

ALTER TABLE IF EXISTS inventory.stock_adjustment_items
    ADD COLUMN IF NOT EXISTS variance_case_id bigint,
    ADD COLUMN IF NOT EXISTS stock_count_item_id bigint,
    ADD COLUMN IF NOT EXISTS system_quantity numeric(18,4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS physical_quantity numeric(18,4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS balance_before numeric(18,4),
    ADD COLUMN IF NOT EXISTS balance_after numeric(18,4),
    ADD COLUMN IF NOT EXISTS inventory_movement_id bigint;

CREATE INDEX IF NOT EXISTS idx_inventory_variance_cases_posting
    ON inventory.inventory_variance_cases (tenant_id, stock_adjustment_id, inventory_movement_id);

CREATE INDEX IF NOT EXISTS idx_inventory_stock_adjustments_source
    ON inventory.stock_adjustments (tenant_id, source_entity_type, source_entity_id);

COMMIT;