-- =====================================================================
-- Caresoft Imaging (RIS + PACS)  —  MySQL 8.0 schema
-- Charset utf8mb4 / collation utf8mb4_unicode_ci
-- NOTE: this database holds INDEX data only. Pixel data lives in Orthanc.
-- =====================================================================

SET NAMES utf8mb4;
SET time_zone = '+05:30';

CREATE DATABASE IF NOT EXISTS `caresoft_imaging`
  DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `caresoft_imaging`;

-- ---------------------------------------------------------------------
-- 1. TENANCY & USERS  (3 access levels: platform / client / doctor)
-- ---------------------------------------------------------------------

CREATE TABLE tenant (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code            VARCHAR(24)  NOT NULL UNIQUE COMMENT 'short slug, used in URLs/logs',
  name            VARCHAR(160) NOT NULL,
  legal_name      VARCHAR(200) NULL,
  city            VARCHAR(80)  NULL,
  state           VARCHAR(80)  NULL,
  country         VARCHAR(80)  NOT NULL DEFAULT 'India',
  timezone        VARCHAR(48)  NOT NULL DEFAULT 'Asia/Kolkata',
  contact_person  VARCHAR(120) NULL,
  contact_email   VARCHAR(160) NULL,
  contact_phone   VARCHAR(40)  NULL,
  bed_count       INT          NULL,
  plan            ENUM('trial','onprem','cloud','teleradiology') NOT NULL DEFAULT 'trial',
  status          ENUM('active','suspended','closed') NOT NULL DEFAULT 'active',
  go_live_date    DATE         NULL,
  -- Orthanc endpoint for this tenant (site node or cloud node)
  orthanc_base_url VARCHAR(200) NULL COMMENT 'e.g. http://10.0.0.12:8042',
  orthanc_user     VARCHAR(80)  NULL,
  orthanc_pass_enc VARBINARY(512) NULL COMMENT 'AES-encrypted, see lib/crypto.php',
  orthanc_ae_title VARCHAR(32)  NULL DEFAULT 'CARESOFT',
  webhook_secret   VARCHAR(64)  NULL COMMENT 'shared secret Orthanc Lua uses to call us',
  storage_quota_gb INT          NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE branch (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id   INT UNSIGNED NOT NULL,
  name        VARCHAR(160) NOT NULL,
  code        VARCHAR(24)  NOT NULL,
  address     VARCHAR(255) NULL,
  is_active   TINYINT(1)   NOT NULL DEFAULT 1,
  UNIQUE KEY uq_branch (tenant_id, code),
  CONSTRAINT fk_branch_tenant FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- level: 1 = Caresoft platform staff, 2 = client admin, 3 = end user (doctor/tech/desk)
CREATE TABLE app_user (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id      INT UNSIGNED NULL COMMENT 'NULL for level-1 platform staff',
  branch_id      INT UNSIGNED NULL,
  partner_id     INT UNSIGNED NULL COMMENT 'set for external teleradiology readers',
  level          TINYINT      NOT NULL COMMENT '1=platform 2=client-admin 3=user',
  role           ENUM('superadmin','support','sales',
                      'client_admin','it_admin',
                      'radiologist','senior_radiologist','technician','front_desk','referring_doctor')
                 NOT NULL,
  full_name      VARCHAR(140) NOT NULL,
  email          VARCHAR(160) NOT NULL,
  phone          VARCHAR(40)  NULL,
  username       VARCHAR(80)  NOT NULL,
  password_hash  VARCHAR(255) NOT NULL,
  registration_no VARCHAR(60) NULL COMMENT 'medical council reg. no for report footer',
  qualification  VARCHAR(120) NULL,
  signature_path VARCHAR(255) NULL,
  dictation_lang VARCHAR(16) NULL DEFAULT 'en-IN',
  dictation_autopunct TINYINT(1) NOT NULL DEFAULT 1,
  is_active      TINYINT(1)   NOT NULL DEFAULT 1,
  must_change_pw TINYINT(1)   NOT NULL DEFAULT 0,
  failed_logins  INT          NOT NULL DEFAULT 0,
  locked_until   DATETIME     NULL,
  last_login_at  DATETIME     NULL,
  last_login_ip  VARCHAR(45)  NULL,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_username (username),
  KEY ix_user_tenant (tenant_id, level, is_active),
  KEY ix_user_partner (partner_id),
  CONSTRAINT fk_user_tenant FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE user_session (
  id           CHAR(64) PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  ip           VARCHAR(45)  NULL,
  user_agent   VARCHAR(255) NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at   DATETIME NOT NULL,
  KEY ix_sess_user (user_id),
  CONSTRAINT fk_sess_user FOREIGN KEY (user_id) REFERENCES app_user(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 2. IMAGING — equipment, orders, studies, reports
-- ---------------------------------------------------------------------

CREATE TABLE img_modality (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id       INT UNSIGNED NOT NULL,
  branch_id       INT UNSIGNED NULL,
  name            VARCHAR(120) NOT NULL COMMENT 'e.g. CT-1 Siemens Somatom',
  modality_type   VARCHAR(8)   NOT NULL COMMENT 'DICOM code: CT MR CR DX US MG NM PT XA OT',
  manufacturer    VARCHAR(120) NULL,
  model           VARCHAR(120) NULL,
  serial_no       VARCHAR(80)  NULL,
  ae_title        VARCHAR(32)  NOT NULL,
  host_ip         VARCHAR(64)  NULL,
  port            INT          NULL DEFAULT 104,
  station_name    VARCHAR(64)  NULL,
  room_location   VARCHAR(120) NULL,
  supports_mwl    TINYINT(1)   NOT NULL DEFAULT 1,
  supports_mpps   TINYINT(1)   NOT NULL DEFAULT 0,
  conformance_notes TEXT       NULL,
  install_date    DATE         NULL,
  amc_vendor      VARCHAR(140) NULL,
  amc_expiry      DATE         NULL,
  warranty_expiry DATE         NULL,
  aerb_reg_no     VARCHAR(80)  NULL,
  aerb_expiry     DATE         NULL,
  pm_frequency_days INT        NULL,
  last_pm_date    DATE         NULL,
  calibration_due DATE         NULL,
  silent_alert_hours INT       NULL COMMENT 'warn if no study arrives for this many hours',
  -- health monitoring
  last_echo_at    DATETIME     NULL,
  last_echo_ok    TINYINT(1)   NULL,
  last_echo_error VARCHAR(255) NULL,
  echo_fail_count INT          NOT NULL DEFAULT 0,
  last_study_at   DATETIME     NULL,
  is_active       TINYINT(1)   NOT NULL DEFAULT 1,
  service_status  ENUM('in_service','down','maintenance','retired') NOT NULL DEFAULT 'in_service',
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ae (tenant_id, ae_title),
  KEY ix_mod_tenant (tenant_id, is_active),
  KEY ix_mod_status (tenant_id, service_status),
  CONSTRAINT fk_mod_tenant FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Everything that happens to a machine, on one timeline.
CREATE TABLE img_equipment_event (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  modality_id   INT UNSIGNED NOT NULL,
  event_type    ENUM('breakdown','service','pm','calibration','installation','qa','note')
                NOT NULL DEFAULT 'breakdown',
  severity      ENUM('minor','major','total') NOT NULL DEFAULT 'major',
  title         VARCHAR(200) NOT NULL,
  description   TEXT NULL,
  action_taken  TEXT NULL,
  vendor        VARCHAR(140) NULL,
  ticket_ref    VARCHAR(80)  NULL,
  parts_replaced VARCHAR(300) NULL,
  cost          DECIMAL(12,2) NULL,
  started_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at   DATETIME NULL,
  counts_downtime TINYINT(1) NOT NULL DEFAULT 1,
  next_due_date DATE NULL,
  raised_by     INT UNSIGNED NULL COMMENT 'null when the monitor raised it',
  closed_by     INT UNSIGNED NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_ee (tenant_id, modality_id, started_at),
  KEY ix_ee_open (tenant_id, resolved_at),
  CONSTRAINT fk_ee_tenant FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE,
  CONSTRAINT fk_ee_mod FOREIGN KEY (modality_id) REFERENCES img_modality(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE img_procedure (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  code          VARCHAR(40)  NOT NULL,
  name          VARCHAR(180) NOT NULL,
  modality_type VARCHAR(8)   NOT NULL,
  body_part     VARCHAR(80)  NULL,
  duration_min  INT          NOT NULL DEFAULT 15,
  contrast_used TINYINT(1)   NOT NULL DEFAULT 0,
  price         DECIMAL(12,2) NULL,
  his_item_code VARCHAR(40)  NULL COMMENT 'maps to Caresoft HIS service item',
  is_active     TINYINT(1)   NOT NULL DEFAULT 1,
  UNIQUE KEY uq_proc (tenant_id, code),
  CONSTRAINT fk_proc_tenant FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE img_patient (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  -- Caresoft HIS patient key convention
  his_ptype     VARCHAR(4)   NULL,
  his_pno       VARCHAR(24)  NULL,
  his_pyr       VARCHAR(8)   NULL,
  mrn           VARCHAR(48)  NOT NULL COMMENT 'displayed hospital number / DICOM PatientID',
  full_name     VARCHAR(160) NOT NULL,
  sex           ENUM('M','F','O','U') NOT NULL DEFAULT 'U',
  dob           DATE         NULL,
  age_years     INT          NULL,
  phone         VARCHAR(40)  NULL,
  abha_id       VARCHAR(64)  NULL,
  abha_address  VARCHAR(120) NULL,
  merged_into   BIGINT UNSIGNED NULL COMMENT 'set when HIS merges duplicates',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_mrn (tenant_id, mrn),
  KEY ix_pat_name (tenant_id, full_name),
  KEY ix_pat_his (tenant_id, his_ptype, his_pno, his_pyr),
  KEY ix_pat_abha (tenant_id, abha_address),
  CONSTRAINT fk_pat_tenant FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE img_order (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id        INT UNSIGNED NOT NULL,
  branch_id        INT UNSIGNED NULL,
  accession_no     VARCHAR(32)  NOT NULL COMMENT 'the join key between HIS, MWL and DICOM',
  patient_id       BIGINT UNSIGNED NOT NULL,
  procedure_id     INT UNSIGNED NULL,
  modality_id      INT UNSIGNED NULL,
  modality_type    VARCHAR(8)   NOT NULL,
  his_order_ref    VARCHAR(48)  NULL,
  visit_type       ENUM('OPD','IPD','EMERGENCY','HEALTHCHECK','WALKIN','EXTERNAL') NOT NULL DEFAULT 'OPD',
  medicolegal      TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'accident, assault, poisoning — held far longer',
  ward_bed         VARCHAR(60)  NULL,
  referring_doctor VARCHAR(140) NULL,
  clinical_history TEXT         NULL,
  priority         ENUM('routine','urgent','stat') NOT NULL DEFAULT 'routine',
  scheduled_at     DATETIME     NULL,
  billing_status   ENUM('unbilled','billed','paid','credit','waived') NOT NULL DEFAULT 'unbilled',
  charged_amount   DECIMAL(12,2) NULL COMMENT 'captured at order time so a price change cannot rewrite history',
  status           ENUM('ordered','scheduled','arrived','in_progress','acquired',
                        'reported','verified','delivered','cancelled') NOT NULL DEFAULT 'ordered',
  cancelled_reason VARCHAR(255) NULL,
  created_by       INT UNSIGNED NULL,
  created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_accession (tenant_id, accession_no),
  KEY ix_order_status (tenant_id, status, priority, scheduled_at),
  KEY ix_order_patient (patient_id),
  KEY ix_ord_tat (tenant_id, created_at, status),
  KEY ix_ord_ref (tenant_id, referring_doctor),
  KEY ix_ord_mod (tenant_id, modality_id, created_at),
  CONSTRAINT fk_ord_tenant FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE,
  CONSTRAINT fk_ord_pat FOREIGN KEY (patient_id) REFERENCES img_patient(id)
) ENGINE=InnoDB;

CREATE TABLE img_study (
  id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id          INT UNSIGNED NOT NULL,
  order_id           BIGINT UNSIGNED NULL COMMENT 'NULL until reconciled',
  patient_id         BIGINT UNSIGNED NULL,
  orthanc_study_id   VARCHAR(64)  NOT NULL COMMENT 'Orthanc internal UUID',
  study_instance_uid VARCHAR(128) NOT NULL,
  accession_no       VARCHAR(32)  NULL,
  study_date         DATE         NULL,
  study_time         TIME         NULL,
  modality_types     VARCHAR(48)  NULL COMMENT 'comma list from Orthanc',
  station_ae         VARCHAR(32)  NULL,
  description        VARCHAR(200) NULL,
  series_count       INT          NOT NULL DEFAULT 0,
  instance_count     INT          NOT NULL DEFAULT 0,
  size_bytes         BIGINT       NOT NULL DEFAULT 0,
  reconcile_status   ENUM('matched','unmatched','manual','rejected') NOT NULL DEFAULT 'unmatched',
  storage_tier       ENUM('hot','cold','archived','purged') NOT NULL DEFAULT 'hot',
  tier_moved_at      DATETIME     NULL,
  purge_due_on       DATE         NULL,
  legal_hold         TINYINT(1)   NOT NULL DEFAULT 0,
  legal_hold_reason  VARCHAR(255) NULL,
  legal_hold_by      INT UNSIGNED NULL,
  retention_rule_id  INT UNSIGNED NULL,
  cold_location      VARCHAR(200) NULL,
  purged_at          DATETIME     NULL,
  received_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_orthanc (tenant_id, orthanc_study_id),
  KEY ix_study_acc (tenant_id, accession_no),
  KEY ix_study_recon (tenant_id, reconcile_status),
  KEY ix_study_date (tenant_id, study_date),
  KEY ix_study_lifecycle (tenant_id, storage_tier, purge_due_on),
  KEY ix_study_hold (tenant_id, legal_hold),
  CONSTRAINT fk_std_tenant FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE img_series (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  study_id            BIGINT UNSIGNED NOT NULL,
  orthanc_series_id   VARCHAR(64)  NOT NULL,
  series_instance_uid VARCHAR(128) NULL,
  series_number       INT          NULL,
  description         VARCHAR(200) NULL,
  modality_type       VARCHAR(8)   NULL,
  body_part           VARCHAR(80)  NULL,
  instance_count      INT          NOT NULL DEFAULT 0,
  UNIQUE KEY uq_series (orthanc_series_id),
  KEY ix_series_study (study_id),
  CONSTRAINT fk_ser_study FOREIGN KEY (study_id) REFERENCES img_study(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- How long to keep what. Rules are matched most specific first.
CREATE TABLE img_retention_rule (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  name          VARCHAR(140) NOT NULL,
  modality_type VARCHAR(8) NULL COMMENT 'NULL = any modality',
  patient_group ENUM('any','adult','paediatric','medicolegal') NOT NULL DEFAULT 'any',
  hot_months    INT NOT NULL DEFAULT 6,
  retain_years  INT NOT NULL DEFAULT 7,
  priority      INT NOT NULL DEFAULT 100 COMMENT 'lower number wins',
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_rr_tenant (tenant_id, is_active, priority),
  CONSTRAINT fk_rr_ten FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- What the lifecycle worker did, every time it ran.
CREATE TABLE img_storage_job (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NULL,
  job_type      ENUM('plan','tier','purge','export','backup_check') NOT NULL,
  mode          ENUM('dry_run','live') NOT NULL DEFAULT 'dry_run',
  started_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at   DATETIME NULL,
  studies_seen  INT NOT NULL DEFAULT 0,
  studies_acted INT NOT NULL DEFAULT 0,
  bytes_acted   BIGINT NOT NULL DEFAULT 0,
  status        ENUM('running','ok','partial','failed') NOT NULL DEFAULT 'running',
  detail        TEXT NULL,
  run_by        INT UNSIGNED NULL,
  KEY ix_sj (tenant_id, job_type, started_at)
) ENGINE=InnoDB;

-- Evidence that a restore was actually tried, not just that a backup exists.
CREATE TABLE img_backup_check (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id      INT UNSIGNED NOT NULL,
  checked_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  method         VARCHAR(140) NOT NULL,
  studies_sampled INT NOT NULL DEFAULT 0,
  restored_ok    INT NOT NULL DEFAULT 0,
  failed         INT NOT NULL DEFAULT 0,
  backup_dated   DATE NULL,
  notes          TEXT NULL,
  checked_by     INT UNSIGNED NULL,
  KEY ix_bc (tenant_id, checked_at),
  CONSTRAINT fk_bc_ten FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Bulk export requests. The answer to "what if we leave you".
CREATE TABLE img_export_job (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  requested_by  INT UNSIGNED NULL,
  reason        VARCHAR(255) NULL,
  date_from     DATE NULL,
  date_to       DATE NULL,
  modality_type VARCHAR(8) NULL,
  mrn           VARCHAR(48) NULL,
  accession_no  VARCHAR(32) NULL,
  deidentify    TINYINT(1) NOT NULL DEFAULT 0,
  study_count   INT NOT NULL DEFAULT 0,
  bytes_total   BIGINT NOT NULL DEFAULT 0,
  status        ENUM('draft','ready','collected','cancelled') NOT NULL DEFAULT 'draft',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  collected_at  DATETIME NULL,
  KEY ix_ej (tenant_id, created_at),
  CONSTRAINT fk_ej_ten FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE img_acquisition (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id       BIGINT UNSIGNED NOT NULL,
  modality_id    INT UNSIGNED NULL,
  technician_id  INT UNSIGNED NULL,
  patient_arrived_at DATETIME NULL,
  started_at     DATETIME NULL,
  completed_at   DATETIME NULL,
  repeat_count   INT NOT NULL DEFAULT 0,
  reject_reason  VARCHAR(120) NULL COMMENT 'positioning / motion / exposure / artefact / equipment',
  contrast_agent VARCHAR(120) NULL,
  contrast_volume_ml DECIMAL(8,2) NULL,
  contrast_batch VARCHAR(60) NULL,
  contrast_route VARCHAR(40) NULL,
  reaction_noted VARCHAR(255) NULL,
  reaction_severity ENUM('none','mild','moderate','severe') NOT NULL DEFAULT 'none',
  reaction_managed_by VARCHAR(140) NULL,
  film_used      INT NOT NULL DEFAULT 0,
  consumables    VARCHAR(300) NULL,
  notes          TEXT NULL,
  KEY ix_acq_order (order_id),
  KEY ix_acq_tenant_time (started_at),
  CONSTRAINT fk_acq_order FOREIGN KEY (order_id) REFERENCES img_order(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 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 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,
  reason_note     VARCHAR(300) NULL,
  action_taken    VARCHAR(300) NULL,
  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;

CREATE TABLE img_report_template (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  name          VARCHAR(160) NOT NULL,
  modality_type VARCHAR(8)   NULL,
  body_part     VARCHAR(80)  NULL,
  body_html     MEDIUMTEXT   NOT NULL,
  is_normal     TINYINT(1)   NOT NULL DEFAULT 0 COMMENT 'one-click normal report',
  is_active     TINYINT(1)   NOT NULL DEFAULT 1,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_tpl (tenant_id, modality_type, is_active),
  CONSTRAINT fk_tpl_tenant FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE img_report (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id       INT UNSIGNED NOT NULL,
  order_id        BIGINT UNSIGNED NOT NULL,
  study_id        BIGINT UNSIGNED NULL,
  template_id     INT UNSIGNED NULL,
  clinical_info   TEXT NULL,
  technique       TEXT NULL,
  findings        MEDIUMTEXT NULL,
  impression      MEDIUMTEXT NULL,
  recommendation  TEXT NULL,
  status          ENUM('draft','submitted','verified','addendum','cancelled') NOT NULL DEFAULT 'draft',
  version         INT NOT NULL DEFAULT 1,
  authored_by     INT UNSIGNED NULL,
  authored_at     DATETIME NULL,
  verified_by     INT UNSIGNED NULL,
  verified_at     DATETIME NULL,
  delivered_at    DATETIME NULL,
  signature_hash  VARCHAR(128) NULL,
  verify_code     VARCHAR(24) NULL COMMENT 'short public code printed with the QR',
  is_critical     TINYINT(1) NOT NULL DEFAULT 0,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY ix_rep_order (order_id, version),
  KEY ix_rep_status (tenant_id, status),
  KEY ix_rep_verified (tenant_id, verified_at),
  KEY ix_rep_author (authored_by, authored_at),
  UNIQUE KEY uq_verify (verify_code),
  CONSTRAINT fk_rep_order FOREIGN KEY (order_id) REFERENCES img_order(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE img_report_history (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  report_id   BIGINT UNSIGNED NOT NULL,
  version     INT NOT NULL,
  snapshot    MEDIUMTEXT NOT NULL COMMENT 'JSON of report at that version',
  changed_by  INT UNSIGNED NULL,
  changed_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_hist (report_id, version)
) ENGINE=InnoDB;

CREATE TABLE img_critical_finding (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  report_id     BIGINT UNSIGNED NOT NULL,
  order_id      BIGINT UNSIGNED NULL,
  flagged_by    INT UNSIGNED NULL,
  flagged_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finding_text  VARCHAR(500) NOT NULL,
  category      VARCHAR(48) NULL,
  severity      ENUM('critical','urgent') NOT NULL DEFAULT 'critical',
  notify_to     VARCHAR(200) NULL,
  notified_at   DATETIME NULL,
  ack_token     VARCHAR(64) NULL,
  ack_expires_at DATETIME NULL,
  acknowledged_by VARCHAR(140) NULL,
  acknowledged_role VARCHAR(80) NULL,
  acknowledged_ip VARCHAR(45) NULL,
  ack_note      VARCHAR(500) NULL,
  acknowledged_at DATETIME NULL,
  escalated_at  DATETIME NULL,
  escalation_level INT NOT NULL DEFAULT 0,
  next_action_at DATETIME NULL,
  closed_at     DATETIME NULL,
  closed_by     INT UNSIGNED NULL,
  closure_note  VARCHAR(500) NULL,
  UNIQUE KEY uq_ack_token (ack_token),
  KEY ix_cf (tenant_id, acknowledged_at),
  KEY ix_cf_due (tenant_id, acknowledged_at, next_action_at)
) ENGINE=InnoDB;

-- Every attempt to reach somebody, kept whether it worked or not.
-- "We called" is not evidence; this is.
CREATE TABLE 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,
  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 img_escalation_contact (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id  INT UNSIGNED NOT NULL,
  level      INT NOT NULL DEFAULT 1,
  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;

CREATE TABLE img_allocation (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id      INT UNSIGNED NOT NULL,
  order_id       BIGINT UNSIGNED NOT NULL,
  radiologist_id INT UNSIGNED NULL,
  partner_id     INT UNSIGNED NULL,
  rule_id        INT UNSIGNED NULL,
  source         ENUM('internal','empanelled','outsourced') NOT NULL DEFAULT 'internal',
  partner_name   VARCHAR(140) NULL,
  rate           DECIMAL(10,2) NULL,
  currency       VARCHAR(8) NULL,
  allocated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  accepted_at    DATETIME NULL,
  declined_at    DATETIME NULL,
  decline_reason VARCHAR(255) NULL,
  reassigned_from INT UNSIGNED NULL,
  sla_due_at     DATETIME NULL,
  completed_at   DATETIME NULL,
  breached       TINYINT(1) NOT NULL DEFAULT 0,
  invoice_id     BIGINT UNSIGNED NULL,
  KEY ix_alloc (tenant_id, radiologist_id, completed_at),
  KEY ix_alloc_partner (tenant_id, partner_id, completed_at),
  KEY ix_alloc_invoice (invoice_id)
) ENGINE=InnoDB;

-- Who reads for this hospital: its own staff, empanelled individuals, or a
-- teleradiology company.
CREATE TABLE img_telerad_partner (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  name          VARCHAR(160) NOT NULL,
  partner_type  ENUM('internal','empanelled','outsourced') NOT NULL DEFAULT 'empanelled',
  contact_person VARCHAR(140) NULL,
  email         VARCHAR(160) NULL,
  phone         VARCHAR(40)  NULL,
  agreement_ref VARCHAR(80)  NULL,
  currency      VARCHAR(8)   NOT NULL DEFAULT 'INR',
  is_active     TINYINT(1)   NOT NULL DEFAULT 1,
  notes         TEXT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_tp (tenant_id, is_active),
  CONSTRAINT fk_tp_ten FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Rates are dated: last quarter's invoice must not change when a new rate
-- is agreed.
CREATE TABLE img_telerad_rate (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  partner_id     INT UNSIGNED NOT NULL,
  modality_type  VARCHAR(8) NULL COMMENT 'NULL = any modality',
  priority       ENUM('any','routine','urgent','stat') NOT NULL DEFAULT 'any',
  rate           DECIMAL(10,2) NOT NULL,
  sla_minutes    INT NOT NULL DEFAULT 1440,
  effective_from DATE NOT NULL,
  effective_to   DATE NULL,
  KEY ix_tr (partner_id, modality_type, priority, effective_from),
  CONSTRAINT fk_tr_partner FOREIGN KEY (partner_id) REFERENCES img_telerad_partner(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Who gets what, and when. Matched in order, first hit wins.
CREATE TABLE img_allocation_rule (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  name          VARCHAR(140) NOT NULL,
  modality_type VARCHAR(8) NULL,
  priority      ENUM('any','routine','urgent','stat') NOT NULL DEFAULT 'any',
  visit_type    VARCHAR(20) NULL,
  hour_from     TINYINT NULL,
  hour_to       TINYINT NULL,
  partner_id    INT UNSIGNED NULL,
  radiologist_id INT UNSIGNED NULL,
  forward_study TINYINT(1) NOT NULL DEFAULT 0,
  sort_order    INT NOT NULL DEFAULT 100,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  KEY ix_ar (tenant_id, is_active, sort_order),
  CONSTRAINT fk_ar_ten FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Images pushed to a central node. A queue rather than a flag, so a failed
-- forward is retried rather than lost.
CREATE TABLE img_study_forward (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id    INT UNSIGNED NOT NULL,
  study_id     BIGINT UNSIGNED NOT NULL,
  peer         VARCHAR(80) NOT NULL,
  status       ENUM('queued','sending','sent','failed') NOT NULL DEFAULT 'queued',
  attempts     INT NOT NULL DEFAULT 0,
  queued_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at      DATETIME NULL,
  last_error   VARCHAR(255) NULL,
  KEY ix_sf (tenant_id, status, queued_at),
  KEY ix_sf_study (study_id),
  CONSTRAINT fk_sf_study FOREIGN KEY (study_id) REFERENCES img_study(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Monthly reconciliation. The hospital's own count, not the partner's.
CREATE TABLE img_telerad_invoice (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  partner_id    INT UNSIGNED NOT NULL,
  period_from   DATE NOT NULL,
  period_to     DATE NOT NULL,
  study_count   INT NOT NULL DEFAULT 0,
  within_sla    INT NOT NULL DEFAULT 0,
  breached      INT NOT NULL DEFAULT 0,
  amount        DECIMAL(12,2) NOT NULL DEFAULT 0,
  currency      VARCHAR(8) NOT NULL DEFAULT 'INR',
  status        ENUM('draft','sent','agreed','disputed','paid') NOT NULL DEFAULT 'draft',
  partner_claim DECIMAL(12,2) NULL,
  notes         TEXT NULL,
  generated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  generated_by  INT UNSIGNED NULL,
  UNIQUE KEY uq_period (tenant_id, partner_id, period_from, period_to),
  KEY ix_ti (tenant_id, status),
  CONSTRAINT fk_ti_partner FOREIGN KEY (partner_id) REFERENCES img_telerad_partner(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE img_delivery (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id   INT UNSIGNED NULL,
  report_id   BIGINT UNSIGNED NOT NULL,
  report_version INT NOT NULL DEFAULT 1,
  channel     ENUM('portal','whatsapp','email','print','his','abdm') NOT NULL,
  recipient_kind ENUM('patient','referrer','hospital','partner','other') NOT NULL DEFAULT 'patient',
  recipient_name VARCHAR(140) NULL,
  target      VARCHAR(200) NULL,
  token       VARCHAR(64)  NULL COMMENT 'short-lived public access token',
  token_expires_at DATETIME NULL,
  sent_at     DATETIME NULL,
  viewed_at   DATETIME NULL,
  view_count  INT NOT NULL DEFAULT 0,
  last_view_ip VARCHAR(45) NULL,
  revoked_at  DATETIME NULL,
  status      ENUM('queued','sent','failed','viewed') NOT NULL DEFAULT 'queued',
  sent_by     INT UNSIGNED NULL,
  error_text  VARCHAR(255) NULL,
  KEY ix_del (report_id, channel),
  KEY ix_del_token (token),
  KEY ix_del_tenant (tenant_id, sent_at)
) ENGINE=InnoDB;

CREATE TABLE img_audit (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id   INT UNSIGNED NULL,
  user_id     INT UNSIGNED NULL,
  actor_name  VARCHAR(140) NULL,
  action      VARCHAR(60)  NOT NULL COMMENT 'login, view_study, export, edit_report, reconcile, break_glass',
  object_type VARCHAR(40)  NULL,
  object_id   VARCHAR(64)  NULL,
  detail      VARCHAR(500) NULL,
  ip          VARCHAR(45)  NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_audit (tenant_id, created_at),
  KEY ix_audit_user (user_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE img_setting (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id  INT UNSIGNED NULL COMMENT 'NULL = platform-wide default',
  skey       VARCHAR(80)  NOT NULL,
  sval       TEXT NULL,
  UNIQUE KEY uq_setting (tenant_id, skey)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 3. CMS — landing page, blog, testimonials, SEO / AEO / GEO
-- ---------------------------------------------------------------------

CREATE TABLE cms_page (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug          VARCHAR(140) NOT NULL UNIQUE,
  title         VARCHAR(200) NOT NULL,
  body_html     MEDIUMTEXT NULL,
  is_published  TINYINT(1) NOT NULL DEFAULT 1,
  show_in_nav   TINYINT(1) NOT NULL DEFAULT 0,
  nav_order     INT NOT NULL DEFAULT 0,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Every editable block on the landing page. Keeps the homepage fully CMS-driven.
CREATE TABLE cms_block (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  block_key   VARCHAR(80) NOT NULL UNIQUE COMMENT 'e.g. hero.headline',
  label       VARCHAR(140) NOT NULL,
  block_group VARCHAR(60)  NOT NULL DEFAULT 'home',
  content     MEDIUMTEXT NULL,
  input_type  ENUM('text','textarea','html','image','url') NOT NULL DEFAULT 'text',
  sort_order  INT NOT NULL DEFAULT 0,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE cms_feature (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  section     VARCHAR(40) NOT NULL DEFAULT 'module' COMMENT 'module | capability | outcome',
  icon        VARCHAR(40) NULL COMMENT 'bootstrap-icons name',
  title       VARCHAR(160) NOT NULL,
  summary     VARCHAR(500) NULL,
  detail_html MEDIUMTEXT NULL,
  sort_order  INT NOT NULL DEFAULT 0,
  is_active   TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE cms_category (
  id     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug   VARCHAR(100) NOT NULL UNIQUE,
  name   VARCHAR(120) NOT NULL,
  description VARCHAR(300) NULL
) ENGINE=InnoDB;

CREATE TABLE cms_post (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id    INT UNSIGNED NULL,
  slug           VARCHAR(180) NOT NULL UNIQUE,
  title          VARCHAR(220) NOT NULL,
  excerpt        VARCHAR(500) NULL,
  body_html      MEDIUMTEXT NULL,
  cover_image    VARCHAR(255) NULL,
  cover_alt      VARCHAR(200) NULL,
  author_name    VARCHAR(120) NOT NULL DEFAULT 'Caresoft Systems',
  reading_minutes INT NULL,
  -- AEO: a direct, quotable answer block that answer engines can lift
  answer_summary VARCHAR(1200) NULL COMMENT 'AEO: 40-60 word direct answer to the title question',
  key_takeaways  TEXT NULL COMMENT 'AEO: one bullet per line',
  is_published   TINYINT(1) NOT NULL DEFAULT 0,
  published_at   DATETIME NULL,
  view_count     INT NOT NULL DEFAULT 0,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY ix_post_pub (is_published, published_at)
) ENGINE=InnoDB;

CREATE TABLE cms_testimonial (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  quote         VARCHAR(900) NOT NULL,
  author_name   VARCHAR(140) NOT NULL,
  author_title  VARCHAR(160) NULL,
  hospital_name VARCHAR(180) NULL,
  city          VARCHAR(80)  NULL,
  country       VARCHAR(80)  NULL DEFAULT 'India',
  photo         VARCHAR(255) NULL,
  rating        TINYINT NULL COMMENT '1-5, used for AggregateRating schema',
  bed_count     INT NULL,
  is_featured   TINYINT(1) NOT NULL DEFAULT 0,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  sort_order    INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

-- AEO: question/answer pairs rendered as FAQPage JSON-LD and visible accordion
CREATE TABLE cms_faq (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  scope      VARCHAR(60) NOT NULL DEFAULT 'home' COMMENT 'home | post:<id> | page:<slug>',
  question   VARCHAR(300) NOT NULL,
  answer     TEXT NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active  TINYINT(1) NOT NULL DEFAULT 1,
  KEY ix_faq_scope (scope, is_active)
) ENGINE=InnoDB;

-- SEO record attached to any entity
CREATE TABLE cms_seo (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  entity_type    ENUM('home','page','post','blog_index','geo','custom') NOT NULL,
  entity_ref     VARCHAR(180) NOT NULL COMMENT 'id or slug; "-" for singletons',
  meta_title     VARCHAR(200) NULL,
  meta_desc      VARCHAR(400) NULL,
  meta_keywords  VARCHAR(400) NULL,
  canonical_url  VARCHAR(255) NULL,
  og_title       VARCHAR(200) NULL,
  og_desc        VARCHAR(400) NULL,
  og_image       VARCHAR(255) NULL,
  robots         VARCHAR(60) NOT NULL DEFAULT 'index,follow',
  schema_json    MEDIUMTEXT NULL COMMENT 'extra JSON-LD merged into the page',
  changefreq     VARCHAR(20) NOT NULL DEFAULT 'monthly',
  priority       DECIMAL(2,1) NOT NULL DEFAULT 0.6,
  UNIQUE KEY uq_seo (entity_type, entity_ref)
) ENGINE=InnoDB;

-- GEO: location landing pages ("PACS software in Mumbai") + LocalBusiness schema
CREATE TABLE cms_geo_location (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug          VARCHAR(120) NOT NULL UNIQUE,
  city          VARCHAR(100) NOT NULL,
  state         VARCHAR(100) NULL,
  country       VARCHAR(100) NOT NULL DEFAULT 'India',
  latitude      DECIMAL(10,7) NULL,
  longitude     DECIMAL(10,7) NULL,
  service_area  VARCHAR(300) NULL,
  office_address VARCHAR(300) NULL,
  office_phone  VARCHAR(60) NULL,
  intro_html    MEDIUMTEXT NULL,
  hospitals_served INT NULL,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  sort_order    INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE cms_enquiry (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name     VARCHAR(140) NOT NULL,
  hospital_name VARCHAR(180) NULL,
  email         VARCHAR(160) NOT NULL,
  phone         VARCHAR(40)  NULL,
  city          VARCHAR(80)  NULL,
  bed_count     VARCHAR(40)  NULL,
  modalities    VARCHAR(200) NULL,
  message       TEXT NULL,
  source_page   VARCHAR(200) NULL,
  utm_source    VARCHAR(80)  NULL,
  utm_campaign  VARCHAR(120) NULL,
  ip            VARCHAR(45)  NULL,
  status        ENUM('new','contacted','demo_done','proposal','won','lost') NOT NULL DEFAULT 'new',
  internal_note TEXT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_enq (status, created_at)
) ENGINE=InnoDB;

CREATE TABLE cms_redirect (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  from_path   VARCHAR(255) NOT NULL UNIQUE,
  to_path     VARCHAR(255) NOT NULL,
  http_code   INT NOT NULL DEFAULT 301,
  hit_count   INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 4. COMPLIANCE — ABDM linkage and DPDP Act 2023 controls
-- ---------------------------------------------------------------------

CREATE TABLE abdm_config (
  tenant_id       INT UNSIGNED NOT NULL PRIMARY KEY,
  environment     ENUM('sandbox','production') NOT NULL DEFAULT 'sandbox',
  hip_id          VARCHAR(80)  NULL COMMENT 'health facility id from the HFR',
  hip_name        VARCHAR(160) NULL,
  hfr_id          VARCHAR(80)  NULL,
  client_id       VARCHAR(120) NULL,
  client_secret_enc VARBINARY(512) NULL,
  gateway_base_url VARCHAR(200) NULL,
  cm_suffix       VARCHAR(40)  NOT NULL DEFAULT 'sbx' COMMENT 'consent manager id, e.g. sbx or abdm',
  is_enabled      TINYINT(1)   NOT NULL DEFAULT 0,
  certified_on    DATE NULL COMMENT 'date ABDM milestone certification was granted',
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_abdm_ten FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE abdm_link (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  patient_id    BIGINT UNSIGNED NOT NULL,
  abha_number   VARCHAR(24)  NULL,
  abha_address  VARCHAR(120) NULL,
  link_status   ENUM('pending','linked','revoked','failed') NOT NULL DEFAULT 'pending',
  linked_at     DATETIME NULL,
  revoked_at    DATETIME NULL,
  link_ref      VARCHAR(80) NULL,
  last_error    VARCHAR(255) NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_link (tenant_id, patient_id),
  KEY ix_abha (abha_address),
  CONSTRAINT fk_link_ten FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE abdm_care_context (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  link_id       BIGINT UNSIGNED NOT NULL,
  order_id      BIGINT UNSIGNED NOT NULL,
  reference     VARCHAR(80)  NOT NULL COMMENT 'our stable id for this context',
  display       VARCHAR(200) NOT NULL,
  status        ENUM('pending','linked','failed') NOT NULL DEFAULT 'pending',
  linked_at     DATETIME NULL,
  last_error    VARCHAR(255) NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_cc (tenant_id, reference),
  KEY ix_cc_link (link_id),
  CONSTRAINT fk_cc_link FOREIGN KEY (link_id) REFERENCES abdm_link(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE abdm_consent (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id      INT UNSIGNED NOT NULL,
  consent_id     VARCHAR(80) NOT NULL,
  patient_id     BIGINT UNSIGNED NULL,
  abha_address   VARCHAR(120) NULL,
  requester_name VARCHAR(160) NULL COMMENT 'the HIU asking for the data',
  purpose_code   VARCHAR(40)  NULL,
  purpose_text   VARCHAR(160) NULL,
  hi_types       VARCHAR(200) NULL,
  date_from      DATETIME NULL,
  date_to        DATETIME NULL,
  expires_at     DATETIME NULL,
  status         ENUM('requested','granted','denied','expired','revoked') NOT NULL DEFAULT 'requested',
  granted_at     DATETIME NULL,
  revoked_at     DATETIME NULL,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_consent (tenant_id, consent_id),
  KEY ix_consent_status (tenant_id, status, expires_at),
  CONSTRAINT fk_cons_ten FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE abdm_transaction (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  request_id    VARCHAR(64) NULL,
  txn_type      VARCHAR(60) NOT NULL COMMENT 'link-init, care-context-notify, data-transfer, consent-notify',
  direction     ENUM('outbound','inbound') NOT NULL,
  reference     VARCHAR(80) NULL,
  http_code     INT NULL,
  status        ENUM('ok','failed','pending') NOT NULL DEFAULT 'pending',
  payload_bytes INT NULL,
  payload_hash  CHAR(64) NULL COMMENT 'sha256 of what was sent, for dispute, not the content',
  error_text    VARCHAR(500) NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_txn (tenant_id, txn_type, created_at)
) ENGINE=InnoDB;

CREATE TABLE dpdp_consent (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  patient_id    BIGINT UNSIGNED NOT NULL,
  purpose       VARCHAR(80) NOT NULL COMMENT 'treatment, report_delivery, teleradiology, research, marketing',
  notice_version VARCHAR(24) NULL COMMENT 'which version of the notice they were shown',
  channel       ENUM('written','verbal','portal','kiosk','whatsapp') NOT NULL DEFAULT 'written',
  language      VARCHAR(24) NULL COMMENT 'the notice must be available in the language they chose',
  granted_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  withdrawn_at  DATETIME NULL,
  withdrawn_reason VARCHAR(255) NULL,
  evidence_ref  VARCHAR(160) NULL COMMENT 'signed form reference, recording id',
  recorded_by   INT UNSIGNED NULL,
  KEY ix_dc (tenant_id, patient_id, purpose),
  CONSTRAINT fk_dc_ten FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE dpdp_request (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  patient_id    BIGINT UNSIGNED NULL,
  requester_name VARCHAR(140) NOT NULL,
  relationship  VARCHAR(80) NULL COMMENT 'self, parent, guardian, nominee',
  request_type  ENUM('access','correction','erasure','nomination','grievance') NOT NULL,
  detail        TEXT NULL,
  identity_verified TINYINT(1) NOT NULL DEFAULT 0,
  identity_method VARCHAR(120) NULL,
  received_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  due_at        DATETIME NULL,
  status        ENUM('received','verifying','in_progress','fulfilled','refused','withdrawn') NOT NULL DEFAULT 'received',
  outcome_note  TEXT NULL,
  lawful_basis  VARCHAR(255) NULL COMMENT 'when refused, the ground relied on',
  closed_at     DATETIME NULL,
  handled_by    INT UNSIGNED NULL,
  KEY ix_dr (tenant_id, status, due_at),
  CONSTRAINT fk_dr_ten FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE dpdp_breach (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id      INT UNSIGNED NOT NULL,
  title          VARCHAR(200) NOT NULL,
  discovered_at  DATETIME NOT NULL,
  occurred_at    DATETIME NULL,
  description    TEXT NULL,
  people_affected INT NULL,
  data_types     VARCHAR(255) NULL,
  containment    TEXT NULL,
  root_cause     TEXT NULL,
  board_notified_at DATETIME NULL,
  principals_notified_at DATETIME NULL,
  severity       ENUM('low','medium','high') NOT NULL DEFAULT 'medium',
  status         ENUM('open','contained','closed') NOT NULL DEFAULT 'open',
  closed_at      DATETIME NULL,
  recorded_by    INT UNSIGNED NULL,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_db (tenant_id, status, discovered_at),
  CONSTRAINT fk_db_ten FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE dpdp_processing_record (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  activity      VARCHAR(160) NOT NULL,
  data_category VARCHAR(200) NOT NULL,
  purpose       VARCHAR(255) NOT NULL,
  lawful_basis  VARCHAR(160) NOT NULL,
  recipients    VARCHAR(255) NULL,
  retention     VARCHAR(160) NULL,
  cross_border  VARCHAR(160) NULL,
  reviewed_on   DATE NULL,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  KEY ix_pr (tenant_id, is_active),
  CONSTRAINT fk_pr_ten FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 5. ASSISTANCE — dictation macros, hanging protocols, AI advisories
-- ---------------------------------------------------------------------

CREATE TABLE 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;

CREATE TABLE 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;

CREATE TABLE 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;

CREATE TABLE 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;