BEGIN;

CREATE TABLE IF NOT EXISTS manufacturing.finished_goods_inspections (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    inspection_no varchar(100) NOT NULL,
    production_order_id bigint NOT NULL REFERENCES manufacturing.production_orders(id) ON DELETE CASCADE,
    production_order_no varchar(100),
    item_id bigint,
    item_code varchar(100),
    item_name varchar(255),
    batch_no varchar(100),
    lot_no varchar(100),
    uom_id bigint,
    uom_code varchar(50),
    produced_quantity numeric(18,4) NOT NULL DEFAULT 0,
    sampled_quantity numeric(18,4) NOT NULL DEFAULT 0,
    accepted_quantity numeric(18,4) NOT NULL DEFAULT 0,
    rejected_quantity numeric(18,4) NOT NULL DEFAULT 0,
    quarantined_quantity numeric(18,4) NOT NULL DEFAULT 0,
    rework_required_quantity numeric(18,4) NOT NULL DEFAULT 0,
    outcome varchar(40) NOT NULL DEFAULT 'pending',
    inspection_status varchar(40) NOT NULL DEFAULT 'draft',
    test_results jsonb NOT NULL DEFAULT '{}'::jsonb,
    notes text,
    inspected_at timestamp without time zone,
    inspected_by uuid,
    inspected_by_name varchar(255),
    released_at timestamp without time zone,
    released_by uuid,
    released_by_name varchar(255),
    is_current 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_finished_goods_inspections_no UNIQUE (tenant_id, inspection_no),
    CONSTRAINT chk_manufacturing_finished_goods_inspection_outcome CHECK (outcome IN ('pending', 'passed', 'failed', 'partial_release', 'quarantine', 'rework_required')),
    CONSTRAINT chk_manufacturing_finished_goods_inspection_qty_nonnegative CHECK (
        produced_quantity >= 0
        AND sampled_quantity >= 0
        AND accepted_quantity >= 0
        AND rejected_quantity >= 0
        AND quarantined_quantity >= 0
        AND rework_required_quantity >= 0
    ),
    CONSTRAINT chk_manufacturing_finished_goods_inspection_disposition CHECK (
        accepted_quantity + rejected_quantity + quarantined_quantity + rework_required_quantity <= produced_quantity
    )
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_manufacturing_finished_goods_inspections_current
    ON manufacturing.finished_goods_inspections (tenant_id, production_order_id)
    WHERE is_current = TRUE AND COALESCE(is_deleted, FALSE) = FALSE;

CREATE INDEX IF NOT EXISTS idx_manufacturing_finished_goods_inspections_status
    ON manufacturing.finished_goods_inspections (tenant_id, outcome, inspection_status)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;

ALTER TABLE IF EXISTS manufacturing.production_orders
    ADD COLUMN IF NOT EXISTS finished_goods_inspection_id bigint,
    ADD COLUMN IF NOT EXISTS finished_goods_inspection_no varchar(100);

COMMIT;