BEGIN;

ALTER TABLE IF EXISTS warehouse.pick_backorders
    ADD COLUMN IF NOT EXISTS stock_allocation_id bigint,
    ADD COLUMN IF NOT EXISTS stock_allocation_no varchar(100),
    ADD COLUMN IF NOT EXISTS allocation_status varchar(60),
    ADD COLUMN IF NOT EXISTS reallocated_at timestamp,
    ADD COLUMN IF NOT EXISTS reallocated_by_name varchar(255),
    ADD COLUMN IF NOT EXISTS resolution_notes text,
    ADD COLUMN IF NOT EXISTS cancelled_at timestamp,
    ADD COLUMN IF NOT EXISTS cancelled_by_name varchar(255);

ALTER TABLE IF EXISTS warehouse.pick_substitution_requests
    ADD COLUMN IF NOT EXISTS stock_allocation_id bigint,
    ADD COLUMN IF NOT EXISTS stock_allocation_no varchar(100),
    ADD COLUMN IF NOT EXISTS allocation_status varchar(60),
    ADD COLUMN IF NOT EXISTS decision_notes text,
    ADD COLUMN IF NOT EXISTS rejected_at timestamp,
    ADD COLUMN IF NOT EXISTS rejected_by_name varchar(255);

CREATE INDEX IF NOT EXISTS idx_warehouse_pick_backorders_allocation
    ON warehouse.pick_backorders (tenant_id, stock_allocation_id);

CREATE INDEX IF NOT EXISTS idx_warehouse_pick_substitutions_allocation
    ON warehouse.pick_substitution_requests (tenant_id, stock_allocation_id);

COMMIT;