<?php
// SPDX-License-Identifier: GPL-3.0-or-later
declare(strict_types=1);
namespace Aiwend\Chat;

use Aiwend\Db\Connection;
use Aiwend\Policy\Capabilities;
use Aiwend\Policy\Principal;

/** Bounded operator reads. Session credentials and visitor identifiers are never selected. */
final class Conversations
{
    public function __construct(private Connection $db) {}

    private function authorize(Principal $principal): void
    {
        if (!$principal->isAuthenticated() || !$principal->can(Capabilities::EXPORT_CONVERSATIONS)) {
            throw new ChatException('forbidden', 403);
        }
    }

    public function listing(Principal $principal, string $search = '', int $bot = 0, int $page = 1, int $rating = 0, int $since = 0, string $attention = ''): array
    {
        $this->authorize($principal);
        $where = 's.site_id = ?'; $args = [$principal->siteId];
        if ($bot > 0) { $where .= ' AND s.bot_id = ?'; $args[] = $bot; }
        if ($since > 0) { $where .= ' AND s.created_at >= ?'; $args[] = $since; }
        // The filters below are probes per candidate session on indexed columns (messages: session_id, feedback: the primary key through its
        // message), and the counts are computed after the page is chosen. The Phase 112 query counted every candidate session through
        // dependent subqueries, which with no InnoDB statistics (a fresh or freshly restored database) meant table scans per session before
        // LIMIT. The plan can still differ by server and statistics (MySQL ignores the inner LIMIT as a plan barrier, MariaDB materializes the
        // subquery), so the bound is measured (Phase113ConversationListingCostMysqlTest, under a statement time cap), not guaranteed by structure.
        if (in_array($rating, [-1, 1], true)) {
            $where .= ' AND EXISTS (SELECT 1 FROM {messages} m JOIN {message_feedback} f ON f.site_id = m.site_id AND f.message_id = m.id WHERE m.site_id = s.site_id AND m.session_id = s.id AND f.rating = ? LIMIT 1)'; $args[] = $rating;
        }
        // Needs attention (Stage D): an answer that found no source (exact outcome, or the labelled older reading) or a not-helpful rating.
        $noSource = "EXISTS (SELECT 1 FROM {messages} m WHERE m.site_id = s.site_id AND m.session_id = s.id AND m.role = 'assistant' AND ((" . SupportInsights::SQL_EXACT . ') OR (' . SupportInsights::SQL_INFERRED . ')) LIMIT 1)';
        $notUseful = 'EXISTS (SELECT 1 FROM {messages} m JOIN {message_feedback} f ON f.site_id = m.site_id AND f.message_id = m.id WHERE m.site_id = s.site_id AND m.session_id = s.id AND f.rating = -1 LIMIT 1)';
        if ($attention === 'no_source') {
            $where .= ' AND ' . $noSource;
        } elseif ($attention === 'attention') {
            $where .= ' AND (' . $noSource . ' OR ' . $notUseful . ')';
        }
        if ($search !== '') {
            $where .= " AND EXISTS (SELECT 1 FROM {messages} m WHERE m.site_id = s.site_id AND m.session_id = s.id AND m.content LIKE ? ESCAPE '!' LIMIT 1)";
            $args[] = '%' . str_replace(['!', '%', '_'], ['!!', '!%', '!_'], mb_substr($search, 0, 100)) . '%';
        }
        $args[] = (max(1, min(10000, $page)) - 1) * 50;
        // The page first (at most 51 rows), then the counts for exactly those sessions: the work no longer depends on the size of the history.
        $rows = $this->db->select("SELECT s.id, s.bot_id, s.created_at, s.expires_at, s.revoked, b.name
            FROM {chat_sessions} s LEFT JOIN {bots} b ON b.id = s.bot_id AND b.site_id = s.site_id AND b.status <> 'deleted'
            WHERE $where ORDER BY s.id DESC LIMIT 51 OFFSET ?", $args);
        if ($rows === []) {
            return [];
        }
        $ids = array_map(static fn(array $r): int => (int) $r['id'], $rows);
        $in = implode(',', array_fill(0, count($ids), '?'));
        $messages = [];
        foreach ($this->db->select("SELECT session_id, COUNT(*) AS n FROM {messages} WHERE site_id = ? AND session_id IN ($in) GROUP BY session_id", [$principal->siteId, ...$ids]) as $r) {
            $messages[(int) $r['session_id']] = (int) $r['n'];
        }
        $rated = [];
        foreach ($this->db->select("SELECT m.session_id, f.rating, COUNT(*) AS n FROM {messages} m JOIN {message_feedback} f ON f.site_id = m.site_id AND f.message_id = m.id WHERE m.site_id = ? AND m.session_id IN ($in) AND f.rating IN (1, -1) GROUP BY m.session_id, f.rating", [$principal->siteId, ...$ids]) as $r) {
            $rated[(int) $r['session_id']][(int) $r['rating']] = (int) $r['n'];
        }
        $flags = [];
        foreach ($this->db->select("SELECT m.session_id,
                COALESCE(SUM(CASE WHEN m.outcome = 'no_match' THEN 1 ELSE 0 END), 0) AS no_match,
                COALESCE(SUM(CASE WHEN m.outcome = 'withdrawn' THEN 1 ELSE 0 END), 0) AS withdrawn,
                COALESCE(SUM(CASE WHEN " . SupportInsights::SQL_INFERRED . " THEN 1 ELSE 0 END), 0) AS inferred
             FROM {messages} m WHERE m.site_id = ? AND m.session_id IN ($in) AND m.role = 'assistant' GROUP BY m.session_id", [$principal->siteId, ...$ids]) as $r) {
            $flags[(int) $r['session_id']] = $r;
        }
        foreach ($rows as &$row) {
            $id = (int) $row['id'];
            $f = $flags[$id] ?? [];
            // Reason codes, most certain first. no_match_inferred is a best-effort reading of an older row and is always shown as such.
            $row['reasons'] = array_values(array_filter([
                (int) ($f['no_match'] ?? 0) > 0 ? 'no_match' : null,
                (int) ($f['withdrawn'] ?? 0) > 0 ? 'withdrawn' : null,
                (int) ($f['inferred'] ?? 0) > 0 ? 'no_match_inferred' : null,
                ($rated[$id][-1] ?? 0) > 0 ? 'not_useful' : null,
            ]));
            $row['message_count'] = $messages[$id] ?? 0;
            $row['useful'] = $rated[$id][1] ?? 0;
            $row['not_useful'] = $rated[$id][-1] ?? 0;
        }
        unset($row);
        return $rows;
    }

    public function transcript(Principal $principal, int $session, int $page = 1, int $limit = 100): array
    {
        $this->authorize($principal);
        if ($this->db->selectOne('SELECT id FROM {chat_sessions} WHERE site_id = ? AND id = ?', [$principal->siteId, $session]) === null) {
            throw new ChatException('not_found', 404);
        }
        $limit = max(1, min(1000, $limit));
        $rows = $this->db->select('SELECT m.id, m.role, m.content, m.request_id, m.created_at, m.outcome, f.rating FROM {messages} m LEFT JOIN {message_feedback} f ON f.site_id = m.site_id AND f.message_id = m.id WHERE m.site_id = ? AND m.session_id = ? ORDER BY m.seq LIMIT ? OFFSET ?', [$principal->siteId, $session, $limit + 1, (max(1, min(10000, $page)) - 1) * $limit]);
        foreach ($rows as &$row) {
            $row['rating'] = $row['rating'] === null ? null : (int) $row['rating'];
            $row['created_at'] = (int) $row['created_at'];
            $row['citations'] = $this->db->select('SELECT n, title, anchor FROM {message_citations} WHERE site_id = ? AND message_id = ? ORDER BY n', [$principal->siteId, $row['id']]);
            unset($row['id']);
        }
        return $rows;
    }
}