<?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.

/**
 * CRUD for `prli_keywords` and `prli_post_keywords`.
 */
class KeywordRepository
{
    public const ALL_TRANSIENT    = 'prli_kw_all';
    public const INDEXED_META_KEY = '_plp_post_keywords_updated_at';

    /**
     * In-request memoization of all() — survives one request lifetime.
     *
     * @var array<int, array<string, mixed>>|null
     */
    private ?array $allMemo = null;

    /**
     * All active keywords joined with their link row. Result is stored in
     * a transient and a per-request static so repeat callers (feed path,
     * comment filter, lazy indexing batches) don't re-hydrate from the DB.
     *
     * Invalidated by CacheInvalidator whenever the underlying data could
     * change — see the invalidation table in docs for the full matrix.
     *
     * @return array<int, array<string, mixed>>
     */
    public function all(): array
    {
        if ($this->allMemo !== null) {
            return $this->allMemo;
        }
        $cached = get_transient(self::ALL_TRANSIENT);
        if (is_array($cached)) {
            return $this->allMemo = $cached;
        }

        global $wpdb;
        $rows = $wpdb->get_results(
            "SELECT k.id, k.link_id, k.text AS keyword, k.replace_limit,
                    l.name, l.slug, l.url, l.nofollow, l.sponsored, l.new_window, l.deleted_at
             FROM {$wpdb->prefix}prli_keywords k
             INNER JOIN {$wpdb->prefix}prli_links l ON l.id = k.link_id
             WHERE l.deleted_at IS NULL
             ORDER BY LENGTH(k.text) DESC, k.id DESC",
            ARRAY_A
        ) ?: [];
        $out  = array_map([self::class, 'hydrate'], $rows);
        set_transient(self::ALL_TRANSIENT, $out, HOUR_IN_SECONDS);
        return $this->allMemo = $out;
    }

    /**
     * Drop the in-request memo (e.g., immediately after writes).
     */
    public function forgetAllMemo(): void
    {
        $this->allMemo = null;
    }

    /**
     * Find a single keyword by id, joined with its link row.
     *
     * @param integer $id The keyword id to find.
     *
     * @return array<string, mixed>|null
     */
    public function find(int $id): ?array
    {
        global $wpdb;
        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT k.id, k.link_id, k.text AS keyword, k.replace_limit,
                        l.name, l.slug, l.url, l.nofollow, l.sponsored, l.new_window, l.deleted_at
                 FROM {$wpdb->prefix}prli_keywords k
                 INNER JOIN {$wpdb->prefix}prli_links l ON l.id = k.link_id
                 WHERE k.id = %d LIMIT 1",
                $id
            ),
            ARRAY_A
        );
        return is_array($row) ? self::hydrate($row) : null;
    }

    /**
     * Create a new keyword row.
     *
     * @param array<string, mixed> $data The keyword data (keyword, link_id, replace_limit).
     *
     * @return array<string, mixed>
     */
    public function create(array $data): array
    {
        global $wpdb;
        $keyword = trim((string) ($data['keyword'] ?? ''));
        $linkId  = (int) ($data['link_id'] ?? 0);
        if ($keyword === '' || $linkId <= 0) {
            return ['error' => 'keyword_and_link_id_required'];
        }
        $row = [
            'link_id'       => $linkId,
            'text'          => $keyword,
            'created_at'    => current_time('mysql', true),
            'replace_limit' => (int) ($data['replace_limit'] ?? 0),
        ];
        $wpdb->insert($wpdb->prefix . 'prli_keywords', $row);
        $id = (int) $wpdb->insert_id;
        return $this->find($id) ?? [];
    }

    /**
     * Update an existing keyword row.
     *
     * @param integer              $id   The keyword id to update.
     * @param array<string, mixed> $data The fields to patch (keyword, link_id, replace_limit).
     *
     * @return array<string, mixed>|null
     */
    public function update(int $id, array $data): ?array
    {
        global $wpdb;
        $patch = [];
        if (array_key_exists('keyword', $data)) {
            $patch['text'] = (string) $data['keyword'];
        }
        if (array_key_exists('link_id', $data)) {
            $patch['link_id'] = (int) $data['link_id'];
        }
        if (array_key_exists('replace_limit', $data)) {
            $patch['replace_limit'] = (int) $data['replace_limit'];
        }
        if (!$patch) {
            return $this->find($id);
        }
        $wpdb->update($wpdb->prefix . 'prli_keywords', $patch, ['id' => $id]);
        return $this->find($id);
    }

    /**
     * Delete a keyword and its index rows.
     *
     * @param integer $id The keyword id to delete.
     *
     * @return boolean
     */
    public function delete(int $id): bool
    {
        global $wpdb;
        $wpdb->delete($wpdb->prefix . 'prli_post_keywords', ['keyword_id' => $id]);
        return (bool) $wpdb->delete($wpdb->prefix . 'prli_keywords', ['id' => $id]);
    }

    /**
     * All keyword rows indexed against a given post.
     *
     * @param integer $postId The post id to fetch keywords for.
     *
     * @return array<int, array<string, mixed>>
     */
    public function forPost(int $postId): array
    {
        global $wpdb;
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT k.id, k.link_id, k.text AS keyword, k.replace_limit,
                        l.name, l.slug, l.url, l.nofollow, l.sponsored, l.new_window, l.deleted_at
                 FROM {$wpdb->prefix}prli_post_keywords pk
                 INNER JOIN {$wpdb->prefix}prli_keywords k ON k.id = pk.keyword_id
                 INNER JOIN {$wpdb->prefix}prli_links l ON l.id = k.link_id
                 WHERE pk.post_id = %d
                   AND l.deleted_at IS NULL
                 ORDER BY LENGTH(k.text) DESC, k.id DESC",
                $postId
            ),
            ARRAY_A
        ) ?: [];
        return array_map([self::class, 'hydrate'], $rows);
    }

    /**
     * Index a single keyword against a post.
     *
     * @param integer $postId    The post id.
     * @param integer $keywordId The keyword id to link.
     *
     * @return void
     */
    public function linkPostKeyword(int $postId, int $keywordId): void
    {
        $this->linkPostKeywordsMany($postId, [$keywordId]);
    }

    /**
     * Batch variant of linkPostKeyword — one INSERT for all matched keyword IDs.
     *
     * @param integer $postId     The post id.
     * @param int[]   $keywordIds The keyword ids to link.
     *
     * @return void
     */
    public function linkPostKeywordsMany(int $postId, array $keywordIds): void
    {
        if (!$keywordIds) {
            return;
        }
        global $wpdb;
        $placeholders = [];
        $values       = [];
        foreach ($keywordIds as $id) {
            $placeholders[] = '(%d,%d)';
            $values[]       = $postId;
            $values[]       = (int) $id;
        }
        $sql = "INSERT IGNORE INTO {$wpdb->prefix}prli_post_keywords (post_id, keyword_id) VALUES "
            . implode(',', $placeholders);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $wpdb->query($wpdb->prepare($sql, ...$values));
    }

    /**
     * Remove all index rows for a single post.
     *
     * @param integer $postId The post id to clear.
     *
     * @return void
     */
    public function clearPostKeywords(int $postId): void
    {
        global $wpdb;
        $wpdb->delete($wpdb->prefix . 'prli_post_keywords', ['post_id' => $postId]);
    }

    /**
     * Remove this keyword's index rows across all posts. Used when a
     * keyword's text has changed so prior matches are stale.
     *
     * @param integer $keywordId The keyword id to remove from the index.
     *
     * @return void
     */
    public function clearKeywordFromIndex(int $keywordId): void
    {
        global $wpdb;
        $wpdb->delete($wpdb->prefix . 'prli_post_keywords', ['keyword_id' => $keywordId]);
    }

    /**
     * Wipe the entire post-keyword index.
     *
     * @return void
     */
    public function clearAllPostKeywords(): void
    {
        global $wpdb;
        $wpdb->query("DELETE FROM {$wpdb->prefix}prli_post_keywords");
    }

    /**
     * Whether the post has ever been indexed.
     *
     * @param integer $postId The post id to check.
     *
     * @return boolean
     */
    public function isIndexed(int $postId): bool
    {
        return (bool) get_post_meta($postId, self::INDEXED_META_KEY, true);
    }

    /**
     * Post's "synced as of" stamp. '' when the post has never been indexed.
     *
     * @param integer $postId The post id to fetch the stamp for.
     *
     * @return string
     */
    public function syncedStamp(int $postId): string
    {
        return (string) get_post_meta($postId, self::INDEXED_META_KEY, true);
    }

    /**
     * Keywords created after the given stamp, oldest first.
     *
     * `prli_keywords` is the source of truth for when a keyword came into
     * existence — every path that creates one writes this row, so unlike
     * the add-log it cannot miss a write. Catch-up uses it to backstop the
     * log, so an add that never got logged can't strand a post on a stale
     * index forever. Every in-code path does log, so the triggers are a
     * missing `prli_keyword_adds` table, a failed insert, or out-of-band
     * writes straight to the DB from migration or sync tooling.
     *
     * Joined against `prli_links` on `deleted_at IS NULL` to match all(),
     * which is what the full-scan indexPost() walks. Without the join the
     * incremental path would write index rows for keywords on trashed links
     * that a full re-scan of the same post would have skipped.
     *
     * Shape matches KeywordAddLog::since() so the two merge cleanly.
     *
     * @param string $stamp The MySQL UTC timestamp to fetch keywords after.
     *
     * @return array<int, array{keyword_id:int, keyword_text:string}>
     */
    public function createdSince(string $stamp): array
    {
        global $wpdb;
        $rows = (array) $wpdb->get_results(
            $wpdb->prepare(
                "SELECT k.id AS keyword_id, k.text AS keyword_text
                 FROM {$wpdb->prefix}prli_keywords k
                 INNER JOIN {$wpdb->prefix}prli_links l ON l.id = k.link_id
                 WHERE k.created_at > %s
                   AND l.deleted_at IS NULL
                 ORDER BY k.created_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 keywords created after the given stamp. Lets catch-up decide
     * between replay and a full re-scan without loading any rows.
     *
     * @param string $stamp The MySQL UTC timestamp to count keywords after.
     *
     * @return integer
     */
    public function countCreatedSince(string $stamp): int
    {
        global $wpdb;
        return (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*)
                 FROM {$wpdb->prefix}prli_keywords k
                 INNER JOIN {$wpdb->prefix}prli_links l ON l.id = k.link_id
                 WHERE k.created_at > %s
                   AND l.deleted_at IS NULL",
                $stamp
            )
        );
    }

    /**
     * All keyword rows for a given link (with full hydrated shape).
     *
     * @param integer $linkId The link id to fetch keywords for.
     *
     * @return array<int, array<string, mixed>>
     */
    public function forLink(int $linkId): array
    {
        if ($linkId <= 0) {
            return [];
        }
        global $wpdb;
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT k.id, k.link_id, k.text AS keyword, k.replace_limit,
                        l.name, l.slug, l.url, l.nofollow, l.sponsored, l.new_window, l.deleted_at
                 FROM {$wpdb->prefix}prli_keywords k
                 INNER JOIN {$wpdb->prefix}prli_links l ON l.id = k.link_id
                 WHERE k.link_id = %d AND l.deleted_at IS NULL
                 ORDER BY LENGTH(k.text) DESC, k.id DESC",
                $linkId
            ),
            ARRAY_A
        ) ?: [];
        return array_map([self::class, 'hydrate'], $rows);
    }

    /**
     * Bulk variant of textForLink — one query for N links. Returns a map
     * keyed by link id with each value being the ordered keyword string
     * list for that link. Missing IDs map to an empty list.
     *
     * @param int[] $linkIds The link ids to fetch keyword text for.
     *
     * @return array<int, string[]>
     */
    public function textForLinks(array $linkIds): array
    {
        $ids = array_values(array_unique(array_filter(array_map('intval', $linkIds))));
        $out = [];
        foreach ($ids as $id) {
            $out[$id] = [];
        }
        if (!$ids) {
            return $out;
        }
        global $wpdb;
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
        $sql = "SELECT link_id, text
                  FROM {$wpdb->prefix}prli_keywords
                 WHERE link_id IN ({$placeholders})
                 ORDER BY LENGTH(text) DESC, id DESC";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $rows = (array) $wpdb->get_results($wpdb->prepare($sql, ...$ids), ARRAY_A);
        foreach ($rows as $row) {
            $lid = (int) $row['link_id'];
            if (!isset($out[$lid])) {
                continue;
            }
            $out[$lid][] = (string) $row['text'];
        }
        return $out;
    }

    /**
     * Plain list of keyword strings for a link — convenience for the
     * per-link form field on the link edit screen.
     *
     * @param integer $linkId The link id to fetch keyword text for.
     *
     * @return string[]
     */
    public function textForLink(int $linkId): array
    {
        if ($linkId <= 0) {
            return [];
        }
        global $wpdb;
        $rows = $wpdb->get_col(
            $wpdb->prepare(
                "SELECT text FROM {$wpdb->prefix}prli_keywords
                 WHERE link_id = %d
                 ORDER BY LENGTH(text) DESC, id DESC",
                $linkId
            )
        ) ?: [];
        return array_map('strval', $rows);
    }

    /**
     * Replace the full keyword set for a link. Merge-style: keywords
     * already attached that aren't in $newTexts are deleted (cascading
     * to prli_post_keywords); keywords in $newTexts that aren't already
     * attached get inserted. Existing rows keep their replace_limit
     * setting.
     *
     * V3 drop-in: matches PlpKeyword::updateLinkKeywords() semantics
     * from `current-version/pretty-link/pro/app/models/PlpKeyword.php`.
     *
     * @param integer  $linkId   The link id whose keyword set is replaced.
     * @param string[] $newTexts The full new list of keyword texts.
     *
     * @return array{added: int, removed: int, kept: int, added_keywords: array<int, array{id:int, text:string}>}
     */
    public function setForLink(int $linkId, array $newTexts): array
    {
        if ($linkId <= 0) {
            return [
                'added'          => 0,
                'removed'        => 0,
                'kept'           => 0,
                'added_keywords' => [],
            ];
        }

        // Normalize: trim, drop empties, dedupe case-sensitively (matching
        // how prli_keywords stores them — one row per distinct text).
        $norm = [];
        foreach ($newTexts as $t) {
            $t = trim((string) $t);
            if ($t !== '' && !in_array($t, $norm, true)) {
                $norm[] = $t;
            }
        }

        global $wpdb;
        $existing = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, text FROM {$wpdb->prefix}prli_keywords WHERE link_id = %d",
                $linkId
            ),
            ARRAY_A
        ) ?: [];

        $existingByText = [];
        foreach ($existing as $row) {
            $existingByText[(string) $row['text']] = (int) $row['id'];
        }

        $toDelete = [];
        $kept     = 0;
        foreach ($existingByText as $text => $id) {
            if (in_array($text, $norm, true)) {
                ++$kept;
            } else {
                $toDelete[] = $id;
            }
        }

        $toAdd = [];
        foreach ($norm as $text) {
            if (!isset($existingByText[$text])) {
                $toAdd[] = $text;
            }
        }

        // Delete first — cascade to prli_post_keywords so the index stays clean.
        if ($toDelete) {
            $placeholders = implode(',', array_fill(0, count($toDelete), '%d'));
            $wpdb->query($wpdb->prepare(
                "DELETE FROM {$wpdb->prefix}prli_post_keywords WHERE keyword_id IN ({$placeholders})",
                ...$toDelete
            ));
            $wpdb->query($wpdb->prepare(
                "DELETE FROM {$wpdb->prefix}prli_keywords WHERE id IN ({$placeholders})",
                ...$toDelete
            ));
        }

        // Insert added. replace_limit defaults to 0; fine-grain tuning
        // happens on the central Keywords admin page.
        $now           = current_time('mysql', true);
        $addedKeywords = [];
        foreach ($toAdd as $text) {
            $wpdb->insert($wpdb->prefix . 'prli_keywords', [
                'link_id'       => $linkId,
                'text'          => $text,
                'created_at'    => $now,
                'replace_limit' => 0,
            ]);
            $addedKeywords[] = [
                'id'   => (int) $wpdb->insert_id,
                'text' => $text,
            ];
        }

        return [
            'added'          => count($toAdd),
            'removed'        => count($toDelete),
            'kept'           => $kept,
            'added_keywords' => $addedKeywords,
        ];
    }

    /**
     * Normalize a raw DB row into the canonical keyword shape.
     *
     * @param array<string, mixed> $row The raw joined database row.
     *
     * @return array<string, mixed>
     */
    private static function hydrate(array $row): array
    {
        return [
            'id'            => (int) $row['id'],
            'link_id'       => (int) $row['link_id'],
            'keyword'       => (string) $row['keyword'],
            'replace_limit' => (int) ($row['replace_limit'] ?? 0),
            'name'          => (string) ($row['name'] ?? ''),
            'slug'          => (string) ($row['slug'] ?? ''),
            'url'           => (string) ($row['url'] ?? ''),
            'nofollow'      => (bool) ($row['nofollow'] ?? 0),
            'sponsored'     => (bool) ($row['sponsored'] ?? 0),
            'new_window'    => (bool) ($row['new_window'] ?? 0),
        ];
    }
}
