-- Baltique OS Core 0.26.0 — Consultation Conversion Analytics

CREATE TABLE IF NOT EXISTS consultation_conversion_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  provider_id BIGINT UNSIGNED NULL,
  location_id BIGINT UNSIGNED NULL,
  total_consultations INT UNSIGNED NOT NULL DEFAULT 0,
  converted_count INT UNSIGNED NOT NULL DEFAULT 0,
  conversion_rate DECIMAL(6,2) NOT NULL DEFAULT 0.00,
  surgery_count INT UNSIGNED NOT NULL DEFAULT 0,
  second_consultation_count INT UNSIGNED NOT NULL DEFAULT 0,
  physiotherapy_count INT UNSIGNED NOT NULL DEFAULT 0,
  aesthetic_medicine_count INT UNSIGNED NOT NULL DEFAULT 0,
  avg_days_to_decision DECIMAL(8,2) NULL,
  followup_count INT UNSIGNED NOT NULL DEFAULT 0,
  followup_effectiveness_rate DECIMAL(6,2) NOT NULL DEFAULT 0.00,
  report_json JSON 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_conversion_report_scope (period_from, period_to, provider_id, location_id),
  KEY idx_conversion_period (period_from, period_to),
  KEY idx_conversion_provider (provider_id, period_from),
  KEY idx_conversion_location (location_id, period_from)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consultation_conversion_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  appointment_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  event_type VARCHAR(80) NOT NULL,
  previous_value VARCHAR(190) NULL,
  new_value VARCHAR(190) NULL,
  event_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by_user_id BIGINT UNSIGNED NULL,
  metadata_json JSON NULL,
  PRIMARY KEY (id),
  KEY idx_conversion_events_appointment (appointment_id, event_at),
  KEY idx_conversion_events_patient (patient_id, event_at),
  KEY idx_conversion_events_type (event_type, event_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
