-- Check if indexes for process_store_category_data optimization already exist
-- Run this script to see which indexes exist and which need to be created

\echo '========================================'
\echo 'CHECKING INDEXES FOR OPTIMIZATION'
\echo '========================================'
\echo ''

-- Check indexes on task table
\echo '=== TASK TABLE INDEXES ==='
SELECT 
    CASE 
        WHEN indexname IS NOT NULL THEN 'EXISTS: ' || indexname
        ELSE 'MISSING'
    END as status,
    indexname,
    indexdef
FROM (
    SELECT 'idx_task_customer_store_week' as check_name
    UNION ALL SELECT 'idx_task_customer_category'
    UNION ALL SELECT 'idx_task_name_status'
) checks
LEFT JOIN pg_indexes pi ON 
    pi.tablename = 'task' 
    AND pi.indexname = checks.check_name
ORDER BY check_name;

\echo ''
\echo 'All existing indexes on task table:'
SELECT 
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'task'
  AND schemaname = 'public'
ORDER BY indexname;

\echo ''
\echo '=== SJREPORT TABLE INDEXES ==='
SELECT 
    CASE 
        WHEN indexname IS NOT NULL THEN 'EXISTS: ' || indexname
        ELSE 'MISSING'
    END as status,
    indexname,
    indexdef
FROM (
    SELECT 'idx_sjreport_customer_store_week' as check_name
    UNION ALL SELECT 'idx_sjreport_customer_category'
    UNION ALL SELECT 'idx_sjreport_dedup'
) checks
LEFT JOIN pg_indexes pi ON 
    pi.tablename = 'sjreport' 
    AND pi.indexname = checks.check_name
ORDER BY check_name;

\echo ''
\echo 'All existing indexes on sjreport table:'
SELECT 
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'sjreport'
  AND schemaname = 'public'
ORDER BY indexname;

\echo ''
\echo '=== STORE_USER_LINK TABLE INDEXES ==='
SELECT 
    CASE 
        WHEN indexname IS NOT NULL THEN 'EXISTS: ' || indexname
        ELSE 'MISSING'
    END as status,
    indexname,
    indexdef
FROM (
    SELECT 'idx_store_user_link_store_customer' as check_name
) checks
LEFT JOIN pg_indexes pi ON 
    pi.tablename = 'store_user_link' 
    AND pi.indexname = checks.check_name
ORDER BY check_name;

\echo ''
\echo 'All existing indexes on store_user_link table:'
SELECT 
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'store_user_link'
  AND schemaname = 'public'
ORDER BY indexname;

\echo ''
\echo '========================================'
\echo 'SUMMARY: Check the output above'
\echo 'If any indexes show "MISSING", run:'
\echo '  \i database/functions/create_indexes_for_process_store_category_data.sql'
\echo '========================================'
