-- Baltique OS Core 0.23.0 — Automated Consultation Day Board
-- Reference migration. Prefer /upgrade_0_23_0.php because it is idempotent.

CREATE TABLE IF NOT EXISTS consultation_day_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  day_date DATE NOT NULL,
  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,
  high_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,
  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_consultation_day_snapshots_day (day_date),
  KEY idx_consultation_day_snapshots_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consultation_day_notes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  appointment_id BIGINT UNSIGNED NOT NULL,
  day_status VARCHAR(60) NOT NULL DEFAULT 'not_started',
  note TEXT 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_consultation_day_notes_appointment (appointment_id),
  KEY idx_consultation_day_notes_status (day_status, updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
