-- Create the large sjreport deduplication index
-- This index is OPTIONAL and may take 10-30+ minutes to create on a large table
-- Run this during off-peak hours if possible
-- 
-- Note: CONCURRENTLY option allows the index to be built without blocking writes,
-- but it takes longer and requires more resources

-- Option 1: Standard CREATE INDEX (faster but blocks writes)
-- Uncomment the line below if you want to run it now (may block table writes)
-- 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);

-- Option 2: CONCURRENTLY (slower but doesn't block writes) - RECOMMENDED for production
-- Uncomment the lines below to create the index without blocking writes
-- Note: CONCURRENTLY cannot be used with IF NOT EXISTS, so check first
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_indexes 
        WHERE tablename = 'sjreport' 
        AND indexname = 'idx_sjreport_dedup'
        AND schemaname = 'public'
    ) THEN
        CREATE INDEX CONCURRENTLY idx_sjreport_dedup 
          ON sjreport(customer_id, store_code, week_start_date, channel, store_name, category, product, variant, size, created_at DESC, id DESC);
    END IF;
END $$;

-- After index creation, analyze the table to update statistics
ANALYZE sjreport;

