<?php

namespace Opencart\Admin\Model\Extension\Alsendo\Module;

require_once(DIR_EXTENSION . 'alsendo/src/DTO/Entity/OrderDetailsEntityFactoryInterface.php');
require_once(DIR_EXTENSION . 'alsendo/src/DTO/Entity/Apaczka/ApaczkaOrderDetailsEntityFactory.php');

use Opencart\Extension\Alsendo\DTO\Entity\AbstractOrderDetailsShipmentEntity;
use Opencart\Extension\Alsendo\DTO\Entity\OrderDetailsEntityFactoryInterface;
use Opencart\Extension\Alsendo\DTO\Entity\Apaczka\ApaczkaOrderDetailsEntityFactory;
use Opencart\System\Engine\Model;
use Opencart\System\Engine\Registry;

class AlsendoOrderDetailsShipment extends Model
{
    public function __construct(Registry $registry)
    {
        parent::__construct($registry);
    }

    public function populate(): void
    {
        $this->db->query("
            CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "alsendo_order_details_shipment` (
                `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
                `order_details_id` INT(11) UNSIGNED DEFAULT NULL,
                `shipping_method_id` INT(11) NULL DEFAULT NULL,
                `shipping_method_name` VARCHAR(255) NULL DEFAULT NULL,
                `service_id` INT(11) NULL DEFAULT NULL,
                `external_service_id` VARCHAR(255) NULL DEFAULT NULL,
                `shipping_service_name` VARCHAR(255) NULL DEFAULT NULL,
                `map_operator` VARCHAR(255) NULL DEFAULT NULL,
                `preferred_pickup_date` DATE NULL DEFAULT NULL,
                `preferred_pickup_hour_from` VARCHAR(255) NULL DEFAULT NULL,
                `preferred_pickup_hour_to` VARCHAR(255) NULL DEFAULT NULL,
                `shipment_pickup_point` JSON NULL DEFAULT NULL,
                `shipment_pickup_point_display` VARCHAR(255) NULL DEFAULT NULL,
                PRIMARY KEY (`id`),
                CONSTRAINT `fk_alsendo_order_details_shipment_order_details`
                    FOREIGN KEY (`order_details_id`)
                    REFERENCES `" . DB_PREFIX . "alsendo_order_details` (`id`)
                    ON DELETE CASCADE,
                CONSTRAINT `fk_alsendo_order_details_shipment_shipping_method`
                    FOREIGN KEY (`shipping_method_id`)
                    REFERENCES `" . DB_PREFIX . "alsendo_shipping_methods` (`id`)
                    ON DELETE SET NULL,
                CONSTRAINT `fk_alsendo_order_details_shipment_service`
                    FOREIGN KEY (`service_id`)
                    REFERENCES `" . DB_PREFIX . "alsendo_services` (`id`)
                    ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        ");
    }

    public function getShipmentByOrderDetailsId(int $orderDetailsId, ?OrderDetailsEntityFactoryInterface $factory = null): ?AbstractOrderDetailsShipmentEntity
    {
        $query = $this->db->query("
            SELECT *
            FROM `" . DB_PREFIX . "alsendo_order_details_shipment`
            WHERE order_details_id = '" . (int)$orderDetailsId . "'
            LIMIT 1
        ");
        if (!$query->num_rows) {
            return null;
        }
        $factory = $factory ?? new ApaczkaOrderDetailsEntityFactory();
        return $factory->createShipmentFromArray($query->row);
    }

    public function insertShipment(int $orderDetailsId, array $data): void
    {
        $shipmentPickupPoint = $data['shipment_pickup_point'] ?? null;

        if (is_array($shipmentPickupPoint)) {
            $shipmentPickupPoint = json_encode($shipmentPickupPoint, JSON_UNESCAPED_UNICODE | JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_AMP | JSON_HEX_QUOT);
        }

        $strSql = function($key) use ($data) {
            return array_key_exists($key, $data) ? ($data[$key] === null ? "NULL" : "'" . $this->db->escape(htmlspecialchars($data[$key], ENT_QUOTES, 'UTF-8')) . "'") : "NULL";
        };
        $intSql = function($key) use ($data) {
            return (array_key_exists($key, $data) && $data[$key] !== null && $data[$key] !== '') ? "'" . (int)$data[$key] . "'" : "NULL";
        };
        $this->db->query("
        INSERT INTO `" . DB_PREFIX . "alsendo_order_details_shipment`
        SET `order_details_id` = '" . (int)$orderDetailsId . "',
            `shipping_method_id` = " . $intSql('shipping_method_id') . ",
            `shipping_method_name` = " . $strSql('shipping_method_name') . ",
            `service_id` = " . $intSql('service_id') . ",
            `external_service_id` = " . $strSql('external_service_id') . ",
            `shipping_service_name` = " . $strSql('shipping_service_name') . ",
            `map_operator` = " . $strSql('map_operator') . ",
            `preferred_pickup_date` = " . (array_key_exists('preferred_pickup_date', $data) && $data['preferred_pickup_date'] !== null && $data['preferred_pickup_date'] !== '' ? "'" . $this->db->escape($data['preferred_pickup_date']) . "'" : "NULL") . ",
            `preferred_pickup_hour_from` = " . $strSql('preferred_pickup_hour_from') . ",
            `preferred_pickup_hour_to` = " . $strSql('preferred_pickup_hour_to') . ",
            `shipment_pickup_point` = " . ($shipmentPickupPoint !== null && $shipmentPickupPoint !== '' ? "'" . $this->db->escape($shipmentPickupPoint) . "'" : "NULL") . ",
            `shipment_pickup_point_display` = " . $strSql('shipment_pickup_point_display') . "
    ");
    }

    public function syncShipment(int $orderDetailsId, array $data): void
    {
        $updates = [
            "`shipping_method_id` = " . (array_key_exists('shipping_method_id', $data) && $data['shipping_method_id'] !== null && $data['shipping_method_id'] !== '' ? "'" . (int)$data['shipping_method_id'] . "'" : "NULL"),
            "`shipping_method_name` = " . (array_key_exists('shipping_method_name', $data) ? ($data['shipping_method_name'] === null ? "NULL" : "'" . $this->db->escape(htmlspecialchars($data['shipping_method_name'], ENT_QUOTES, 'UTF-8')) . "'") : "NULL"),
            "`shipping_service_name` = " . (array_key_exists('shipping_service_name', $data) ? ($data['shipping_service_name'] === null ? "NULL" : "'" . $this->db->escape(htmlspecialchars($data['shipping_service_name'], ENT_QUOTES, 'UTF-8')) . "'") : "NULL"),
            "`service_id` = " . (array_key_exists('service_id', $data) && $data['service_id'] !== null && $data['service_id'] !== '' ? "'" . (int)$data['service_id'] . "'" : "NULL"),
            "`external_service_id` = " . (array_key_exists('external_service_id', $data) ? ($data['external_service_id'] === null ? "NULL" : "'" . $this->db->escape(htmlspecialchars($data['external_service_id'], ENT_QUOTES, 'UTF-8')) . "'") : "NULL"),
        ];

        if (array_key_exists('shipment_pickup_point', $data) && $data['shipment_pickup_point'] !== null) {
            $shipmentPickupPoint = $data['shipment_pickup_point'];
            if (is_array($shipmentPickupPoint)) {
                $shipmentPickupPoint = json_encode(
                    $shipmentPickupPoint,
                    JSON_UNESCAPED_UNICODE | JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_AMP | JSON_HEX_QUOT
                );
            }
            $updates[] = "`shipment_pickup_point` = " . ($shipmentPickupPoint !== null ? "'" . $this->db->escape($shipmentPickupPoint) . "'" : "NULL");
        }

        if (array_key_exists('shipment_pickup_point_display', $data) && $data['shipment_pickup_point_display'] !== null) {
            $updates[] = "`shipment_pickup_point_display` = " . ($data['shipment_pickup_point_display'] !== null ? "'" . $this->db->escape(htmlspecialchars($data['shipment_pickup_point_display'], ENT_QUOTES, 'UTF-8')) . "'" : "NULL");
        }

        $this->db->query("
            UPDATE `" . DB_PREFIX . "alsendo_order_details_shipment`
            SET " . implode(", ", $updates) . "
            WHERE order_details_id = '" . $orderDetailsId . "'
        ");
    }

    public function updateShipment(int $shipmentId, array $data): void
    {
        $updates = [];

        if (array_key_exists('shipping_method_id', $data)) {
            $updates[] = ($data['shipping_method_id'] === null || $data['shipping_method_id'] === '') ? "`shipping_method_id` = NULL" : "`shipping_method_id` = '" . (int)$data['shipping_method_id'] . "'";
        }

        if (array_key_exists('shipping_method_name', $data)) {
            $updates[] = $data['shipping_method_name'] === null ? "`shipping_method_name` = NULL" : "`shipping_method_name` = '" . $this->db->escape($data['shipping_method_name']) . "'";
        }

        if (array_key_exists('service_id', $data)) {
            $updates[] = ($data['service_id'] === null || $data['service_id'] === '') ? "`service_id` = NULL" : "`service_id` = '" . (int)$data['service_id'] . "'";
        }

        if (array_key_exists('external_service_id', $data)) {
            $updates[] = $data['external_service_id'] === null ? "`external_service_id` = NULL" : "`external_service_id` = '" . $this->db->escape($data['external_service_id']) . "'";
        }

        if (array_key_exists('shipping_service_name', $data)) {
            $updates[] = $data['shipping_service_name'] === null ? "`shipping_service_name` = NULL" : "`shipping_service_name` = '" . $this->db->escape($data['shipping_service_name']) . "'";
        }

        if (array_key_exists('map_operator', $data)) {
            $updates[] = $data['map_operator'] === null ? "`map_operator` = NULL" : "`map_operator` = '" . $this->db->escape($data['map_operator']) . "'";
        }

        if (array_key_exists('preferred_pickup_date', $data)) {
            $updates[] = ($data['preferred_pickup_date'] === null || $data['preferred_pickup_date'] === '') ? "`preferred_pickup_date` = NULL" : "`preferred_pickup_date` = '" . $this->db->escape($data['preferred_pickup_date']) . "'";
        }

        if (array_key_exists('preferred_pickup_hour_from', $data)) {
            $updates[] = $data['preferred_pickup_hour_from'] === null ? "`preferred_pickup_hour_from` = NULL" : "`preferred_pickup_hour_from` = '" . $this->db->escape($data['preferred_pickup_hour_from']) . "'";
        }

        if (array_key_exists('preferred_pickup_hour_to', $data)) {
            $updates[] = $data['preferred_pickup_hour_to'] === null ? "`preferred_pickup_hour_to` = NULL" : "`preferred_pickup_hour_to` = '" . $this->db->escape($data['preferred_pickup_hour_to']) . "'";
        }

        if (array_key_exists('shipment_pickup_point', $data)) {
            $shipmentPickupPoint = $data['shipment_pickup_point'] === null ? null : (is_array($data['shipment_pickup_point'])
                ? json_encode($data['shipment_pickup_point'], JSON_UNESCAPED_UNICODE | JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_AMP | JSON_HEX_QUOT)
                : $data['shipment_pickup_point']);
            $updates[] = "`shipment_pickup_point` = " . ($shipmentPickupPoint !== null && $shipmentPickupPoint !== '' ? "'" . $this->db->escape($shipmentPickupPoint) . "'" : "NULL");
        }

        if (array_key_exists('shipment_pickup_point_display', $data)) {
            $updates[] = $data['shipment_pickup_point_display'] === null ? "`shipment_pickup_point_display` = NULL" : "`shipment_pickup_point_display` = '" . $this->db->escape($data['shipment_pickup_point_display']) . "'";
        }

        if (!empty($updates)) {
            $this->db->query("
            UPDATE `" . DB_PREFIX . "alsendo_order_details_shipment`
            SET " . implode(", ", $updates) . "
            WHERE `id` = '" . (int)$shipmentId . "'
        ");
        }
    }
}
