<?php

declare(strict_types=1);

namespace PrettyLinks\Pro\Database;

use wpdb;

/**
 * Pretty Links Pro schema installer.
 *
 * Creates pro-only tables (keywords, rotations, taxonomies, reports)
 * and runs pro-only v3→v4 migration steps. Invoked from Pro\Bootstrap::load()
 * on the `prli_loaded` action, after Lite's Migrator has already created the
 * core tables. Guarded by an atomic add_option() mutex (prli_migration_lock_pro).
 *
 * State is tracked separately from the free migrator under
 * `prlipro_migration_state` so the two run independently — activating
 * pro after free doesn't re-run free steps, and vice versa.
 */
class Migrator
{
    public const OPTION_STATE = 'prlipro_migration_state';

    /**
     * Seconds after which a held lock is treated as stale and reclaimed.
     * Guards against a hard fatal/timeout mid-migration leaving the lock
     * wedged forever (the finally block does not run on a fatal).
     */
    private const LOCK_TIMEOUT = 300;

    /**
     * Transient that paces retries of a failed run. When a step fails it stays
     * pending (so it retries), but without a pace a persistently-failing step
     * would re-attempt on every page load. Set only on a failed/incomplete run
     * and cleared on full success, so maybeRun() honors it regardless of
     * version — a failed upgrade keeps the stored version below target, so
     * gating on version would skip the backoff.
     */
    private const RETRY_BACKOFF_KEY = 'prlipro_migration_retry_after';

    /**
     * Seconds between retry attempts of a run that left failing steps.
     */
    private const RETRY_BACKOFF = 900;

    /**
     * Neutral collation used to compare slugs across tables in the v3→v4
     * backfill. See {@see self::collateSlug()}.
     */
    private const SLUG_COLLATION = 'utf8mb4_unicode_ci';

    /**
     * WordPress database handle.
     *
     * @var wpdb
     */
    private wpdb $db;

    /**
     * Target schema version this migrator runs toward.
     *
     * @var string
     */
    private string $version;

    /**
     * Constructor.
     *
     * @param wpdb   $db      WordPress database handle.
     * @param string $version Target schema version this migrator runs toward.
     */
    public function __construct(wpdb $db, string $version)
    {
        $this->db      = $db;
        $this->version = $version;
    }

    /**
     * Run any pending migration steps, guarded by an atomic lock.
     *
     * @return void
     */
    public function maybeRun(): void
    {
        $state          = (array) (get_option(self::OPTION_STATE, []) ?: []);
        $done           = array_keys((array) ($state['steps'] ?? []));
        $pending        = array_diff(array_keys($this->steps()), $done);
        $versionMatches = ($state['version'] ?? '') === $this->version;
        if ($versionMatches && empty($state['pending']) && empty($pending)) {
            return;
        }

        // Pace retries whenever a prior run left work outstanding. The backoff
        // transient is set only when a run fails/doesn't finish and cleared on
        // full success, so its presence always means "recently attempted, not
        // done" — honor it regardless of version. (Gating on versionMatches
        // was wrong: a failed upgrade keeps the stored version below target, so
        // the backoff would be skipped and every request would re-run.) A
        // genuine fresh update has no transient and runs immediately.
        if (get_transient(self::RETRY_BACKOFF_KEY)) {
            return;
        }

        $lockOption = 'prli_migration_lock_pro';
        $now        = time();
        if (!add_option($lockOption, $now, '', 'no')) {
            // Lock held. Reclaim it only if the previous holder died mid-run
            // (no finally on a hard fatal) and left it older than the timeout.
            // The reclaim itself is not perfectly atomic — two requests could
            // both see a stale lock and proceed — but every step is guarded by
            // its $done flag and re-running one is a no-op, so a concurrent
            // double-run after a fatal is harmless.
            $heldSince = (int) get_option($lockOption, 0);
            if ($now - $heldSince < self::LOCK_TIMEOUT) {
                return;
            }
            update_option($lockOption, $now, false);
        }

        try {
            $this->run($state);
        } finally {
            delete_option($lockOption);
        }
    }

    /**
     * Re-arm a completed step so it runs again on the next {@see self::maybeRun()}.
     * Clears its done flag, marks the state pending, and drops the retry backoff
     * so it fires promptly. Any recorded failure is deliberately LEFT in place —
     * run() owns clearing it on a successful re-run — so the state can't read
     * healthy before the step has actually re-run. This is the "trigger it
     * again" mechanism (admin retry / WP-CLI) — no new step key required.
     *
     * @param string $step Step key to re-arm (e.g. 'sync_link_terms').
     *
     * @return void
     */
    public function requeueStep(string $step): void
    {
        $state = (array) (get_option(self::OPTION_STATE, []) ?: []);
        // Only clear the DONE flag (and pace) — leave any recorded failure in
        // place. run() owns `failures`: it clears them on a successful re-run
        // and repopulates on a failing one. Clearing here would make the state
        // look healthy before the step has actually re-run.
        unset($state['steps'][$step]);
        $state['pending'] = true;
        update_option(self::OPTION_STATE, $state, false);
        delete_transient(self::RETRY_BACKOFF_KEY);
    }

    /**
     * Ordered map of migration step keys to their callables.
     *
     * @return array<string, callable>
     */
    private function steps(): array
    {
        return [
            'install_keyword_tables'           => [$this, 'installKeywordTables'],
            'install_keyword_add_log'          => [$this, 'installKeywordAddLogTable'],
            // Fresh key so installs that already ran install_keyword_tables get
            // one more dbDelta pass, which adds the KEY on prli_keywords
            // (created_at) that catch-up's countCreatedSince()/createdSince()
            // filter by. Without it that COUNT is a full scan on every
            // uncached post render — measured at ~12ms with 20k keywords,
            // versus ~0.4ms indexed. dbDelta is idempotent, so re-running
            // installKeywordTables is a no-op beyond the missing index.
            'index_keyword_created_at'         => [$this, 'installKeywordTables'],
            'install_rotation_tables'          => [$this, 'installRotationTables'],
            'install_taxonomy_tables'          => [$this, 'installTaxonomyTables'],
            'install_reports_tables'           => [$this, 'installReportsTables'],
            'install_post_url_cache'           => [$this, 'installPostUrlCacheTable'],
            // Single link→category/tag assignment migration, replacing the old
            // migrate_categories / migrate_tags / migrate_groups /
            // repair_all_assignments chain. As a fresh step key it gives every
            // already-migrated install one more healing pass; on failure it
            // retries (never silently sticks); and it can be re-armed on demand
            // via requeueStep() instead of us shipping a new key each time.
            'sync_link_terms'                  => [$this, 'syncLinkTerms'],
            'migrate_clicks_rotations'         => [$this, 'migrateClicksRotations'],
            'migrate_autocreate'               => [$this, 'migrateAutocreate'],
            'migrate_num_slug_chars'           => [$this, 'migrateNumSlugChars'],
            'migrate_public_link_display_page' => [$this, 'migratePublicLinkDisplayPage'],
            'normalise_social_buttons'         => [$this, 'normaliseSocialButtons'],
            'clear_legacy_cron_hooks'          => [$this, 'clearLegacyCronHooks'],
        ];
    }

    /**
     * Execute each pending step and persist progress as it goes.
     *
     * @param array<string, mixed> $state Current migration state blob.
     *
     * @return void
     */
    private function run(array $state): void
    {
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $steps = $this->steps();

        $done     = is_array($state['steps'] ?? null) ? (array) $state['steps'] : [];
        // Seed from the persisted failures so each per-step write reflects the
        // true, current failure set (a step that just succeeded clears its own
        // entry; a fatal before settlement leaves an accurate record). Prune
        // entries for step keys that no longer exist — otherwise a failure
        // recorded under a removed key (e.g. the old migrate_categories /
        // repair_all_assignments) would linger forever, since nothing re-runs
        // to clear it, and keep the notice/Site Health showing a phantom error.
        $failures = is_array($state['failures'] ?? null) ? (array) $state['failures'] : [];
        $failures = array_intersect_key($failures, $steps);

        foreach ($steps as $name => $callable) {
            // Each step runs exactly once (guarded by its done flag). A failed
            // step is left un-done below so it retries, and any step can be
            // re-armed on demand via requeueStep() — but nothing re-runs on
            // every update.
            if (!empty($done[$name])) {
                continue;
            }
            // A step is recorded as done ONLY when it finishes without throwing
            // and without leaving a SQL error behind. wpdb::query() returns
            // false and sets last_error on failure but never throws, so a
            // silently-failed step used to be marked complete and never retried
            // — that's the root cause of category/tag migrations wedging after
            // a single failure. Now a failed step stays pending and is retried
            // on the next run (paced by RETRY_BACKOFF_KEY).
            $this->db->last_error = '';
            try {
                $callable();
                $error = (string) $this->db->last_error;
            } catch (\Throwable $e) {
                $error = $e->getMessage() !== '' ? $e->getMessage() : get_class($e);
            }

            if ($error !== '') {
                // Drop the done flag (if a re-runnable step had one) so the step
                // stays pending and is retried rather than settling as complete,
                // and persist the failure now so it's accurate even if the run
                // fatals before settlement.
                unset($done[$name]);
                $failures[$name]   = $error;
                $state['steps']    = $done;
                $state['failures'] = $failures;
                update_option(self::OPTION_STATE, $state, false);
                /**
                 * Fires when a Pro migration step fails, so the real database
                 * error is captured (telemetry/logging) instead of being
                 * silently swallowed the way it was before.
                 *
                 * @param string $name  Step key that failed.
                 * @param string $error The DB/exception message.
                 */
                do_action('prli_migration_step_failed', $name, $error);
                continue;
            }

            // Persist step progress as we go (so a fatal mid-run resumes) and
            // clear any stale failure this step had — so the option never shows
            // an error for a step that just passed. The target version is
            // stamped only at settlement once every step succeeded.
            $done[$name] = true;
            unset($failures[$name]);
            $state['steps']    = $done;
            $state['failures'] = $failures;
            update_option(self::OPTION_STATE, $state, false);
        }

        // Settle only when every step succeeded. If any are still pending,
        // keep pending=true (and don't stamp the target version) so maybeRun()
        // re-enters and retries them; record the failure reasons so support can
        // see WHY a site is stuck instead of it failing invisibly.
        $allDone           = empty(array_diff(array_keys($steps), array_keys($done)));
        $state['steps']    = $done;
        $state['pending']  = !$allDone;
        $state['failures'] = $failures;
        if ($allDone) {
            $state['version'] = $this->version;
        }
        update_option(self::OPTION_STATE, $state, false);

        if ($allDone) {
            delete_transient(self::RETRY_BACKOFF_KEY);
        } else {
            set_transient(self::RETRY_BACKOFF_KEY, time(), self::RETRY_BACKOFF);
        }
    }

    /**
     * Create the keyword and post-keyword tables.
     *
     * @return void
     */
    private function installKeywordTables(): void
    {
        $charset = $this->db->get_charset_collate();

        dbDelta("CREATE TABLE {$this->db->prefix}prli_keywords (
            id INT(11) NOT NULL AUTO_INCREMENT,
            text VARCHAR(255) NOT NULL,
            link_id INT(11) NOT NULL,
            created_at DATETIME NOT NULL,
            replace_limit INT UNSIGNED NOT NULL DEFAULT 0,
            PRIMARY KEY  (id),
            KEY link_id (link_id),
            KEY text (text(191)),
            KEY created_at (created_at)
        ) {$charset};");

        dbDelta("CREATE TABLE {$this->db->prefix}prli_post_keywords (
            id INT(11) NOT NULL AUTO_INCREMENT,
            keyword_id INT(11) NOT NULL,
            post_id BIGINT(20) UNSIGNED NOT NULL,
            PRIMARY KEY  (id),
            KEY keyword_id (keyword_id),
            KEY post_id (post_id),
            UNIQUE KEY post_keyword_index (keyword_id,post_id)
        ) {$charset};");
    }

    /**
     * Append-only log of keyword-add events.
     *
     * A post's `_plp_post_keywords_updated_at`
     * stamp is compared against `occurred_at` to decide which entries need to be
     * replayed against that post's content on next render — incremental catch-up
     * instead of wiping the entire index on every keyword change.
     *
     * v4-only table. v3 ignores it on downgrade; v4 rebuilds semantics on upgrade.
     */
    private function installKeywordAddLogTable(): void
    {
        $charset = $this->db->get_charset_collate();

        dbDelta("CREATE TABLE {$this->db->prefix}prli_keyword_adds (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            occurred_at DATETIME NOT NULL,
            keyword_id INT(11) NOT NULL,
            keyword_text VARCHAR(255) NOT NULL,
            PRIMARY KEY  (id),
            KEY occurred_at (occurred_at),
            KEY keyword_id (keyword_id)
        ) {$charset};");
    }

    /**
     * Create the link-rotation and clicks-rotation tables.
     *
     * @return void
     */
    private function installRotationTables(): void
    {
        $charset = $this->db->get_charset_collate();

        dbDelta("CREATE TABLE {$this->db->prefix}prli_link_rotations (
            id INT(11) NOT NULL AUTO_INCREMENT,
            url VARCHAR(255) DEFAULT NULL,
            weight INT(11) NOT NULL DEFAULT 0,
            r_index INT(11) NOT NULL DEFAULT 0,
            link_id INT(11) NOT NULL,
            created_at DATETIME NOT NULL,
            position INT UNSIGNED NOT NULL DEFAULT 0,
            PRIMARY KEY  (id),
            KEY link_id (link_id),
            KEY weight (weight),
            KEY r_index (r_index)
        ) {$charset};");

        dbDelta("CREATE TABLE {$this->db->prefix}prli_clicks_rotations (
            id INT(11) NOT NULL AUTO_INCREMENT,
            click_id INT(11) NOT NULL,
            link_id INT(11) NOT NULL,
            url TEXT NOT NULL,
            rotation_id INT(11) DEFAULT NULL,
            PRIMARY KEY  (id),
            KEY click_id (click_id),
            KEY link_id (link_id),
            KEY rotation_id (rotation_id)
        ) {$charset};");
    }

    /**
     * Create the category and tag taxonomy tables.
     *
     * @return void
     */
    private function installTaxonomyTables(): void
    {
        $charset = $this->db->get_charset_collate();

        dbDelta("CREATE TABLE {$this->db->prefix}prli_categories (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            name VARCHAR(191) NOT NULL,
            slug VARCHAR(191) NOT NULL,
            description TEXT NULL,
            parent_id BIGINT UNSIGNED NULL DEFAULT NULL,
            count INT UNSIGNED NOT NULL DEFAULT 0,
            PRIMARY KEY  (id),
            UNIQUE KEY slug (slug),
            KEY parent_id (parent_id)
        ) {$charset};");

        dbDelta("CREATE TABLE {$this->db->prefix}prli_tags (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            name VARCHAR(191) NOT NULL,
            slug VARCHAR(191) NOT NULL,
            count INT UNSIGNED NOT NULL DEFAULT 0,
            PRIMARY KEY  (id),
            UNIQUE KEY slug (slug)
        ) {$charset};");
    }

    /**
     * Create the reports and report-links tables.
     *
     * V3 prli_reports/prli_report_links were Pro-owned (PlpDbController).
     * v4 keeps that ownership — Lite has no reader for either table. The
     * v3 column shape is preserved (dbDelta is a no-op on upgrade); 4.0
     * additions are purely additive: description/options/updated_at on
     * prli_reports, and a role column on prli_report_links.
     */
    private function installReportsTables(): void
    {
        $charset = $this->db->get_charset_collate();

        dbDelta("CREATE TABLE {$this->db->prefix}prli_reports (
            id INT(11) NOT NULL AUTO_INCREMENT,
            name VARCHAR(255) NOT NULL,
            goal_link_id INT(11) DEFAULT NULL,
            created_at DATETIME NOT NULL,
            description TEXT NULL,
            options LONGTEXT NULL,
            updated_at DATETIME NULL DEFAULT NULL,
            PRIMARY KEY  (id),
            KEY goal_link_id (goal_link_id),
            KEY name (name(191))
        ) {$charset};");

        dbDelta("CREATE TABLE {$this->db->prefix}prli_report_links (
            id INT(11) NOT NULL AUTO_INCREMENT,
            report_id INT(11) NOT NULL,
            link_id INT(11) NOT NULL,
            created_at DATETIME NOT NULL,
            role VARCHAR(32) NOT NULL DEFAULT 'link',
            PRIMARY KEY  (id),
            KEY report_id (report_id),
            KEY link_id (link_id)
        ) {$charset};");
    }

    /**
     * Per-post URL replacement cache. Populated lazily the first time a
     * post runs through the URL replacer, then read on every subsequent
     * visit so the filter doesn't re-scan `prli_links` for each page load.
     *
     * Rows with `is_replaced = 1` are active replacements (link_id points
     * at the owning pretty link). Rows with `is_replaced = 0` record hrefs
     * that were found in the post but had no pretty-link match at scan
     * time — they exist so surgical invalidation by URL can find the post
     * even when no replacement is active for that URL yet.
     *
     * PRIMARY KEY uses a 191-char url prefix to fit the utf8mb4 index
     * limit; the full URL is still stored in the column.
     */
    private function installPostUrlCacheTable(): void
    {
        $charset = $this->db->get_charset_collate();

        dbDelta("CREATE TABLE {$this->db->prefix}prli_post_url_cache (
            post_id BIGINT UNSIGNED NOT NULL,
            url VARCHAR(2048) NOT NULL,
            link_id BIGINT UNSIGNED NULL DEFAULT NULL,
            is_replaced TINYINT(1) NOT NULL DEFAULT 0,
            PRIMARY KEY  (post_id, url(191)),
            KEY link_id (link_id),
            KEY is_replaced (is_replaced),
            KEY url (url(191))
        ) {$charset};");
    }

    /**
     * Wrap a slug column/expression so a cross-table slug comparison ignores
     * whatever charset and collation each side happens to use.
     *
     * The v4 tables (prli_categories/prli_tags, created with the DB's current
     * default collation) are joined by slug to WP core's `terms` and to v3's
     * `prli_groups` — tables we don't own, whose charset/collation vary per
     * site. A plain `=` raises "Illegal mix of collations" (errno 1267) when
     * two utf8mb4 collations differ, and simply forcing a collation raises
     * errno 1253 on a legacy utf8mb3 column. CONVERT() normalises the charset
     * first, then COLLATE pins one comparison rule, so the join is correct
     * regardless of how either side is stored. This is a one-time migration,
     * so losing the slug index to the wrapped expression is immaterial.
     *
     * @param string $expr A slug column or SQL expression.
     *
     * @return string
     */
    private function collateSlug(string $expr): string
    {
        return "CONVERT({$expr} USING utf8mb4) COLLATE " . self::SLUG_COLLATION;
    }

    /**
     * Run a write query and throw if the database reports an error.
     *
     * `wpdb::query()` returns false and populates `last_error` on failure but
     * never throws, so a broken INSERT…SELECT (e.g. the collation abort behind
     * #743) would otherwise pass silently and let the step be marked done. The
     * throw surfaces the failure to {@see self::run()}, which keeps the step
     * pending for retry instead of recording a false success.
     *
     * @param string $sql SQL to execute.
     *
     * @return void
     * @throws \RuntimeException When the query leaves a SQL error.
     */
    private function execOrThrow(string $sql): void
    {
        $this->db->last_error = '';
        $this->db->query($sql);
        if ($this->db->last_error !== '') {
            // Raw DB error: stored in `failures` and escaped at the display
            // boundary, so don't esc_html() here (would double-encode it).
            // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped
            throw new \RuntimeException($this->db->last_error);
        }
    }

    /**
     * Migrate v3 pretty-link-category terms into the v4 categories table.
     *
     * @return void
     */
    private function migrateCategoryTaxonomy(): void
    {
        $linksTable = $this->db->prefix . 'prli_links';
        if (!$this->hasLegacyCptData($linksTable)) {
            return;
        }

        $catsTable      = $this->db->prefix . 'prli_categories';
        $linkTermsTable = $this->db->prefix . 'prli_link_terms';
        $terms          = $this->db->prefix . 'terms';
        $termTaxonomy   = $this->db->prefix . 'term_taxonomy';
        $termRel        = $this->db->prefix . 'term_relationships';

        // Insert the flat term set first. parent_id resolves by slug in a second
        // pass below because the child may be inserted before its parent when
        // the term-taxonomy table is scanned in term_id order.
        $this->execOrThrow(
            "INSERT IGNORE INTO {$catsTable} (name, slug, description, parent_id, count)
             SELECT t.name, t.slug, COALESCE(tt.description, ''), 0, 0
             FROM {$terms} t
             INNER JOIN {$termTaxonomy} tt ON tt.term_id = t.term_id
             WHERE tt.taxonomy = 'pretty-link-category'"
        );

        // Resolve hierarchy: for each v3 child, look up its parent term's slug
        // and map that slug back to a v4 category id. Join chain:
        // child.term_id -> tt_child.parent (WP parent term_id)
        // -> terms_parent.slug -> v4 categories.id.
        $this->execOrThrow(
            "UPDATE {$catsTable} c
             INNER JOIN {$terms} child_term ON {$this->collateSlug('child_term.slug')} = {$this->collateSlug('c.slug')}
             INNER JOIN {$termTaxonomy} tt_child
                 ON tt_child.term_id = child_term.term_id
                AND tt_child.taxonomy = 'pretty-link-category'
             INNER JOIN {$terms} parent_term ON parent_term.term_id = tt_child.parent
             INNER JOIN {$catsTable} parent_cat
                 ON {$this->collateSlug('parent_cat.slug')} = {$this->collateSlug('parent_term.slug')}
             SET c.parent_id = parent_cat.id
             WHERE tt_child.parent > 0 AND c.parent_id = 0"
        );

        $this->execOrThrow(
            "INSERT IGNORE INTO {$linkTermsTable} (link_id, term_id, taxonomy)
             SELECT l.id, c.id, 'category'
             FROM {$linksTable} l
             INNER JOIN {$termRel} tr ON tr.object_id = l.link_cpt_id
             INNER JOIN {$termTaxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
             INNER JOIN {$terms} wt ON wt.term_id = tt.term_id
             INNER JOIN {$catsTable} c ON {$this->collateSlug('c.slug')} = {$this->collateSlug('wt.slug')}
             WHERE l.link_cpt_id IS NOT NULL AND l.link_cpt_id > 0
               AND tt.taxonomy = 'pretty-link-category'"
        );
    }

    /**
     * Migrate v3 pretty-link-tag terms into the v4 tags table.
     *
     * @return void
     */
    private function migrateTagTaxonomy(): void
    {
        $linksTable = $this->db->prefix . 'prli_links';
        if (!$this->hasLegacyCptData($linksTable)) {
            return;
        }

        $tagsTable      = $this->db->prefix . 'prli_tags';
        $linkTermsTable = $this->db->prefix . 'prli_link_terms';
        $terms          = $this->db->prefix . 'terms';
        $termTaxonomy   = $this->db->prefix . 'term_taxonomy';
        $termRel        = $this->db->prefix . 'term_relationships';

        $this->execOrThrow(
            "INSERT IGNORE INTO {$tagsTable} (name, slug, count)
             SELECT t.name, t.slug, 0
             FROM {$terms} t
             INNER JOIN {$termTaxonomy} tt ON tt.term_id = t.term_id
             WHERE tt.taxonomy = 'pretty-link-tag'"
        );

        $this->execOrThrow(
            "INSERT IGNORE INTO {$linkTermsTable} (link_id, term_id, taxonomy)
             SELECT l.id, t.id, 'tag'
             FROM {$linksTable} l
             INNER JOIN {$termRel} tr ON tr.object_id = l.link_cpt_id
             INNER JOIN {$termTaxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
             INNER JOIN {$terms} wt ON wt.term_id = tt.term_id
             INNER JOIN {$tagsTable} t ON {$this->collateSlug('t.slug')} = {$this->collateSlug('wt.slug')}
             WHERE l.link_cpt_id IS NOT NULL AND l.link_cpt_id > 0
               AND tt.taxonomy = 'pretty-link-tag'"
        );
    }

    /**
     * Apply all three link→term assignment sources into `prli_link_terms`:
     * category taxonomy, tag taxonomy, and link groups. Internal helper for
     * {@see self::syncLinkTerms()} (was its own step key in earlier builds).
     *
     * On the reported sites (#743) the original migration marked itself
     * complete without populating prli_link_terms: the pivot join compared v4
     * slug columns against WP core's terms.slug (and v3's prli_groups) under a
     * mismatched collation, so MySQL aborted the INSERT…SELECT and every link's
     * assignment silently vanished. Terms migrated; links were left orphaned.
     * The collation-safe {@see self::collateSlug()} wrapper applied to those
     * joins is what makes re-running them actually populate the pivot.
     *
     * Every underlying migration is idempotent (INSERT IGNORE against the
     * prli_link_terms primary key) and self-guards when its source is absent,
     * so re-running is a harmless no-op on already-migrated sites.
     *
     * @return void
     */
    private function repairAllAssignments(): void
    {
        $this->migrateCategoryTaxonomy();
        $this->migrateTagTaxonomy();
        $this->migrateGroups();
    }

    /**
     * The single, self-healing link→category/tag assignment migration.
     *
     * Replaces the old migrate_categories / migrate_tags / migrate_groups /
     * repair_all_assignments / backfill_link_terms chain. Each of those needed
     * a brand-new step key whenever the migration had to be re-triggered,
     * because a completed step never re-runs.
     *
     * This is a single step that runs once (a fresh key, so every
     * already-migrated install gets one more healing pass), retries on failure
     * instead of silently sticking, and can be re-armed on demand via
     * {@see self::requeueStep()}. It does NOT re-run on every update. Safe to
     * re-run whenever triggered because {@see self::repairAllAssignments()} is
     * entirely INSERT IGNORE + guarded UPDATEs — re-running never duplicates,
     * it only fills what's missing. The final verification counts assignments
     * whose v3 source still exists (CPT term relationships via link_cpt_id, or
     * a group) but that never landed in `prli_link_terms`, and throws while any
     * remain — so {@see self::run()} keeps the step pending and retries rather
     * than settling with an empty pivot. It can neither silently stick nor
     * falsely report success.
     *
     * Limitation: a link whose only assignment source is gone (link_cpt_id
     * cleared AND its group discarded) can't be rebuilt from PL tables — those
     * aren't counted as recoverable, so this doesn't loop forever on them.
     *
     * @return void
     * @throws \RuntimeException When recoverable assignments remain unmigrated.
     */
    private function syncLinkTerms(): void
    {
        $this->repairAllAssignments();

        $remaining = $this->recoverableAssignmentOrphans();
        if ($remaining > 0) {
            // Raw message: run() stores it in `failures` and the display layer
            // (notice / Site Health) escapes at output — escaping here too
            // would double-encode the operator-facing error.
            $message = "link-term sync incomplete: {$remaining} recoverable assignment(s) still unmigrated";
            // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped
            throw new \RuntimeException($message);
        }
    }

    /**
     * Count link→term assignments that CAN be reconstructed from surviving v3
     * sources but are not yet in `prli_link_terms`.
     *
     * "Recoverable" is the key qualifier: a link whose source is genuinely gone
     * (no `link_cpt_id` term relationship and no group) is not counted, so a
     * site with unrecoverable data doesn't loop forever — only assignments we
     * still have the source for and failed to migrate register here.
     *
     * @return integer
     * @throws \RuntimeException When the verification query itself errors.
     */
    private function recoverableAssignmentOrphans(): int
    {
        $linksTable   = $this->db->prefix . 'prli_links';
        $hasLegacyCpt = $this->hasLegacyCptData($linksTable);
        if (!$hasLegacyCpt && !$this->tableExists($this->db->prefix . 'prli_groups')) {
            return 0;
        }

        $linkTermsTable = $this->db->prefix . 'prli_link_terms';
        $catsTable      = $this->db->prefix . 'prli_categories';
        $tagsTable      = $this->db->prefix . 'prli_tags';
        $groupsTable    = $this->db->prefix . 'prli_groups';
        $terms          = $this->db->prefix . 'terms';
        $termTaxonomy   = $this->db->prefix . 'term_taxonomy';
        $termRel        = $this->db->prefix . 'term_relationships';
        $slugExpr       = "LOWER(REPLACE(REPLACE(REPLACE(g.name, ' ', '-'), '/', '-'), '_', '-'))";

        // Every (link, term, taxonomy) pair the three migrations should have
        // produced, drawn only from sources that still exist. A LEFT JOIN to
        // the pivot then keeps just the ones that never landed.
        $sources = [];

        // Category + tag via the surviving CPT term relationships.
        if ($hasLegacyCpt) {
            foreach ([['pretty-link-category', $catsTable, 'category'], ['pretty-link-tag', $tagsTable, 'tag']] as [$taxonomy, $destTable, $destTax]) {
                $sources[] = "SELECT l.id AS link_id, d.id AS term_id, '{$destTax}' AS taxonomy
                     FROM {$linksTable} l
                     INNER JOIN {$termRel} tr ON tr.object_id = l.link_cpt_id
                     INNER JOIN {$termTaxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
                     INNER JOIN {$terms} wt ON wt.term_id = tt.term_id
                     INNER JOIN {$destTable} d ON {$this->collateSlug('d.slug')} = {$this->collateSlug('wt.slug')}
                     WHERE l.link_cpt_id IS NOT NULL AND l.link_cpt_id > 0
                       AND tt.taxonomy = '{$taxonomy}'";
            }
        }

        // Category via the surviving group assignment.
        if ($this->tableExists($groupsTable)) {
            $sources[] = "SELECT l.id AS link_id, c.id AS term_id, 'category' AS taxonomy
                 FROM {$linksTable} l
                 INNER JOIN {$groupsTable} g ON g.id = l.group_id
                 INNER JOIN {$catsTable} c ON {$this->collateSlug('c.slug')} = {$this->collateSlug("($slugExpr)")}
                 WHERE l.group_id IS NOT NULL AND l.group_id > 0";
        }

        if ($sources === []) {
            return 0;
        }

        $union = implode(' UNION ', $sources);
        $this->db->last_error = '';
        $count = (int) $this->db->get_var(
            "SELECT COUNT(*) FROM ({$union}) src
             LEFT JOIN {$linkTermsTable} lt
                    ON lt.link_id = src.link_id
                   AND lt.term_id = src.term_id
                   AND lt.taxonomy = src.taxonomy
             WHERE lt.link_id IS NULL"
        );
        if ($this->db->last_error !== '') {
            // Raw DB error — escaped at the display boundary, not here.
            // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped
            throw new \RuntimeException($this->db->last_error);
        }
        return $count;
    }

    /**
     * Migrate v3 link groups into the v4 categories table.
     *
     * @return void
     */
    private function migrateGroups(): void
    {
        $groupsTable = $this->db->prefix . 'prli_groups';
        if (!$this->tableExists($groupsTable)) {
            return;
        }

        $linksTable     = $this->db->prefix . 'prli_links';
        $catsTable      = $this->db->prefix . 'prli_categories';
        $linkTermsTable = $this->db->prefix . 'prli_link_terms';

        // Slug is the uniqueness key (UNIQUE INDEX on prli_categories.slug),
        // so dedup and the pivot join both match by slug. Two v3 records that
        // slugify the same (e.g. a WP category "Holiday Sales" and a group
        // "Holiday-Sales") collapse into one v4 category; the pre-existing
        // row keeps its name.
        //
        // $slugExpr is a pure SQL expression built from literal strings only —
        // no user input reaches it. wpdb::prepare() cannot parameterize a
        // computed expression used in both SELECT and WHERE, so raw
        // interpolation is intentional and safe here.
        $slugExpr = "LOWER(REPLACE(REPLACE(REPLACE(g.name, ' ', '-'), '/', '-'), '_', '-'))";

        $this->execOrThrow(
            "INSERT IGNORE INTO {$catsTable} (name, slug, description, parent_id, count)
             SELECT g.name, {$slugExpr}, COALESCE(g.description, ''), 0, 0
             FROM {$groupsTable} g
             WHERE g.name IS NOT NULL AND g.name <> ''
               AND NOT EXISTS (
                 SELECT 1 FROM {$catsTable} c WHERE {$this->collateSlug('c.slug')} = {$this->collateSlug("($slugExpr)")}
               )"
        );

        $this->execOrThrow(
            "INSERT IGNORE INTO {$linkTermsTable} (link_id, term_id, taxonomy)
             SELECT l.id, c.id, 'category'
             FROM {$linksTable} l
             INNER JOIN {$groupsTable} g ON g.id = l.group_id
             INNER JOIN {$catsTable} c ON {$this->collateSlug('c.slug')} = {$this->collateSlug("($slugExpr)")}
             WHERE l.group_id IS NOT NULL AND l.group_id > 0"
        );
    }

    /**
     * Backfill `rotation_id` on pre-4.0 click-rotation rows. Single bulk
     * UPDATE…JOIN — runs in MySQL, no PHP iteration. Idempotent.
     */
    private function migrateClicksRotations(): void
    {
        $clicksRot = $this->db->prefix . 'prli_clicks_rotations';
        $linksRot  = $this->db->prefix . 'prli_link_rotations';

        if (!$this->columnExists($clicksRot, 'url') || !$this->columnExists($clicksRot, 'rotation_id')) {
            return;
        }

        $this->db->query(
            "UPDATE {$clicksRot} cr
             INNER JOIN {$linksRot} lr ON lr.link_id = cr.link_id AND lr.url = cr.url
             SET cr.rotation_id = lr.id
             WHERE cr.rotation_id IS NULL"
        );
    }

    /**
     * Bridge v3's flat auto-create / social-button keys into v4's unified
     * `autocreate[type]` map plus the new `autocreate_enable` /
     * `social_buttons_enabled` master toggles.
     *
     * Pretty Link 3.x stored flat `posts_auto` / `pages_auto` /
     * `posts_category` / `pages_category` / `social_posts_buttons` /
     * `social_pages_buttons`; v4 reads only `autocreate['post'|'page']` rows.
     * Custom-CPT rows already
     * sit in the map under their own keys (v3 stored those in map form) and
     * are preserved here — existing post/page rows are never clobbered.
     *
     * Master toggles are only ever raised to true; an explicit existing value
     * is respected (we never force false).
     */
    private function migrateAutocreate(): void
    {
        $options = get_option('prlipro_options');
        if (!is_array($options)) {
            return;
        }

        $autocreate = isset($options['autocreate']) && is_array($options['autocreate'])
            ? $options['autocreate']
            : [];

        $map = [
            'post' => ['posts_auto', 'posts_category', 'social_posts_buttons'],
            'page' => ['pages_auto', 'pages_category', 'social_pages_buttons'],
        ];

        foreach ($map as $type => $keys) {
            // Preserve an existing row for this type — don't clobber.
            if (isset($autocreate[$type])) {
                continue;
            }

            list($autoKey, $catKey, $socKey) = $keys;
            // V3 stored auto-create and social-buttons as INDEPENDENT flags, so
            // migrate the row when EITHER is set. Skipping on auto-create alone
            // would silently drop a "share buttons on, auto-create off" config.
            if (empty($options[$autoKey]) && empty($options[$socKey])) {
                continue;
            }

            $autocreate[$type] = [
                'enabled'  => !empty($options[$autoKey]),
                'category' => (int) ($options[$catKey] ?? 0),
                'socbtns'  => !empty($options[$socKey]),
            ];
        }

        $options['autocreate'] = $autocreate;

        // Master toggles: raise to true if anything is on (flat v3 keys or any
        // existing row). Never force false — respect an explicit existing value.
        $anyAutocreate = !empty($options['posts_auto']) || !empty($options['pages_auto']);
        $anySocial     = !empty($options['social_posts_buttons']) || !empty($options['social_pages_buttons']);

        foreach ($autocreate as $row) {
            if (is_array($row) && !empty($row['enabled'])) {
                $anyAutocreate = true;
            }
            if (is_array($row) && !empty($row['socbtns'])) {
                $anySocial = true;
            }
        }

        if ($anyAutocreate) {
            $options['autocreate_enable'] = true;
        }
        if ($anySocial) {
            $options['social_buttons_enabled'] = true;
        }

        update_option('prlipro_options', $options);
    }

    /**
     * `num_slug_chars` moved from the Pro blob (v3) to the Lite blob (v4).
     * Copy it across only when the Pro blob has it and the Lite blob does
     * not — re-running can't overwrite a value the Lite blob already holds.
     */
    private function migrateNumSlugChars(): void
    {
        $proOptions = get_option('prlipro_options');
        if (!is_array($proOptions) || !isset($proOptions['num_slug_chars'])) {
            return;
        }

        $liteOptions = get_option('prli_options');
        if (!is_array($liteOptions)) {
            $liteOptions = [];
        }
        if (isset($liteOptions['num_slug_chars'])) {
            return;
        }

        $liteOptions['num_slug_chars'] = $proOptions['num_slug_chars'];
        update_option('prli_options', $liteOptions);
    }

    /**
     * Pretty Link 3.x stored `public_link_display_page` as a URL string; v4
     * reads it as an `(int)` page ID. Resolve the stored URL to a page ID
     * WITHOUT depending on rewrite rules — this runs during `prli_loaded`
     * (on `after_setup_theme`), before rewrite rules are registered on
     * `init`, so `url_to_postid()` is unreliable. Read `?page_id=`/`?p=` for
     * plain permalinks, else match the path with `get_page_by_path()` (a
     * direct DB lookup). Unresolvable (deleted page / external URL) becomes
     * `0` (v4's "not set" default). Already-numeric values are left untouched.
     */
    private function migratePublicLinkDisplayPage(): void
    {
        $options = get_option('prlipro_options');
        if (!is_array($options) || !isset($options['public_link_display_page'])) {
            return;
        }

        $value = $options['public_link_display_page'];
        if (!is_string($value) || $value === '' || is_numeric($value)) {
            return;
        }

        $pageId = 0;
        $parts  = wp_parse_url($value);
        if (is_array($parts)) {
            // Plain permalinks: ?page_id=NN or ?p=NN.
            if (isset($parts['query'])) {
                wp_parse_str($parts['query'], $query);
                if (isset($query['page_id'])) {
                    $pageId = (int) $query['page_id'];
                } elseif (isset($query['p'])) {
                    $pageId = (int) $query['p'];
                }
            }

            // Pretty permalinks: resolve the page by its slug path. Strip the
            // site's home path first so subdirectory installs resolve too.
            if ($pageId === 0 && isset($parts['path'])) {
                $path     = (string) $parts['path'];
                $homePath = (string) wp_parse_url(home_url(), PHP_URL_PATH);
                if ($homePath !== '' && strpos($path, $homePath) === 0) {
                    $path = substr($path, strlen($homePath));
                }
                $path = trim($path, '/');
                if ($path !== '') {
                    $page = get_page_by_path($path);
                    if ($page instanceof \WP_Post) {
                        $pageId = (int) $page->ID;
                    }
                }
            }
        }

        $options['public_link_display_page'] = $pageId;
        update_option('prlipro_options', $options);
    }

    /**
     * Normalise stored social buttons to the canonical v4 shape.
     *
     * V3 → v4 social-button reshaping — the single point that converts any
     * legacy `social_buttons` blob to the canonical v4 shape:
     *   - `twitter` slug → `x` (Service::defaultButtons rebrand)
     *   - slug-keyed assoc rows → indexed `{ slug, enabled }` list
     *   - v3 `checked` flag → v4 `enabled` (admin UI reads only `enabled`)
     *
     * Placement is NOT migrated: v4 reuses v3's `top-and-bottom` value verbatim.
     * The admin (REST read) and front-end (Service::resolveButtons) both assume
     * the canonical shape — there is no read-time/runtime translation; this
     * step is the only normaliser, so the step key is intentionally distinct
     * from the original twitter-only step so it re-runs and fully normalises
     * sites migrated under the old shape.
     */
    private function normaliseSocialButtons(): void
    {
        $options = get_option('prlipro_options');
        if (!is_array($options) || !isset($options['social_buttons']) || !is_array($options['social_buttons'])) {
            return;
        }

        $buttons = $options['social_buttons'];
        if ($buttons === []) {
            return;
        }

        if (!$this->socialButtonsNeedMigration($buttons)) {
            return;
        }

        $options['social_buttons'] = \PrettyLinks\Pro\SocialButtons\Service::normaliseStoredButtons($buttons);
        update_option('prlipro_options', $options);
    }

    /**
     * Determine whether a stored social-buttons blob still needs migration.
     *
     * @param array<int|string, mixed> $buttons Stored social-buttons blob.
     *
     * @return boolean
     */
    private function socialButtonsNeedMigration(array $buttons): bool
    {
        $isAssoc = array_keys($buttons) !== range(0, count($buttons) - 1);
        if ($isAssoc) {
            return true;
        }

        foreach ($buttons as $row) {
            if (!is_array($row)) {
                continue;
            }
            // Any lingering v3 `checked` key means the row isn't clean v4 yet —
            // including the case where a prior partial migration left BOTH
            // `checked` and `enabled` on the row.
            if (array_key_exists('checked', $row)) {
                return true;
            }
            if (($row['slug'] ?? '') === 'twitter') {
                return true;
            }
        }

        return false;
    }

    /**
     * Determine whether the links table holds any legacy CPT references.
     *
     * @param string $linksTable Fully-qualified prli_links table name.
     *
     * @return boolean
     */
    private function hasLegacyCptData(string $linksTable): bool
    {
        if (!$this->columnExists($linksTable, 'link_cpt_id')) {
            return false;
        }
        return (bool) $this->db->get_var(
            "SELECT 1 FROM {$linksTable} WHERE link_cpt_id IS NOT NULL AND link_cpt_id > 0 LIMIT 1"
        );
    }

    /**
     * Determine whether a database table exists.
     *
     * @param string $table Fully-qualified table name.
     *
     * @return boolean
     */
    private function tableExists(string $table): bool
    {
        return (bool) $this->db->get_var(
            $this->db->prepare('SHOW TABLES LIKE %s', $table)
        );
    }

    /**
     * Determine whether a column exists on a database table.
     *
     * @param string $table  Fully-qualified table name.
     * @param string $column Column name to check for.
     *
     * @return boolean
     */
    private function columnExists(string $table, string $column): bool
    {
        return (bool) $this->db->get_var(
            $this->db->prepare("SHOW COLUMNS FROM {$table} LIKE %s", $column)
        );
    }

    /**
     * Unschedule cron events left behind by v3 Pro whose custom schedules
     * v4 Pro no longer registers — WP-Cron logs `Cron reschedule event
     * error / invalid_schedule` for each one on every cron tick otherwise.
     *
     * - `prli_broken_link_check` — v3 Pro broken-link scanner. v4 Pro
     *   uses `prli_link_health_check` (Health\Scheduler) instead.
     * - `prli_send_broken_link_emails` — v3 Pro digest email. v4 Pro
     *   reuses the same hook name in Health\EmailDigest but with a
     *   different (configurable) recurrence; the v3 entry references
     *   the now-unregistered `prli_weekly` schedule, so it has to be
     *   cleared. EmailDigest::start() reschedules on next Pro load.
     * - `plp_post_build_keyword_index{0,1}` /
     *   `plp_post_build_url_index{0,1}` — v3 Pro index builders. v4 Pro
     *   removed the feature; no v4 consumer.
     */
    private function clearLegacyCronHooks(): void
    {
        $legacy = [
            'prli_broken_link_check',
            'prli_send_broken_link_emails',
            'plp_post_build_keyword_index0',
            'plp_post_build_keyword_index1',
            'plp_post_build_url_index0',
            'plp_post_build_url_index1',
        ];
        foreach ($legacy as $hook) {
            wp_clear_scheduled_hook($hook);
        }
    }
}
