<?php
/**
 * Database operations for RAG knowledge chunks and embeddings.
 *
 * @since 1.7.0
 */
class SiteStaffr_Knowledge_Manager {

    const SIMILARITY_THRESHOLD = 0.20;
    const MAX_RESULTS          = 5;

    /**
     * Diagnostic: populated by search_hybrid() with top 10 vector-pass and top 10 keyword-pass
     * results (id, chunk_title, score/rank) before fusion. Read by REST handler to attach to
     * the response for debugging retrieval mismatches. TEMPORARY — remove once root cause found.
     */
    public static $last_debug_passes = null;

    /**
     * Save chunks with embeddings for a post.
     * Deletes existing chunks for the post first (re-sync).
     *
     * @param int   $post_id WordPress post ID.
     * @param array $chunks  Array of arrays with keys: post_title, post_type, chunk_index, text, embedding.
     * @return int Number of chunks inserted.
     */
    public static function save_chunks( $post_id, $chunks ) {
        global $wpdb;
        $table = SiteStaffr_Database::get_knowledge_table();

        // Delete existing chunks for this post.
        $wpdb->delete( $table, array( 'post_id' => $post_id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

        $inserted = 0;
        foreach ( $chunks as $chunk ) {
            $embedding_binary = isset( $chunk['embedding'] ) ? self::vector_to_binary( $chunk['embedding'] ) : null;

            // Use UNHEX() for binary embedding — wpdb %s escaping corrupts raw binary.
            $embedding_hex = bin2hex( $embedding_binary );
            $chunk_title   = isset( $chunk['chunk_title'] ) && $chunk['chunk_title'] !== '' ? sanitize_text_field( $chunk['chunk_title'] ) : '';
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $result = $wpdb->query(
                $wpdb->prepare(
                    'INSERT INTO %i (post_id, post_title, post_type, chunk_index, chunk_text, chunk_title, embedding, synced_at) VALUES (%d, %s, %s, %d, %s, %s, UNHEX(%s), %s)',
                    $table,
                    $post_id,
                    sanitize_text_field( $chunk['post_title'] ),
                    sanitize_key( $chunk['post_type'] ),
                    intval( $chunk['chunk_index'] ),
                    $chunk['text'],
                    $chunk_title,
                    $embedding_hex,
                    current_time( 'mysql' )
                )
            );

            if ( false !== $result ) {
                ++$inserted;
            }
        }

        // Update post meta with sync timestamp.
        update_post_meta( $post_id, '_sitestaffr_knowledge_synced', current_time( 'mysql' ) );

        return $inserted;
    }

    /**
     * Insert a single chunk with its embedding.
     * Does NOT delete existing chunks — caller is responsible for cleanup.
     *
     * @param int   $post_id WordPress post ID.
     * @param array $chunk   Array with keys: post_title, post_type, chunk_index, text, embedding.
     * @return bool True on success.
     */
    public static function insert_chunk( $post_id, $chunk ) {
        global $wpdb;
        $table = SiteStaffr_Database::get_knowledge_table();

        $embedding_binary = ! empty( $chunk['embedding'] ) ? self::vector_to_binary( $chunk['embedding'] ) : null;
        $source           = isset( $chunk['source'] ) ? $chunk['source'] : 'auto';
        $chunk_title      = isset( $chunk['chunk_title'] ) && $chunk['chunk_title'] !== '' ? sanitize_text_field( $chunk['chunk_title'] ) : '';

        if ( null !== $embedding_binary ) {
            $embedding_hex = bin2hex( $embedding_binary );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $result = $wpdb->query(
                $wpdb->prepare(
                    'INSERT INTO %i (post_id, post_title, post_type, chunk_index, chunk_text, chunk_title, embedding, source, synced_at) VALUES (%d, %s, %s, %d, %s, %s, UNHEX(%s), %s, %s)',
                    $table,
                    $post_id,
                    sanitize_text_field( $chunk['post_title'] ),
                    sanitize_key( $chunk['post_type'] ),
                    intval( $chunk['chunk_index'] ),
                    $chunk['text'],
                    $chunk_title,
                    $embedding_hex,
                    $source,
                    current_time( 'mysql' )
                )
            );
        } else {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $result = $wpdb->query(
                $wpdb->prepare(
                    'INSERT INTO %i (post_id, post_title, post_type, chunk_index, chunk_text, chunk_title, source, synced_at) VALUES (%d, %s, %s, %d, %s, %s, %s, %s)',
                    $table,
                    $post_id,
                    sanitize_text_field( $chunk['post_title'] ),
                    sanitize_key( $chunk['post_type'] ),
                    intval( $chunk['chunk_index'] ),
                    $chunk['text'],
                    $chunk_title,
                    $source,
                    current_time( 'mysql' )
                )
            );
        }

        return false !== $result;
    }

    /**
     * Delete auto-synced chunks for a post. Manual chunks are preserved.
     *
     * @param int $post_id WordPress post ID.
     * @return int|false Number of rows deleted or false on error.
     */
    public static function delete_post_chunks( $post_id ) {
        global $wpdb;
        $table = SiteStaffr_Database::get_knowledge_table();
        // Only delete auto-synced chunks. Manual chunks are preserved.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM %i WHERE post_id = %d AND source = 'auto'",
                $table,
                $post_id
            )
        );
        // Only clear the synced meta if no chunks remain (auto or manual).
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $remaining = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COUNT(*) FROM %i WHERE post_id = %d',
                $table,
                $post_id
            )
        );
        if ( 0 === (int) $remaining ) {
            delete_post_meta( $post_id, '_sitestaffr_knowledge_synced' );
        }
        return $result;
    }

    /**
     * Search for chunks similar to a query embedding.
     *
     * @param array $query_embedding Float array (1536 dimensions).
     * @return array Matched chunks with relevance_score, sorted by relevance.
     */
    public static function search( $query_embedding, $max_results = null, $post_id = null ) {
        global $wpdb;
        $table = SiteStaffr_Database::get_knowledge_table();

        // Phase 1: Load only IDs and embeddings for similarity computation.
        // Avoids loading chunk_text (LONGTEXT) for every row — only fetched for top results.
        if ( $post_id ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $rows = $wpdb->get_results(
                $wpdb->prepare( 'SELECT id, embedding FROM %i WHERE post_id = %d AND embedding IS NOT NULL', $table, $post_id )
            );
        } else {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $rows = $wpdb->get_results(
                $wpdb->prepare( 'SELECT id, embedding FROM %i WHERE embedding IS NOT NULL', $table )
            );
        }

        if ( empty( $rows ) ) {
            return array();
        }

        $scored = array();
        foreach ( $rows as $row ) {
            $stored_embedding = self::binary_to_vector( $row->embedding );
            if ( empty( $stored_embedding ) ) {
                continue;
            }

            $score = self::cosine_similarity( $query_embedding, $stored_embedding );
            if ( $score >= self::SIMILARITY_THRESHOLD ) {
                $scored[] = array(
                    'id'        => intval( $row->id ),
                    'score'     => round( $score, 4 ),
                    'embedding' => $stored_embedding,
                );
            }
        }

        // Sort by relevance descending and take top N.
        usort( $scored, function ( $a, $b ) {
            return $b['score'] <=> $a['score'];
        } );

        if ( empty( $scored ) ) {
            return array();
        }

        $limit = $max_results ? intval( $max_results ) : self::MAX_RESULTS;

        // Fetch a larger candidate pool for deduplication (3× limit).
        // Multiple pages often have overlapping content — without dedup, duplicate
        // chunks eat all result slots and crowd out genuinely different answers.
        $candidate_count = min( count( $scored ), $limit * 3 );
        $candidates      = array_slice( $scored, 0, $candidate_count );
        unset( $scored ); // Free embeddings for non-candidate chunks.

        // Phase 2: Load full data for the candidate pool.
        $candidate_ids = array_column( $candidates, 'id' );
        $score_map     = array_column( $candidates, 'score', 'id' );
        $placeholders  = implode( ',', array_fill( 0, count( $candidate_ids ), '%d' ) );

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $placeholders is a dynamic list of %d tokens built from array_fill; count matches candidate_ids.
        $detail_rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, post_id, post_title, chunk_title, chunk_text FROM %i WHERE id IN ({$placeholders})",
                array_merge( array( $table ), $candidate_ids )
            )
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber

        // Build lookup by id for ordered iteration.
        $detail_map = array();
        foreach ( $detail_rows as $row ) {
            $detail_map[ intval( $row->id ) ] = $row;
        }

        // Phase 3: Embedding-based deduplication — skip chunks that are semantically
        // redundant with an already-selected result. Uses cosine similarity between
        // candidate embeddings (> 0.85 = near-duplicate). Handles both exact copies
        // across pages AND semantically similar but differently-worded content
        // (e.g., 20 medical pages all describing the same procedure differently).
        $dedup_threshold    = 0.85;
        $results            = array();
        $selected_embeddings = array();
        foreach ( $candidates as $candidate ) {
            if ( count( $results ) >= $limit ) {
                break;
            }
            $cid = $candidate['id'];
            if ( ! isset( $detail_map[ $cid ] ) ) {
                continue;
            }

            // Compare this candidate's embedding against all already-selected embeddings.
            $is_dup = false;
            foreach ( $selected_embeddings as $selected_emb ) {
                if ( self::cosine_similarity( $candidate['embedding'], $selected_emb ) >= $dedup_threshold ) {
                    $is_dup = true;
                    break;
                }
            }
            if ( $is_dup ) {
                continue;
            }

            $selected_embeddings[] = $candidate['embedding'];
            $row = $detail_map[ $cid ];
            $results[] = array(
                'id'              => $cid,
                'post_id'         => intval( $row->post_id ),
                'post_title'      => $row->post_title,
                'chunk_title'     => ! empty( $row->chunk_title ) ? $row->chunk_title : null,
                'content'         => $row->chunk_text,
                'relevance_score' => $score_map[ $cid ],
                'url'             => self::get_chunk_url( intval( $row->post_id ) ),
            );
        }

        return $results;
    }

    /**
     * Hybrid search: vector ranking preserved, keyword used for recall only.
     *
     * Vector top-K (ordered by cosine) comes first; keyword hits not already in the
     * vector set are appended in keyword rank order. This avoids RRF's failure mode
     * where a strong vector hit gets displaced by weaker chunks that happen to appear
     * in both lists.
     *
     * When $query_text is empty, falls back to pure vector search.
     * When FULLTEXT index is unavailable (older MySQL), falls back to LIKE tokens.
     *
     * @param array  $query_embedding Vector of floats.
     * @param string $query_text      Raw visitor query text for keyword matching.
     * @param int|null $max_results   Max results to return.
     * @return array
     */
    public static function search_hybrid( $query_embedding, $query_text, $max_results = null, $post_id = null ) {
        if ( empty( $query_text ) || ! is_string( $query_text ) ) {
            return self::search( $query_embedding, $max_results, $post_id );
        }

        global $wpdb;
        $table = SiteStaffr_Database::get_knowledge_table();
        $limit = $max_results ? intval( $max_results ) : self::MAX_RESULTS;
        $per_pass_k = 30;

        // --- Vector pass: reuse embedding-only load + cosine scoring. ---
        if ( $post_id ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $rows = $wpdb->get_results(
                $wpdb->prepare( 'SELECT id, embedding FROM %i WHERE post_id = %d AND embedding IS NOT NULL', $table, $post_id )
            );
        } else {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $rows = $wpdb->get_results(
                $wpdb->prepare( 'SELECT id, embedding FROM %i WHERE embedding IS NOT NULL', $table )
            );
        }
        $vector_scored = array();
        foreach ( $rows as $row ) {
            $stored = self::binary_to_vector( $row->embedding );
            if ( empty( $stored ) ) continue;
            $score = self::cosine_similarity( $query_embedding, $stored );
            if ( $score >= self::SIMILARITY_THRESHOLD ) {
                $vector_scored[] = array(
                    'id'        => intval( $row->id ),
                    'score'     => round( $score, 4 ),
                    'embedding' => $stored,
                );
            }
        }
        usort( $vector_scored, function ( $a, $b ) { return $b['score'] <=> $a['score']; } );
        $vector_top = array_slice( $vector_scored, 0, $per_pass_k );

        // --- Keyword pass: MySQL FULLTEXT; fallback to LIKE tokens if FT unavailable. ---
        $keyword_top = self::keyword_search( $query_text, $per_pass_k, $post_id );

        // --- Union fusion: vector order preserved, keyword-only appended for recall. ---
        // Why not RRF: RRF rewards any chunk appearing in both lists, which can displace
        // a strong vector hit with weaker chunks that share a common keyword. Union-append
        // guarantees vector's top picks stay on top; keyword's role is to surface chunks
        // vector missed, not to re-rank the ones vector found.
        $score_map     = array(); // id => cosine score
        $embedding_map = array(); // id => embedding vector (for dedup)
        foreach ( $vector_top as $cand ) {
            $score_map[ $cand['id'] ]     = $cand['score'];
            $embedding_map[ $cand['id'] ] = $cand['embedding'];
        }
        $keyword_only_ids = array();
        foreach ( $keyword_top as $kid ) {
            if ( ! isset( $score_map[ $kid ] ) ) {
                $keyword_only_ids[] = $kid;
            }
        }
        $ordered_ids = array_merge( array_column( $vector_top, 'id' ), $keyword_only_ids );
        if ( empty( $ordered_ids ) ) {
            return array();
        }

        // Take 3× limit as candidate pool for semantic dedup.
        $candidate_ids = array_slice( $ordered_ids, 0, $limit * 3 );

        // Fetch embeddings for keyword-only ids we don't have cached; compute their cosine
        // so dedup and relevance_score work consistently across vector + keyword-only.
        $missing_ids = array_values( array_filter( $candidate_ids, function ( $id ) use ( $embedding_map ) {
            return ! isset( $embedding_map[ $id ] );
        } ) );
        if ( ! empty( $missing_ids ) ) {
            $placeholders = implode( ',', array_fill( 0, count( $missing_ids ), '%d' ) );
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
            $emb_rows = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT id, embedding FROM %i WHERE id IN ({$placeholders})",
                    array_merge( array( $table ), $missing_ids )
                )
            );
            // phpcs:enable
            foreach ( $emb_rows as $er ) {
                $id  = intval( $er->id );
                $emb = self::binary_to_vector( $er->embedding );
                $embedding_map[ $id ] = $emb;
                if ( ! isset( $score_map[ $id ] ) && ! empty( $emb ) ) {
                    $score_map[ $id ] = round( self::cosine_similarity( $query_embedding, $emb ), 4 );
                }
            }
        }

        $placeholders = implode( ',', array_fill( 0, count( $candidate_ids ), '%d' ) );
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
        $detail_rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, post_id, post_title, chunk_title, chunk_text FROM %i WHERE id IN ({$placeholders})",
                array_merge( array( $table ), $candidate_ids )
            )
        );
        // phpcs:enable
        $detail_map = array();
        foreach ( $detail_rows as $row ) {
            $detail_map[ intval( $row->id ) ] = $row;
        }

        // Semantic dedup. Threshold tightened to 0.95 (vs 0.85 for vector-only search)
        // because sibling chunks on a single page (e.g. multiple pricing options that
        // share a template) cluster ~0.88-0.93 cosine and were being collapsed into one.
        // 0.95 still catches true cross-page boilerplate duplicates (which run 0.97-1.0).
        $dedup_threshold = 0.95;
        $results = array();
        $selected_embeddings = array();
        foreach ( $candidate_ids as $cid ) {
            if ( count( $results ) >= $limit ) break;
            if ( ! isset( $detail_map[ $cid ] ) ) continue;
            $emb = isset( $embedding_map[ $cid ] ) ? $embedding_map[ $cid ] : null;
            if ( $emb ) {
                $is_dup = false;
                foreach ( $selected_embeddings as $sel ) {
                    if ( self::cosine_similarity( $emb, $sel ) >= $dedup_threshold ) {
                        $is_dup = true;
                        break;
                    }
                }
                if ( $is_dup ) continue;
                $selected_embeddings[] = $emb;
            }
            $row       = $detail_map[ $cid ];
            $cos       = isset( $score_map[ $cid ] ) ? $score_map[ $cid ] : 0.0;
            $results[] = array(
                'id'              => $cid,
                'post_id'         => intval( $row->post_id ),
                'post_title'      => $row->post_title,
                'chunk_title'     => ! empty( $row->chunk_title ) ? $row->chunk_title : null,
                'content'         => $row->chunk_text,
                'relevance_score' => $cos,
                'vector_score'    => $cos,
                'url'             => self::get_chunk_url( intval( $row->post_id ) ),
            );
        }

        // Diagnostic: capture pre-fusion top 10 of each pass so we can see which half
        // surfaced (or missed) a given chunk. Temporary — remove once retrieval bug is solved.
        $debug_v_ids = array_slice( array_column( $vector_top, 'id' ), 0, 10 );
        $debug_k_ids = array_slice( $keyword_top, 0, 10 );
        $debug_all_ids = array_values( array_unique( array_merge( $debug_v_ids, $debug_k_ids ) ) );
        $debug_title_map = array();
        if ( ! empty( $debug_all_ids ) ) {
            $ph = implode( ',', array_fill( 0, count( $debug_all_ids ), '%d' ) );
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
            $drows = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT id, chunk_title FROM %i WHERE id IN ({$ph})",
                    array_merge( array( $table ), $debug_all_ids )
                )
            );
            // phpcs:enable
            foreach ( $drows as $dr ) {
                $debug_title_map[ intval( $dr->id ) ] = $dr->chunk_title;
            }
        }
        $debug_vector = array();
        foreach ( array_slice( $vector_top, 0, 10 ) as $i => $cand ) {
            $id = $cand['id'];
            $debug_vector[] = array(
                'rank'   => $i + 1,
                'id'     => $id,
                'title'  => isset( $debug_title_map[ $id ] ) ? $debug_title_map[ $id ] : null,
                'cosine' => $cand['score'],
            );
        }
        $debug_keyword = array();
        foreach ( $debug_k_ids as $i => $kid ) {
            $debug_keyword[] = array(
                'rank'  => $i + 1,
                'id'    => $kid,
                'title' => isset( $debug_title_map[ $kid ] ) ? $debug_title_map[ $kid ] : null,
            );
        }
        self::$last_debug_passes = array(
            'vector_top_n'  => count( $vector_top ),
            'keyword_top_n' => count( $keyword_top ),
            'vector'        => $debug_vector,
            'keyword'       => $debug_keyword,
        );

        return $results;
    }

    /**
     * Keyword pass for hybrid search. Returns array of chunk ids in relevance order.
     * Tries FULLTEXT NATURAL LANGUAGE MODE first; falls back to tokenized LIKE if FT index is missing.
     *
     * @param string $query_text
     * @param int    $limit
     * @return int[]
     */
    private static function keyword_search( $query_text, $limit, $post_id = null ) {
        global $wpdb;
        $table = SiteStaffr_Database::get_knowledge_table();

        $cleaned = trim( preg_replace( '/\s+/', ' ', $query_text ) );
        if ( strlen( $cleaned ) < 2 ) return array();

        // Try FULLTEXT first. Silently swallow errors (missing index, wrong engine, etc.).
        $suppress_previous = $wpdb->suppress_errors( true );
        if ( $post_id ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $rows = $wpdb->get_results(
                $wpdb->prepare(
                    'SELECT id FROM %i
                 WHERE post_id = %d AND MATCH(chunk_title, chunk_text) AGAINST (%s IN NATURAL LANGUAGE MODE)
                 ORDER BY MATCH(chunk_title, chunk_text) AGAINST (%s IN NATURAL LANGUAGE MODE) DESC
                 LIMIT %d',
                    $table, $post_id, $cleaned, $cleaned, $limit
                )
            );
        } else {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $rows = $wpdb->get_results(
                $wpdb->prepare(
                    'SELECT id FROM %i
                 WHERE MATCH(chunk_title, chunk_text) AGAINST (%s IN NATURAL LANGUAGE MODE)
                 ORDER BY MATCH(chunk_title, chunk_text) AGAINST (%s IN NATURAL LANGUAGE MODE) DESC
                 LIMIT %d',
                    $table, $cleaned, $cleaned, $limit
                )
            );
        }
        $wpdb->suppress_errors( $suppress_previous );

        if ( is_array( $rows ) && ! empty( $rows ) ) {
            return array_map( function ( $r ) { return intval( $r->id ); }, $rows );
        }

        // Fallback: tokenized LIKE. Slow but always works. Score by term-hit count.
        $tokens = array_filter( preg_split( '/\W+/', mb_strtolower( $cleaned ) ), function ( $t ) {
            return strlen( $t ) >= 3;
        } );
        $tokens = array_slice( array_values( array_unique( $tokens ) ), 0, 6 );
        if ( empty( $tokens ) ) return array();

        $score_sql = array();
        $params    = array( $table );
        foreach ( $tokens as $t ) {
            $like = '%' . $wpdb->esc_like( $t ) . '%';
            $score_sql[] = '(CASE WHEN LOWER(CONCAT_WS(" ", chunk_title, chunk_text)) LIKE %s THEN 1 ELSE 0 END)';
            $params[] = $like;
        }
        $score_expr = '(' . implode( ' + ', $score_sql ) . ')';

        if ( $post_id ) {
            $where_clause = 'WHERE post_id = %d HAVING hits > 0';
            array_splice( $params, 1, 0, array( $post_id ) );
        } else {
            $where_clause = 'HAVING hits > 0';
        }
        $params[] = $limit;

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, {$score_expr} AS hits FROM %i {$where_clause} ORDER BY hits DESC LIMIT %d",
                $params
            )
        );
        // phpcs:enable
        if ( ! is_array( $rows ) ) return array();
        return array_map( function ( $r ) { return intval( $r->id ); }, $rows );
    }

    /**
     * Search for chunks relevant to a specific post (by post_id).
     * Used at session start to get current-page context.
     *
     * @param int $post_id WordPress post ID.
     * @param int $limit   Max chunks to return.
     * @return array Chunks for the given post.
     */
    public static function get_chunks_for_post( $post_id, $limit = 8 ) {
        global $wpdb;
        $table = SiteStaffr_Database::get_knowledge_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT id, post_id, post_title, chunk_title, chunk_text, source, hit_count FROM %i WHERE post_id = %d ORDER BY chunk_index ASC LIMIT %d',
                $table,
                $post_id,
                $limit
            )
        );

        if ( empty( $rows ) ) {
            return array();
        }

        $chunks = array();
        foreach ( $rows as $row ) {
            $chunks[] = array(
                'id'          => intval( $row->id ),
                'post_id'     => intval( $row->post_id ),
                'post_title'  => $row->post_title,
                'chunk_title' => ! empty( $row->chunk_title ) ? $row->chunk_title : null,
                'content'     => $row->chunk_text,
                'source'      => $row->source,
                'hit_count'   => intval( $row->hit_count ),
            );
        }

        return $chunks;
    }

    /**
     * Check if any knowledge chunks exist in the database.
     *
     * @return bool True if at least one chunk exists.
     */
    public static function has_knowledge() {
        global $wpdb;
        $table = SiteStaffr_Database::get_knowledge_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return (bool) $wpdb->get_var( $wpdb->prepare( 'SELECT 1 FROM %i LIMIT 1', $table ) );
    }

    /**
     * Get all synced chunk text, grouped by page title.
     *
     * Returns a flat string suitable for sending to the AI model:
     * PAGE: Home
     * [chunk text]
     * [chunk text]
     *
     * PAGE: About
     * [chunk text]
     *
     * @since 1.14.0
     * @return string Concatenated chunk text organized by page, or empty string.
     */
    public static function get_all_synced_chunks_as_text() {
        global $wpdb;
        $table = SiteStaffr_Database::get_knowledge_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT post_title, chunk_text FROM %i WHERE chunk_text IS NOT NULL ORDER BY post_title ASC, chunk_index ASC',
                $table
            )
        );

        if ( empty( $rows ) ) {
            return '';
        }

        $output        = '';
        $current_title = null;
        foreach ( $rows as $row ) {
            if ( $row->post_title !== $current_title ) {
                if ( null !== $current_title ) {
                    $output .= "\n";
                }
                $output       .= 'PAGE: ' . $row->post_title . "\n";
                $current_title = $row->post_title;
            }
            $output .= $row->chunk_text . "\n";
        }

        return $output;
    }

    /**
     * Relative permalink for a chunk's source post, or null when unresolvable
     * (e.g. manually added chunks without a real post).
     *
     * Lets the AI answer "where can I find X?" with the actual page link.
     *
     * @param int $post_id Source post ID.
     * @return string|null Site-relative URL.
     */
    private static function get_chunk_url( $post_id ) {
        if ( $post_id <= 0 ) {
            return null;
        }
        $permalink = get_permalink( $post_id );
        return $permalink ? wp_make_link_relative( $permalink ) : null;
    }

    /**
     * Get all published pages and posts as a lightweight directory.
     *
     * Used by Page Expert Mode to give the AI a navigation map.
     * Cached as a transient (1 hour) and cleared on post save.
     *
     * @param int $limit Max pages to return. Default 100.
     * @return array Array of [ 'title' => string, 'url' => string ].
     */
    public static function get_site_pages( $limit = 100 ) {
        $cache_key = 'sitestaffr_site_pages';
        $cached    = get_transient( $cache_key );
        if ( false !== $cached ) {
            return $cached;
        }

        $public_types    = get_post_types( array( 'public' => true ), 'names' );
        $queryable_types = get_post_types( array( 'publicly_queryable' => true ), 'names' );
        $ui_types        = get_post_types( array( 'show_ui' => true ), 'names' );
        $all_types       = array_unique( array_merge( array_values( $public_types ), array_values( $queryable_types ), array_values( $ui_types ) ) );
        $all_types       = array_diff( $all_types, array( 'attachment', 'revision', 'nav_menu_item', 'custom_css', 'customize_changeset', 'oembed_cache', 'user_request', 'wp_block', 'wp_template', 'wp_template_part', 'wp_global_styles', 'wp_navigation', 'wp_font_family', 'wp_font_face' ) );

        $posts = get_posts( array(
            'post_type'      => array_values( $all_types ),
            'post_status'    => 'publish',
            'posts_per_page' => $limit,
            'orderby'        => 'title',
            'order'          => 'ASC',
            'fields'         => 'ids',
        ) );

        $pages = array();
        foreach ( $posts as $post_id ) {
            $pages[] = array(
                'title' => get_the_title( $post_id ),
                'url'   => wp_make_link_relative( get_permalink( $post_id ) ),
            );
        }

        set_transient( $cache_key, $pages, HOUR_IN_SECONDS );
        return $pages;
    }

    /**
     * Clear the site pages cache.
     *
     * Hooked to save_post to keep the directory fresh.
     */
    public static function clear_site_pages_cache() {
        delete_transient( 'sitestaffr_site_pages' );
    }

    /**
     * Get knowledge sync stats.
     *
     * @return array Stats with keys: total_chunks, total_posts, last_synced.
     */
    public static function get_stats() {
        global $wpdb;
        $table = SiteStaffr_Database::get_knowledge_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $total_chunks = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $total_posts = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(DISTINCT post_id) FROM %i', $table ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $last_synced = $wpdb->get_var( $wpdb->prepare( 'SELECT MAX(synced_at) FROM %i', $table ) );

        return array(
            'total_chunks' => $total_chunks,
            'total_posts'  => $total_posts,
            'last_synced'  => $last_synced,
        );
    }

    /**
     * Top knowledge sections by retrieval hit count.
     *
     * @param int $limit Max rows.
     * @return array<int,array{post_title:string,chunk_title:string,hit_count:int}>
     */
    public static function get_top_chunks( $limit = 5 ) {
        global $wpdb;
        $table = SiteStaffr_Database::get_knowledge_table();
        $limit = max( 1, absint( $limit ) );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT post_title, chunk_title, hit_count FROM %i
                 WHERE hit_count > 0
                 ORDER BY hit_count DESC
                 LIMIT %d',
                $table,
                $limit
            ),
            ARRAY_A
        );

        return is_array( $rows ) ? $rows : array();
    }

    /**
     * Cosine similarity between two vectors.
     *
     * @param array $a Vector A.
     * @param array $b Vector B.
     * @return float Similarity score between -1 and 1.
     */
    public static function cosine_similarity( $a, $b ) {
        $dot    = 0.0;
        $norm_a = 0.0;
        $norm_b = 0.0;
        $count  = min( count( $a ), count( $b ) );

        for ( $i = 0; $i < $count; $i++ ) {
            $dot    += $a[ $i ] * $b[ $i ];
            $norm_a += $a[ $i ] * $a[ $i ];
            $norm_b += $b[ $i ] * $b[ $i ];
        }

        $denominator = sqrt( $norm_a ) * sqrt( $norm_b );
        if ( $denominator < 1e-10 ) {
            return 0.0;
        }

        return $dot / $denominator;
    }

    /**
     * Convert float array to binary for storage.
     *
     * @param array $vector Array of floats.
     * @return string Binary string.
     */
    public static function vector_to_binary( $vector ) {
        return pack( 'f*', ...$vector );
    }

    /**
     * Convert binary back to float array.
     *
     * @param string $binary Binary string from DB.
     * @return array Array of floats.
     */
    public static function binary_to_vector( $binary ) {
        if ( empty( $binary ) ) {
            return array();
        }
        return array_values( unpack( 'f*', $binary ) );
    }

    /**
     * Increment hit_count for chunks returned in search results.
     *
     * @since 2.0.0
     * @param array $chunk_ids Array of chunk ID integers.
     * @return void
     */
    public static function increment_hit_counts( $chunk_ids ) {
        if ( empty( $chunk_ids ) ) {
            return;
        }
        global $wpdb;
        $table = SiteStaffr_Database::get_knowledge_table();
        $ids = array_map( 'intval', $chunk_ids );
        $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->query(
            $wpdb->prepare(
                "UPDATE %i SET hit_count = hit_count + 1 WHERE id IN ($placeholders)", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- placeholders are safe %d fill
                array_merge( array( $table ), $ids )
            )
        );
    }

    /**
     * Get top chunks ranked by search frequency (hot knowledge).
     *
     * @since 2.0.0
     * @param int   $limit     Maximum chunks to return.
     * @param array $exclude   Optional array of chunk IDs to exclude (deduplication).
     * @return array Array of associative arrays with id, post_title, chunk_text, hit_count.
     */
    public static function get_hot_chunks( $limit = 5, $exclude = array() ) {
        global $wpdb;
        $table = SiteStaffr_Database::get_knowledge_table();

        $exclude_clause = '';
        $params = array( $table );
        if ( ! empty( $exclude ) ) {
            $exclude_ids = array_map( 'intval', $exclude );
            $placeholders = implode( ',', array_fill( 0, count( $exclude_ids ), '%d' ) );
            $exclude_clause = " AND id NOT IN ($placeholders)"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $params = array_merge( $params, $exclude_ids );
        }
        $params[] = $limit;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
        $results = $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
            $wpdb->prepare(
                "SELECT id, post_title, chunk_text, hit_count FROM %i WHERE hit_count > 0{$exclude_clause} ORDER BY hit_count DESC LIMIT %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $params
            ),
            ARRAY_A
        );

        return is_array( $results ) ? $results : array();
    }

    /**
     * Count hand-added knowledge entries (source = manual).
     *
     * @return int
     */
    public static function count_manual_chunks() {
        global $wpdb;
        $table = SiteStaffr_Database::get_knowledge_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return (int) $wpdb->get_var(
            $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE source = %s', $table, 'manual' )
        );
    }

    /**
     * Insert a manual knowledge chunk.
     *
     * @since 2.0.0
     * @param int    $post_id   WordPress post ID (0 for standalone).
     * @param string $title     Chunk title.
     * @param string $text      Chunk content text.
     * @return int|false Inserted row ID or false on error.
     */
    public static function insert_manual_chunk( $post_id, $title, $text ) {
        global $wpdb;
        $table = SiteStaffr_Database::get_knowledge_table();

        // Determine next chunk_index for this post.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $max_index = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT MAX(chunk_index) FROM %i WHERE post_id = %d',
                $table,
                $post_id
            )
        );
        $next_index = is_null( $max_index ) ? 0 : ( (int) $max_index + 1 );

        $post_type = 'manual';
        if ( $post_id > 0 ) {
            $post = get_post( $post_id );
            $post_type = $post ? $post->post_type : 'manual';
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->insert(
            $table,
            array(
                'post_id'     => $post_id,
                'post_title'  => $title,
                'post_type'   => $post_type,
                'chunk_index' => $next_index,
                'chunk_text'  => $text,
                'embedding'   => null,
                'source'      => 'manual',
                'hit_count'   => 0,
                'synced_at'   => current_time( 'mysql', true ),
            ),
            array( '%d', '%s', '%s', '%d', '%s', null, '%s', '%d', '%s' )
        );

        return false !== $result ? $wpdb->insert_id : false;
    }

    /**
     * Delete a specific manual chunk by ID.
     *
     * Only deletes manual-source chunks as a safety guard.
     *
     * @since 2.0.0
     * @param int $chunk_id Chunk ID.
     * @return bool True if deleted.
     */
    public static function delete_manual_chunk( $chunk_id ) {
        global $wpdb;
        $table = SiteStaffr_Database::get_knowledge_table();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->delete(
            $table,
            array(
                'id'     => $chunk_id,
                'source' => 'manual',
            ),
            array( '%d', '%s' )
        );
        return false !== $result && $result > 0;
    }
}
