-- Baltique OS Core 0.55.0 — Mail Transport Center

CREATE TABLE IF NOT EXISTS mail_transport_providers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider_code VARCHAR(80) NOT NULL,
  provider_name VARCHAR(190) NOT NULL,
  transport_type VARCHAR(40) NOT NULL DEFAULT 'mail',
  priority INT NOT NULL DEFAULT 100,
  active TINYINT(1) NOT NULL DEFAULT 1,
  timeout_sec INT UNSIGNED NOT NULL DEFAULT 12,
  max_attempts INT UNSIGNED NOT NULL DEFAULT 3,
  failure_count INT UNSIGNED NOT NULL DEFAULT 0,
  last_success_at DATETIME NULL,
  last_failure_at DATETIME NULL,
  last_error TEXT NULL,
  config_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_mail_transport_provider_code (provider_code),
  KEY idx_mail_transport_active (active, priority, transport_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mail_transport_delivery_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider_id BIGINT UNSIGNED NULL,
  provider_code VARCHAR(80) NULL,
  transport_type VARCHAR(40) NULL,
  mail_queue_id BIGINT UNSIGNED NULL,
  recipient_email VARCHAR(190) NULL,
  subject VARCHAR(255) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'new',
  duration_ms INT UNSIGNED NULL,
  response_code VARCHAR(80) NULL,
  response_body TEXT NULL,
  error_message TEXT NULL,
  attempt_no INT UNSIGNED NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_mail_transport_log_provider (provider_id, created_at),
  KEY idx_mail_transport_log_queue (mail_queue_id, created_at),
  KEY idx_mail_transport_log_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mail_transport_failover_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  mail_queue_id BIGINT UNSIGNED NULL,
  from_provider_id BIGINT UNSIGNED NULL,
  to_provider_id BIGINT UNSIGNED NULL,
  reason TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_mail_transport_fail_queue (mail_queue_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
