CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS email_lists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS emails (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(190) NOT NULL UNIQUE,
    name VARCHAR(190) DEFAULT NULL,
    list_id INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_emails_list FOREIGN KEY (list_id) REFERENCES email_lists(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(190) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    sender_name VARCHAR(150) NOT NULL,
    sender_email VARCHAR(190) NOT NULL,
    reply_to VARCHAR(190) DEFAULT NULL,
    content_html MEDIUMTEXT NOT NULL,
    status ENUM('brouillon', 'en_attente', 'en_cours', 'terminée') NOT NULL DEFAULT 'brouillon',
    scheduled_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS campaign_emails (
    id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT NOT NULL,
    email_id INT NOT NULL,
    status ENUM('en_attente', 'envoyé', 'échoué', 'rejeté') NOT NULL DEFAULT 'en_attente',
    sent_at DATETIME DEFAULT NULL,
    error_message TEXT DEFAULT NULL,
    opened_at DATETIME DEFAULT NULL,
    click_count INT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_campaign_email (campaign_id, email_id),
    CONSTRAINT fk_campaign_emails_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    CONSTRAINT fk_campaign_emails_email FOREIGN KEY (email_id) REFERENCES emails(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS unsubscribe_list (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(190) NOT NULL UNIQUE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    daily_limit INT NOT NULL DEFAULT 200,
    delay_between_emails INT NOT NULL DEFAULT 2,
    app_url VARCHAR(255) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(190) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    content_html MEDIUMTEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS click_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT NOT NULL,
    email_id INT NOT NULL,
    url TEXT NOT NULL,
    clicked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_click_logs_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    CONSTRAINT fk_click_logs_email FOREIGN KEY (email_id) REFERENCES emails(id) ON DELETE CASCADE
);

INSERT INTO settings (id, daily_limit, delay_between_emails)
SELECT 1, 200, 2
WHERE NOT EXISTS (SELECT 1 FROM settings WHERE id = 1);