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

CREATE TABLE IF NOT EXISTS operative_sites (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_key VARCHAR(120) NOT NULL,
  name VARCHAR(190) NOT NULL,
  address TEXT NULL,
  contact_phone VARCHAR(80) NULL,
  contact_email VARCHAR(190) NULL,
  site_json JSON 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_site_key (site_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_business_entities (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  entity_key VARCHAR(120) NOT NULL,
  entity_type VARCHAR(80) NOT NULL DEFAULT 'coordination_fee',
  display_name VARCHAR(190) NOT NULL,
  nip VARCHAR(40) NULL,
  address TEXT NULL,
  bank_account VARCHAR(80) NULL,
  swift VARCHAR(40) NULL,
  entity_json JSON 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_entity_key (entity_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_procedure_library (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  procedure_key VARCHAR(160) NOT NULL,
  name VARCHAR(190) NOT NULL,
  category VARCHAR(90) NOT NULL,
  default_anesthesia VARCHAR(60) NOT NULL DEFAULT 'general',
  default_stay VARCHAR(60) NOT NULL DEFAULT 'day_surgery',
  coordination_fee_percent DECIMAL(5,2) NOT NULL DEFAULT 10.00,
  library_json JSON NULL,
  source_reference TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  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_procedure_key (procedure_key),
  KEY idx_operative_procedure_active (active, category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_cases (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  appointment_id BIGINT UNSIGNED NULL,
  surgery_case_id BIGINT UNSIGNED NULL,
  procedure_id BIGINT UNSIGNED NOT NULL,
  procedure_variant VARCHAR(190) NULL,
  site_id BIGINT UNSIGNED NOT NULL,
  scheduled_at DATETIME NULL,
  anesthesia_type VARCHAR(60) NOT NULL DEFAULT 'general',
  stay_type VARCHAR(60) NOT NULL DEFAULT 'day_surgery',
  operation_price_gross_cents INT UNSIGNED NOT NULL DEFAULT 0,
  coordination_fee_percent DECIMAL(5,2) NOT NULL DEFAULT 10.00,
  coordination_fee_cents INT UNSIGNED NOT NULL DEFAULT 0,
  coordination_fee_status VARCHAR(60) NOT NULL DEFAULT 'pending',
  hospital_payment_status VARCHAR(60) NOT NULL DEFAULT 'pending',
  labs_status VARCHAR(60) NOT NULL DEFAULT 'missing',
  anesthesia_survey_status VARCHAR(60) NOT NULL DEFAULT 'missing',
  status VARCHAR(60) NOT NULL DEFAULT 'qualified',
  risk_flags_json JSON NULL,
  selected_options_json JSON NULL,
  coordinator_note TEXT 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_operative_cases_patient (patient_id, created_at),
  KEY idx_operative_cases_status (status, scheduled_at),
  KEY idx_operative_cases_scheduled (scheduled_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_case_requirements (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  case_id BIGINT UNSIGNED NOT NULL,
  requirement_type VARCHAR(80) NOT NULL,
  requirement_key VARCHAR(120) NOT NULL,
  title VARCHAR(255) NOT NULL,
  due_at DATETIME NULL,
  status VARCHAR(60) NOT NULL DEFAULT 'missing',
  required TINYINT(1) NOT NULL DEFAULT 1,
  note TEXT NULL,
  metadata_json JSON NULL,
  completed_at DATETIME NULL,
  completed_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_req (case_id, requirement_key),
  KEY idx_op_req_case_status (case_id, status, due_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_case_timeline (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  case_id BIGINT UNSIGNED NOT NULL,
  marker VARCHAR(20) NOT NULL,
  title VARCHAR(255) NOT NULL,
  timeline_kind VARCHAR(80) NOT NULL DEFAULT 'patient',
  due_at DATETIME NULL,
  status VARCHAR(60) NOT NULL DEFAULT 'pending',
  completed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_op_timeline (case_id, marker),
  KEY idx_op_timeline_due (due_at, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_case_tasks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  task_key VARCHAR(120) NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  priority VARCHAR(40) NOT NULL DEFAULT 'normal',
  due_at DATETIME NULL,
  status VARCHAR(60) NOT NULL DEFAULT 'open',
  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_tasks_case (case_id, status, due_at),
  KEY idx_op_tasks_patient (patient_id, 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,
  case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  document_type VARCHAR(80) NOT NULL,
  title VARCHAR(190) NOT NULL,
  content_html MEDIUMTEXT NULL,
  status VARCHAR(60) NOT NULL DEFAULT 'generated',
  signed_at DATETIME NULL,
  signature_json JSON 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_op_doc (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_case_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  message_type VARCHAR(80) NOT NULL,
  channel VARCHAR(40) NOT NULL DEFAULT 'email',
  direction VARCHAR(20) NOT NULL DEFAULT 'outbound',
  subject VARCHAR(190) NULL,
  body MEDIUMTEXT NULL,
  status VARCHAR(60) NOT NULL DEFAULT 'queued',
  scheduled_at DATETIME NULL,
  sent_at DATETIME NULL,
  mail_queue_id BIGINT UNSIGNED NULL,
  communication_queue_id BIGINT UNSIGNED 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_op_msg_case (case_id, status, scheduled_at),
  KEY idx_op_msg_patient (patient_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_upload_links (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  token_hash VARCHAR(128) NOT NULL,
  purpose VARCHAR(80) NOT NULL DEFAULT 'labs_upload',
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  expires_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_upload_token (token_hash),
  KEY idx_op_upload_case (case_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_case_uploads (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  upload_link_id BIGINT UNSIGNED NULL,
  file_kind VARCHAR(60) NOT NULL DEFAULT 'lab',
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  storage_path TEXT NOT NULL,
  mime_type VARCHAR(120) NOT NULL,
  size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  sha256 VARCHAR(80) NULL,
  status VARCHAR(60) NOT NULL DEFAULT 'uploaded',
  reviewed_at DATETIME NULL,
  reviewed_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_op_uploads_case (case_id, created_at),
  KEY idx_op_uploads_patient (patient_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS data_retention_jobs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  job_type VARCHAR(80) NOT NULL,
  patient_id BIGINT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  reason TEXT NULL,
  status VARCHAR(60) NOT NULL DEFAULT 'done',
  summary_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_dr_jobs_patient (patient_id, created_at),
  KEY idx_dr_jobs_type (job_type, 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,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_data_compliance_reports_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
