-- Baltique OS Core 0.20.0 — Photo Comparison / Missing Views / Versioning
-- Reference migration. Prefer /upgrade_0_20_0.php because it conditionally adds columns.

ALTER TABLE public_patient_uploads
  ADD COLUMN version_parent_upload_id BIGINT UNSIGNED NULL,
  ADD COLUMN version_no INT UNSIGNED NOT NULL DEFAULT 1,
  ADD COLUMN version_note TEXT NULL,
  ADD COLUMN comparison_phase VARCHAR(40) NULL,
  ADD COLUMN captured_at DATE NULL;

CREATE TABLE IF NOT EXISTS photo_comparison_pairs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  procedure_area VARCHAR(80) NULL,
  title VARCHAR(190) NOT NULL,
  before_upload_id BIGINT UNSIGNED NOT NULL,
  after_upload_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_photo_comp_patient (patient_id, created_at),
  KEY idx_photo_comp_before (before_upload_id),
  KEY idx_photo_comp_after (after_upload_id),
  KEY idx_photo_comp_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS photo_version_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  parent_upload_id BIGINT UNSIGNED NOT NULL,
  version_upload_id BIGINT UNSIGNED NOT NULL,
  version_no INT UNSIGNED NOT NULL DEFAULT 1,
  note TEXT NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_photo_version_patient (patient_id, created_at),
  KEY idx_photo_version_parent (parent_upload_id),
  KEY idx_photo_version_upload (version_upload_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS photo_missing_view_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,
  missing_views_json JSON NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'requested',
  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_missing_patient (patient_id, status, created_at),
  KEY idx_photo_missing_link (public_link_id),
  KEY idx_photo_missing_appointment (appointment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
