-- =====================================================================
-- Caresoft Imaging — migration 002
-- P3: technician acquisition, repeat/reject analysis, contrast register.
-- Safe to run on an existing database created from schema.sql.
-- =====================================================================
USE `caresoft_imaging`;

-- One row per rejected or repeated exposure. A count alone is not enough:
-- NABH assessors look for reason codes, trends, and action taken.
CREATE TABLE IF NOT EXISTS img_repeat_reject (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id       INT UNSIGNED NOT NULL,
  order_id        BIGINT UNSIGNED NOT NULL,
  acquisition_id  BIGINT UNSIGNED NULL,
  modality_id     INT UNSIGNED NULL,
  technician_id   INT UNSIGNED NULL,
  reason_code     VARCHAR(32)  NOT NULL COMMENT 'positioning, motion, exposure_over, ...',
  view_projection VARCHAR(80)  NULL COMMENT 'which view had to be repeated',
  reason_note     VARCHAR(300) NULL,
  action_taken    VARCHAR(300) NULL COMMENT 'what was done about it',
  occurred_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_rr_tenant (tenant_id, occurred_at),
  KEY ix_rr_reason (tenant_id, reason_code),
  KEY ix_rr_order  (order_id),
  KEY ix_rr_tech   (technician_id, occurred_at),
  CONSTRAINT fk_rr_tenant FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE,
  CONSTRAINT fk_rr_order  FOREIGN KEY (order_id)  REFERENCES img_order(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Contrast reaction detail on the acquisition record.
ALTER TABLE img_acquisition
  ADD COLUMN contrast_route VARCHAR(40) NULL AFTER contrast_batch,
  ADD COLUMN reaction_severity ENUM('none','mild','moderate','severe') NOT NULL DEFAULT 'none' AFTER reaction_noted,
  ADD COLUMN reaction_managed_by VARCHAR(140) NULL AFTER reaction_severity,
  ADD COLUMN consumables VARCHAR(300) NULL AFTER film_used,
  ADD COLUMN modality_id INT UNSIGNED NULL AFTER order_id,
  ADD COLUMN patient_arrived_at DATETIME NULL AFTER modality_id,
  ADD KEY ix_acq_tenant_time (started_at);