-- Newsletter Pro Database Schema for ModuleManager
-- Tables use mm_pro_ prefix to avoid conflicts

-- Business profile (white-label: no MM branding in public)
CREATE TABLE IF NOT EXISTS mm_pro_business (
    business_id INT(10) NOT NULL AUTO_INCREMENT,
    business_name VARCHAR(200) NOT NULL DEFAULT '',
    logo_path VARCHAR(500) NOT NULL DEFAULT '',
    default_sender_name VARCHAR(200) NOT NULL DEFAULT '',
    default_sender_email VARCHAR(254) NOT NULL DEFAULT '',
    reply_to_email VARCHAR(254) NOT NULL DEFAULT '',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (business_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Subscribers
CREATE TABLE IF NOT EXISTS mm_pro_subscriber (
    subscriber_id INT(10) NOT NULL AUTO_INCREMENT,
    email VARCHAR(254) NOT NULL,
    firstname VARCHAR(100) NOT NULL DEFAULT '',
    lastname VARCHAR(100) NOT NULL DEFAULT '',
    company VARCHAR(200) NOT NULL DEFAULT '',
    status ENUM('pending','active','unsubscribed','bounced') NOT NULL DEFAULT 'pending',
    activation_token VARCHAR(64) NOT NULL DEFAULT '',
    unsubscribe_token VARCHAR(64) NOT NULL DEFAULT '',
    meta JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (subscriber_id),
    UNIQUE KEY idx_email (email),
    KEY idx_status (status),
    KEY idx_activation (activation_token),
    KEY idx_unsubscribe (unsubscribe_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Segments for grouping subscribers
CREATE TABLE IF NOT EXISTS mm_pro_segment (
    segment_id INT(10) NOT NULL AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    description TEXT NULL,
    conditions JSON NULL,
    is_public TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (segment_id),
    KEY idx_public (is_public)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Subscriber-Segment relationship
CREATE TABLE IF NOT EXISTS mm_pro_segment_map (
    segment_id INT(10) NOT NULL,
    subscriber_id INT(10) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (segment_id, subscriber_id),
    KEY idx_subscriber (subscriber_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Campaigns (newsletters)
CREATE TABLE IF NOT EXISTS mm_pro_campaign (
    campaign_id INT(10) NOT NULL AUTO_INCREMENT,
    name VARCHAR(500) NOT NULL,
    subject VARCHAR(500) NOT NULL,
    body_html LONGTEXT NULL,
    body_text LONGTEXT NULL,
    template_id INT(10) NULL DEFAULT NULL,
    status ENUM('draft','scheduled','sending','sent','paused') NOT NULL DEFAULT 'draft',
    scheduled_at DATETIME NULL,
    sent_at DATETIME NULL,
    segment_id INT(10) NULL DEFAULT NULL,
    opens INT(10) NOT NULL DEFAULT 0,
    clicks INT(10) NOT NULL DEFAULT 0,
    unsubscribes INT(10) NOT NULL DEFAULT 0,
    bounces INT(10) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (campaign_id),
    KEY idx_status (status),
    KEY idx_segment (segment_id),
    KEY idx_sent (sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Trackable links in campaigns
CREATE TABLE IF NOT EXISTS mm_pro_campaign_link (
    link_id INT(10) NOT NULL AUTO_INCREMENT,
    campaign_id INT(10) NOT NULL,
    original_url VARCHAR(2000) NOT NULL,
    short_code VARCHAR(32) NOT NULL,
    clicks INT(10) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (link_id),
    UNIQUE KEY idx_short_code (short_code),
    KEY idx_campaign (campaign_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sending queue
CREATE TABLE IF NOT EXISTS mm_pro_queue (
    queue_id INT(10) NOT NULL AUTO_INCREMENT,
    campaign_id INT(10) NOT NULL,
    subscriber_id INT(10) NOT NULL,
    status ENUM('queued','sent','failed','bounced') NOT NULL DEFAULT 'queued',
    attempts TINYINT NOT NULL DEFAULT 0,
    error_message VARCHAR(500) NULL,
    sent_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (queue_id),
    UNIQUE KEY idx_campaign_subscriber (campaign_id, subscriber_id),
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tracking events (opens, clicks, unsubscribes)
CREATE TABLE IF NOT EXISTS mm_pro_tracking (
    tracking_id BIGINT(20) NOT NULL AUTO_INCREMENT,
    campaign_id INT(10) NOT NULL,
    subscriber_id INT(10) NOT NULL,
    event_type ENUM('open','click','unsubscribe','bounce') NOT NULL,
    link_id INT(10) NULL DEFAULT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(500) NULL,
    occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (tracking_id),
    KEY idx_campaign_event (campaign_id, event_type),
    KEY idx_subscriber_event (subscriber_id, event_type),
    KEY idx_occurred (occurred_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Email templates
CREATE TABLE IF NOT EXISTS mm_pro_template (
    template_id INT(10) NOT NULL AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    description VARCHAR(500) NULL,
    body_html LONGTEXT NULL,
    is_system TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (template_id),
    KEY idx_system (is_system)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default business
INSERT INTO mm_pro_business (business_name, default_sender_name) VALUES ('My Business', 'My Business') ON DUPLICATE KEY UPDATE business_name=business_name;

-- Insert default template
INSERT INTO mm_pro_template (template_id, name, description, body_html, is_system) VALUES (1, 'Default Template', 'Clean professional template', '<!DOCTYPE html><html><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1"></head><body style="margin:0;padding:20px;font-family:Arial,sans-serif;background:#f4f4f4"><div style="max-width:600px;margin:0 auto;background:#fff;padding:30px;border-radius:8px">{{content}}</div></body></html>', 1) ON DUPLICATE KEY UPDATE template_id=template_id;

-- Insert default segment
INSERT INTO mm_pro_segment (segment_id, name, description, is_public) VALUES (1, 'All Subscribers', 'Default segment with all subscribers', 1) ON DUPLICATE KEY UPDATE segment_id=segment_id;