-- Baltique OS Core 0.32.0 — Marketing Budget Plan / Scenarios / Agency Export

CREATE TABLE IF NOT EXISTS marketing_budget_plans (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period_type VARCHAR(20) NOT NULL DEFAULT 'month',
  period_label VARCHAR(40) NOT NULL,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  label VARCHAR(190) NOT NULL,
  planned_budget_cents INT UNSIGNED NOT NULL DEFAULT 0,
  target_leads INT UNSIGNED NOT NULL DEFAULT 0,
  target_consultations INT UNSIGNED NOT NULL DEFAULT 0,
  target_surgeries INT UNSIGNED NOT NULL DEFAULT 0,
  target_roi_pct DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  target_roas DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  status VARCHAR(40) NOT NULL DEFAULT 'draft',
  plan_json JSON NULL,
  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_plan_period (period_type, period_label),
  KEY idx_marketing_budget_plan_dates (period_from, period_to),
  KEY idx_marketing_budget_plan_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS marketing_budget_scenarios (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period_type VARCHAR(20) NOT NULL DEFAULT 'month',
  period_label VARCHAR(40) NOT NULL,
  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,
  current_budget_cents INT UNSIGNED NOT NULL DEFAULT 0,
  scenario_budget_cents INT UNSIGNED NOT NULL DEFAULT 0,
  budget_change_pct DECIMAL(10,2) NULL,
  expected_leads INT UNSIGNED NOT NULL DEFAULT 0,
  expected_consultations INT UNSIGNED NOT NULL DEFAULT 0,
  expected_surgeries INT UNSIGNED NOT NULL DEFAULT 0,
  expected_revenue_cents INT UNSIGNED NOT NULL DEFAULT 0,
  expected_roi_pct DECIMAL(10,2) NULL,
  expected_roas DECIMAL(10,2) NULL,
  scenario_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_scenarios_period (period_from, period_to),
  KEY idx_marketing_budget_scenarios_dimension (dimension_type, dimension_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS marketing_agency_exports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period_type VARCHAR(20) NOT NULL DEFAULT 'month',
  period_label VARCHAR(40) NOT NULL,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  export_format VARCHAR(20) NOT NULL DEFAULT 'csv',
  recommendations_count INT UNSIGNED NOT NULL DEFAULT 0,
  payload_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_marketing_agency_exports_period (period_from, period_to),
  KEY idx_marketing_agency_exports_format (export_format, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
