<?php
namespace EasyChat\Library\Commerce;

use EasyChat\Library\Catalog\CmsCatalogConnectionResolver;






final class OpenCartDeliveryMethodCatalog
{
    private $db;
    private $settings;
    private $edition;

    public function __construct($db, array $settings, string $edition = '')
    {
        $this->db = $db;
        $this->settings = $settings;
        $this->edition = $edition;
    }

    public function discover(int $limit = 24): array
    {
        $connection = CmsCatalogConnectionResolver::resolve($this->db, $this->settings, $this->edition);
        if (empty($connection['ready']) || ($connection['type'] ?? '') !== 'opencart') {
            return array('available' => false, 'items' => array(), 'manual_count' => 0);
        }

        $prefix = (string)($connection['prefix'] ?? '');
        if (!preg_match('/^[A-Za-z0-9_]{0,64}$/', $prefix)) {
            return array('available' => false, 'items' => array(), 'manual_count' => 0);
        }
        $columns = $this->columns($prefix);
        if (empty($columns['shipping_method']) || empty($columns['tracking'])) {
            return array('available' => false, 'items' => array(), 'manual_count' => 0);
        }
        $has_code = !empty($columns['shipping_code']);
        $code = $has_code ? '`shipping_code`' : "''";
        $limit = max(1, min(48, $limit));
        try {
            $query = $this->db->query(
                "SELECT {$code} AS `shipping_code`,`shipping_method`,COUNT(*) AS `orders_count`,MAX(`date_modified`) AS `last_seen`\n"
                . "FROM `{$prefix}order` WHERE `order_status_id` > 0 AND TRIM(COALESCE(`tracking`, '')) <> ''\n"
                . "GROUP BY {$code},`shipping_method` ORDER BY `last_seen` DESC LIMIT {$limit}"
            );
            $rows = is_object($query) && isset($query->rows) && is_array($query->rows) ? $query->rows : array();
        } catch (\Throwable $e) {
            return array('available' => false, 'items' => array(), 'manual_count' => 0);
        }

        $resolver = new OpenCartShippingResolver();
        $mapping = $resolver->mappingFromSettings($this->settings['opencart_shipping_mapping'] ?? array());
        $items = array();
        foreach ($rows as $row) {
            $method = $this->method((string)($row['shipping_method'] ?? ''));
            $code_value = $method['code'] !== '' ? $method['code'] : (string)($row['shipping_code'] ?? '');
            $name = $method['name'] !== '' ? $method['name'] : 'Способ доставки без названия';
            $resolved = $resolver->resolve($code_value, $name, $mapping);
            $key = (string)($resolved['key'] ?? '');
            if ($key === '') continue;
            $items[$key] = array(
                'key' => $key,
                'name' => $this->plain($name, 120),
                'code' => $this->plain($code_value, 120),
                'orders_count' => max(1, (int)($row['orders_count'] ?? 1)),
                'last_seen' => $this->plain((string)($row['last_seen'] ?? ''), 32),
                'provider' => (string)($resolved['provider'] ?? ''),
                'carrier' => (string)($resolved['carrier'] ?? ''),
                'evidence' => (string)($resolved['evidence'] ?? ''),
                'suggested_provider' => ($resolved['confidence'] ?? 0) < 0.95 ? (string)($resolved['provider'] ?? '') : '',
            );
        }

         
         
         
        foreach ($mapping as $key => $provider) {
            if (isset($items[$key])) continue;
            $items[$key] = array(
                'key' => $key, 'name' => $key, 'code' => $key,
                'orders_count' => 0, 'last_seen' => '', 'provider' => $provider,
                'carrier' => $resolver->label($provider), 'evidence' => 'admin_mapping',
                'suggested_provider' => '',
            );
        }
        return array(
            'available' => true,
            'items' => array_values($items),
            'manual_count' => count($mapping),
        );
    }

    private function columns(string $prefix): array
    {
        try {
            $query = $this->db->query("SHOW COLUMNS FROM `{$prefix}order`");
            $rows = is_object($query) && isset($query->rows) && is_array($query->rows) ? $query->rows : array();
            $columns = array();
            foreach ($rows as $row) $columns[(string)($row['Field'] ?? '')] = true;
            return $columns;
        } catch (\Throwable $e) {
            return array();
        }
    }

    private function method(string $raw): array
    {
        $raw = trim($raw);
        if ($raw !== '' && ($raw[0] === '{' || $raw[0] === '[')) {
            $decoded = json_decode($raw, true);
            if (is_array($decoded)) {
                return array('name' => $this->plain((string)($decoded['name'] ?? $decoded['title'] ?? ''), 120), 'code' => $this->plain((string)($decoded['code'] ?? ''), 120));
            }
        }
        return array('name' => $this->plain($raw, 120), 'code' => '');
    }

    private function plain($value, int $limit): string
    {
        $value = trim(preg_replace('/\s+/u', ' ', strip_tags((string)$value)));
        return function_exists('mb_substr') ? mb_substr($value, 0, $limit, 'UTF-8') : substr($value, 0, $limit);
    }
}
