<?php
namespace EasyChat\Model;

class MessengerItemContext extends Base {
    private const MAX_DETAIL_ARRAY_ITEMS = 24;
    private const MAX_DETAIL_DEPTH = 3;
    private const MAX_DETAIL_SCALAR_CHARS = 500;

    public static function tableSql($prefix) {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}messenger_item_context_cache` (
            `cache_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `provider` VARCHAR(32) NOT NULL DEFAULT '',
            `channel_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `item_id` VARCHAR(64) NOT NULL DEFAULT '',
            `title` VARCHAR(255) NOT NULL DEFAULT '',
            `url` VARCHAR(768) NOT NULL DEFAULT '',
            `price_string` VARCHAR(128) NOT NULL DEFAULT '',
            `image_url` VARCHAR(768) NOT NULL DEFAULT '',
            `status_id` VARCHAR(32) NOT NULL DEFAULT '',
            `details_json` MEDIUMTEXT NOT NULL,
            `enrichment_status` VARCHAR(16) NOT NULL DEFAULT 'pending',
            `last_error` VARCHAR(255) NOT NULL DEFAULT '',
            `enriched_at` DATETIME NULL DEFAULT NULL,
            `next_enrich_after` DATETIME NULL DEFAULT NULL,
            `date_added` DATETIME NOT NULL,
            `date_modified` DATETIME NOT NULL,
            UNIQUE KEY `uniq_provider_channel_item` (`provider`, `channel_id`, `item_id`),
            KEY `idx_provider_item` (`provider`, `item_id`),
            KEY `idx_enrichment_due` (`provider`, `enrichment_status`, `next_enrich_after`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    }

    




    public static function projectionBackfillSql($prefix): string {
        $contexts = $prefix . 'messenger_message_contexts';
        $cache = $prefix . 'messenger_item_context_cache';
        return "INSERT INTO `{$cache}`
                (`provider`, `channel_id`, `item_id`, `title`, `url`, `price_string`,
                 `image_url`, `status_id`, `details_json`, `enrichment_status`,
                 `last_error`, `enriched_at`, `next_enrich_after`, `date_added`, `date_modified`)
                SELECT `provider`, `channel_id`, `item_id`,
                       COALESCE(MAX(NULLIF(`title`, '')), ''),
                       COALESCE(MAX(NULLIF(`url`, '')), ''),
                       COALESCE(MAX(NULLIF(`price_string`, '')), ''),
                       COALESCE(MAX(NULLIF(`image_url`, '')), ''),
                       COALESCE(MAX(NULLIF(`status_id`, '')), ''),
                       '', 'pending', '', NULL, NOW(), NOW(), NOW()
                FROM `{$contexts}`
                WHERE `provider` IN ('avito', 'youla', 'wildberries', 'ozon', 'yandex_market')
                  AND `channel_id` > 0
                  AND `item_id` <> ''
                GROUP BY `provider`, `channel_id`, `item_id`
                ON DUPLICATE KEY UPDATE
                  `date_modified` = IF(
                      (`title` = '' AND VALUES(`title`) <> '')
                      OR (`url` = '' AND VALUES(`url`) <> '')
                      OR (`price_string` = '' AND VALUES(`price_string`) <> '')
                      OR (`image_url` = '' AND VALUES(`image_url`) <> '')
                      OR (`status_id` = '' AND VALUES(`status_id`) <> ''),
                      NOW(),
                      `date_modified`
                  ),
                  `title` = IF(`title` = '', VALUES(`title`), `title`),
                  `url` = IF(`url` = '', VALUES(`url`), `url`),
                  `price_string` = IF(`price_string` = '', VALUES(`price_string`), `price_string`),
                  `image_url` = IF(`image_url` = '', VALUES(`image_url`), `image_url`),
                  `status_id` = IF(`status_id` = '', VALUES(`status_id`), `status_id`)";
    }

    public function upsertSnapshot(string $provider, int $channel_id, string $item_id, array $snapshot): array {
        $provider = $this->limit($provider, 32);
        $item_id = $this->limit($item_id, 64);
        if ($provider === '' || $channel_id <= 0 || $item_id === '') {
            return [];
        }

        $existing = $this->get($provider, $channel_id, $item_id);
        $fields = $this->fieldsFromSnapshot($snapshot, $existing);
        $details = $this->detailsFromSnapshot($snapshot);
        if ($details && !empty($existing['details']) && is_array($existing['details'])) {
            $details = $this->mergeDetails($existing['details'], $details);
        }
        $has_details = !empty($details);
        $details_json = $has_details ? json_encode($details, JSON_UNESCAPED_UNICODE) : '';

        if ($existing) {
            $fields_unchanged = true;
            foreach (['title', 'url', 'price_string', 'image_url', 'status_id'] as $key) {
                if ((string)($existing[$key] ?? '') !== (string)$fields[$key]) {
                    $fields_unchanged = false;
                    break;
                }
            }
            $details_unchanged = !$has_details
                || $details === (is_array($existing['details'] ?? null) ? $existing['details'] : []);
            if ($fields_unchanged && $details_unchanged) {
                return $existing;
            }

            $details_sql = '';
            if ($has_details) {
                $details_sql = ",
                     `details_json` = '" . $this->db->escape((string)$details_json) . "',
                     `enrichment_status` = 'ready',
                     `last_error` = '',
                     `enriched_at` = NOW(),
                     `next_enrich_after` = DATE_ADD(NOW(), INTERVAL 1 DAY)";
            }
            $this->db->query(
                "UPDATE `{$this->prefix}messenger_item_context_cache`
                 SET `title` = '" . $this->db->escape($fields['title']) . "',
                     `url` = '" . $this->db->escape($fields['url']) . "',
                     `price_string` = '" . $this->db->escape($fields['price_string']) . "',
                     `image_url` = '" . $this->db->escape($fields['image_url']) . "',
                     `status_id` = '" . $this->db->escape($fields['status_id']) . "',
                     " . ltrim($details_sql, ",\n ") . ($details_sql !== '' ? "," : "") . "
                     `date_modified` = NOW()
                 WHERE `cache_id` = " . (int)$existing['cache_id']
            );
        } else {
            $this->db->query(
                "INSERT INTO `{$this->prefix}messenger_item_context_cache`
                 SET `provider` = '" . $this->db->escape($provider) . "',
                     `channel_id` = " . (int)$channel_id . ",
                     `item_id` = '" . $this->db->escape($item_id) . "',
                     `title` = '" . $this->db->escape($fields['title']) . "',
                     `url` = '" . $this->db->escape($fields['url']) . "',
                     `price_string` = '" . $this->db->escape($fields['price_string']) . "',
                     `image_url` = '" . $this->db->escape($fields['image_url']) . "',
                     `status_id` = '" . $this->db->escape($fields['status_id']) . "',
                     `details_json` = '" . $this->db->escape((string)$details_json) . "',
                     `enrichment_status` = '" . ($has_details ? 'ready' : 'pending') . "',
                     `next_enrich_after` = " . ($has_details ? "DATE_ADD(NOW(), INTERVAL 1 DAY)" : "NOW()") . ",
                     `enriched_at` = " . ($has_details ? "NOW()" : "NULL") . ",
                     `date_added` = NOW(),
                     `date_modified` = NOW()"
            );
        }

        return $this->get($provider, $channel_id, $item_id);
    }

    public function get(string $provider, int $channel_id, string $item_id): array {
        $query = $this->db->query(
            "SELECT *
             FROM `{$this->prefix}messenger_item_context_cache`
             WHERE `provider` = '" . $this->db->escape($this->limit($provider, 32)) . "'
               AND `channel_id` = " . (int)$channel_id . "
               AND `item_id` = '" . $this->db->escape($this->limit($item_id, 64)) . "'
             LIMIT 1"
        );
        return $query->row ? $this->payload($query->row) : [];
    }

    public function shouldEnrich(string $provider, int $channel_id, string $item_id): bool {
        $row = $this->get($provider, $channel_id, $item_id);
        if (!$row) {
            return true;
        }
        $status = (string)($row['enrichment_status'] ?? '');
        if ($status === 'unsupported') {
            return false;
        }
        if ($this->isIncompleteReadyContext($row)) {
            return true;
        }
        $next = (string)($row['next_enrich_after'] ?? '');
        if ($next !== '' && strtotime($next) > time()) {
            return false;
        }
        return true;
    }

    public function markEnriched(string $provider, int $channel_id, string $item_id, array $details): array {
        $provider = $this->limit($provider, 32);
        $item_id = $this->limit($item_id, 64);
        $existing = $this->get($provider, $channel_id, $item_id);
        $fields = $this->fieldsFromSnapshot($details, $existing);
        if (!array_key_exists('status_id', $details) && !array_key_exists('status', $details)) {
            $fields['status_id'] = '';
        } else {
            $fields['status_id'] = $this->limit((string)($details['status_id'] ?? $details['status'] ?? ''), 32);
        }
        $normalized_details = $this->detailsFromSnapshot($details);
        if (!empty($existing['details']) && is_array($existing['details'])) {
            $normalized_details = $this->mergeDetails($existing['details'], $normalized_details);
        }
        $details_json = json_encode($normalized_details, JSON_UNESCAPED_UNICODE);

        $this->upsertSnapshot($provider, $channel_id, $item_id, $details);
        $this->db->query(
            "UPDATE `{$this->prefix}messenger_item_context_cache`
             SET `title` = '" . $this->db->escape($fields['title']) . "',
                 `url` = '" . $this->db->escape($fields['url']) . "',
                 `price_string` = '" . $this->db->escape($fields['price_string']) . "',
                 `image_url` = '" . $this->db->escape($fields['image_url']) . "',
                 `status_id` = '" . $this->db->escape($fields['status_id']) . "',
                 `details_json` = '" . $this->db->escape((string)$details_json) . "',
                 `enrichment_status` = 'ready',
                 `last_error` = '',
                 `enriched_at` = NOW(),
                 `next_enrich_after` = DATE_ADD(NOW(), INTERVAL 1 DAY),
                 `date_modified` = NOW()
             WHERE `provider` = '" . $this->db->escape($provider) . "'
               AND `channel_id` = " . (int)$channel_id . "
               AND `item_id` = '" . $this->db->escape($item_id) . "'"
        );

        return $this->get($provider, $channel_id, $item_id);
    }

    public function markUnavailable(string $provider, int $channel_id, string $item_id, string $status, string $error): void {
        $status = in_array($status, ['failed', 'unsupported'], true) ? $status : 'failed';
        $delay_sql = $status === 'unsupported'
            ? "DATE_ADD(NOW(), INTERVAL 7 DAY)"
            : "DATE_ADD(NOW(), INTERVAL 1 HOUR)";

        $this->upsertSnapshot($provider, $channel_id, $item_id, []);
        $this->db->query(
            "UPDATE `{$this->prefix}messenger_item_context_cache`
             SET `enrichment_status` = '" . $this->db->escape($status) . "',
                 `last_error` = '" . $this->db->escape($this->limit($error, 255)) . "',
                 `next_enrich_after` = {$delay_sql},
                 `date_modified` = NOW()
             WHERE `provider` = '" . $this->db->escape($this->limit($provider, 32)) . "'
               AND `channel_id` = " . (int)$channel_id . "
               AND `item_id` = '" . $this->db->escape($this->limit($item_id, 64)) . "'"
        );
    }

    public function mergeWithBase(array $base): array {
        $provider = (string)($base['provider'] ?? 'avito');
        $channel_id = (int)($base['channel_id'] ?? 0);
        $item_id = (string)($base['item_id'] ?? '');
        $cached = $channel_id > 0 && $item_id !== '' ? $this->get($provider, $channel_id, $item_id) : [];
        if (!$cached) {
            return $base;
        }

        foreach (['title', 'url', 'price_string', 'image_url', 'status_id'] as $key) {
            if (empty($base[$key]) && !empty($cached[$key])) {
                $base[$key] = $cached[$key];
            }
        }
        if (!empty($cached['details'])) {
            $base['details'] = $cached['details'];
            $base['enrichment_status'] = (string)($cached['enrichment_status'] ?? '');
        }
        return $base;
    }

    private function fieldsFromSnapshot(array $snapshot, array $fallback = []): array {
        $image_url = (string)($snapshot['image_url'] ?? $snapshot['image'] ?? '');
        if ($image_url === '' && isset($snapshot['images']['main']) && is_array($snapshot['images']['main'])) {
            $image_url = $this->largestImageUrl($snapshot['images']['main']);
        }

        $price = (string)($snapshot['price_string'] ?? '');
        if ($price === '' && isset($snapshot['price'])) {
            $price = is_scalar($snapshot['price']) ? (string)$snapshot['price'] : '';
        }
        $price = trim($price);
        $image_url = trim($image_url);

        $title = trim((string)($snapshot['title'] ?? ''));
        $url = trim((string)($snapshot['url'] ?? $snapshot['item_url'] ?? ''));
        $status = trim((string)($snapshot['status_id'] ?? $snapshot['status'] ?? ''));

        return [
            'title' => $this->limit($title !== '' ? $title : (string)($fallback['title'] ?? ''), 255),
            'url' => $this->limit($url !== '' ? $url : (string)($fallback['url'] ?? ''), 768),
            'price_string' => $this->limit($price !== '' ? $price : (string)($fallback['price_string'] ?? ''), 128),
            'image_url' => $this->limit($image_url !== '' ? $image_url : (string)($fallback['image_url'] ?? ''), 768),
            'status_id' => $this->limit($status !== '' ? $status : (string)($fallback['status_id'] ?? ''), 32),
        ];
    }

    private function detailsFromSnapshot(array $snapshot): array {
        $details = [];
        foreach (['description', 'category', 'location', 'params', 'event_type', 'block_types', 'is_hard_blocked'] as $key) {
            if (!array_key_exists($key, $snapshot)) {
                continue;
            }
            $value = $snapshot[$key];
            if ($value === '' || $value === [] || $value === null) {
                continue;
            }
            if ($key === 'description') {
                $details[$key] = $this->limit($this->normalizePlainText((string)$value), 5000);
            } elseif (is_array($value)) {
                $normalized = $this->normalizeDetailValue($value, 0);
                if ($normalized !== []) {
                    $details[$key] = $normalized;
                }
            } elseif (is_scalar($value)) {
                $normalized = $this->normalizeDetailValue($value, 0);
                if ($normalized !== []) {
                    $details[$key] = $normalized;
                }
            }
        }
        return $details;
    }

    private function mergeDetails(array $existing, array $incoming): array {
        if (!$existing) {
            return $incoming;
        }
        if (!$incoming) {
            return $existing;
        }

        $merged = $existing;
        foreach (['description', 'category', 'location', 'params'] as $key) {
            if (!empty($incoming[$key])) {
                $merged[$key] = $incoming[$key];
            }
        }
        foreach (['event_type', 'block_types', 'is_hard_blocked'] as $key) {
            if (!empty($incoming[$key])) {
                $merged[$key] = $incoming[$key];
            } elseif (!empty($existing[$key])) {
                $merged[$key] = $existing[$key];
            }
        }
        foreach ($incoming as $key => $value) {
            if (!array_key_exists($key, $merged) || $merged[$key] === [] || $merged[$key] === '') {
                $merged[$key] = $value;
            }
        }
        return $merged;
    }

    private function normalizeDetailValue($value, int $depth) {
        if ($value === null || $value === '' || $value === []) {
            return [];
        }
        if (is_scalar($value)) {
            $text = $this->normalizePlainText((string)$value);
            return $text !== '' ? $this->limit($text, self::MAX_DETAIL_SCALAR_CHARS) : [];
        }
        if (!is_array($value) || $depth >= self::MAX_DETAIL_DEPTH) {
            return [];
        }

        $result = [];
        $count = 0;
        foreach ($value as $key => $item) {
            if ($count >= self::MAX_DETAIL_ARRAY_ITEMS) {
                break;
            }
            $normalized = $this->normalizeDetailValue($item, $depth + 1);
            if ($normalized === [] || $normalized === '') {
                continue;
            }
            $safe_key = is_string($key)
                ? $this->limit($this->normalizePlainText($key), 80)
                : (string)(int)$key;
            if ($safe_key === '') {
                continue;
            }
            $result[$safe_key] = $normalized;
            $count++;
        }
        return $result;
    }

    private function payload(array $row): array {
        $details = json_decode((string)($row['details_json'] ?? ''), true);
        return [
            'cache_id' => (int)($row['cache_id'] ?? 0),
            'provider' => (string)($row['provider'] ?? ''),
            'channel_id' => (int)($row['channel_id'] ?? 0),
            'item_id' => (string)($row['item_id'] ?? ''),
            'title' => (string)($row['title'] ?? ''),
            'url' => (string)($row['url'] ?? ''),
            'price_string' => (string)($row['price_string'] ?? ''),
            'image_url' => (string)($row['image_url'] ?? ''),
            'status_id' => (string)($row['status_id'] ?? ''),
            'details' => is_array($details) ? $details : [],
            'enrichment_status' => (string)($row['enrichment_status'] ?? ''),
            'last_error' => (string)($row['last_error'] ?? ''),
            'enriched_at' => (string)($row['enriched_at'] ?? ''),
            'next_enrich_after' => (string)($row['next_enrich_after'] ?? ''),
        ];
    }

    private function isIncompleteReadyContext(array $row): bool {
        if ((string)($row['enrichment_status'] ?? '') !== 'ready') {
            return false;
        }
        foreach (['title', 'price_string', 'image_url'] as $field) {
            if (trim((string)($row[$field] ?? '')) !== '') {
                return false;
            }
        }

        $details = is_array($row['details'] ?? null) ? $row['details'] : [];
        foreach (['description', 'category', 'location', 'params'] as $field) {
            if (!empty($details[$field])) {
                return false;
            }
        }

        return true;
    }

    private function largestImageUrl(array $sizes): string {
        $best_url = '';
        $best_area = 0;
        foreach ($sizes as $size => $url) {
            if (!is_string($url) || trim($url) === '') {
                continue;
            }
            $area = 0;
            if (preg_match('/^(\d+)x(\d+)$/', (string)$size, $matches)) {
                $area = (int)$matches[1] * (int)$matches[2];
            }
            if ($area >= $best_area) {
                $best_area = $area;
                $best_url = trim($url);
            }
        }
        return $best_url;
    }

    private function limit(string $value, int $limit): string {
        $value = trim(str_replace("\0", '', $value));
        if (function_exists('mb_strlen') && function_exists('mb_substr')) {
            return \mb_strlen($value, 'UTF-8') > $limit ? \mb_substr($value, 0, $limit, 'UTF-8') : $value;
        }
        return strlen($value) > $limit ? substr($value, 0, $limit) : $value;
    }

    private function normalizePlainText(string $value): string {
        $value = preg_replace('#<(script|style)\b[^>]*>.*?</\1>#isu', ' ', $value);
        $value = html_entity_decode(strip_tags($value), ENT_QUOTES | ENT_HTML5, 'UTF-8');
        $value = preg_replace('/\s+/u', ' ', $value);
        return trim((string)$value);
    }
}
