-- Baltique OS Core 0.40.0 — Immutable Signed Document Archive

CREATE TABLE IF NOT EXISTS operative_signed_document_archives (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  document_id BIGINT UNSIGNED NOT NULL,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  signature_id BIGINT UNSIGNED NULL,
  signer_role VARCHAR(60) NOT NULL DEFAULT 'patient',
  signed_at DATETIME NOT NULL,
  signed_by_user_id BIGINT UNSIGNED NULL,
  signer_ip VARCHAR(64) NULL,
  signer_user_agent VARCHAR(500) NULL,
  document_title VARCHAR(255) NOT NULL,
  document_type VARCHAR(80) NOT NULL,
  snapshot_html MEDIUMTEXT NOT NULL,
  signature_png MEDIUMTEXT NULL,
  snapshot_sha256 VARCHAR(80) NOT NULL,
  pdf_storage_path TEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'locked',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_signed_doc_role (document_id, signer_role),
  KEY idx_signed_doc_case (operative_case_id, created_at),
  KEY idx_signed_doc_patient (patient_id, created_at),
  KEY idx_signed_doc_hash (snapshot_sha256)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
