<?php

namespace Opencart\Extension\Alsendo\Repositories;

require_once(DIR_EXTENSION . 'alsendo/src/DTO/Entity/AlsendoRegionEntity.php');
require_once(DIR_EXTENSION . 'alsendo/src/Models/ShippingMethod.php');

use Opencart\Extension\Alsendo\DTO\Entity\AlsendoRegionEntity;
use Opencart\Extension\Alsendo\Models\ShippingMethod;
use Opencart\System\Engine\Registry;

class ShippingMethodRepository
{
    private Registry $registry;
    private object $db;
    private string $tablePrefix;
    private string $tableName;

    public function __construct(Registry $registry, object $db, string $tablePrefix = '')
    {
        $this->registry = $registry;
        $this->db = $db;
        $this->tablePrefix = $tablePrefix ?: (defined('DB_PREFIX') ? DB_PREFIX : '');
        $this->tableName = $this->tablePrefix . 'alsendo_shipping_methods';
    }

    /**
     * @return ShippingMethod[]
     */
    public function getAllShippingMethodsByRegion(AlsendoRegionEntity $alsendoRegionEntity): array
    {
        $query = $this->db->query("SELECT * FROM `" . $this->tableName . "` WHERE `region_id` = '".$alsendoRegionEntity->id."';");
        $shippingMethods = [];
        
        foreach ($query->rows as $row) {
            $shippingMethods[] = $this->createShippingMethodFromRow($row);
        }
        
        return $shippingMethods;
    }

    public function getShippingMethodById(int $id): ?ShippingMethod
    {
        $query = $this->db->query("SELECT * FROM `" . $this->tableName . "` WHERE `id` = '" . (int)$id . "'");
        
        if (!$query->num_rows) {
            return null;
        }
        
        return $this->createShippingMethodFromRow($query->row);
    }

    public function getShippingMethodByServiceId(string $serviceId): ?ShippingMethod
    {
        $query = $this->db->query("SELECT * FROM `" . $this->tableName . "` WHERE `service_id` = '" . $this->db->escape($serviceId) . "'");
        
        if (!$query->num_rows) {
            return null;
        }
        
        return $this->createShippingMethodFromRow($query->row);
    }

    public function getShippingMethodByName(string $name): ?ShippingMethod
    {
        $query = $this->db->query("SELECT * FROM `" . $this->tableName . "` WHERE `name` = '" . $this->db->escape($name) . "'");
        
        if (!$query->num_rows) {
            return null;
        }
        
        return $this->createShippingMethodFromRow($query->row);
    }

    public function saveShippingMethod(ShippingMethod $shippingMethod): int
    {
        if ($shippingMethod->getId()) {
            $this->updateShippingMethod($shippingMethod);
            return $shippingMethod->getId();
        }

        $sql = "INSERT INTO `" . $this->tableName . "` SET 
            `service_id` = " . ($shippingMethod->getServiceId() ? "'" . $this->db->escape($shippingMethod->getServiceId()) . "'" : "NULL") . ",
            `region_id` = '".$shippingMethod->getRegionId()."',
            `name` = " . ($shippingMethod->getName() ? "'" . $this->db->escape($shippingMethod->getName()) . "'" : "NULL") . ",
            `price` = " . ($shippingMethod->getPrice() !== null ? (float)$shippingMethod->getPrice() : "NULL") . ",
            `has_map` = '" . ((int)$shippingMethod->getHasMap()) . "',
            `shipping_via_pickup_point` = " . ($shippingMethod->getShippingViaPickupPoint() !== null ? (int)$shippingMethod->getShippingViaPickupPoint() : "NULL") . ",
            `pickup_request` = " . ($shippingMethod->getPickupRequest() !== null ? (int)$shippingMethod->getPickupRequest() : "NULL");

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

    public function updateShippingMethod(ShippingMethod $shippingMethod): void
    {
        if (!$shippingMethod->getId()) {
            return;
        }

        $sql = "UPDATE `" . $this->tableName . "` SET 
            `service_id` = " . ($shippingMethod->getServiceId() ? "'" . $this->db->escape($shippingMethod->getServiceId()) . "'" : "NULL") . ",
            `region_id` = '".$shippingMethod->getRegionId()."',
            `name` = " . ($shippingMethod->getName() ? "'" . $this->db->escape($shippingMethod->getName()) . "'" : "NULL") . ",
            `price` = " . ($shippingMethod->getPrice() !== null ? (float)$shippingMethod->getPrice() : "NULL") . ", 
            `has_map` = '" . ((int) $shippingMethod->getHasMap()) . "',
            `shipping_via_pickup_point` = " . ($shippingMethod->getShippingViaPickupPoint() !== null ? (int)$shippingMethod->getShippingViaPickupPoint() : "NULL") . ",
            `pickup_request` = " . ($shippingMethod->getPickupRequest() !== null ? (int)$shippingMethod->getPickupRequest() : "NULL") . "
            WHERE `id` = '" . (int)$shippingMethod->getId() . "'";

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

    public function deleteShippingMethod(int $id): void
    {
        $this->db->query("DELETE FROM `" . $this->tableName . "` WHERE `id` = '" . (int)$id . "'");
    }

    public function clearUnselectedServiceReferences(): void
    {
        $selectedServicesQuery = $this->db->query("SELECT `id` FROM `" . $this->tablePrefix . "alsendo_services` WHERE `is_selected` = 1");
        $selectedServiceIds = [];
        
        foreach ($selectedServicesQuery->rows as $row) {
            $selectedServiceIds[] = $row['id'];
        }

        if (empty($selectedServiceIds)) {
            $this->db->query("UPDATE `" . $this->tableName . "` SET `service_id` = NULL WHERE `service_id` IS NOT NULL");
        } else {
            $quotedIds = array_map([$this->db, 'escape'], $selectedServiceIds);
            $idsString = "'" . implode("','", $quotedIds) . "'";
            $this->db->query("UPDATE `" . $this->tableName . "` SET `service_id` = NULL WHERE `service_id` IS NOT NULL AND `service_id` NOT IN (" . $idsString . ")");
        }
    }

    public function updateShippingMethods(array $selectedServiceIds): void
    {
        $this->registry->load->model('extension/alsendo/module/alsendo_region');
        /** @var AlsendoRegionEntity $currentRegion */
        $currentRegion = $this->registry->model_extension_alsendo_module_alsendo_region->getCurrentRegion();

        $this->db->query("START TRANSACTION");
        
        try {
            $servicesQuery = $this->db->query("SELECT `id`, `external_id`, `name` FROM `" . $this->tablePrefix . "alsendo_services` WHERE `id` IN ('" . implode("','", array_map([$this->db, 'escape'], $selectedServiceIds)) . "')");
            $selectedServices = [];
            
            foreach ($servicesQuery->rows as $row) {
                $selectedServices[$row['id']] = $row['name'];
            }

            $existingMethodsQuery = $this->db->query("SELECT * FROM `" . $this->tableName . "`");
            $existingMethods = [];
            
            foreach ($existingMethodsQuery->rows as $row) {
                $existingMethods[$row['id']] = $row;
            }

            foreach ($selectedServiceIds as $serviceId) {
                if (!isset($existingMethods[$serviceId]) && isset($selectedServices[$serviceId])) {
                    $shippingMethod = new ShippingMethod($currentRegion->id, null, $serviceId, $selectedServices[$serviceId]);
                    $this->saveShippingMethod($shippingMethod);
                }
            }

            foreach ($existingMethods as $serviceId => $method) {
                if ($serviceId && !in_array($serviceId, $selectedServiceIds)) {
                    $shippingMethod = new ShippingMethod($currentRegion->id, (int)$method['id'], null, $method['name']);
                    $this->updateShippingMethod($shippingMethod);
                }
            }
            
            $this->db->query("COMMIT");
        } catch (\Exception $e) {
            $this->db->query("ROLLBACK");
            throw $e;
        }
    }

    private function createShippingMethodFromRow(array $row): ShippingMethod
    {
        return new ShippingMethod(
            (int)$row['region_id'],
            (int)$row['id'],
            $row['service_id'],
            $row['name'],
            $row['price'],
            $row['has_map'],
            isset($row['shipping_via_pickup_point']) ? (int)$row['shipping_via_pickup_point'] : null,
            isset($row['pickup_request']) ? (int)$row['pickup_request'] : null
        );
    }
}
