<?php

defined('_MAILING') or die('Restricted access');

/**
 * type:
 * 1 - domain
 * 2 - ip
 */
$db->query("
    CREATE TABLE IF NOT EXISTS ".PREF."_blacklist (
        id INT(11) NOT NULL AUTO_INCREMENT,
        value VARCHAR(128) NULL,
        type INT(1) NULL,
        PRIMARY KEY (id),
        UNIQUE KEY black (value)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci AUTO_INCREMENT=1 ;
");

/**
 * status:
 * 0 - prepared
 * 1 - during AJAX sending
 * 2 - finished
 * 3 - during background sending
 * 4 - stopped
 * 5 - cron prepared and waiting
 * 6 - during cron sending
 */
$db->query("
    CREATE TABLE IF NOT EXISTS ".PREF."_campaign (
        id int(11) NOT NULL AUTO_INCREMENT,
        name varchar(1024) NOT NULL DEFAULT '',
        recipient_qty int(11) NOT NULL DEFAULT '0',
        id_template int(11) NOT NULL DEFAULT '0',
        id_sender int(11) NOT NULL DEFAULT '0',
        status int(11) NOT NULL DEFAULT '0',
        date_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        date_start timestamp NOT NULL DEFAULT '2000-01-01 00:00:01',
        date_planned_to_start timestamp null,
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci AUTO_INCREMENT=1 ;
");

/**
 * status:
 * 0 - prepared
 * 1 - sent
 * 2 - opened
 * 3 - unsubscribed
 * 4 - not valid email address || sending error
 * 5 - sending process stared
 */
$db->query("
    CREATE TABLE IF NOT EXISTS ".PREF."_campaign_rec_conn (
        id_campaign int(11) NOT NULL DEFAULT '0',
        id_recipient int(11) NOT NULL DEFAULT '0',
        id_smtp int(11) NOT NULL DEFAULT '0',
        date_sent timestamp NOT NULL DEFAULT '2000-01-01 00:00:01',
        status int(11) NOT NULL DEFAULT '0',
        bounced int(1) NOT NULL DEFAULT '0',
        rule_cat varchar(32) NOT NULL DEFAULT '',
        clicked int(11) NOT NULL DEFAULT '0',
        ip_receiver varchar(48) NOT NULL DEFAULT '',
        PRIMARY KEY (id_campaign, id_recipient)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;
");
$db->query("
    CREATE TABLE IF NOT EXISTS ".PREF."_recipient (
        id int(11) NOT NULL AUTO_INCREMENT,
        date_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        date_modify timestamp NOT NULL DEFAULT '2000-01-01 00:00:01',
        person varchar(1024) NOT NULL DEFAULT '',
        email varchar(64) NOT NULL DEFAULT '',
        tag varchar(256) NOT NULL DEFAULT '',
        comment varchar(1024) NOT NULL DEFAULT '',
        txt_only int(1) NOT NULL DEFAULT '0',
        website varchar(512) NOT NULL DEFAULT '',
        f01 varchar(2048) NOT NULL DEFAULT '',
        f02 varchar(2048) NOT NULL DEFAULT '',
        f03 varchar(2048) NOT NULL DEFAULT '',
        verified varchar(16) NOT NULL DEFAULT 'not-verified',
        PRIMARY KEY (id),
        UNIQUE KEY email_2 (email),
        KEY email (email)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci AUTO_INCREMENT=1;
");
$db->query("
    CREATE TABLE IF NOT EXISTS ".PREF."_recipient_tag (
        id int(11) NOT NULL AUTO_INCREMENT,
        name varchar(48) NOT NULL DEFAULT '',
        description varchar(1024) NOT NULL DEFAULT '',
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci AUTO_INCREMENT=1;
");
$db->query("
    CREATE TABLE IF NOT EXISTS ".PREF."_recipient_tag_conn (
        id_recipient int(11) NOT NULL DEFAULT '0',
        id_tag int(11) NOT NULL DEFAULT '0',
        PRIMARY KEY (id_recipient, id_tag)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;
");
$db->query("
    CREATE TABLE IF NOT EXISTS ".PREF."_sender (
        id int(11) NOT NULL AUTO_INCREMENT,
        name varchar(128) NOT NULL DEFAULT '',
        email varchar(128) NOT NULL DEFAULT '',
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci AUTO_INCREMENT=1;
");
$db->query("
    CREATE TABLE IF NOT EXISTS ".PREF."_template (
        id int(11) NOT NULL AUTO_INCREMENT,
        date_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        date_modify timestamp NOT NULL DEFAULT '2000-01-01 00:00:01',
        name varchar(1024) NOT NULL DEFAULT '',
        doctype varchar(512) NOT NULL DEFAULT '',
        subject varchar(1024) NOT NULL DEFAULT '',
        html_header text,
        body_attribs varchar(1024) NOT NULL,
        content_html text,
        content_txt text,
        thumb_filename varchar(128) NOT NULL DEFAULT 'no-picture.png',
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci AUTO_INCREMENT=1;
");
$db->query("
    CREATE TABLE IF NOT EXISTS ".PREF."_template_att (
        id int(11) NOT NULL AUTO_INCREMENT,
        filename varchar(1024) NOT NULL DEFAULT '',
        date_upload timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci AUTO_INCREMENT=1 ;
");
$db->query("
    CREATE TABLE IF NOT EXISTS ".PREF."_template_att_conn (
        id_template int(11) NOT NULL DEFAULT '0',
        id_att int(11) NOT NULL DEFAULT '0'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;
");
$db->query("
    CREATE TABLE IF NOT EXISTS ".PREF."_smtp (
        id int(11) NOT NULL AUTO_INCREMENT,
        name varchar(128) NOT NULL DEFAULT '',
        host varchar(128) NOT NULL DEFAULT '',
        smtpauth int(1) NOT NULL DEFAULT '0',
        username varchar(128) NOT NULL DEFAULT '',
        login varchar(128) NOT NULL DEFAULT '',
        password varchar(128) NOT NULL DEFAULT '',
        replytomail varchar(128) NOT NULL DEFAULT '',
        replytoname varchar(128) NOT NULL DEFAULT '',
        smtpsecure varchar(3) NOT NULL DEFAULT '',
        port int(11) NOT NULL DEFAULT '0',
        maxlimit int(11) NOT NULL DEFAULT '0',
        verifypeer int(1) NOT NULL DEFAULT '0',
        forcesmtp int(1) NOT NULL DEFAULT '0',
        bmh_server varchar(128) NOT NULL DEFAULT '',
        bmh_port int(11) NOT NULL DEFAULT '0',
        bmh_service varchar(128) NOT NULL DEFAULT '',
        bmh_soption varchar(128) NOT NULL DEFAULT '',
        bmh_folder varchar(128) NOT NULL DEFAULT '',
        usedkim int(1) NOT NULL DEFAULT '0',
        dkim_domain varchar(1024) NOT NULL DEFAULT '',
        dkim_private varchar(1024) NOT NULL DEFAULT '',
        dkim_selector varchar(1024) NOT NULL DEFAULT '',
        dkim_passphrase varchar(1024) NOT NULL DEFAULT '',
        dkim_identity varchar(1024) NOT NULL DEFAULT '',
        senderforce int(1) NOT NULL DEFAULT '0',
        sendermail varchar(1024) NOT NULL DEFAULT '',
        senderdescription varchar(1024) NOT NULL DEFAULT '',
        PRIMARY KEY (id)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci AUTO_INCREMENT=1;
");
$db->query("
    CREATE TABLE IF NOT EXISTS ".PREF."_geolocation (
        ip_receiver varchar(48) NOT NULL DEFAULT '',
        user_agent varchar(1024) NOT NULL DEFAULT '',
        city varchar(48) NOT NULL DEFAULT '',
        region varchar(48) NOT NULL DEFAULT '',
        country_code varchar(24) NOT NULL DEFAULT '',
        country_name varchar(48) NOT NULL DEFAULT '',
        continent_code varchar(24) NOT NULL DEFAULT '',
        latitude varchar(64) NOT NULL DEFAULT '',
        longitude varchar(64) NOT NULL DEFAULT '',
        region_code varchar(24) NOT NULL DEFAULT '',
        region_name varchar(48) NOT NULL DEFAULT '',
        currency_code varchar(24) NOT NULL DEFAULT '',
        PRIMARY KEY (ip_receiver)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;
");
$db->query("
    CREATE TABLE IF NOT EXISTS ".PREF."_template_thumb (
        id INT(11) NOT NULL AUTO_INCREMENT,
        filename VARCHAR(128) NOT NULL DEFAULT 'no-picture.png',
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci AUTO_INCREMENT=1;
");
$db->query("
    CREATE TABLE IF NOT EXISTS ".PREF."_recipient_bridge (
        id INT(11) NOT NULL AUTO_INCREMENT ,
        bridge_description VARCHAR(128) NOT NULL DEFAULT '',
        bridge_db_host VARCHAR(128) NOT NULL DEFAULT '',
        bridge_db_user VARCHAR(128) NOT NULL DEFAULT '',
        bridge_db_password VARCHAR(128) NOT NULL DEFAULT '',
        bridge_db_name VARCHAR(128) NOT NULL DEFAULT '',
        source_table_name VARCHAR(128) NOT NULL DEFAULT '',
        source_col_rec_name VARCHAR(128) NOT NULL DEFAULT '',
        source_col_rec_mail VARCHAR(128) NOT NULL DEFAULT '',
        source_col_rec_desc VARCHAR(128) NOT NULL DEFAULT '',
        source_col_rec_website VARCHAR(128) NOT NULL DEFAULT '',
        source_col_rec_f01 VARCHAR(128) NOT NULL DEFAULT '',
        source_col_rec_f02 VARCHAR(128) NOT NULL DEFAULT '',
        source_col_rec_f03 VARCHAR(128) NOT NULL DEFAULT '',
        additional_name VARCHAR(128) NOT NULL DEFAULT '',
        additional_desc VARCHAR(128) NOT NULL DEFAULT '',
        option_override INT(1) NOT NULL DEFAULT '0',
        option_tags VARCHAR(128) NOT NULL DEFAULT '',
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci AUTO_INCREMENT=1;
");
$db->query("
    CREATE TABLE ".PREF."_verify_bulk (
        id int(11) NOT NULL AUTO_INCREMENT,
        verify_id varchar(45) DEFAULT NULL,
        verify_qty int(11) DEFAULT NULL,
        verify_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        verify_message varchar(255) DEFAULT NULL,
        unavailable_smtp int(11) DEFAULT NULL,
        timeout int(11) DEFAULT NULL,
        invalid_domain int(11) DEFAULT NULL,
        invalid_email int(11) DEFAULT NULL,
        no_connect int(11) DEFAULT NULL,
        rejected_email int(11) DEFAULT NULL,
        disposable int(11) DEFAULT NULL,
        accept_all int(11) DEFAULT NULL,
        role int(11) DEFAULT NULL,
        accepted_email int(11) DEFAULT NULL,
        unknown int(11) DEFAULT NULL,
        undeliverable int(11) DEFAULT NULL,
        risky int(11) DEFAULT NULL,
        verified int(11) DEFAULT NULL,
        deliverable int(11) DEFAULT NULL,
        total int(11) DEFAULT NULL,
        percentage int(11) DEFAULT NULL,
        paid int(1) DEFAULT NULL,
        PRIMARY KEY (id)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;
");
