-- Baltique OS Core 0.24.0 — Consultation Briefing / Reminders
-- Reference migration. Prefer /upgrade_0_24_0.php because it is idempotent.

CREATE TABLE IF NOT EXISTS consultation_briefing_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  briefing_date DATE NOT NULL,
  view_type VARCHAR(40) NOT NULL DEFAULT 'all',
  appointments_count INT UNSIGNED NOT NULL DEFAULT 0,
  ready_count INT UNSIGNED NOT NULL DEFAULT 0,
  blocked_count INT UNSIGNED NOT NULL DEFAULT 0,
  critical_count INT UNSIGNED NOT NULL DEFAULT 0,
  payment_pending_count INT UNSIGNED NOT NULL DEFAULT 0,
  photo_missing_count INT UNSIGNED NOT NULL DEFAULT 0,
  intake_missing_count INT UNSIGNED NOT NULL DEFAULT 0,
  snapshot_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_briefing_date (briefing_date, view_type),
  KEY idx_briefing_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consultation_reminder_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  appointment_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  reminder_key VARCHAR(80) NOT NULL,
  channel VARCHAR(30) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'queued',
  queue_id BIGINT UNSIGNED NULL,
  message TEXT NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_consult_reminder_appt (appointment_id, reminder_key, channel),
  KEY idx_consult_reminder_patient (patient_id, created_at),
  KEY idx_consult_reminder_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
