BEGIN;

CREATE TABLE IF NOT EXISTS manufacturing.production_genealogy_links (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    genealogy_no varchar(100) NOT NULL,
    production_order_id bigint NOT NULL REFERENCES manufacturing.production_orders(id) ON DELETE CASCADE,
    production_order_no varchar(100),
    link_type varchar(40) NOT NULL,
    item_id bigint,
    item_code varchar(100),
    item_name varchar(255),
    quantity numeric(18,4) NOT NULL DEFAULT 0,
    uom_id bigint,
    uom_code varchar(50),
    stock_balance_id bigint,
    stock_movement_id bigint,
    stock_movement_no varchar(100),
    lot_no varchar(120),
    batch_no varchar(120),
    serial_no varchar(160),
    supplier_batch_no varchar(160),
    expiry_date date,
    source_module varchar(80),
    source_type varchar(80),
    source_id bigint,
    source_no varchar(120),
    inspection_id bigint,
    inspection_no varchar(100),
    relationship_role varchar(80),
    notes 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,
    CONSTRAINT uq_manufacturing_production_genealogy_no UNIQUE (tenant_id, genealogy_no),
    CONSTRAINT chk_manufacturing_genealogy_link_type CHECK (link_type IN ('input_consumed', 'output_produced', 'byproduct_produced', 'scrap_generated'))
);

CREATE INDEX IF NOT EXISTS idx_manufacturing_genealogy_order
    ON manufacturing.production_genealogy_links (tenant_id, production_order_id, link_type)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;

CREATE INDEX IF NOT EXISTS idx_manufacturing_genealogy_lot_batch
    ON manufacturing.production_genealogy_links (tenant_id, item_id, item_code, lot_no, batch_no, serial_no)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;

ALTER TABLE IF EXISTS inventory.stock_balances
    ADD COLUMN IF NOT EXISTS production_order_id bigint,
    ADD COLUMN IF NOT EXISTS production_order_no varchar(100),
    ADD COLUMN IF NOT EXISTS finished_goods_inspection_id bigint,
    ADD COLUMN IF NOT EXISTS finished_goods_inspection_no varchar(100);

ALTER TABLE IF EXISTS inventory.stock_movements
    ADD COLUMN IF NOT EXISTS production_order_id bigint,
    ADD COLUMN IF NOT EXISTS production_order_no varchar(100),
    ADD COLUMN IF NOT EXISTS finished_goods_inspection_id bigint,
    ADD COLUMN IF NOT EXISTS finished_goods_inspection_no varchar(100);

COMMIT;