-- Baltique OS Core 0.21.0 — Photo Set Wizard / Reminders / Readiness
-- Reference migration. Prefer /upgrade_0_21_0.php because it is idempotent.

CREATE TABLE IF NOT EXISTS photo_instruction_requests (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  appointment_id BIGINT UNSIGNED NULL,
  public_link_id BIGINT UNSIGNED NULL,
  public_url TEXT NULL,
  procedure_area VARCHAR(80) NOT NULL DEFAULT 'general',
  required_views_json JSON NULL,
  missing_views_json JSON NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'requested',
  reminder_email_enabled TINYINT(1) NOT NULL DEFAULT 1,
  reminder_sms_enabled TINYINT(1) NOT NULL DEFAULT 0,
  reminder_count INT UNSIGNED NOT NULL DEFAULT 0,
  max_reminders INT UNSIGNED NOT NULL DEFAULT 3,
  reminder_interval_hours INT UNSIGNED NOT NULL DEFAULT 48,
  last_reminder_at DATETIME NULL,
  next_reminder_at DATETIME NULL,
  requested_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_photo_instr_patient (patient_id, created_at),
  KEY idx_photo_instr_appointment (appointment_id),
  KEY idx_photo_instr_status (status, next_reminder_at),
  KEY idx_photo_instr_link (public_link_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS photo_readiness_status (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  appointment_id BIGINT UNSIGNED NULL,
  procedure_area VARCHAR(80) NOT NULL DEFAULT 'general',
  required_count INT UNSIGNED NOT NULL DEFAULT 0,
  found_count INT UNSIGNED NOT NULL DEFAULT 0,
  missing_count INT UNSIGNED NOT NULL DEFAULT 0,
  readiness_percent INT UNSIGNED NOT NULL DEFAULT 0,
  readiness_status VARCHAR(40) NOT NULL DEFAULT 'missing',
  missing_views_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_photo_ready_patient (patient_id, updated_at),
  KEY idx_photo_ready_appointment (appointment_id),
  KEY idx_photo_ready_status (readiness_status, updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS photo_reminder_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  photo_instruction_request_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  channels_json JSON NULL,
  message_subject VARCHAR(190) NULL,
  message_body TEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'queued',
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_photo_reminder_patient (patient_id, created_at),
  KEY idx_photo_reminder_request (photo_instruction_request_id),
  KEY idx_photo_reminder_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
