-- Baltique OS Core 0.28.0 — Consultation Strategy / Period Goals / Automated Reports / Source Analytics

CREATE TABLE IF NOT EXISTS consultation_period_goals (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period_type VARCHAR(20) NOT NULL DEFAULT 'month',
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  dimension_type VARCHAR(40) NOT NULL DEFAULT 'overall',
  dimension_key VARCHAR(160) NOT NULL DEFAULT '*',
  label VARCHAR(190) NULL,
  target_consultations INT UNSIGNED NOT NULL DEFAULT 0,
  target_conversion_rate DECIMAL(6,2) NOT NULL DEFAULT 0.00,
  target_surgery_count INT UNSIGNED NOT NULL DEFAULT 0,
  target_surgery_rate DECIMAL(6,2) NOT NULL DEFAULT 0.00,
  target_followup_effectiveness_rate DECIMAL(6,2) NOT NULL DEFAULT 0.00,
  target_revenue_gross_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
  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_period_goal_scope (period_type, period_start, period_end, dimension_type, dimension_key),
  KEY idx_period_goal_period (period_type, period_start, period_end),
  KEY idx_period_goal_dimension (dimension_type, dimension_key, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consultation_auto_report_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_type VARCHAR(40) NOT NULL DEFAULT 'management',
  period_type VARCHAR(40) NOT NULL DEFAULT 'month',
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  provider_id BIGINT UNSIGNED NULL,
  location_id BIGINT UNSIGNED NULL,
  recipient_email VARCHAR(190) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'queued',
  mail_queue_id BIGINT UNSIGNED NULL,
  report_subject VARCHAR(190) NULL,
  report_body MEDIUMTEXT NULL,
  report_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_auto_report_period (report_type, period_start, period_end),
  KEY idx_auto_report_status (status, created_at),
  KEY idx_auto_report_recipient (recipient_email, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consultation_coordinator_alerts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  alert_type VARCHAR(60) NOT NULL DEFAULT 'period_goal',
  period_type VARCHAR(20) NOT NULL DEFAULT 'month',
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  dimension_type VARCHAR(40) NOT NULL DEFAULT 'overall',
  dimension_key VARCHAR(160) NOT NULL DEFAULT '*',
  label VARCHAR(190) NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'normal',
  reason TEXT NULL,
  actual_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',
  recipient_email VARCHAR(190) NULL,
  mail_queue_id BIGINT UNSIGNED NULL,
  details_json JSON NULL,
  resolved_at DATETIME NULL,
  resolved_by_user_id BIGINT UNSIGNED 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_coord_alert_status (status, severity, created_at),
  KEY idx_coord_alert_period (period_type, period_start, period_end),
  KEY idx_coord_alert_dimension (dimension_type, dimension_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS patient_source_attribution (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  appointment_id BIGINT UNSIGNED NULL,
  pretriage_id BIGINT UNSIGNED NULL,
  public_booking_request_id BIGINT UNSIGNED NULL,
  source_channel VARCHAR(80) NOT NULL DEFAULT 'unknown',
  utm_source VARCHAR(160) NULL,
  utm_medium VARCHAR(160) NULL,
  utm_campaign VARCHAR(190) NULL,
  utm_content VARCHAR(190) NULL,
  utm_term VARCHAR(190) NULL,
  landing_url TEXT NULL,
  referrer TEXT NULL,
  captured_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_patient_source_patient (patient_id, captured_at),
  KEY idx_patient_source_appointment (appointment_id),
  KEY idx_patient_source_channel (source_channel, utm_source, utm_campaign)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
