-- Quick check for required indexes
-- This will show which indexes exist and which are missing

SELECT 
    'task' as table_name,
    'idx_task_customer_store_week' as index_name,
    CASE WHEN EXISTS (
        SELECT 1 FROM pg_indexes 
        WHERE tablename = 'task' 
        AND indexname = 'idx_task_customer_store_week'
    ) THEN 'EXISTS' ELSE 'MISSING' END as status
UNION ALL
SELECT 
    'task',
    'idx_task_customer_category',
    CASE WHEN EXISTS (
        SELECT 1 FROM pg_indexes 
        WHERE tablename = 'task' 
        AND indexname = 'idx_task_customer_category'
    ) THEN 'EXISTS' ELSE 'MISSING' END
UNION ALL
SELECT 
    'task',
    'idx_task_name_status',
    CASE WHEN EXISTS (
        SELECT 1 FROM pg_indexes 
        WHERE tablename = 'task' 
        AND indexname = 'idx_task_name_status'
    ) THEN 'EXISTS' ELSE 'MISSING' END
UNION ALL
SELECT 
    'sjreport',
    'idx_sjreport_customer_store_week',
    CASE WHEN EXISTS (
        SELECT 1 FROM pg_indexes 
        WHERE tablename = 'sjreport' 
        AND indexname = 'idx_sjreport_customer_store_week'
    ) THEN 'EXISTS' ELSE 'MISSING' END
UNION ALL
SELECT 
    'sjreport',
    'idx_sjreport_customer_category',
    CASE WHEN EXISTS (
        SELECT 1 FROM pg_indexes 
        WHERE tablename = 'sjreport' 
        AND indexname = 'idx_sjreport_customer_category'
    ) THEN 'EXISTS' ELSE 'MISSING' END
UNION ALL
SELECT 
    'sjreport',
    'idx_sjreport_dedup',
    CASE WHEN EXISTS (
        SELECT 1 FROM pg_indexes 
        WHERE tablename = 'sjreport' 
        AND indexname = 'idx_sjreport_dedup'
    ) THEN 'EXISTS' ELSE 'MISSING' END
UNION ALL
SELECT 
    'store_user_link',
    'idx_store_user_link_store_customer',
    CASE WHEN EXISTS (
        SELECT 1 FROM pg_indexes 
        WHERE tablename = 'store_user_link' 
        AND indexname = 'idx_store_user_link_store_customer'
    ) THEN 'EXISTS' ELSE 'MISSING' END
ORDER BY table_name, index_name;

-- Also show similar indexes that might already exist (partial matches)
SELECT 
    '=== SIMILAR EXISTING INDEXES (might be useful) ===' as info;

SELECT 
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE (
    (tablename = 'task' AND (
        indexname LIKE '%customer%store%week%' OR
        indexname LIKE '%customer%category%' OR
        indexname LIKE '%store%week%'
    ))
    OR
    (tablename = 'sjreport' AND (
        indexname LIKE '%customer%store%week%' OR
        indexname LIKE '%customer%category%' OR
        indexname LIKE '%store%week%'
    ))
    OR
    (tablename = 'store_user_link' AND (
        indexname LIKE '%store%customer%'
    ))
)
AND schemaname = 'public'
ORDER BY tablename, indexname;

