<?php
namespace EasyChat\Model;

class Reply extends Base {
    const SCOPE_GLOBAL = 'global';
    const SCOPE_PERSONAL = 'personal';
    const MAX_PER_SCOPE = 50;
    const MAX_TITLE_LENGTH = 40;
    const MAX_TEXT_LENGTH = 4000;

    private function ensureTableExists() {
        $this->ensureLegacyTableExists();
        $this->ensureQuickRepliesTableExists();
        $this->migrateLegacyRepliesOnce();
    }

    private function ensureLegacyTableExists() {
        try {
            $this->db->query("CREATE TABLE IF NOT EXISTS `{$this->prefix}reply` (
                `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `manager_id` INT(11) DEFAULT NULL,
                `json` MEDIUMTEXT NOT NULL,
                FOREIGN KEY (`manager_id`) REFERENCES `{$this->prefix}managers` (`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        } catch (\Exception $e) {
             
            try {
                $this->db->query("CREATE TABLE IF NOT EXISTS `{$this->prefix}reply` (
                    `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    `manager_id` INT(11) DEFAULT NULL,
                    `json` MEDIUMTEXT NOT NULL
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
            } catch (\Exception $ex) {}
        }
    }

    private function ensureQuickRepliesTableExists() {
        try {
            $this->db->query("CREATE TABLE IF NOT EXISTS `{$this->prefix}quick_replies` (
                `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `scope` VARCHAR(16) NOT NULL DEFAULT 'personal',
                `manager_id` INT(11) DEFAULT NULL,
                `title` VARCHAR(80) NOT NULL DEFAULT '',
                `text` MEDIUMTEXT NOT NULL,
                `sort_order` INT(11) NOT NULL DEFAULT 0,
                `is_active` TINYINT(1) NOT NULL DEFAULT 1,
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                INDEX `idx_scope_manager_active` (`scope`, `manager_id`, `is_active`, `sort_order`),
                FOREIGN KEY (`manager_id`) REFERENCES `{$this->prefix}managers` (`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        } catch (\Exception $e) {
            try {
                $this->db->query("CREATE TABLE IF NOT EXISTS `{$this->prefix}quick_replies` (
                    `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    `scope` VARCHAR(16) NOT NULL DEFAULT 'personal',
                    `manager_id` INT(11) DEFAULT NULL,
                    `title` VARCHAR(80) NOT NULL DEFAULT '',
                    `text` MEDIUMTEXT NOT NULL,
                    `sort_order` INT(11) NOT NULL DEFAULT 0,
                    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
                    `date_added` DATETIME NOT NULL,
                    `date_modified` DATETIME NOT NULL,
                    INDEX `idx_scope_manager_active` (`scope`, `manager_id`, `is_active`, `sort_order`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
            } catch (\Exception $ex) {}
        }
    }

    private function migrateLegacyRepliesOnce() {
        $migration_key = 'data_migration_20260614_quick_replies_legacy_json';
        try {
            $query = $this->db->query("SELECT `value` FROM `{$this->prefix}settings` WHERE `key` = '" . $this->db->escape($migration_key) . "' LIMIT 1");
            if ($query && $query->num_rows) {
                return;
            }

            $legacy = $this->db->query("SELECT `manager_id`, `json` FROM `{$this->prefix}reply` WHERE `json` != ''");
            foreach ($legacy->rows as $row) {
                $manager_id = (int)$row['manager_id'];
                if ($manager_id <= 0 || $this->countScopeReplies(self::SCOPE_PERSONAL, $manager_id) > 0) {
                    continue;
                }

                $items = json_decode((string)$row['json'], true);
                if (!is_array($items)) {
                    continue;
                }

                $this->replaceScopeReplies(self::SCOPE_PERSONAL, $manager_id, $items);
            }

            $this->db->query("INSERT INTO `{$this->prefix}settings` (`key`, `value`) VALUES ('" . $this->db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
        } catch (\Exception $e) {
             
             
        }
    }

    public function getReplyJsonByManagerId($manager_id) {
        $this->ensureTableExists();
        try {
            $replies = $this->getScopeReplies(self::SCOPE_PERSONAL, (int)$manager_id);
            $texts = array_map(function($reply) {
                return $reply['text'];
            }, $replies);
            return json_encode($texts, JSON_UNESCAPED_UNICODE);
        } catch (\Exception $e) {
            return '';
        }
    }

    public function hasReply($manager_id) {
        $this->ensureTableExists();
        try {
            return $this->countScopeReplies(self::SCOPE_PERSONAL, (int)$manager_id) > 0;
        } catch (\Exception $e) {
            return false;
        }
    }

    public function saveReply($manager_id, $json_data) {
        $this->ensureTableExists();
        $items = json_decode((string)$json_data, true);
        if (!is_array($items)) {
            $items = [];
        }
        $this->replaceScopeReplies(self::SCOPE_PERSONAL, (int)$manager_id, $items);
    }

    public function getGroupedReplies($manager_id) {
        $this->ensureTableExists();
        return [
            self::SCOPE_GLOBAL => $this->getScopeReplies(self::SCOPE_GLOBAL, 0),
            self::SCOPE_PERSONAL => $this->getScopeReplies(self::SCOPE_PERSONAL, (int)$manager_id),
        ];
    }

    public function getQuickRepliesForManager($manager_id) {
        $this->ensureTableExists();
        $global = $this->getScopeReplies(self::SCOPE_GLOBAL, 0);
        $personal = $this->getScopeReplies(self::SCOPE_PERSONAL, (int)$manager_id);
        return array_merge($global, $personal);
    }

    public function saveScopedReplies($scope, $manager_id, array $items) {
        $this->ensureTableExists();
        $scope = $this->normalizeScope($scope);
        $manager_id = $scope === self::SCOPE_GLOBAL ? 0 : (int)$manager_id;
        $this->upsertScopeReplies($scope, $manager_id, $items);
    }

    public function reorderScopedReplies($scope, $manager_id, array $ids) {
        $this->ensureTableExists();
        $scope = $this->normalizeScope($scope);
        $manager_id = $scope === self::SCOPE_GLOBAL ? 0 : (int)$manager_id;
        $ids = $this->normalizeIds($ids);
        if (!$ids) {
            return;
        }

        $owned_ids = $this->ownedReplyIds($scope, $manager_id);
        if (count($ids) !== count($owned_ids)) {
            throw new \InvalidArgumentException('Invalid quick reply order payload');
        }

        $ordered_ids = $ids;
        $ids_for_compare = $ids;
        $owned_ids_for_compare = $owned_ids;
        sort($ids_for_compare);
        sort($owned_ids_for_compare);
        if ($ids_for_compare !== $owned_ids_for_compare) {
            throw new \InvalidArgumentException('Quick reply order contains foreign or missing rows');
        }

        $sort = 10;
        foreach ($ordered_ids as $id) {
            $this->db->query("UPDATE `{$this->prefix}quick_replies` SET
                `sort_order` = " . (int)$sort . ",
                `date_modified` = NOW()
                WHERE `id` = " . (int)$id);
            $sort += 10;
        }
    }

    private function getScopeReplies($scope, $manager_id) {
        $scope = $this->normalizeScope($scope);
        $manager_sql = $scope === self::SCOPE_GLOBAL
            ? "(`manager_id` IS NULL OR `manager_id` = 0)"
            : "`manager_id` = " . (int)$manager_id;

        $query = $this->db->query("SELECT `id`, `scope`, `manager_id`, `title`, `text`, `sort_order`
            FROM `{$this->prefix}quick_replies`
            WHERE `scope` = '" . $this->db->escape($scope) . "'
              AND {$manager_sql}
              AND `is_active` = 1
            ORDER BY `sort_order` ASC, `id` ASC");

        $rows = [];
        foreach ($query->rows as $row) {
            $rows[] = $this->formatReplyRow($row);
        }
        return $rows;
    }

    private function countScopeReplies($scope, $manager_id) {
        $scope = $this->normalizeScope($scope);
        $manager_sql = $scope === self::SCOPE_GLOBAL
            ? "(`manager_id` IS NULL OR `manager_id` = 0)"
            : "`manager_id` = " . (int)$manager_id;
        $query = $this->db->query("SELECT COUNT(*) AS total FROM `{$this->prefix}quick_replies`
            WHERE `scope` = '" . $this->db->escape($scope) . "' AND {$manager_sql}");
        return (int)($query->row['total'] ?? 0);
    }

    private function replaceScopeReplies($scope, $manager_id, array $items) {
        $scope = $this->normalizeScope($scope);
        $manager_id = $scope === self::SCOPE_GLOBAL ? 0 : (int)$manager_id;
        $manager_condition = $scope === self::SCOPE_GLOBAL
            ? "(`manager_id` IS NULL OR `manager_id` = 0)"
            : "`manager_id` = " . (int)$manager_id;

        $this->db->query("DELETE FROM `{$this->prefix}quick_replies`
            WHERE `scope` = '" . $this->db->escape($scope) . "' AND {$manager_condition}");

        $normalized = $this->normalizeItems($items);
        $sort = 10;
        foreach ($normalized as $item) {
            $manager_sql = $scope === self::SCOPE_GLOBAL ? 'NULL' : (int)$manager_id;
            $this->db->query("INSERT INTO `{$this->prefix}quick_replies` SET
                `scope` = '" . $this->db->escape($scope) . "',
                `manager_id` = {$manager_sql},
                `title` = '" . $this->db->escape($item['title']) . "',
                `text` = '" . $this->db->escape($item['text']) . "',
                `sort_order` = " . (int)$sort . ",
                `is_active` = 1,
                `date_added` = NOW(),
                `date_modified` = NOW()");
            $sort += 10;
        }
    }

    private function upsertScopeReplies($scope, $manager_id, array $items) {
        $scope = $this->normalizeScope($scope);
        $manager_id = $scope === self::SCOPE_GLOBAL ? 0 : (int)$manager_id;
        $owned_ids = $this->ownedReplyIds($scope, $manager_id);
        $normalized = $this->normalizeItems($items);
        $kept_ids = [];
        $sort = 10;

        foreach ($normalized as $item) {
            $id = (int)($item['id'] ?? 0);
            $manager_sql = $scope === self::SCOPE_GLOBAL ? 'NULL' : (int)$manager_id;

            if ($id > 0 && in_array($id, $owned_ids, true) && !in_array($id, $kept_ids, true)) {
                $this->db->query("UPDATE `{$this->prefix}quick_replies` SET
                    `title` = '" . $this->db->escape($item['title']) . "',
                    `text` = '" . $this->db->escape($item['text']) . "',
                    `sort_order` = " . (int)$sort . ",
                    `is_active` = 1,
                    `date_modified` = NOW()
                    WHERE `id` = " . (int)$id);
                $kept_ids[] = $id;
            } else {
                $this->db->query("INSERT INTO `{$this->prefix}quick_replies` SET
                    `scope` = '" . $this->db->escape($scope) . "',
                    `manager_id` = {$manager_sql},
                    `title` = '" . $this->db->escape($item['title']) . "',
                    `text` = '" . $this->db->escape($item['text']) . "',
                    `sort_order` = " . (int)$sort . ",
                    `is_active` = 1,
                    `date_added` = NOW(),
                    `date_modified` = NOW()");
            }

            $sort += 10;
        }

        $delete_ids = array_values(array_diff($owned_ids, $kept_ids));
        if ($delete_ids) {
            $this->db->query("DELETE FROM `{$this->prefix}quick_replies`
                WHERE `id` IN (" . implode(',', array_map('intval', $delete_ids)) . ")");
        }
    }

    private function ownedReplyIds($scope, $manager_id) {
        $scope = $this->normalizeScope($scope);
        $manager_sql = $scope === self::SCOPE_GLOBAL
            ? "(`manager_id` IS NULL OR `manager_id` = 0)"
            : "`manager_id` = " . (int)$manager_id;
        $query = $this->db->query("SELECT `id` FROM `{$this->prefix}quick_replies`
            WHERE `scope` = '" . $this->db->escape($scope) . "'
              AND {$manager_sql}
              AND `is_active` = 1
            ORDER BY `sort_order` ASC, `id` ASC");

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

    private function normalizeItems(array $items) {
        $result = [];
        foreach ($items as $item) {
            $reply = $this->normalizeItem($item);
            if ($reply === null) {
                continue;
            }
            $result[] = $reply;
            if (count($result) >= self::MAX_PER_SCOPE) {
                break;
            }
        }
        return $result;
    }

    private function normalizeItem($item) {
        if (is_array($item)) {
            $title = isset($item['title']) ? (string)$item['title'] : '';
            $text = isset($item['text']) ? (string)$item['text'] : '';
        } else {
            $title = '';
            $text = (string)$item;
        }

        $text = $this->sanitizeText($text);
        if ($text === '') {
            return null;
        }

        $title = $this->sanitizeTitle($title);
        if ($title === '') {
            $title = $this->deriveTitle($text);
        }

        return [
            'id' => $id,
            'title' => $title,
            'text' => $text,
        ];
    }

    private function normalizeIds(array $ids) {
        $result = [];
        foreach ($ids as $id) {
            $id = (int)$id;
            if ($id <= 0 || in_array($id, $result, true)) {
                continue;
            }
            $result[] = $id;
            if (count($result) >= self::MAX_PER_SCOPE) {
                break;
            }
        }
        return $result;
    }

    private function formatReplyRow(array $row) {
        $scope = $this->normalizeScope($row['scope'] ?? self::SCOPE_PERSONAL);
        $id = (int)($row['id'] ?? 0);
        return [
            'id' => ($scope === self::SCOPE_GLOBAL ? 'g_' : 'm_') . $id,
            'raw_id' => $id,
            'scope' => $scope,
            'manager_id' => isset($row['manager_id']) ? (int)$row['manager_id'] : 0,
            'title' => $this->sanitizeTitle($row['title'] ?? ''),
            'text' => $this->sanitizeText($row['text'] ?? ''),
            'sort_order' => (int)($row['sort_order'] ?? 0),
        ];
    }

    private function normalizeScope($scope) {
        return (string)$scope === self::SCOPE_GLOBAL ? self::SCOPE_GLOBAL : self::SCOPE_PERSONAL;
    }

    private function sanitizeTitle($value) {
        $value = trim(strip_tags((string)$value));
        $value = preg_replace('/\s+/u', ' ', $value);
        return $this->limitChars($value, self::MAX_TITLE_LENGTH);
    }

    private function sanitizeText($value) {
        $value = str_replace("\0", '', (string)$value);
        $value = trim($value);
        return $this->limitChars($value, self::MAX_TEXT_LENGTH);
    }

    private function deriveTitle($text) {
        $title = preg_replace('/\s+/u', ' ', trim((string)$text));
        return $this->limitChars($title, self::MAX_TITLE_LENGTH);
    }

    private function limitChars($value, $limit) {
        $value = (string)$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;
    }
}
