-- ============================================================================
-- EduAdvise CRM — Phase 7: performance indexes
-- Trigram indexes for name/phone/email search; FK indexes that were missing.
-- ============================================================================

-- Search (ilike) acceleration
create index if not exists leads_full_name_trgm on public.leads using gin (full_name gin_trgm_ops);
create index if not exists leads_phone_trgm on public.leads using gin (phone gin_trgm_ops);
create index if not exists leads_email_trgm on public.leads using gin (email gin_trgm_ops);
create index if not exists student_applications_name_trgm on public.student_applications using gin (applicant_name gin_trgm_ops);

-- Foreign-key / filter columns commonly joined or filtered
create index if not exists leads_source_idx on public.leads (lead_source_id);
create index if not exists leads_created_by_idx on public.leads (created_by);
create index if not exists student_applications_counselor_idx on public.student_applications (counselor_id);
create index if not exists student_applications_institute_idx on public.student_applications (institute_id);
create index if not exists student_applications_course_idx on public.student_applications (course_id);
create index if not exists fee_payments_created_idx on public.fee_payments (created_at desc);
create index if not exists university_commissions_status_idx on public.university_commissions (status);
create index if not exists courses_program_level_idx on public.courses (program_level_id);
create index if not exists campuses_active_idx on public.campuses (is_active);