<?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. Runs on `plugins_loaded` at
 * priority 6 so the free Migrator (priority 5) finishes its core tables
 * first. 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;

    /**
     * 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);
        if (($state['version'] ?? '') === $this->version && empty($state['pending']) && empty($pending)) {
            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);
        }
    }

    /**
     * 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'],
            'install_rotation_tables'          => [$this, 'installRotationTables'],
            'install_taxonomy_tables'          => [$this, 'installTaxonomyTables'],
            'install_reports_tables'           => [$this, 'installReportsTables'],
            'install_post_url_cache'           => [$this, 'installPostUrlCacheTable'],
            'migrate_categories'               => [$this, 'migrateCategoryTaxonomy'],
            'migrate_tags'                     => [$this, 'migrateTagTaxonomy'],
            'migrate_groups'                   => [$this, 'migrateGroups'],
            'repair_all_assignments'           => [$this, 'repairAllAssignments'],
            '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'] : [];

        foreach ($steps as $name => $callable) {
            if (!empty($done[$name])) {
                continue;
            }
            $callable();
            $done[$name]      = true;
            $state['steps']   = $done;
            $state['version'] = $this->version;
            update_option(self::OPTION_STATE, $state, false);
        }

        $state['pending'] = false;
        $state['version'] = $this->version;
        update_option(self::OPTION_STATE, $state, false);
    }

    /**
     * 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))
        ) {$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;
    }

    /**
     * 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->db->query(
            "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->db->query(
            "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->db->query(
            "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->db->query(
            "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->db->query(
            "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'"
        );
    }

    /**
     * Repair missing v3->v4 link→category/tag assignments after an upgrade.
     *
     * On the reported sites (#743) the original migrate_* steps marked
     * themselves 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 now applied to those joins is what makes
     * re-running them actually populate the pivot.
     *
     * Re-runs all three sources that write into prli_link_terms — category
     * taxonomy, tag taxonomy, and link groups — so whichever source a site
     * has repopulates. Every underlying migration is idempotent (INSERT
     * IGNORE against the prli_link_terms primary key) and self-guards when its
     * source is absent, so this is a harmless no-op on healthy sites.
     *
     * Runs under its own new step key, so sites already migrated on an earlier
     * build (where prli_link_terms was left empty) pick it up on their next
     * upgrade: maybeRun() sees the key missing from prlipro_migration_state
     * and runs it exactly once.
     *
     * Limitation: a link whose only surviving assignment source has been
     * discarded (link_cpt_id cleared AND prli_groups dropped AND group_id
     * cleared) cannot be reconstructed from PL tables and needs a manual
     * reassignment or a one-off SQL restore.
     *
     * @return void
     */
    private function repairAllAssignments(): void
    {
        $this->migrateCategoryTaxonomy();
        $this->migrateTagTaxonomy();
        $this->migrateGroups();
    }

    /**
     * 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->db->query(
            "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->db->query(
            "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 on `plugins_loaded`,
     * before CPTs and rewrite rules are registered, 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);
        }
    }
}
