-- =====================================================================
-- migrate-scale-perf.sql
-- Performance + scalability migration for the multi-tenant Collection
-- Dashboard (supports 100+ clients sharing the same data tables).
--
-- Problem this fixes:
--   The Facility Mapping page ran COUNT(DISTINCT facility) on every load.
--   With ~800k rows the optimizer used the (report_id, visit_id) index and
--   did ~387k random primary-key lookups to read `facility` -> 134 SECONDS.
--   Reports browser + upload pages also scanned collection_raw live.
--
-- Strategy:
--   1) Covering index (report_id, facility) -> COUNT(DISTINCT facility)
--      becomes an index-only scan (134s -> ~0.5s) when hinted.
--   2) Index (report_id, id) -> per-client "newest 200" browse stays instant
--      regardless of how many other clients' rows sit above it in the PK.
--   3) collection_uploads.meta_json -> cache derived KPI counts (unique
--      visits / facilities) computed ONCE at ingest, so page loads do zero
--      heavy aggregation. Combined with valid_rows (= live row count, since
--      every upload fully replaces the data), all KPI tiles read O(1).
--
-- Safe to run once. Re-running will error on duplicate key / column names;
-- that is expected — it means the migration is already applied.
-- =====================================================================

ALTER TABLE facility_mappings ADD INDEX idx_fac_map_report_facility (report_id, facility);
ALTER TABLE facility_mappings ADD INDEX idx_fac_map_report_id       (report_id, id);

ALTER TABLE collection_uploads ADD COLUMN meta_json TEXT NULL AFTER note;
