<?php

if (! defined('ABSPATH')) {
    exit;
}

/**
 * Conversation storage and retrieval: session listings, search, message
 * history, deletion, and per-event token usage logging.
 */
class RSVL_Conversations
{
    /**
     * Get paginated conversation sessions, most recent first.
     *
     * @param int $per_page Number of sessions per page.
     * @param int $offset   Row offset.
     * @return array Each element has session_id, started_at, updated_at, message_count, first_message, page_url.
     */
    public function get_conversation_sessions(int $per_page, int $offset = 0, string $filter = 'all'): array
    {
        global $wpdb;
        $table = RSVL_Schema::chats_table();
        $where = self::filter_where_clause($filter, $table);

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the filter's condition is fixed SQL, from filter_condition().
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT session_id,
                    MIN(created_at) AS started_at,
                    MAX(created_at) AS updated_at,
                    MAX(is_admin) AS is_admin,
                    COUNT(*) AS message_count
             FROM %i
             {$where}
             GROUP BY session_id
             ORDER BY MAX(created_at) DESC
             LIMIT %d OFFSET %d",
            $table,
            $per_page,
            $offset
        ), ARRAY_A);
        // phpcs:enable

        return $this->hydrate_session_rows($rows);
    }

    /**
     * Search conversations by message content (both user and assistant messages).
     *
     * @param string $query    Search term.
     * @param int    $per_page Results per page.
     * @param int    $offset   Row offset.
     * @param string $filter   Same filters as get_conversation_sessions(), applied in addition to the search.
     * @return array Same shape as get_conversation_sessions().
     */
    public function search_conversations(string $query, int $per_page, int $offset = 0, string $filter = 'all'): array
    {
        global $wpdb;
        $table = RSVL_Schema::chats_table();
        $like  = '%' . $wpdb->esc_like($query) . '%';
        $and   = self::filter_and_clause($filter, $table);

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the filter's condition is fixed SQL, from filter_condition().
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT session_id,
                    MIN(created_at) AS started_at,
                    MAX(created_at) AS updated_at,
                    MAX(is_admin) AS is_admin,
                    COUNT(*) AS message_count
             FROM %i
             WHERE session_id IN (SELECT DISTINCT session_id FROM %i WHERE message LIKE %s)
             {$and}
             GROUP BY session_id
             ORDER BY MAX(created_at) DESC
             LIMIT %d OFFSET %d",
            $table,
            $table,
            $like,
            $per_page,
            $offset
        ), ARRAY_A);
        // phpcs:enable

        return $this->hydrate_session_rows($rows);
    }

    /**
     * Count distinct sessions that contain at least one message matching $query.
     *
     * @param string $query  Search term.
     * @param string $filter Same filters as get_conversation_count(), applied in addition to the search.
     */
    public function count_search_results(string $query, string $filter = 'all'): int
    {
        global $wpdb;
        $table = RSVL_Schema::chats_table();
        $like  = '%' . $wpdb->esc_like($query) . '%';
        $and   = self::filter_and_clause($filter, $table);

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the filter's condition is fixed SQL, from filter_condition().
        return (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT session_id) FROM %i
             WHERE session_id IN (SELECT DISTINCT session_id FROM %i WHERE message LIKE %s)
             {$and}",
            $table,
            $table,
            $like
        ));
        // phpcs:enable
    }

    /**
     * Count distinct conversation sessions.
     */
    public function get_conversation_count(string $filter = 'all'): int
    {
        global $wpdb;
        $table = RSVL_Schema::chats_table();
        $where = self::filter_where_clause($filter, $table);

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the filter's condition is fixed SQL, from filter_condition().
        return (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(DISTINCT session_id) FROM %i {$where}", $table));
        // phpcs:enable
    }

    /**
     * Determine which 1-indexed page a session falls on in get_conversation_sessions()'s
     * ordering (most-recently-active sessions first), for linking back to the right page.
     */
    public function get_session_page(string $session_id, int $per_page, string $filter = 'all'): int
    {
        global $wpdb;
        $table = RSVL_Schema::chats_table();
        $where = self::filter_where_clause($filter, $table);

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the filter's condition is fixed SQL, from filter_condition().
        $rank = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM (
                 SELECT session_id, MAX(created_at) AS updated_at
                 FROM %i
                 {$where}
                 GROUP BY session_id
             ) sessions
             WHERE updated_at > (
                 SELECT MAX(created_at) FROM %i WHERE session_id = %s
             )",
            $table,
            $table,
            $session_id
        ));
        // phpcs:enable

        return (int) floor($rank / $per_page) + 1;
    }

    /** The page a chat was started on, or '' when none was recorded. */
    public function get_page_url(string $session_id): string
    {
        global $wpdb;

        return (string) $wpdb->get_var($wpdb->prepare(
            "SELECT page_url FROM %i
             WHERE session_id = %s AND page_url IS NOT NULL AND page_url != '' LIMIT 1",
            RSVL_Schema::chats_table(),
            $session_id
        ));
    }

    /**
     * A chat's most recent messages, oldest first: the visitor's and the
     * chatbot's replies, without errors, as the model is sent them.
     *
     * @return array<int, array{role: string, content: string}>
     */
    public function get_recent_history(string $session_id, int $limit): array
    {
        global $wpdb;

        $rows = $wpdb->get_results($wpdb->prepare(
            'SELECT role, message FROM %i
             WHERE session_id = %s AND is_error = 0
             ORDER BY created_at DESC, id DESC
             LIMIT %d',
            RSVL_Schema::chats_table(),
            $session_id,
            $limit
        ), ARRAY_A);

        return array_map(fn ($row) => ['role' => $row['role'], 'content' => $row['message']], array_reverse($rows ?: []));
    }

    /** The latest stored message's UTC timestamp, or null for a new session. */
    public function get_session_last_message_at(string $session_id): ?int
    {
        global $wpdb;

        $created_at = $wpdb->get_var($wpdb->prepare(
            'SELECT MAX(created_at) FROM %i WHERE session_id = %s',
            RSVL_Schema::chats_table(),
            $session_id
        ));

        return null === $created_at ? null : strtotime($created_at . ' UTC');
    }

    /**
     * Get all messages for a conversation session, ordered chronologically.
     *
     * @param string $session_id The session UUID.
     * @return array Each element has role, message, and time (formatted).
     */
    public function get_conversation_messages(string $session_id): array
    {
        global $wpdb;
        $table = RSVL_Schema::chats_table();

        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT id, role, message, rating, is_error, created_at
             FROM %i
             WHERE session_id = %s
             ORDER BY created_at ASC, id ASC",
            $table,
            $session_id
        ), ARRAY_A);

        $messages = array_map(function ($row) {
            return [
                'id'       => (int) $row['id'],
                'role'     => $row['role'],
                'message'  => $row['message'],
                'rating'   => $row['rating'],
                'is_error' => (bool) $row['is_error'],
                'time'     => wp_date(resolver_date_format('time'), strtotime($row['created_at'])),
                'ts'       => (int) strtotime($row['created_at']) * 1000,
            ];
        }, $rows ?: []);

        /**
         * Filter a chat's messages before they're sent to the Chats screen.
         *
         * @param array  $messages   As above, oldest first.
         * @param string $session_id The chat's session ID.
         */
        return (array) apply_filters('resolver_conversation_messages', $messages, $session_id);
    }

    /**
     * Delete all messages for a conversation session and unlink any notes.
     */
    public function delete_conversation(string $session_id): void
    {
        global $wpdb;

        $wpdb->delete(RSVL_Schema::chats_table(), ['session_id' => $session_id], ['%s']);

        RSVL_Index::unlink_chats([$session_id]);
    }

    /**
     * Return the model name and total token count for a conversation session.
     *
     * @param string $session_id The session UUID.
     * @return array { model: string, total_tokens: int } — model is empty string and tokens 0 if no data.
     */
    public function get_conversation_usage(string $session_id): array
    {
        global $wpdb;
        $table = RSVL_Schema::token_usage_table();

        $row = $wpdb->get_row($wpdb->prepare(
            "SELECT model,
                    SUM(prompt_tokens)     AS prompt_tokens,
                    SUM(completion_tokens) AS completion_tokens,
                    SUM(total_tokens)      AS total_tokens
             FROM %i
             WHERE type = 'chat' AND session_id = %s",
            $table,
            $session_id
        ), ARRAY_A);

        return [
            'model'        => $row['model'] ?? '',
            'total_tokens' => (int) ($row['total_tokens'] ?? 0),
        ];
    }

    /**
     * Attach first_message and page_url to aggregated session rows.
     *
     * @param array $rows Aggregated rows with session_id, started_at, updated_at, message_count.
     * @return array Hydrated session data.
     */
    private function hydrate_session_rows(array $rows): array
    {
        global $wpdb;
        $table = RSVL_Schema::chats_table();

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

        $session_ids = array_column($rows, 'session_id');

        $first_msgs = $wpdb->get_results($wpdb->prepare(
            'SELECT c.session_id, c.message, c.page_url
             FROM %i c
             INNER JOIN (
                 SELECT session_id, MIN(id) AS min_id
                 FROM %i
                 WHERE session_id IN (' . implode(',', array_fill(0, count($session_ids), '%s')) . ") AND role = 'user'
                 GROUP BY session_id
             ) f ON c.session_id = f.session_id AND c.id = f.min_id",
            array_merge([$table, $table], $session_ids)
        ), ARRAY_A);

        $first_by_session = [];
        foreach ($first_msgs as $row) {
            $first_by_session[ $row['session_id'] ] = $row;
        }

        // Find which sessions contain at least one error message.
        $error_sessions_raw = $wpdb->get_col($wpdb->prepare(
            'SELECT DISTINCT session_id FROM %i
             WHERE session_id IN (' . implode(',', array_fill(0, count($session_ids), '%s')) . ') AND is_error = 1',
            array_merge([$table], $session_ids)
        ));
        $error_sessions = array_flip($error_sessions_raw);

        // Find which sessions contain liked or disliked messages.
        $rated_rows = $wpdb->get_results($wpdb->prepare(
            'SELECT DISTINCT session_id, rating FROM %i
             WHERE session_id IN (' . implode(',', array_fill(0, count($session_ids), '%s')) . ') AND rating IS NOT NULL',
            array_merge([$table], $session_ids)
        ), ARRAY_A);

        $liked_sessions    = [];
        $disliked_sessions = [];
        foreach ($rated_rows as $rated) {
            if ('up' === $rated['rating']) {
                $liked_sessions[ $rated['session_id'] ] = true;
            } elseif ('down' === $rated['rating']) {
                $disliked_sessions[ $rated['session_id'] ] = true;
            }
        }

        $sessions = [];
        foreach ($rows as $row) {
            $first      = $first_by_session[ $row['session_id'] ] ?? null;
            $page_url   = $first ? ($first['page_url'] ?? '') : '';
            $sessions[] = [
                'session_id'    => $row['session_id'],
                'started_at'    => $row['started_at'],
                'updated_at'    => $row['updated_at'],
                'message_count' => (int) $row['message_count'],
                'first_message' => $first ? $first['message'] : '',
                'page_url'      => $page_url,
                'is_admin'      => (bool) $row['is_admin'],
                'has_error'     => isset($error_sessions[ $row['session_id'] ]),
                'has_like'      => isset($liked_sessions[ $row['session_id'] ]),
                'has_dislike'   => isset($disliked_sessions[ $row['session_id'] ]),
            ];
        }

        /**
         * Filter the sessions shown in the Chats list, to add flags of its own.
         *
         * @param array    $sessions    As above.
         * @param string[] $session_ids Their session IDs.
         */
        return (array) apply_filters('resolver_conversation_sessions', $sessions, $session_ids);
    }

    /**
     * The Chats list's filters, value => label, in display order. Each value
     * other than 'all' needs a condition in filter_condition(), or from the
     * resolver_conversation_filter_condition filter.
     */
    public static function get_filters(): array
    {
        /**
         * Filter the Chats list's filters. Pro adds Likes, Dislikes and Human
         * Requests.
         *
         * @param array $filters Value => label.
         */
        return (array) apply_filters('resolver_conversation_filters', [
            'all'         => __('All', 'resolver-ai-chatbot'),
            'hide_admin'  => __('Hide Admin', 'resolver-ai-chatbot'),
            'errors_only' => __('Errors', 'resolver-ai-chatbot'),
        ]);
    }

    /**
     * Log a token usage event.
     */
    public static function log_token_usage(
        string $type,
        string $provider,
        string $model,
        int    $prompt_tokens,
        int    $completion_tokens,
        int    $total_tokens,
        string $session_id = ''
    ): void {
        global $wpdb;
        $wpdb->insert(
            RSVL_Schema::token_usage_table(),
            [
                'type'              => $type,
                'provider'          => $provider,
                'model'             => $model,
                'session_id'        => $session_id ?: null,
                'prompt_tokens'     => max(0, $prompt_tokens),
                'completion_tokens' => max(0, $completion_tokens),
                'total_tokens'      => max(0, $total_tokens),
                'created_at'        => gmdate('Y-m-d H:i:s'),
            ],
            ['%s', '%s', '%s', '%s', '%d', '%d', '%d', '%s']
        );
    }

    /**
     * Whether a session contains a message logged from an admin's request.
     */
    public function is_admin_session(string $session_id): bool
    {
        global $wpdb;

        return (bool) $wpdb->get_var($wpdb->prepare(
            'SELECT 1 FROM %i WHERE session_id = %s AND is_admin = 1 LIMIT 1',
            RSVL_Schema::chats_table(),
            $session_id
        ));
    }

    /**
     * Delete whole chats once their last message is older than the retention
     * period. A chat someone kept coming back to is kept whole until it has
     * been quiet that long, rather than losing its beginning a day at a time,
     * and its notes are unlinked only once it's gone. Runs daily via the
     * resolver_cleanup_chats WP-Cron event (registered in
     * resolver_init()). Does nothing when retention is set to 0 (keep all).
     */
    public static function cleanup_old_chats(): void
    {
        $days = (int) resolver_get_setting('chat_retention_days');
        if ($days <= 0) {
            return;
        }

        global $wpdb;
        $table = RSVL_Schema::chats_table();
        $batch = 500;

        // In batches of chats, so a large backlog isn't one huge query.
        do {
            $sessions = $wpdb->get_col($wpdb->prepare(
                'SELECT session_id FROM %i
                 GROUP BY session_id
                 HAVING MAX(created_at) < DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)
                 LIMIT %d',
                $table,
                $days,
                $batch
            ));
            if (empty($sessions)) {
                return;
            }

            $deleted = $wpdb->query($wpdb->prepare(
                'DELETE FROM %i WHERE session_id IN (' . implode(',', array_fill(0, count($sessions), '%s')) . ')',
                array_merge([$table], $sessions)
            ));

            RSVL_Index::unlink_chats($sessions);
        } while (false !== $deleted && count($sessions) === $batch);
    }

    /**
     * Build a boolean condition for the given conversation filter, suitable
     * for combining with other conditions via AND.
     * 'hide_admin'  — exclude sessions containing an admin's message.
     * 'errors_only' — only sessions that contain at least one error message.
     */
    private static function filter_condition(string $filter, string $table): string
    {
        if ($filter === 'hide_admin') {
            return "session_id NOT IN (
                        SELECT DISTINCT session_id FROM {$table}
                        WHERE is_admin = 1
                    )";
        }
        if ($filter === 'errors_only') {
            return "session_id IN (
                        SELECT DISTINCT session_id FROM {$table}
                        WHERE is_error = 1
                    )";
        }

        /**
         * Filter the SQL condition for a Chats list filter added through
         * resolver_conversation_filters. It's used in a WHERE clause as is,
         * so it must not contain user input.
         *
         * @param string $condition '' for no condition.
         * @param string $filter    The filter's value.
         * @param string $table     The conversations table.
         */
        return (string) apply_filters('resolver_conversation_filter_condition', '', $filter, $table);
    }

    /**
     * Build a WHERE clause for the given conversation filter.
     */
    private static function filter_where_clause(string $filter, string $table): string
    {
        $condition = self::filter_condition($filter, $table);

        return $condition === '' ? '' : "WHERE {$condition}";
    }

    /**
     * Build an AND clause for the given conversation filter, to add to a
     * WHERE clause.
     */
    private static function filter_and_clause(string $filter, string $table): string
    {
        $condition = self::filter_condition($filter, $table);

        return $condition === '' ? '' : "AND {$condition}";
    }
}