-- =====================================================================
-- Caresoft Imaging — migration 009
-- P11: ABDM linkage (FHIR, ABHA, care contexts, consent) and
--      DPDP Act 2023 controls (consent register, data principal
--      rights, breach register, record of processing).
-- Safe to run on a database created from schema.sql + migrations 002-008.
-- =====================================================================
USE `caresoft_imaging`;

-- ---------------------------------------------------------------- ABDM

CREATE TABLE IF NOT EXISTS 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;

-- A patient's ABHA, once they have chosen to link it. Never inferred.
CREATE TABLE IF NOT EXISTS 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;

-- One care context per study, which is what a patient sees in their app.
CREATE TABLE IF NOT EXISTS 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;

-- Consent artefacts received from a consent manager, and what they permit.
CREATE TABLE IF NOT EXISTS 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;

-- Every exchange with the gateway. Payloads are NOT stored: only what is
-- needed to prove an exchange happened and to debug it.
CREATE TABLE IF NOT EXISTS 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;

-- ---------------------------------------------------------------- DPDP

-- Consent taken from the patient at the counter, separate from ABDM's own.
CREATE TABLE IF NOT EXISTS 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;

-- Requests from data principals: access, correction, erasure, nomination.
CREATE TABLE IF NOT EXISTS 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;

-- Incident register. Kept whether or not the incident was notifiable.
CREATE TABLE IF NOT EXISTS 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;

-- Record of processing: what is held, why, on what basis, for how long.
CREATE TABLE IF NOT EXISTS 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;

ALTER TABLE img_patient
  ADD COLUMN abha_address VARCHAR(120) NULL AFTER abha_id,
  ADD KEY ix_pat_abha (tenant_id, abha_address);