-- Baltique OS Core 0.56.0 — Deliverability Center

CREATE TABLE IF NOT EXISTS mail_deliverability_tokens (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  token_hash VARCHAR(128) NOT NULL,
  mail_queue_id BIGINT UNSIGNED NULL,
  communication_hub_message_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  recipient_email VARCHAR(190) NULL,
  subject VARCHAR(255) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  expires_at DATETIME NULL,
  last_event_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_mail_del_token_hash (token_hash),
  KEY idx_mail_del_token_queue (mail_queue_id),
  KEY idx_mail_del_token_email (recipient_email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mail_deliverability_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  token_hash VARCHAR(128) NULL,
  provider_id BIGINT UNSIGNED NULL,
  provider_code VARCHAR(80) NULL,
  mail_queue_id BIGINT UNSIGNED NULL,
  communication_hub_message_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  recipient_email VARCHAR(190) NULL,
  event_type VARCHAR(60) NOT NULL,
  event_source VARCHAR(80) NOT NULL DEFAULT 'system',
  external_message_id VARCHAR(190) NULL,
  url TEXT NULL,
  ip_address VARCHAR(80) NULL,
  user_agent TEXT NULL,
  payload_json JSON NULL,
  event_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_mail_del_event_token (token_hash, event_at),
  KEY idx_mail_del_event_type (event_type, event_at),
  KEY idx_mail_del_event_provider (provider_id, event_at),
  KEY idx_mail_del_event_queue (mail_queue_id, event_at),
  KEY idx_mail_del_event_email (recipient_email, event_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mail_provider_reputation_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider_id BIGINT UNSIGNED NULL,
  provider_code VARCHAR(80) NULL,
  period_date 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,
  bounce_count INT UNSIGNED NOT NULL DEFAULT 0,
  complaint_count INT UNSIGNED NOT NULL DEFAULT 0,
  failed_count INT UNSIGNED NOT NULL DEFAULT 0,
  failure_rate DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
  open_rate DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
  click_rate DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
  reputation_score DECIMAL(8,2) NOT NULL DEFAULT 100.00,
  action_taken VARCHAR(120) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_mail_rep_provider_day (provider_code, period_date),
  KEY idx_mail_rep_day (period_date, reputation_score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mail_deliverability_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_month CHAR(7) NOT NULL,
  period_from DATE NOT NULL,
  period_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,
  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,
  open_rate DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
  click_rate DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
  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_mail_del_report_month (report_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
