-- =====================================================================
-- Caresoft Imaging — migration 010
-- P12: dictation macros, hanging protocols, and AI advisory results.
-- Safe to run on a database created from schema.sql + migrations 002-009.
-- =====================================================================
USE `caresoft_imaging`;

-- Short triggers a radiologist types or says, expanded into full text.
CREATE TABLE IF NOT EXISTS img_dictation_macro (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  user_id       INT UNSIGNED NULL COMMENT 'NULL = shared across the department',
  trigger_word  VARCHAR(60) NOT NULL,
  expansion     TEXT NOT NULL,
  target_field  ENUM('any','findings','impression','technique','recommendation') NOT NULL DEFAULT 'any',
  use_count     INT NOT NULL DEFAULT 0,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_macro (tenant_id, user_id, is_active),
  CONSTRAINT fk_macro_ten FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- How a radiologist wants a study laid out when it opens.
CREATE TABLE IF NOT EXISTS img_hanging_protocol (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  user_id       INT UNSIGNED NULL COMMENT 'NULL = department default',
  name          VARCHAR(140) NOT NULL,
  modality_type VARCHAR(8) NULL,
  body_part     VARCHAR(80) NULL,
  layout        VARCHAR(12) NOT NULL DEFAULT '1x1' COMMENT '1x1, 1x2, 2x2, 1x3, 2x3',
  series_rules  TEXT NULL COMMENT 'one rule per line: pane|match text',
  window_preset VARCHAR(40) NULL COMMENT 'name of a preset below',
  window_centre INT NULL,
  window_width  INT NULL,
  show_priors   TINYINT(1) NOT NULL DEFAULT 1,
  sort_order    INT NOT NULL DEFAULT 100,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  KEY ix_hp (tenant_id, user_id, modality_type, is_active),
  CONSTRAINT fk_hp_ten FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- An AI service the hospital has chosen to connect.
CREATE TABLE IF NOT EXISTS img_ai_service (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  name          VARCHAR(140) NOT NULL,
  vendor        VARCHAR(140) NULL,
  endpoint      VARCHAR(255) NULL,
  api_key_enc   VARBINARY(512) NULL,
  modality_type VARCHAR(8) NULL,
  body_part     VARCHAR(80) NULL,
  mode          ENUM('advisory','triage') NOT NULL DEFAULT 'advisory'
                COMMENT 'advisory shows a note; triage may also raise worklist priority',
  regulatory_ref VARCHAR(160) NULL COMMENT 'CDSCO licence, CE mark or FDA clearance the vendor claims',
  regulatory_note TEXT NULL,
  is_enabled    TINYINT(1) NOT NULL DEFAULT 0,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_ai (tenant_id, is_enabled),
  CONSTRAINT fk_ai_ten FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- What a service said about one study, and whether the radiologist agreed.
-- The agreement column is the point: an AI nobody checks is an AI nobody
-- can defend.
CREATE TABLE IF NOT EXISTS img_ai_result (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id      INT UNSIGNED NOT NULL,
  service_id     INT UNSIGNED NOT NULL,
  study_id       BIGINT UNSIGNED NULL,
  order_id       BIGINT UNSIGNED NOT NULL,
  status         ENUM('queued','sent','returned','failed','skipped') NOT NULL DEFAULT 'queued',
  finding_code   VARCHAR(60) NULL,
  finding_text   VARCHAR(500) NULL,
  confidence     DECIMAL(5,4) NULL,
  severity       ENUM('none','low','moderate','high') NULL,
  is_positive    TINYINT(1) NULL COMMENT 'did the service flag anything at all',
  raw_hash       CHAR(64) NULL,
  requested_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  returned_at    DATETIME NULL,
  error_text     VARCHAR(255) NULL,
  seen_by        INT UNSIGNED NULL,
  seen_at        DATETIME NULL,
  agreement      ENUM('agreed','disagreed','partly','not_assessed') NOT NULL DEFAULT 'not_assessed',
  agreement_note VARCHAR(500) NULL,
  KEY ix_air (tenant_id, status, requested_at),
  KEY ix_air_order (order_id),
  CONSTRAINT fk_air_service FOREIGN KEY (service_id) REFERENCES img_ai_service(id) ON DELETE CASCADE
) ENGINE=InnoDB;

ALTER TABLE app_user
  ADD COLUMN dictation_lang VARCHAR(16) NULL DEFAULT 'en-IN' AFTER signature_path,
  ADD COLUMN dictation_autopunct TINYINT(1) NOT NULL DEFAULT 1 AFTER dictation_lang;