-- Baltique OS Core 0.58.0 — Smart Mail Routing

CREATE TABLE IF NOT EXISTS mail_smart_routes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  route_code VARCHAR(80) NOT NULL,
  route_label VARCHAR(190) NOT NULL,
  description TEXT NULL,
  urgency_rank INT NOT NULL DEFAULT 100,
  provider_policy VARCHAR(40) NOT NULL DEFAULT 'best_score',
  fallback_policy VARCHAR(40) NOT NULL DEFAULT 'allow_all_active',
  require_independent_transport TINYINT(1) NOT NULL DEFAULT 0,
  allow_local_mail_fallback TINYINT(1) NOT NULL DEFAULT 1,
  active TINYINT(1) NOT NULL DEFAULT 1,
  metadata_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_smart_route_code (route_code),
  KEY idx_smart_route_active (active, urgency_rank)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mail_smart_route_providers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  route_id BIGINT UNSIGNED NOT NULL,
  provider_id BIGINT UNSIGNED NULL,
  provider_code VARCHAR(80) NULL,
  rank_order INT NOT NULL DEFAULT 100,
  weight DECIMAL(8,2) NOT NULL DEFAULT 1.00,
  min_reputation_score DECIMAL(8,2) NULL,
  max_failure_rate DECIMAL(8,4) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_smart_route_provider_route (route_id, active, rank_order),
  KEY idx_smart_route_provider_code (provider_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mail_smart_routing_decisions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  route_code VARCHAR(80) NOT NULL,
  detected_by VARCHAR(120) NULL,
  mail_queue_id BIGINT UNSIGNED NULL,
  recipient_email VARCHAR(190) NULL,
  subject VARCHAR(255) NULL,
  selected_provider_id BIGINT UNSIGNED NULL,
  selected_provider_code VARCHAR(80) NULL,
  provider_order_json JSON NULL,
  score_json JSON NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'selected',
  result_provider_code VARCHAR(80) NULL,
  result_status VARCHAR(40) NULL,
  result_error TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_smart_decision_route (route_code, created_at),
  KEY idx_smart_decision_queue (mail_queue_id),
  KEY idx_smart_decision_provider (selected_provider_code, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mail_smart_provider_route_scores (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  route_code VARCHAR(80) NOT NULL,
  provider_id BIGINT UNSIGNED NULL,
  provider_code VARCHAR(80) NOT NULL,
  sample_from DATE NOT NULL,
  sample_to DATE NOT NULL,
  sent_count INT UNSIGNED NOT NULL DEFAULT 0,
  delivered_count INT UNSIGNED NOT NULL DEFAULT 0,
  open_count INT UNSIGNED NOT NULL DEFAULT 0,
  click_count INT UNSIGNED NOT NULL DEFAULT 0,
  failed_count INT UNSIGNED NOT NULL DEFAULT 0,
  bounce_count INT UNSIGNED NOT NULL DEFAULT 0,
  complaint_count INT UNSIGNED NOT NULL DEFAULT 0,
  failure_rate DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
  engagement_rate DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
  smart_score DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  recommendation TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_smart_score_route_provider (route_code, provider_code, sample_from, sample_to),
  KEY idx_smart_score_route (route_code, smart_score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
