-- =====================================================================
-- Caresoft Imaging — migration 007
-- P9: indexes and a couple of columns the management reports need.
-- No new tables: analytics reads the operational data rather than
-- duplicating it, so a figure on a dashboard can always be drilled into.
-- =====================================================================
USE `caresoft_imaging`;

-- Timestamps the turnaround report groups on.
ALTER TABLE img_order
  ADD KEY ix_ord_tat (tenant_id, created_at, status),
  ADD KEY ix_ord_ref (tenant_id, referring_doctor),
  ADD KEY ix_ord_mod (tenant_id, modality_id, created_at);

ALTER TABLE img_report
  ADD KEY ix_rep_verified (tenant_id, verified_at),
  ADD KEY ix_rep_author (authored_by, authored_at);

-- What the study was actually charged, captured at order time so a later
-- price-list change does not rewrite last year's revenue.
ALTER TABLE img_order
  ADD COLUMN charged_amount DECIMAL(12,2) NULL AFTER billing_status;