-- Baltique OS Core 0.34.0 — Operative Patient Path / Data Retention

CREATE TABLE IF NOT EXISTS operative_sites (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(80) NOT NULL,
  name VARCHAR(190) NOT NULL,
  site_type VARCHAR(80) NOT NULL DEFAULT 'external_hospital',
  address VARCHAR(255) NULL,
  city VARCHAR(120) NULL,
  phone VARCHAR(80) NULL,
  email VARCHAR(190) NULL,
  arrival_instruction TEXT NULL,
  hospital_payment_instruction TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_operative_sites_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_procedures (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(120) NOT NULL,
  name VARCHAR(190) NOT NULL,
  category VARCHAR(80) NOT NULL DEFAULT 'general',
  default_anesthesia VARCHAR(80) NOT NULL DEFAULT 'general',
  default_stay VARCHAR(80) NOT NULL DEFAULT 'one_night',
  requires_implants TINYINT(1) NOT NULL DEFAULT 0,
  default_implant_qty DECIMAL(8,2) NOT NULL DEFAULT 0.00,
  source_hint TEXT NULL,
  short_description TEXT NULL,
  tests_text MEDIUMTEXT NULL,
  risk_text MEDIUMTEXT NULL,
  postop_text MEDIUMTEXT NULL,
  faq_text MEDIUMTEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_operative_procedures_code (code),
  KEY idx_operative_procedures_category (category, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_cases (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  case_number VARCHAR(120) NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  procedure_id BIGINT UNSIGNED NOT NULL,
  site_id BIGINT UNSIGNED NULL,
  qualification_date DATE NOT NULL,
  surgery_date DATE NULL,
  operation_variant VARCHAR(255) NULL,
  anesthesia_type VARCHAR(80) NOT NULL DEFAULT 'general',
  stay_type VARCHAR(80) NOT NULL DEFAULT 'one_night',
  operation_gross_cents INT UNSIGNED NOT NULL DEFAULT 0,
  coordination_fee_cents INT UNSIGNED NOT NULL DEFAULT 0,
  coordination_fee_percent DECIMAL(6,2) NOT NULL DEFAULT 10.00,
  implant_required TINYINT(1) NOT NULL DEFAULT 0,
  implant_qty DECIMAL(8,2) NOT NULL DEFAULT 0.00,
  implant_notes TEXT NULL,
  individual_risks TEXT NULL,
  hospital_notes TEXT NULL,
  go_status VARCHAR(20) NOT NULL DEFAULT 'HOLD',
  manual_status_override VARCHAR(20) NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  updated_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_operative_cases_number (case_number),
  KEY idx_operative_cases_patient (patient_id),
  KEY idx_operative_cases_surgery (surgery_date, go_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_case_documents (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  document_type VARCHAR(80) NOT NULL,
  title VARCHAR(190) NOT NULL,
  document_html MEDIUMTEXT NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'generated',
  created_by_user_id BIGINT UNSIGNED NULL,
  updated_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_op_docs_case (operative_case_id, document_type),
  KEY idx_op_docs_patient (patient_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_document_signatures (
  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,
  signer_role VARCHAR(80) NOT NULL DEFAULT 'patient',
  signature_png MEDIUMTEXT NOT NULL,
  signed_by_user_id BIGINT UNSIGNED NULL,
  signed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_op_sign_doc (document_id),
  KEY idx_op_sign_case (operative_case_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_case_tasks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  task_code VARCHAR(100) NOT NULL,
  title VARCHAR(190) NOT NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'normal',
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  due_date DATE NULL,
  note TEXT NULL,
  completed_at DATETIME NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  updated_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_op_tasks_case (operative_case_id, status, due_date),
  KEY idx_op_tasks_patient (patient_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_case_timeline (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(40) NOT NULL,
  label VARCHAR(190) NOT NULL,
  due_date DATE NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_op_timeline_case (operative_case_id, due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_case_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  channel VARCHAR(40) NOT NULL DEFAULT 'email',
  direction VARCHAR(40) NOT NULL DEFAULT 'outbound',
  subject VARCHAR(190) NULL,
  body MEDIUMTEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'draft',
  queue_ref VARCHAR(120) NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_op_messages_case (operative_case_id, created_at)
) 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(80) NOT NULL,
  action_type VARCHAR(60) 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_anon_patient (patient_id, created_at),
  KEY idx_anon_hash (patient_hash)
) 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_compliance_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
