-- Create only the missing indexes for process_store_category_data optimization
-- Most indexes already exist! This script only creates the missing ones.

-- RECOMMENDED: Index for store_user_link UPDATE operations
-- This is the most important missing index
CREATE INDEX IF NOT EXISTS idx_store_user_link_store_customer 
  ON store_user_link(store_code, customer_id);

-- OPTIONAL: Partial index for task name filters (may help with aggregations)
-- This can improve performance of COUNT(*) FILTER operations
CREATE INDEX IF NOT EXISTS idx_task_name_status 
  ON task(name, status) 
  WHERE name IN (
    'NO SALES - FIX BALANCE/SHOW STOCK ON SHELF',
    'OOS - PLACE ORDER',
    'NEGATIVE STOCK - FIX STOCK BALANCE',
    'NO SALES 100 DAYS PLUS - FIX STOCK BALANCE',
    '<7 DAYS COVER - SEND FD CATEGORY PHOTO'
  );

-- OPTIONAL: Index for sjreport deduplication window function
-- This can improve performance of the row_number() window function
CREATE INDEX IF NOT EXISTS idx_sjreport_dedup 
  ON sjreport(customer_id, store_code, week_start_date, channel, store_name, category, product, variant, size, created_at DESC, id DESC);

-- Analyze tables to update statistics
ANALYZE store_user_link;
ANALYZE task;
ANALYZE sjreport;

