<?php
namespace EasyChat\Model;








class AdminHelpSession extends Base {
    public const TTL_SECONDS = 7200;

    public static function schemaQueries(string $prefix): array {
        return [
            "CREATE TABLE IF NOT EXISTS `{$prefix}admin_help_sessions` (
                `session_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `public_id` CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
                `manager_id` INT(11) NOT NULL,
                `locale` VARCHAR(8) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'ru',
                `unresolved_count` TINYINT UNSIGNED NOT NULL DEFAULT 0,
                `created_at` DATETIME NOT NULL,
                `updated_at` DATETIME NOT NULL,
                `expires_at` DATETIME NOT NULL,
                UNIQUE KEY `uniq_admin_help_public` (`public_id`),
                KEY `idx_admin_help_manager` (`manager_id`, `updated_at`),
                KEY `idx_admin_help_expires` (`expires_at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
            "CREATE TABLE IF NOT EXISTS `{$prefix}admin_help_messages` (
                `message_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `session_id` BIGINT UNSIGNED NOT NULL,
                `role` VARCHAR(16) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
                `content` TEXT NOT NULL,
                `metadata_json` MEDIUMTEXT NOT NULL,
                `request_key` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',
                `ip_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',
                `feedback` TINYINT NULL DEFAULT NULL,
                `feedback_at` DATETIME NULL DEFAULT NULL,
                `created_at` DATETIME NOT NULL,
                KEY `idx_admin_help_history` (`session_id`, `message_id`),
                KEY `idx_admin_help_rate_installation` (`role`, `created_at`),
                KEY `idx_admin_help_rate_manager` (`created_at`, `session_id`, `role`),
                KEY `idx_admin_help_rate_ip` (`ip_hash`, `created_at`, `role`),
                UNIQUE KEY `uniq_admin_help_request` (`session_id`, `role`, `request_key`),
                FOREIGN KEY (`session_id`)
                    REFERENCES `{$prefix}admin_help_sessions` (`session_id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        ];
    }

    public function findOrCreate(int $manager_id, string $public_id, string $locale): array {
        $manager_id = max(0, $manager_id);
        $locale = $locale === 'en' ? 'en' : 'ru';
        if ($manager_id <= 0) return [];

        if (preg_match('/^[a-f0-9]{32}$/D', $public_id) === 1) {
            $query = $this->db->query(
                "SELECT * FROM `{$this->prefix}admin_help_sessions`
                 WHERE `public_id` = '" . $this->db->escape($public_id) . "'
                   AND `manager_id` = {$manager_id}
                   AND `expires_at` > NOW()
                 LIMIT 1"
            );
            if ($query && $query->num_rows) {
                $this->touch((int)$query->row['session_id']);
                return $query->row;
            }
        }

        $public_id = bin2hex(random_bytes(16));
        $this->db->query(
            "INSERT INTO `{$this->prefix}admin_help_sessions` SET
             `public_id` = '" . $this->db->escape($public_id) . "',
             `manager_id` = {$manager_id},
             `locale` = '" . $this->db->escape($locale) . "',
             `created_at` = NOW(), `updated_at` = NOW(),
             `expires_at` = DATE_ADD(NOW(), INTERVAL " . self::TTL_SECONDS . " SECOND)"
        );
        return [
            'session_id' => (int)$this->db->getLastId(),
            'public_id' => $public_id,
            'manager_id' => $manager_id,
            'locale' => $locale,
            'unresolved_count' => 0,
        ];
    }

    public function history(int $session_id, int $limit = 6): array {
        $limit = max(1, min(8, $limit));
        $query = $this->db->query(
            "SELECT `message_id`, `role`, `content`, `metadata_json`, `request_key`, `feedback`, `created_at`
             FROM `{$this->prefix}admin_help_messages`
             WHERE `session_id` = {$session_id}
             ORDER BY `message_id` DESC LIMIT {$limit}"
        );
        return array_reverse(($query && is_array($query->rows)) ? $query->rows : []);
    }

    public function append(int $session_id, string $role, string $content, array $metadata, string $request_key, string $ip_hash): int {
        $role = in_array($role, ['user', 'assistant'], true) ? $role : 'assistant';
        $request_key = preg_match('/^[a-f0-9]{64}$/D', $request_key) === 1 ? $request_key : '';
        $ip_hash = preg_match('/^[a-f0-9]{64}$/D', $ip_hash) === 1 ? $ip_hash : '';
        try {
            $this->db->query(
                "INSERT INTO `{$this->prefix}admin_help_messages` SET
                 `session_id` = {$session_id},
                 `role` = '" . $this->db->escape($role) . "',
                 `content` = '" . $this->db->escape($content) . "',
                 `metadata_json` = '" . $this->db->escape((string)json_encode($metadata, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES)) . "',
                 `request_key` = '" . $this->db->escape($request_key) . "',
                 `ip_hash` = '" . $this->db->escape($ip_hash) . "',
                 `created_at` = NOW()"
            );
             
             
            $message_id = (int)$this->db->getLastId();
            $this->touch($session_id);
            return $message_id;
        } catch (\Throwable $e) {
            return 0;
        }
    }

    public function responseForRequest(int $session_id, string $request_key): array {
        if (preg_match('/^[a-f0-9]{64}$/D', $request_key) !== 1) return [];
        $query = $this->db->query(
            "SELECT `message_id`, `content`, `metadata_json`, `feedback` FROM `{$this->prefix}admin_help_messages`
             WHERE `session_id` = {$session_id} AND `role` = 'assistant'
               AND `request_key` = '" . $this->db->escape($request_key) . "'
             LIMIT 1"
        );
        if (!$query || !$query->num_rows) return [];
        $metadata = json_decode((string)$query->row['metadata_json'], true);
        return [
            'message_id' => (int)$query->row['message_id'],
            'answer' => (string)$query->row['content'],
            'metadata' => is_array($metadata) ? $metadata : [],
            'feedback' => isset($query->row['feedback']) ? (int)$query->row['feedback'] : null,
        ];
    }

    



    public function recordFeedback(int $manager_id, string $public_id, int $message_id, int $value): int {
        if ($manager_id <= 0 || $message_id <= 0 || !in_array($value, [-1, 1], true)) return 0;
        if (preg_match('/^[a-f0-9]{32}$/D', $public_id) !== 1) return 0;

        $query = $this->db->query(
            "SELECT m.`session_id` FROM `{$this->prefix}admin_help_messages` m
             INNER JOIN `{$this->prefix}admin_help_sessions` s ON s.`session_id` = m.`session_id`
             WHERE m.`message_id` = {$message_id} AND m.`role` = 'assistant'
               AND s.`manager_id` = {$manager_id}
               AND s.`public_id` = '" . $this->db->escape($public_id) . "'
               AND s.`expires_at` > NOW()
             LIMIT 1"
        );
        if (!$query || !$query->num_rows) return 0;

        $session_id = (int)$query->row['session_id'];
        $this->db->query(
            "UPDATE `{$this->prefix}admin_help_messages`
             SET `feedback` = {$value}, `feedback_at` = NOW()
             WHERE `message_id` = {$message_id} AND `session_id` = {$session_id} AND `role` = 'assistant'"
        );
        $this->touch($session_id);
        return $session_id;
    }

    public function isRateLimited(int $manager_id, string $ip_hash): bool {
        $installation = $this->db->query(
            "SELECT COUNT(*) AS total FROM `{$this->prefix}admin_help_messages`
             WHERE `role` = 'user' AND `created_at` >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)"
        );
        if ((int)($installation->row['total'] ?? 0) >= 120) return true;
        $manager = $this->db->query(
            "SELECT COUNT(*) AS total
             FROM `{$this->prefix}admin_help_messages` m
             INNER JOIN `{$this->prefix}admin_help_sessions` s ON s.`session_id` = m.`session_id`
             WHERE s.`manager_id` = {$manager_id} AND m.`role` = 'user'
               AND m.`created_at` >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)"
        );
        if ((int)($manager->row['total'] ?? 0) >= 20) return true;
        if ($ip_hash !== '') {
            $ip = $this->db->query(
                "SELECT COUNT(*) AS total FROM `{$this->prefix}admin_help_messages`
                 WHERE `ip_hash` = '" . $this->db->escape($ip_hash) . "'
                   AND `role` = 'user' AND `created_at` >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)"
            );
            if ((int)($ip->row['total'] ?? 0) >= 30) return true;
        }
        return false;
    }

    public function setUnresolvedCount(int $session_id, int $count): void {
        $count = max(0, min(3, $count));
        $this->db->query(
            "UPDATE `{$this->prefix}admin_help_sessions`
             SET `unresolved_count` = {$count}, `updated_at` = NOW(),
                 `expires_at` = DATE_ADD(NOW(), INTERVAL " . self::TTL_SECONDS . " SECOND)
             WHERE `session_id` = {$session_id}"
        );
    }

    public function reset(int $manager_id, string $public_id): void {
        if (preg_match('/^[a-f0-9]{32}$/D', $public_id) !== 1) return;
        $this->db->query(
            "DELETE FROM `{$this->prefix}admin_help_sessions`
             WHERE `manager_id` = {$manager_id}
               AND `public_id` = '" . $this->db->escape($public_id) . "'"
        );
    }

    public function cleanupExpired(): void {
        $this->db->query("DELETE FROM `{$this->prefix}admin_help_sessions` WHERE `expires_at` <= NOW() LIMIT 200");
    }

    private function touch(int $session_id): void {
        $this->db->query(
            "UPDATE `{$this->prefix}admin_help_sessions`
             SET `updated_at` = NOW(), `expires_at` = DATE_ADD(NOW(), INTERVAL " . self::TTL_SECONDS . " SECOND)
             WHERE `session_id` = {$session_id}"
        );
    }
}
