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

/**
 * Semantic cohesion score calculator.
 * Full port of the calculate-semantic-cohesion Supabase edge function.
 *
 * Score (0-100):
 *   Connectivity   35%  — % pages connected to ≥1 other page via shared entities
 *   Clusters       25%  — % pages in meaningful topic clusters
 *   Depth          20%  — avg entity relationships / max, normalised
 *   Core Alignment 20%  — % pages containing ≥1 top entity
 */
class Semantic_Cohesion {

    const OVERLAP_THRESHOLD   = 1.5;
    // Minimum IDF-weighted overlap for the Topic Clusters *visualisation* to draw
    // an edge. Lower than OVERLAP_THRESHOLD because the viz wants to keep genuine
    // intra-domain links while still rejecting single cross-cutting bridge entities.
    const CLUSTER_EDGE_THRESHOLD = 1.0;
    const HUB_ENTITY_THRESHOLD= 0.8;
    const SALIENCE_THRESHOLD  = 0.3;
    const MAX_PAGES_PER_ENTITY= 500;

    const PREFERRED_TYPES = [
        'organization', 'product', 'technology', 'person',
        'concept', 'industry', 'event',
    ];

    const GENERIC_TYPES = [
        'place', 'location', 'phenomenon', 'trend',
    ];

    public function calculate() {
        global $wpdb;

        // --- Total eligible pages ---
        $post_types = Semantic_Settings::post_types();
        if ( empty( $post_types ) ) {
            return $this->empty_snapshot();
        }
        $ph = implode( ',', array_fill( 0, count( $post_types ), '%s' ) );
        $all_post_ids = $wpdb->get_col( $wpdb->prepare(
            "SELECT ID FROM {$wpdb->posts} WHERE post_status = 'publish' AND post_type IN ($ph)
             AND ID NOT IN ( SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key = %s AND meta_value = '1' )",
            array_merge( $post_types, [ Semantic_Settings::EXCLUDE_META ] )
        ) );
        $total_content_pages = count( $all_post_ids );

        if ( $total_content_pages === 0 ) {
            return $this->empty_snapshot();
        }

        // --- Completed analyses (latest per post only — ignore superseded duplicates) ---
        $analyses = $wpdb->get_results(
            "SELECT id, post_id, entity_relationships FROM {$wpdb->prefix}semantic_analyses
             WHERE status = 'completed'
             ORDER BY id DESC",
            ARRAY_A
        );

        $analysis_id_map = []; // post_id → latest analysis row
        foreach ( $analyses as $a ) {
            if ( ! isset( $analysis_id_map[ $a['post_id'] ] ) ) {
                $analysis_id_map[ $a['post_id'] ] = $a; // first seen = highest id (latest)
            }
        }

        // De-duplicate: only the latest analysis per post counts
        $analyses           = array_values( $analysis_id_map );
        $completed_analyses = count( $analyses ); // now == distinct analysed posts
        $analysis_ids       = array_column( $analyses, 'id' );

        // --- Approved entities ---
        $all_entities = [];
        if ( ! empty( $analysis_ids ) ) {
            $in_ids = implode( ',', array_map( 'intval', $analysis_ids ) );
            $all_entities = $wpdb->get_results(
                "SELECT se.id, se.canonical_entity_id, se.name, se.type, se.salience, se.analysis_id,
                        sa.post_id
                 FROM {$wpdb->prefix}semantic_suggested_entities se
                 JOIN {$wpdb->prefix}semantic_analyses sa ON sa.id = se.analysis_id
                 WHERE se.approval_status = 'approved'
                   AND se.analysis_id IN ({$in_ids})",
                ARRAY_A
            );
        }

        // --- Filter entities ---
        $filtered = array_filter( $all_entities, function( $e ) {
            if ( $e['salience'] < self::SALIENCE_THRESHOLD ) return false;
            if ( in_array( $e['type'], self::GENERIC_TYPES, true ) && $e['salience'] < 0.5 ) return false;
            return true;
        } );

        // --- Entity frequency map (canonical_entity_id → set of post_ids) ---
        $entity_freq     = []; // entity_key → [ post_id, ... ]
        $entity_metadata = []; // entity_key → { displayName, type, avgSalience, count }

        foreach ( $filtered as $e ) {
            $key = $e['canonical_entity_id'] ?: $e['id'];
            $pid = $e['post_id'];

            if ( ! isset( $entity_freq[ $key ] ) ) $entity_freq[ $key ] = [];
            $entity_freq[ $key ][ $pid ] = true;

            if ( ! isset( $entity_metadata[ $key ] ) ) {
                $entity_metadata[ $key ] = [
                    'displayName' => $e['name'],
                    'type'        => $e['type'],
                    'avgSalience' => (float) $e['salience'],
                    'count'       => 1,
                ];
            } else {
                $meta = &$entity_metadata[ $key ];
                $meta['count']++;
                $meta['avgSalience'] = ( $meta['avgSalience'] * ( $meta['count'] - 1 ) + (float) $e['salience'] ) / $meta['count'];
                unset( $meta );
            }
        }

        // --- IDF + hub detection ---
        $entity_idf  = [];
        $hub_entities = [];
        foreach ( $entity_freq as $key => $page_set ) {
            $df        = count( $page_set );
            $frequency = $df / $total_content_pages;
            if ( $frequency > self::HUB_ENTITY_THRESHOLD ) {
                $hub_entities[ $key ] = true;
                $entity_idf[ $key ]   = 0.1;
            } else {
                $entity_idf[ $key ] = log( $total_content_pages / ( $df + 1 ) );
            }
        }

        // --- Inverted index (entity → pages), excluding hubs ---
        $inverted = [];
        foreach ( $filtered as $e ) {
            $key = $e['canonical_entity_id'] ?: $e['id'];
            if ( isset( $hub_entities[ $key ] ) ) continue;
            if ( count( $entity_freq[ $key ] ) > self::MAX_PAGES_PER_ENTITY ) continue;
            $inverted[ $key ][ $e['post_id'] ] = true;
        }

        // --- Weighted page-page overlaps ---
        $page_overlaps = []; // page1 → page2 → score
        foreach ( $inverted as $key => $page_set ) {
            $pages = array_keys( $page_set );
            $idf   = $entity_idf[ $key ] ?? 0;
            for ( $i = 0; $i < count( $pages ); $i++ ) {
                for ( $j = $i + 1; $j < count( $pages ); $j++ ) {
                    $p1 = $pages[$i];
                    $p2 = $pages[$j];
                    $page_overlaps[ $p1 ][ $p2 ] = ( $page_overlaps[ $p1 ][ $p2 ] ?? 0 ) + $idf;
                    $page_overlaps[ $p2 ][ $p1 ] = ( $page_overlaps[ $p2 ][ $p1 ] ?? 0 ) + $idf;
                }
            }
        }

        // --- Connectivity ---
        $connected_pages = [];
        $page_graph      = []; // adjacency list for clustering
        foreach ( $page_overlaps as $p1 => $neighbors ) {
            foreach ( $neighbors as $p2 => $overlap ) {
                if ( $overlap >= self::OVERLAP_THRESHOLD ) {
                    $connected_pages[ $p1 ] = true;
                    $connected_pages[ $p2 ] = true;
                    $page_graph[ $p1 ][ $p2 ] = true;
                    $page_graph[ $p2 ][ $p1 ] = true;
                }
            }
        }
        $connectivity_count = count( $connected_pages );
        $connectivity       = $total_content_pages > 0
            ? (int) round( ( $connectivity_count / $total_content_pages ) * 100 )
            : 0;

        // --- Connected components (clusters) via iterative DFS ---
        // An explicit stack (rather than recursion) so a large, densely-connected
        // page graph cannot exhaust PHP's call stack / memory on big sites. Each
        // unvisited page seeds a new component; we drain its reachable set before
        // moving on. Connected-component membership is order-independent, so the
        // resulting cluster_map / cluster_sizes match the previous recursive walk.
        $visited      = [];
        $cluster_map  = []; // post_id → cluster_id
        $cluster_sizes= [];
        $cluster_id   = 0;

        foreach ( array_keys( $page_graph ) as $start ) {
            if ( isset( $visited[ $start ] ) ) continue;

            $cid   = $cluster_id;
            $stack = [ $start ];
            while ( $stack ) {
                $page = array_pop( $stack );
                if ( isset( $visited[ $page ] ) ) continue;

                $visited[ $page ]      = true;
                $cluster_map[ $page ]  = $cid;
                $cluster_sizes[ $cid ] = ( $cluster_sizes[ $cid ] ?? 0 ) + 1;

                foreach ( array_keys( $page_graph[ $page ] ?? [] ) as $neighbor ) {
                    if ( ! isset( $visited[ $neighbor ] ) ) $stack[] = $neighbor;
                }
            }
            $cluster_id++;
        }

        $min_cluster_size = $total_content_pages < 10 ? 2 : 3;
        $pages_in_clusters = 0;
        foreach ( $cluster_map as $page => $cid ) {
            if ( ( $cluster_sizes[ $cid ] ?? 0 ) >= $min_cluster_size ) {
                $pages_in_clusters++;
            }
        }
        $cluster_count       = count( array_filter( $cluster_sizes, fn( $s ) => $s >= $min_cluster_size ) );
        $largest_cluster_size= empty( $cluster_sizes ) ? 0 : max( $cluster_sizes );
        $clusters            = $total_content_pages > 0
            ? (int) round( ( $pages_in_clusters / $total_content_pages ) * 100 )
            : 0;

        // --- Relationship depth ---
        $rel_counts = [];
        foreach ( $all_post_ids as $pid ) {
            $a = $analysis_id_map[ $pid ] ?? null;
            $rels = $a ? ( json_decode( $a['entity_relationships'] ?? '[]', true ) ?: [] ) : [];
            $rel_counts[] = count( $rels );
        }
        sort( $rel_counts );
        $avg_rels  = count( $rel_counts ) > 0 ? array_sum( $rel_counts ) / count( $rel_counts ) : 0;
        $max_rels  = count( $rel_counts ) > 0 ? max( $rel_counts ) : 1;
        $depth     = $max_rels > 0 ? (int) round( ( $avg_rels / $max_rels ) * 100 ) : 0;

        // --- Core topic alignment ---
        $entity_scores = [];
        foreach ( $entity_freq as $key => $page_set ) {
            if ( isset( $hub_entities[ $key ] ) ) continue;
            $idf = $entity_idf[ $key ] ?? 0;
            $entity_scores[ $key ] = count( $page_set ) * $idf;
        }
        arsort( $entity_scores );
        $top_entity_count = $total_content_pages < 10 ? 5 : 10;
        $top_entities     = array_slice( array_keys( $entity_scores ), 0, $top_entity_count );

        $aligned_pages = [];
        foreach ( $filtered as $e ) {
            $key = $e['canonical_entity_id'] ?: $e['id'];
            if ( in_array( $key, $top_entities, true ) ) {
                $aligned_pages[ $e['post_id'] ] = true;
            }
        }
        $core_alignment = $total_content_pages > 0
            ? (int) round( ( count( $aligned_pages ) / $total_content_pages ) * 100 )
            : 0;

        // --- Composite score ---
        $connectivity_score = (int) round( $connectivity   * 0.35 );
        $clusters_score     = (int) round( $clusters       * 0.25 );
        $depth_score        = (int) round( $depth          * 0.20 );
        $alignment_score    = (int) round( $core_alignment * 0.20 );
        $score              = $connectivity_score + $clusters_score + $depth_score + $alignment_score;

        $coverage = $total_content_pages > 0
            ? (int) round( ( $completed_analyses / $total_content_pages ) * 100 )
            : 0;

        // --- Dominant entities ---
        $dominant = [];
        foreach ( array_slice( $top_entities, 0, 5 ) as $key ) {
            $meta = $entity_metadata[ $key ] ?? null;
            $dominant[] = [
                'id'        => $key,
                'name'      => $meta['displayName'] ?? 'Unknown',
                'frequency' => count( $entity_freq[ $key ] ?? [] ),
            ];
        }

        // --- Actions (enriched with specific pages + entity suggestions for the UI) ---
        $isolated_count = $total_content_pages - $connectivity_count;
        $actions        = [];

        // Top dominant entity names — used as concrete "write about these" suggestions
        $dominant_names = array_values( array_filter( array_map(
            fn( $d ) => $d['name'] ?? null, $dominant
        ) ) );

        // Identify the specific analysed-but-unconnected pages (the actionable "isolated" set)
        $isolated_pages = [];
        foreach ( array_keys( $analysis_id_map ) as $pid ) {
            if ( isset( $connected_pages[ $pid ] ) ) continue;
            $title = get_the_title( $pid ) ?: "Post {$pid}";
            // Don't nag users to interlink boilerplate/utility pages (privacy,
            // sample, hello-world, cart, etc.) — they don't belong in topic clusters.
            if ( self::is_low_value_page( $title, get_permalink( $pid ) ) ) continue;
            $isolated_pages[] = [ 'id' => (int) $pid, 'title' => $title ];
        }

        if ( ! empty( $isolated_pages ) ) {
            $actions[] = [
                'action'      => 'connect_pages',
                'count'       => count( $isolated_pages ),
                'description' => 'Connect ' . count( $isolated_pages ) . ' isolated page' . ( count( $isolated_pages ) !== 1 ? 's' : '' ) . ' to your core topics',
                'pages'       => $isolated_pages,
                'entities'    => array_slice( $dominant_names, 0, 5 ),
            ];
        }
        if ( $cluster_count > 0 && $largest_cluster_size < 5 ) {
            $actions[] = [
                'action'      => 'strengthen_cluster',
                'count'       => $cluster_count,
                'description' => 'Strengthen ' . $cluster_count . ' topic cluster' . ( $cluster_count !== 1 ? 's' : '' ),
                'entities'    => array_slice( $dominant_names, 0, 5 ),
            ];
        }

        $snapshot = [
            'score'                => $score,
            'coverage'             => $coverage,
            'connectivity'         => $connectivity,
            'clusters'             => $clusters,
            'depth'                => $depth,
            'core_alignment'       => $core_alignment,
            'total_content_pages'  => $total_content_pages,
            'completed_analyses'   => $completed_analyses,
            'dominant_entities'    => $dominant,
            'largest_cluster_size' => $largest_cluster_size,
            'isolated_pages_count' => $isolated_count,
            'cluster_count'        => $cluster_count,
            'recommended_actions'  => $actions,
            'score_components'     => [
                'connectivity' => $connectivity_score,
                'clusters'     => $clusters_score,
                'depth'        => $depth_score,
                'alignment'    => $alignment_score,
            ],
            'computed_at'          => current_time( 'mysql' ),
            'version'              => 'v1',
        ];

        // Store snapshot
        global $wpdb;
        $wpdb->insert( $wpdb->prefix . 'semantic_cohesion', [
            'score'                => $snapshot['score'],
            'coverage'             => $snapshot['coverage'],
            'connectivity'         => $snapshot['connectivity'],
            'clusters'             => $snapshot['clusters'],
            'depth'                => $snapshot['depth'],
            'core_alignment'       => $snapshot['core_alignment'],
            'total_content_pages'  => $snapshot['total_content_pages'],
            'completed_analyses'   => $snapshot['completed_analyses'],
            'dominant_entities'    => wp_json_encode( $snapshot['dominant_entities'] ),
            'largest_cluster_size' => $snapshot['largest_cluster_size'],
            'isolated_pages_count' => $snapshot['isolated_pages_count'],
            'cluster_count'        => $snapshot['cluster_count'],
            'recommended_actions'  => wp_json_encode( $snapshot['recommended_actions'] ),
            'score_components'     => wp_json_encode( $snapshot['score_components'] ),
            'computed_at'          => $snapshot['computed_at'],
        ] );

        return $snapshot;
    }

    /**
     * Build a cluster graph for visualisation.
     * Returns clusters (with a derived topic label), the posts within each,
     * and the shared-entity edges between posts.
     */
    public static function cluster_graph() {
        global $wpdb;

        // Scope to publicly-queryable tracked types. This graph powers the public
        // MCP site overview and the related-pages feature, so it must only reflect
        // currently published, public, password-free, non-excluded posts — never
        // drafts/private/trashed content.
        $post_types = Semantic_Settings::public_post_types();
        if ( empty( $post_types ) ) return [ 'clusters' => [], 'posts' => [], 'edges' => [] ];

        $ph          = implode( ',', array_fill( 0, count( $post_types ), '%s' ) );
        $public_args = array_merge( $post_types, [ Semantic_Settings::EXCLUDE_META ] );
        // Reusable predicate (post alias `p`) keeping only public, live posts.
        $public_where = "p.post_status = 'publish' AND p.post_password = '' AND p.post_type IN ($ph)
                         AND p.ID NOT IN ( SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key = %s AND meta_value = '1' )";

        // Approved entities joined to their post + analysis (published posts only)
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT se.canonical_entity_id, se.name, se.type, se.salience, sa.post_id
             FROM {$wpdb->prefix}semantic_suggested_entities se
             JOIN {$wpdb->prefix}semantic_analyses sa ON sa.id = se.analysis_id
             JOIN {$wpdb->posts} p ON p.ID = sa.post_id
             WHERE se.approval_status = 'approved' AND sa.status = 'completed' AND {$public_where}",
            $public_args
        ), ARRAY_A );
        if ( empty( $rows ) ) return [ 'clusters' => [], 'posts' => [], 'edges' => [] ];

        // post → set of entity keys; entity key → display name/type; entity → set of posts
        $post_entities = [];
        $entity_name   = [];
        $entity_type   = [];
        $entity_salience = []; // key → max salience seen (for label weighting)
        $entity_posts  = [];
        foreach ( $rows as $r ) {
            if ( (float) $r['salience'] < self::SALIENCE_THRESHOLD ) continue;
            $key = $r['canonical_entity_id'] ?: strtolower( $r['name'] );
            $pid = (int) $r['post_id'];
            $post_entities[ $pid ][ $key ] = true;
            $entity_posts[ $key ][ $pid ]  = true;
            if ( ! isset( $entity_name[ $key ] ) ) $entity_name[ $key ] = $r['name'];
            $entity_type[ $key ] = $r['type'];
            $entity_salience[ $key ] = max( $entity_salience[ $key ] ?? 0, (float) $r['salience'] );
        }

        // Pull each post's primary topic entity — the strongest signal for a cluster's
        // real subject (published posts only, matching the entity query above).
        $primary_topics = [];
        $pt_rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT sa.post_id, sa.primary_topic_entity
             FROM {$wpdb->prefix}semantic_analyses sa
             JOIN {$wpdb->posts} p ON p.ID = sa.post_id
             WHERE sa.status = 'completed' AND sa.primary_topic_entity <> '' AND {$public_where}",
            $public_args
        ), ARRAY_A );
        foreach ( $pt_rows as $pt ) $primary_topics[ (int) $pt['post_id'] ] = $pt['primary_topic_entity'];

        $post_ids = array_keys( $post_entities );
        $total    = count( $post_ids );
        if ( $total === 0 ) return [ 'clusters' => [], 'posts' => [], 'edges' => [] ];

        // ── Hub detection + IDF weighting ────────────────────────────────────
        // Entities on a large share of pages are too generic to link on. For
        // small corpora we tighten the threshold (a cross-cutting term on >60%
        // of pages would otherwise merge everything). Remaining entities get an
        // IDF weight so rare, specific entities dominate edge strength.
        $hub_threshold = $total < 12 ? 0.6 : self::HUB_ENTITY_THRESHOLD;
        $hub = [];
        $idf = [];
        foreach ( $entity_posts as $key => $pset ) {
            $df = count( $pset );
            if ( $df / $total > $hub_threshold ) { $hub[ $key ] = true; $idf[ $key ] = 0; }
            else { $idf[ $key ] = log( $total / ( $df + 1 ) ); }
        }

        // ── Weighted page-page edges ─────────────────────────────────────────
        // Edge weight = Σ IDF of shared (non-hub) entities. A single shared
        // generic entity no longer links two posts; only a meaningful overlap
        // (a rare entity, or several shared entities) clears the threshold.
        $raw_edges = []; // ek → ['source','target','shared'=>[], 'weight'=>float]
        foreach ( $entity_posts as $key => $pset ) {
            if ( isset( $hub[ $key ] ) ) continue;
            $w = $idf[ $key ] ?? 0;
            if ( $w <= 0 ) continue;
            $pages = array_keys( $pset );
            for ( $i = 0; $i < count( $pages ); $i++ ) {
                for ( $j = $i + 1; $j < count( $pages ); $j++ ) {
                    $a = (int) $pages[$i]; $b = (int) $pages[$j];
                    $lo = min( $a, $b ); $hi = max( $a, $b );
                    $ek = "$lo-$hi";
                    if ( ! isset( $raw_edges[ $ek ] ) ) {
                        $raw_edges[ $ek ] = [ 'source' => $lo, 'target' => $hi, 'shared' => [], 'weight' => 0 ];
                    }
                    $raw_edges[ $ek ]['shared'][] = $entity_name[ $key ];
                    $raw_edges[ $ek ]['weight']  += $w;
                }
            }
        }

        // Keep only edges above the overlap threshold; build weighted adjacency.
        $edges     = [];
        $adjacency = [];
        foreach ( $raw_edges as $e ) {
            if ( $e['weight'] < self::CLUSTER_EDGE_THRESHOLD ) continue;
            $edges[] = [ 'source' => $e['source'], 'target' => $e['target'], 'shared' => $e['shared'] ];
            $adjacency[ $e['source'] ][ $e['target'] ] = $e['weight'];
            $adjacency[ $e['target'] ][ $e['source'] ] = $e['weight'];
        }

        // ── Community detection (Louvain) ────────────────────────────────────
        // Plain connected components collapse everything once a single bridge
        // edge survives. Louvain modularity finds dense communities even within
        // a connected graph, so cross-domain bridges no longer merge distinct
        // topics. Isolated posts fall out as their own singleton communities.
        $cluster_of = self::detect_communities( $post_ids, $adjacency );

        // Build cluster → posts, plus a weighted score per entity for labelling
        $cluster_posts        = [];
        $cluster_entity_score = []; // cluster → entity key → weighted score
        $cluster_topic_votes  = []; // cluster → primary_topic string → count
        foreach ( $cluster_of as $pid => $c ) {
            $cluster_posts[ $c ][] = $pid;

            // Vote for this post's primary topic (strongest label signal)
            if ( ! empty( $primary_topics[ $pid ] ) ) {
                $t = $primary_topics[ $pid ];
                $cluster_topic_votes[ $c ][ $t ] = ( $cluster_topic_votes[ $c ][ $t ] ?? 0 ) + 1;
            }

            // Weighted entity scoring as fallback
            foreach ( array_keys( $post_entities[ $pid ] ) as $key ) {
                if ( isset( $hub[ $key ] ) ) continue; // skip near-universal entities
                $type   = $entity_type[ $key ] ?? 'concept';
                // Prefer domain-topic types over cross-cutting technologies for the label
                $type_weight = in_array( $type, [ 'industry', 'concept', 'product', 'organization', 'methodology' ], true ) ? 1.5
                             : ( in_array( $type, [ 'technology', 'emerging_technology' ], true ) ? 0.6 : 1.0 );
                $score = ( $entity_salience[ $key ] ?? 0.5 ) * $type_weight;
                $cluster_entity_score[ $c ][ $key ] = ( $cluster_entity_score[ $c ][ $key ] ?? 0 ) + $score;
            }
        }

        $clusters = [];
        foreach ( $cluster_posts as $c => $pids ) {
            $label = 'Misc';
            // 1) Prefer the most common primary topic across the cluster's posts
            if ( ! empty( $cluster_topic_votes[ $c ] ) ) {
                arsort( $cluster_topic_votes[ $c ] );
                $label = array_key_first( $cluster_topic_votes[ $c ] );
            }
            // 2) Fall back to the highest weighted entity
            elseif ( ! empty( $cluster_entity_score[ $c ] ) ) {
                arsort( $cluster_entity_score[ $c ] );
                $top_key = array_key_first( $cluster_entity_score[ $c ] );
                $label   = $entity_name[ $top_key ] ?? 'Topic';
            }
            $clusters[] = [
                'id'    => $c,
                'label' => $label,
                'size'  => count( $pids ),
                'post_ids' => array_values( $pids ),
            ];
        }

        // Post metadata (title + top entities + cluster)
        $titles = [];
        if ( ! empty( $post_ids ) ) {
            $id_ph = implode( ',', array_fill( 0, count( $post_ids ), '%d' ) );
            $title_rows = $wpdb->get_results( $wpdb->prepare(
                "SELECT ID, post_title FROM {$wpdb->posts} WHERE ID IN ($id_ph)",
                array_map( 'intval', $post_ids )
            ), ARRAY_A );
            foreach ( $title_rows as $t ) $titles[ (int) $t['ID'] ] = $t['post_title'];
        }

        $posts = [];
        foreach ( $post_ids as $pid ) {
            $ents = array_keys( $post_entities[ $pid ] );
            $names = array_map( fn( $k ) => $entity_name[ $k ] ?? $k, array_slice( $ents, 0, 8 ) );
            $posts[] = [
                'id'         => $pid,
                'title'      => $titles[ $pid ] ?? "Post {$pid}",
                'url'        => get_permalink( $pid ),
                'cluster'    => $cluster_of[ $pid ],
                'entities'   => $names,
                'entity_count' => count( $ents ),
            ];
        }

        return [
            'clusters' => $clusters,
            'posts'    => $posts,
            'edges'    => array_values( array_map( function( $e ) {
                $e['shared'] = array_values( array_unique( $e['shared'] ) );
                $e['weight'] = count( $e['shared'] );
                return $e;
            }, $edges ) ),
        ];
    }

    /**
     * Related pages for a single post, derived from real shared-entity overlap.
     *
     * Reuses cluster_graph()'s IDF-weighted edges (the same engine that powers the
     * Topic Clusters map) and filters to the edges touching $post_id. Because both
     * endpoints are real, analysed posts that verifiably share a Wikidata-grounded
     * entity, this can never suggest a page that does not exist — unlike the old
     * AI-invented internal-link suggestions it replaces.
     *
     * Returns [] until at least one OTHER post shares a meaningful (non-hub) entity
     * with this one. That's accurate, not a gap: there is no real internal-link
     * target until a sibling page covering the same topic has been analysed.
     *
     * @param int $post_id The post being reviewed.
     * @param int $limit   Max related pages to return.
     * @return array<int,array{id:int,title:string,url:string,shared:string[],weight:int}>
     */
    public static function related_pages( $post_id, $limit = 8 ) {
        $post_id = (int) $post_id;
        $graph   = self::cluster_graph();
        if ( empty( $graph['edges'] ) ) return [];

        // Title/url lookup for the posts present in the graph.
        $meta = [];
        foreach ( $graph['posts'] as $p ) $meta[ (int) $p['id'] ] = $p;

        $related = [];
        foreach ( $graph['edges'] as $e ) {
            $a = (int) $e['source'];
            $b = (int) $e['target'];
            if ( $a !== $post_id && $b !== $post_id ) continue;
            $other = $a === $post_id ? $b : $a;
            if ( ! isset( $meta[ $other ] ) ) continue;
            $shared = isset( $e['shared'] ) ? array_values( array_unique( $e['shared'] ) ) : [];
            $related[] = [
                'id'     => $other,
                'title'  => $meta[ $other ]['title'] ?? get_the_title( $other ),
                'url'    => $meta[ $other ]['url'] ?? get_permalink( $other ),
                'shared' => $shared,
                'weight' => count( $shared ),
            ];
        }

        // Strongest overlaps first (most shared entities).
        usort( $related, fn( $x, $y ) => $y['weight'] <=> $x['weight'] );
        return array_slice( $related, 0, (int) $limit );
    }

    /**
     * Single-level Louvain community detection on a weighted, undirected graph.
     * Greedily moves nodes to the neighbouring community that yields the largest
     * modularity gain until stable. Deterministic: nodes and candidate
     * communities are processed in ascending id order. Isolated nodes (no edges)
     * remain their own community.
     *
     * @param int[]              $nodes Post ids.
     * @param array<int,array>   $adj   Weighted adjacency: $adj[a][b] = weight.
     * @return array<int,int>           post_id → community index (compact 0..k).
     */
    private static function detect_communities( array $nodes, array $adj ) {
        $sorted = $nodes;
        sort( $sorted );

        // Weighted degrees and 2m (sum of all degrees).
        $deg = [];
        $m2  = 0.0;
        foreach ( $sorted as $n ) {
            $d = 0.0;
            foreach ( ( $adj[ $n ] ?? [] ) as $w ) $d += $w;
            $deg[ $n ] = $d;
            $m2 += $d;
        }

        // No edges → every node is its own community.
        if ( $m2 <= 0 ) {
            $out = []; $i = 0;
            foreach ( $sorted as $n ) $out[ $n ] = $i++;
            return $out;
        }

        // Init: each node in its own community (community id = node id).
        $comm    = [];
        $sum_tot = []; // community → Σ degree of members
        foreach ( $sorted as $n ) { $comm[ $n ] = $n; $sum_tot[ $n ] = $deg[ $n ]; }

        $improved = true;
        $iter     = 0;
        while ( $improved && $iter < 50 ) {
            $improved = false;
            $iter++;
            foreach ( $sorted as $n ) {
                $cn = $comm[ $n ];
                // Remove n from its current community.
                $sum_tot[ $cn ] -= $deg[ $n ];

                // Sum of edge weights from n into each neighbouring community.
                $w_to = [];
                foreach ( ( $adj[ $n ] ?? [] ) as $nb => $w ) {
                    $cnb = $comm[ $nb ];
                    $w_to[ $cnb ] = ( $w_to[ $cnb ] ?? 0 ) + $w;
                }

                // Best move: maximise (w_to[c] - sum_tot[c] * deg[n] / 2m).
                // Start from staying in (the now-emptied) original community.
                $best_c    = $cn;
                $best_gain = ( $w_to[ $cn ] ?? 0 ) - ( $sum_tot[ $cn ] * $deg[ $n ] / $m2 );

                $cands = array_keys( $w_to );
                sort( $cands ); // deterministic tie handling
                foreach ( $cands as $c ) {
                    $gain = $w_to[ $c ] - ( $sum_tot[ $c ] * $deg[ $n ] / $m2 );
                    if ( $gain > $best_gain + 1e-12 ) { $best_gain = $gain; $best_c = $c; }
                }

                // Place n into the chosen community.
                $comm[ $n ] = $best_c;
                $sum_tot[ $best_c ] = ( $sum_tot[ $best_c ] ?? 0 ) + $deg[ $n ];
                if ( $best_c !== $cn ) $improved = true;
            }
        }

        // Compact community ids to 0..k in deterministic order.
        $relabel = []; $next = 0; $out = [];
        foreach ( $sorted as $n ) {
            $c = $comm[ $n ];
            if ( ! isset( $relabel[ $c ] ) ) $relabel[ $c ] = $next++;
            $out[ $n ] = $relabel[ $c ];
        }
        return $out;
    }

    /** Utility/boilerplate pages that shouldn't be pushed into topic clusters. */
    private static function is_low_value_page( $title, $url ) {
        $hay = strtolower( (string) $title . ' ' . (string) $url );
        return (bool) preg_match(
            '/\b(privacy|terms|conditions|disclaimer|cookie|sample[\s-]?page|hello[\s-]?world|test|404|not[\s-]?found|cart|checkout|my[\s-]?account|thank[\s-]?you|refund|shipping|returns?|login|sign[\s-]?in)\b/i',
            $hay
        );
    }

    public static function latest() {
        global $wpdb;
        $row = $wpdb->get_row(
            "SELECT * FROM {$wpdb->prefix}semantic_cohesion ORDER BY computed_at DESC LIMIT 1",
            ARRAY_A
        );
        if ( ! $row ) return null;

        foreach ( [ 'dominant_entities', 'recommended_actions', 'score_components' ] as $col ) {
            $row[ $col ] = json_decode( $row[ $col ] ?? '[]', true ) ?: [];
        }
        return $row;
    }

    private function empty_snapshot() {
        return [
            'score' => 0, 'coverage' => 0, 'connectivity' => 0, 'clusters' => 0,
            'depth' => 0, 'core_alignment' => 0, 'total_content_pages' => 0,
            'completed_analyses' => 0, 'dominant_entities' => [],
            'largest_cluster_size' => 0, 'isolated_pages_count' => 0,
            'cluster_count' => 0, 'recommended_actions' => [],
            'score_components' => [ 'connectivity' => 0, 'clusters' => 0, 'depth' => 0, 'alignment' => 0 ],
        ];
    }
}