BEGIN;

CREATE SCHEMA IF NOT EXISTS inventory;

ALTER TABLE IF EXISTS inventory.items
    ADD COLUMN IF NOT EXISTS item_code varchar(100),
    ADD COLUMN IF NOT EXISTS sku varchar(100),
    ADD COLUMN IF NOT EXISTS item_name varchar(255),
    ADD COLUMN IF NOT EXISTS brand_id bigint,
    ADD COLUMN IF NOT EXISTS brand_name varchar(255),
    ADD COLUMN IF NOT EXISTS category_id bigint,
    ADD COLUMN IF NOT EXISTS category_name varchar(255),
    ADD COLUMN IF NOT EXISTS sub_category_id bigint,
    ADD COLUMN IF NOT EXISTS sub_category_name varchar(255),
    ADD COLUMN IF NOT EXISTS barcode varchar(255),
    ADD COLUMN IF NOT EXISTS unit_of_measure varchar(50),
    ADD COLUMN IF NOT EXISTS cost_price numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS selling_price numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS default_warehouse_id bigint,
    ADD COLUMN IF NOT EXISTS default_warehouse_name varchar(255),
    ADD COLUMN IF NOT EXISTS track_stock boolean DEFAULT TRUE,
    ADD COLUMN IF NOT EXISTS serial_tracking boolean DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS reorder_level numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS min_stock_level numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS max_stock_level numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS inventory_account_code varchar(100),
    ADD COLUMN IF NOT EXISTS cogs_account_code varchar(100),
    ADD COLUMN IF NOT EXISTS status varchar(50) DEFAULT 'active',
    ADD COLUMN IF NOT EXISTS is_active boolean DEFAULT TRUE,
    ADD COLUMN IF NOT EXISTS is_deleted boolean DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS created_by uuid,
    ADD COLUMN IF NOT EXISTS updated_by uuid,
    ADD COLUMN IF NOT EXISTS deleted_at timestamp,
    ADD COLUMN IF NOT EXISTS deleted_by uuid;

ALTER TABLE IF EXISTS inventory.item_variants
    ADD COLUMN IF NOT EXISTS item_id bigint,
    ADD COLUMN IF NOT EXISTS variant_code varchar(100),
    ADD COLUMN IF NOT EXISTS variant_name varchar(255),
    ADD COLUMN IF NOT EXISTS barcode varchar(255),
    ADD COLUMN IF NOT EXISTS attributes jsonb DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS cost_price numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS selling_price numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS status varchar(50) DEFAULT 'active',
    ADD COLUMN IF NOT EXISTS is_deleted boolean DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb;

ALTER TABLE IF EXISTS inventory.stock_balances
    ADD COLUMN IF NOT EXISTS item_id bigint,
    ADD COLUMN IF NOT EXISTS item_variant_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 category_name varchar(255),
    ADD COLUMN IF NOT EXISTS warehouse_id bigint,
    ADD COLUMN IF NOT EXISTS warehouse_name varchar(255),
    ADD COLUMN IF NOT EXISTS available_quantity numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS reserved_quantity numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS on_hand_quantity numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS incoming_quantity numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS outgoing_quantity numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS reorder_level numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS unit_cost numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS stock_value numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS stock_status varchar(50) DEFAULT 'healthy',
    ADD COLUMN IF NOT EXISTS oldest_receipt_date date,
    ADD COLUMN IF NOT EXISTS aging_buckets jsonb DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS updated_at timestamp DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE IF EXISTS inventory.stock_movements
    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 warehouse_id bigint,
    ADD COLUMN IF NOT EXISTS warehouse_name varchar(255),
    ADD COLUMN IF NOT EXISTS movement_type varchar(100),
    ADD COLUMN IF NOT EXISTS direction varchar(20),
    ADD COLUMN IF NOT EXISTS quantity numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS balance_after numeric(18, 4),
    ADD COLUMN IF NOT EXISTS unit_cost numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS reference_type varchar(100),
    ADD COLUMN IF NOT EXISTS reference_id bigint,
    ADD COLUMN IF NOT EXISTS reference_no varchar(100),
    ADD COLUMN IF NOT EXISTS occurred_at timestamp DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS occurred_by_name varchar(255),
    ADD COLUMN IF NOT EXISTS notes text,
    ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb;

ALTER TABLE IF EXISTS inventory.stock_counts
    ADD COLUMN IF NOT EXISTS count_no varchar(100),
    ADD COLUMN IF NOT EXISTS count_number varchar(100),
    ADD COLUMN IF NOT EXISTS warehouse_id bigint,
    ADD COLUMN IF NOT EXISTS warehouse_name varchar(255),
    ADD COLUMN IF NOT EXISTS count_date date,
    ADD COLUMN IF NOT EXISTS status varchar(50) DEFAULT 'draft',
    ADD COLUMN IF NOT EXISTS approval_status varchar(50) DEFAULT 'draft',
    ADD COLUMN IF NOT EXISTS variance_status varchar(50) DEFAULT 'none',
    ADD COLUMN IF NOT EXISTS assigned_to_name varchar(255),
    ADD COLUMN IF NOT EXISTS item_count integer DEFAULT 0,
    ADD COLUMN IF NOT EXISTS completed_item_count integer DEFAULT 0,
    ADD COLUMN IF NOT EXISTS total_variance_quantity numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS total_variance_value numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS submitted_at timestamp,
    ADD COLUMN IF NOT EXISTS submitted_by_name varchar(255),
    ADD COLUMN IF NOT EXISTS approved_at timestamp,
    ADD COLUMN IF NOT EXISTS approved_by_name varchar(255),
    ADD COLUMN IF NOT EXISTS adjusted_at timestamp,
    ADD COLUMN IF NOT EXISTS adjusted_by_name varchar(255),
    ADD COLUMN IF NOT EXISTS notes text,
    ADD COLUMN IF NOT EXISTS is_deleted boolean DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb;

ALTER TABLE IF EXISTS inventory.stock_count_items
    ADD COLUMN IF NOT EXISTS stock_count_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 unit_of_measure varchar(50),
    ADD COLUMN IF NOT EXISTS system_quantity numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS physical_quantity numeric(18, 4),
    ADD COLUMN IF NOT EXISTS variance_quantity numeric(18, 4),
    ADD COLUMN IF NOT EXISTS unit_cost numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS variance_value numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS status varchar(50) DEFAULT 'open',
    ADD COLUMN IF NOT EXISTS counted_at timestamp,
    ADD COLUMN IF NOT EXISTS counted_by_name varchar(255),
    ADD COLUMN IF NOT EXISTS notes text,
    ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb;

ALTER TABLE IF EXISTS inventory.stock_adjustments
    ADD COLUMN IF NOT EXISTS adjustment_no varchar(100),
    ADD COLUMN IF NOT EXISTS adjustment_type varchar(100),
    ADD COLUMN IF NOT EXISTS warehouse_id bigint,
    ADD COLUMN IF NOT EXISTS warehouse_name varchar(255),
    ADD COLUMN IF NOT EXISTS status varchar(50) DEFAULT 'draft',
    ADD COLUMN IF NOT EXISTS approval_status varchar(50) DEFAULT 'draft',
    ADD COLUMN IF NOT EXISTS source_entity_type varchar(100),
    ADD COLUMN IF NOT EXISTS source_entity_id bigint,
    ADD COLUMN IF NOT EXISTS source_entity_no varchar(100),
    ADD COLUMN IF NOT EXISTS total_quantity numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS total_value numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS reason varchar(255),
    ADD COLUMN IF NOT EXISTS submitted_at timestamp,
    ADD COLUMN IF NOT EXISTS approved_at timestamp,
    ADD COLUMN IF NOT EXISTS approved_by_name varchar(255),
    ADD COLUMN IF NOT EXISTS posted_at timestamp,
    ADD COLUMN IF NOT EXISTS posted_by_name varchar(255),
    ADD COLUMN IF NOT EXISTS is_deleted boolean DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb;

ALTER TABLE IF EXISTS inventory.stock_adjustment_items
    ADD COLUMN IF NOT EXISTS stock_adjustment_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 unit_of_measure varchar(50),
    ADD COLUMN IF NOT EXISTS adjustment_quantity numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS unit_cost numeric(18, 4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS line_value numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS reason varchar(255),
    ADD COLUMN IF NOT EXISTS notes text,
    ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb;

CREATE TABLE IF NOT EXISTS inventory.categories (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    category_code varchar(100),
    category_name varchar(255) NOT NULL,
    parent_category_id bigint,
    parent_category_name varchar(255),
    description text,
    item_count integer DEFAULT 0,
    status varchar(50) DEFAULT 'active',
    is_active boolean DEFAULT TRUE,
    is_deleted boolean DEFAULT FALSE,
    metadata jsonb DEFAULT '{}'::jsonb,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp
);

CREATE TABLE IF NOT EXISTS inventory.brands (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    brand_code varchar(100),
    brand_name varchar(255) NOT NULL,
    description text,
    logo_url text,
    active_item_count integer DEFAULT 0,
    status varchar(50) DEFAULT 'active',
    is_active boolean DEFAULT TRUE,
    is_deleted boolean DEFAULT FALSE,
    metadata jsonb DEFAULT '{}'::jsonb,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp
);

CREATE TABLE IF NOT EXISTS inventory.reorder_rules (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    rule_no varchar(100),
    item_id bigint,
    item_code varchar(100),
    item_name varchar(255),
    warehouse_id bigint,
    warehouse_name varchar(255),
    current_stock numeric(18, 4) DEFAULT 0,
    reorder_level numeric(18, 4) NOT NULL DEFAULT 0,
    suggested_quantity numeric(18, 4) NOT NULL DEFAULT 0,
    lead_time_days integer DEFAULT 0,
    avg_daily_usage numeric(18, 4) DEFAULT 0,
    days_until_stockout integer,
    last_restocked_at date,
    status varchar(50) DEFAULT 'normal',
    ai_confidence_label varchar(50),
    ai_recommendation text,
    preferred_supplier_id bigint,
    preferred_supplier_name varchar(255),
    converted_purchase_order_id bigint,
    converted_purchase_order_no varchar(100),
    is_active boolean DEFAULT TRUE,
    is_deleted boolean DEFAULT FALSE,
    metadata jsonb DEFAULT '{}'::jsonb,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS inventory.stock_transfers (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    transfer_no varchar(100) NOT NULL,
    source_warehouse_id bigint,
    source_warehouse_name varchar(255),
    destination_warehouse_id bigint,
    destination_warehouse_name varchar(255),
    transfer_date date DEFAULT CURRENT_DATE,
    expected_receipt_date date,
    item_count integer DEFAULT 0,
    total_quantity numeric(18, 4) DEFAULT 0,
    total_value numeric(18, 2) DEFAULT 0,
    status varchar(50) DEFAULT 'draft',
    approval_status varchar(50) DEFAULT 'draft',
    priority varchar(50) DEFAULT 'normal',
    submitted_at timestamp,
    submitted_by_name varchar(255),
    approved_at timestamp,
    approved_by_name varchar(255),
    dispatched_at timestamp,
    received_at timestamp,
    cancelled_at timestamp,
    created_by_name varchar(255),
    notes text,
    is_deleted boolean DEFAULT FALSE,
    metadata jsonb DEFAULT '{}'::jsonb,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS inventory.stock_transfer_items (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    stock_transfer_id bigint NOT NULL,
    item_id bigint,
    item_code varchar(100),
    item_name varchar(255),
    unit_of_measure varchar(50),
    available_quantity_snapshot numeric(18, 4) DEFAULT 0,
    transfer_quantity numeric(18, 4) NOT NULL DEFAULT 0,
    dispatched_quantity numeric(18, 4) DEFAULT 0,
    received_quantity numeric(18, 4) DEFAULT 0,
    unit_cost numeric(18, 4) DEFAULT 0,
    line_value numeric(18, 2) DEFAULT 0,
    status varchar(50) DEFAULT 'draft',
    notes text,
    metadata jsonb DEFAULT '{}'::jsonb,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS inventory.stock_transfer_events (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    event_no varchar(100),
    stock_transfer_id bigint,
    transfer_no varchar(100),
    event_type varchar(100),
    from_status varchar(50),
    to_status varchar(50),
    event_title varchar(255),
    event_details text,
    occurred_at timestamp DEFAULT CURRENT_TIMESTAMP,
    occurred_by_name varchar(255),
    metadata jsonb DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS inventory.scrap_requests (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    scrap_no varchar(100) NOT NULL,
    item_id bigint,
    item_code varchar(100),
    item_name varchar(255),
    warehouse_id bigint,
    warehouse_name varchar(255),
    scrap_date date DEFAULT CURRENT_DATE,
    quantity numeric(18, 4) NOT NULL DEFAULT 0,
    unit_cost numeric(18, 4) DEFAULT 0,
    cost_impact numeric(18, 2) DEFAULT 0,
    reason varchar(255),
    status varchar(50) DEFAULT 'draft',
    approval_status varchar(50) DEFAULT 'draft',
    requires_approval boolean DEFAULT FALSE,
    approval_threshold_quantity numeric(18, 4),
    submitted_at timestamp,
    submitted_by_name varchar(255),
    approved_at timestamp,
    approved_by_name varchar(255),
    rejected_at timestamp,
    rejected_by_name varchar(255),
    rejection_reason text,
    written_off_at timestamp,
    written_off_by_name varchar(255),
    notes text,
    is_deleted boolean DEFAULT FALSE,
    metadata jsonb DEFAULT '{}'::jsonb,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS inventory.scrap_attachments (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    scrap_request_id bigint NOT NULL,
    file_name varchar(255),
    file_reference text,
    file_type varchar(100),
    file_size_bytes bigint,
    uploaded_at timestamp DEFAULT CURRENT_TIMESTAMP,
    uploaded_by_name varchar(255),
    metadata jsonb DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS inventory.inventory_variance_cases (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    variance_no varchar(100),
    stock_count_id bigint,
    stock_count_no varchar(100),
    stock_count_item_id bigint,
    item_id bigint,
    item_code varchar(100),
    item_name varchar(255),
    warehouse_id bigint,
    warehouse_name varchar(255),
    system_quantity numeric(18, 4) DEFAULT 0,
    physical_quantity numeric(18, 4) DEFAULT 0,
    variance_quantity numeric(18, 4) DEFAULT 0,
    unit_cost numeric(18, 4) DEFAULT 0,
    financial_impact numeric(18, 2) DEFAULT 0,
    status varchar(50) DEFAULT 'pending',
    approval_status varchar(50) DEFAULT 'pending',
    resolution_action varchar(100),
    resolution_notes text,
    ai_analysis jsonb DEFAULT '[]'::jsonb,
    detected_at timestamp DEFAULT CURRENT_TIMESTAMP,
    approved_at timestamp,
    approved_by_name varchar(255),
    adjusted_at timestamp,
    adjusted_by_name varchar(255),
    is_deleted boolean DEFAULT FALSE,
    metadata jsonb DEFAULT '{}'::jsonb,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS inventory.inventory_approval_requests (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    request_no varchar(100) NOT NULL,
    entity_type varchar(100) NOT NULL,
    entity_id bigint NOT NULL,
    entity_no varchar(100),
    approval_type varchar(100),
    title varchar(255),
    summary text,
    amount numeric(18, 2),
    quantity numeric(18, 4),
    priority varchar(50) DEFAULT 'normal',
    status varchar(50) DEFAULT 'pending',
    submitted_by_name varchar(255),
    submitted_at timestamp DEFAULT CURRENT_TIMESTAMP,
    due_at timestamp,
    approved_at timestamp,
    approved_by_name varchar(255),
    rejected_at timestamp,
    rejected_by_name varchar(255),
    rejection_reason text,
    ai_suggestions jsonb DEFAULT '[]'::jsonb,
    notes text,
    is_deleted boolean DEFAULT FALSE,
    metadata jsonb DEFAULT '{}'::jsonb,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS inventory.inventory_ai_insights (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    insight_no varchar(100),
    insight_type varchar(100),
    module_area varchar(100),
    entity_type varchar(100),
    entity_id bigint,
    title varchar(255) NOT NULL,
    description text,
    confidence_percent numeric(5, 2),
    confidence_label varchar(50),
    impact_label varchar(100),
    recommendation text,
    status varchar(50) DEFAULT 'active',
    metadata jsonb DEFAULT '{}'::jsonb,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS inventory.inventory_document_links (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    item_id bigint,
    item_code varchar(100),
    warehouse_id bigint,
    warehouse_name varchar(255),
    document_type varchar(100) NOT NULL,
    document_id bigint,
    document_no varchar(100),
    document_date date,
    document_status varchar(50),
    quantity numeric(18, 4),
    amount numeric(18, 2),
    metadata jsonb DEFAULT '{}'::jsonb,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_inventory_categories_tenant_name_parent
    ON inventory.categories (tenant_id, category_name, COALESCE(parent_category_id, 0))
    WHERE COALESCE(is_deleted, FALSE) = FALSE;

CREATE UNIQUE INDEX IF NOT EXISTS uq_inventory_brands_tenant_name
    ON inventory.brands (tenant_id, brand_name)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;

CREATE UNIQUE INDEX IF NOT EXISTS uq_inventory_reorder_rules_item_warehouse
    ON inventory.reorder_rules (tenant_id, item_code, warehouse_name)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;

CREATE UNIQUE INDEX IF NOT EXISTS uq_inventory_stock_transfers_no
    ON inventory.stock_transfers (tenant_id, transfer_no)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;

CREATE UNIQUE INDEX IF NOT EXISTS uq_inventory_scrap_requests_no
    ON inventory.scrap_requests (tenant_id, scrap_no)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;

CREATE UNIQUE INDEX IF NOT EXISTS uq_inventory_approval_requests_no
    ON inventory.inventory_approval_requests (tenant_id, request_no)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;

CREATE INDEX IF NOT EXISTS idx_inventory_stock_balances_item_warehouse
    ON inventory.stock_balances (tenant_id, item_code, warehouse_name);

CREATE INDEX IF NOT EXISTS idx_inventory_stock_movements_item
    ON inventory.stock_movements (tenant_id, item_code, occurred_at DESC);

CREATE INDEX IF NOT EXISTS idx_inventory_transfer_items_transfer
    ON inventory.stock_transfer_items (tenant_id, stock_transfer_id);

CREATE INDEX IF NOT EXISTS idx_inventory_variance_cases_status
    ON inventory.inventory_variance_cases (tenant_id, status, approval_status);

CREATE INDEX IF NOT EXISTS idx_inventory_ai_insights_entity
    ON inventory.inventory_ai_insights (tenant_id, entity_type, entity_id);

COMMIT;