BEGIN;

ALTER TABLE accounting.vendor_bills
    ADD COLUMN IF NOT EXISTS source_module varchar(80),
    ADD COLUMN IF NOT EXISTS source_type varchar(80),
    ADD COLUMN IF NOT EXISTS purchase_order_id bigint,
    ADD COLUMN IF NOT EXISTS purchase_order_no varchar(80),
    ADD COLUMN IF NOT EXISTS goods_receipt_id bigint,
    ADD COLUMN IF NOT EXISTS goods_receipt_no varchar(80),
    ADD COLUMN IF NOT EXISTS three_way_match_status varchar(40),
    ADD COLUMN IF NOT EXISTS match_variance_amount numeric(18, 2) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS match_summary jsonb NOT NULL DEFAULT '{}'::jsonb;

ALTER TABLE accounting.vendor_bill_items
    ADD COLUMN IF NOT EXISTS purchase_order_id bigint,
    ADD COLUMN IF NOT EXISTS purchase_order_item_id bigint,
    ADD COLUMN IF NOT EXISTS goods_receipt_id bigint,
    ADD COLUMN IF NOT EXISTS goods_receipt_item_id bigint,
    ADD COLUMN IF NOT EXISTS received_quantity numeric(18, 4) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS accepted_quantity numeric(18, 4) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS billed_quantity numeric(18, 4) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS match_status varchar(40),
    ADD COLUMN IF NOT EXISTS variance_reason text;

CREATE INDEX IF NOT EXISTS idx_vendor_bills_purchase_order
    ON accounting.vendor_bills (tenant_id, purchase_order_id)
    WHERE purchase_order_id IS NOT NULL AND COALESCE(is_deleted, false) = false;

CREATE INDEX IF NOT EXISTS idx_vendor_bills_goods_receipt
    ON accounting.vendor_bills (tenant_id, goods_receipt_id)
    WHERE goods_receipt_id IS NOT NULL AND COALESCE(is_deleted, false) = false;

CREATE INDEX IF NOT EXISTS idx_vendor_bill_items_purchase_refs
    ON accounting.vendor_bill_items (tenant_id, purchase_order_item_id, goods_receipt_item_id)
    WHERE purchase_order_item_id IS NOT NULL OR goods_receipt_item_id IS NOT NULL;

COMMIT;