-- Baltique OS Core 0.29.0 — Marketing ROI Dashboard
-- Lead → booking → consultation → surgery, acquisition cost, revenue source and campaign ROI.

CREATE TABLE IF NOT EXISTS marketing_campaign_costs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  label VARCHAR(190) NOT NULL,
  source_channel VARCHAR(80) NOT NULL DEFAULT 'public_booking',
  utm_source VARCHAR(160) NULL,
  utm_medium VARCHAR(160) NULL,
  utm_campaign VARCHAR(190) NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  cost_gross_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
  currency VARCHAR(10) NOT NULL DEFAULT 'PLN',
  notes TEXT NULL,
  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),
  KEY idx_marketing_costs_period (period_start, period_end, active),
  KEY idx_marketing_costs_source (source_channel, utm_source, utm_campaign, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS marketing_roi_goals (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  dimension_type VARCHAR(40) NOT NULL DEFAULT 'campaign',
  dimension_key VARCHAR(190) NOT NULL DEFAULT '*',
  label VARCHAR(190) NULL,
  target_roi_pct DECIMAL(8,2) NOT NULL DEFAULT 0.00,
  max_cost_per_lead_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
  max_cost_per_surgery_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
  min_roas DECIMAL(8,2) NOT NULL DEFAULT 0.00,
  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_marketing_roi_goal (dimension_type, dimension_key),
  KEY idx_marketing_roi_goal_active (active, dimension_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS marketing_roi_alerts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  dimension_type VARCHAR(40) NOT NULL,
  dimension_key VARCHAR(190) NOT NULL,
  label VARCHAR(190) NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'normal',
  reason TEXT NULL,
  roi_pct DECIMAL(8,2) NULL,
  roas DECIMAL(8,2) NULL,
  cost_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
  revenue_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  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_marketing_roi_alert_status (status, severity, created_at),
  KEY idx_marketing_roi_alert_period (period_from, period_to),
  KEY idx_marketing_roi_alert_dimension (dimension_type, dimension_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS marketing_roi_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  leads_count INT UNSIGNED NOT NULL DEFAULT 0,
  bookings_count INT UNSIGNED NOT NULL DEFAULT 0,
  consultations_count INT UNSIGNED NOT NULL DEFAULT 0,
  surgery_count INT UNSIGNED NOT NULL DEFAULT 0,
  revenue_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
  cost_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
  roi_pct DECIMAL(8,2) NULL,
  roas DECIMAL(8,2) NULL,
  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_marketing_roi_snapshot_period (period_from, period_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
