<?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';

    // ─────────────────────────────────────────────────────────────────
    //  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;

        $content = $post->post_content;

        // ── 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 );
                }
            }
        }

        // ── Extract outgoing semantic links (data-il-s="1") ──────────
        $out_semantic = array();
        if ( preg_match_all(
            '/<a\s([^>]*data-il-s=["\']1["\'][^>]*)>(.*?)<\/a>/is',
            $content, $matches, PREG_SET_ORDER
        ) ) {
            foreach ( $matches as $m ) {
                $attrs = $m[1];
                $anchor = wp_strip_all_tags( $m[2] );
                $url = '';
                if ( preg_match( '/href=["\']([^"\']+)["\']/', $attrs, $href_m ) ) {
                    $url = esc_url_raw( $href_m[1] );
                }
                if ( $url ) {
                    $out_semantic[] = array( 'url' => $url, 'anchor' => $anchor );
                }
            }
        }

        // Enrich outgoing 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 );

        foreach ( $out_semantic as &$link ) {
            $dest_id = url_to_postid( $link['url'] );
            $link['post_id']    = $dest_id;
            $link['post_title'] = $dest_id ? get_the_title( $dest_id ) : '';
            // Add score from semantic DB if available
            if ( $dest_id && class_exists( 'FLINK_Semantic_DB' ) ) {
                global $wpdb;
                $score = $wpdb->get_var( $wpdb->prepare(
                    "SELECT score FROM {$wpdb->prefix}il_semantic_links
                     WHERE source_id = %d AND target_id = %d AND anchor_text != '__analyzed__'",
                    $post_id, $dest_id
                ) );
                $link['score'] = (int) $score;
            }
        }
        unset( $link );

        // ── Save to cache ────────────────────────────────────────────
        global $wpdb;
        $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;

        // Search for posts containing links to this URL
        $like_il  = '%' . $wpdb->esc_like( 'data-il=' ) . '%';
        $like_u1  = '%' . $wpdb->esc_like( 'href="' . $dest_url . '"' ) . '%';
        $like_u2  = '%' . $wpdb->esc_like( 'href="' . $dest_url_trail . '"' ) . '%';
        $posts = $wpdb->get_results( $wpdb->prepare(
            "SELECT ID, post_title, post_content 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
        ) );

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

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

            // ── Rule links: find ALL <a data-il="1"> tags, then check href ──
            if ( preg_match_all(
                '/<a\s([^>]*data-il=["\']1["\'][^>]*)>(.*?)<\/a>/is',
                $source->post_content, $matches, PREG_SET_ORDER
            ) ) {
                foreach ( $matches as $m ) {
                    if ( preg_match( '/href=["\']([^"\']+)["\']/', $m[1], $href_m ) ) {
                        $href = esc_url_raw( $href_m[1] );
                        // Check if this href points to our destination (with or without trailing slash)
                        if ( trailingslashit( $href ) === $dest_url_trail || $href === $dest_url ) {
                            $in_rules[] = array(
                                'source_id'    => (int) $source->ID,
                                'source_title' => $source->post_title,
                                'source_url'   => $source_url,
                                'anchor'       => wp_strip_all_tags( $m[2] ),
                            );
                        }
                    }
                }
            }

            // ── Semantic links: find ALL <a data-il-s="1"> tags, then check href ──
            if ( preg_match_all(
                '/<a\s([^>]*data-il-s=["\']1["\'][^>]*)>(.*?)<\/a>/is',
                $source->post_content, $matches, PREG_SET_ORDER
            ) ) {
                foreach ( $matches as $m ) {
                    if ( preg_match( '/href=["\']([^"\']+)["\']/', $m[1], $href_m ) ) {
                        $href = esc_url_raw( $href_m[1] );
                        if ( trailingslashit( $href ) === $dest_url_trail || $href === $dest_url ) {
                            $in_semantic[] = array(
                                'source_id'    => (int) $source->ID,
                                'source_title' => $source->post_title,
                                'source_url'   => $source_url,
                                'anchor'       => wp_strip_all_tags( $m[2] ),
                            );
                        }
                    }
                }
            }
        }

        // Enrich semantic incoming with scores
        foreach ( $in_semantic as &$link ) {
            if ( class_exists( 'FLINK_Semantic_DB' ) ) {
                $score = $wpdb->get_var( $wpdb->prepare(
                    "SELECT score FROM {$wpdb->prefix}il_semantic_links
                     WHERE source_id = %d AND target_id = %d AND anchor_text != '__analyzed__'",
                    $link['source_id'], $dest_post_id
                ) );
                $link['score'] = (int) $score;
            }
        }
        unset( $link );

        // 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 ) {
            $stats_table = $wpdb->prefix . self::TABLE;
            $wpdb->query(
                "DELETE s FROM {$stats_table} s
                 LEFT JOIN {$wpdb->posts} p ON p.ID = s.post_id
                 WHERE p.ID IS NULL"
            );
        }

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

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

    // ─────────────────────────────────────────────────────────────────
    //  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;

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

        $order_clause = 'ORDER BY (s.out_rules + s.out_semantic + s.in_rules + s.in_semantic) DESC';
        if ( $orderby === 'in_total' )  $order_clause = 'ORDER BY (s.in_rules + s.in_semantic) ' . $order;
        if ( $orderby === 'out_total' ) $order_clause = 'ORDER BY (s.out_rules + s.out_semantic) ' . $order;
        if ( $orderby === 'date' )      $order_clause = 'ORDER BY s.post_date ' . $order;
        if ( $orderby === 'title' )     $order_clause = 'ORDER BY s.post_title ' . $order;
        return $wpdb->get_results(
            "SELECT s.*,
                    (s.out_rules + s.out_semantic) AS out_total,
                    (s.in_rules + s.in_semantic)   AS in_total,
                    (s.out_rules + s.out_semantic + s.in_rules + s.in_semantic) AS link_total
             FROM {$table} s
             WHERE (s.out_rules + s.out_semantic + s.in_rules + s.in_semantic) > 0
               {$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;
        $column = '';
        switch ( $type ) {
            case 'out_rules':    $column = 'out_rules_data'; break;
            case 'out_semantic': $column = 'out_semantic_data'; break;
            case 'in_rules':     $column = 'in_rules_data'; break;
            case 'in_semantic':  $column = 'in_semantic_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();
    }

    /**
     * Get summary totals for the header stats.
     */
    public static function get_summary() {
        global $wpdb;
        $table = $wpdb->prefix . self::TABLE;
        return $wpdb->get_row(
            "SELECT
                COUNT(*)                                    AS total_posts,
                SUM(out_rules)                              AS total_out_rules,
                SUM(out_semantic)                           AS total_out_semantic,
                SUM(in_rules)                               AS total_in_rules,
                SUM(in_semantic)                            AS total_in_semantic,
                SUM(out_rules + out_semantic + in_rules + in_semantic) AS total_links,
                SUM(CASE WHEN in_rules + in_semantic = 0 THEN 1 ELSE 0 END) AS orphan_posts
             FROM {$table}
             WHERE (out_rules + out_semantic + in_rules + in_semantic) > 0"
        );
    }
}