BEGIN;

CREATE SCHEMA IF NOT EXISTS manufacturing;

CREATE TABLE IF NOT EXISTS manufacturing.production_demands (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    demand_no varchar(100) NOT NULL,
    demand_type varchar(40) NOT NULL DEFAULT 'schedule',
    item_id bigint,
    item_code varchar(100),
    item_name varchar(255) NOT NULL,
    demand_quantity numeric(18, 4) NOT NULL DEFAULT 0,
    uom_id bigint,
    uom_code varchar(50),
    required_date date NOT NULL DEFAULT CURRENT_DATE,
    source_module varchar(60),
    source_record_id bigint,
    source_record_no varchar(100),
    status varchar(40) NOT NULL DEFAULT 'open',
    notes text,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_manufacturing_production_demands_no UNIQUE (tenant_id, demand_no)
);

ALTER TABLE IF EXISTS manufacturing.mrp_results
    ADD COLUMN IF NOT EXISTS demand_source varchar(80),
    ADD COLUMN IF NOT EXISTS source_document_no varchar(100),
    ADD COLUMN IF NOT EXISTS source_document_id bigint;

CREATE INDEX IF NOT EXISTS idx_manufacturing_production_demands_item
    ON manufacturing.production_demands (tenant_id, demand_type, item_id, item_code, required_date, status);

CREATE INDEX IF NOT EXISTS idx_manufacturing_mrp_results_demand_source
    ON manufacturing.mrp_results (tenant_id, demand_source, source_document_no);

COMMIT;