-- Baltique OS Core 0.62.0 — Document Assembly Engine

CREATE TABLE IF NOT EXISTS document_assembly_blocks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  block_code VARCHAR(120) NOT NULL,
  version VARCHAR(40) NOT NULL DEFAULT 'v1',
  block_type VARCHAR(80) NOT NULL DEFAULT 'content',
  audience VARCHAR(80) NOT NULL DEFAULT 'patient',
  procedure_key VARCHAR(160) NULL,
  site_key VARCHAR(120) NULL,
  title VARCHAR(255) NOT NULL,
  body_html LONGTEXT NOT NULL,
  body_text LONGTEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  source_label VARCHAR(190) NULL,
  effective_from DATE NULL,
  effective_to DATE 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_doc_block_code_version (block_code, version),
  KEY idx_doc_block_status (status, audience, block_type),
  KEY idx_doc_block_procedure (procedure_key, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS document_assembly_templates (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  template_code VARCHAR(120) NOT NULL,
  template_name VARCHAR(255) NOT NULL,
  document_family VARCHAR(100) NOT NULL DEFAULT 'operative',
  audience VARCHAR(80) NOT NULL DEFAULT 'patient',
  page_size VARCHAR(20) NOT NULL DEFAULT 'A4',
  orientation VARCHAR(20) NOT NULL DEFAULT 'portrait',
  requires_signature TINYINT(1) NOT NULL DEFAULT 0,
  header_html LONGTEXT NULL,
  footer_html LONGTEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  version VARCHAR(40) NOT NULL DEFAULT 'v1',
  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_doc_template_code (template_code),
  KEY idx_doc_template_family (document_family, audience, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS document_assembly_template_blocks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  template_id BIGINT UNSIGNED NOT NULL,
  block_id BIGINT UNSIGNED NOT NULL,
  slot_code VARCHAR(100) NOT NULL DEFAULT 'body',
  sort_order INT NOT NULL DEFAULT 100,
  is_required TINYINT(1) NOT NULL DEFAULT 1,
  conditions_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  UNIQUE KEY uq_doc_template_block (template_id, block_id, slot_code),
  KEY idx_doc_template_block_order (template_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS document_assembly_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  template_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  site_id BIGINT UNSIGNED NULL,
  procedure_id BIGINT UNSIGNED NULL,
  title VARCHAR(255) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'draft',
  assembled_html LONGTEXT NOT NULL,
  assembled_text LONGTEXT NULL,
  variable_snapshot_json JSON NULL,
  source_manifest_json JSON NULL,
  snapshot_sha256 VARCHAR(128) NOT NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  signed_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_doc_run_case (operative_case_id, created_at),
  KEY idx_doc_run_patient (patient_id, created_at),
  KEY idx_doc_run_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS document_assembly_exports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id BIGINT UNSIGNED NOT NULL,
  export_type VARCHAR(40) NOT NULL DEFAULT 'html',
  file_name VARCHAR(255) NOT NULL,
  storage_path VARCHAR(500) NOT NULL,
  file_sha256 VARCHAR(128) NOT NULL,
  file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_doc_export_run (run_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS document_assembly_signatures (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  signer_label VARCHAR(190) NOT NULL DEFAULT 'Pacjent',
  signer_role VARCHAR(80) NOT NULL DEFAULT 'patient',
  signature_data_url LONGTEXT NOT NULL,
  signed_html LONGTEXT NOT NULL,
  signed_sha256 VARCHAR(128) NOT NULL,
  signed_at DATETIME NOT NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_doc_sig_run (run_id, signed_at),
  KEY idx_doc_sig_case (operative_case_id, signed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS document_assembly_audit_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id BIGINT UNSIGNED NULL,
  event_type VARCHAR(80) NOT NULL,
  event_body TEXT NULL,
  metadata_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_doc_audit_run (run_id, created_at),
  KEY idx_doc_audit_type (event_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
