<?php

namespace Opencart\Extension\Alsendo\Database;

require_once DIR_APPLICATION . "model/setting/event.php";
require_once DIR_APPLICATION . "model/setting/extension.php";
require_once DIR_APPLICATION . "model/setting/setting.php";
require_once DIR_APPLICATION . "model/user/user_group.php";

use Opencart\Admin\Model\Setting\Event;
use Opencart\Admin\Model\Setting\Extension;
use Opencart\Admin\Model\Setting\Setting;
use Opencart\Admin\Model\User\UserGroup;
use Opencart\System\Engine\Registry;
use Opencart\System\Library\DB;

class DatabaseMigrator
{
    /** @var Registry */
    protected $registry;

    /** @var DB */
    private $db;

    /** @var string */
    private $dbPrefix;

    /** @var UserGroup */
    private $userGroupModel;

    /** @var Event */
    private $settingEventModel;

    /** @var Extension */
    private $settingExtensionModel;

    /** @var Setting */
    private $settingModel;

    public function __construct(Registry $registry, DB $db, string $dbPrefix) {
        $this->registry = $registry;
        $this->db = $db;
        $this->dbPrefix = $dbPrefix;

        $this->userGroupModel = new UserGroup($this->registry);
        $this->settingEventModel = new Event($this->registry);
        $this->settingExtensionModel = new Extension($this->registry);
        $this->settingModel = new Setting($this->registry);
    }

    public function isInstalled(): bool {
        $tableExists = $this->db->query("SHOW TABLES LIKE '".DB_PREFIX."alsendo_region'");
        return (bool)$tableExists->num_rows;
    }

    public function up(): void {
        $adminUserGroupId = null;
        $permissions = [];
        foreach ($this->userGroupModel->getUserGroups() as $userGroup) {
            if ($userGroup['name'] === 'Administrator') {
                $adminUserGroupId = $userGroup['user_group_id'];
                $permissions = json_decode($userGroup['permission'], true);
                break;
            }
        }

        $this->ensurePermissionsAssigned($adminUserGroupId, $permissions, [
            'extension/alsendo/module/alsendo',
            'extension/alsendo/shipping/alsendo',
            'extension/alsendo/alsendo_order',
            'extension/alsendo/alsendo_shipment',
        ]);


        $this->ensureEventsInstalled();
        $this->settingExtensionModel->install('shipping', 'alsendo', 'alsendo');

        $this->createRegionTable();
        $this->registry->load->model('extension/alsendo/module/alsendo_region');
        $this->registry->model_extension_alsendo_module_alsendo_region->populate();

        $this->createSenderAddressTable();
        $this->createServicesTable();
        $this->createShippingMethodsTable();

        //$this->createOrderDetailsTable();
        $this->registry->load->model('extension/alsendo/module/alsendo_order_details');
        $this->registry->model_extension_alsendo_module_alsendo_order_details->populate();

        $this->registry->load->model('extension/alsendo/module/alsendo_order_details_sender');
        $this->registry->model_extension_alsendo_module_alsendo_order_details_sender->populate();

        $this->registry->load->model('extension/alsendo/module/alsendo_order_details_shipping_address');
        $this->registry->model_extension_alsendo_module_alsendo_order_details_shipping_address->populate();

        $this->registry->load->model('extension/alsendo/module/alsendo_order_details_package_details');
        $this->registry->model_extension_alsendo_module_alsendo_order_details_package_details->populate();

        $this->registry->load->model('extension/alsendo/module/alsendo_order_details_package');
        $this->registry->model_extension_alsendo_module_alsendo_order_details_package->populate();

        $this->registry->load->model('extension/alsendo/module/alsendo_order_details_shipment');
        $this->registry->model_extension_alsendo_module_alsendo_order_details_shipment->populate();

        $this->createOrderShipmentTable();
        $this->createOrderShipmentPackageTable();
        $this->createShippingSettingsTable();
        $this->createAdditionalSettingsTable();
        $this->createSelectedPickupPointsTable();
        $this->createOrderSelectedPickupPointsTable();

        $this->createShipmentStatusTable();
        $this->registry->load->model('extension/alsendo/module/alsendo_shipment_status');
        $this->registry->model_extension_alsendo_module_alsendo_shipment_status->populate();

        $this->createPaymentShipmentMappingTable();

        $this->createLogsTable();

        $this->createLanguagesIfNotExist();
    }

    private function createLanguagesIfNotExist(): void {
        $this->registry->load->model('localisation/language');

        $languages = [
            [
                'name'       => 'Polish',
                'code'       => 'pl-pl',
                'locale'     => 'pl_PL.UTF-8,pl_PL,polish',
                'extension'  => '',
                'sort_order' => 1,
                'status'     => 1
            ],
            [
                'name'       => 'Romanian',
                'code'       => 'ro-ro',
                'locale'     => 'ro_RO.UTF-8,ro_RO,romanian',
                'extension'  => '',
                'sort_order' => 1,
                'status'     => 1
            ],
            [
                'name'       => 'Czech',
                'code'       => 'cs-cz',
                'locale'     => 'cs_CZ.UTF-8,cs_CZ,czech',
                'extension'  => '',
                'sort_order' => 1,
                'status'     => 1
            ]
        ];

        foreach ($languages as $language) {
            if (empty($this->registry->model_localisation_language->getLanguageByCode($language['code']))) {
                $this->registry->model_localisation_language->addLanguage($language);
            }
        }
    }

    public function down(): void
    {
        $this->removeAllEvents();
        $this->removeAllPermissions();
        $this->removeExtension();
        $this->dropAllTables();
        $this->removeExtensionPaths();
    }

    private function createRegionTable(): void
    {
        $this->db->query(
            "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."alsendo_region` (
            `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
            `name` varchar(255) NOT NULL,
            `code` varchar(255) NOT NULL,
            `currency_id` int(11) NOT NULL,
            `country_id` int(11) NOT NULL,
            `active` tinyint(1) NOT NULL DEFAULT 0,
            PRIMARY KEY (`id`),
            CONSTRAINT `fk_alsendo_region_currency`
                FOREIGN KEY (`currency_id`) 
                REFERENCES `" . DB_PREFIX . "currency` (`currency_id`) 
                ON DELETE RESTRICT 
                ON UPDATE CASCADE,
            CONSTRAINT `fk_alsendo_region_country`
                FOREIGN KEY (`country_id`) 
                REFERENCES `" . DB_PREFIX . "country` (`country_id`) 
                ON DELETE RESTRICT 
                ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;"
        );
    }

    private function createSenderAddressTable(): void
    {
        $tableName = sprintf('%salsendo_sender_address_templates', $this->dbPrefix);

        $tableExists = $this->db->query("SHOW TABLES LIKE '" . $tableName . "'");

        if (!$tableExists->num_rows) {
            $sql = "CREATE TABLE `" . $tableName . "` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `external_id` varchar(255) NULL DEFAULT NULL,
                `address_type` varchar(255) NOT NULL DEFAULT 'company',
                `company_name` varchar(255) NOT NULL,
                `first_name` varchar(255) NOT NULL,
                `last_name` varchar(255) NOT NULL,
                `full_name` varchar(255) NOT NULL,
                `street` varchar(255) DEFAULT NULL,
                `building_number` varchar(50) NOT NULL,
                `apartment_number` varchar(50) DEFAULT NULL,
                `block` varchar(50) NULL DEFAULT NULL,
                `entrance` varchar(50) NULL DEFAULT NULL,
                `floor` varchar(50) NULL DEFAULT NULL,
                `postal_code` varchar(20) NOT NULL,
                `city` varchar(255) NOT NULL,
                `country` varchar(2) NOT NULL,
                `contact_person` varchar(255) NOT NULL,
                `phone_number` varchar(50) NOT NULL,
                `email` varchar(255) NOT NULL,
                `bank_account_number` varchar(255) NOT NULL,
                `bank_code` varchar(50) NULL DEFAULT NULL,
                `additional_bank_account_number` varchar(255) NULL DEFAULT NULL,
                `pickup_point` TEXT DEFAULT NULL,
                `main` tinyint(1) NOT NULL DEFAULT 0,
                `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
                `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                INDEX `idx_main` (`main`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

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

    private function createServicesTable(): void
    {
        $tableName = sprintf('%salsendo_services', $this->dbPrefix);

        $tableExists = $this->db->query("SHOW TABLES LIKE '" . $tableName . "'");

        if (!$tableExists->num_rows) {
            $sql = "CREATE TABLE `" . $tableName . "` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `external_id` varchar(255) NOT NULL,
            `region_id` int(11) UNSIGNED DEFAULT NULL,
            `is_selected` tinyint(1) NOT NULL DEFAULT 0,
            `name` varchar(255) NOT NULL,
            `supplier` varchar(255) DEFAULT NULL,
            `map_operator` varchar(255) DEFAULT NULL,
            `to_point` tinyint(1) DEFAULT NULL,
            `comment` text DEFAULT NULL,
            `point_to_point` tinyint(1) NULL DEFAULT NULL,
            `door_to_point` tinyint(1) NULL DEFAULT NULL,
            `point_to_door` tinyint(1) NULL DEFAULT NULL,
            `door_to_door` tinyint(1) NULL DEFAULT NULL,
            `logo_url` varchar(255) DEFAULT NULL,
            `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
            `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`),
            INDEX `idx_name` (`name`),
            INDEX `idx_supplier` (`supplier`),
            CONSTRAINT `fk_services_region`
                FOREIGN KEY (`region_id`) 
                REFERENCES `" . DB_PREFIX . "alsendo_region` (`id`) 
                ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

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

    private function createShippingMethodsTable(): void
    {
        $tableName = sprintf('%salsendo_shipping_methods', $this->dbPrefix);

        $tableExists = $this->db->query("SHOW TABLES LIKE '" . $tableName . "'");

        if (!$tableExists->num_rows) {
            $sql = "CREATE TABLE `" . $tableName . "` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `service_id` int(11) DEFAULT NULL,
                `region_id` int(11) UNSIGNED DEFAULT NULL,
                `name` varchar(255) DEFAULT NULL,
                `price` decimal(10,2) DEFAULT NULL,
                `has_map` tinyint(1) NOT NULL DEFAULT 0,
                `shipping_via_pickup_point` tinyint(1) NULL DEFAULT NULL,
                `pickup_request` tinyint(1) NULL DEFAULT NULL,
                `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
                `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                INDEX `idx_service_id` (`service_id`),
                FOREIGN KEY (`service_id`) REFERENCES `" . \DB_PREFIX . "alsendo_services` (`id`) ON DELETE SET NULL,
                CONSTRAINT `fk_shipping_methods_region`
                    FOREIGN KEY (`region_id`) 
                    REFERENCES `" . DB_PREFIX . "alsendo_region` (`id`) 
                    ON DELETE SET NULL 
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

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

    private function createOrderDetailsTable(): void
    {
        $tableName = sprintf('%salsendo_order_details', $this->dbPrefix);

        $tableExists = $this->db->query("SHOW TABLES LIKE '" . $tableName . "'");

        if (!$tableExists->num_rows) {
            $sql = "CREATE TABLE `" . $tableName . "` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `region_id` int(11) UNSIGNED DEFAULT NULL,
                `order_id` int(11) NOT NULL,
                `data` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
                `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                UNIQUE KEY `unique_id_order` (`id`, `order_id`),
                CONSTRAINT `fk_order_details_order` 
                    FOREIGN KEY (`order_id`)
                    REFERENCES `" . \DB_PREFIX . "order` (`order_id`)
                    ON DELETE CASCADE,
                CONSTRAINT `fk_order_details_region`
                    FOREIGN KEY (`region_id`) 
                    REFERENCES `" . DB_PREFIX . "alsendo_region` (`id`) 
                    ON DELETE SET NULL 
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

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

    private function createOrderShipmentTable(): void
    {
        $tableName = sprintf('%salsendo_order_shipment', $this->dbPrefix);

        $tableExists = $this->db->query("SHOW TABLES LIKE '" . $tableName . "'");

        if (!$tableExists->num_rows) {
            $sql = "CREATE TABLE `" . $tableName . "` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `region_id` int(11) UNSIGNED DEFAULT NULL,
                `order_id` int(11) NOT NULL,
                `external_id` varchar(255) DEFAULT NULL,
                `status` varchar(255) DEFAULT NULL,
                `data` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
                `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                CONSTRAINT `fk_order_shipment_order` FOREIGN KEY (`order_id`) REFERENCES `".$this->dbPrefix."order` (`order_id`) ON DELETE CASCADE,
                CONSTRAINT `fk_order_shipment_region`
                    FOREIGN KEY (`region_id`) 
                    REFERENCES `" . DB_PREFIX . "alsendo_region` (`id`) 
                    ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

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

    private function createShippingSettingsTable(): void
    {
        $tableName = sprintf('%salsendo_shipping_settings_templates', $this->dbPrefix);

        $tableExists = $this->db->query("SHOW TABLES LIKE '" . $tableName . "'");

        if (!$tableExists->num_rows) {
            $sql = "CREATE TABLE `" . $tableName . "` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `name` varchar(255) NOT NULL,
                `default_service` varchar(255) DEFAULT NULL,
                `package_type` varchar(50) NOT NULL DEFAULT 'standard',
                `pickup_type` varchar(50) DEFAULT NULL,
                `length` int(11) NOT NULL DEFAULT 0,
                `width` int(11) NOT NULL DEFAULT 0,
                `height` int(11) NOT NULL DEFAULT 0,
                `weight` int(11) NOT NULL DEFAULT 0,
                `cash_on_delivery` int(11) DEFAULT NULL,
                `declared_value` int(11) DEFAULT NULL,
                `default_content` varchar(255) DEFAULT NULL,
                `default_content_custom_text` varchar(255) DEFAULT NULL,
                `non_standard` tinyint(1) NOT NULL DEFAULT 0,
                `main` tinyint(1) NOT NULL DEFAULT 0,
                `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
                `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                INDEX `idx_main` (`main`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

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

    private function createAdditionalSettingsTable(): void
    {
        $tableName = sprintf('%salsendo_additional_settings', $this->dbPrefix);

        $tableExists = $this->db->query("SHOW TABLES LIKE '" . $tableName . "'");

        if (!$tableExists->num_rows) {
            $sql = "CREATE TABLE `" . $tableName . "` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `inpost_default_point` text DEFAULT NULL,
                `dhl_default_point` text DEFAULT NULL,
                `orlen_default_point` text DEFAULT NULL,
                `poczta_polska_default_point` text DEFAULT NULL,
                `dpd_default_point` text DEFAULT NULL,
                `ups_default_point` text DEFAULT NULL,
                `balikovna_default_point` text DEFAULT NULL,
                `gls_default_point` text DEFAULT NULL,
                `ppl_default_point` text DEFAULT NULL,
                `zasilkovna_default_point` text DEFAULT NULL,
                `sameday_default_point` text DEFAULT NULL,
                `fan_courier_default_point` text DEFAULT NULL,
                `cargus_default_point` text DEFAULT NULL,
                `pickup_hour_from` varchar(10) DEFAULT NULL,
                `pickup_hour_to` varchar(10) DEFAULT NULL,
                `auto_declared_value` tinyint(1) NOT NULL DEFAULT 0,
                `sameday_dispatch` tinyint(1) NOT NULL DEFAULT 0,
                `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
                `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

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

    private function createOrderShipmentPackageTable(): void
    {
        $tableName = sprintf('%salsendo_order_shipment_package', $this->dbPrefix);

        $tableExists = $this->db->query("SHOW TABLES LIKE '" . $tableName . "'");

        if (!$tableExists->num_rows) {
            $sql = "CREATE TABLE `" . $tableName . "` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `order_shipment_id` int(11) NOT NULL,
                `shipping_settings_template_id` int(11) DEFAULT NULL,
                `package_type` varchar(50) NOT NULL DEFAULT 'standard',
                `pickup_type` varchar(50) DEFAULT NULL,
                `length` int(11) NOT NULL DEFAULT 0,
                `width` int(11) NOT NULL DEFAULT 0,
                `height` int(11) NOT NULL DEFAULT 0,
                `weight` int(11) NOT NULL DEFAULT 0,
                `cash_on_delivery` int(11) DEFAULT NULL,
                `declared_value` int(11) DEFAULT NULL,
                `content_text` varchar(255) DEFAULT NULL,
                `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
                `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                CONSTRAINT `fk_order_shipment_package_shipment` FOREIGN KEY (`order_shipment_id`) REFERENCES `" . \DB_PREFIX . "alsendo_order_shipment` (`id`) ON DELETE CASCADE,
                CONSTRAINT `fk_order_shipment_package_template` FOREIGN KEY (`shipping_settings_template_id`) REFERENCES `" . \DB_PREFIX . "alsendo_shipping_settings_templates` (`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

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

    private function createSelectedPickupPointsTable(): void
    {
        $tableName = sprintf('%salsendo_order_pickup_points', $this->dbPrefix);

        $tableExists = $this->db->query("SHOW TABLES LIKE '" . $tableName . "'");

        if (!$tableExists->num_rows) {
            $sql = "CREATE TABLE `" . $tableName . "` (
                `order_id` INT(11) NOT NULL,
                `pickup_point` TEXT NOT NULL,
                PRIMARY KEY (`order_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

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

    private function createOrderSelectedPickupPointsTable(): void
    {
        $tableName = sprintf('%salsendo_order_selected_pickup_points', $this->dbPrefix);

        $tableExists = $this->db->query("SHOW TABLES LIKE '" . $tableName . "'");

        if (!$tableExists->num_rows) {
            $sql = "CREATE TABLE `" . $tableName . "` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `pickup_point` JSON NOT NULL,
                `order_id` int(11) NULL DEFAULT NULL,
                `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                CONSTRAINT `fk_alsendo_order_selected_pickup_points_order`
                    FOREIGN KEY (`order_id`)
                    REFERENCES `" . $this->dbPrefix . "order` (`order_id`)
                    ON DELETE SET NULL
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

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

    private function createShipmentStatusTable(): void {
        $tableName = sprintf('%salsendo_shipment_status', $this->dbPrefix);

        $tableExists = $this->db->query("SHOW TABLES LIKE '" . $tableName . "'");

        if (!$tableExists->num_rows) {
            $sql = "CREATE TABLE `" . $tableName . "` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `name` varchar(255) NOT NULL,
                `order` int(11) NOT NULL DEFAULT 0,
                `order_status_id` int(11) DEFAULT NULL,
                `region_id` int(11) UNSIGNED DEFAULT NULL,
                `data` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                PRIMARY KEY (`id`),
                CONSTRAINT `fk_shipment_status_region` FOREIGN KEY (`region_id`) REFERENCES `" . \DB_PREFIX . "alsendo_region` (`id`) ON DELETE SET NULL,
                CONSTRAINT `fk_shipment_status_order_status` FOREIGN KEY (`order_status_id`) REFERENCES `" . \DB_PREFIX . "order_status` (`order_status_id`) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

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

    private function createLogsTable(): void {
        $tableName = sprintf('%salsendo_logs', $this->dbPrefix);

        $tableExists = $this->db->query("SHOW TABLES LIKE '" . $tableName . "'");

        if (!$tableExists->num_rows) {
            $sql = "CREATE TABLE `" . $tableName . "` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `entity_type` varchar(255) NULL,
                `entity_id` int(11) DEFAULT NULL,
                `log_type` varchar(255),
                `data` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

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

    private function createPaymentShipmentMappingTable(): void {
        $tableName = sprintf('%salsendo_payment_shipment', $this->dbPrefix);

        $tableExists = $this->db->query("SHOW TABLES LIKE '" . $tableName . "'");

        if (!$tableExists->num_rows) {
            $sql = "CREATE TABLE `" . $tableName . "` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `payment_extension_id` int(11) DEFAULT NULL,
                `shipping_method_id` int(11) DEFAULT NULL,
                PRIMARY KEY (`id`),
                CONSTRAINT `fk_spmap_payment` FOREIGN KEY (`payment_extension_id`) REFERENCES `" . \DB_PREFIX . "extension` (`extension_id`) ON DELETE SET NULL,
                CONSTRAINT `fk_spmap_shipping_method` FOREIGN KEY (`shipping_method_id`) REFERENCES `" . \DB_PREFIX . "alsendo_shipping_method` (`id`) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

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

    private function ensureEventsInstalled(): void
    {
        $useDotTrigger = false;
        $result = $this->db->query("SELECT `trigger` FROM `" . $this->dbPrefix . "event` WHERE `action` = 'event/activity.addCustomer' LIMIT 1");
        if ($result->num_rows && $result->row['trigger'] === 'catalog/model/account/customer.addCustomer/after') {
            $useDotTrigger = true;
        }

        $triggerKey = $useDotTrigger ? 'dot' : 'slash';
        $triggers = [
            'alsendo_admin_menu' => [
                'slash' => 'admin/view/common/column_left/before',
                'dot' => 'admin/view/common/column_left/before',
            ],
            'alsendo_order_info' => [
                'slash' => 'admin/view/sale/order_info/after',
                'dot' => 'admin/view/sale/order_info/after',
            ],
            'alsendo_checkout_map' => [
                'slash' => 'catalog/view/common/footer/after',
                'dot' => 'catalog/view/common/footer/after',
            ],
            'alsendo_checkout_handle_selected_point' => [
                'slash' => 'catalog/model/checkout/order/addOrder/after',
                'dot' => 'catalog/model/checkout/order.addOrder/after',
            ],
            'alsendo_checkout_handle_selected_point_on_success' => [
                'slash' => 'catalog/controller/checkout/success/after',
                'dot' => 'catalog/controller/checkout/success/after',
            ],
            'alsendo_payment_methods_in_cart_filter' => [
                'slash' => 'catalog/model/checkout/payment_method/getMethods/after',
                'dot' => 'catalog/model/checkout/payment_method.getMethods/after',
            ],
            'alsendo_create_order_details' => [
                'slash' => 'catalog/controller/checkout/success/after',
                'dot' => 'catalog/controller/checkout/success/after',
            ],
            'alsendo_order_bulk_operations_button' => [
                'slash' => 'admin/view/sale/order/after',
                'dot' => 'admin/view/sale/order/after',
            ],
            'alsendo_trigger_shipment_on_order_status_change' => [
                'slash' => 'catalog/model/checkout/order/addHistory/after',
                'dot' => 'catalog/model/checkout/order.addHistory/after',
            ],
            'alsendo_shipping_extensions_filter' => [
                'slash' => 'catalog/model/setting/extension/getExtensionsByType/after',
                'dot' => 'catalog/model/setting/extension.getExtensionsByType/after',
            ],
            'alsendo_opencart_order_details_sync' => [
                'slash' => 'admin/controller/sale/order/call/after',
                'dot' => 'admin/controller/sale/order.call/after',
            ],
        ];

        $this->ensureEventInstalled(
            'alsendo_admin_menu',
            $triggers['alsendo_admin_menu'][$triggerKey],
            'extension/alsendo/module/alsendo.extendAdminMenu',
            'Add Alsendo Configuration to Admin Menu'
        );

        $this->ensureEventInstalled(
            'alsendo_order_info',
            $triggers['alsendo_order_info'][$triggerKey],
            'extension/alsendo/module/alsendo.addOrderSection',
            'Add Alsendo Section to Order Info'
        );

        $this->ensureEventInstalled(
            'alsendo_checkout_map',
            $triggers['alsendo_checkout_map'][$triggerKey],
            'extension/alsendo/event/alsendo.addCheckoutMapDetection',
            'Add Alsendo Map Detection to Checkout'
        );

        $this->ensureEventInstalled(
            'alsendo_checkout_handle_selected_point',
            $triggers['alsendo_checkout_handle_selected_point'][$triggerKey],
            'extension/alsendo/event/checkout.saveSelectedPoint',
            'Save selected pickup point on checkout'
        );

        $this->ensureEventInstalled(
            'alsendo_checkout_handle_selected_point_on_success',
            $triggers['alsendo_checkout_handle_selected_point_on_success'][$triggerKey],
            'extension/alsendo/event/checkout.ensureSyncedSelectedPointOnSuccess',
            'Save selected pickup point on checkout success'
        );

        $this->ensureEventInstalled(
            'alsendo_payment_methods_in_cart_filter',
            $triggers['alsendo_payment_methods_in_cart_filter'][$triggerKey],
            'extension/alsendo/event/payment_method.paymentFilter',
            'Filter out payment methods based on shipping method'
        );

        $this->ensureEventInstalled(
            'alsendo_create_order_details',
            $triggers['alsendo_create_order_details'][$triggerKey],
            'extension/alsendo/event/checkout.createAlsendoOrderDetails',
            'Create alsendo order details when order is created'
        );

        $this->ensureEventInstalled(
            'alsendo_order_bulk_operations_button',
            $triggers['alsendo_order_bulk_operations_button'][$triggerKey],
            'extension/alsendo/alsendo_order.appendBulkOperationsViewButton',
            'Add Alsendo Bulk Operations on Order event'
        );

        $this->ensureEventInstalled(
            'alsendo_trigger_shipment_on_order_status_change',
            $triggers['alsendo_trigger_shipment_on_order_status_change'][$triggerKey],
            'extension/alsendo/event/shipment.triggerShipmentOnOrderStatusChange',
            'Trigger submitting a shipment on order status change'
        );

        $this->ensureEventInstalled(
            'alsendo_shipping_extensions_filter',
            $triggers['alsendo_shipping_extensions_filter'][$triggerKey],
            'extension/alsendo/event/shipping_extension.filterShippingExtensions',
            'Change shipping extensions returned by getExtensionsByType'
        );

        $this->ensureEventInstalled(
            'alsendo_opencart_order_details_sync',
            $triggers['alsendo_opencart_order_details_sync'][$triggerKey],
            'extension/alsendo/event/order.syncOrder',
            'Sync OpenCart order with Alsendo Order Details'
        );
    }

    private function ensurePermissionsAssigned($adminUserGroupId, $permissions, array $routes): void
    {
        foreach ($routes as $route) {
            foreach (['access', 'modify'] as $type) {
                if (!in_array($route, $permissions[$type], true)) {
                    $this->userGroupModel->addPermission($adminUserGroupId, $type, $route);
                }
            }
        }
    }

    private function ensureEventInstalled(
        $code,
        $trigger,
        $action,
        $description = '',
        $status = true,
        $sortOrder = 0
    ): void {
        $event = $this->settingEventModel->getEventByCode($code);
        if (empty($event)) {
            $this->settingEventModel->addEvent([
                'code'        => $code,
                'description' => $description,
                'trigger'     => $trigger,
                'action'      => $action,
                'status'      => $status,
                'sort_order'  => $sortOrder,
            ]);
        }
    }

    private function removeAllEvents(): void
    {
        $eventCodes = [
            'alsendo_admin_menu',
            'alsendo_order_info',
            'alsendo_checkout_map',
            'alsendo_checkout_handle_selected_point',
            'alsendo_checkout_handle_selected_point_on_success',
            'alsendo_payment_methods_in_cart_filter',
            'alsendo_create_order_details',
            'alsendo_order_bulk_operations_button',
            'alsendo_trigger_shipment_on_order_status_change'
        ];

        foreach ($eventCodes as $code) {
            $eventExists = $this->db->query("SELECT * FROM `" . DB_PREFIX . "event` WHERE `code` = '" . $this->db->escape($code) . "'");
            if ($eventExists->num_rows) {
                $this->settingEventModel->deleteEventByCode($code);
            }
        }
    }

    private function removeAllPermissions(): void
    {
        $adminUserGroupId = null;
        foreach ($this->userGroupModel->getUserGroups() as $userGroup) {
            if ($userGroup['name'] === 'Administrator') {
                $adminUserGroupId = $userGroup['user_group_id'];
                break;
            }
        }

        if ($adminUserGroupId) {
            $permissions = [
                'extension/alsendo/module/alsendo',
                'extension/alsendo/alsendo_order',
                'extension/alsendo/alsendo_shipment'
            ];

            foreach ($permissions as $permission) {
                $this->userGroupModel->removePermission($adminUserGroupId, 'access', $permission);
                $this->userGroupModel->removePermission($adminUserGroupId, 'modify', $permission);
            }
        }
    }

    private function removeExtension(): void
    {
        $this->settingExtensionModel->uninstall('shipping', 'alsendo');
    }

    private function dropAllTables(): void
    {
        $this->db->query("SET FOREIGN_KEY_CHECKS = 0");

        $tables = [
            'alsendo_order_shipment_package',
            'alsendo_payment_shipment',
            'alsendo_order_details_packages',
            'alsendo_order_details_package_details',
            'alsendo_order_details_shipment',
            'alsendo_order_details_shipping_address',
            'alsendo_order_details_sender',
            'alsendo_order_details',
            'alsendo_order_shipment',
            'alsendo_order_selected_pickup_points',
            'alsendo_order_pickup_points',
            'alsendo_shipping_methods',
            'alsendo_services',
            'alsendo_shipment_status',
            'alsendo_shipping_settings_templates',
            'alsendo_additional_settings',
            'alsendo_sender_address_templates',
            'alsendo_logs',
            'alsendo_region'
        ];

        foreach ($tables as $table) {
            $tableName = $this->dbPrefix . $table;
            $this->db->query("DROP TABLE IF EXISTS `" . $tableName . "`");
        }

        $this->db->query("SET FOREIGN_KEY_CHECKS = 1");
    }

    private function removeExtensionPaths(): void {
        $this->db->query("DELETE FROM `" . $this->dbPrefix . "extension_path` WHERE `path` LIKE 'alsendo/%'");
    }
}