BEGIN;

CREATE SCHEMA IF NOT EXISTS manufacturing;

ALTER TABLE IF EXISTS manufacturing.mrp_results
    ADD COLUMN IF NOT EXISTS source_type varchar(40),
    ADD COLUMN IF NOT EXISTS source_recipe_id bigint,
    ADD COLUMN IF NOT EXISTS source_recipe_no varchar(100),
    ADD COLUMN IF NOT EXISTS demand_quantity numeric(18, 4) NOT NULL DEFAULT 1,
    ADD COLUMN IF NOT EXISTS lead_time_days integer NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS planned_order_date date,
    ADD COLUMN IF NOT EXISTS planning_evidence jsonb NOT NULL DEFAULT '{}'::jsonb;

ALTER TABLE IF EXISTS manufacturing.mrp_runs
    ADD COLUMN IF NOT EXISTS planned_quantity numeric(18, 4) NOT NULL DEFAULT 1,
    ADD COLUMN IF NOT EXISTS source_type varchar(40) NOT NULL DEFAULT 'all',
    ADD COLUMN IF NOT EXISTS source_record_id bigint,
    ADD COLUMN IF NOT EXISTS source_record_no varchar(100);

CREATE INDEX IF NOT EXISTS idx_manufacturing_mrp_results_source
    ON manufacturing.mrp_results (tenant_id, source_type, source_bom_id, source_recipe_id);

COMMIT;