<?php
if ( ! defined( 'ABSPATH' ) ) exit;


/**
 * FLINK_Link_Stats — Real-time link statistics from HTML content.
 *
 * Single source of truth: the actual post_content HTML.
 * Links are identified by their data attributes:
 *   data-il="1"   → rule-based links (linker)
 *   data-il-s="1" → semantic links
 *
 * The cache table stores per-post counts + detailed link data (JSON).
 * It is updated automatically whenever the linker or semantic engine
 * modifies a post, guaranteeing 100% accuracy.
 */
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter
// -- All $wpdb operations in this file target custom plugin tables
// (e.g. $wpdb->prefix . 'il_pilares') whose names and column lists
// are constants under plugin control. Generic WordPress checks
// for direct DB queries, caching and prepared-SQL interpolation
// don't apply meaningfully here. See docs/LICENSING.md for the
// full architecture rationale.

class FLINK_Link_Stats {

    const TABLE   = 'il_link_stats';
    const VERSION = '1.0';

    /**
     * Whether the semantic-links table exists.
     *
     * The semantic engine is a premium module, so its table
     * (il_semantic_links) is absent in the free edition. Several stats
     * queries below join or sub-query that table to count semantic links;
     * running those against a missing table makes the whole query fail
     * (and the link map shows no rows). This helper lets each query fall
     * back to a literal 0 for the semantic part when the table is absent,
     * so the link map works in both editions. Result is cached per request.
     */
    private static function sem_available() {
        static $cache = null;
        if ( $cache !== null ) return $cache;
        global $wpdb;
        $t = $wpdb->prefix . 'il_semantic_links';
        $cache = (bool) $wpdb->get_var( $wpdb->prepare( "SHOW TABLES LIKE %s", $t ) );
        return $cache;
    }

    /**
     * TEMPORARY DEBUG LOG for the link map.
     * Writes to uploads/4linking-map-debug.log when option
     * 'flink_map_debug' is '1'. Used to trace why outgoing/incoming
     * semantic totals can disagree. Off by default.
     */
    private static function dbg_enabled() {
        return get_option( 'flink_map_debug', '0' ) === '1';
    }
    private static function dbg_log( $msg, $ctx = array() ) {
        if ( ! self::dbg_enabled() ) return;
        $up = wp_upload_dir();
        if ( empty( $up['basedir'] ) ) return;
        $line = '[' . gmdate( 'H:i:s' ) . '] ' . $msg;
        if ( $ctx ) $line .= ' ' . wp_json_encode( $ctx );
        @file_put_contents( trailingslashit( $up['basedir'] ) . '4linking-map-debug.log', $line . "\n", FILE_APPEND | LOCK_EX );
    }
    public static function dbg_reset() {
        $up = wp_upload_dir();
        if ( empty( $up['basedir'] ) ) return;
        @file_put_contents( trailingslashit( $up['basedir'] ) . '4linking-map-debug.log', "=== map debug · " . gmdate( 'Y-m-d H:i:s' ) . " ===\n" );
    }

    // ─────────────────────────────────────────────────────────────────
    //  INSTALL
    // ─────────────────────────────────────────────────────────────────

    public static function maybe_install() {
        global $wpdb;
        $table = $wpdb->prefix . self::TABLE;

        // Two conditions for "skip install":
        //   (1) Version option already matches the current schema version.
        //   (2) The table physically exists in the database.
        // Without check (2), an installation can get stuck if the version
        // option was set but the table got dropped externally — or, in
        // this plugin's case, if the version option was written under one
        // table name (flink_link_stats) and we later switched to another
        // (il_link_stats). We want maybe_install() to re-run in that case.
        $installed = get_option( 'flink_link_stats_version', '' );
        if ( $installed === self::VERSION ) {
            $exists = $wpdb->get_var( $wpdb->prepare(
                "SHOW TABLES LIKE %s", $table
            ) );
            if ( $exists ) return;
        }

        $charset = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE IF NOT EXISTS {$table} (
            post_id          BIGINT UNSIGNED NOT NULL,
            post_title       VARCHAR(500) NOT NULL DEFAULT '',
            post_url         VARCHAR(500) NOT NULL DEFAULT '',
            post_status      VARCHAR(20)  NOT NULL DEFAULT 'publish',
            post_date        DATETIME     NOT NULL DEFAULT '1970-01-01',
            out_rules        SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            out_semantic     SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            in_rules         SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            in_semantic      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            out_rules_data   MEDIUMTEXT DEFAULT NULL,
            out_semantic_data MEDIUMTEXT DEFAULT NULL,
            in_rules_data    MEDIUMTEXT DEFAULT NULL,
            in_semantic_data MEDIUMTEXT DEFAULT NULL,
            updated_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (post_id),
            KEY idx_total_links (out_rules, out_semantic, in_rules, in_semantic)
        ) {$charset};";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta( $sql );
        update_option( 'flink_link_stats_version', self::VERSION );
    }

    // ─────────────────────────────────────────────────────────────────
    //  UPDATE STATS FOR A SINGLE POST (called after every link change)
    // ─────────────────────────────────────────────────────────────────

    /**
     * Scan the HTML content of $post_id, extract all plugin links,
     * and update the cache table.
     *
     * Uses a single-pass approach: match ALL <a> tags containing the marker
     * attribute, then extract href from each matched tag. This avoids
     * double-counting (two regexes catching the same tag) and undercounting
     * (dedup removing legitimate duplicate anchors).
     *
     * Also updates the INCOMING stats of every post that $post_id links to.
     */
    public static function update_post( $post_id ) {
        $post_id = (int) $post_id;
        $post    = get_post( $post_id );
        if ( ! $post ) return;

        // Use the canonical scan HTML: Elementor widget HTML for builder
        // pages (NOT post_content too — that would double-count), or
        // post_content for classic pages.
        $content = self::get_scan_html( $post_id );

        // ── Extract outgoing rule links (data-il="1") ────────────────
        // Single regex: match any <a> that contains data-il="1" or data-il='1'
        $out_rules = array();
        if ( preg_match_all(
            '/<a\s([^>]*data-il=["\']1["\'][^>]*)>(.*?)<\/a>/is',
            $content, $matches, PREG_SET_ORDER
        ) ) {
            foreach ( $matches as $m ) {
                $attrs = $m[1];
                $anchor = wp_strip_all_tags( $m[2] );
                // Extract href from the attributes
                $url = '';
                if ( preg_match( '/href=["\']([^"\']+)["\']/', $attrs, $href_m ) ) {
                    $url = esc_url_raw( $href_m[1] );
                }
                if ( $url ) {
                    $out_rules[] = array( 'url' => $url, 'anchor' => $anchor );
                }
            }
        }

        // ── Outgoing SEMANTIC links: read from il_semantic_links ─────
        // Semantic links have a single source of truth: the il_semantic_links
        // table (one row per A→B link, with score). Counting them by scanning
        // HTML caused outgoing/incoming totals to disagree (e.g. 9 vs 18).
        // Reading from the table guarantees they reconcile by construction.
        $out_semantic = array();
        global $wpdb;
        $sem_rows = self::sem_available() ? $wpdb->get_results( $wpdb->prepare(
            "SELECT target_id, anchor_text, score FROM {$wpdb->prefix}il_semantic_links
             WHERE source_id = %d AND anchor_text != '__analyzed__'
               AND anchor_text IS NOT NULL AND anchor_text != ''",
            $post_id
        ) ) : array();
        foreach ( $sem_rows as $r ) {
            $tid = (int) $r->target_id;
            $out_semantic[] = array(
                'url'        => $tid ? get_permalink( $tid ) : '',
                'anchor'     => $r->anchor_text,
                'post_id'    => $tid,
                'post_title' => $tid ? get_the_title( $tid ) : '',
                'score'      => (int) $r->score,
            );
        }

        self::dbg_log( 'update_post OUT', array(
            'post_id'      => $post_id,
            'title'        => $post->post_title,
            'type'         => $post->post_type,
            'out_rules'    => count( $out_rules ),
            'out_semantic' => count( $out_semantic ),
        ) );

        // Enrich outgoing RULE links with destination post info
        foreach ( $out_rules as &$link ) {
            $dest_id = url_to_postid( $link['url'] );
            $link['post_id']    = $dest_id;
            $link['post_title'] = $dest_id ? get_the_title( $dest_id ) : '';
        }
        unset( $link );

        // ── Save to cache ────────────────────────────────────────────
        $table = $wpdb->prefix . self::TABLE;
        $wpdb->query( $wpdb->prepare(
            "INSERT INTO {$table}
                (post_id, post_title, post_url, post_status, post_date,
                 out_rules, out_semantic, out_rules_data, out_semantic_data)
             VALUES (%d, %s, %s, %s, %s, %d, %d, %s, %s)
             ON DUPLICATE KEY UPDATE
                post_title = VALUES(post_title),
                post_url = VALUES(post_url),
                post_status = VALUES(post_status),
                out_rules = VALUES(out_rules),
                out_semantic = VALUES(out_semantic),
                out_rules_data = VALUES(out_rules_data),
                out_semantic_data = VALUES(out_semantic_data),
                updated_at = NOW()",
            $post_id,
            $post->post_title,
            get_permalink( $post_id ),
            $post->post_status,
            $post->post_date,
            count( $out_rules ),
            count( $out_semantic ),
            wp_json_encode( $out_rules ),
            wp_json_encode( $out_semantic )
        ) );

        // ── Update INCOMING stats of destination posts ────────────────
        // Collect all destination post IDs
        $dest_ids = array();
        foreach ( $out_rules as $link ) {
            if ( ! empty( $link['post_id'] ) ) $dest_ids[] = (int) $link['post_id'];
        }
        foreach ( $out_semantic as $link ) {
            if ( ! empty( $link['post_id'] ) ) $dest_ids[] = (int) $link['post_id'];
        }
        $dest_ids = array_unique( $dest_ids );

        foreach ( $dest_ids as $dest_id ) {
            self::update_incoming( $dest_id );
        }
    }

    /**
     * Recalculate INCOMING links for a post by scanning all other posts' content.
     * This is the inverse lookup: "who links to me?"
     */
    public static function update_incoming( $dest_post_id ) {
        $dest_post_id = (int) $dest_post_id;
        $dest_post    = get_post( $dest_post_id );
        if ( ! $dest_post ) return;

        $dest_url       = get_permalink( $dest_post_id );
        $dest_url_trail = trailingslashit( $dest_url );

        global $wpdb;
        $table = $wpdb->prefix . self::TABLE;

        // Find candidate source posts. Rule/semantic links can live either in
        // post_content (classic) OR in _elementor_data (Elementor builder
        // pages). The old query only matched post_content, so incoming links
        // from Elementor pages were never counted (e.g. a pillar showing 15
        // incoming rule links but the map reporting 0). We therefore gather
        // candidates from BOTH sources and scan each one's full renderable
        // HTML below.
        $like_il  = '%' . $wpdb->esc_like( 'data-il' ) . '%';
        $like_u1  = '%' . $wpdb->esc_like( 'href="' . $dest_url . '"' ) . '%';
        $like_u2  = '%' . $wpdb->esc_like( 'href="' . $dest_url_trail . '"' ) . '%';

        // Classic candidates (link + href present in post_content).
        $classic = $wpdb->get_results( $wpdb->prepare(
            "SELECT ID, post_title FROM {$wpdb->posts}
             WHERE post_status IN ('publish','draft')
               AND ID != %d
               AND post_content LIKE %s
               AND ( post_content LIKE %s OR post_content LIKE %s )
             LIMIT 200",
            $dest_post_id, $like_il, $like_u1, $like_u2
        ) );

        // Elementor candidates: any builder page (we scan their widget HTML
        // below, since the link/href live escaped inside _elementor_data and
        // can't be matched reliably with a LIKE on post_content).
        $elementor = $wpdb->get_results( $wpdb->prepare(
            "SELECT p.ID, p.post_title FROM {$wpdb->postmeta} pm
             INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
             WHERE pm.meta_key = '_elementor_edit_mode'
               AND pm.meta_value = 'builder'
               AND p.post_status IN ('publish','draft')
               AND p.ID != %d
               AND p.post_type NOT IN ('revision','attachment','nav_menu_item')
             LIMIT 200",
            $dest_post_id
        ) );

        // Merge unique by ID.
        $posts = array();
        foreach ( array_merge( $classic, $elementor ) as $p ) {
            $posts[ (int) $p->ID ] = $p;
        }

        $in_rules    = array();
        $in_semantic = array();

        $scanned = 0;
        $rule_hits_total = 0;
        foreach ( $posts as $source ) {
            $source_url = get_permalink( $source->ID );

            // Canonical scan HTML (no double-count on Elementor pages).
            $html = self::get_scan_html( $source->ID );
            $scanned++;

            // ── Rule links: find ALL <a data-il="1"> tags, then check href ──
            if ( preg_match_all(
                '/<a\s([^>]*data-il=["\']1["\'][^>]*)>(.*?)<\/a>/is',
                $html, $matches, PREG_SET_ORDER
            ) ) {
                foreach ( $matches as $m ) {
                    if ( preg_match( '/href=["\']([^"\']+)["\']/', $m[1], $href_m ) ) {
                        $href = esc_url_raw( $href_m[1] );
                        $rule_hits_total++;
                        // Robust match: compare by RESOLVED post ID (immune to
                        // http/https, www, trailing slash, query/fragment
                        // differences), with a string compare as fallback.
                        $href_id = url_to_postid( $href );
                        $is_match = ( $href_id && $href_id === $dest_post_id )
                                 || trailingslashit( $href ) === $dest_url_trail
                                 || $href === $dest_url;
                        if ( $is_match ) {
                            $in_rules[] = array(
                                'source_id'    => (int) $source->ID,
                                'source_title' => $source->post_title,
                                'source_url'   => $source_url,
                                'anchor'       => wp_strip_all_tags( $m[2] ),
                            );
                        }
                    }
                }
            }
        }

        // ── Incoming SEMANTIC links: read from il_semantic_links ─────
        // Single source of truth — guarantees the global incoming total
        // equals the global outgoing total (every A→B row is one outgoing
        // for A and one incoming for B).
        $sem_in = self::sem_available() ? $wpdb->get_results( $wpdb->prepare(
            "SELECT source_id, anchor_text, score FROM {$wpdb->prefix}il_semantic_links
             WHERE target_id = %d AND anchor_text != '__analyzed__'
               AND anchor_text IS NOT NULL AND anchor_text != ''",
            $dest_post_id
        ) ) : array();
        foreach ( $sem_in as $r ) {
            $sid = (int) $r->source_id;
            $in_semantic[] = array(
                'source_id'    => $sid,
                'source_title' => $sid ? get_the_title( $sid ) : '',
                'source_url'   => $sid ? get_permalink( $sid ) : '',
                'anchor'       => $r->anchor_text,
                'score'        => (int) $r->score,
            );
        }

        self::dbg_log( 'update_incoming IN', array(
            'post_id'     => $dest_post_id,
            'title'       => $dest_post->post_title,
            'type'        => $dest_post->post_type,
            'dest_url'    => $dest_url,
            'candidates'  => $scanned,
            'rule_tags_seen' => $rule_hits_total,
            'in_rules'    => count( $in_rules ),
            'in_semantic' => count( $in_semantic ),
        ) );

        // Save incoming counts
        $wpdb->query( $wpdb->prepare(
            "INSERT INTO {$table}
                (post_id, post_title, post_url, post_status, post_date,
                 in_rules, in_semantic, in_rules_data, in_semantic_data)
             VALUES (%d, %s, %s, %s, %s, %d, %d, %s, %s)
             ON DUPLICATE KEY UPDATE
                post_title = VALUES(post_title),
                post_url = VALUES(post_url),
                in_rules = VALUES(in_rules),
                in_semantic = VALUES(in_semantic),
                in_rules_data = VALUES(in_rules_data),
                in_semantic_data = VALUES(in_semantic_data),
                updated_at = NOW()",
            $dest_post_id,
            $dest_post->post_title,
            get_permalink( $dest_post_id ),
            $dest_post->post_status,
            $dest_post->post_date,
            count( $in_rules ),
            count( $in_semantic ),
            wp_json_encode( $in_rules ),
            wp_json_encode( $in_semantic )
        ) );
    }

    // ─────────────────────────────────────────────────────────────────
    //  FULL RECALCULATION (all posts)
    // ─────────────────────────────────────────────────────────────────

    /**
     * Recalculate stats for all published posts.
     * Called from the admin "Recalcular" button.
     * Processes in batches to avoid timeout.
     */
    public static function recalculate_batch( $batch_size = 20, $offset = 0 ) {
        global $wpdb;

        // On first batch, clean up deleted posts from link_stats table
        if ( $offset === 0 ) {
            self::dbg_reset();
            self::dbg_log( 'recalculate START' );
            $stats_table = $wpdb->prefix . self::TABLE;
            // Remove rows whose post no longer exists OR is no longer in a
            // visible state (trashed, auto-draft, etc.). Without the status
            // check, a page sent to the Trash days ago would keep showing in
            // the map because the row lingers in the cache.
            $wpdb->query(
                "DELETE s FROM {$stats_table} s
                 LEFT JOIN {$wpdb->posts} p ON p.ID = s.post_id
                 WHERE p.ID IS NULL
                    OR p.post_status NOT IN ('publish','draft')"
            );

            // Pillar pages may be of post_type 'page', which the type filter
            // below can exclude — yet they are the most important link
            // destinations. Process them explicitly so their incoming links
            // (and their row in the map) are always computed, regardless of
            // the configured origin type.
            $pil_table = $wpdb->prefix . 'il_pilares';
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            $pil_urls = $wpdb->get_col( "SELECT url FROM {$pil_table}" );
            foreach ( (array) $pil_urls as $u ) {
                $pid = url_to_postid( $u );
                if ( $pid ) {
                    self::update_post( (int) $pid );
                    self::update_incoming( (int) $pid );
                }
            }
        }

        $origen_tipo = get_option( 'flink_origen_tipo', 'any' );
        if ( $origen_tipo !== 'any' ) {
            $types = array_map( 'sanitize_key', explode( ',', $origen_tipo ) );
        } else {
            $types = array( 'post', 'page' );
        }
        // Build a "%s, %s, ..." placeholder list for the post types so the
        // values go through wpdb::prepare() instead of being concatenated
        // straight into the query.
        $type_placeholders = implode( ', ', array_fill( 0, count( $types ), '%s' ) );

        // COUNT query.
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $type_placeholders only contains %s tokens; the actual values are bound via prepare().
        $total = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->posts}
             WHERE post_status IN ('publish','draft')
               AND post_type IN ({$type_placeholders})",
            $types
        ) );

        // ID batch query: same placeholders + the LIMIT/OFFSET values.
        $batch_args = array_merge( $types, array( $batch_size, $offset ) );
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $type_placeholders holds a dynamic count of %s tokens that the static analyser cannot expand; all values (types + limit + offset) are bound via the $batch_args array.
        $posts = $wpdb->get_col( $wpdb->prepare(
            "SELECT ID FROM {$wpdb->posts}
             WHERE post_status IN ('publish','draft')
               AND post_type IN ({$type_placeholders})
             ORDER BY ID ASC
             LIMIT %d OFFSET %d",
            $batch_args
        ) );

        foreach ( $posts as $pid ) {
            self::update_post( (int) $pid );
        }

        $done = ( $offset + count( $posts ) >= $total );

        // FINAL CONSISTENCY PASS. update_post() writes a post's outgoing
        // counts and refreshes the incoming counts of its destinations. But
        // across a batched run the incoming side can lag (a destination's row
        // may be touched before all its sources are processed). Once every
        // source has been processed, we recompute incoming for every post
        // that is a destination of a semantic link or a rule link, so the
        // cached in_* columns match the source of truth and the table totals
        // reconcile with the summary.
        if ( $done ) {
            $dest_ids = array();

            // Semantic endpoints (real links only) — BOTH sides, so every
            // post involved in a semantic link has a row and the table totals
            // match the summary exactly. Skipped entirely in the free edition,
            // which has no semantic table.
            if ( self::sem_available() ) {
                $sem_targets = $wpdb->get_col(
                    "SELECT DISTINCT target_id FROM {$wpdb->prefix}il_semantic_links
                     WHERE anchor_text != '__analyzed__'
                       AND anchor_text IS NOT NULL AND anchor_text != ''"
                );
                foreach ( (array) $sem_targets as $t ) $dest_ids[ (int) $t ] = true;

                $sem_sources = $wpdb->get_col(
                    "SELECT DISTINCT source_id FROM {$wpdb->prefix}il_semantic_links
                     WHERE anchor_text != '__analyzed__'
                       AND anchor_text IS NOT NULL AND anchor_text != ''"
                );
                foreach ( (array) $sem_sources as $sx ) $dest_ids[ (int) $sx ] = true;
            }

            // Also refresh incoming for every row already in the stats table
            // (rule destinations etc.).
            $stats_ids = $wpdb->get_col( "SELECT post_id FROM {$wpdb->prefix}" . self::TABLE );
            foreach ( (array) $stats_ids as $sid ) $dest_ids[ (int) $sid ] = true;

            self::dbg_log( 'final consistency pass', array( 'dest_count' => count( $dest_ids ) ) );
            foreach ( array_keys( $dest_ids ) as $did ) {
                if ( $did ) {
                    // Ensure a row exists with base data (out side) AND refresh
                    // the incoming side, so semantic-only targets show up too.
                    self::update_post( (int) $did );
                    self::update_incoming( (int) $did );
                }
            }
        }

        return array(
            'processed' => count( $posts ),
            'offset'    => $offset + count( $posts ),
            'total'     => $total,
            'done'      => $done,
        );
    }

    // ─────────────────────────────────────────────────────────────────
    //  QUERIES
    // ─────────────────────────────────────────────────────────────────

    /**
     * Get all posts with their link stats for the "Éxitos" table.
     * Only returns posts that have at least one link (in or out).
     */
    public static function get_all( $orderby = 'total', $order = 'DESC', $search = '' ) {
        global $wpdb;
        $table = $wpdb->prefix . self::TABLE;
        $sem   = $wpdb->prefix . 'il_semantic_links';

        $search_where = '';
        if ( $search ) {
            $search_where = $wpdb->prepare( "AND s.post_title LIKE %s", '%' . $wpdb->esc_like( $search ) . '%' );
        }

        // Semantic counts are computed LIVE from il_semantic_links (the single
        // source of truth) via correlated subqueries, NOT from the cached
        // out_semantic/in_semantic columns. This guarantees that, row by row,
        // every A→B link is counted once as outgoing for A and once as
        // incoming for B — so the column totals always reconcile with the
        // summary (no more "Sal. semántico 11 vs Ent. semántico 18"). Rule
        // counts stay cached (they're scanned from HTML).
        $sem_out = "(SELECT COUNT(*) FROM {$sem} z WHERE z.source_id = s.post_id AND z.anchor_text != '__analyzed__' AND z.anchor_text IS NOT NULL AND z.anchor_text != '')";
        $sem_in  = "(SELECT COUNT(*) FROM {$sem} z WHERE z.target_id = s.post_id AND z.anchor_text != '__analyzed__' AND z.anchor_text IS NOT NULL AND z.anchor_text != '')";
        // Free edition has no semantic table: fall back to 0 so the query
        // doesn't reference a missing table (which would fail entirely).
        if ( ! self::sem_available() ) { $sem_out = '0'; $sem_in = '0'; }

        $order_col = "(s.out_rules + {$sem_out} + s.in_rules + {$sem_in})";
        $order_clause = "ORDER BY {$order_col} DESC";
        if ( $orderby === 'in_total' )  $order_clause = "ORDER BY (s.in_rules + {$sem_in}) " . $order;
        if ( $orderby === 'out_total' ) $order_clause = "ORDER BY (s.out_rules + {$sem_out}) " . $order;
        if ( $orderby === 'date' )      $order_clause = 'ORDER BY s.post_date ' . $order;
        if ( $orderby === 'title' )     $order_clause = 'ORDER BY s.post_title ' . $order;

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- subqueries reference fixed table names and bound-free constants; $search_where is prepared above.
        return $wpdb->get_results(
            "SELECT s.post_id, s.post_title, s.post_url, s.post_status, s.post_date,
                    s.out_rules, s.in_rules, s.out_rules_data, s.in_rules_data,
                    {$sem_out} AS out_semantic,
                    {$sem_in}  AS in_semantic,
                    p.post_type AS post_type,
                    (s.out_rules + {$sem_out}) AS out_total,
                    (s.in_rules + {$sem_in})   AS in_total,
                    (s.out_rules + {$sem_out} + s.in_rules + {$sem_in}) AS link_total
             FROM {$table} s
             LEFT JOIN {$wpdb->posts} p ON p.ID = s.post_id
             WHERE (s.out_rules + {$sem_out} + s.in_rules + {$sem_in}) > 0
               AND p.ID IS NOT NULL
               AND p.post_status IN ('publish','draft')
               {$search_where}
             {$order_clause}
             LIMIT 500"
        );
    }

    /**
     * Get detail data (JSON) for a specific post and link type.
     */
    public static function get_detail( $post_id, $type ) {
        global $wpdb;
        $table  = $wpdb->prefix . self::TABLE;
        $post_id = (int) $post_id;

        // Semantic detail comes from il_semantic_links (same source as the
        // counters in get_all), so the modal list always matches the cell
        // number. Rule detail still comes from the cached JSON columns.
        if ( $type === 'out_semantic' || $type === 'in_semantic' ) {
            // Free edition has no semantic table: nothing to show.
            if ( ! self::sem_available() ) return array();
            $sem = $wpdb->prefix . 'il_semantic_links';
            if ( $type === 'out_semantic' ) {
                $rows = $wpdb->get_results( $wpdb->prepare(
                    "SELECT target_id AS other_id, anchor_text, score FROM {$sem}
                     WHERE source_id = %d AND anchor_text != '__analyzed__'
                       AND anchor_text IS NOT NULL AND anchor_text != ''",
                    $post_id
                ) );
            } else {
                $rows = $wpdb->get_results( $wpdb->prepare(
                    "SELECT source_id AS other_id, anchor_text, score FROM {$sem}
                     WHERE target_id = %d AND anchor_text != '__analyzed__'
                       AND anchor_text IS NOT NULL AND anchor_text != ''",
                    $post_id
                ) );
            }
            $out = array();
            foreach ( $rows as $r ) {
                $oid = (int) $r->other_id;
                if ( $type === 'out_semantic' ) {
                    $out[] = array(
                        'post_id'    => $oid,
                        'post_title' => $oid ? get_the_title( $oid ) : '',
                        'url'        => $oid ? get_permalink( $oid ) : '',
                        'anchor'     => $r->anchor_text,
                        'score'      => (int) $r->score,
                    );
                } else {
                    $out[] = array(
                        'source_id'    => $oid,
                        'source_title' => $oid ? get_the_title( $oid ) : '',
                        'source_url'   => $oid ? get_permalink( $oid ) : '',
                        'anchor'       => $r->anchor_text,
                        'score'        => (int) $r->score,
                    );
                }
            }
            return $out;
        }

        $column = '';
        switch ( $type ) {
            case 'out_rules':    $column = 'out_rules_data'; break;
            case 'in_rules':     $column = 'in_rules_data'; break;
            default: return array();
        }
        $raw = $wpdb->get_var( $wpdb->prepare(
            "SELECT {$column} FROM {$table} WHERE post_id = %d",
            $post_id
        ) );
        return json_decode( $raw ?: '[]', true ) ?: array();
    }

    /**
     * Concatenate the HTML of every text-editor and html widget found in a
     * post's _elementor_data tree. Used by update_post() so link counters
     * include links living inside Elementor widgets (not just post_content).
     *
     * Public so the stats/detail panels (FLINK_Stats, FLINK_Ajax) can count
     * against the SAME source the front-end renders, instead of post_content
     * (which may hold stale, non-rendered links on Elementor pages).
     */
    /**
     * Return the HTML to scan for internal links for a given post.
     *
     * CRITICAL: for Elementor builder pages we return ONLY the Elementor
     * widget HTML, never post_content. Elementor keeps a rendered copy of the
     * page in post_content as well, so concatenating both counts every link
     * TWICE (this is why a pillar with 15 incoming links was reported as 30).
     * Classic pages have their content in post_content as usual.
     */
    public static function get_scan_html( $post_id ) {
        if ( get_post_meta( $post_id, '_elementor_edit_mode', true ) === 'builder' ) {
            $el = self::extract_elementor_html( $post_id );
            // Fallback: if for some reason the Elementor data yields nothing,
            // fall back to post_content so we don't lose links entirely.
            return ( trim( $el ) !== '' ) ? $el : (string) get_post_field( 'post_content', $post_id );
        }
        return (string) get_post_field( 'post_content', $post_id );
    }

    public static function extract_elementor_html( $post_id ) {
        $raw = get_post_meta( $post_id, '_elementor_data', true );
        if ( is_string( $raw ) ) {
            $data = json_decode( $raw, true );
        } elseif ( is_array( $raw ) ) {
            $data = $raw;
        } else {
            $data = null;
        }
        if ( ! is_array( $data ) ) return '';

        $html = '';
        self::collect_elementor_widget_html( $data, $html );
        return $html;
    }

    /** Recursively gather text-editor/html widget content from the tree. */
    private static function collect_elementor_widget_html( array $elements, &$html ) {
        foreach ( $elements as $el ) {
            if ( isset( $el['widgetType'] ) ) {
                if ( $el['widgetType'] === 'text-editor' && isset( $el['settings']['editor'] ) ) {
                    $html .= "\n" . $el['settings']['editor'];
                } elseif ( $el['widgetType'] === 'html' && isset( $el['settings']['html'] ) ) {
                    $html .= "\n" . $el['settings']['html'];
                }
            }
            if ( ! empty( $el['elements'] ) && is_array( $el['elements'] ) ) {
                self::collect_elementor_widget_html( $el['elements'], $html );
            }
        }
    }

    /**
     * Get summary totals for the header stats.
     *
     * Rule links are read from the cache table (they're scanned from HTML).
     * Semantic links are read DIRECTLY from il_semantic_links so the outgoing
     * total always equals the incoming total — every A→B row contributes one
     * outgoing (for A) and one incoming (for B). Mixing sources here is what
     * produced "Sal. semántico 11 vs Ent. semántico 18".
     */
    public static function get_summary() {
        global $wpdb;
        $table = $wpdb->prefix . self::TABLE;

        // Rule totals + post/orphan counts from the cache. Filter to posts
        // that still exist in a visible state, EXACTLY like get_all(), so the
        // summary always matches the sum of the table below it. (A stale row
        // for a trashed/deleted page was inflating out_rules in the summary
        // while the table — which filters — didn't show it.)
        $base = $wpdb->get_row(
            "SELECT
                COUNT(*)                                    AS total_posts,
                SUM(s.out_rules)                            AS total_out_rules,
                SUM(s.in_rules)                             AS total_in_rules,
                SUM(CASE WHEN p.post_type = 'page' THEN 1 ELSE 0 END) AS pages_count,
                SUM(CASE WHEN p.post_type = 'post' THEN 1 ELSE 0 END) AS posts_count,
                SUM(CASE WHEN s.in_rules + s.in_semantic = 0 THEN 1 ELSE 0 END) AS orphan_posts,
                SUM(CASE WHEN s.in_rules + s.in_semantic = 0 AND p.post_type = 'page' THEN 1 ELSE 0 END) AS orphan_pages,
                SUM(CASE WHEN s.in_rules + s.in_semantic = 0 AND p.post_type = 'post' THEN 1 ELSE 0 END) AS orphan_post_type
             FROM {$table} s
             INNER JOIN {$wpdb->posts} p ON p.ID = s.post_id
             WHERE (s.out_rules + s.out_semantic + s.in_rules + s.in_semantic) > 0
               AND p.post_status IN ('publish','draft')"
        );

        // Semantic total = number of real links in il_semantic_links. This is
        // simultaneously the outgoing total AND the incoming total (they're
        // equal by definition), so both columns show the same correct number.
        // Free edition has no semantic table: total is simply 0.
        $sem_total = self::sem_available() ? (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->prefix}il_semantic_links
             WHERE anchor_text != '__analyzed__'
               AND anchor_text IS NOT NULL AND anchor_text != ''"
        ) : 0;

        $out_rules = (int) ( $base->total_out_rules ?? 0 );
        $in_rules  = (int) ( $base->total_in_rules ?? 0 );

        // Rule links counted ONCE: outgoing == incoming for rules (every rule
        // link that leaves a post arrives at its destination). We use the
        // outgoing count as the canonical "number of rule links".
        $rule_links = $out_rules;

        // Build a plain object mirroring the previous shape.
        $summary = new \stdClass();
        $summary->total_posts        = (int) ( $base->total_posts ?? 0 );
        $summary->pages_count        = (int) ( $base->pages_count ?? 0 );
        $summary->posts_count        = (int) ( $base->posts_count ?? 0 );
        $summary->total_out_rules    = $out_rules;
        $summary->total_out_semantic = $sem_total;
        $summary->total_in_rules     = $in_rules;
        $summary->total_in_semantic  = $sem_total;
        $summary->rule_links         = $rule_links;
        // Total internal links = each link counted ONCE (rule links + semantic
        // links). NOT outgoing+incoming, which would double-count every link.
        $summary->total_links        = $rule_links + $sem_total;
        $summary->orphan_posts       = (int) ( $base->orphan_posts ?? 0 );
        $summary->orphan_pages       = (int) ( $base->orphan_pages ?? 0 );
        $summary->orphan_post_type   = (int) ( $base->orphan_post_type ?? 0 );

        self::dbg_log( 'get_summary', array(
            'rule_links'   => $rule_links,
            'sem_total'    => $sem_total,
            'total_links'  => $summary->total_links,
            'pages'        => $summary->pages_count,
            'posts'        => $summary->posts_count,
            'orphan_posts' => $summary->orphan_posts,
        ) );

        return $summary;
    }
}