-- Baltique OS Core 0.57.0 — Provider Normalization Center

CREATE TABLE IF NOT EXISTS provider_normalization_profiles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider_code VARCHAR(80) NOT NULL,
  provider_name VARCHAR(190) NOT NULL,
  provider_family VARCHAR(80) NOT NULL DEFAULT 'generic',
  description TEXT NULL,
  inbound_secret VARCHAR(190) NULL,
  event_map_json JSON NULL,
  field_map_json JSON NULL,
  sample_payload_json JSON NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  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_provider_norm_code (provider_code),
  KEY idx_provider_norm_active (active, provider_family)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS provider_normalization_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  profile_id BIGINT UNSIGNED NULL,
  provider_code VARCHAR(80) NULL,
  raw_event_type VARCHAR(120) NULL,
  normalized_event_type VARCHAR(60) NOT NULL,
  recipient_email VARCHAR(190) NULL,
  external_message_id VARCHAR(190) NULL,
  token VARCHAR(190) NULL,
  token_hash VARCHAR(128) NULL,
  url TEXT NULL,
  payload_json JSON NULL,
  deliverability_event_id BIGINT UNSIGNED NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'normalized',
  error_message TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_provider_norm_event_provider (provider_code, created_at),
  KEY idx_provider_norm_event_type (normalized_event_type, created_at),
  KEY idx_provider_norm_event_email (recipient_email, created_at),
  KEY idx_provider_norm_event_del (deliverability_event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS provider_normalization_tests (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  profile_id BIGINT UNSIGNED NULL,
  provider_code VARCHAR(80) NULL,
  test_event_type VARCHAR(60) NOT NULL,
  payload_json JSON NULL,
  normalized_json JSON NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'new',
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_provider_norm_test_provider (provider_code, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS provider_normalization_learning (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  provider_code VARCHAR(80) 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,
  bounce_count INT UNSIGNED NOT NULL DEFAULT 0,
  complaint_count INT UNSIGNED NOT NULL DEFAULT 0,
  failed_count INT UNSIGNED NOT NULL DEFAULT 0,
  delivery_rate DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
  failure_rate DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
  engagement_rate DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
  learning_score DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  recommended_priority INT NULL,
  recommendation TEXT NULL,
  action_taken VARCHAR(120) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_provider_norm_learning (period_from, period_to, provider_code),
  KEY idx_provider_norm_learning_score (learning_score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
