-- Baltique OS Core 0.31.0 — Marketing Budgeting / Recommendations / Quality Drop Alerts

CREATE TABLE IF NOT EXISTS marketing_budget_rules (
  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) NOT NULL,
  min_roi_pct DECIMAL(8,2) NOT NULL DEFAULT 100.00,
  min_roas DECIMAL(8,2) NOT NULL DEFAULT 2.00,
  max_cpl_cents INT UNSIGNED NOT NULL DEFAULT 0,
  max_cost_per_surgery_cents INT UNSIGNED NOT NULL DEFAULT 0,
  min_lead_quality_score INT UNSIGNED NOT NULL DEFAULT 55,
  quality_drop_threshold_points INT UNSIGNED NOT NULL DEFAULT 15,
  increase_budget_pct DECIMAL(8,2) NOT NULL DEFAULT 20.00,
  decrease_budget_pct DECIMAL(8,2) NOT NULL DEFAULT 20.00,
  min_consultations INT UNSIGNED NOT NULL DEFAULT 3,
  pause_when_negative_roi TINYINT(1) NOT NULL DEFAULT 1,
  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_budget_rule (dimension_type, dimension_key),
  KEY idx_marketing_budget_rules_active (active, dimension_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS marketing_budget_recommendations (
  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) NOT NULL,
  recommendation_action VARCHAR(40) NOT NULL DEFAULT 'keep',
  severity VARCHAR(40) NOT NULL DEFAULT 'normal',
  current_budget_cents INT UNSIGNED NOT NULL DEFAULT 0,
  recommended_budget_cents INT UNSIGNED NOT NULL DEFAULT 0,
  recommended_change_pct DECIMAL(8,2) NOT NULL DEFAULT 0.00,
  roi_pct DECIMAL(10,2) NULL,
  roas DECIMAL(10,2) NULL,
  lead_quality_score DECIMAL(8,2) NULL,
  reason TEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  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_marketing_budget_rec_period (period_from, period_to),
  KEY idx_marketing_budget_rec_dimension (dimension_type, dimension_key, status),
  KEY idx_marketing_budget_rec_action (recommendation_action, severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS marketing_quality_drop_alerts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  procedure_key VARCHAR(190) NOT NULL,
  label VARCHAR(190) NOT NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'high',
  current_score DECIMAL(8,2) NULL,
  previous_score DECIMAL(8,2) NULL,
  drop_points DECIMAL(8,2) NULL,
  reason TEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  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_quality_drop_period (period_from, period_to),
  KEY idx_quality_drop_procedure (procedure_key, status),
  KEY idx_quality_drop_severity (severity, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS marketing_budget_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  total_cost_cents INT UNSIGNED NOT NULL DEFAULT 0,
  total_revenue_cents INT UNSIGNED NOT NULL DEFAULT 0,
  roi_pct DECIMAL(10,2) NULL,
  recommendation_count INT UNSIGNED NOT NULL DEFAULT 0,
  increase_count INT UNSIGNED NOT NULL DEFAULT 0,
  decrease_count INT UNSIGNED NOT NULL DEFAULT 0,
  pause_count INT UNSIGNED NOT NULL DEFAULT 0,
  quality_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_budget_snapshots_period (period_from, period_to),
  KEY idx_budget_snapshots_roi (roi_pct)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
