-- Database Optimization Script
-- Date: January 4, 2026
-- Purpose: Remove redundant indexes, optimize queries, improve performance

-- ============================================
-- STEP 1: Remove Redundant Indexes
-- ============================================

-- Remove duplicate index on profile_song_keys
-- Keep: uq_profile_song_key (unique constraint)
-- Keep: idx_profile_song_keys (for lookups)
-- Remove: idx_profile_keys (exact duplicate)
DROP INDEX IF EXISTS idx_profile_keys;

-- Remove redundant single-column index on plan_songs
-- Keep: idx_plan_songs_order (composite index: plan_id, order_index)
-- Remove: idx_plan_songs_plan (redundant - composite index handles this)
DROP INDEX IF EXISTS idx_plan_songs_plan;

-- ============================================
-- STEP 2: Optimize Low-Cardinality Indexes
-- ============================================

-- Replace full index on users.active with partial index
-- Most users are active, so only index the exceptions
DROP INDEX IF EXISTS idx_user_active;
CREATE INDEX IF NOT EXISTS idx_user_inactive ON users (id) WHERE active = false;

-- ============================================
-- STEP 3: Add Composite Indexes for Common Queries
-- ============================================

-- Optimize query: "find plans by profile within date range"
CREATE INDEX IF NOT EXISTS idx_plan_profile_date ON plans (profile_id, date) 
WHERE profile_id IS NOT NULL;

-- Add index for plan song lookups (if not exists)
CREATE INDEX IF NOT EXISTS idx_plan_songs_song ON plan_songs (song_id);

-- Add index for profile song reverse lookups (if not exists)
CREATE INDEX IF NOT EXISTS idx_profile_songs_song ON profile_songs (song_id);

-- ============================================
-- STEP 4: Verify Index Effectiveness
-- ============================================

-- Show all indexes after cleanup
SELECT 
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes 
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

-- Show index sizes
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

-- ============================================
-- STEP 5: Analyze Tables for Query Planner
-- ============================================

-- Update statistics for query planner optimization
ANALYZE users;
ANALYZE profiles;
ANALYZE songs;
ANALYZE plans;
ANALYZE plan_songs;
ANALYZE profile_songs;
ANALYZE profile_song_keys;
ANALYZE biometric_credentials;

-- ============================================
-- VERIFICATION QUERIES
-- ============================================

-- Check for unused indexes (run after a week of production use)
-- SELECT 
--     schemaname,
--     tablename,
--     indexname,
--     idx_scan as scans,
--     pg_size_pretty(pg_relation_size(indexrelid)) as size
-- FROM pg_stat_user_indexes
-- WHERE schemaname = 'public'
-- AND idx_scan = 0
-- AND indexrelid IS NOT NULL
-- ORDER BY pg_relation_size(indexrelid) DESC;

-- Check for missing indexes on foreign keys
-- SELECT 
--     c.conname AS constraint_name,
--     t.relname AS table_name,
--     ARRAY_AGG(a.attname ORDER BY u.attposition) AS columns
-- FROM pg_constraint c
-- JOIN pg_class t ON c.conrelid = t.oid
-- JOIN pg_namespace n ON t.relnamespace = n.oid
-- JOIN LATERAL UNNEST(c.conkey) WITH ORDINALITY AS u(attnum, attposition) ON TRUE
-- JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = u.attnum
-- WHERE c.contype = 'f'
-- AND n.nspname = 'public'
-- GROUP BY c.conname, t.relname;

COMMIT;