CREATE TABLE IF NOT EXISTS affiliate_clicks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  affiliate_profile_id BIGINT UNSIGNED NOT NULL,
  affiliate_user_id BIGINT UNSIGNED NOT NULL,
  slug VARCHAR(60) NOT NULL,
  ip_hash CHAR(64) NULL,
  user_agent VARCHAR(500) NULL,
  referer VARCHAR(500) NULL,
  landing_path VARCHAR(255) NULL,
  clicked_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_affiliate_clicks_profile_day (affiliate_profile_id, clicked_at),
  KEY idx_affiliate_clicks_slug_day (slug, clicked_at),
  KEY idx_affiliate_clicks_ip_day (ip_hash, clicked_at),
  CONSTRAINT fk_affiliate_clicks_profile FOREIGN KEY (affiliate_profile_id) REFERENCES affiliate_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_affiliate_clicks_affiliate_user FOREIGN KEY (affiliate_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;