<?php
namespace EasyChat\Model;

use EasyChat\KnowledgeSection;

class Knowledge extends Base {

    const CMS_SOURCE_MISSING_ERROR = 'cms_source_missing';

    









    public function upsertCmsItem(string $title, string $content, int $section_id, int $source_id, string $generation = ''): array {
        $p   = $this->prefix;
        $now = date('Y-m-d H:i:s');
        $content_hash = hash('sha256', $content);
        $generation = preg_match('/^[a-f0-9]{1,32}$/', $generation) ? $generation : '';

        $existing = $this->db->query(
            "SELECT id, status, content, content_hash, vector_id, error FROM `{$p}knowledge`
             WHERE section_id = $section_id AND item_id = $source_id
             LIMIT 1"
        );

        if (!empty($existing->row)) {
            $row_id = (int)$existing->row['id'];
            $status = (string)$existing->row['status'];
            $stored_hash = trim((string)($existing->row['content_hash'] ?? ''));
            if ($stored_hash === '') {
                $stored_hash = hash('sha256', (string)($existing->row['content'] ?? ''));
            }

            $source_was_missing = $status === 'disabled'
                && (string)($existing->row['error'] ?? '') === self::CMS_SOURCE_MISSING_ERROR;
            $manually_disabled = $status === 'disabled' && !$source_was_missing;

            if (hash_equals($stored_hash, $content_hash)
                && (in_array($status, ['active', 'pending', 'indexing'], true) || $manually_disabled)) {
                $this->db->query(
                    "UPDATE `{$p}knowledge`
                     SET sync_generation = '" . $this->db->escape($generation) . "'
                     WHERE id = $row_id"
                );
                return ['id' => $row_id, 'is_new' => false, 'skip' => true, 'final_status' => $status];
            }

            $this->deleteVector((int)($existing->row['vector_id'] ?? 0));
            $title_e   = $this->db->escape($title);
            $content_e = $this->db->escape($content);
            $final_status = $manually_disabled ? 'disabled' : 'active';
            $this->db->query(
                "UPDATE `{$p}knowledge`
                 SET title = '$title_e', content = '$content_e',
                     content_hash = '" . $this->db->escape($content_hash) . "',
                     sync_generation = '" . $this->db->escape($generation) . "',
                     vector_id = NULL, status = 'pending', error = NULL, date_modified = '$now'
                 WHERE id = $row_id"
            );
            return ['id' => $row_id, 'is_new' => false, 'skip' => false, 'final_status' => $final_status];
        }

         
        $title_e   = $this->db->escape($title);
        $content_e = $this->db->escape($content);
        $this->db->query(
            "INSERT INTO `{$p}knowledge`
                (title, content, section_id, item_id, content_hash, sync_generation, status, date_added, date_modified)
             VALUES
                ('$title_e', '$content_e', $section_id, $source_id,
                 '" . $this->db->escape($content_hash) . "', '" . $this->db->escape($generation) . "',
                 'pending', '$now', '$now')"
        );
        return ['id' => (int)$this->db->getLastId(), 'is_new' => true, 'skip' => false, 'final_status' => 'active'];
    }

    public function disableCmsItemsNotSeen(int $section_id, string $generation): int {
        if (!in_array($section_id, [KnowledgeSection::PRODUCT, KnowledgeSection::PAGE], true)
            || !preg_match('/^[a-f0-9]{1,32}$/', $generation)) {
            return 0;
        }

        $generation_e = $this->db->escape($generation);
        $rows = $this->db->query(
            "SELECT id, vector_id FROM `{$this->prefix}knowledge`
             WHERE section_id = {$section_id} AND item_id > 0
               AND COALESCE(sync_generation, '') != '{$generation_e}' AND status != 'disabled'"
        );
        $ids = array();
        foreach (($rows->rows ?? array()) as $row) {
            $ids[] = (int)$row['id'];
            $this->deleteVector((int)($row['vector_id'] ?? 0));
        }
        if (empty($ids)) {
            return 0;
        }

        $this->db->query(
            "UPDATE `{$this->prefix}knowledge`
             SET status = 'disabled', vector_id = NULL,
                 error = '" . self::CMS_SOURCE_MISSING_ERROR . "', date_modified = '" . date('Y-m-d H:i:s') . "'
             WHERE id IN (" . implode(',', $ids) . ")"
        );
        return count($ids);
    }

    


    public function getList(int $page = 1, int $limit = 20): array {
        $p      = $this->prefix;
        $offset = ($page - 1) * $limit;

        $result = $this->db->query(
            "SELECT id, title, section_id, status, error, date_added, date_modified
             FROM `{$p}knowledge`
             ORDER BY id DESC
             LIMIT $limit OFFSET $offset"
        );
        return $result->rows ?? [];
    }

    public function getFilteredList(array $filters, int $page = 1, int $limit = 20): array {
        $p      = $this->prefix;
        $page   = max(1, $page);
        $limit  = max(1, min(100, $limit));
        $offset = ($page - 1) * $limit;
        $where  = $this->buildFilterWhere($filters);

        $result = $this->db->query(
            "SELECT id, title, section_id, status, error, date_added, date_modified
             FROM `{$p}knowledge`
             WHERE {$where}
             ORDER BY id DESC
             LIMIT {$limit} OFFSET {$offset}"
        );
        return $result->rows ?? [];
    }

    public function getTotal(): int {
        $result = $this->db->query(
            "SELECT COUNT(*) AS total FROM `{$this->prefix}knowledge`"
        );
        return (int)($result->row['total'] ?? 0);
    }

    public function getFilteredTotal(array $filters): int {
        $where = $this->buildFilterWhere($filters);
        $result = $this->db->query(
            "SELECT COUNT(*) AS total FROM `{$this->prefix}knowledge` WHERE {$where}"
        );
        return (int)($result->row['total'] ?? 0);
    }

    public function getActiveTotal(): int {
        $result = $this->db->query(
            "SELECT COUNT(*) AS total FROM `{$this->prefix}knowledge` WHERE status = 'active'"
        );
        return (int)($result->row['total'] ?? 0);
    }

    public function findActiveSnippetsForAi(string $query, int $limit = 4, int $max_chars = 2600): array {
        $limit = max(1, min(8, $limit));
        $max_chars = max(400, min(6000, $max_chars));
        $terms = $this->extractSearchTerms($query);
        $where = "status = 'active'";
        $match_parts = [];
        $score_parts = [];

        foreach ($terms as $term) {
            $escaped = $this->db->escape($term);
            $like = "'%" . $escaped . "%'";
            $match_parts[] = "(LOWER(title) LIKE {$like} OR LOWER(content) LIKE {$like})";
            $score_parts[] = "(CASE WHEN LOWER(title) LIKE {$like} THEN 3 ELSE 0 END)";
            $score_parts[] = "(CASE WHEN LOWER(content) LIKE {$like} THEN 1 ELSE 0 END)";
        }

        if (!empty($match_parts)) {
            $where .= " AND (" . implode(' OR ', $match_parts) . ")";
        }

        $score_sql = empty($score_parts) ? '0' : implode(' + ', $score_parts);
        $sql = "SELECT id, title, content, ({$score_sql}) AS relevance
            FROM `{$this->prefix}knowledge`
            WHERE {$where}
            ORDER BY relevance DESC, date_modified DESC, id DESC
            LIMIT " . (int)$limit;

        $result = $this->db->query($sql);
        $rows = $result->rows ?? [];
        $snippets = [];
        $used_chars = 0;

        foreach ($rows as $row) {
            $title = $this->normalizeSnippetText($row['title'] ?? '');
            $content = $this->normalizeSnippetText($row['content'] ?? '');
            if ($title === '' || $content === '') {
                continue;
            }

            $remaining = $max_chars - $used_chars;
            if ($remaining < 120) {
                break;
            }

            $content = $this->limitSnippetChars($content, min(900, $remaining));
            $used_chars += mb_strlen($title . "\n" . $content, 'UTF-8');
            $snippets[] = [
                'id' => (int)($row['id'] ?? 0),
                'title' => $this->limitSnippetChars($title, 120),
                'content' => $content,
            ];
        }

        return $snippets;
    }

    public function getById(int $id): array {
        $result = $this->db->query(
            "SELECT * FROM `{$this->prefix}knowledge` WHERE id = $id LIMIT 1"
        );
        return $result->row ?? [];
    }

    public function getByIds(array $ids): array {
        $ids = $this->normalizeIds($ids, 100);
        if (!$ids) {
            return [];
        }

        $result = $this->db->query(
            "SELECT * FROM `{$this->prefix}knowledge`
             WHERE id IN (" . implode(',', $ids) . ")
             ORDER BY id DESC"
        );
        return $result->rows ?? [];
    }

    





    public function add(string $title, string $content): int {
        $p       = $this->prefix;
        $title   = $this->db->escape($title);
        $content = $this->db->escape($content);
        $now     = date('Y-m-d H:i:s');

        $this->db->query(
            "INSERT INTO `{$p}knowledge`
                (title, content, section_id, status, date_added, date_modified)
             VALUES
                ('$title', '$content', " . KnowledgeSection::DOCUMENT . ", 'pending', '$now', '$now')"
        );
        return (int)$this->db->getLastId();
    }

    




    public function updateManualDocument(int $id, string $title, string $content): array {
        $row = $this->getById($id);
        if (empty($row) || (int)($row['section_id'] ?? 0) !== KnowledgeSection::DOCUMENT) {
            return [];
        }

        $this->deleteVector((int)($row['vector_id'] ?? 0));

        $p       = $this->prefix;
        $title   = $this->db->escape($title);
        $content = $this->db->escape($content);
        $now     = date('Y-m-d H:i:s');

        $this->db->query(
            "UPDATE `{$p}knowledge`
             SET title = '$title',
                 content = '$content',
                 vector_id = NULL,
                 status = 'pending',
                 error = NULL,
                 date_modified = '$now'
             WHERE id = $id"
        );

        return $this->getById($id);
    }

    




    public function toggleDisabled(int $id): string {
        $row = $this->getById($id);
        if (empty($row) || !in_array($row['status'], ['active', 'disabled'], true)) {
            return '';
        }

        $new_status = $row['status'] === 'disabled' ? 'active' : 'disabled';
        $now        = date('Y-m-d H:i:s');
        $p          = $this->prefix;

        $this->db->query(
            "UPDATE `{$p}knowledge`
             SET status = '$new_status', date_modified = '$now'
             WHERE id = $id"
        );
        return $new_status;
    }

    



    public function resetToPending(int $id): array {
        $row = $this->getById($id);
        if (empty($row) || $row['status'] !== 'failed') {
            return [];
        }

        $now = date('Y-m-d H:i:s');
        $p   = $this->prefix;

        $this->db->query(
            "UPDATE `{$p}knowledge`
             SET status = 'pending', error = NULL, date_modified = '$now'
             WHERE id = $id"
        );

        return $row;  
    }

    public function resetFailedToPendingByIds(array $ids): array {
        $ids = $this->normalizeIds($ids, 100);
        if (!$ids) {
            return [];
        }

        $p = $this->prefix;
        $rows = $this->db->query(
            "SELECT * FROM `{$p}knowledge`
             WHERE id IN (" . implode(',', $ids) . ") AND status = 'failed'"
        )->rows ?? [];

        if (!$rows) {
            return [];
        }

        $matched_ids = [];
        foreach ($rows as $row) {
            $matched_ids[] = (int)($row['id'] ?? 0);
        }

        $now = date('Y-m-d H:i:s');
        $this->db->query(
            "UPDATE `{$p}knowledge`
             SET status = 'pending', error = NULL, date_modified = '$now'
             WHERE id IN (" . implode(',', $matched_ids) . ")"
        );

        return $rows;
    }

    public function setStatusByIds(array $ids, string $status, array $allowed_current_statuses): int {
        $ids = $this->normalizeIds($ids, 100);
        if (!$ids || !in_array($status, ['active', 'disabled'], true)) {
            return 0;
        }

        $allowed = [];
        foreach ($allowed_current_statuses as $current_status) {
            $current_status = (string)$current_status;
            if (in_array($current_status, ['active', 'disabled'], true)) {
                $allowed[$current_status] = "'" . $this->db->escape($current_status) . "'";
            }
        }
        if (!$allowed) {
            return 0;
        }

        $rows = $this->db->query(
            "SELECT id FROM `{$this->prefix}knowledge`
             WHERE id IN (" . implode(',', $ids) . ")
               AND status IN (" . implode(',', $allowed) . ")"
        )->rows ?? [];

        $matched_ids = [];
        foreach ($rows as $row) {
            $matched_ids[] = (int)($row['id'] ?? 0);
        }
        if (!$matched_ids) {
            return 0;
        }

        $now = date('Y-m-d H:i:s');
        $status_e = $this->db->escape($status);
        $this->db->query(
            "UPDATE `{$this->prefix}knowledge`
             SET status = '{$status_e}', date_modified = '$now'
             WHERE id IN (" . implode(',', $matched_ids) . ")"
        );

        return count($matched_ids);
    }

    


    public function delete(int $id): void {
        $p   = $this->prefix;
        $row = $this->getById($id);

        $this->deleteVector((int)($row['vector_id'] ?? 0));

        $this->db->query("DELETE FROM `{$p}knowledge` WHERE id = $id");
    }

    public function deleteByIds(array $ids): int {
        $ids = $this->normalizeIds($ids, 100);
        if (!$ids) {
            return 0;
        }

        $rows = $this->getByIds($ids);
        if (!$rows) {
            return 0;
        }

        $matched_ids = [];
        foreach ($rows as $row) {
            $matched_ids[] = (int)($row['id'] ?? 0);
            $this->deleteVector((int)($row['vector_id'] ?? 0));
        }

        $this->db->query(
            "DELETE FROM `{$this->prefix}knowledge`
             WHERE id IN (" . implode(',', $matched_ids) . ")"
        );

        return count($matched_ids);
    }

    private function buildFilterWhere(array $filters): string {
        $where = ['1=1'];

        $query = trim((string)($filters['q'] ?? ''));
        if ($query !== '') {
            $query = mb_substr(strip_tags($query), 0, 120, 'UTF-8');
            $query_e = $this->db->escape($query);
            $where[] = "(title LIKE '%{$query_e}%' OR content LIKE '%{$query_e}%')";
        }

        $status = (string)($filters['status'] ?? '');
        if (in_array($status, ['pending', 'indexing', 'active', 'disabled', 'failed'], true)) {
            $where[] = "status = '" . $this->db->escape($status) . "'";
        }

        $section_id = (int)($filters['section_id'] ?? 0);
        if (in_array($section_id, [KnowledgeSection::DOCUMENT, KnowledgeSection::PRODUCT, KnowledgeSection::PAGE], true)) {
            $where[] = "section_id = " . $section_id;
        }

        return implode(' AND ', $where);
    }

    private function normalizeIds(array $ids, int $limit = 100): array {
        $normalized = [];
        foreach ($ids as $id) {
            $id = (int)$id;
            if ($id > 0) {
                $normalized[$id] = $id;
            }
            if (count($normalized) >= $limit) {
                break;
            }
        }
        return array_values($normalized);
    }

    private function deleteVector(int $vector_id): void {
        if ($vector_id <= 0) {
            return;
        }

        try {
            require_once DIR_LIBRARY . DS . 'vector_table' . DS . 'vectorTable.php';
            $vectorTable = new \D1medrol\MysqlVector\VectorTable(
                $this->db->getRawConnection(),
                VECTOR_STORAGE,
                768
            );
            $vectorTable->delete($vector_id);
        } catch (\Throwable $e) {
             
             
        }
    }

    private function extractSearchTerms(string $query): array {
        $query = mb_strtolower(strip_tags($query), 'UTF-8');
        $query = preg_replace('/[^\p{L}\p{N}\s-]+/u', ' ', $query);
        $parts = preg_split('/\s+/u', trim((string)$query));
        $terms = [];

        foreach ($parts as $part) {
            $part = trim($part, "- \t\n\r\0\x0B");
            if (mb_strlen($part, 'UTF-8') < 3) {
                continue;
            }
            $terms[$part] = true;
            if (count($terms) >= 4) {
                break;
            }
        }

        return array_keys($terms);
    }

    private function normalizeSnippetText($value): string {
        $value = strip_tags((string)$value);
        $value = str_replace("\0", '', $value);
        $value = preg_replace('/[ \t]+/u', ' ', $value);
        $value = preg_replace('/\R{3,}/u', "\n\n", $value);
        return trim($value);
    }

    private function limitSnippetChars(string $value, int $limit): string {
        $limit = max(1, $limit);
        if (mb_strlen($value, 'UTF-8') <= $limit) {
            return $value;
        }
        return rtrim(mb_substr($value, 0, max(1, $limit - 1), 'UTF-8')) . '…';
    }
}
