<?php
/**
 * Unified Link Repository for AILinkMaster.
 *
 * Single entry point for all link data access across the plugin.
 * All modules (Dashboard, KI, PageRank, Scout, Orphan) read through this class.
 *
 * SaaS-ready: When switching to remote mode, replace SQL methods with API calls.
 *
 * @since 2.0
 * @package AILinkMaster
 */

if (!defined('ABSPATH')) {
    exit;
}

class AILM_Link_Repository
{

    /**
     * Bulk-mode flag: when true, register_link() skips per-link cache
     * invalidation and SaaS sync-queue enqueuing for performance.
     *
     * Used by AILM_Import_Existing during bulk imports. Call set_bulk_mode(false)
     * and invalidate_cache() once after the bulk operation completes.
     *
     * @var bool
     */
    private static $bulk_mode = false;

    /**
     * Enable or disable bulk mode.
     *
     * @param bool $enabled True to enable bulk mode.
     * @return void
     */
    public static function set_bulk_mode($enabled)
    {
        self::$bulk_mode = (bool) $enabled;
    }

    /**
     * Get the count of outbound internal links for a given donor post.
     *
     * @param int $donor_id Post ID of the donor.
     * @return int Number of outbound internal links.
     */
    public static function get_outbound_count($donor_id)
    {
        global $wpdb;
        $table = AILM_Link_DB::get_links_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$table} WHERE donor_id = %d AND link_type = 'internal'", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $donor_id
            )
        );
    }

    /**
     * Get the count of inbound internal links for a given target post.
     *
     * @param int $target_id Post ID of the target.
     * @return int Number of inbound internal links.
     */
    public static function get_inbound_count($target_id)
    {
        global $wpdb;
        $table = AILM_Link_DB::get_links_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$table} WHERE target_id = %d AND link_type = 'internal'", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $target_id
            )
        );
    }

    /**
     * Get all outbound links for a donor post, optionally filtered by type.
     *
     * @param int    $donor_id Post ID of the donor.
     * @param string $type     Link type filter: 'internal', 'external', 'sponsored', or 'all'.
     * @return array Array of link row objects.
     */
    public static function get_outbound_links($donor_id, $type = 'internal')
    {
        global $wpdb;
        $table = AILM_Link_DB::get_links_table();

        if ('all' === $type) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
            return $wpdb->get_results(
                $wpdb->prepare("SELECT * FROM {$table} WHERE donor_id = %d", $donor_id) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            );
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$table} WHERE donor_id = %d AND link_type = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $donor_id,
                $type
            )
        );
    }

    /**
     * Get all inbound links for a target post.
     *
     * Returns donor details for the inbound links modal.
     *
     * @param int    $target_id Post ID of the target.
     * @param string $type      Link type filter.
     * @return array Array of link row objects with donor information.
     */
    public static function get_inbound_links($target_id, $type = 'internal')
    {
        global $wpdb;
        $table = AILM_Link_DB::get_links_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$table} WHERE target_id = %d AND link_type = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $target_id,
                $type
            )
        );
    }

    /**
     * Count all links of a given type.
     *
     * @param string $type Link type: 'internal', 'external', 'sponsored'.
     * @return int Total count.
     */
    public static function count_all($type = 'internal')
    {
        global $wpdb;
        $table = AILM_Link_DB::get_links_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$table} WHERE link_type = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $type
            )
        );
    }

    /**
     * Get bulk outbound counts grouped by donor_id.
     *
     * Used by Dashboard table to avoid N+1 queries.
     *
     * @param string $type Link type filter.
     * @return array Associative array: [ donor_id => count ].
     */
    public static function get_bulk_outbound_counts($type = 'internal')
    {
        global $wpdb;
        $table = AILM_Link_DB::get_links_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT donor_id, COUNT(*) as cnt FROM {$table} WHERE link_type = %s GROUP BY donor_id", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $type
            )
        );

        $counts = array();
        foreach ($rows as $row) {
            $counts[(int) $row->donor_id] = (int) $row->cnt;
        }
        return $counts;
    }

    /**
     * Get bulk inbound counts grouped by target_id.
     *
     * Used by Dashboard table to avoid N+1 queries.
     *
     * @param string $type Link type filter.
     * @return array Associative array: [ target_id => count ].
     */
    public static function get_bulk_inbound_counts($type = 'internal')
    {
        global $wpdb;
        $table = AILM_Link_DB::get_links_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT target_id, COUNT(*) as cnt FROM {$table} WHERE link_type = %s AND target_id > 0 GROUP BY target_id", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $type
            )
        );

        $counts = array();
        foreach ($rows as $row) {
            $counts[(int) $row->target_id] = (int) $row->cnt;
        }
        return $counts;
    }

    /**
     * Get all published post IDs that have zero inbound internal links (orphans).
     *
     * @return array List of post IDs.
     */
    public static function get_orphan_ids()
    {
        global $wpdb;
        $table = AILM_Link_DB::get_links_table();

        // Get all post IDs that receive at least one internal link
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $linked_ids = $wpdb->get_col(
            "SELECT DISTINCT target_id FROM {$table} WHERE link_type = 'internal' AND target_id > 0" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        );

        $all_post_ids = get_posts(
            array(
                'numberposts' => -1,
                'post_type' => 'post',
                'post_status' => 'publish',
                'fields' => 'ids',
            )
        );

        if (empty($linked_ids)) {
            return $all_post_ids;
        }

        $linked_set = array_flip($linked_ids);
        $orphans = array();
        foreach ($all_post_ids as $pid) {
            if (!isset($linked_set[$pid])) {
                $orphans[] = $pid;
            }
        }

        return $orphans;
    }

    /**
     * Build the link graph for PageRank computation.
     *
     * Returns nodes and weighted edges. Position weight is read from the stored
     * `position` column (computed by Scanner at scan time).
     *
     * @return array [ 'nodes' => [ id => 0, ... ], 'edges' => [ from => [ to => weight ] ] ]
     */
    public static function get_link_graph()
    {
        global $wpdb;
        $table = AILM_Link_DB::get_links_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $rows = $wpdb->get_results(
            "SELECT donor_id, target_id, origin, position FROM {$table} WHERE link_type = 'internal' AND target_id > 0" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        );

        $nodes = array();
        $edges = array();

        // Collect all nodes (all published posts + pages)
        $all_ids = get_posts(
            array(
                'numberposts' => -1,
                'post_type' => array('post', 'page'),
                'post_status' => 'publish',
                'fields' => 'ids',
            )
        );
        foreach ($all_ids as $pid) {
            $nodes[$pid] = 0;
        }

        foreach ($rows as $row) {
            $from = (int) $row->donor_id;
            $to = (int) $row->target_id;
            $pos = (float) $row->position;
            $origin = $row->origin;

            if ($from === $to) {
                continue; // Skip self-links.
            }

            // Position-based weight
            $weight = 1.0;
            if ($pos < 0.2) {
                $weight = 1.2; // First 20% of content.
            } elseif ($pos > 0.8) {
                $weight = 0.8; // Last 20% of content.
            }

            // Plugin-injected links get a small bonus.
            if ('plugin' === $origin) {
                $weight *= 1.1;
            }

            if (!isset($edges[$from])) {
                $edges[$from] = array();
            }
            if (!isset($edges[$from][$to])) {
                $edges[$from][$to] = 0;
            }
            $edges[$from][$to] += $weight;
        }

        return array(
            'nodes' => $nodes,
            'edges' => $edges,
        );
    }

    /**
     * Build the link graph with CTR (click) bonuses for PageRank.
     *
     * Same as get_link_graph() but enriches edge weights using click stats.
     *
     * @return array [ 'nodes' => [...], 'edges' => [...] ]
     */
    public static function get_click_enriched_graph()
    {
        $graph = self::get_link_graph();

        if (!class_exists('AILM_Clicks_DB')) {
            return $graph;
        }

        global $wpdb;
        $clicks_table = esc_sql(AILM_Clicks_DB::get_table_name());

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $clicks_rows = $wpdb->get_results(
            "SELECT target_url, SUM(click_count) as total_clicks FROM {$clicks_table} WHERE link_type = 'internal' GROUP BY target_url" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        );

        if (empty($clicks_rows)) {
            return $graph;
        }

        // Build target_id => total_clicks map
        $ctr_map = array();
        foreach ($clicks_rows as $crow) {
            $target_id = url_to_postid($crow->target_url);
            if ($target_id) {
                if (!isset($ctr_map[$target_id])) {
                    $ctr_map[$target_id] = 0;
                }
                $ctr_map[$target_id] += (int) $crow->total_clicks;
            }
        }

        // Apply CTR bonus to edges
        foreach ($graph['edges'] as $from => &$targets) {
            foreach ($targets as $to => &$weight) {
                if (isset($ctr_map[$to])) {
                    $ctr_bonus = min(2.0, log(1 + $ctr_map[$to], 2));
                    $weight *= (1.0 + $ctr_bonus * 0.3);
                }
            }
        }
        unset($targets, $weight);

        return $graph;
    }

    // =========================================================================
    // RULE MANAGEMENT
    // =========================================================================

    /**
     * Add a new link injection rule.
     *
     * @param int    $donor_id   Post ID of the donor.
     * @param string $target_url URL of the link target.
     * @param string $anchor     Anchor text / keyword.
     * @param string $source     Origin: 'ai', 'ki_manual', 'csv', 'dashboard'.
     * @param int    $occurrence Which occurrence of the keyword to link (default 1).
     * @return int|false Inserted rule ID on success, false on failure.
     */
    public static function add_rule($donor_id, $target_url, $anchor, $source = 'ai', $occurrence = 1)
    {
        global $wpdb;
        $table = AILM_Link_DB::get_rules_table();

        $target_id = url_to_postid($target_url);

        // Safeguard: Check if an identical active rule already exists to avoid duplicates
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $existing = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT id FROM {$table} WHERE donor_id = %d AND (target_id = %d OR target_url = %s) AND anchor = %s AND status = 'active'", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $donor_id,
                $target_id ? $target_id : 0,
                esc_url_raw($target_url),
                sanitize_text_field($anchor)
            )
        );

        if ($existing) {
            return (int) $existing->id;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $result = $wpdb->insert(
            $table,
            array(
                'donor_id' => $donor_id,
                'target_id' => $target_id ? $target_id : 0,
                'target_url' => esc_url_raw($target_url),
                'anchor' => sanitize_text_field($anchor),
                'occurrence' => $occurrence,
                'source' => sanitize_key($source),
                'status' => 'active',
            ),
            array('%d', '%d', '%s', '%s', '%d', '%s', '%s')
        );

        if (false === $result) {
            return false;
        }

        return $wpdb->insert_id;
    }

    /**
     * Unified entry point for registering a link rule.
     *
     * All modules (KI, Rescue, Force Link, AI, Gutenberg, Sync, Reconciler)
     * should go through this method instead of calling add_rule() directly.
     *
     * It wraps add_rule() and additionally:
     *  - sets the `in_html` flag (0 = needs injection, 1 = already in HTML);
     *  - invalidates the relevant caches;
     *  - (Phase 5) enqueues async replication to the SaaS server.
     *
     * Backward compatible: if the `in_html` column does not exist yet
     * (pre-2.1 DB), the flag is silently ignored and add_rule() behaves
     * exactly as before.
     *
     * @param int    $donor_id   Post ID of the donor.
     * @param string $target_url URL of the link target.
     * @param string $anchor     Anchor text / keyword.
     * @param string $source     Origin: 'ai', 'ki_manual', 'csv', 'dashboard',
     *                           'orphan_rescue', 'manual_ui', 'ai_smart_link',
     *                           'editor_manual', 'pre_existing', 'manual'.
     * @param int    $occurrence Which occurrence of the keyword to link (default 1).
     * @param bool   $in_html    True if the link is already present in the
     *                           donor's post_content (manual / pre-existing /
     *                           already injected). Default false (needs injection).
     * @return int|false Inserted rule ID on success, false on failure.
     */
    public static function register_link($donor_id, $target_url, $anchor, $source = 'ai', $occurrence = 1, $in_html = false)
    {
        $rule_id = self::add_rule($donor_id, $target_url, $anchor, $source, $occurrence);

        if (!$rule_id) {
            return false;
        }

        // Set the in_html flag. add_rule() returns an existing ID for duplicates,
        // so this also updates the flag on a pre-existing rule if it changed.
        if ($in_html) {
            self::mark_in_html($rule_id, true);
        }

        // In bulk mode, skip per-link cache invalidation and SaaS sync
        // queue enqueuing for performance. The caller is responsible for
        // calling invalidate_cache() once after the bulk operation.
        if (!self::$bulk_mode) {
            // Invalidate caches for both donor and target so counts are fresh.
            if (method_exists(__CLASS__, 'invalidate_rule_count_cache')) {
                self::invalidate_rule_count_cache($donor_id);
                $target_id = url_to_postid($target_url);
                if ($target_id) {
                    self::invalidate_rule_count_cache($target_id);
                }
            }
            self::invalidate_cache();

            // Phase 5: enqueue async replication to SaaS.
            if (class_exists('AILM_Sync_Queue')) {
                AILM_Sync_Queue::enqueue('upsert', (int) $rule_id, array(
                    'donor_id'   => (int) $donor_id,
                    'target_url' => $target_url,
                    'anchor'     => $anchor,
                    'source'     => $source,
                    'occurrence' => (int) $occurrence,
                    'in_html'    => $in_html ? 1 : 0,
                ));
            }
        }

        return $rule_id;
    }

    /**
     * Mark a rule as already present in the donor's HTML (or not).
     *
     * Used by:
     *  - inject_physical_links() after a successful injection (in_html = 1);
     *  - apply_smart_link() (AI/Gutenberg) after inserting a link (in_html = 1);
     *  - the Reconciler when it detects a rule's link is present/absent in HTML.
     *
     * Silently no-ops if the `in_html` column does not exist (pre-2.1 DB),
     * keeping the method safe to call from upgraded code paths.
     *
     * @param int  $rule_id Rule ID.
     * @param bool $value   True = link is in HTML, False = needs injection.
     * @return bool True on success (or column missing), false on DB error.
     */
    public static function mark_in_html($rule_id, $value)
    {
        global $wpdb;
        $table = AILM_Link_DB::get_rules_table();

        // Guard: if the column does not exist (pre-2.1), do nothing.
        // Cached per-request to avoid running SHOW COLUMNS on every call
        // (critical for bulk imports that register thousands of links).
        static $column_exists_cache = null;
        if (null === $column_exists_cache) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $column_exists_cache = $wpdb->get_results(
                $wpdb->prepare(
                    'SHOW COLUMNS FROM %i LIKE %s',
                    $table,
                    'in_html'
                )
            );
        }
        if (empty($column_exists_cache)) {
            return true;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->update(
            $table,
            array('in_html' => $value ? 1 : 0),
            array('id' => (int) $rule_id),
            array('%d'),
            array('%d')
        );

        $success = false !== $result;

        // Phase 5: enqueue async replication to SaaS.
        if ($success && class_exists('AILM_Sync_Queue')) {
            AILM_Sync_Queue::enqueue('upsert', (int) $rule_id, array('in_html' => $value ? 1 : 0));
        }

        return $success;
    }

    /**
     * Get all active rules for a given donor post.
     *
     * @param int $donor_id Post ID.
     * @return array Array of rule objects.
     */
    public static function get_rules_for_donor($donor_id)
    {
        global $wpdb;
        $table = AILM_Link_DB::get_rules_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$table} WHERE donor_id = %d AND status = 'active' ORDER BY id ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $donor_id
            )
        );
    }

    /**
     * Get all active rules targeting a given post.
     *
     * @param int $target_id Post ID of the target.
     * @return array Array of rule objects.
     */
    public static function get_rules_for_target($target_id)
    {
        global $wpdb;
        $table = AILM_Link_DB::get_rules_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$table} WHERE target_id = %d AND status = 'active' ORDER BY id ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $target_id
            )
        );
    }

    /**
     * Get the count of active inbound rules for a given target post.
     *
     * @param int $target_id Post ID of the target.
     * @return int Number of active inbound rules.
     */
    public static function get_active_inbound_rule_count($target_id)
    {
        global $wpdb;
        $table = AILM_Link_DB::get_rules_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$table} WHERE target_id = %d AND status = 'active'", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $target_id
            )
        );
    }

    /**
     * Delete a rule by ID.
     *
     * @param int $rule_id Rule ID.
     * @return bool True on success.
     */
    public static function delete_rule($rule_id)
    {
        global $wpdb;
        $table = AILM_Link_DB::get_rules_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = (bool) $wpdb->delete($table, array('id' => $rule_id), array('%d'));

        // Phase 5: enqueue async replication to SaaS.
        if ($result && class_exists('AILM_Sync_Queue')) {
            AILM_Sync_Queue::enqueue('delete', (int) $rule_id);
        }

        return $result;
    }

    /**
     * Delete all rules for a donor post.
     *
     * @param int $donor_id Post ID.
     * @return int Number of deleted rows.
     */
    public static function delete_rules_for_donor($donor_id)
    {
        global $wpdb;
        $table = AILM_Link_DB::get_rules_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->delete($table, array('donor_id' => $donor_id), array('%d'));
    }

    /**
     * Delete a scanned link by ID.
     *
     * @param int $link_id Link ID.
     * @return bool True on success.
     */
    public static function delete_link($link_id)
    {
        global $wpdb;
        $table = AILM_Link_DB::get_links_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return (bool) $wpdb->delete($table, array('id' => $link_id), array('%d'));
    }

    /**
     * Delete all scanned links for a donor post.
     *
     * Used by Scanner before re-scanning a post.
     *
     * @param int $donor_id Post ID.
     * @return int|false Number of deleted rows or false on error.
     */
    public static function delete_links_by_donor($donor_id)
    {
        global $wpdb;
        $table = AILM_Link_DB::get_links_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->delete($table, array('donor_id' => $donor_id), array('%d'));
    }

    /**
     * Update the source of a rule by ID.
     *
     * Used by the sync tool to correct the source of rules that were
     * previously created with a different source (e.g., 'ai' by the
     * reconciler) but are actually pre-existing manual links.
     *
     * @param int    $rule_id Rule ID.
     * @param string $source  New source: 'pre_existing', 'ai', etc.
     * @return bool True on success.
     */
    public static function update_rule_source($rule_id, $source)
    {
        global $wpdb;
        $table = AILM_Link_DB::get_rules_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = (bool) $wpdb->update(
            $table,
            array('source' => sanitize_key($source)),
            array('id' => (int) $rule_id),
            array('%s'),
            array('%d')
        );

        return $result;
    }

    /**
     * Update rule status by ID.
     *
     * @param int    $rule_id Rule ID.
     * @param string $status  New status: 'active', 'failed', 'paused'.
     * @return bool True on success.
     */
    public static function update_rule_status($rule_id, $status)
    {
        global $wpdb;
        $table = AILM_Link_DB::get_rules_table();

        $allowed_statuses = array('active', 'failed', 'paused');
        if (!in_array($status, $allowed_statuses, true)) {
            return false;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = (bool) $wpdb->update(
            $table,
            array('status' => $status),
            array('id' => $rule_id),
            array('%s'),
            array('%d')
        );

        // Phase 5: enqueue async replication to SaaS.
        if ($result && class_exists('AILM_Sync_Queue')) {
            AILM_Sync_Queue::enqueue('upsert', (int) $rule_id, array('status' => $status));
        }

        return $result;
    }

    /**
     * Delete all rules across all donors.
     *
     * @return int|false Number of deleted rows or false on error.
     */
    public static function delete_all_rules()
    {
        global $wpdb;
        $table = AILM_Link_DB::get_rules_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->query("TRUNCATE TABLE {$table}");
    }

    /**
     * Get all active rules across the entire site.
     *
     * @return array Array of rule objects.
     */
    public static function get_all_rules()
    {
        global $wpdb;
        $table = AILM_Link_DB::get_rules_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_results(
            "SELECT * FROM {$table} WHERE status = 'active' ORDER BY id ASC" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        );
    }

    /**
     * Get rules for a specific target post and anchor text.
     *
     * @param int    $target_id Target post ID.
     * @param string $anchor    Anchor text.
     * @return array Array of matching rule objects.
     */
    public static function get_rules_by_target_and_anchor($target_id, $anchor)
    {
        global $wpdb;
        $table = AILM_Link_DB::get_rules_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$table} WHERE target_id = %d AND anchor = %s AND status = 'active'", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $target_id,
                $anchor
            )
        );
    }

    /**
     * Get anchor usage counts for a target post.
     *
     * Returns associative array: anchor_text => count of active rules.
     *
     * @param int $target_id Post ID of the target.
     * @return array
     */
    public static function get_anchor_counts_for_target($target_id)
    {
        global $wpdb;
        $table = AILM_Link_DB::get_rules_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT anchor, COUNT(*) as cnt FROM {$table} WHERE target_id = %d AND status = 'active' GROUP BY anchor", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $target_id
            )
        );

        $counts = array();
        foreach ($rows as $row) {
            $counts[$row->anchor] = (int) $row->cnt;
        }
        return $counts;
    }

    /**
     * Trigger a rescan of a single post.
     *
     * Convenience wrapper that calls the Scanner if available.
     *
     * @param int $donor_id Post ID to rescan.
     * @return void
     */
    public static function sync_donor($donor_id)
    {
        if (class_exists('AILM_Scout_Scanner')) {
            AILM_Scout_Scanner::process_post($donor_id);
        }
    }

    /**
     * Invalidate all dashboard-related caches.
     *
     * Should be called after any data mutation (link insert/delete, rule change).
     *
     * @return void
     */
    public static function invalidate_cache()
    {
        wp_cache_delete('ailm_dashboard_frozen_maps', 'ailm_cache_group');
        wp_cache_delete('ailm_dashboard_manual_links', 'ailm_cache_group');
        wp_cache_delete('ailm_dashboard_internal_links', 'ailm_cache_group');
    }

    /**
     * Build a cache key for rule count queries.
     *
     * @param int    $post_id Post ID.
     * @param string $type    'inbound' or 'outbound'.
     * @return string
     */
    private static function _get_count_cache_key($post_id, $type)
    {
        return "ailm_{$type}_rule_count_{$post_id}";
    }

    /**
     * Get the count of active outbound rules for a given donor post.
     *
     * Uses Object Cache (group: ailm_cache_group) with a 1-hour TTL.
     * Effective only with a persistent object cache (Redis/Memcached).
     *
     * @param int $donor_id Post ID of the donor.
     * @return int Number of active outbound rules.
     */
    public static function get_active_outbound_rule_count($donor_id)
    {
        $cache_key = self::_get_count_cache_key($donor_id, 'outbound');
        $cached = wp_cache_get($cache_key, 'ailm_cache_group');
        if (false !== $cached) {
            return (int) $cached;
        }

        global $wpdb;
        $table = AILM_Link_DB::get_rules_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $count = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$table} WHERE donor_id = %d AND status = 'active'", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $donor_id
            )
        );

        wp_cache_set($cache_key, $count, 'ailm_cache_group', HOUR_IN_SECONDS);
        return $count;
    }

    /**
     * Invalidate the rule count cache (inbound + outbound) for a post.
     *
     * Call this whenever rules for a post are added, deleted, or status-changed.
     *
     * @param int $post_id Post ID (works for both donor and target roles).
     * @return void
     */
    public static function invalidate_rule_count_cache($post_id)
    {
        wp_cache_delete(self::_get_count_cache_key($post_id, 'inbound'), 'ailm_cache_group');
        wp_cache_delete(self::_get_count_cache_key($post_id, 'outbound'), 'ailm_cache_group');
    }
}