-- Newsletter Pro v2.0 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 LONGTEXT 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 LONGTEXT 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,
    design_json LONGTEXT NULL,
    template_id INT(10) NULL DEFAULT NULL,
    provider_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_provider (provider_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,
    template_type VARCHAR(20) NOT NULL DEFAULT 'wrapper',
    design_json LONGTEXT NULL,
    source_url VARCHAR(2000) NULL,
    is_public TINYINT(1) NOT NULL DEFAULT 1,
    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;

-- Module-owned images used by visual and imported templates
CREATE TABLE IF NOT EXISTS mm_pro_media (
    media_id INT(10) NOT NULL AUTO_INCREMENT,
    public_token CHAR(48) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    original_name VARCHAR(255) NOT NULL DEFAULT '',
    mime_type VARCHAR(50) NOT NULL,
    byte_size INT(10) UNSIGNED NOT NULL,
    width INT(10) UNSIGNED NOT NULL,
    height INT(10) UNSIGNED NOT NULL,
    source_url VARCHAR(2000) NOT NULL DEFAULT '',
    content LONGBLOB NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (media_id),
    UNIQUE KEY idx_public_token (public_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Custom fields for subscribers
CREATE TABLE IF NOT EXISTS mm_pro_custom_field (
    field_id INT(10) NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    label VARCHAR(200) NOT NULL,
    field_type ENUM('text','email','number','date','select','checkbox','radio','textarea') NOT NULL DEFAULT 'text',
    options LONGTEXT NULL,
    is_required TINYINT(1) NOT NULL DEFAULT 0,
    is_public TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT(5) 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 (field_id),
    KEY idx_sort (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Subscriber custom field values
CREATE TABLE IF NOT EXISTS mm_pro_subscriber_field (
    subscriber_id INT(10) NOT NULL,
    field_id INT(10) NOT NULL,
    value TEXT 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, field_id),
    KEY idx_field (field_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Subscriber tags
CREATE TABLE IF NOT EXISTS mm_pro_tag (
    tag_id INT(10) NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    color VARCHAR(7) NOT NULL DEFAULT '#808080',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (tag_id),
    UNIQUE KEY idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- Drag-and-drop email builder widgets
CREATE TABLE IF NOT EXISTS mm_pro_widget (
    widget_id INT(10) NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(50) NOT NULL,
    icon VARCHAR(50) NOT NULL DEFAULT 'cube',
    default_config LONGTEXT NULL,
    is_system TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT(5) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (widget_id),
    KEY idx_widget_sort (is_system, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Widget instances saved in campaign content
CREATE TABLE IF NOT EXISTS mm_pro_widget_instance (
    instance_id INT(10) NOT NULL AUTO_INCREMENT,
    campaign_id INT(10) NULL DEFAULT NULL,
    widget_id INT(10) NOT NULL,
    section_name VARCHAR(50) NOT NULL DEFAULT 'content',
    sort_order INT(5) NOT NULL DEFAULT 0,
    config LONGTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (instance_id),
    KEY idx_campaign (campaign_id),
    KEY idx_widget (widget_id),
    KEY idx_section (section_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Subscriber activity log
CREATE TABLE IF NOT EXISTS mm_pro_activity (
    activity_id BIGINT(20) NOT NULL AUTO_INCREMENT,
    subscriber_id INT(10) NOT NULL,
    campaign_id INT(10) NULL DEFAULT NULL,
    activity_type ENUM('sent','open','click','unsubscribe','bounce','subscribed','unsubscribed') NOT NULL,
    link_id INT(10) NULL DEFAULT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(500) NULL,
    metadata LONGTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (activity_id),
    KEY idx_subscriber (subscriber_id),
    KEY idx_campaign (campaign_id),
    KEY idx_type (activity_type),
    KEY idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Email provider configurations
CREATE TABLE IF NOT EXISTS mm_pro_provider (
    provider_id INT(10) NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    provider_type ENUM('smtp','ses','sendgrid','mailgun','postmark','mail','mailchimp') NOT NULL,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    config LONGTEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (provider_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Provider delivery statistics
CREATE TABLE IF NOT EXISTS mm_pro_delivery_log (
    log_id BIGINT(20) NOT NULL AUTO_INCREMENT,
    provider_id INT(10) NOT NULL,
    campaign_id INT(10) NULL DEFAULT NULL,
    subscriber_id INT(10) NULL DEFAULT NULL,
    message_id VARCHAR(255) NULL,
    status ENUM('sent','failed','bounced') NOT NULL,
    error_message TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (log_id),
    KEY idx_provider (provider_id),
    KEY idx_campaign (campaign_id),
    KEY idx_message (message_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert one default business only when no business profile exists.
INSERT INTO mm_pro_business (business_name, default_sender_name)
SELECT 'My Business', 'My Business'
WHERE NOT EXISTS (SELECT 1 FROM mm_pro_business);

-- 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;

-- Insert default widgets
INSERT INTO mm_pro_widget (widget_id, name, type, icon, default_config, is_system, sort_order) VALUES (1, 'Text Block', 'text', 'T', '{"content":"Enter your text here...","align":"left","font_size":"14px","color":"#333333"}', 1, 1) ON DUPLICATE KEY UPDATE widget_id=widget_id;
INSERT INTO mm_pro_widget (widget_id, name, type, icon, default_config, is_system, sort_order) VALUES (2, 'Image', 'image', 'IMG', '{"src":"","alt":"Image description","link":"","width":"100%","align":"center"}', 1, 2) ON DUPLICATE KEY UPDATE widget_id=widget_id;
INSERT INTO mm_pro_widget (widget_id, name, type, icon, default_config, is_system, sort_order) VALUES (3, 'Button', 'button', 'BTN', '{"text":"Click Here","link":"#","bg_color":"#2d5af0","text_color":"#ffffff","border_radius":"6px","align":"center","width":"auto"}', 1, 3) ON DUPLICATE KEY UPDATE widget_id=widget_id;
INSERT INTO mm_pro_widget (widget_id, name, type, icon, default_config, is_system, sort_order) VALUES (4, 'Social Links', 'social', 'SOC', '{"facebook":"","twitter":"","instagram":"","linkedin":"","youtube":"","align":"center","size":24,"style":"rounded"}', 1, 4) ON DUPLICATE KEY UPDATE widget_id=widget_id;
INSERT INTO mm_pro_widget (widget_id, name, type, icon, default_config, is_system, sort_order) VALUES (5, 'Divider', 'divider', 'DIV', '{"color":"#e4e8f0","width":"100%","style":"solid","margin":"15px"}', 1, 5) ON DUPLICATE KEY UPDATE widget_id=widget_id;
INSERT INTO mm_pro_widget (widget_id, name, type, icon, default_config, is_system, sort_order) VALUES (6, 'Spacer', 'spacer', 'SP', '{"height":"20px"}', 1, 6) ON DUPLICATE KEY UPDATE widget_id=widget_id;
INSERT INTO mm_pro_widget (widget_id, name, type, icon, default_config, is_system, sort_order) VALUES (7, 'Two Columns', 'columns', 'COL', '{"columns":[{"content":""},{"content":""}]}', 1, 7) ON DUPLICATE KEY UPDATE widget_id=widget_id;

-- Insert default tags
INSERT INTO mm_pro_tag (tag_id, name, color) VALUES (1, 'VIP', '#f59e0b') ON DUPLICATE KEY UPDATE tag_id=tag_id;
INSERT INTO mm_pro_tag (tag_id, name, color) VALUES (2, 'New Subscriber', '#2d5af0') ON DUPLICATE KEY UPDATE tag_id=tag_id;
INSERT INTO mm_pro_tag (tag_id, name, color) VALUES (3, 'Engaged', '#18a877') ON DUPLICATE KEY UPDATE tag_id=tag_id;
INSERT INTO mm_pro_tag (tag_id, name, color) VALUES (4, 'Inactive', '#e05252') ON DUPLICATE KEY UPDATE tag_id=tag_id;

-- Insert default mail provider (free, uses PHP mail())
INSERT INTO mm_pro_provider (provider_id, name, provider_type, is_default, config, is_active) VALUES (1, 'Host Mail (Default)', 'mail', 1, '{"method":"mail"}', 1) ON DUPLICATE KEY UPDATE provider_id=provider_id;

