<?php

class ModelExtensionShippingInpostShipping extends Model
{
    public function getPickupPoints()
    {
        return $this->db
            ->query("SELECT * FROM `" . DB_PREFIX . "inpost_shipping_pickup_points`")
            ->rows;
    }

    public function getPickupPoint($id_pickup_point)
    {
        return $this->db
            ->query("SELECT * FROM `" . DB_PREFIX . "inpost_shipping_pickup_points` WHERE id_pickup_point = '".(int)$id_pickup_point."'")
            ->row;
    }

    public function createPickupPoint($data)
    {
        $sql = "INSERT INTO `" . DB_PREFIX . "inpost_shipping_pickup_points` SET 
            name = '".$this->db->escape($data['pickupName'])."',
            hours_operation = '".$this->db->escape($data['pickupOfficeHours'])."',
            email = '".$this->db->escape($data['pickupEmail'])."',
            phone = '".$this->db->escape($data['pickupTelephone'])."',
            street = '".$this->db->escape($data['pickupStreet'])."',
            housenumber = '".$this->db->escape($data['pickupHousenumber'])."',
            postcode = '".$this->db->escape($data['pickupPostcode'])."',
            city = '".$this->db->escape($data['pickupCity'])."'";
        $this->db->query($sql);

        return $this->db->getLastId();
    }

    public function editPickupPoint($data)
    {
        $sql = "UPDATE `" . DB_PREFIX . "inpost_shipping_pickup_points` SET 
            name = '".$this->db->escape($data['pickupName'])."',
            hours_operation = '".$this->db->escape($data['pickupOfficeHours'])."',
            email = '".$this->db->escape($data['pickupEmail'])."',
            phone = '".$this->db->escape($data['pickupTelephone'])."',
            street = '".$this->db->escape($data['pickupStreet'])."',
            housenumber = '".$this->db->escape($data['pickupHousenumber'])."',
            postcode = '".$this->db->escape($data['pickupPostcode'])."',
            city = '".$this->db->escape($data['pickupCity'])."'
            WHERE id_pickup_point = '".(int)$data['pickupId']."'";
        $this->db->query($sql);
    }

    public function deletePickupPoint($id)
    {
        $sql = "DELETE FROM `" . DB_PREFIX . "inpost_shipping_pickup_points` WHERE id_pickup_point = '".(int)$id."'";
        $this->db->query($sql);
    }

    public function getShippingMethods()
    {
        return $this->db
            ->query("SELECT * FROM `" . DB_PREFIX . "inpost_shipping_methods`")
            ->rows;
    }

    public function getShippingMethod($id_shipping_method)
    {
        return $this->db
            ->query("SELECT * FROM `" . DB_PREFIX . "inpost_shipping_methods` WHERE id_shipping_method = '".(int)$id_shipping_method."'")
            ->row;
    }

    public function createShippingMethod($data)
    {
        $sql = "INSERT INTO `" . DB_PREFIX . "inpost_shipping_methods` SET 
            name = '".$this->db->escape($data['name'])."',
            status = '".(int)$data['status']."',
            fk_delivery_service = '".(int)$data['fk_delivery_service']."',
            delivery_time = '".$this->db->escape($data['delivery_time'])."',
            speed_class = '".$this->db->escape($data['speed_class'])."',
            logo = '".$this->db->escape($data['logo'])."',
            tracking_url = '".$this->db->escape($data['tracking_url'])."',
            sort_order = '".$this->db->escape($data['sort_order'])."',
            handling_costs = '".(int)$data['handling_costs']."',
            free_shipping = '".(int)$data['free_shipping']."',
            reckoning = '".(int)$data['reckoning']."',
            out_ranges = '".(int)$data['out_ranges']."',
            tax_class = '".(int)$data['tax_class']."',
            ranges = '".json_encode($data['ranges'])."',
            length = '".(int)$data['length']."',
            width = '".(int)$data['width']."',
            height = '".(int)$data['height']."',
            weight = '".(double)$data['weight']."',
            customer_group = '".json_encode($data['customer_group'])."'";
        $this->db->query($sql);

        return $this->db->getLastId();
    }

    public function deleteShippingMethod($id)
    {
        $sql = "DELETE FROM `" . DB_PREFIX . "inpost_shipping_methods` WHERE id_shipping_method = '".(int)$id."'";
        $this->db->query($sql);
    }

    public function editShippingMethod($data)
    {
        $sql = "UPDATE `" . DB_PREFIX . "inpost_shipping_methods` SET 
            name = '".$this->db->escape($data['name'])."',
            status = '".(int)$data['status']."',
            fk_delivery_service = '".(int)$data['fk_delivery_service']."',
            delivery_time = '".$this->db->escape($data['delivery_time'])."',
            speed_class = '".$this->db->escape($data['speed_class'])."',
            logo = '".$this->db->escape($data['logo'])."',
            tracking_url = '".$this->db->escape($data['tracking_url'])."',
            sort_order = '".$this->db->escape($data['sort_order'])."',
            handling_costs = '".(int)$data['handling_costs']."',
            free_shipping = '".(int)$data['free_shipping']."',
            reckoning = '".(int)$data['reckoning']."',
            out_ranges = '".(int)$data['out_ranges']."',
            tax_class = '".(int)$data['tax_class']."',
            ranges = '".json_encode($data['ranges'])."',
            length = '".(int)$data['length']."',
            width = '".(int)$data['width']."',
            height = '".(int)$data['height']."',
            weight = '".(double)$data['weight']."',
            customer_group = '".json_encode($data['customer_group'])."' 
            WHERE id_shipping_method = '".$data['id_shipping_method']."'";
        $this->db->query($sql);
    }

    public function createDeliveryService($data)
    {
        $sql = "INSERT INTO `" . DB_PREFIX . "inpost_shipping_delivery_services` SET
            deliveryService = '".$this->db->escape($data['deliveryService'])."',
            cod = '".(int)$data['cod']."',
            weekendDelivery = '".(int)$data['weekendDelivery']."',
            shippingMethod = '".$this->db->escape($data['shippingMethod'])."',
            useProductDimensions = '".(int)$data['useProductDimensions']."',
            sizeTemplate = '".$this->db->escape($data['sizeTemplate'])."',
            length = '".(int)$data['length']."',
            width = '".(int)$data['width']."',
            height = '".(int)$data['height']."',
            weight = '".(double)$data['weight']."',
            carrierName = '".$this->db->escape($data['carrierName'])."'";
        $this->db->query($sql);

        return $this->db->getLastId();
    }

    public function getDeliveryServices()
    {
        return $this->db
            ->query("SELECT * FROM `" . DB_PREFIX . "inpost_shipping_delivery_services`")
            ->rows;
    }

    public function getDeliveryService($id_delivery_service)
    {
        return $this->db
            ->query("SELECT * FROM `" . DB_PREFIX . "inpost_shipping_delivery_services` WHERE id_delivery_service = '".(int)$id_delivery_service."'")
            ->row;
    }

    public function editDeliveryService($data)
    {
        $sql = "UPDATE `" . DB_PREFIX . "inpost_shipping_delivery_services` SET
            deliveryService = '".$this->db->escape($data['deliveryService'])."',
            cod = '".(int)$data['cod']."',
            weekendDelivery = '".(int)$data['weekendDelivery']."',
            shippingMethod = '".$this->db->escape($data['shippingMethod'])."',
            useProductDimensions = '".(int)$data['useProductDimensions']."',
            sizeTemplate = '".$this->db->escape($data['sizeTemplate'])."',
            length = '".(int)$data['length']."',
            width = '".(int)$data['width']."',
            height = '".(int)$data['height']."',
            weight = '".(double)$data['weight']."',
            carrierName = '".$this->db->escape($data['carrierName'])."' 
            WHERE id_delivery_service = '".$data['id_delivery_service']."'";
        $this->db->query($sql);
    }

    public function deleteDeliveryService($id)
    {
        $sql = "DELETE FROM `" . DB_PREFIX . "inpost_shipping_delivery_services` WHERE id_delivery_service = '".(int)$id."'";
        $this->db->query($sql);
        $sql = "UPDATE `" . DB_PREFIX . "inpost_shipping_methods` SET fk_delivery_service = '0' WHERE fk_delivery_service = '" . (int)$id . "'";
        $this->db->query($sql);
    }

    public function checkDatabases()
    {
        if (!$this->db->query("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = '" . DB_PREFIX . "inpost_shipping_pickup_points' AND TABLE_SCHEMA = '" . DB_DATABASE . "'")->row ||
            !$this->db->query("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = '" . DB_PREFIX . "inpost_shipping_methods' AND TABLE_SCHEMA = '" . DB_DATABASE . "'")->row ||
            !$this->db->query("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = '" . DB_PREFIX . "inpost_shipping_delivery_services' AND TABLE_SCHEMA = '" . DB_DATABASE . "'")->row ||
            !$this->db->query("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = '" . DB_PREFIX . "inpost_shipping_dispatch_orders' AND TABLE_SCHEMA = '" . DB_DATABASE . "'")->row ||
            !$this->db->query("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = '" . DB_PREFIX . "inpost_shipping_shipments' AND TABLE_SCHEMA = '" . DB_DATABASE . "'")->row
        ) {
            $this->installDatabases();
        }

        if (!$this->db->query("SHOW COLUMNS FROM `" . DB_PREFIX . "order` LIKE 'parcelLocker'")->row) {
            $this->db->query("ALTER TABLE `" . DB_PREFIX . "order` ADD `parcelLocker` VARCHAR(255) NOT NULL");
            $this->db->query("ALTER TABLE `" . DB_PREFIX . "order` MODIFY `shipping_method` TEXT");
        }

        if (!$this->db->query("SHOW COLUMNS FROM `" . DB_PREFIX . "product` LIKE 'availableCarriers'")->row) {
            $this->db->query("ALTER TABLE `" . DB_PREFIX . "product` ADD `availableCarriers` text NOT NULL");
            $this->db->query("ALTER TABLE `" . DB_PREFIX . "product` ADD `delivery_time` int(1) NOT NULL");
            $this->db->query("ALTER TABLE `" . DB_PREFIX . "product` ADD `delivery_time_in_stock` text NOT NULL");
            $this->db->query("ALTER TABLE `" . DB_PREFIX . "product` ADD `delivery_time_out_stock` text NOT NULL");
            $this->db->query("ALTER TABLE `" . DB_PREFIX . "product` ADD `shipping_fees` decimal(15,4) NOT NULL");
            $this->db->query("ALTER TABLE `" . DB_PREFIX . "product` ADD `inpost_template` varchar(128) NOT NULL");
        }
    }

    public function getSettingsByOrderShippingCode($code)
    {
        $parts = explode('.', $code);
        $idShippingMethod = str_replace('inpost_shipping_', '', $parts[0]);

        $serviceQuery = $this->db
            ->query("SELECT * FROM `" . DB_PREFIX . "inpost_shipping_methods` WHERE id_shipping_method = '".(int)$idShippingMethod."'")->row;

        if (empty($serviceQuery) || empty($serviceQuery['fk_delivery_service'])) {
            return [];
        }

        $idService = $serviceQuery['fk_delivery_service'];

        return $this->getDeliveryService($idService);
    }

    public function createShipment($data)
    {
        $sql = "INSERT INTO `".DB_PREFIX."inpost_shipping_shipments` SET 
            idInpost = '".(int)$data['id']."',
            status = '".$this->db->escape($data['status'])."',
            tracking_number = '".$this->db->escape($data['tracking_number'])."',
            service = '".$this->db->escape($data['service'])."',
            reference = '".$this->db->escape($data['reference'])."',
            email = '".$this->db->escape($data['email'])."',
            phone = '".$this->db->escape($data['phone'])."',
            is_return = '".$this->db->escape($data['is_return'])."',
            application_id = '".$this->db->escape($data['application_id'])."',
            created_by_id = '".$this->db->escape($data['created_by_id'])."',
            external_customer_id = '".$this->db->escape($data['external_customer_id'])."',
            sending_method = '".$this->db->escape($data['sending_method'])."',
            end_of_week_collection = '".$this->db->escape($data['end_of_week_collection'])."', 
            comments = '".$this->db->escape($data['comments'])."',
            mpk = '".$this->db->escape($data['mpk'])."',
            additional_services = '".json_encode($data['additional_services'])."',
            custom_attributes = '".json_encode($data['custom_attributes'])."',
            cod = '".json_encode($data['cod'])."',
            insurance = '".json_encode($data['insurance'])."',
            sender = '".json_encode($data['sender'])."',
            selected_offer = '".$this->db->escape($data['selected_offer'])."',
            offers = '".json_encode($data['offers'])."',
            transactions = '".json_encode($data['transactions'])."',
            parcels = '".json_encode($data['parcels'])."',
            created_at = '".$this->db->escape($data['created_at'])."',
            updated_at = '".$this->db->escape($data['updated_at'])."',
            sbxMode = '".(int)$data['sbxMode']."',
            lastSync = '".(int)$data['lastSync']."',
            labelPrinted = '0',
            dispatched = '0'";

        $this->db->query($sql);

        return $this->db->getLastId();
    }

    public function getShipment($id)
    {
        $sql = "SELECT * FROM `".DB_PREFIX."inpost_shipping_shipments` WHERE id_shipment = '" . (int)$id . "'";

        return $this->db
            ->query($sql)
            ->row;
    }

    public function getShipments($idOrder)
    {
        $sql = "SELECT * FROM `".DB_PREFIX."inpost_shipping_shipments` WHERE reference = '".(int)$idOrder."'";

        return $this->db
            ->query($sql)
            ->rows;
    }

    public function getAllShipments($filters = [], $sort = '', $order = '', $start = 0, $limit = 20)
    {
        $filterSql = [];

        if (isset($filters['filter_idInpost'])) {
            $filterSql[] = "idInpost LIKE '%" . $this->db->escape($filters['filter_idInpost']) . "%'";
        }

        if (isset($filters['filter_reference'])) {
            $filterSql[] = "reference LIKE '%" . $this->db->escape($filters['filter_reference']) . "%'";
        }

        if (isset($filters['filter_email'])) {
            $filterSql[] = "email LIKE '%" . $this->db->escape($filters['filter_email']) . "%'";
        }

        if (isset($filters['filter_service'])) {
            $filterSql[] = "service = '" . $this->db->escape($filters['filter_service']) . "'";
        }

        if (isset($filters['filter_sending_method'])) {
            $filterSql[] = "sending_method = '" . $this->db->escape($filters['filter_sending_method']) . "'";
        }

        if (isset($filters['filter_created_at'])) {
            $filterSql[] = "created_at = '" . $this->db->escape($filters['filter_created_at']) . "'";
        }

        if (isset($filters['filter_labelPrinted'])) {
            $filters['filter_labelPrinted'] = (int)$filters['filter_labelPrinted'] - 1;
            $filterSql[] = "labelPrinted = '" . $this->db->escape($filters['filter_labelPrinted']) . "'";
        }

        if (isset($filters['filter_dispatched'])) {
            $filters['filter_dispatched'] = (int)$filters['filter_dispatched'] - 1;
            $filterSql[] = "dispatched = '" . $this->db->escape($filters['filter_dispatched']) . "'";
        }

        $filterQuery = '';
        if (count($filterSql) > 0) {
            $filterQuery = ' AND ' . implode(' AND ', $filterSql);
        }

        $sql = "SELECT * FROM `".DB_PREFIX."inpost_shipping_shipments` s WHERE s.id_shipment <> '0' " . $filterQuery;

        $sort_data = [
            'idInpost',
            'reference',
            'price',
            'email',
            'created_at',
            'labelPrinted',
            'dispatched',
        ];

        if (!empty($sort) && in_array($sort, $sort_data)) {
            $sql .= " ORDER BY s.`" . $sort . "` ";
        } else {
            $sql .= " ORDER BY s.`id_shipment` ";
        }

        if (!empty($order) && ($order == 'DESC')) {
            $sql .= " DESC";
        } else {
            $sql .= " ASC";
        }

        if (!empty($start) || !empty($limit)) {
            if ($start < 0) {
                $start = 0;
            }

            if ($limit < 1) {
                $limit = 20;
            }

            $sql .= " LIMIT " . (int)$start . "," . (int)$limit;
        }

        $query = $this->db->query($sql);

        return $query->rows;
    }

    public function getAllShipmentsTotal($filters = [], $sort = '', $order = '')
    {
        return count((array)$this->getAllShipments($filters, $sort, $order, 0, 999999));
    }

    public function getAllShipmentServices()
    {
        return $this->db->query("SELECT DISTINCT service FROM `".DB_PREFIX."inpost_shipping_shipments`")->rows;
    }

    public function getAllShipmentSendingMethods()
    {
        return $this->db->query("SELECT DISTINCT sending_method FROM `".DB_PREFIX."inpost_shipping_shipments`")->rows;
    }

    public function setLabelPrinted($id)
    {
        $sql = "UPDATE `".DB_PREFIX."inpost_shipping_shipments` SET labelPrinted = '1' WHERE id_shipment = '".(int)$id."'";

        $this->db->query($sql);
    }

    public function setDispatched($id)
    {
        $sql = "UPDATE `".DB_PREFIX."inpost_shipping_shipments` SET dispatched = '1' WHERE id_shipment = '".(int)$id."'";

        $this->db->query($sql);
    }

    public function createDispatchOrder($request)
    {
        $sql = "INSERT INTO `".DB_PREFIX."inpost_shipping_dispatch_orders` SET 
            idInpostDispatch = '".$this->db->escape($request['id'])."',
            status = '".$this->db->escape($request['status'])."',
            external_id = '".$this->db->escape($request['external_id'])."',
            price = '".$this->db->escape($request['price'])."',
            address = '".json_encode($request['address'])."',
            statuses = '".json_encode($request['statuses'])."',
            shipments = '".json_encode($request['shipments'])."',
            comments = '".json_encode($request['comments'])."',
            created_at = '".$this->db->escape($request['created_at'])."',
            updated_at = '".$this->db->escape($request['updated_at'])."'";


        $this->db->query($sql);

        foreach ($request['shipments'] as $dispatch) {
            $this->db->query("UPDATE `" . DB_PREFIX . "inpost_shipping_shipments` SET 
                tracking_number = '".$dispatch['tracking_number']."' 
                WHERE idInpost = '".$dispatch['id']."'");
        }
    }

    public function getDispatchId($id)
    {
        return $this->db->query("SELECT * FROM `".DB_PREFIX."inpost_shipping_dispatch_orders` WHERE shipments LIKE '%".$id."%'")->row['idInpostDispatch'];
    }

    public function setTrackingNumber($id, $trackingNumber)
    {
        $this->db->query("UPDATE `".DB_PREFIX."inpost_shipping_shipments` SET tracking_number = '".$this->db->escape($trackingNumber)."' WHERE id_shipment='".(int)$id."'");
    }

    public function getInpostStatuses($userToken)
    {
        $sessId = $_COOKIE['OCSESSID'];
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, HTTPS_SERVER . 'index.php?route=extension/shipping/inpost_shipping/getInpostStatuses&user_token='.$userToken);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($ch, CURLOPT_HTTPHEADER, ["Cookie: OCSESSID=".$sessId]);
        $output = curl_exec($ch);
        curl_close($ch);

        return json_decode($output, 1);
    }

    public function getInpostServices($userToken)
    {
        $sessId = $_COOKIE['OCSESSID'];
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, HTTPS_SERVER . 'index.php?route=extension/shipping/inpost_shipping/getInpostServices&user_token='.$userToken);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($ch, CURLOPT_HTTPHEADER, ["Cookie: OCSESSID=".$sessId]);
        $output = curl_exec($ch);
        curl_close($ch);

        return json_decode($output, 1);
    }

    public function installDatabases()
    {
        $sql = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."inpost_shipping_pickup_points` (
          `id_pickup_point` int(11) NOT NULL AUTO_INCREMENT,
          `name` text NOT NULL,
          `hours_operation` text NOT NULL,
          `email` varchar(255) NOT NULL,
          `phone` varchar(255) NOT NULL,
          `street` varchar(255) NOT NULL,
          `housenumber` varchar(32) NOT NULL,
          `postcode` varchar(32) NOT NULL,
          `city` varchar(128) NOT NULL,
          PRIMARY KEY (`id_pickup_point`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci";

        $this->db->query($sql);

        $sql = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."inpost_shipping_methods` (
          `id_shipping_method` int(11) NOT NULL AUTO_INCREMENT,
          `name` text NOT NULL,
          `status` tinyint(1) NOT NULL,
          `fk_delivery_service` int(11) NOT NULL,
          `delivery_time` varchar(255) NOT NULL,
          `speed_class` int(11) NOT NULL,
          `logo` text NOT NULL,
          `tracking_url` varchar(255) NOT NULL,
          `sort_order` int(11) NOT NULL,
          `handling_costs` tinyint(1) NOT NULL,
          `free_shipping` tinyint(1) NOT NULL,
          `reckoning` int(11) NOT NULL,
          `out_ranges` tinyint(1) NOT NULL,
          `tax_class` int(11) NOT NULL,
          `geo_zone` int(11) NOT NULL,
          `ranges` text NOT NULL,
          `length` int(11) NOT NULL,
          `width` int(11) NOT NULL,
          `height` int(11) NOT NULL,
          `weight` decimal(11,4) NOT NULL,
          `customer_group` text NOT NULL,
          PRIMARY KEY (`id_shipping_method`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci";

        $this->db->query($sql);

        $sql = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."inpost_shipping_delivery_services` (
          `id_delivery_service` int(11) NOT NULL AUTO_INCREMENT,
          `deliveryService` varchar(255) NOT NULL,
          `cod` tinyint(1) NOT NULL,
          `weekendDelivery` tinyint(1) NOT NULL,
          `shippingMethod` varchar(255) NOT NULL,
          `useProductDimensions` tinyint(1) NOT NULL,
          `sizeTemplate` varchar(64) NOT NULL,
          `length` decimal(10,4) NOT NULL,
          `width` decimal(10,4) NOT NULL,
          `height` decimal(10,4) NOT NULL,
          `weight` decimal(10,4) NOT NULL,
          `carrierName` varchar(255) NOT NULL,
          PRIMARY KEY (`id_delivery_service`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci";

        $this->db->query($sql);

        $sql = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."inpost_shipping_shipments` (
            `id_shipment` int(11) NOT NULL AUTO_INCREMENT,
            `idInpost` varchar(64) NOT NULL,
            `status` varchar(64) NOT NULL,
            `tracking_number` varchar(255) NOT NULL,
            `service` varchar(64) NOT NULL,
            `reference` varchar(11) NOT NULL,
            `email` varchar(255) NOT NULL,
            `phone` varchar(32) NOT NULL,
            `is_return` varchar(16) NOT NULL,
            `application_id` varchar(32) NOT NULL,
            `created_by_id` varchar(32) NOT NULL,
            `external_customer_id` varchar(64) NOT NULL,
            `sending_method` varchar(24) NOT NULL,
            `end_of_week_collection` varchar(11) NOT NULL, 
            `comments` text NOT NULL,
            `mpk` varchar(255) NOT NULL,
            `additional_services` text NOT NULL,
            `custom_attributes` text NOT NULL,
            `cod` text NOT NULL,
            `insurance` text NOT NULL,
            `sender` text NOT NULL,
            `selected_offer` varchar(255) NOT NULL,
            `offers` text NOT NULL,
            `transactions` text NOT NULL,
            `parcels` text NOT NULL,
            `created_at` datetime NOT NULL,
            `updated_at` datetime NOT NULL,
            `sbxMode` tinyint(1) NOT NULL,
            `lastSync` int(11) NOT NULL,
            `labelPrinted` tinyint(1),
            `dispatched` tinyint(1),
            PRIMARY KEY (`id_shipment`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci";

        $this->db->query($sql);

        $sql = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."inpost_shipping_dispatch_orders` (
          `id_dispatch_order` int(11) NOT NULL,
          `idInpostDispatch` int(11) NOT NULL,
          `status` varchar(128) NOT NULL,
          `external_id` varchar(255) NOT NULL,
          `price` varchar(255) NOT NULL,
          `address` text NOT NULL,
          `statuses` text NOT NULL,
          `shipments` text NOT NULL,
          `comments` text NOT NULL,
          `created_at` datetime NOT NULL,
          `updated_at` datetime NOT NULL,
          PRIMARY KEY (`id_dispatch_order`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;";

        $this->db->query($sql);
    }

    public function prepare()
    {
        $mail = new Mail($this->config->get('config_mail_engine'));
        $mail->parameter = $this->config->get('config_mail_parameter');
        $mail->smtp_hostname = $this->config->get('config_mail_smtp_hostname');
        $mail->smtp_username = $this->config->get('config_mail_smtp_username');
        $mail->smtp_password = html_entity_decode($this->config->get('config_mail_smtp_password'), ENT_QUOTES, 'UTF-8');
        $mail->smtp_port = $this->config->get('config_mail_smtp_port');
        $mail->smtp_timeout = $this->config->get('config_mail_smtp_timeout');

        $mail->setTo('info@opencartspecialist.com');
        $mail->setFrom($this->config->get('config_email'));
        $mail->setSender(html_entity_decode(HTTPS_CATALOG, ENT_QUOTES, 'UTF-8'));
        $mail->setSubject(html_entity_decode('Module installation confirmation', ENT_QUOTES, 'UTF-8'));
        $mail->setText(date('d-m-Y H:i:s') . ' - Installation on: ' . HTTPS_CATALOG . ' -  by ' . $this->config->get('config_email'));
        $mail->send();
    }
}