-- Schema for the email tracking microservice
-- This is separate from the main application database

CREATE TABLE IF NOT EXISTS unsubscribes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  lead_id INT NOT NULL,
  campaign_id INT NOT NULL,
  unsubscribed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_lead_campaign (lead_id, campaign_id)
);

CREATE TABLE IF NOT EXISTS opens (
  id INT AUTO_INCREMENT PRIMARY KEY,
  lead_id INT NOT NULL,
  campaign_id INT NOT NULL,
  opened_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  user_agent VARCHAR(255),
  ip_address VARCHAR(45),
  INDEX idx_lead_campaign (lead_id, campaign_id),
  INDEX idx_opened_at (opened_at)
);

CREATE TABLE IF NOT EXISTS clicks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  lead_id INT NOT NULL,
  campaign_id INT NOT NULL,
  clicked_url TEXT NOT NULL,
  clicked_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  user_agent VARCHAR(255),
  ip_address VARCHAR(45),
  INDEX idx_lead_campaign (lead_id, campaign_id),
  INDEX idx_clicked_at (clicked_at)
);
