<?php

declare(strict_types=1);

namespace PrettyLinks\Pro\Tools;

// 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.
use PrettyLinks\Pro\Replacements\CacheInvalidator;
use PrettyLinks\Pro\Replacements\KeywordRepository;

/**
 * Tool service — find and resolve keyword text that's attached to more
 * than one link. "Resolve" = keep one keyword row, delete the other rows
 * (cascading their prli_post_keywords index entries).
 *
 * Matching is case-insensitive by design: "WordPress", "WordPress", and
 * "WordPress" attached to different links are the same conflict from a
 * replacement-output standpoint. We group by LOWER(text) so the collation
 * of the `text` column doesn't change behavior.
 */
class DuplicateKeywords
{
    /**
     * Keyword repository used to read and delete keyword rows.
     *
     * @var KeywordRepository
     */
    private KeywordRepository $keywords;

    /**
     * Cache invalidator flushed after keyword rows are removed.
     *
     * @var CacheInvalidator
     */
    private CacheInvalidator $invalidator;

    /**
     * Constructor.
     *
     * @param KeywordRepository $keywords    Keyword repository dependency.
     * @param CacheInvalidator  $invalidator Cache invalidator dependency.
     */
    public function __construct(KeywordRepository $keywords, CacheInvalidator $invalidator)
    {
        $this->keywords    = $keywords;
        $this->invalidator = $invalidator;
    }

    /**
     * Find keyword text attached to more than one link, grouped case-insensitively.
     *
     * @return array<int, array{text: string, links: array<int, array<string, mixed>>}>
     */
    public function findGroups(): array
    {
        global $wpdb;
        // Only consider keyword rows whose link is not soft-deleted — a
        // trashed link's keywords shouldn't show up as a live conflict.
        // LOWER() for case-insensitive grouping regardless of collation.
        $rows = $wpdb->get_results(
            "SELECT k.id AS keyword_id, k.text, k.link_id,
                    l.name, l.slug, l.url
             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
               AND LOWER(k.text) IN (
                   SELECT t FROM (
                       SELECT LOWER(k2.text) AS t
                       FROM {$wpdb->prefix}prli_keywords k2
                       INNER JOIN {$wpdb->prefix}prli_links l2 ON l2.id = k2.link_id
                       WHERE l2.deleted_at IS NULL
                       GROUP BY LOWER(k2.text)
                       HAVING COUNT(DISTINCT k2.link_id) > 1
                   ) sub
               )
             ORDER BY LOWER(k.text) ASC, k.id ASC",
            ARRAY_A
        ) ?: [];

        $groups = [];
        foreach ($rows as $row) {
            $key  = strtolower((string) $row['text']);
            $text = (string) $row['text'];
            if (!isset($groups[$key])) {
                $groups[$key] = [
                    // Display text: first variant seen. Individual per-link
                    // rows still carry their own original casing below, so
                    // users can tell case variants apart in the picker.
                    'text'  => $text,
                    'links' => [],
                ];
            }
            $groups[$key]['links'][] = [
                'id'         => (int) $row['link_id'],
                'keyword_id' => (int) $row['keyword_id'],
                'text'       => $text,
                'name'       => (string) $row['name'],
                'slug'       => (string) $row['slug'],
                'url'        => (string) $row['url'],
            ];
        }
        return array_values($groups);
    }

    /**
     * Keep $keepKeywordId; delete every other keyword row whose text
     * case-insensitively matches the keeper's text. Cascades
     * prli_post_keywords for the deleted ids.
     *
     * @param  integer $keepKeywordId The keyword row id to keep.
     * @return array{removed: int, kept: int, text: string}
     */
    public function resolve(int $keepKeywordId): array
    {
        global $wpdb;
        // Use the keeper's own text as the match basis. Avoids a
        // client/server disagreement about which "group" is being
        // collapsed — the server picks it from the keeper's row.
        $keepText = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT text FROM {$wpdb->prefix}prli_keywords WHERE id = %d",
                $keepKeywordId
            )
        );
        if ($keepText === null) {
            return [
                'removed' => 0,
                'kept'    => 0,
                'text'    => '',
            ];
        }

        $ids      = $wpdb->get_col(
            $wpdb->prepare(
                "SELECT id FROM {$wpdb->prefix}prli_keywords
                 WHERE LOWER(text) = LOWER(%s) AND id != %d",
                (string) $keepText,
                $keepKeywordId
            )
        ) ?: [];
        $toDelete = array_map('intval', $ids);

        foreach ($toDelete as $id) {
            $this->keywords->delete($id);
        }
        if ($toDelete) {
            // Joined fields change on `all()` and the list cache needs to flush.
            $this->invalidator->onKeywordRemoved();
        }
        return [
            'removed' => count($toDelete),
            'kept'    => 1,
            'text'    => (string) $keepText,
        ];
    }
}
