-- Baltique OS Core 0.27.0 — Consultation Performance / Goals / Alerts

CREATE TABLE IF NOT EXISTS consultation_conversion_goals (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  dimension_type VARCHAR(40) NOT NULL DEFAULT 'doctor',
  dimension_key VARCHAR(160) NOT NULL DEFAULT '*',
  label VARCHAR(190) NULL,
  target_conversion_rate DECIMAL(6,2) NOT NULL DEFAULT 50.00,
  target_surgery_rate DECIMAL(6,2) NOT NULL DEFAULT 25.00,
  target_followup_effectiveness_rate DECIMAL(6,2) NOT NULL DEFAULT 60.00,
  alert_drop_threshold_pp DECIMAL(6,2) NOT NULL DEFAULT 15.00,
  min_consultations INT UNSIGNED NOT NULL DEFAULT 5,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_by_user_id BIGINT UNSIGNED NULL,
  updated_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_goal_dimension (dimension_type, dimension_key),
  KEY idx_conversion_goal_active (active, dimension_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consultation_conversion_alerts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  dimension_type VARCHAR(40) NOT NULL,
  dimension_key VARCHAR(160) NOT NULL,
  label VARCHAR(190) NULL,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'normal',
  alert_reason TEXT NULL,
  actual_conversion_rate DECIMAL(6,2) NOT NULL DEFAULT 0.00,
  target_conversion_rate DECIMAL(6,2) NOT NULL DEFAULT 0.00,
  actual_total INT UNSIGNED NOT NULL DEFAULT 0,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  resolved_at DATETIME NULL,
  resolved_by_user_id BIGINT UNSIGNED NULL,
  details_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),
  KEY idx_conversion_alerts_status (status, severity, created_at),
  KEY idx_conversion_alerts_dimension (dimension_type, dimension_key, period_from),
  KEY idx_conversion_alerts_period (period_from, period_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consultation_performance_snapshots (
  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,
  conversion_rate DECIMAL(6,2) NOT NULL DEFAULT 0.00,
  surgery_rate DECIMAL(6,2) NOT NULL DEFAULT 0.00,
  followup_effectiveness_rate DECIMAL(6,2) NOT NULL DEFAULT 0.00,
  alert_count INT UNSIGNED NOT NULL DEFAULT 0,
  snapshot_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_performance_snapshots_period (period_from, period_to),
  KEY idx_performance_snapshots_provider (provider_id, period_from),
  KEY idx_performance_snapshots_location (location_id, period_from)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
