-- ============================================================
-- Covering indexes for the Collection drill-down + scorecards.
-- These make the per-dimension aggregations (Carrier/CPT/Provider/Aging/Summary
-- > Year > Month) index-only, so they stay sub-second at scale.
-- Added locally during the build; run ONCE on the live DB.
--
-- Percona / MySQL 5.7 has NO "ADD INDEX IF NOT EXISTS", so each index is its own
-- statement created ONLINE (ALGORITHM=INPLACE, LOCK=NONE) — the table stays readable
-- and writable during creation. If a line errors with "Duplicate key name", that
-- index already exists: just skip that one line and continue.
--
--   phpMyAdmin: select your DB, paste, Go.   CLI: mysql -u <u> -p <db> < this.sql
-- ============================================================

ALTER TABLE `collection_raw`
  ADD INDEX `idx_drill_carrier`
  (`report_id`,`carrier1_name`,`dos_year`,`dos_month`,`visit_id`,`primary_ins_paid`,`outstanding`,`charge_amount`,`amount_allowed`,`days_to_first_payment`),
  ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE `collection_raw`
  ADD INDEX `idx_drill_cpt`
  (`report_id`,`charge_code`,`dos_year`,`dos_month`,`units`,`charge_amount`,`expected_payment`,`primary_ins_paid`,`secondary_ins_paid`,`tertiary_ins_paid`),
  ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE `collection_raw`
  ADD INDEX `idx_drill_provider`
  (`report_id`,`provider_name`,`dos_year`,`dos_month`,`visit_id`,`charge_amount`,`expected_payment`,`primary_ins_paid`,`secondary_ins_paid`,`tertiary_ins_paid`,`outstanding`,`days_to_first_payment`),
  ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE `collection_raw`
  ADD INDEX `idx_drill_aging`
  (`report_id`,`dos`,`dos_year`,`dos_month`,`visit_id`,`outstanding`,`charge_amount`,`amount_allowed`),
  ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE `collection_raw`
  ADD INDEX `idx_drill_summary`
  (`report_id`,`dos_year`,`dos_month`,`facility`,`visit_id`,`primary_ins_paid`,`secondary_ins_paid`,`tertiary_ins_paid`,`patient_payment_amount`,`charge_amount`,`expected_payment`,`outstanding`),
  ALGORITHM=INPLACE, LOCK=NONE;

-- Facility-mapping lookup indexes (skip any that already exist).
ALTER TABLE `facility_mappings`
  ADD INDEX `idx_fac_map_report_facility` (`report_id`,`facility`),
  ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE `facility_mappings`
  ADD INDEX `idx_fac_map_report_id` (`report_id`,`id`),
  ALGORITHM=INPLACE, LOCK=NONE;
