-- Migration: Performance Indexes - Phase 2
-- Date: 2026-02-06
-- Description: Add additional indexes for common query patterns and performance optimization

-- 1. Composite indexes for filtered queries
-- Leads table - common filters
CREATE INDEX IF NOT EXISTS idx_leads_status_assigned_created ON leads(status, assigned_to, created_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_leads_email_lower ON leads(lower(email));
CREATE INDEX IF NOT EXISTS idx_leads_phone_clean ON leads(regexp_replace(phone, '[^0-9]', '', 'g'));
CREATE INDEX IF NOT EXISTS idx_leads_company_lower ON leads(lower(company));
CREATE INDEX IF NOT EXISTS idx_leads_source_status ON leads(source, status);

-- 2. Date range indexes for time-based queries
CREATE INDEX IF NOT EXISTS idx_leads_created_date ON leads(DATE(created_at));
CREATE INDEX IF NOT EXISTS idx_leads_updated_date ON leads(DATE(updated_at));
CREATE INDEX IF NOT EXISTS idx_leads_last_contact ON leads(last_contact_at DESC) WHERE last_contact_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_communications_created_date ON communications(DATE(created_at));
CREATE INDEX IF NOT EXISTS idx_platform_activity_activity_date ON platform_activity(DATE(activity_at));
CREATE INDEX IF NOT EXISTS idx_callbacks_scheduled_date ON callbacks(DATE(scheduled_at)) WHERE status = 'pending';

-- 3. JSONB indexes for metadata queries
CREATE INDEX IF NOT EXISTS idx_leads_metadata_gin ON leads USING GIN (metadata);
CREATE INDEX IF NOT EXISTS idx_communications_metadata_gin ON communications USING GIN (metadata);
CREATE INDEX IF NOT EXISTS idx_platform_activity_metadata_gin ON platform_activity USING GIN (metadata);

-- 4. Partial indexes for status-based queries
CREATE INDEX IF NOT EXISTS idx_leads_active ON leads(id) WHERE deleted_at IS NULL AND status != 'archived';
CREATE INDEX IF NOT EXISTS idx_callbacks_pending ON callbacks(lead_id, scheduled_at) WHERE status = 'pending';
CREATE INDEX IF NOT EXISTS idx_communications_recent ON communications(lead_id, created_at DESC) WHERE created_at > NOW() - INTERVAL '30 days';

-- 5. Communications table optimization
CREATE INDEX IF NOT EXISTS idx_communications_lead_type_created ON communications(lead_id, type, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_communications_type_direction ON communications(type, direction, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_communications_external_id ON communications(external_id) WHERE external_id IS NOT NULL;

-- 6. Daily metrics optimization
CREATE INDEX IF NOT EXISTS idx_daily_metrics_date ON daily_metrics(date DESC);
CREATE INDEX IF NOT EXISTS idx_daily_metrics_year_month ON daily_metrics(EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date));

-- 7. Sequence enrollments optimization
CREATE INDEX IF NOT EXISTS idx_sequence_enrollments_active ON sequence_enrollments(lead_id, sequence_id) WHERE status IN ('active', 'paused');
CREATE INDEX IF NOT EXISTS idx_sequence_enrollments_status_updated ON sequence_enrollments(status, updated_at DESC);

-- 8. Voice commands optimization
CREATE INDEX IF NOT EXISTS idx_voice_commands_pending ON voice_commands(created_at) WHERE processed = false;
CREATE INDEX IF NOT EXISTS idx_voice_commands_type ON voice_commands(command_type, created_at DESC);

-- 9. Add BRIN indexes for large temporal tables (if they grow very large)
-- BRIN indexes are smaller and faster for append-only time-series data
CREATE INDEX IF NOT EXISTS idx_leads_created_brin ON leads USING BRIN (created_at);
CREATE INDEX IF NOT EXISTS idx_communications_created_brin ON communications USING BRIN (created_at);

-- 10. Text search indexes
CREATE INDEX IF NOT EXISTS idx_leads_name_search ON leads USING gin(to_tsvector('english', coalesce(first_name, '') || ' ' || coalesce(last_name, '')));
CREATE INDEX IF NOT EXISTS idx_leads_company_search ON leads USING gin(to_tsvector('english', company));
CREATE INDEX IF NOT EXISTS idx_lead_notes_content_search ON lead_notes USING gin(to_tsvector('english', content));

-- Analyze tables to update statistics
ANALYZE leads;
ANALYZE communications;
ANALYZE platform_activity;
ANALYZE callbacks;
ANALYZE daily_metrics;
ANALYZE sequence_enrollments;
ANALYZE voice_commands;
ANALYZE lead_notes;