-- Email Marketing Module Database Schema
-- Run this script to set up the database

CREATE DATABASE IF NOT EXISTS email_marketing CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE email_marketing;

-- Subscribers table
CREATE TABLE IF NOT EXISTS subscribers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100),
    status ENUM('pending', 'verified', 'unsubscribed', 'bounced') DEFAULT 'pending',
    verification_token VARCHAR(64),
    token_expires_at DATETIME,
    subscribed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    verified_at DATETIME NULL,
    unsubscribed_at DATETIME NULL,
    ip_address VARCHAR(45),
    tags VARCHAR(500),
    INDEX idx_status (status),
    INDEX idx_email (email),
    INDEX idx_token (verification_token)
) ENGINE=InnoDB;

-- Lists table
CREATE TABLE IF NOT EXISTS lists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- List memberships
CREATE TABLE IF NOT EXISTS list_subscribers (
    list_id INT NOT NULL,
    subscriber_id INT NOT NULL,
    added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (list_id, subscriber_id),
    FOREIGN KEY (list_id) REFERENCES lists(id) ON DELETE CASCADE,
    FOREIGN KEY (subscriber_id) REFERENCES subscribers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Campaigns table
CREATE TABLE IF NOT EXISTS campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    subject VARCHAR(300) NOT NULL,
    preview_text VARCHAR(200),
    from_name VARCHAR(100) NOT NULL,
    from_email VARCHAR(255) NOT NULL,
    reply_to VARCHAR(255),
    html_content LONGTEXT,
    text_content LONGTEXT,
    status ENUM('draft', 'scheduled', 'sending', 'sent', 'paused') DEFAULT 'draft',
    campaign_type ENUM('newsletter', 'promotional', 'announcement', 'welcome') DEFAULT 'newsletter',
    scheduled_at DATETIME NULL,
    sent_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    total_recipients INT DEFAULT 0,
    sent_count INT DEFAULT 0,
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- Campaign lists (which lists a campaign was sent to)
CREATE TABLE IF NOT EXISTS campaign_lists (
    campaign_id INT NOT NULL,
    list_id INT NOT NULL,
    PRIMARY KEY (campaign_id, list_id),
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    FOREIGN KEY (list_id) REFERENCES lists(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Email sends / tracking
CREATE TABLE IF NOT EXISTS email_sends (
    id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT NOT NULL,
    subscriber_id INT NOT NULL,
    status ENUM('queued', 'sent', 'failed', 'bounced') DEFAULT 'queued',
    sent_at DATETIME NULL,
    opened_at DATETIME NULL,
    open_count INT DEFAULT 0,
    click_count INT DEFAULT 0,
    tracking_pixel VARCHAR(64),
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    FOREIGN KEY (subscriber_id) REFERENCES subscribers(id) ON DELETE CASCADE,
    INDEX idx_campaign (campaign_id),
    INDEX idx_subscriber (subscriber_id),
    INDEX idx_tracking (tracking_pixel)
) ENGINE=InnoDB;

-- Email templates
CREATE TABLE IF NOT EXISTS templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(300),
    thumbnail VARCHAR(255),
    html_content LONGTEXT,
    category ENUM('newsletter', 'promotional', 'announcement', 'welcome', 'custom') DEFAULT 'newsletter',
    is_default TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Admin users
CREATE TABLE IF NOT EXISTS admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    role ENUM('super_admin', 'admin', 'editor') DEFAULT 'admin',
    last_login DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Settings table
CREATE TABLE IF NOT EXISTS settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Insert default settings
INSERT INTO settings (setting_key, setting_value) VALUES
('company_name', 'Your Company'),
('company_logo_url', 'assets/images/logo.png'),
('company_website', 'https://yourcompany.com'),
('company_address', '123 Business Street, City, Country'),
('from_name', 'Your Company Newsletter'),
('from_email', 'newsletter@yourcompany.com'),
('reply_to', 'support@yourcompany.com'),
('unsubscribe_base_url', 'https://yourcompany.com/email-marketing/public/'),
('verify_base_url', 'https://yourcompany.com/email-marketing/public/'),
('brand_color', '#2563EB'),
('brand_accent', '#F59E0B')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

-- Insert default admin (password: Admin@123 - CHANGE THIS)
INSERT INTO admin_users (username, email, password_hash, full_name, role) VALUES
('admin', 'admin@yourcompany.com', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Admin', 'super_admin')
ON DUPLICATE KEY UPDATE username = username;

-- Insert default list
INSERT INTO lists (name, description) VALUES
('Main Newsletter', 'Primary newsletter subscriber list'),
('Promotions', 'Promotional offers and deals'),
('Announcements', 'Company announcements and updates')
ON DUPLICATE KEY UPDATE name = name;

-- Insert a sample template
INSERT INTO templates (name, description, category, is_default, html_content) VALUES
('Default Newsletter', 'Clean newsletter template with header and footer', 'newsletter', 1, '')
ON DUPLICATE KEY UPDATE name = name;