BEGIN;

CREATE SCHEMA IF NOT EXISTS manufacturing;

ALTER TABLE IF EXISTS manufacturing.boms
    ADD COLUMN IF NOT EXISTS manufacturing_type varchar(30) NOT NULL DEFAULT 'discrete',
    ADD COLUMN IF NOT EXISTS bom_family varchar(40) NOT NULL DEFAULT 'standard_bom',
    ADD COLUMN IF NOT EXISTS production_method varchar(40) NOT NULL DEFAULT 'discrete',
    ADD COLUMN IF NOT EXISTS yield_percent numeric(12,4) NOT NULL DEFAULT 100,
    ADD COLUMN IF NOT EXISTS batch_size numeric(18,6),
    ADD COLUMN IF NOT EXISTS batch_uom_id bigint,
    ADD COLUMN IF NOT EXISTS batch_uom_code varchar(40);

CREATE TABLE IF NOT EXISTS manufacturing.work_centers (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    work_center_code varchar(100) NOT NULL,
    work_center_name varchar(255) NOT NULL,
    manufacturing_type varchar(30) NOT NULL DEFAULT 'mixed',
    description text,
    capacity_per_day numeric(18,6),
    capacity_uom_id bigint,
    capacity_uom_code varchar(40),
    status varchar(40) NOT NULL DEFAULT 'active',
    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_work_centers_code UNIQUE (tenant_id, work_center_code)
);

CREATE TABLE IF NOT EXISTS manufacturing.resources (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    resource_code varchar(100) NOT NULL,
    resource_name varchar(255) NOT NULL,
    work_center_id bigint REFERENCES manufacturing.work_centers(id) ON DELETE SET NULL,
    resource_type varchar(60) NOT NULL DEFAULT 'machine',
    manufacturing_type varchar(30) NOT NULL DEFAULT 'mixed',
    capacity_per_day numeric(18,6),
    capacity_uom_id bigint,
    capacity_uom_code varchar(40),
    status varchar(40) NOT NULL DEFAULT 'active',
    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_resources_code UNIQUE (tenant_id, resource_code)
);

CREATE TABLE IF NOT EXISTS manufacturing.recipes (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    recipe_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',
    standard_batch_size numeric(18,6) NOT NULL DEFAULT 1,
    batch_uom_id bigint,
    batch_uom_code varchar(40),
    expected_yield_quantity numeric(18,6),
    expected_yield_percent numeric(12,4) NOT NULL DEFAULT 100,
    potency_required boolean NOT NULL DEFAULT FALSE,
    status varchar(40) NOT NULL DEFAULT 'draft',
    approval_status varchar(40) NOT NULL DEFAULT 'draft',
    effective_from date,
    effective_to date,
    approved_at timestamp without time zone,
    approved_by uuid,
    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 without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_manufacturing_recipes_no UNIQUE (tenant_id, recipe_no)
);

CREATE TABLE IF NOT EXISTS manufacturing.recipe_ingredients (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    recipe_id bigint NOT NULL REFERENCES manufacturing.recipes(id) ON DELETE CASCADE,
    line_no integer NOT NULL DEFAULT 1,
    ingredient_item_id bigint,
    ingredient_item_code varchar(100),
    ingredient_item_name varchar(255) NOT NULL,
    input_type varchar(60) NOT NULL DEFAULT 'raw_material',
    quantity_per_batch numeric(18,6) NOT NULL,
    uom_id bigint,
    uom_code varchar(40),
    potency_percent numeric(12,4),
    tolerance_percent numeric(12,4) NOT NULL DEFAULT 0,
    scrap_loss_percent numeric(12,4) NOT NULL DEFAULT 0,
    qa_release_required boolean NOT NULL DEFAULT TRUE,
    allow_substitute boolean NOT NULL DEFAULT FALSE,
    substitute_item_id bigint,
    substitute_item_code varchar(100),
    substitute_item_name varchar(255),
    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
);

CREATE TABLE IF NOT EXISTS manufacturing.recipe_outputs (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    recipe_id bigint NOT NULL REFERENCES manufacturing.recipes(id) ON DELETE CASCADE,
    output_item_id bigint,
    output_item_code varchar(100),
    output_item_name varchar(255) NOT NULL,
    output_type varchar(40) NOT NULL DEFAULT 'primary',
    output_quantity numeric(18,6) NOT NULL,
    uom_id bigint,
    uom_code varchar(40),
    expected_yield_percent numeric(12,4) NOT NULL DEFAULT 100,
    is_primary 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
);

CREATE TABLE IF NOT EXISTS manufacturing.recipe_phases (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    recipe_id bigint NOT NULL REFERENCES manufacturing.recipes(id) ON DELETE CASCADE,
    phase_no integer NOT NULL DEFAULT 1,
    phase_name varchar(255) NOT NULL,
    work_center_id bigint REFERENCES manufacturing.work_centers(id) ON DELETE SET NULL,
    work_center_name varchar(255),
    resource_id bigint REFERENCES manufacturing.resources(id) ON DELETE SET NULL,
    resource_name varchar(255),
    setup_minutes numeric(12,2) NOT NULL DEFAULT 0,
    run_minutes numeric(12,2) NOT NULL DEFAULT 0,
    duration_minutes numeric(12,2),
    temperature_min numeric(18,6),
    temperature_max numeric(18,6),
    pressure_min numeric(18,6),
    pressure_max numeric(18,6),
    qa_checkpoint boolean NOT NULL DEFAULT FALSE,
    instructions text,
    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
);

CREATE TABLE IF NOT EXISTS manufacturing.batch_parameters (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    recipe_id bigint NOT NULL REFERENCES manufacturing.recipes(id) ON DELETE CASCADE,
    phase_id bigint REFERENCES manufacturing.recipe_phases(id) ON DELETE CASCADE,
    parameter_code varchar(100),
    parameter_name varchar(255) NOT NULL,
    target_value varchar(100),
    min_value varchar(100),
    max_value varchar(100),
    uom_code varchar(40),
    is_critical_quality_attribute boolean NOT NULL DEFAULT FALSE,
    is_required 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
);

CREATE INDEX IF NOT EXISTS idx_manufacturing_boms_type
    ON manufacturing.boms (tenant_id, manufacturing_type, status, approval_status);

CREATE INDEX IF NOT EXISTS idx_manufacturing_recipes_tenant_status
    ON manufacturing.recipes (tenant_id, status, approval_status, updated_at DESC);

CREATE INDEX IF NOT EXISTS idx_manufacturing_recipe_ingredients_recipe
    ON manufacturing.recipe_ingredients (tenant_id, recipe_id, line_no);

CREATE INDEX IF NOT EXISTS idx_manufacturing_recipe_outputs_recipe
    ON manufacturing.recipe_outputs (tenant_id, recipe_id);

CREATE INDEX IF NOT EXISTS idx_manufacturing_recipe_phases_recipe
    ON manufacturing.recipe_phases (tenant_id, recipe_id, phase_no);

CREATE INDEX IF NOT EXISTS idx_manufacturing_batch_parameters_recipe
    ON manufacturing.batch_parameters (tenant_id, recipe_id, phase_id);

COMMIT;