BEGIN;

CREATE SCHEMA IF NOT EXISTS manufacturing;

CREATE TABLE IF NOT EXISTS manufacturing.production_orders (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    production_order_no varchar(100) NOT NULL,
    order_type varchar(40) NOT NULL DEFAULT 'make',
    manufacturing_type varchar(40) NOT NULL DEFAULT 'discrete',
    item_id bigint,
    item_code varchar(100),
    item_name varchar(255) NOT NULL,
    planned_quantity numeric(18,4) NOT NULL DEFAULT 0,
    uom_id bigint,
    uom_code varchar(50),
    planned_start_date date,
    due_date date,
    source_mrp_result_id bigint REFERENCES manufacturing.mrp_results(id) ON DELETE SET NULL,
    source_mrp_run_id bigint REFERENCES manufacturing.mrp_runs(id) ON DELETE SET NULL,
    source_document_no varchar(100),
    source_bom_id bigint,
    source_bom_no varchar(100),
    source_recipe_id bigint,
    source_recipe_no varchar(100),
    status varchar(40) NOT NULL DEFAULT 'planned',
    approval_status varchar(40) NOT NULL DEFAULT 'draft',
    notes text,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_by uuid,
    updated_by uuid,
    created_by_name varchar(255),
    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_orders_no UNIQUE (tenant_id, production_order_no)
);

ALTER TABLE IF EXISTS manufacturing.production_orders
    ADD COLUMN IF NOT EXISTS tenant_id uuid,
    ADD COLUMN IF NOT EXISTS production_order_no varchar(100),
    ADD COLUMN IF NOT EXISTS order_type varchar(40) NOT NULL DEFAULT 'make',
    ADD COLUMN IF NOT EXISTS manufacturing_type varchar(40) NOT NULL DEFAULT 'discrete',
    ADD COLUMN IF NOT EXISTS item_id bigint,
    ADD COLUMN IF NOT EXISTS item_code varchar(100),
    ADD COLUMN IF NOT EXISTS item_name varchar(255),
    ADD COLUMN IF NOT EXISTS planned_quantity numeric(18,4) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS uom_id bigint,
    ADD COLUMN IF NOT EXISTS uom_code varchar(50),
    ADD COLUMN IF NOT EXISTS planned_start_date date,
    ADD COLUMN IF NOT EXISTS due_date date,
    ADD COLUMN IF NOT EXISTS source_mrp_result_id bigint,
    ADD COLUMN IF NOT EXISTS source_mrp_run_id bigint,
    ADD COLUMN IF NOT EXISTS source_document_no varchar(100),
    ADD COLUMN IF NOT EXISTS source_bom_id bigint,
    ADD COLUMN IF NOT EXISTS source_bom_no varchar(100),
    ADD COLUMN IF NOT EXISTS source_recipe_id bigint,
    ADD COLUMN IF NOT EXISTS source_recipe_no varchar(100),
    ADD COLUMN IF NOT EXISTS status varchar(40) NOT NULL DEFAULT 'planned',
    ADD COLUMN IF NOT EXISTS approval_status varchar(40) NOT NULL DEFAULT 'draft',
    ADD COLUMN IF NOT EXISTS notes text,
    ADD COLUMN IF NOT EXISTS is_active boolean NOT NULL DEFAULT TRUE,
    ADD COLUMN IF NOT EXISTS is_deleted boolean NOT NULL DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS created_by uuid,
    ADD COLUMN IF NOT EXISTS updated_by uuid,
    ADD COLUMN IF NOT EXISTS created_by_name varchar(255),
    ADD COLUMN IF NOT EXISTS created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE IF EXISTS manufacturing.mrp_results
    ADD COLUMN IF NOT EXISTS conversion_status varchar(40) NOT NULL DEFAULT 'not_converted',
    ADD COLUMN IF NOT EXISTS conversion_action varchar(80),
    ADD COLUMN IF NOT EXISTS converted_document_type varchar(80),
    ADD COLUMN IF NOT EXISTS converted_document_id bigint,
    ADD COLUMN IF NOT EXISTS converted_document_no varchar(100),
    ADD COLUMN IF NOT EXISTS converted_quantity numeric(18,4) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS converted_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS converted_by uuid,
    ADD COLUMN IF NOT EXISTS converted_by_name varchar(255),
    ADD COLUMN IF NOT EXISTS conversion_notes text;

UPDATE manufacturing.mrp_results
SET conversion_status = COALESCE(NULLIF(conversion_status, ''), 'not_converted')
WHERE conversion_status IS NULL OR conversion_status = '';

CREATE INDEX IF NOT EXISTS idx_manufacturing_production_orders_source_mrp
    ON manufacturing.production_orders (tenant_id, source_mrp_result_id, source_mrp_run_id)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;

CREATE INDEX IF NOT EXISTS idx_manufacturing_mrp_results_conversion
    ON manufacturing.mrp_results (tenant_id, conversion_status, converted_document_type)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;

COMMIT;