-- Baltique OS Core 0.61.0 — Consent Evidence Vault

CREATE TABLE IF NOT EXISTS consent_evidence_clause_versions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  clause_code VARCHAR(140) NOT NULL,
  title VARCHAR(255) NOT NULL,
  purpose_code VARCHAR(80) NULL,
  channel_code VARCHAR(40) NULL,
  document_family VARCHAR(80) NOT NULL DEFAULT 'consent',
  procedure_code VARCHAR(120) NULL,
  version_label VARCHAR(80) NOT NULL DEFAULT 'v1',
  source_label VARCHAR(120) NULL,
  legal_basis VARCHAR(120) NULL,
  body_text MEDIUMTEXT NULL,
  body_html MEDIUMTEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'draft',
  effective_from DATETIME NULL,
  superseded_at DATETIME NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cev_clause_version (clause_code, version_label),
  KEY idx_cev_clause_family (document_family, procedure_code, status),
  KEY idx_cev_clause_purpose (purpose_code, channel_code, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consent_evidence_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  consent_record_id BIGINT UNSIGNED NULL,
  clause_version_id BIGINT UNSIGNED NULL,
  signed_archive_id BIGINT UNSIGNED NULL,
  evidence_type VARCHAR(80) NOT NULL DEFAULT 'attachment',
  evidence_title VARCHAR(255) NOT NULL,
  source_channel VARCHAR(80) NOT NULL DEFAULT 'staff_upload',
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  legal_basis VARCHAR(120) NULL,
  evidence_note TEXT NULL,
  snapshot_html MEDIUMTEXT NULL,
  snapshot_text MEDIUMTEXT NULL,
  snapshot_sha256 VARCHAR(80) NULL,
  signed_at DATETIME NULL,
  signer_name VARCHAR(190) NULL,
  signer_role VARCHAR(80) NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_cev_item_patient (patient_id, created_at),
  KEY idx_cev_item_case (operative_case_id, created_at),
  KEY idx_cev_item_type (evidence_type, status),
  KEY idx_cev_item_hash (snapshot_sha256)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consent_evidence_attachments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  evidence_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  attachment_type VARCHAR(80) NOT NULL DEFAULT 'source_file',
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  storage_path TEXT NOT NULL,
  mime_type VARCHAR(120) NULL,
  size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  sha256 VARCHAR(80) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cev_attach_evidence (evidence_id, status),
  KEY idx_cev_attach_patient (patient_id, created_at),
  KEY idx_cev_attach_case (operative_case_id, created_at),
  KEY idx_cev_attach_hash (sha256)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consent_evidence_signatures (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  evidence_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  signer_role VARCHAR(80) NOT NULL DEFAULT 'patient',
  signer_name VARCHAR(190) NULL,
  signature_png MEDIUMTEXT NULL,
  signed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  signer_ip VARCHAR(80) NULL,
  signer_user_agent TEXT NULL,
  signature_sha256 VARCHAR(80) NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cev_sig_evidence (evidence_id, signed_at),
  KEY idx_cev_sig_patient (patient_id, signed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consent_evidence_source_imports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_name VARCHAR(255) NOT NULL,
  source_file TEXT NULL,
  source_kind VARCHAR(80) NOT NULL DEFAULT 'zip',
  source_policy VARCHAR(120) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'new',
  imported_count INT UNSIGNED NOT NULL DEFAULT 0,
  skipped_count INT UNSIGNED NOT NULL DEFAULT 0,
  manifest_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_cev_source_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consent_evidence_source_documents (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_import_id BIGINT UNSIGNED NULL,
  original_path TEXT NOT NULL,
  source_family VARCHAR(120) NULL,
  document_family VARCHAR(80) NULL,
  procedure_hint VARCHAR(120) NULL,
  title VARCHAR(255) NOT NULL,
  extension VARCHAR(20) NULL,
  size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  sha256 VARCHAR(80) NULL,
  extracted_text MEDIUMTEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'indexed',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_cev_src_doc_import (source_import_id),
  KEY idx_cev_src_doc_family (document_family, procedure_hint, status),
  KEY idx_cev_src_doc_hash (sha256)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consent_evidence_pathway_blueprints (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  blueprint_code VARCHAR(120) NOT NULL,
  name VARCHAR(255) NOT NULL,
  procedure_family VARCHAR(120) NOT NULL DEFAULT 'general',
  description TEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cev_blueprint_code (blueprint_code),
  KEY idx_cev_blueprint_family (procedure_family, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consent_evidence_pathway_steps (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  blueprint_id BIGINT UNSIGNED NOT NULL,
  step_order INT UNSIGNED NOT NULL DEFAULT 0,
  phase_code VARCHAR(80) NOT NULL,
  audience VARCHAR(80) NOT NULL DEFAULT 'patient',
  document_family VARCHAR(80) NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  source_priority VARCHAR(120) NULL,
  required TINYINT(1) NOT NULL DEFAULT 1,
  signature_required TINYINT(1) NOT NULL DEFAULT 0,
  timing_rule VARCHAR(80) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_cev_step_blueprint (blueprint_id, step_order),
  KEY idx_cev_step_family (document_family, audience)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consent_evidence_audit_exports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  export_scope VARCHAR(80) NOT NULL DEFAULT 'case',
  export_path TEXT NULL,
  export_sha256 VARCHAR(80) NULL,
  manifest_json JSON NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'created',
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cev_export_patient (patient_id, created_at),
  KEY idx_cev_export_case (operative_case_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
