CREATE TABLE campaigns (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(255),
 subject VARCHAR(255),
 html_body LONGTEXT
);

CREATE TABLE email_queue (
 id INT AUTO_INCREMENT PRIMARY KEY,
 campaign_id INT,
 recipient_email VARCHAR(255),
 status VARCHAR(50),
 attempts INT DEFAULT 0
);

CREATE TABLE email_logs (
 id INT AUTO_INCREMENT PRIMARY KEY,
 queue_id INT,
 smtp_id INT,
 status VARCHAR(50),
 error_message TEXT,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE smtp_health (
 id INT AUTO_INCREMENT PRIMARY KEY,
 smtp_id INT,
 success_count INT DEFAULT 0,
 failure_count INT DEFAULT 0,
 score INT DEFAULT 0
);

CREATE TABLE click_logs (
 id INT AUTO_INCREMENT PRIMARY KEY,
 recipient_email VARCHAR(255),
 url TEXT,
 clicked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE open_logs (
 id INT AUTO_INCREMENT PRIMARY KEY,
 recipient_email VARCHAR(255),
 opened_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);