-- Baltique OS Core 0.33.0 — Marketing Execution / Patient Delete

CREATE TABLE IF NOT EXISTS marketing_recommendation_decisions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  recommendation_id BIGINT UNSIGNED NOT NULL,
  decision_type VARCHAR(40) NOT NULL DEFAULT 'manual_review',
  decision_reason TEXT NULL,
  automatic TINYINT(1) NOT NULL DEFAULT 0,
  decided_by_user_id BIGINT UNSIGNED NULL,
  decided_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_marketing_rec_decision_rec (recommendation_id, decided_at),
  KEY idx_marketing_rec_decision_type (decision_type, automatic)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS marketing_agency_implementation_status (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  recommendation_id BIGINT UNSIGNED NULL,
  period_from DATE NULL,
  period_to DATE 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',
  implementation_status VARCHAR(40) NOT NULL DEFAULT 'review',
  current_budget_cents INT UNSIGNED NOT NULL DEFAULT 0,
  recommended_budget_cents INT UNSIGNED NOT NULL DEFAULT 0,
  decision_id BIGINT UNSIGNED NULL,
  agency_note TEXT NULL,
  internal_note TEXT NULL,
  implemented_at DATETIME 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_agency_impl_recommendation (recommendation_id),
  KEY idx_agency_impl_status (implementation_status, updated_at),
  KEY idx_agency_impl_period (period_from, period_to),
  KEY idx_agency_impl_dimension (dimension_type, dimension_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS marketing_agency_execution_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  recipient_email VARCHAR(190) NULL,
  subject VARCHAR(190) NULL,
  body MEDIUMTEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'safe_mode',
  queued TINYINT(1) NOT NULL DEFAULT 0,
  item_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_agency_exec_logs_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS marketing_plan_vs_real_reports (
  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,
  plan_id BIGINT UNSIGNED NULL,
  planned_budget_cents INT UNSIGNED NOT NULL DEFAULT 0,
  actual_cost_cents INT UNSIGNED NOT NULL DEFAULT 0,
  budget_variance_cents INT NOT NULL DEFAULT 0,
  planned_leads INT UNSIGNED NOT NULL DEFAULT 0,
  actual_leads INT UNSIGNED NOT NULL DEFAULT 0,
  planned_consultations INT UNSIGNED NOT NULL DEFAULT 0,
  actual_consultations INT UNSIGNED NOT NULL DEFAULT 0,
  planned_surgeries INT UNSIGNED NOT NULL DEFAULT 0,
  actual_surgeries INT UNSIGNED NOT NULL DEFAULT 0,
  target_roi_pct DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  actual_roi_pct DECIMAL(10,2) NULL,
  target_roas DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  actual_roas DECIMAL(10,2) NULL,
  report_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),
  UNIQUE KEY uq_plan_vs_real_period (period_type, period_label),
  KEY idx_plan_vs_real_dates (period_from, period_to),
  KEY idx_plan_vs_real_variance (budget_variance_cents)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS patient_delete_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_count INT UNSIGNED NOT NULL DEFAULT 0,
  patient_hash VARCHAR(128) NOT NULL,
  reason TEXT NULL,
  delete_files TINYINT(1) NOT NULL DEFAULT 1,
  deleted_summary_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_patient_delete_logs_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
