<?php

namespace Opencart\Extension\Alsendo\Repositories;

require_once(DIR_EXTENSION . 'alsendo/src/DTO/Entity/AlsendoRegionEntity.php');
require_once(DIR_EXTENSION . 'alsendo/src/Models/Service.php');
require_once(DIR_EXTENSION . 'alsendo/src/Services/MapWidgetService.php');

use Opencart\Extension\Alsendo\DTO\Entity\AlsendoRegionEntity;
use Opencart\Extension\Alsendo\Models\Service;
use Opencart\Extension\Alsendo\Services\MapWidgetService;

class ServicesRepository
{
    private $db;
    private string $tablePrefix;

    public function __construct($db, string $tablePrefix)
    {
        $this->db = $db;
        $this->tablePrefix = $tablePrefix;
    }

    public function getServiceByExternalId(int $id): ?Service
    {
        $query = $this->db->query("SELECT * FROM `" . $this->tablePrefix . "alsendo_services` WHERE `external_id` = $id");

        if (!$query->num_rows) {
            return null;
        }

        return new Service($query->row);
    }

    public function getServiceById(int $id): ?Service
    {
        $query = $this->db->query("SELECT * FROM `" . $this->tablePrefix . "alsendo_services` WHERE `id` = $id");

        if (!$query->num_rows) {
            return null;
        }

        return new Service($query->row);
    }


    public function getAllServicesByRegion(AlsendoRegionEntity $alsendoRegionEntity): array
    {
        $query = $this->db->query("SELECT * FROM `" . $this->tablePrefix . "alsendo_services` WHERE `region_id` = '".$alsendoRegionEntity->id."';");
        $services = [];
        
        foreach ($query->rows as $row) {
            $services[] = new Service($row);
        }
        
        return $services;
    }

    public function getSelectedServices(): array
    {
        $query = $this->db->query("SELECT * FROM `" . $this->tablePrefix . "alsendo_services` WHERE `is_selected` = 1");
        $services = [];
        
        foreach ($query->rows as $row) {
            $services[] = new Service($row);
        }
        
        return $services;
    }

    public function getSelectedServicesByRegion(AlsendoRegionEntity $alsendoRegionEntity): array
    {
        $query = $this->db->query("SELECT * FROM `" . $this->tablePrefix . "alsendo_services` WHERE `is_selected` = 1 AND `region_id` = '".$alsendoRegionEntity->id."';");
        $services = [];

        foreach ($query->rows as $row) {
            $services[] = new Service($row);
        }

        return $services;
    }

    public function updateSelectedServices(array $selectedServiceNames): void
    {
        $this->db->query("START TRANSACTION");
        
        try {
            $this->db->query("UPDATE `" . $this->tablePrefix . "alsendo_services` SET `is_selected` = 0");
            
            if (!empty($selectedServiceNames)) {
                $quotedNames = array_map(function($name) {
                    return "'" . $this->db->escape($name) . "'";
                }, $selectedServiceNames);
                
                $namesString = implode(',', $quotedNames);
                
                $this->db->query("UPDATE `" . $this->tablePrefix . "alsendo_services` SET `is_selected` = 1 WHERE `name` IN (" . $namesString . ")");
            }
            
            $this->db->query("COMMIT");
        } catch (\Exception $e) {
            $this->db->query("ROLLBACK");
            throw $e;
        }
    }

    public function getServicesIndexedById(AlsendoRegionEntity $currentRegion): array
    {
        $query = $this->db->query("SELECT * FROM `" . $this->tablePrefix . "alsendo_services` WHERE `region_id` = '".$currentRegion->id."';");
        $services = [];

        foreach ($query->rows as $row) {
            $services[$row['id']] = $row;
        }

        return $services;
    }

    public function getServicesIndexedByExternalId(AlsendoRegionEntity $currentRegion): array
    {
        $query = $this->db->query("SELECT * FROM `" . $this->tablePrefix . "alsendo_services` WHERE `region_id` = '".$currentRegion->id."';");
        $services = [];

        foreach ($query->rows as $row) {
            $services[$row['external_id']] = $row;
        }

        return $services;
    }

    public function getMapOperatorMappings(AlsendoRegionEntity $currentRegion): array
    {
        $services = $this->getServicesIndexedByExternalId($currentRegion);

        $result = [];

        foreach ($services as $serviceId => $service) {
            if (empty($service['to_point'])) {
                continue;
            }

            $operator = !empty($service['map_operator']) ? strtolower($service['map_operator']) : null;
            if ($operator === null) {
                $operator = strtolower($service['supplier']);
            }

            if (in_array($operator, $this->getAvailableMapOperators())) {
                $result[$serviceId] = $operator;
            }
        }

        return $result;
    }

    public function syncServices(array $servicesData): void
    {
        $this->db->query("START TRANSACTION");
        
        try {
            $currentServiceIds = $this->getCurrentServiceExternalIds();
            $newServiceIds = [];
            
            foreach ($servicesData as $serviceData) {
                $serviceId = $serviceData['external_id'];
                $newServiceIds[] = $serviceId;
                
                if (in_array($serviceId, $currentServiceIds)) {
                    $this->updateService($serviceData);
                } else {
                    $this->insertService($serviceData);
                }
            }
            
            $servicesToDelete = array_diff($currentServiceIds, $newServiceIds);
            foreach ($servicesToDelete as $serviceId) {
                $this->deleteService($serviceId);
            }
            
            $this->db->query("COMMIT");
        } catch (\Exception $e) {
            $this->db->query("ROLLBACK");
            throw $e;
        }
    }

    private function getCurrentServiceExternalIds(): array
    {
        $query = $this->db->query("SELECT `external_id` FROM `" . $this->tablePrefix . "alsendo_services`");
        $ids = [];
        
        foreach ($query->rows as $row) {
            $ids[] = $row['external_id'];
        }
        
        return $ids;
    }

    private function insertService(array $data): void
    {
        $supplier = array_key_exists('supplier', $data) && $data['supplier'] !== null ? "'" . $this->db->escape($data['supplier']) . "'" : 'NULL';
        $mapOperator = array_key_exists('map_operator', $data) && $data['map_operator'] !== null ? "'" . $this->db->escape($data['map_operator']) . "'" : 'NULL';
        $toPoint = array_key_exists('to_point', $data) && $data['to_point'] !== null ? (int)$data['to_point'] : 'NULL';
        $comment = array_key_exists('comment', $data) && $data['comment'] !== null ? "'" . $this->db->escape($data['comment']) . "'" : 'NULL';
        $pointToPoint = array_key_exists('point_to_point', $data) && $data['point_to_point'] !== null ? (int)$data['point_to_point'] : 'NULL';
        $doorToPoint = array_key_exists('door_to_point', $data) && $data['door_to_point'] !== null ? (int)$data['door_to_point'] : 'NULL';
        $pointToDoor = array_key_exists('point_to_door', $data) && $data['point_to_door'] !== null ? (int)$data['point_to_door'] : 'NULL';
        $doorToDoor = array_key_exists('door_to_door', $data) && $data['door_to_door'] !== null ? (int)$data['door_to_door'] : 'NULL';
        $logoUrl = array_key_exists('logo_url', $data) && $data['logo_url'] !== null ? "'" . $this->db->escape($data['logo_url']) . "'" : 'NULL';

        $this->db->query("INSERT INTO `" . $this->tablePrefix . "alsendo_services` SET
            `region_id` = '".$data['region_id']."',
            `external_id` = '" . $this->db->escape($data['external_id']) . "',
            `is_selected` = 1,
            `name` = '" . $this->db->escape($data['name']) . "',
            `supplier` = " . $supplier . ",
            `map_operator` = " . $mapOperator . ",
            `to_point` = " . ($toPoint === 'NULL' ? 'NULL' : $toPoint) . ",
            `comment` = " . $comment . ",
            `logo_url` = " . ($logoUrl === 'NULl' ? 'NULL' : $logoUrl) . ",
            `point_to_point` = " . ($pointToPoint === 'NULL' ? 'NULL' : $pointToPoint) . ",
            `door_to_point` = " . ($doorToPoint === 'NULL' ? 'NULL' : $doorToPoint) . ",
            `point_to_door` = " . ($pointToDoor === 'NULL' ? 'NULL' : $pointToDoor) . ",
            `door_to_door` = " . ($doorToDoor === 'NULL' ? 'NULL' : $doorToDoor));
    }

    private function updateService(array $data): void
    {
        $supplier = array_key_exists('supplier', $data) && $data['supplier'] !== null ? "'" . $this->db->escape($data['supplier']) . "'" : 'NULL';
        $mapOperator = array_key_exists('map_operator', $data) && $data['map_operator'] !== null ? "'" . $this->db->escape($data['map_operator']) . "'" : 'NULL';
        $toPoint = array_key_exists('to_point', $data) && $data['to_point'] !== null ? (int)$data['to_point'] : 'NULL';
        $comment = array_key_exists('comment', $data) && $data['comment'] !== null ? "'" . $this->db->escape($data['comment']) . "'" : 'NULL';
        $pointToPoint = array_key_exists('point_to_point', $data) && $data['point_to_point'] !== null ? (int)$data['point_to_point'] : 'NULL';
        $doorToPoint = array_key_exists('door_to_point', $data) && $data['door_to_point'] !== null ? (int)$data['door_to_point'] : 'NULL';
        $pointToDoor = array_key_exists('point_to_door', $data) && $data['point_to_door'] !== null ? (int)$data['point_to_door'] : 'NULL';
        $doorToDoor = array_key_exists('door_to_door', $data) && $data['door_to_door'] !== null ? (int)$data['door_to_door'] : 'NULL';
        $logoUrl = array_key_exists('logo_url', $data) && $data['logo_url'] !== null ? "'" . $this->db->escape($data['logo_url']) . "'" : 'NULL';

        $this->db->query("UPDATE `" . $this->tablePrefix . "alsendo_services` SET
            `region_id` = '".$data['region_id']."',
            `name` = '" . $this->db->escape($data['name']) . "',
            `supplier` = " . $supplier . ",
            `map_operator` = " . $mapOperator . ",
            `to_point` = " . ($toPoint === 'NULL' ? 'NULL' : $toPoint) . ",
            `comment` = " . $comment . ",
            `logo_url` = " . ($logoUrl === 'NULl' ? 'NULL' : $logoUrl) . ",
            `point_to_point` = " . ($pointToPoint === 'NULL' ? 'NULL' : $pointToPoint) . ",
            `door_to_point` = " . ($doorToPoint === 'NULL' ? 'NULL' : $doorToPoint) . ",
            `point_to_door` = " . ($pointToDoor === 'NULL' ? 'NULL' : $pointToDoor) . ",
            `door_to_door` = " . ($doorToDoor === 'NULL' ? 'NULL' : $doorToDoor) . "
            WHERE `external_id` = '" . $this->db->escape($data['external_id']) . "'");
    }

    private function deleteService(string $serviceId): void
    {
        $this->db->query("DELETE FROM `" . $this->tablePrefix . "alsendo_services` WHERE `external_id` = '" . $this->db->escape($serviceId) . "'");
    }

    public function updateServiceDetails(string $serviceId, ?string $comment): void
    {
        $commentSql = $comment ? "'" . $this->db->escape($comment) . "'" : 'NULL';
        $this->db->query("UPDATE `" . $this->tablePrefix . "alsendo_services` SET
            `comment` = " . $commentSql . "
            WHERE `external_id` = '" . $this->db->escape($serviceId) . "'");
    }

    public function removeFromSelection(string $externalId): void
    {
        $this->db->query("UPDATE `" . $this->tablePrefix . "alsendo_services` SET `is_selected` = 0 WHERE `external_id` = '" . $this->db->escape($externalId) . "'");
    }

    public function updateServiceSelection(array $ids): void
    {
        $this->db->query("START TRANSACTION");
        
        try {
            $this->db->query("UPDATE `" . $this->tablePrefix . "alsendo_services` SET `is_selected` = 0");

            if (!empty($ids)) {
                $quotedIds = array_map(function($id) {
                    return "'" . $this->db->escape($id) . "'";
                }, $ids);
                
                $idsString = implode(',', $quotedIds);
                $this->db->query("UPDATE `" . $this->tablePrefix . "alsendo_services` SET `is_selected` = 1 WHERE `id` IN (" . $idsString . ")");
            }
            
            $this->db->query("COMMIT");
        } catch (\Exception $e) {
            $this->db->query("ROLLBACK");
            throw $e;
        }
    }

    private function getAvailableMapOperators(): array
    {
        $operatorAliasMap = MapWidgetService::OPERATOR_ALIAS_MAP;

        $result = [];

        foreach ($operatorAliasMap as $countryCode => $operators) {
            foreach ($operators as $key => $value) {
                $value = strtolower($value);
                if (!in_array($value, $result)) {
                    $result[] = $value;
                }
            }
        }

        return $result;
    }
} 