-- Baltique OS Core 0.19.0 — Private Photo Gallery / Dynamic Consents / Intake Tasks
-- Reference migration. Prefer /upgrade_0_19_0.php because it conditionally adds columns.

ALTER TABLE public_patient_uploads
  ADD COLUMN view_label VARCHAR(120) NULL,
  ADD COLUMN quality_status VARCHAR(40) NOT NULL DEFAULT 'new',
  ADD COLUMN quality_score INT UNSIGNED NULL,
  ADD COLUMN quality_note TEXT NULL;

CREATE TABLE IF NOT EXISTS photo_quality_reviews (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  upload_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  appointment_id BIGINT UNSIGNED NULL,
  checklist_json JSON NULL,
  quality_status VARCHAR(40) NOT NULL DEFAULT 'needs_review',
  quality_score INT UNSIGNED NULL,
  review_note TEXT NULL,
  reviewed_by_user_id BIGINT UNSIGNED NULL,
  reviewed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_photo_quality_upload (upload_id),
  KEY idx_photo_quality_patient (patient_id, quality_status),
  KEY idx_photo_quality_status (quality_status, reviewed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS procedure_consent_templates (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  procedure_area VARCHAR(80) NOT NULL DEFAULT 'common',
  consent_key VARCHAR(120) NOT NULL,
  label VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  required TINYINT(1) NOT NULL DEFAULT 1,
  active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT UNSIGNED NOT NULL DEFAULT 100,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_consent_template_area_key (procedure_area, consent_key),
  KEY idx_consent_templates_active (active, procedure_area, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS public_patient_consents (
  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,
  intake_id BIGINT UNSIGNED NULL,
  consent_key VARCHAR(120) NOT NULL,
  consent_label VARCHAR(255) NOT NULL,
  consent_text TEXT NULL,
  procedure_area VARCHAR(80) NULL,
  required TINYINT(1) NOT NULL DEFAULT 1,
  agreed TINYINT(1) NOT NULL DEFAULT 0,
  agreed_at DATETIME 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_patient_consents_patient (patient_id, created_at),
  KEY idx_patient_consents_intake (intake_id),
  KEY idx_patient_consents_key (consent_key, agreed)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
