<?php

declare(strict_types=1);

namespace PrettyLinks\Pro\Replacements;

// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_meta_key
// phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_meta_value
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter
// Custom plugin tables (prli_*): table names interpolated from $wpdb->prefix (trusted),
// user values bind through $wpdb->prepare(). No caching: these tables are the source
// of truth for click/redirect data and must read-through. "meta_key"/"meta_value" here
// refer to our own prli_link_metas table, not wp_postmeta.

/**
 * Append-only log of keyword-add events in `prli_keyword_adds`.
 *
 * Keyword *removals* are applied surgically the moment they happen
 * (DELETE FROM prli_post_keywords WHERE keyword_id = ?), so posts never
 * have to catch up on them. Adds are different — determining whether an
 * existing post contains a newly-added keyword requires scanning that
 * post's content, which we defer to the post's next render. This log is
 * how the renderer knows what it missed.
 */
class KeywordAddLog
{
    public const TABLE = 'prli_keyword_adds';

    /**
     * Threshold above which a post with a stale stamp should do a full
     * scan instead of replaying individual log entries. Dormant posts
     * with thousands of backlogged adds run faster this way.
     */
    public const REPLAY_THRESHOLD = 200;

    /**
     * Append a keyword-add event to the log.
     *
     * @param integer $keywordId The id of the keyword that was added.
     * @param string  $text      The keyword text that was added.
     *
     * @return void
     */
    public function write(int $keywordId, string $text): void
    {
        if ($keywordId <= 0 || $text === '') {
            return;
        }
        global $wpdb;
        $wpdb->insert(
            $wpdb->prefix . self::TABLE,
            [
                'occurred_at'  => current_time('mysql', true),
                'keyword_id'   => $keywordId,
                'keyword_text' => $text,
            ]
        );
    }

    /**
     * Entries newer than the given stamp, chronological order. Dead
     * keywords (deleted after the add) are filtered out via the inner
     * join, so callers never see zombie rows.
     *
     * @param string $stamp The MySQL UTC timestamp to fetch entries after.
     *
     * @return array<int, array{keyword_id:int, keyword_text:string}>
     */
    public function since(string $stamp): array
    {
        global $wpdb;
        $table    = $wpdb->prefix . self::TABLE;
        $keywords = $wpdb->prefix . 'prli_keywords';
        $rows     = (array) $wpdb->get_results(
            $wpdb->prepare(
                "SELECT l.keyword_id, l.keyword_text
                 FROM {$table} l
                 INNER JOIN {$keywords} k ON k.id = l.keyword_id
                 WHERE l.occurred_at > %s
                 ORDER BY l.occurred_at ASC",
                $stamp
            ),
            ARRAY_A
        );
        return array_map(
            static fn(array $r): array => [
                'keyword_id'   => (int) $r['keyword_id'],
                'keyword_text' => (string) $r['keyword_text'],
            ],
            $rows
        );
    }

    /**
     * Count log entries newer than the given stamp.
     *
     * @param string $stamp The MySQL UTC timestamp to count entries after.
     *
     * @return integer
     */
    public function countSince(string $stamp): int
    {
        global $wpdb;
        $table = $wpdb->prefix . self::TABLE;
        return (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$table} WHERE occurred_at > %s",
                $stamp
            )
        );
    }
}
