BEGIN;

CREATE SCHEMA IF NOT EXISTS manufacturing;

CREATE TABLE IF NOT EXISTS manufacturing.boms (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    bom_no varchar(100) NOT NULL,
    item_id bigint,
    item_code varchar(100),
    item_name varchar(255) NOT NULL,
    version_no varchar(40) NOT NULL DEFAULT 'v1.0',
    status varchar(40) NOT NULL DEFAULT 'draft',
    effective_from date,
    effective_to date,
    output_quantity numeric(18, 4) NOT NULL DEFAULT 1,
    output_uom_id bigint,
    output_uom_code varchar(50),
    scrap_factor_percent numeric(10, 4) NOT NULL DEFAULT 0,
    estimated_unit_cost numeric(18, 4) NOT NULL DEFAULT 0,
    approval_status varchar(40) NOT NULL DEFAULT 'draft',
    approved_at timestamp,
    approved_by_name varchar(255),
    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 NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp,
    CONSTRAINT uq_manufacturing_boms_no UNIQUE (tenant_id, bom_no)
);

CREATE TABLE IF NOT EXISTS manufacturing.bom_components (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    bom_id bigint NOT NULL REFERENCES manufacturing.boms(id) ON DELETE CASCADE,
    line_no integer NOT NULL DEFAULT 1,
    component_item_id bigint,
    component_item_code varchar(100),
    component_item_name varchar(255) NOT NULL,
    quantity_per_output numeric(18, 6) NOT NULL DEFAULT 0,
    uom_id bigint,
    uom_code varchar(50),
    scrap_factor_percent numeric(10, 4) NOT NULL DEFAULT 0,
    substitute_item_id bigint,
    substitute_item_code varchar(100),
    substitute_item_name varchar(255),
    issue_method varchar(40) NOT NULL DEFAULT 'manual',
    qa_release_required boolean NOT NULL DEFAULT true,
    is_active boolean NOT NULL DEFAULT true,
    is_deleted boolean NOT NULL DEFAULT false,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS manufacturing.bom_outputs (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    bom_id bigint NOT NULL REFERENCES manufacturing.boms(id) ON DELETE CASCADE,
    output_item_id bigint,
    output_item_code varchar(100),
    output_item_name varchar(255) NOT NULL,
    output_quantity numeric(18, 6) NOT NULL DEFAULT 1,
    uom_id bigint,
    uom_code varchar(50),
    output_type varchar(40) NOT NULL DEFAULT 'finished_good',
    is_primary boolean NOT NULL DEFAULT true,
    is_deleted boolean NOT NULL DEFAULT false,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS manufacturing.routing_steps (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    bom_id bigint REFERENCES manufacturing.boms(id) ON DELETE CASCADE,
    step_no integer NOT NULL DEFAULT 1,
    step_name varchar(160) NOT NULL,
    work_center varchar(160),
    setup_minutes numeric(18, 4) NOT NULL DEFAULT 0,
    run_minutes numeric(18, 4) NOT NULL DEFAULT 0,
    qa_checkpoint boolean NOT NULL DEFAULT false,
    instructions text,
    is_deleted boolean NOT NULL DEFAULT false,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS manufacturing.mrp_runs (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    run_no varchar(100) NOT NULL,
    plan_name varchar(180) NOT NULL,
    planning_date date NOT NULL DEFAULT CURRENT_DATE,
    demand_source varchar(80) NOT NULL DEFAULT 'manual',
    horizon_days integer NOT NULL DEFAULT 30,
    status varchar(40) NOT NULL DEFAULT 'draft',
    generated_at timestamp,
    generated_by_name varchar(255),
    notes text,
    is_deleted boolean NOT NULL DEFAULT false,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_manufacturing_mrp_runs_no UNIQUE (tenant_id, run_no)
);

CREATE TABLE IF NOT EXISTS manufacturing.mrp_results (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    mrp_run_id bigint NOT NULL REFERENCES manufacturing.mrp_runs(id) ON DELETE CASCADE,
    item_id bigint,
    item_code varchar(100),
    item_name varchar(255) NOT NULL,
    item_type varchar(60),
    gross_requirement numeric(18, 4) NOT NULL DEFAULT 0,
    available_quantity numeric(18, 4) NOT NULL DEFAULT 0,
    reserved_quantity numeric(18, 4) NOT NULL DEFAULT 0,
    net_requirement numeric(18, 4) NOT NULL DEFAULT 0,
    recommended_action varchar(80) NOT NULL DEFAULT 'review',
    recommended_quantity numeric(18, 4) NOT NULL DEFAULT 0,
    uom_id bigint,
    uom_code varchar(50),
    required_date date,
    shortage_status varchar(40) NOT NULL DEFAULT 'ok',
    source_bom_id bigint,
    source_bom_no varchar(100),
    is_deleted boolean NOT NULL DEFAULT false,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE IF EXISTS manufacturing.boms
    ADD COLUMN IF NOT EXISTS tenant_id uuid,
    ADD COLUMN IF NOT EXISTS bom_no varchar(100),
    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 version_no varchar(40) NOT NULL DEFAULT 'v1.0',
    ADD COLUMN IF NOT EXISTS status varchar(40) NOT NULL DEFAULT 'draft',
    ADD COLUMN IF NOT EXISTS effective_from date,
    ADD COLUMN IF NOT EXISTS effective_to date,
    ADD COLUMN IF NOT EXISTS output_quantity numeric(18, 4) NOT NULL DEFAULT 1,
    ADD COLUMN IF NOT EXISTS output_uom_id bigint,
    ADD COLUMN IF NOT EXISTS output_uom_code varchar(50),
    ADD COLUMN IF NOT EXISTS scrap_factor_percent numeric(10, 4) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS estimated_unit_cost numeric(18, 4) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS approval_status varchar(40) NOT NULL DEFAULT 'draft',
    ADD COLUMN IF NOT EXISTS approved_at timestamp,
    ADD COLUMN IF NOT EXISTS approved_by_name varchar(255),
    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_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS deleted_at timestamp;

UPDATE manufacturing.boms
SET
    bom_no = COALESCE(NULLIF(bom_no, ''), 'BOM-' || id::text),
    item_name = COALESCE(NULLIF(item_name, ''), 'Manufactured item'),
    status = COALESCE(NULLIF(status, ''), 'draft'),
    approval_status = COALESCE(NULLIF(approval_status, ''), CASE WHEN LOWER(COALESCE(status, '')) = 'active' THEN 'approved' ELSE 'draft' END),
    updated_at = COALESCE(updated_at, CURRENT_TIMESTAMP)
WHERE tenant_id IS NOT NULL;

ALTER TABLE IF EXISTS manufacturing.bom_components
    ADD COLUMN IF NOT EXISTS tenant_id uuid,
    ADD COLUMN IF NOT EXISTS bom_id bigint,
    ADD COLUMN IF NOT EXISTS line_no integer NOT NULL DEFAULT 1,
    ADD COLUMN IF NOT EXISTS component_item_id bigint,
    ADD COLUMN IF NOT EXISTS component_item_code varchar(100),
    ADD COLUMN IF NOT EXISTS component_item_name varchar(255),
    ADD COLUMN IF NOT EXISTS quantity_per_output numeric(18, 6) 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 scrap_factor_percent numeric(10, 4) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS substitute_item_id bigint,
    ADD COLUMN IF NOT EXISTS substitute_item_code varchar(100),
    ADD COLUMN IF NOT EXISTS substitute_item_name varchar(255),
    ADD COLUMN IF NOT EXISTS issue_method varchar(40) NOT NULL DEFAULT 'manual',
    ADD COLUMN IF NOT EXISTS qa_release_required boolean NOT NULL DEFAULT true,
    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_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;

UPDATE manufacturing.bom_components
SET
    component_item_name = COALESCE(NULLIF(component_item_name, ''), 'Component item'),
    quantity_per_output = COALESCE(quantity_per_output, 0),
    uom_code = NULLIF(uom_code, ''),
    updated_at = COALESCE(updated_at, CURRENT_TIMESTAMP)
WHERE tenant_id IS NOT NULL;

ALTER TABLE IF EXISTS manufacturing.bom_outputs
    ADD COLUMN IF NOT EXISTS tenant_id uuid,
    ADD COLUMN IF NOT EXISTS bom_id bigint,
    ADD COLUMN IF NOT EXISTS output_item_id bigint,
    ADD COLUMN IF NOT EXISTS output_item_code varchar(100),
    ADD COLUMN IF NOT EXISTS output_item_name varchar(255),
    ADD COLUMN IF NOT EXISTS output_quantity numeric(18, 6) NOT NULL DEFAULT 1,
    ADD COLUMN IF NOT EXISTS uom_id bigint,
    ADD COLUMN IF NOT EXISTS uom_code varchar(50),
    ADD COLUMN IF NOT EXISTS output_type varchar(40) NOT NULL DEFAULT 'finished_good',
    ADD COLUMN IF NOT EXISTS is_primary boolean NOT NULL DEFAULT true,
    ADD COLUMN IF NOT EXISTS is_deleted boolean NOT NULL DEFAULT false,
    ADD COLUMN IF NOT EXISTS created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE IF EXISTS manufacturing.routing_steps
    ADD COLUMN IF NOT EXISTS tenant_id uuid,
    ADD COLUMN IF NOT EXISTS bom_id bigint,
    ADD COLUMN IF NOT EXISTS step_no integer NOT NULL DEFAULT 1,
    ADD COLUMN IF NOT EXISTS step_name varchar(160),
    ADD COLUMN IF NOT EXISTS work_center varchar(160),
    ADD COLUMN IF NOT EXISTS setup_minutes numeric(18, 4) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS run_minutes numeric(18, 4) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS qa_checkpoint boolean NOT NULL DEFAULT false,
    ADD COLUMN IF NOT EXISTS instructions text,
    ADD COLUMN IF NOT EXISTS is_deleted boolean NOT NULL DEFAULT false,
    ADD COLUMN IF NOT EXISTS created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;

UPDATE manufacturing.routing_steps
SET
    step_name = COALESCE(NULLIF(step_name, ''), 'Routing step'),
    updated_at = COALESCE(updated_at, CURRENT_TIMESTAMP)
WHERE tenant_id IS NOT NULL;

ALTER TABLE IF EXISTS manufacturing.mrp_runs
    ADD COLUMN IF NOT EXISTS tenant_id uuid,
    ADD COLUMN IF NOT EXISTS run_no varchar(100),
    ADD COLUMN IF NOT EXISTS plan_name varchar(180),
    ADD COLUMN IF NOT EXISTS planning_date date NOT NULL DEFAULT CURRENT_DATE,
    ADD COLUMN IF NOT EXISTS demand_source varchar(80) NOT NULL DEFAULT 'manual',
    ADD COLUMN IF NOT EXISTS horizon_days integer NOT NULL DEFAULT 30,
    ADD COLUMN IF NOT EXISTS status varchar(40) NOT NULL DEFAULT 'draft',
    ADD COLUMN IF NOT EXISTS generated_at timestamp,
    ADD COLUMN IF NOT EXISTS generated_by_name varchar(255),
    ADD COLUMN IF NOT EXISTS notes text,
    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_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;

UPDATE manufacturing.mrp_runs
SET
    run_no = COALESCE(NULLIF(run_no, ''), 'MRP-' || id::text),
    plan_name = COALESCE(NULLIF(plan_name, ''), 'MRP Run ' || id::text),
    updated_at = COALESCE(updated_at, CURRENT_TIMESTAMP)
WHERE tenant_id IS NOT NULL;

ALTER TABLE IF EXISTS manufacturing.mrp_results
    ADD COLUMN IF NOT EXISTS tenant_id uuid,
    ADD COLUMN IF NOT EXISTS mrp_run_id bigint,
    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 item_type varchar(60),
    ADD COLUMN IF NOT EXISTS gross_requirement numeric(18, 4) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS available_quantity numeric(18, 4) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS reserved_quantity numeric(18, 4) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS net_requirement numeric(18, 4) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS recommended_action varchar(80) NOT NULL DEFAULT 'review',
    ADD COLUMN IF NOT EXISTS recommended_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 required_date date,
    ADD COLUMN IF NOT EXISTS shortage_status varchar(40) NOT NULL DEFAULT 'ok',
    ADD COLUMN IF NOT EXISTS source_bom_id bigint,
    ADD COLUMN IF NOT EXISTS source_bom_no varchar(100),
    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_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;

CREATE INDEX IF NOT EXISTS idx_manufacturing_boms_tenant_status
    ON manufacturing.boms (tenant_id, status, approval_status)
    WHERE COALESCE(is_deleted, false) = false;

CREATE INDEX IF NOT EXISTS idx_manufacturing_bom_components_bom
    ON manufacturing.bom_components (tenant_id, bom_id)
    WHERE COALESCE(is_deleted, false) = false;

CREATE INDEX IF NOT EXISTS idx_manufacturing_mrp_runs_tenant
    ON manufacturing.mrp_runs (tenant_id, planning_date DESC, id DESC)
    WHERE COALESCE(is_deleted, false) = false;

CREATE INDEX IF NOT EXISTS idx_manufacturing_mrp_results_run
    ON manufacturing.mrp_results (tenant_id, mrp_run_id)
    WHERE COALESCE(is_deleted, false) = false;

COMMIT;