-- Migration: inventory_history_indexes.sql
-- Purpose: Add indexes to support the new /items/history route
-- Date: 2026-01-09
-- 
-- Run this script once on the production database.
-- All indexes use IF NOT EXISTS for safe re-runs.

-- ============================================================================
-- INVENTORY_ITEMS INDEXES
-- ============================================================================

-- Index for tracing exchange_item relationships (forward chain traversal)
-- Used when JOINing to get exchange item details
CREATE INDEX IF NOT EXISTS idx_inventory_items_exchange_item 
  ON inventory_items(exchange_item) 
  WHERE exchange_item IS NOT NULL;

-- Index for filtering "gone" items (sold=99, discarded=98)
-- Used in SKU history queries to identify items no longer in stock
CREATE INDEX IF NOT EXISTS idx_inventory_items_condition_gone 
  ON inventory_items(condition) 
  WHERE condition >= 98;

-- Composite index for SKU history queries
-- Efficiently retrieves all items for a SKU grouped by condition/outcome
CREATE INDEX IF NOT EXISTS idx_inventory_items_sku_condition 
  ON inventory_items(sku, condition);

-- ============================================================================
-- ORDERS INDEXES
-- ============================================================================

-- Primary lookup index for orders (JOIN performance)
CREATE INDEX IF NOT EXISTS idx_orders_order_id 
  ON orders(order_id);

-- Customer order lookups
CREATE INDEX IF NOT EXISTS idx_orders_customer_id 
  ON orders(customer_id);

-- Company order lookups
CREATE INDEX IF NOT EXISTS idx_orders_company_id 
  ON orders(company_id);

-- ============================================================================
-- LINKS INDEXES (for cost source queries)
-- ============================================================================

-- For joining links to cost_sources (link_item_id = source_id)
CREATE INDEX IF NOT EXISTS idx_links_link_item_id 
  ON links(link_item_id);

-- For joining links to expenses (item_id = expense_id)
CREATE INDEX IF NOT EXISTS idx_links_item_id 
  ON links(item_id);

-- ============================================================================
-- VERIFICATION QUERIES (run after migration to confirm)
-- ============================================================================

-- Uncomment to verify indexes were created:
-- SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'inventory_items';
-- SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';
-- SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'links';
