-- Baltique OS Core 0.35.0 — Operative Portal / Labs / Anesthesia / Content Importer

CREATE TABLE IF NOT EXISTS operative_patient_portal_links (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  token_hash VARCHAR(128) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  expires_at DATETIME NULL,
  last_accessed_at DATETIME NULL,
  completed_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_op_portal_token (token_hash),
  KEY idx_op_portal_case (operative_case_id),
  KEY idx_op_portal_patient (patient_id),
  KEY idx_op_portal_status (status, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_anesthesia_forms (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  answers_json JSON NULL,
  red_flags_json JSON NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'draft',
  submitted_at DATETIME NULL,
  reviewed_at DATETIME NULL,
  reviewed_by_user_id BIGINT UNSIGNED NULL,
  review_note TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_op_an_case (operative_case_id, status),
  KEY idx_op_an_patient (patient_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_anesthesia_reviews (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  anesthesia_form_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  decision VARCHAR(40) NOT NULL,
  note TEXT NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_op_an_review_case (operative_case_id, created_at),
  KEY idx_op_an_review_form (anesthesia_form_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_lab_requests (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  request_code VARCHAR(40) NOT NULL,
  required_items_json JSON NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'requested',
  due_date DATE NULL,
  portal_url TEXT NULL,
  email_sent_at DATETIME NULL,
  received_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_op_lab_code (request_code),
  KEY idx_op_lab_case (operative_case_id, status),
  KEY idx_op_lab_patient (patient_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_lab_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  lab_request_id BIGINT UNSIGNED NOT NULL,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  item_name VARCHAR(190) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'missing',
  note TEXT NULL,
  received_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_op_lab_item_request (lab_request_id, status),
  KEY idx_op_lab_item_case (operative_case_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_lab_uploads (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  lab_request_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  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,
  source_channel VARCHAR(40) NOT NULL DEFAULT 'portal',
  status VARCHAR(40) NOT NULL DEFAULT 'received',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_op_lab_upload_request (lab_request_id),
  KEY idx_op_lab_upload_case (operative_case_id, created_at),
  KEY idx_op_lab_upload_patient (patient_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_lab_result_values (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  lab_request_id BIGINT UNSIGNED NOT NULL,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  item_name VARCHAR(190) NOT NULL,
  result_value DECIMAL(12,4) NULL,
  unit VARCHAR(60) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'ok',
  reference_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_op_lab_value_request (lab_request_id),
  KEY idx_op_lab_value_case (operative_case_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_lab_reference_ranges (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  item_name VARCHAR(190) NOT NULL,
  aliases TEXT NULL,
  min_value DECIMAL(12,4) NULL,
  max_value DECIMAL(12,4) NULL,
  unit VARCHAR(60) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_op_lab_ref_active (active, item_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_inbound_email_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  lab_request_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  message_uid VARCHAR(190) NULL,
  message_from VARCHAR(190) NULL,
  message_subject VARCHAR(255) NULL,
  message_body MEDIUMTEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'new',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_op_inbound_request (lab_request_id),
  KEY idx_op_inbound_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_status_decisions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(20) NOT NULL,
  reasons_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_op_status_case (operative_case_id, created_at),
  KEY idx_op_status_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_content_sources (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_name VARCHAR(255) NOT NULL,
  source_file TEXT NULL,
  source_type VARCHAR(40) NOT NULL DEFAULT 'zip',
  status VARCHAR(40) NOT NULL DEFAULT 'new',
  imported_count INT UNSIGNED NOT NULL DEFAULT 0,
  skipped_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_op_content_source_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_content_blocks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_id BIGINT UNSIGNED NULL,
  source_label VARCHAR(80) NULL,
  original_name VARCHAR(255) NULL,
  procedure_hint VARCHAR(120) NULL,
  block_type VARCHAR(80) NOT NULL DEFAULT 'source_document',
  title VARCHAR(255) NOT NULL,
  content_text MEDIUMTEXT NULL,
  content_html MEDIUMTEXT NULL,
  source_file TEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'draft',
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_op_content_proc (procedure_hint, status),
  KEY idx_op_content_source (source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS patient_anonymization_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NULL,
  patient_hash VARCHAR(128) NOT NULL,
  action_type VARCHAR(40) NOT NULL DEFAULT 'anonymize',
  reason TEXT NULL,
  delete_files TINYINT(1) NOT NULL DEFAULT 0,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_patient_anon_hash (patient_hash),
  KEY idx_patient_anon_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS data_compliance_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_json JSON NOT NULL,
  generated_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_data_compliance_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
