-- Baltique OS Core 0.18.0 — Public Intake / Photo Uploads
-- Reference migration. Prefer /upgrade_0_18_0.php because it is idempotent.

CREATE TABLE IF NOT EXISTS public_patient_links (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  appointment_id BIGINT UNSIGNED NULL,
  pretriage_id BIGINT UNSIGNED NULL,
  token_hash VARCHAR(128) NOT NULL,
  purpose VARCHAR(80) NOT NULL DEFAULT 'preconsultation_intake',
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  expires_at DATETIME NULL,
  last_accessed_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_public_patient_links_token_hash (token_hash),
  KEY idx_public_patient_links_patient (patient_id, created_at),
  KEY idx_public_patient_links_appointment (appointment_id),
  KEY idx_public_patient_links_status (status, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS public_patient_intakes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  appointment_id BIGINT UNSIGNED NULL,
  pretriage_id BIGINT UNSIGNED NULL,
  public_link_id BIGINT UNSIGNED NULL,
  token_hash VARCHAR(128) NOT NULL,
  question_schema_key VARCHAR(80) NOT NULL DEFAULT 'general',
  procedure_area VARCHAR(80) NULL,
  answers_json JSON NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'draft',
  submitted_at DATETIME NULL,
  reviewed_at DATETIME NULL,
  reviewed_by_user_id BIGINT UNSIGNED NULL,
  ip_address VARCHAR(64) NULL,
  user_agent VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_public_intakes_patient (patient_id, created_at),
  KEY idx_public_intakes_appointment (appointment_id),
  KEY idx_public_intakes_status (status, created_at),
  KEY idx_public_intakes_token (token_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS public_patient_uploads (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  appointment_id BIGINT UNSIGNED NULL,
  pretriage_id BIGINT UNSIGNED NULL,
  public_link_id BIGINT UNSIGNED NULL,
  token_hash VARCHAR(128) NOT NULL,
  file_kind VARCHAR(40) NOT NULL DEFAULT 'photo',
  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,
  photo_area VARCHAR(120) NULL,
  photo_side VARCHAR(60) NULL,
  status VARCHAR(40) 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_public_uploads_patient (patient_id, created_at),
  KEY idx_public_uploads_appointment (appointment_id),
  KEY idx_public_uploads_token (token_hash),
  KEY idx_public_uploads_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
