prefix . 'aipcf_chats'; } public static function installed(): bool { return (int) get_option(self::VERSION_OPTION) === self::DB_VERSION; } public static function install(): void { if (self::installed()) { return; } global $wpdb; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $table = self::table(); dbDelta("CREATE TABLE {$table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, conv char(32) NOT NULL, user_id bigint(20) unsigned NOT NULL DEFAULT 0, name varchar(60) NOT NULL DEFAULT '', ts datetime NOT NULL, question text NOT NULL, answer text NOT NULL, found tinyint(1) NOT NULL DEFAULT 0, score float NULL, sources varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY conv (conv), KEY ts (ts), KEY user_id (user_id) ) {$wpdb->get_charset_collate()};"); update_option(self::VERSION_OPTION, self::DB_VERSION, true); } public static function drop(): void { global $wpdb; $wpdb->query($wpdb->prepare('DROP TABLE IF EXISTS %i', self::table())); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange delete_option(self::VERSION_OPTION); } /** * Saves one question and its answer. * * @param list $sources */ public static function add(string $conv, string $name, string $question, string $answer, bool $found, ?float $score, array $sources): void { global $wpdb; self::install(); $wpdb->insert(self::table(), [ // phpcs:ignore WordPress.DB.DirectDatabaseQuery 'conv' => substr(preg_replace('/[^a-f0-9]/', '', strtolower($conv)) ?: md5(uniqid('', true)), 0, 32), 'user_id' => get_current_user_id(), 'name' => mb_substr($name, 0, 60), 'ts' => current_time('mysql', true), 'question' => mb_substr($question, 0, 2000), 'answer' => mb_substr($answer, 0, 4000), 'found' => $found ? 1 : 0, 'score' => $score === null ? null : round($score, 3), 'sources' => mb_substr(implode(',', array_map('intval', $sources)), 0, 255), ]); } /** * Conversations, newest first: one row each, with its first question and counts. * * @return list */ public static function conversations(int $limit, int $offset = 0, bool $unansweredOnly = false): array { global $wpdb; if (!self::installed()) { return []; } $rows = $unansweredOnly ? $wpdb->get_results($wpdb->prepare('SELECT conv, MAX(user_id) AS user_id, MAX(name) AS name, MIN(ts) AS started, MAX(ts) AS last, COUNT(*) AS messages, SUM(found = 0) AS unanswered, MIN(id) AS first_id FROM %i GROUP BY conv HAVING unanswered > 0 ORDER BY last DESC LIMIT %d OFFSET %d', self::table(), $limit, $offset), ARRAY_A) // phpcs:ignore WordPress.DB.DirectDatabaseQuery : $wpdb->get_results($wpdb->prepare('SELECT conv, MAX(user_id) AS user_id, MAX(name) AS name, MIN(ts) AS started, MAX(ts) AS last, COUNT(*) AS messages, SUM(found = 0) AS unanswered, MIN(id) AS first_id FROM %i GROUP BY conv ORDER BY last DESC LIMIT %d OFFSET %d', self::table(), $limit, $offset), ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery $out = []; foreach ((array) $rows as $r) { $first = (string) $wpdb->get_var($wpdb->prepare('SELECT question FROM %i WHERE id = %d', self::table(), (int) $r['first_id'])); // phpcs:ignore WordPress.DB.DirectDatabaseQuery $out[] = [ 'conv' => (string) $r['conv'], 'user_id' => (int) $r['user_id'], 'name' => (string) $r['name'], 'started' => (string) $r['started'], 'last' => (string) $r['last'], 'messages' => (int) $r['messages'], 'unanswered' => (int) $r['unanswered'], 'first' => $first, ]; } return $out; } public static function conversationCount(bool $unansweredOnly = false): int { global $wpdb; if (!self::installed()) { return 0; } if ($unansweredOnly) { return (int) $wpdb->get_var($wpdb->prepare('SELECT COUNT(DISTINCT conv) FROM %i WHERE found = 0', self::table())); // phpcs:ignore WordPress.DB.DirectDatabaseQuery } return (int) $wpdb->get_var($wpdb->prepare('SELECT COUNT(DISTINCT conv) FROM %i', self::table())); // phpcs:ignore WordPress.DB.DirectDatabaseQuery } /** * The questions and answers of conversations, oldest first within each. * * @param list $convs * @return array>> */ public static function messages(array $convs): array { global $wpdb; $convs = array_values(array_filter(array_map(static fn($c): string => preg_replace('/[^a-f0-9]/', '', (string) $c) ?? '', $convs))); if ($convs === [] || !self::installed()) { return []; } $placeholders = implode(',', array_fill(0, count($convs), '%s')); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $placeholders is only "%s,%s,...", one per spread id $rows = $wpdb->get_results($wpdb->prepare("SELECT * FROM %i WHERE conv IN ({$placeholders}) ORDER BY id", self::table(), ...$convs), ARRAY_A); $out = []; foreach ((array) $rows as $r) { $out[(string) $r['conv']][] = $r; } return $out; } /** * The questions the site had no answer for, most asked first: what to write next. * * @return list */ public static function unanswered(int $limit = 20): array { global $wpdb; if (!self::installed()) { return []; } $rows = $wpdb->get_results($wpdb->prepare('SELECT LOWER(question) AS q, MIN(question) AS question, COUNT(*) AS times, MAX(ts) AS last FROM %i WHERE found = 0 GROUP BY q ORDER BY times DESC, last DESC LIMIT %d', self::table(), $limit), ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery return array_map(static fn(array $r): array => ['question' => (string) $r['question'], 'times' => (int) $r['times'], 'last' => (string) $r['last']], (array) $rows); } /** * Every saved question and answer, oldest first, in pages (for the CSV). * * @return list> */ public static function page(int $afterId, int $limit): array { global $wpdb; if (!self::installed()) { return []; } return (array) $wpdb->get_results($wpdb->prepare('SELECT * FROM %i WHERE id > %d ORDER BY id LIMIT %d', self::table(), $afterId, $limit), ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery } public static function clear(): int { global $wpdb; if (!self::installed()) { return 0; } return (int) $wpdb->query($wpdb->prepare('DELETE FROM %i', self::table())); // phpcs:ignore WordPress.DB.DirectDatabaseQuery } public static function schedule(): void { $next = wp_next_scheduled(self::HOOK); if (self::installed() && !$next) { wp_schedule_event(time() + HOUR_IN_SECONDS, 'daily', self::HOOK); } elseif (!self::installed() && $next) { wp_clear_scheduled_hook(self::HOOK); } } /** * Deletes chats older than the retention period. */ public static function prune(): int { global $wpdb; if (!self::installed()) { return 0; } $before = gmdate('Y-m-d H:i:s', time() - DAY_IN_SECONDS * max(1, (int) Settings::get('chat_retention_days'))); return (int) $wpdb->query($wpdb->prepare('DELETE FROM %i WHERE ts < %s', self::table(), $before)); // phpcs:ignore WordPress.DB.DirectDatabaseQuery } /** * A deleted account's chats go with it (they hold the member's own words). */ public static function forgetUser(int $userId): void { global $wpdb; if ($userId > 0 && self::installed()) { $wpdb->delete(self::table(), ['user_id' => $userId]); // phpcs:ignore WordPress.DB.DirectDatabaseQuery } } // ================================================================ personal data /** * @param array $exporters * @return array */ public static function registerExporter($exporters): array { $exporters = (array) $exporters; $exporters['aipcf-chats'] = ['exporter_friendly_name' => __('Site assistant chats', 'aipcf-ai-provider-for-cloudflare'), 'callback' => [self::class, 'export']]; return $exporters; } /** * @param array $erasers * @return array */ public static function registerEraser($erasers): array { $erasers = (array) $erasers; $erasers['aipcf-chats'] = ['eraser_friendly_name' => __('Site assistant chats', 'aipcf-ai-provider-for-cloudflare'), 'callback' => [self::class, 'erase']]; return $erasers; } /** * @return array{data: list>, done: bool} */ public static function export(string $email, int $page = 1): array { global $wpdb; $user = get_user_by('email', $email); if (!$user instanceof \WP_User || !self::installed()) { return ['data' => [], 'done' => true]; } $per = 100; $rows = (array) $wpdb->get_results($wpdb->prepare('SELECT * FROM %i WHERE user_id = %d ORDER BY id LIMIT %d OFFSET %d', self::table(), $user->ID, $per, ($page - 1) * $per), ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery $data = []; foreach ($rows as $r) { $data[] = [ 'group_id' => 'aipcf-chats', 'group_label' => __('Site assistant chats', 'aipcf-ai-provider-for-cloudflare'), 'item_id' => 'aipcf-chat-' . (int) $r['id'], 'data' => [ ['name' => __('Time (UTC)', 'aipcf-ai-provider-for-cloudflare'), 'value' => (string) $r['ts']], ['name' => __('Name given', 'aipcf-ai-provider-for-cloudflare'), 'value' => (string) $r['name']], ['name' => __('Question', 'aipcf-ai-provider-for-cloudflare'), 'value' => (string) $r['question']], ['name' => __('Answer', 'aipcf-ai-provider-for-cloudflare'), 'value' => (string) $r['answer']], ], ]; } return ['data' => $data, 'done' => count($rows) < $per]; } /** * @return array{items_removed: bool, items_retained: bool, messages: list, done: bool} */ public static function erase(string $email, int $page = 1): array { global $wpdb; $user = get_user_by('email', $email); $removed = false; if ($user instanceof \WP_User && self::installed()) { $removed = (int) $wpdb->delete(self::table(), ['user_id' => $user->ID]) > 0; // phpcs:ignore WordPress.DB.DirectDatabaseQuery } return ['items_removed' => $removed, 'items_retained' => false, 'messages' => [], 'done' => true]; } }