-- Baltique OS Core 0.30.0 — Marketing Cost Import / Auto Reports / Lead Quality

CREATE TABLE IF NOT EXISTS marketing_cost_import_batches (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  original_filename VARCHAR(255) NULL,
  delimiter_used VARCHAR(20) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'running',
  imported_count INT UNSIGNED NOT NULL DEFAULT 0,
  skipped_count INT UNSIGNED NOT NULL DEFAULT 0,
  error_count INT UNSIGNED NOT NULL DEFAULT 0,
  error_message TEXT 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_cost_import_batches_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS marketing_cost_import_rows (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  batch_id BIGINT UNSIGNED NOT NULL,
  row_no INT UNSIGNED NOT NULL,
  raw_json JSON NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'imported',
  error_message TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_marketing_cost_import_rows_batch (batch_id, row_no),
  KEY idx_marketing_cost_import_rows_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS marketing_auto_report_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  audience VARCHAR(60) NOT NULL DEFAULT 'management',
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  provider_id BIGINT UNSIGNED NULL,
  recipient_emails TEXT NULL,
  subject VARCHAR(190) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'safe_mode',
  safe_mode TINYINT(1) NOT NULL DEFAULT 1,
  queued_count INT UNSIGNED NOT NULL DEFAULT 0,
  report_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_marketing_auto_report_logs_period (period_from, period_to),
  KEY idx_marketing_auto_report_logs_audience (audience, created_at),
  KEY idx_marketing_auto_report_logs_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS marketing_lead_quality_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  provider_id BIGINT UNSIGNED NULL,
  location_id BIGINT UNSIGNED NULL,
  min_score INT UNSIGNED NOT NULL DEFAULT 60,
  quality_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_marketing_quality_period (period_from, period_to),
  KEY idx_marketing_quality_scope (provider_id, location_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
