-- =====================================================================
-- Caresoft Imaging — migration 004
-- P6: critical findings — notification, acknowledgement, escalation.
-- Safe to run on a database created from schema.sql + migrations 002, 003.
-- =====================================================================
USE `caresoft_imaging`;

ALTER TABLE img_critical_finding
  ADD COLUMN order_id         BIGINT UNSIGNED NULL AFTER report_id,
  ADD COLUMN category         VARCHAR(48) NULL AFTER finding_text,
  ADD COLUMN severity         ENUM('critical','urgent') NOT NULL DEFAULT 'critical' AFTER category,
  ADD COLUMN ack_token        VARCHAR(64) NULL AFTER notified_at,
  ADD COLUMN ack_expires_at   DATETIME NULL AFTER ack_token,
  ADD COLUMN acknowledged_role VARCHAR(80) NULL AFTER acknowledged_by,
  ADD COLUMN acknowledged_ip  VARCHAR(45) NULL AFTER acknowledged_role,
  ADD COLUMN ack_note         VARCHAR(500) NULL AFTER acknowledged_ip,
  ADD COLUMN escalation_level INT NOT NULL DEFAULT 0 AFTER escalated_at,
  ADD COLUMN next_action_at   DATETIME NULL AFTER escalation_level,
  ADD COLUMN closed_at        DATETIME NULL AFTER next_action_at,
  ADD COLUMN closed_by        INT UNSIGNED NULL AFTER closed_at,
  ADD COLUMN closure_note     VARCHAR(500) NULL AFTER closed_by,
  ADD UNIQUE KEY uq_ack_token (ack_token),
  ADD KEY ix_cf_due (tenant_id, acknowledged_at, next_action_at);

-- Every attempt to reach somebody, kept whether it worked or not.
-- "We called" is not evidence; this is.
CREATE TABLE IF NOT EXISTS img_critical_notify (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  finding_id     BIGINT UNSIGNED NOT NULL,
  level          INT NOT NULL DEFAULT 0 COMMENT '0 = first call, 1+ = escalation steps',
  channel        ENUM('whatsapp','email','sms','phone','in_person') NOT NULL,
  recipient_name VARCHAR(140) NULL,
  recipient_role VARCHAR(80)  NULL,
  target         VARCHAR(200) NULL,
  sent_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status         ENUM('sent','failed','manual') NOT NULL DEFAULT 'sent',
  error_text     VARCHAR(255) NULL,
  logged_by      INT UNSIGNED NULL COMMENT 'set when a person records a phone call by hand',
  KEY ix_cn (finding_id, level),
  CONSTRAINT fk_cn_finding FOREIGN KEY (finding_id) REFERENCES img_critical_finding(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Who to reach, in what order, when nobody answers.
CREATE TABLE IF NOT EXISTS img_escalation_contact (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id  INT UNSIGNED NOT NULL,
  level      INT NOT NULL DEFAULT 1 COMMENT '1 = first escalation, 2 = next, ...',
  name       VARCHAR(140) NOT NULL,
  role       VARCHAR(80)  NULL,
  phone      VARCHAR(40)  NULL,
  email      VARCHAR(160) NULL,
  is_active  TINYINT(1) NOT NULL DEFAULT 1,
  KEY ix_esc (tenant_id, level, is_active),
  CONSTRAINT fk_esc_tenant FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;