<?php
/**
 * 2007-2022 ETS-Soft
 *
 * NOTICE OF LICENSE
 *
 * This file is not open source! Each license that you purchased is only available for 1 wesite only.
 * If you want to use this file on more websites (or projects), you need to purchase additional licenses.
 * You are not allowed to redistribute, resell, lease, license, sub-license or offer our resources to any third party.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade PrestaShop to newer
 * versions in the future. If you wish to customize PrestaShop for your
 * needs please contact us for extra customization service at an affordable price
 *
 * @author ETS-Soft <etssoft.jsc@gmail.com>
 * @copyright  2007-2022 ETS-Soft
 * @license    Valid for 1 website (or project) for each purchase of license
 *  International Registered Trademark & Property of ETS-Soft
 */

$sql = array();

$sql[] = '
    CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'ets_hd_ticket` (
        `id_ets_hd_ticket` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        `id_customer` int(11) UNSIGNED NOT NULL DEFAULT 0,
        `id_employee` int(11) UNSIGNED NOT NULL DEFAULT 0,
        `id_guest` int(11) UNSIGNED NOT NULL DEFAULT 0,
        `id_lang` int(11) UNSIGNED NOT NULL DEFAULT 0,
        `customer_name` varchar(255) DEFAULT NULL,
        `customer_email` varchar(255) DEFAULT NULL,
        `id_product` int(11) UNSIGNED DEFAULT NULL,
        `order_ref` varchar(64) DEFAULT NULL,
        `replied` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
        `staff_replied` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
        `read` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
        `staff_read` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
        `priority` varchar(128) NOT NULL,
        `status` varchar(128) NOT NULL,
        `rating` tinyint(2) UNSIGNED NOT NULL DEFAULT 0,
		`date_add` datetime NOT NULL,
		`date_upd` datetime NOT NULL,
        PRIMARY KEY  (`id_ets_hd_ticket`),
        KEY `idx_id_customer` (`id_customer`),
        KEY `idx_id_employee` (`id_employee`),
        KEY `idx_id_product` (`id_product`)
    ) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8mb4;
';
$sql[] = '
    CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'ets_hd_column` (
        `id_ets_hd_column` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        `name` varchar(255) NOT NULL,
        `required` tinyint(1) UNSIGNED NOT NULL,
        `type` varchar(255) NOT NULL,
        `field_type` varchar(255) NOT NULL,
        `field_length` varchar(255) NOT NULL,
        `display_field` tinyint(1) UNSIGNED NOT NULL,
        `display_field_admin` tinyint(1) UNSIGNED NOT NULL,
        `position` int(11) UNSIGNED NOT NULL,
        `active` tinyint(1) UNSIGNED NOT NULL,
        `default` text DEFAULT NULL,
        PRIMARY KEY  (`id_ets_hd_column`)
    ) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8mb4;
';
$sql[] = '
    CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'ets_hd_column_lang` (
        `id_ets_hd_column` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        `id_lang` int(11) UNSIGNED NOT NULL,
        `title` varchar(255) NOT NULL,
        `placeholder` varchar(255) NOT NULL,
        `description` varchar(255) NOT NULL,
        PRIMARY KEY  (`id_ets_hd_column`, `id_lang`)
    ) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8mb4;
';
$sql[] = '
    CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'ets_hd_option` (
        `id_ets_hd_option` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        `default` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
        PRIMARY KEY  (`id_ets_hd_option`)
    ) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8mb4;
';
$sql[] = '
    CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'ets_hd_option_lang` (
        `id_ets_hd_option` int(11) NOT NULL,
        `id_lang` int(11) NOT NULL,
        `title` text NOT NULL,
        PRIMARY KEY  (`id_ets_hd_option`, `id_lang`)
    ) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8mb4;
';
$sql[] = '
    CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'ets_hd_column_option` (
        `id_ets_hd_column` int(11) UNSIGNED NOT NULL,
        `id_ets_hd_option` int(11) UNSIGNED NOT NULL,
        `position` tinyint(1) UNSIGNED NOT NULL,
        PRIMARY KEY  (`id_ets_hd_column`, `id_ets_hd_option`)
    ) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8mb4;
';
$sql[] = '
    CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'ets_hd_ticket_message` (
        `id_ets_hd_ticket_message` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        `id_ets_hd_ticket` int(11) UNSIGNED NOT NULL,
        `message` text NOT NULL,
        `attachment` varchar(500) DEFAULT NULL,
        `id_customer` int(11) UNSIGNED NOT NULL DEFAULT 0,
        `id_employee` int(11) UNSIGNED NOT NULL DEFAULT 0,
        `is_staff` tinyint(1) UNSIGNED NOT NULL,
        `mark_as_replied` int(11) UNSIGNED NOT NULL,
        `response_time` int(11) UNSIGNED NOT NULL,
		`date_add` datetime NOT NULL,
		`date_upd` datetime NOT NULL,
        PRIMARY KEY  (`id_ets_hd_ticket_message`),
        KEY `idx_id_ets_hd_ticket` (`id_ets_hd_ticket`),
        KEY `idx_id_customer` (`id_customer`),
        KEY `idx_id_employee` (`id_employee`),
        KEY `idx_mark_as_replied` (`mark_as_replied`)
    ) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8mb4;
';
$sql[] = '
    CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'ets_hd_staff` (
        `id_ets_hd_staff` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        `id_customer` int(11) UNSIGNED DEFAULT NULL,
        `id_employee` int(11) UNSIGNED DEFAULT NULL,
        `nick_name` varchar(120) NOT NULL,
        `avatar` varchar(255) NOT NULL,
        `signature` text NOT NULL,
        `active` tinyint(1) UNSIGNED NOT NULL,
        PRIMARY KEY  (`id_ets_hd_staff`),
        UNIQUE KEY `uiq_customer_employee` (`id_customer`,`id_employee`) USING BTREE
    ) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8mb4;
';
$sql[] = '
    CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'ets_hd_customer` (
        `id_customer` int(11) UNSIGNED NOT NULL,
        `avatar` varchar(500) NOT NULL,
        PRIMARY KEY (`id_customer`)
    ) ENGINE = ' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8mb4;
';
$sql[] = '
    CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'ets_hd_department` (
        `id_ets_hd_ticket` int(11) UNSIGNED NOT NULL,
        `id_ets_hd_staff` int(11) UNSIGNED NOT NULL,
        `default` tinyint(1) UNSIGNED DEFAULT NULL,
        PRIMARY KEY (`id_ets_hd_ticket`, `id_ets_hd_staff`),
        UNIQUE KEY `uiq_ticket_default` (`id_ets_hd_ticket`, `default`) USING BTREE
    ) ENGINE = ' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8mb4;
';
$sql[] = 'CREATE TABLE IF NOT EXISTS `'._DB_PREFIX_.'ets_hd_mailqueue` ( 
        `id_ets_hd_mailqueue` INT(11) NOT NULL AUTO_INCREMENT , 
        `id_lang` INT(11) NOT NULL,
        `id_employee` INT(11) NOT NULL , 
        `id_customer` INT(11) NOT NULL , 
        `customer_name` VARCHAR(200) NOT NULL , 
        `from_email` VARCHAR(200) NOT NULL , 
        `from_name` VARCHAR(200) NOT NULL , 
        `email` VARCHAR(200) NOT NULL , 
        `subject` VARCHAR(255) NOT NULL , 
        `content` TEXT NOT NULL , 
        `send_count` TINYINT(3) NOT NULL , 
        `sending_time` DATETIME NOT NULL , 
        `date_add` DATETIME NOT NULL , 
        `fileAttachment` text NOT NULL , 
        INDEX (`id_employee`, `id_customer`),
		INDEX(`email`),
        PRIMARY KEY (`id_ets_hd_mailqueue`)
    ) ENGINE = ' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8mb4;
';
$sql[] = 'CREATE TABLE IF NOT EXISTS `'._DB_PREFIX_.'ets_hd_mailtraciking` ( 
    `id_ets_hd_mailtraciking` INT(11) NOT NULL AUTO_INCREMENT , 
    `id_ets_hd_mailqueue` INT(11) NOT NULL , 
    `id_customer` INT(11) NOT NULL , 
    `id_employee` INT(11) NOT NULL , 
    `customer_name` VARCHAR(200) NOT NULL , 
    `email` VARCHAR(200) NOT NULL , 
    `status` VARCHAR(20) NOT NULL , 
    `queue_date` DATETIME NOT NULL , 
    `date_add` DATETIME NOT NULL , 
    PRIMARY KEY (`id_ets_hd_mailtraciking`),
    INDEX (`id_ets_hd_mailqueue`), INDEX (`id_customer`), INDEX (`id_employee`), INDEX (`status`)) ENGINE = ' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8mb4';
$sql[] ='CREATE TABLE IF NOT EXISTS `'._DB_PREFIX_.'ets_hd_maillog` ( 
    `id_ets_hd_maillog` INT(11) NOT NULL AUTO_INCREMENT ,
    `id_ets_hd_mailqueue` INT(11) NOT NULL , 
    `id_customer` INT(11) NOT NULL , 
    `id_employee` INT(11) NOT NULL , 
    `id_lang` INT(11) NOT NULL , 
    `customer_name` VARCHAR(100) NOT NULL , 
    `email` VARCHAR(200) NOT NULL , 
    `from_email` VARCHAR(200) NOT NULL , 
    `from_name` VARCHAR(200) NOT NULL , 
    `subject` VARCHAR(255) NOT NULL , 
    `content` TEXT NOT NULL , 
    `fileAttachment` TEXT NOT NULL , 
    `status` VARCHAR(20) NOT NULL , 
    `queue_date` DATETIME NOT NULL , 
    `date_add` DATETIME NOT NULL , 
     PRIMARY KEY (`id_ets_hd_maillog`), INDEX (`id_ets_hd_mailqueue`), INDEX (`id_customer`), INDEX (`id_employee`), INDEX (`id_lang`)) ENGINE = ' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8mb4';
foreach ($sql as $query) {
    if (Db::getInstance()->execute($query) == false) {
        return false;
    }
}
