-- Raw tracking hit log (debug + analytics) 

CREATE TABLE IF NOT EXISTS email_tracking_hits (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  tracking_token VARCHAR(64) NOT NULL,
  event_type ENUM('open', 'click') NOT NULL,
  accepted TINYINT(1) NOT NULL DEFAULT 0,
  reject_reason VARCHAR(64) NULL,
  recipient_id BIGINT NULL,
  campaign_id INT NULL,
  user_agent VARCHAR(512) NULL,
  ip_address VARCHAR(64) NULL,
  source VARCHAR(32) NOT NULL DEFAULT 'pixel',
  extra_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_eth_campaign (campaign_id, created_at),
  INDEX idx_eth_token (tracking_token, created_at)
);