-- Migration: Add indexes for AI context performance optimization
-- Related to issue #46: Integrate exclusions with AI context
-- Created: 2025-10-31

-- Add indexes on store_data_exclusions for faster lookups
-- These indexes optimize the JOIN queries when building AI context

-- Index for querying exclusions by store_id and data_type (most common query pattern)
CREATE INDEX IF NOT EXISTS idx_store_data_exclusions_store_type
ON store_data_exclusions(store_id, data_type);

-- Index for querying exclusions by store_id, data_type, and is_enabled
-- This helps when we want to filter only enabled/disabled items
CREATE INDEX IF NOT EXISTS idx_store_data_exclusions_store_type_enabled
ON store_data_exclusions(store_id, data_type, is_enabled);

-- Index for faster lookup by data_id within a store
CREATE INDEX IF NOT EXISTS idx_store_data_exclusions_store_data_id
ON store_data_exclusions(store_id, data_id);

-- Add indexes on cache tables for better JOIN performance with exclusions
-- These optimize queries that fetch cache items and join with exclusions

-- WooCommerce indexes
CREATE INDEX IF NOT EXISTS idx_woocommerce_products_store_id
ON woocommerce_products_cache(store_id);

CREATE INDEX IF NOT EXISTS idx_woocommerce_orders_store_id
ON woocommerce_orders_cache(store_id);

CREATE INDEX IF NOT EXISTS idx_woocommerce_customers_store_id
ON woocommerce_customers_cache(store_id);

-- Shopify indexes (tables not yet created in production, skip for now)
-- CREATE INDEX IF NOT EXISTS idx_shopify_products_store_id
-- ON shopify_products_cache(store_id);

-- CREATE INDEX IF NOT EXISTS idx_shopify_orders_store_id
-- ON shopify_orders_cache(store_id);

-- CREATE INDEX IF NOT EXISTS idx_shopify_customers_store_id
-- ON shopify_customers_cache(store_id);

-- ShopRenter indexes
CREATE INDEX IF NOT EXISTS idx_shoprenter_products_store_id
ON shoprenter_products_cache(store_id);

CREATE INDEX IF NOT EXISTS idx_shoprenter_orders_store_id
ON shoprenter_orders_cache(store_id);

CREATE INDEX IF NOT EXISTS idx_shoprenter_customers_store_id
ON shoprenter_customers_cache(store_id);

-- Add composite indexes for faster filtering with created_at ordering
-- These help when fetching recent items for AI context

CREATE INDEX IF NOT EXISTS idx_woocommerce_products_store_created
ON woocommerce_products_cache(store_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_woocommerce_orders_store_created
ON woocommerce_orders_cache(store_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_woocommerce_customers_store_created
ON woocommerce_customers_cache(store_id, created_at DESC);

-- CREATE INDEX IF NOT EXISTS idx_shopify_products_store_created
-- ON shopify_products_cache(store_id, created_at DESC);

-- CREATE INDEX IF NOT EXISTS idx_shopify_orders_store_created
-- ON shopify_orders_cache(store_id, created_at DESC);

-- CREATE INDEX IF NOT EXISTS idx_shopify_customers_store_created
-- ON shopify_customers_cache(store_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_shoprenter_products_store_created
ON shoprenter_products_cache(store_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_shoprenter_orders_store_created
ON shoprenter_orders_cache(store_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_shoprenter_customers_store_created
ON shoprenter_customers_cache(store_id, created_at DESC);

-- Add comments for documentation
COMMENT ON INDEX idx_store_data_exclusions_store_type IS
  'Optimizes queries that fetch exclusions by store and data type for AI context building';

COMMENT ON INDEX idx_store_data_exclusions_store_type_enabled IS
  'Optimizes queries that filter exclusions by enabled status for AI context building';

COMMENT ON INDEX idx_store_data_exclusions_store_data_id IS
  'Optimizes lookups of specific data items within a store for AI context building';
