<?php
if (!defined('ABSPATH')) exit;
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Every table name is $wpdb->prefix plus a fixed suffix from a private helper (never user input); all runtime values go through $wpdb->prepare() placeholders.

class Linkbraid_Database {

    /**
     * One-time migration from the previous brand (jenga_* prefixes).
     * Renames the tables, moves the options and the post meta, clears the old cron.
     * It runs only when the old tables exist and the new ones do not yet.
     */
    private static function migrate_from_legacy() {
        global $wpdb;
        $legacy_index = $wpdb->prefix . 'jenga_post_index';
        $new_index = $wpdb->prefix . 'lbraid_post_index';
        if (!$wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($legacy_index)))) return;
        if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($new_index)))) return;

        foreach (['post_index', 'link_suggestions', 'existing_links', 'gsc_queries', 'stats_history', 'external_links', 'url_status'] as $suffix) {
            $from = $wpdb->prefix . 'jenga_' . $suffix;
            $to = $wpdb->prefix . 'lbraid_' . $suffix;
            if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($from)))
                && !$wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($to)))) {
                $wpdb->query("RENAME TABLE `$from` TO `$to`");
            }
        }

        // Options: the raw value is copied (no double serialization), keeping autoload.
        $options = $wpdb->get_results("SELECT option_name, option_value, autoload FROM {$wpdb->options} WHERE option_name LIKE 'jenga\\_%'");
        foreach ($options as $option) {
            $name = $option->option_name;
            $new_name = strpos($name, 'jenga_linker_') === 0 ? 'lbraid_' . substr($name, 13) : 'lbraid_' . substr($name, 6);
            $wpdb->query($wpdb->prepare(
                "INSERT IGNORE INTO {$wpdb->options} (option_name, option_value, autoload) VALUES (%s, %s, %s)",
                $new_name, $option->option_value, $option->autoload
            ));
            $wpdb->delete($wpdb->options, ['option_name' => $name]);
        }
        $wpdb->query("DELETE FROM {$wpdb->options} WHERE option_name LIKE '\\_jenga\\_lock\\_%'");
        wp_cache_flush();

        // Post meta: _jenga_* -> _lbraid_* (change history, scan flags, anchors, candidates).
        $wpdb->query("UPDATE {$wpdb->postmeta} SET meta_key = REPLACE(meta_key, '_jenga_', '_lbraid_') WHERE meta_key LIKE '\\_jenga\\_%'");

        wp_clear_scheduled_hook('jenga_linker_job_tick');
    }

    public static function create_tables() {
        global $wpdb;
        self::migrate_from_legacy();
        $charset = $wpdb->get_charset_collate();

        $sql = [];

        // Post index with embeddings
        $table_index = $wpdb->prefix . 'lbraid_post_index';
        $sql[] = "CREATE TABLE $table_index (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            post_id BIGINT(20) UNSIGNED NOT NULL,
            title VARCHAR(500) NOT NULL DEFAULT '',
            url VARCHAR(500) NOT NULL DEFAULT '',
            meta_title VARCHAR(500) NOT NULL DEFAULT '',
            keywords TEXT,
            embedding LONGTEXT,
            is_priority TINYINT(1) NOT NULL DEFAULT 0,
            priority_weight INT NOT NULL DEFAULT 5,
            last_indexed DATETIME NULL DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY post_id (post_id),
            KEY is_priority (is_priority)
        ) $charset;";

        // Suggested links (from LLM analysis)
        $table_suggestions = $wpdb->prefix . 'lbraid_link_suggestions';
        $sql[] = "CREATE TABLE $table_suggestions (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            source_post_id BIGINT(20) UNSIGNED NOT NULL,
            target_post_id BIGINT(20) UNSIGNED NOT NULL,
            anchor_text VARCHAR(500) NOT NULL DEFAULT '',
            context_snippet TEXT,
            confidence FLOAT NOT NULL DEFAULT 0,
            status VARCHAR(20) NOT NULL DEFAULT 'pending',
            suggested_sentence TEXT,
            is_new_content TINYINT(1) NOT NULL DEFAULT 0,
            custom_url VARCHAR(500) DEFAULT NULL,
            source_hash VARCHAR(64) NOT NULL DEFAULT '',
            reason TEXT,
            method VARCHAR(20) NOT NULL DEFAULT 'ai',
            model VARCHAR(96) NOT NULL DEFAULT '',
            target_term_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
            rule_id BIGINT(20) UNSIGNED NULL DEFAULT NULL,
            created_at DATETIME NULL DEFAULT NULL,
            PRIMARY KEY (id),
            KEY source_post_id (source_post_id),
            KEY target_post_id (target_post_id),
            KEY status (status)
        ) $charset;";

        // Existing links tracking
        $table_links = $wpdb->prefix . 'lbraid_existing_links';
        $sql[] = "CREATE TABLE $table_links (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            source_post_id BIGINT(20) UNSIGNED NOT NULL,
            target_post_id BIGINT(20) UNSIGNED NOT NULL,
            target_term_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
            anchor_text VARCHAR(500) NOT NULL DEFAULT '',
            location VARCHAR(500) NOT NULL DEFAULT '',
            detected_at DATETIME NULL DEFAULT NULL,
            PRIMARY KEY (id),
            KEY source_post_id (source_post_id),
            KEY target_post_id (target_post_id),
            KEY target_term_id (target_term_id)
        ) $charset;";

        // External links detected during the same scan pass (Domains + broken-link reports)
        $table_external = $wpdb->prefix . 'lbraid_external_links';
        $sql[] = "CREATE TABLE $table_external (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            source_post_id BIGINT(20) UNSIGNED NOT NULL,
            url VARCHAR(500) NOT NULL DEFAULT '',
            host VARCHAR(191) NOT NULL DEFAULT '',
            anchor_text VARCHAR(500) NOT NULL DEFAULT '',
            location VARCHAR(500) NOT NULL DEFAULT '',
            detected_at DATETIME NULL DEFAULT NULL,
            PRIMARY KEY (id),
            KEY source_post_id (source_post_id),
            KEY host (host)
        ) $charset;";

        // HTTP status of checked URLs (broken-link scanner)
        $table_url_status = $wpdb->prefix . 'lbraid_url_status';
        $sql[] = "CREATE TABLE $table_url_status (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            url VARCHAR(500) NOT NULL DEFAULT '',
            url_hash CHAR(32) NOT NULL DEFAULT '',
            host VARCHAR(191) NOT NULL DEFAULT '',
            status_code SMALLINT NOT NULL DEFAULT 0,
            ok TINYINT(1) NOT NULL DEFAULT 1,
            redirect_code SMALLINT NOT NULL DEFAULT 0,
            redirect_url VARCHAR(500) NOT NULL DEFAULT '',
            error_message VARCHAR(500) NOT NULL DEFAULT '',
            last_checked DATETIME NULL DEFAULT NULL,
            check_count INT NOT NULL DEFAULT 0,
            ignored TINYINT(1) NOT NULL DEFAULT 0,
            snoozed_until DATE NULL DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY url_hash (url_hash),
            KEY host (host)
        ) $charset;";

        // Autobraid: phrase -> target page rules (they generate ordinary suggestions)
        $table_rules = $wpdb->prefix . 'lbraid_autobraid_rules';
        $sql[] = "CREATE TABLE $table_rules (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            phrase VARCHAR(191) NOT NULL DEFAULT '',
            target_post_id BIGINT(20) UNSIGNED NOT NULL,
            enabled TINYINT(1) NOT NULL DEFAULT 1,
            flexible TINYINT(1) NOT NULL DEFAULT 0,
            created_at DATETIME NULL DEFAULT NULL,
            PRIMARY KEY (id),
            KEY target_post_id (target_post_id)
        ) $charset;";

        // Strands: topic clusters defined by the user
        $table_strands = $wpdb->prefix . 'lbraid_strands';
        $sql[] = "CREATE TABLE $table_strands (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            name VARCHAR(120) NOT NULL DEFAULT '',
            created_at DATETIME NULL DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY name (name)
        ) $charset;";

        $table_strand_posts = $wpdb->prefix . 'lbraid_strand_posts';
        $sql[] = "CREATE TABLE $table_strand_posts (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            strand_id BIGINT(20) UNSIGNED NOT NULL,
            post_id BIGINT(20) UNSIGNED NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY strand_post (strand_id, post_id),
            KEY post_id (post_id)
        ) $charset;";

        // Internal link clicks (deliberately without IP addresses and identifiers)
        $table_clicks = $wpdb->prefix . 'lbraid_click_data';
        $sql[] = "CREATE TABLE $table_clicks (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            clicked_at DATETIME NULL DEFAULT NULL,
            source_post_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
            target_post_id BIGINT(20) UNSIGNED NOT NULL,
            anchor_text VARCHAR(300) NOT NULL DEFAULT '',
            PRIMARY KEY (id),
            KEY target_post_id (target_post_id),
            KEY clicked_at (clicked_at)
        ) $charset;";

        // Daily stats snapshots for dashboard trends and the history chart
        $table_history = $wpdb->prefix . 'lbraid_stats_history';
        $sql[] = "CREATE TABLE $table_history (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            snapshot_date DATE NOT NULL,
            total_posts INT NOT NULL DEFAULT 0,
            indexed_posts INT NOT NULL DEFAULT 0,
            total_links INT NOT NULL DEFAULT 0,
            pending INT NOT NULL DEFAULT 0,
            accepted INT NOT NULL DEFAULT 0,
            priority INT NOT NULL DEFAULT 0,
            health_score INT NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            UNIQUE KEY snapshot_date (snapshot_date)
        ) $charset;";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        foreach ($sql as $query) {
            dbDelta($query);
        }

        self::create_gsc_table();
        $valid = true;
        foreach ([$table_index, $table_suggestions, $table_links, $table_history, $table_external, $table_url_status, $table_rules, $table_clicks, $table_strands, $table_strand_posts, $wpdb->prefix . 'lbraid_gsc_queries'] as $table) {
            if (!$wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($table)))) $valid = false;
        }
        if (!$wpdb->get_var("SHOW COLUMNS FROM $table_suggestions LIKE 'source_hash'")) $valid = false;
        if (!$wpdb->get_var("SHOW COLUMNS FROM $table_suggestions LIKE 'reason'")) $valid = false;
        if (!$wpdb->get_var("SHOW COLUMNS FROM $table_suggestions LIKE 'method'")) $valid = false;
        if (!$wpdb->get_var("SHOW COLUMNS FROM $table_links LIKE 'location'")) $valid = false;
        if ($valid) {
            self::repair_post_meta();
            update_option('lbraid_db_version', LINKBRAID_VERSION);
        }
    }

    /**
     * Post meta keys that hold exactly one value per post. Post-duplication
     * plugins copy them along with the post, and every further copy of a copy
     * inherits one more row - which multiplied rows in the content list.
     */
    const SINGLE_META = ['_lbraid_index_stale', '_lbraid_links_scanned', '_lbraid_embedding_error', '_lbraid_analysis', '_lbraid_priority_candidates'];

    /** Keeps the newest row of every single-value key; $post_id limits the repair to one post. */
    public static function repair_post_meta($post_id = 0) {
        global $wpdb;
        $in = implode(',', array_fill(0, count(self::SINGLE_META), '%s'));
        $params = self::SINGLE_META;
        $scope = '';
        if ($post_id) { $scope = ' AND post_id = %d'; $params[] = (int) $post_id; }
        $dups = $wpdb->get_results($wpdb->prepare("SELECT post_id, meta_key, MAX(meta_id) AS keep_id FROM {$wpdb->postmeta} WHERE meta_key IN ($in)$scope GROUP BY post_id, meta_key HAVING COUNT(*) > 1", ...$params));
        foreach ($dups as $d) {
            $wpdb->query($wpdb->prepare("DELETE FROM {$wpdb->postmeta} WHERE post_id = %d AND meta_key = %s AND meta_id <> %d", $d->post_id, $d->meta_key, $d->keep_id));
            wp_cache_delete((int) $d->post_id, 'post_meta');
        }
        return count($dups);
    }

    public static function get_post_index($post_id) {
        global $wpdb;
        $table = $wpdb->prefix . 'lbraid_post_index';
        return $wpdb->get_row($wpdb->prepare("SELECT * FROM $table WHERE post_id = %d", $post_id));
    }

    public static function upsert_post_index($data) {
        global $wpdb;
        $table = $wpdb->prefix . 'lbraid_post_index';
        
        $exists = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM $table WHERE post_id = %d", $data['post_id']
        ));

        if ($exists) {
            return $wpdb->update($table, $data, ['post_id' => $data['post_id']]);
        } else {
            return $wpdb->insert($table, $data);
        }
    }

    public static function get_all_indexed_posts() {
        global $wpdb;
        $table = $wpdb->prefix . 'lbraid_post_index';
        return $wpdb->get_results("SELECT i.* FROM $table i INNER JOIN {$wpdb->posts} p ON p.ID=i.post_id WHERE p.post_status='publish' ORDER BY is_priority DESC, post_id ASC");
    }

    public static function get_priority_posts() {
        global $wpdb;
        $table = $wpdb->prefix . 'lbraid_post_index';
        return $wpdb->get_results("SELECT i.* FROM $table i INNER JOIN {$wpdb->posts} p ON p.ID=i.post_id WHERE is_priority = 1 AND p.post_status='publish' ORDER BY priority_weight DESC");
    }

    public static function save_suggestion($data) {
        global $wpdb;
        $table = $wpdb->prefix . 'lbraid_link_suggestions';
        // Timestamps are set here, not by a column default: DATETIME DEFAULT
        // CURRENT_TIMESTAMP needs MySQL 5.6.5+, which WordPress does not require.
        if (empty($data['created_at'])) $data['created_at'] = current_time('mysql');
        return $wpdb->insert($table, $data);
    }

    public static function get_suggestions_for_post($post_id) {
        global $wpdb;
        $table = $wpdb->prefix . 'lbraid_link_suggestions';
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT s.*, p.post_title as target_title, p.ID as target_id
             FROM $table s 
             LEFT JOIN {$wpdb->posts} p ON s.target_post_id = p.ID 
             WHERE s.source_post_id = %d AND s.status = 'pending'
             ORDER BY s.confidence DESC",
            $post_id
        ));

        // Add proper permalink for each suggestion
        foreach ($results as &$r) {
            $r->target_url = !empty($r->custom_url) ? $r->custom_url : get_permalink($r->target_post_id);
            if (!empty($r->target_term_id)) {
                $term = Linkbraid_Terms::get($r->target_term_id);
                if ($term) { $r->target_title = $term['name']; if (empty($r->target_url)) $r->target_url = $term['url']; }
            }
        }

        return $results;
    }

    public static function get_all_pending_suggestions() {
        global $wpdb;
        $table = $wpdb->prefix . 'lbraid_link_suggestions';
        $results = $wpdb->get_results(
            "SELECT s.*,
                    sp.post_title as source_title,
                    tp.post_title as target_title
             FROM $table s
             LEFT JOIN {$wpdb->posts} sp ON s.source_post_id = sp.ID
             LEFT JOIN {$wpdb->posts} tp ON s.target_post_id = tp.ID
             WHERE s.status = 'pending'
             ORDER BY s.confidence DESC"
        );
        foreach ($results as &$r) {
            if (!empty($r->target_term_id)) {
                $term = Linkbraid_Terms::get($r->target_term_id);
                if ($term) $r->target_title = $term['name'];
            }
        }
        return $results;
    }

    public static function update_suggestion_status($id, $status) {
        global $wpdb;
        $table = $wpdb->prefix . 'lbraid_link_suggestions';
        return $wpdb->update($table, ['status' => $status], ['id' => $id]);
    }

    public static function clear_suggestions_for_post($post_id) {
        global $wpdb;
        $table = $wpdb->prefix . 'lbraid_link_suggestions';
        return $wpdb->delete($table, ['source_post_id' => $post_id, 'status' => 'pending']);
    }

    public static function track_existing_link($source_id, $target_id, $anchor) {
        global $wpdb;
        $table = $wpdb->prefix . 'lbraid_existing_links';
        
        // Check if already tracked
        $exists = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM $table WHERE source_post_id = %d AND target_post_id = %d AND anchor_text = %s",
            $source_id, $target_id, $anchor
        ));

        if (!$exists) {
            $wpdb->insert($table, [
                'source_post_id' => $source_id,
                'target_post_id' => $target_id,
                'anchor_text' => $anchor,
                'detected_at' => current_time('mysql'),
            ]);
        }
    }

    public static function get_link_count($post_id, $direction = 'outgoing') {
        global $wpdb;
        $table = $wpdb->prefix . 'lbraid_existing_links';
        $col = $direction === 'outgoing' ? 'source_post_id' : 'target_post_id';
        return (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM $table WHERE $col = %d", $post_id
        ));
    }

    public static function get_all_links() {
        global $wpdb;
        $table = $wpdb->prefix . 'lbraid_existing_links';
        return $wpdb->get_results(
            "SELECT l.*, 
                    sp.post_title as source_title,
                    tp.post_title as target_title
             FROM $table l
             LEFT JOIN {$wpdb->posts} sp ON l.source_post_id = sp.ID
             LEFT JOIN {$wpdb->posts} tp ON l.target_post_id = tp.ID
             ORDER BY l.source_post_id"
        );
    }

    public static function refresh_existing_links($post_id) {
        global $wpdb;
        if (!class_exists('DOMDocument')) return false;
        $post = get_post($post_id);
        if (!$post) return false;
        $rows = []; $external = [];
        $classify = function ($html, $location) use ($post_id, &$rows, &$external) {
            foreach (Linkbraid_Content::links_all($html) as $link) {
                if (empty($link['internal'])) {
                    $external[] = ['source_post_id' => $post_id, 'url' => mb_substr($link['url'], 0, 500), 'host' => mb_substr($link['host'], 0, 191), 'anchor_text' => mb_substr($link['anchor'], 0, 500), 'location' => $location];
                    continue;
                }
                $target = (int) url_to_postid($link['url']);
                if (!$target) {
                    // Internal URL without a post behind it: a taxonomy archive counts too.
                    $term = Linkbraid_Terms::resolve($link['url']);
                    if ($term) { $rows[] = ['source_post_id' => $post_id, 'target_post_id' => 0, 'target_term_id' => (int) $term['term_id'], 'anchor_text' => mb_substr($link['anchor'], 0, 500), 'location' => $location]; continue; }
                    // Anything else is usually an old slug that 301-redirects. It joins
                    // the URL checker (own host), so the Redirects report can catch it.
                    $external[] = ['source_post_id' => $post_id, 'url' => mb_substr($link['url'], 0, 500), 'host' => mb_substr((string) $link['host'], 0, 191), 'anchor_text' => mb_substr($link['anchor'], 0, 500), 'location' => $location];
                    continue;
                }
                if ($target === (int) $post_id || get_post_status($target) !== 'publish') continue;
                if (get_option('lbraid_exclude_home', 1) && $target === (int) get_option('page_on_front')) continue;
                $rows[] = ['source_post_id' => $post_id, 'target_post_id' => $target, 'anchor_text' => mb_substr($link['anchor'], 0, 500), 'location' => $location];
            }
        };
        foreach (Linkbraid_Content::fragments($post_id) as $fragment) $classify($fragment['html'], $fragment['location']);
        // ACF text carriers count too (read-only: links are detected, not written).
        foreach (Linkbraid_Content::acf_fragments($post_id) as $fragment) $classify($fragment['html'], $fragment['location']);
        $table = $wpdb->prefix . 'lbraid_existing_links';
        $table_external = $wpdb->prefix . 'lbraid_external_links';
        // detected_at is stamped in PHP (no CURRENT_TIMESTAMP column default - MySQL < 5.6.5).
        $now = current_time('mysql');
        foreach ($rows as &$row) $row['detected_at'] = $now;
        unset($row);
        foreach ($external as &$row) $row['detected_at'] = $now;
        unset($row);
        $wpdb->query('START TRANSACTION');
        $ok = false !== $wpdb->delete($table, ['source_post_id' => $post_id]);
        foreach ($rows as $row) if (false === $wpdb->insert($table, $row)) $ok = false;
        if (false === $wpdb->delete($table_external, ['source_post_id' => $post_id])) $ok = false;
        foreach ($external as $row) if (false === $wpdb->insert($table_external, $row)) $ok = false;
        $wpdb->query($ok ? 'COMMIT' : 'ROLLBACK');
        if ($ok) {
            update_post_meta($post_id, '_lbraid_links_scanned', current_time('mysql'));
            // The Braid Map draws its nodes from the post index. A plain scan must
            // be enough to put content on the map — Lite has no embedding step —
            // so ensure a row exists, without touching embeddings or priorities.
            if ($post->post_status === 'publish' && in_array($post->post_type, lbraid_post_types(), true) && !self::get_post_index($post_id)) {
                self::upsert_post_index([
                    'post_id' => $post_id,
                    'title'   => mb_substr(html_entity_decode(get_the_title($post_id), ENT_QUOTES | ENT_HTML5, 'UTF-8'), 0, 500),
                    'url'     => mb_substr((string) get_permalink($post_id), 0, 500),
                ]);
            }
        }
        return $ok;
    }

    public static function replace_suggestions($post_id, $suggestions) {
        global $wpdb;
        $table = $wpdb->prefix . 'lbraid_link_suggestions';
        // Insert first. Even on non-transactional legacy tables an insert failure preserves old results.
        $old = $wpdb->get_col($wpdb->prepare("SELECT id FROM $table WHERE source_post_id=%d AND status='pending'", $post_id));
        $inserted = [];
        foreach ($suggestions as $s) {
            if (false === self::save_suggestion($s)) {
                foreach ($inserted as $id) $wpdb->delete($table, ['id' => $id]);
                return false;
            }
            $inserted[] = $wpdb->insert_id;
        }
        foreach ($old as $id) if (false === $wpdb->delete($table, ['id' => $id, 'status' => 'pending'])) return false;
        return true;
    }

    public static function get_orphan_posts() {
        global $wpdb;
        $links_table = $wpdb->prefix . 'lbraid_existing_links';
        
        $types = lbraid_post_types();
        $placeholders = implode(',', array_fill(0, count($types), '%s'));
        
        $query = $wpdb->prepare(
            "SELECT p.ID, p.post_title, p.post_name,
                    COALESCE(outgoing.cnt, 0) as outgoing_links,
                    COALESCE(incoming.cnt, 0) as incoming_links
             FROM {$wpdb->posts} p
             LEFT JOIN (SELECT source_post_id, COUNT(*) as cnt FROM $links_table GROUP BY source_post_id) outgoing ON p.ID = outgoing.source_post_id
             LEFT JOIN (SELECT target_post_id, COUNT(*) as cnt FROM $links_table GROUP BY target_post_id) incoming ON p.ID = incoming.target_post_id
             WHERE p.post_status = 'publish' AND p.post_type IN ($placeholders)
             HAVING incoming_links = 0
             ORDER BY incoming_links ASC, outgoing_links ASC",
            ...$types
        );
        
        return $wpdb->get_results($query);
    }

    // =============================
    // GSC Data Methods
    // =============================

    public static function create_gsc_table() {
        global $wpdb;
        $charset = $wpdb->get_charset_collate();
        $table = $wpdb->prefix . 'lbraid_gsc_queries';

        $sql = "CREATE TABLE $table (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            post_id BIGINT(20) UNSIGNED NOT NULL,
            query VARCHAR(500) NOT NULL,
            clicks INT NOT NULL DEFAULT 0,
            impressions INT NOT NULL DEFAULT 0,
            ctr FLOAT NOT NULL DEFAULT 0,
            position FLOAT NOT NULL DEFAULT 0,
            synced_at DATETIME NULL DEFAULT NULL,
            PRIMARY KEY (id),
            KEY post_id (post_id),
            KEY query (query(191)),
            KEY position (position)
        ) $charset;";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta($sql);
    }

    public static function save_gsc_queries($post_id, $queries) {
        global $wpdb;
        $table = $wpdb->prefix . 'lbraid_gsc_queries';

        // Clear old data for this post
        $wpdb->delete($table, ['post_id' => $post_id]);

        foreach ($queries as $q) {
            $wpdb->insert($table, [
                'post_id'     => $post_id,
                'query'       => $q['query'],
                'clicks'      => $q['clicks'] ?? 0,
                'impressions' => $q['impressions'] ?? 0,
                'ctr'         => $q['ctr'] ?? 0,
                'position'    => $q['position'] ?? 0,
                'synced_at'   => current_time('mysql'),
            ]);
        }
    }

    public static function get_gsc_queries_for_post($post_id, $limit = 10) {
        global $wpdb;
        $table = $wpdb->prefix . 'lbraid_gsc_queries';
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM $table WHERE post_id = %d ORDER BY impressions DESC LIMIT %d",
            $post_id, $limit
        ), ARRAY_A);
    }

    public static function get_all_gsc_queries() {
        global $wpdb;
        $table = $wpdb->prefix . 'lbraid_gsc_queries';
        
        return $wpdb->get_results(
            "SELECT q.*, p.post_title 
             FROM $table q
             LEFT JOIN {$wpdb->posts} p ON q.post_id = p.ID
             ORDER BY q.impressions DESC"
        );
    }
}