-- Migration 0017: add_performance_indexes
-- Auto-generated from database/schema.sql by database/build_migrations.php
-- Do not hand-edit this file — edit schema.sql and re-run that script instead.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- SECTION 17 — PERFORMANCE INDEXES (Phase 6)
-- Every index below targets a column used in a date-range WHERE clause
-- that had NO index at all — confirmed via EXPLAIN during Phase 6's
-- optimization pass, not added speculatively. Each was a full table
-- scan (`type: ALL`) before this migration on the exact query
-- ReportService/DashboardController runs. Composite indexes are
-- ordered filter-column-first, range-column-second, matching how
-- MySQL actually uses a compound index for "WHERE business_line_id = ?
-- AND created_at > ?" style queries.
-- =====================================================================

ALTER TABLE payments      ADD KEY idx_payments_paid_at (paid_at);
ALTER TABLE conversations ADD KEY idx_conversations_started_at (started_at);
ALTER TABLE leads         ADD KEY idx_leads_business_line_created (business_line_id, created_at);
ALTER TABLE customers     ADD KEY idx_customers_business_line_since (business_line_id, customer_since);
ALTER TABLE appointments  ADD KEY idx_appointments_business_line_scheduled (business_line_id, scheduled_start);
ALTER TABLE campaigns     ADD KEY idx_campaigns_sent_at (sent_at);
ALTER TABLE campaigns     ADD KEY idx_campaigns_created_at (created_at);
ALTER TABLE automation_runs ADD KEY idx_automation_runs_started_at (started_at);
ALTER TABLE email_queue   ADD KEY idx_email_queue_created_at (created_at);


SET FOREIGN_KEY_CHECKS = 1;
