-- =====================================================================
-- Caresoft Imaging — migration 006
-- P8: storage lifecycle — retention rules, tiering, legal hold,
--     verified backups, bulk export.
-- Safe to run on a database created from schema.sql + migrations 002-005.
-- =====================================================================
USE `caresoft_imaging`;

ALTER TABLE img_study
  ADD COLUMN legal_hold        TINYINT(1) NOT NULL DEFAULT 0 AFTER purge_due_on,
  ADD COLUMN legal_hold_reason VARCHAR(255) NULL AFTER legal_hold,
  ADD COLUMN legal_hold_by     INT UNSIGNED NULL AFTER legal_hold_reason,
  ADD COLUMN retention_rule_id INT UNSIGNED NULL AFTER legal_hold_by,
  ADD COLUMN cold_location     VARCHAR(200) NULL AFTER retention_rule_id,
  ADD COLUMN purged_at         DATETIME NULL AFTER cold_location,
  ADD KEY ix_study_lifecycle (tenant_id, storage_tier, purge_due_on),
  ADD KEY ix_study_hold (tenant_id, legal_hold);

ALTER TABLE img_order
  ADD COLUMN medicolegal TINYINT(1) NOT NULL DEFAULT 0
      COMMENT 'accident, assault, poisoning — held far longer' AFTER visit_type;

-- How long to keep what. Rules are matched most specific first.
CREATE TABLE IF NOT EXISTS 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 COMMENT 'months on fast disk before moving to cold',
  retain_years  INT NOT NULL DEFAULT 7 COMMENT 'total years to keep before purge is even considered',
  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 IF NOT EXISTS 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 COMMENT 'null when the scheduler ran it',
  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 IF NOT EXISTS 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 COMMENT 'how the restore was tested',
  studies_sampled INT NOT NULL DEFAULT 0,
  restored_ok    INT NOT NULL DEFAULT 0,
  failed         INT NOT NULL DEFAULT 0,
  backup_dated   DATE NULL COMMENT 'which backup was restored from',
  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 IF NOT EXISTS 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;