-- Baltique OS Core 0.22.0 — Consultation Readiness Panel
-- Reference migration. Prefer /upgrade_0_22_0.php because it is idempotent.

CREATE TABLE IF NOT EXISTS consultation_readiness_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  appointment_id BIGINT UNSIGNED NOT NULL,
  readiness_percent INT UNSIGNED NOT NULL DEFAULT 0,
  readiness_status VARCHAR(40) NOT NULL DEFAULT 'missing',
  payment_status VARCHAR(40) NULL,
  photo_status VARCHAR(40) NULL,
  intake_status VARCHAR(40) NULL,
  consent_status VARCHAR(40) NULL,
  task_status VARCHAR(40) NULL,
  components_json JSON NULL,
  missing_json JSON 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_consult_readiness_appointment (appointment_id),
  KEY idx_consult_ready_patient (patient_id, updated_at),
  KEY idx_consult_ready_status (readiness_status, updated_at),
  KEY idx_consult_ready_components (payment_status, photo_status, intake_status, consent_status, task_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
