<?php
/**
 * External KB Endpoint for BoostBot REST API.
 *
 * Exposes the site's Knowledge Base to authorized external consumers
 * (e.g. BoostCalendar's WhatsApp bot) via API keys managed in
 * BoostBot_External_KB_Keys:
 *
 * - GET  /kb/status   — cheap connectivity + change-detection probe.
 * - GET  /kb/export   — paginated export of merged documents (periodic sync).
 * - POST /kb/retrieve — live RAG retrieval (no LLM, no conversation state).
 * - GET/POST/PUT/DELETE /kb/keys — admin-only key management.
 *
 * All responses use the standard plugin envelope: { success, data }.
 * Only publicly-published content is ever exposed.
 *
 * @package BoostBot
 * @since 2.3.0
 */

// Prevent direct access to the file.
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

/**
 * BoostBot_External_KB_Endpoint class.
 *
 * @since 2.3.0
 */
class BoostBot_External_KB_Endpoint extends BoostBot_Base_Endpoint {

    /**
     * The base path for this endpoint.
     *
     * @since 2.3.0
     * @access protected
     * @var string $rest_base
     */
    protected $rest_base = 'kb';

    /**
     * Maximum items per export page.
     *
     * @since 2.3.0
     * @var int
     */
    const EXPORT_MAX_PER_PAGE = 100;

    /**
     * Maximum exported content length per item (chars).
     *
     * @since 2.3.0
     * @var int
     */
    const EXPORT_CONTENT_CAP = 80000;

    /**
     * Maximum content length per retrieved chunk (chars).
     *
     * @since 2.3.0
     * @var int
     */
    const RETRIEVE_CHUNK_CAP = 2000;

    /**
     * The key record authenticated by the permission callback for
     * the current request (set before the route callback runs).
     *
     * @since 2.3.0
     * @var array|null
     */
    private $authenticated_key = null;

    /**
     * Register the routes for this endpoint.
     *
     * @since 2.3.0
     */
    public function register() {
        // GET /wp-json/boostbot/v1/kb/status — connectivity + change detection (KB key auth).
        $this->register_route(
            '/status',
            'GET',
            array( $this, 'get_status' ),
            array( $this, 'kb_key_status_permission' )
        );

        // GET /wp-json/boostbot/v1/kb/export — paginated document export (KB key auth).
        $this->register_route(
            '/export',
            'GET',
            array( $this, 'get_export' ),
            array( $this, 'kb_key_export_permission' ),
            $this->get_export_args()
        );

        // POST /wp-json/boostbot/v1/kb/retrieve — live retrieval (KB key auth).
        $this->register_route(
            '/retrieve',
            'POST',
            array( $this, 'post_retrieve' ),
            array( $this, 'kb_key_retrieve_permission' ),
            $this->get_retrieve_args()
        );

        // ── Admin-only key management ────────────────────────────────

        // GET /wp-json/boostbot/v1/kb/keys — list keys (no hashes).
        $this->register_route(
            '/keys',
            'GET',
            array( $this, 'list_keys' ),
            array( $this, 'admin_access' )
        );

        // POST /wp-json/boostbot/v1/kb/keys — create key (raw key returned once).
        $this->register_route(
            '/keys',
            'POST',
            array( $this, 'create_key' ),
            array( $this, 'admin_access' )
        );

        // PUT /wp-json/boostbot/v1/kb/keys/{id} — update key spec / enable state.
        $this->register_route(
            '/keys/(?P<id>[A-Za-z0-9_\-]+)',
            'PUT',
            array( $this, 'update_key' ),
            array( $this, 'admin_access' )
        );

        // DELETE /wp-json/boostbot/v1/kb/keys/{id} — delete key.
        $this->register_route(
            '/keys/(?P<id>[A-Za-z0-9_\-]+)',
            'DELETE',
            array( $this, 'delete_key' ),
            array( $this, 'admin_access' )
        );
    }

    // =========================================================================
    // Permission callbacks (external key auth)
    // =========================================================================

    /**
     * Permission callback for /kb/status.
     *
     * @since 2.3.0
     * @param WP_REST_Request $request The REST request.
     * @return true|WP_Error
     */
    public function kb_key_status_permission( $request ) {
        return $this->authenticate_kb_key( $request, 'status' );
    }

    /**
     * Permission callback for /kb/export.
     *
     * @since 2.3.0
     * @param WP_REST_Request $request The REST request.
     * @return true|WP_Error
     */
    public function kb_key_export_permission( $request ) {
        return $this->authenticate_kb_key( $request, 'export' );
    }

    /**
     * Permission callback for /kb/retrieve.
     *
     * @since 2.3.0
     * @param WP_REST_Request $request The REST request.
     * @return true|WP_Error
     */
    public function kb_key_retrieve_permission( $request ) {
        return $this->authenticate_kb_key( $request, 'retrieve' );
    }

    /**
     * Shared key authentication for external routes.
     *
     * @since 2.3.0
     * @param WP_REST_Request $request  The REST request.
     * @param string          $endpoint Endpoint name for flag/rate checks.
     * @return true|WP_Error
     */
    private function authenticate_kb_key( $request, $endpoint ) {
        if ( ! class_exists( 'BoostBot_External_KB_Keys' ) ) {
            return new WP_Error( 'boostbot_kb_unavailable', __( 'External KB access is unavailable', 'boostbot' ), array( 'status' => 503 ) );
        }

        $result = BoostBot_External_KB_Keys::authenticate( $request, $endpoint );
        if ( is_wp_error( $result ) ) {
            return $result;
        }

        // Prevent any intermediary cache (Nginx/Engintron micro-cache,
        // LiteSpeed, CDN) from storing this authenticated response and later
        // serving it to an unauthenticated client for the same URL.
        $this->send_no_store_headers();
        $this->authenticated_key = $result;
        return true;
    }

    /**
     * Emit no-store cache headers for authenticated External KB responses.
     *
     * Intermediary caches (the Nginx/Engintron micro-cache, LiteSpeed, or a
     * CDN) key on the URL and ignore the X-BoostBot-KB-Key header. Without
     * these headers a cached keyed response is served to unauthenticated
     * clients requesting the same URL, bypassing the API key gate, rate
     * limits and scopes. X-Accel-Expires is the highest-priority Nginx cache
     * directive and is honored even when Cache-Control is ignored.
     *
     * @since 2.3.1
     * @return void
     */
    private function send_no_store_headers() {
        if ( headers_sent() ) {
            return;
        }
        nocache_headers();
        header( 'Cache-Control: no-store, no-cache, must-revalidate, max-age=0, private', true );
        header( 'X-Accel-Expires: 0', true );
        if ( ! defined( 'DONOTCACHEPAGE' ) ) {
            define( 'DONOTCACHEPAGE', true );
        }
        do_action( 'litespeed_control_set_nocache', 'boostbot external kb api' );
    }

    // =========================================================================
    // GET /kb/status
    // =========================================================================

    /**
     * Get KB status: version, item count, last index time, site name.
     *
     * @since 2.3.0
     * @param WP_REST_Request $request The REST request.
     * @return WP_REST_Response|WP_Error
     */
    public function get_status( $request ) {
        global $wpdb;

        $key   = $this->authenticated_key ?: array();
        $table = $this->knowledge_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $last_indexed = $wpdb->get_var( "SELECT MAX(indexed_at) FROM {$table} WHERE is_enabled = 1" );

        return $this->success( array(
            'kb_version'      => $this->compute_kb_version(),
            'items_count'     => $this->count_exportable_items( $key['scopes'] ?? array() ),
            'last_indexed_at' => $last_indexed ? mysql_to_rfc3339( $last_indexed ) : null,
            'site_name'       => get_bloginfo( 'name' ),
        ) );
    }

    // =========================================================================
    // GET /kb/export
    // =========================================================================

    /**
     * Route args for /kb/export.
     *
     * @since 2.3.0
     * @return array
     */
    private function get_export_args() {
        return array(
            'cursor'         => array(
                'type'              => 'string',
                'required'          => false,
                'sanitize_callback' => 'sanitize_text_field',
                'validate_callback' => function ( $value ) {
                    return '' === $value || ctype_digit( (string) $value );
                },
            ),
            'per_page'       => array(
                'type'              => 'integer',
                'required'          => false,
                'sanitize_callback' => 'absint',
            ),
            'modified_since' => array(
                'type'              => 'string',
                'required'          => false,
                'sanitize_callback' => 'sanitize_text_field',
            ),
        );
    }

    /**
     * Export merged documents (one item per parent document, not raw chunks).
     *
     * Respects the key's scopes (allowed post types) and never includes
     * draft/private content — only published posts (or non-post entries).
     *
     * @since 2.3.0
     * @param WP_REST_Request $request The REST request.
     * @return WP_REST_Response|WP_Error
     */
    public function get_export( $request ) {
        global $wpdb;

        $key    = $this->authenticated_key ?: array();
        $scopes = isset( $key['scopes'] ) && is_array( $key['scopes'] ) ? $key['scopes'] : array();

        $per_page = absint( $request->get_param( 'per_page' ) ) ?: 50;
        $per_page = min( $per_page, self::EXPORT_MAX_PER_PAGE );

        $cursor = (int) $request->get_param( 'cursor' );

        // Optional modified_since (ISO 8601) → MySQL datetime (GMT-agnostic:
        // updated_at is stored in site-local DB time, comparison is best-effort).
        $modified_since = '';
        $raw_since      = (string) $request->get_param( 'modified_since' );
        if ( '' !== $raw_since ) {
            $ts = strtotime( $raw_since );
            if ( false === $ts ) {
                return $this->error( __( 'Invalid modified_since date', 'boostbot' ), 400 );
            }
            $modified_since = gmdate( 'Y-m-d H:i:s', $ts );
        }

        list( $where_sql, $where_params ) = $this->build_export_where( $scopes, $modified_since );

        $table       = $this->knowledge_table();
        $posts_table = $wpdb->posts;

        // Total matching documents (without cursor).
        $count_sql = "SELECT COUNT(*) FROM {$table} k LEFT JOIN {$posts_table} p ON p.ID = k.content_id {$where_sql}";
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared -- Table identifiers sanitized; values bind via prepare().
        $total = (int) $wpdb->get_var( $where_params ? $wpdb->prepare( $count_sql, $where_params ) : $count_sql );

        // Page query: cursor-based (k.id ascending), fetch one extra row to detect more pages.
        $page_sql    = "SELECT k.id, k.content_type, k.content_id, k.title, k.summary, k.full_content, k.url, k.language, k.content_hash, k.updated_at
             FROM {$table} k
             LEFT JOIN {$posts_table} p ON p.ID = k.content_id
             {$where_sql} AND k.id > %d
             ORDER BY k.id ASC
             LIMIT %d";
        $page_params = array_merge( $where_params, array( $cursor, $per_page + 1 ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared -- Table identifiers sanitized; values bind via prepare().
        $rows = $wpdb->get_results( $wpdb->prepare( $page_sql, $page_params ), ARRAY_A );

        $has_more = count( $rows ) > $per_page;
        if ( $has_more ) {
            $rows = array_slice( $rows, 0, $per_page );
        }

        // Merge chunk texts for documents without full_content (single query).
        $missing_content_ids = array();
        foreach ( $rows as $row ) {
            if ( '' === trim( (string) ( $row['full_content'] ?? '' ) ) ) {
                $missing_content_ids[] = (int) $row['id'];
            }
        }
        $merged_chunks = $this->merge_chunk_texts( $missing_content_ids );

        $items       = array();
        $last_row_id = $cursor;
        foreach ( $rows as $row ) {
            $row_id      = (int) $row['id'];
            $last_row_id = $row_id;

            $content = (string) ( $row['full_content'] ?? '' );
            if ( '' === trim( $content ) && isset( $merged_chunks[ $row_id ] ) ) {
                $content = $merged_chunks[ $row_id ];
            }
            if ( '' === trim( $content ) ) {
                $content = (string) ( $row['summary'] ?? '' );
            }

            $content = wp_strip_all_tags( $content );
            if ( mb_strlen( $content ) > self::EXPORT_CONTENT_CAP ) {
                $content = mb_substr( $content, 0, self::EXPORT_CONTENT_CAP );
            }

            $hash = (string) ( $row['content_hash'] ?? '' );
            if ( '' === $hash ) {
                $hash = hash( 'sha256', $content );
            }

            $items[] = array(
                'id'          => 'doc_' . $row_id,
                'title'       => (string) ( $row['title'] ?? '' ),
                'url'         => urldecode( (string) ( $row['url'] ?? '' ) ),
                'content'     => $content,
                'source_type' => (string) ( $row['content_type'] ?? '' ),
                'lang'        => (string) ( $row['language'] ?? '' ),
                'hash'        => $hash,
                'updated_at'  => ! empty( $row['updated_at'] ) ? mysql_to_rfc3339( $row['updated_at'] ) : null,
            );
        }

        return $this->success( array(
            'items'       => $items,
            'next_cursor' => $has_more ? (string) $last_row_id : null,
            'kb_version'  => $this->compute_kb_version(),
            'total'       => $total,
        ) );
    }

    // =========================================================================
    // POST /kb/retrieve
    // =========================================================================

    /**
     * Route args for /kb/retrieve.
     *
     * @since 2.3.0
     * @return array
     */
    private function get_retrieve_args() {
        return array(
            'query' => array(
                'type'              => 'string',
                'required'          => true,
                'sanitize_callback' => 'sanitize_textarea_field',
            ),
            'top_k' => array(
                'type'              => 'integer',
                'required'          => false,
                'sanitize_callback' => 'absint',
            ),
            'lang'  => array(
                'type'              => 'string',
                'required'          => false,
                'sanitize_callback' => 'sanitize_text_field',
            ),
        );
    }

    /**
     * Live retrieval: wraps the existing KB retrieval pipeline
     * (semantic search + title fallback) with no LLM and no conversation state.
     *
     * top_k is clamped to the key's max_top_k. Scopes restrict content types.
     *
     * @since 2.3.0
     * @param WP_REST_Request $request The REST request.
     * @return WP_REST_Response|WP_Error
     */
    public function post_retrieve( $request ) {
        $t0 = microtime( true );

        $key    = $this->authenticated_key ?: array();
        $scopes = isset( $key['scopes'] ) && is_array( $key['scopes'] ) ? $key['scopes'] : array();

        $query = sanitize_textarea_field( (string) $request->get_param( 'query' ) );
        $query = mb_substr( trim( $query ), 0, 500 );
        if ( '' === $query ) {
            return $this->error( __( 'Query is required', 'boostbot' ), 400 );
        }

        $max_top_k = isset( $key['max_top_k'] ) ? (int) $key['max_top_k'] : 8;
        $max_top_k = max( 1, $max_top_k );
        $top_k     = absint( $request->get_param( 'top_k' ) ) ?: min( 5, $max_top_k );
        $top_k     = max( 1, min( $top_k, $max_top_k ) );

        $lang = sanitize_text_field( (string) $request->get_param( 'lang' ) );
        if ( '' !== $lang && class_exists( 'BoostBot_Language' ) ) {
            $lang = BoostBot_Language::sanitize( $lang );
        }

        if ( ! class_exists( 'BoostBot_Knowledge_Index' ) ) {
            return $this->error( __( 'Knowledge index is unavailable', 'boostbot' ), 503 );
        }

        try {
            $knowledge_index = new BoostBot_Knowledge_Index();

            $stats = $knowledge_index->get_stats();
            if ( empty( $stats['total'] ) ) {
                return $this->success( array(
                    'chunks'     => array(),
                    'kb_version' => $this->compute_kb_version(),
                    'took_ms'    => (int) round( ( microtime( true ) - $t0 ) * 1000 ),
                ) );
            }

            // Primary: semantic search over embedding chunks (scoped by content type).
            $chunks = $knowledge_index->semantic_search( $query, array(
                'limit'           => $top_k * 2, // Fetch extra — publish filtering may drop some.
                'prefilter_limit' => 40,
                'max_chunks'      => 300,
                'types'           => ! empty( $scopes ) ? $scopes : null,
                'language'        => '' !== $lang ? $lang : null,
            ) );

            // Fallback: title/summary/keyword search when no embeddings matched.
            if ( empty( $chunks ) ) {
                $chunks = $knowledge_index->fallback_title_search( $query, array(
                    'limit' => $top_k,
                ) );
            }

            $out = $this->format_retrieved_chunks( $chunks, $top_k, $scopes );

            return $this->success( array(
                'chunks'     => $out,
                'kb_version' => $this->compute_kb_version(),
                'took_ms'    => (int) round( ( microtime( true ) - $t0 ) * 1000 ),
            ) );
        } catch ( Exception $e ) {
            BoostBot_Logger::error( '[External KB] Retrieve error: ' . $e->getMessage() );
            return $this->error( __( 'Retrieval failed', 'boostbot' ), 500 );
        }
    }

    /**
     * Normalize raw pipeline results into the external chunk shape,
     * dropping anything that is not publicly published or out of scope.
     *
     * @since 2.3.0
     * @param array $chunks Raw results from semantic/fallback search.
     * @param int   $top_k  Max chunks to return.
     * @param array $scopes Allowed content types (empty = all).
     * @return array Formatted chunks.
     */
    private function format_retrieved_chunks( array $chunks, $top_k, array $scopes ) {
        if ( empty( $chunks ) ) {
            return array();
        }

        // Collect parent knowledge IDs for a single publish/scope validation query.
        $knowledge_ids = array();
        foreach ( $chunks as $chunk ) {
            $kid = (int) ( $chunk['knowledge_id'] ?? $chunk['id'] ?? 0 );
            if ( $kid > 0 && ! in_array( $kid, $knowledge_ids, true ) ) {
                $knowledge_ids[] = $kid;
            }
        }

        $allowed_map = $this->get_public_knowledge_map( $knowledge_ids, $scopes );

        $out = array();
        foreach ( $chunks as $chunk ) {
            if ( count( $out ) >= $top_k ) {
                break;
            }

            $kid = (int) ( $chunk['knowledge_id'] ?? $chunk['id'] ?? 0 );
            if ( ! isset( $allowed_map[ $kid ] ) ) {
                continue; // Not published / disabled / out of scope.
            }

            $content = (string) ( $chunk['chunk_text'] ?? $chunk['summary'] ?? '' );
            $content = wp_strip_all_tags( $content );
            if ( mb_strlen( $content ) > self::RETRIEVE_CHUNK_CAP ) {
                $content = mb_substr( $content, 0, self::RETRIEVE_CHUNK_CAP ) . '…';
            }
            if ( '' === trim( $content ) ) {
                continue;
            }

            $chunk_id = isset( $chunk['chunk_id'] ) ? 'chunk_' . (int) $chunk['chunk_id'] : 'doc_' . $kid;

            $out[] = array(
                'id'          => $chunk_id,
                'title'       => (string) ( $chunk['title'] ?? '' ),
                'url'         => urldecode( (string) ( $chunk['url'] ?? '' ) ),
                'content'     => $content,
                'score'       => round( (float) ( $chunk['score'] ?? $chunk['relevance_score'] ?? 0 ), 4 ),
                'source_type' => $allowed_map[ $kid ],
            );
        }

        return $out;
    }

    // =========================================================================
    // Admin key management
    // =========================================================================

    /**
     * List all keys (hashes are never included).
     *
     * @since 2.3.0
     * @param WP_REST_Request $request The REST request.
     * @return WP_REST_Response
     */
    public function list_keys( $request ) {
        return $this->success( array(
            'keys' => BoostBot_External_KB_Keys::get_all_public(),
        ) );
    }

    /**
     * Create a new key. The raw key is returned ONCE in this response
     * and can never be recovered afterwards.
     *
     * @since 2.3.0
     * @param WP_REST_Request $request The REST request.
     * @return WP_REST_Response|WP_Error
     */
    public function create_key( $request ) {
        $result = BoostBot_External_KB_Keys::create( array(
            'label'              => $request->get_param( 'label' ),
            'allowed_consumers'  => $request->get_param( 'allowed_consumers' ) ?: array(),
            'ip_allowlist'       => $request->get_param( 'ip_allowlist' ) ?: array(),
            'scopes'             => $request->get_param( 'scopes' ) ?: array( 'post', 'page', 'product' ),
            'max_top_k'          => $request->get_param( 'max_top_k' ) ?? 8,
            'rate_limit_per_min' => $request->get_param( 'rate_limit_per_min' ) ?? 30,
            'export_enabled'     => $request->get_param( 'export_enabled' ) ?? true,
            'retrieve_enabled'   => $request->get_param( 'retrieve_enabled' ) ?? true,
        ) );

        if ( is_wp_error( $result ) ) {
            return $result;
        }

        return $this->success( array(
            'key'     => $result['record'],
            'raw_key' => $result['raw_key'], // Shown once — never stored, never returned again.
        ), 201 );
    }

    /**
     * Update a key's specification or enabled state.
     *
     * @since 2.3.0
     * @param WP_REST_Request $request The REST request.
     * @return WP_REST_Response|WP_Error
     */
    public function update_key( $request ) {
        $id = sanitize_text_field( (string) $request->get_param( 'id' ) );

        $args = array();
        foreach ( array( 'label', 'allowed_consumers', 'ip_allowlist', 'scopes', 'max_top_k', 'rate_limit_per_min', 'export_enabled', 'retrieve_enabled', 'enabled' ) as $field ) {
            $value = $request->get_param( $field );
            if ( null !== $value ) {
                $args[ $field ] = $value;
            }
        }

        $result = BoostBot_External_KB_Keys::update( $id, $args );
        if ( is_wp_error( $result ) ) {
            return $result;
        }

        return $this->success( array( 'key' => $result ) );
    }

    /**
     * Delete a key.
     *
     * @since 2.3.0
     * @param WP_REST_Request $request The REST request.
     * @return WP_REST_Response|WP_Error
     */
    public function delete_key( $request ) {
        $id = sanitize_text_field( (string) $request->get_param( 'id' ) );

        if ( ! BoostBot_External_KB_Keys::delete( $id ) ) {
            return $this->not_found( __( 'Key', 'boostbot' ) );
        }

        return $this->success( array( 'deleted' => true ) );
    }

    // =========================================================================
    // Internal helpers
    // =========================================================================

    /**
     * Sanitized knowledge index table name.
     *
     * @since 2.3.0
     * @return string
     */
    private function knowledge_table() {
        global $wpdb;
        return preg_replace( '/[^A-Za-z0-9_]/', '', $wpdb->prefix . 'boostbot_knowledge_index' );
    }

    /**
     * Sanitized knowledge chunks table name.
     *
     * @since 2.3.0
     * @return string
     */
    private function chunks_table() {
        global $wpdb;
        return preg_replace( '/[^A-Za-z0-9_]/', '', $wpdb->prefix . 'boostbot_knowledge_chunks' );
    }

    /**
     * Compute a cheap KB version fingerprint.
     *
     * Derived from the enabled document count and the latest updated_at —
     * any indexing change (add/update/remove) produces a new version.
     * Complements the per-document SHA-256 content_hash used by
     * deep_incremental change detection.
     *
     * @since 2.3.0
     * @return string 16-char version fingerprint.
     */
    private function compute_kb_version() {
        global $wpdb;

        $table = $this->knowledge_table();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $row = $wpdb->get_row( "SELECT COUNT(*) AS cnt, MAX(updated_at) AS latest FROM {$table} WHERE is_enabled = 1" );

        $cnt    = $row ? (int) $row->cnt : 0;
        $latest = $row && $row->latest ? (string) $row->latest : '';

        return substr( hash( 'sha256', $cnt . '|' . $latest ), 0, 16 );
    }

    /**
     * Build the shared WHERE clause for exportable documents.
     *
     * Rules: enabled in index, within key scopes, and — for post-backed
     * entries — the parent post must exist and be published. Draft/private
     * content is excluded even if it is still present in the index.
     *
     * @since 2.3.0
     * @param array  $scopes         Allowed content types (empty = all).
     * @param string $modified_since MySQL datetime or '' for no filter.
     * @return array { 0: string WHERE sql, 1: array params }
     */
    private function build_export_where( array $scopes, $modified_since ) {
        $where  = array( 'k.is_enabled = 1' );
        $params = array();

        // Public-only: non-post entries pass; post-backed entries must be published.
        $where[] = "( k.content_id IS NULL OR k.content_id = 0 OR ( p.ID IS NOT NULL AND p.post_status = 'publish' ) )";

        if ( ! empty( $scopes ) ) {
            $placeholders = implode( ',', array_fill( 0, count( $scopes ), '%s' ) );
            $where[]      = "k.content_type IN ({$placeholders})";
            foreach ( $scopes as $scope ) {
                $params[] = $scope;
            }
        }

        if ( '' !== $modified_since ) {
            $where[]  = 'k.updated_at >= %s';
            $params[] = $modified_since;
        }

        return array( 'WHERE ' . implode( ' AND ', $where ), $params );
    }

    /**
     * Count exportable documents for a key's scopes.
     *
     * @since 2.3.0
     * @param array $scopes Allowed content types.
     * @return int
     */
    private function count_exportable_items( array $scopes ) {
        global $wpdb;

        list( $where_sql, $params ) = $this->build_export_where( is_array( $scopes ) ? $scopes : array(), '' );

        $table = $this->knowledge_table();
        $sql   = "SELECT COUNT(*) FROM {$table} k LEFT JOIN {$wpdb->posts} p ON p.ID = k.content_id {$where_sql}";

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared -- Table identifiers sanitized; values bind via prepare().
        return (int) $wpdb->get_var( $params ? $wpdb->prepare( $sql, $params ) : $sql );
    }

    /**
     * Merge chunk texts per parent document for documents lacking full_content.
     *
     * @since 2.3.0
     * @param array $knowledge_ids Parent document IDs.
     * @return array Map of knowledge_id => merged plain text.
     */
    private function merge_chunk_texts( array $knowledge_ids ) {
        global $wpdb;

        $knowledge_ids = array_values( array_filter( array_map( 'intval', $knowledge_ids ) ) );
        if ( empty( $knowledge_ids ) ) {
            return array();
        }

        $chunks_table = $this->chunks_table();
        $placeholders = implode( ',', array_fill( 0, count( $knowledge_ids ), '%d' ) );

        $sql = "SELECT knowledge_id, chunk_index, chunk_text
                FROM {$chunks_table}
                WHERE knowledge_id IN ({$placeholders})
                ORDER BY knowledge_id ASC, chunk_index ASC";

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared -- Table identifier sanitized; values bind via prepare().
        $rows = $wpdb->get_results( $wpdb->prepare( $sql, $knowledge_ids ), ARRAY_A );

        $merged = array();
        foreach ( (array) $rows as $row ) {
            $kid  = (int) $row['knowledge_id'];
            $text = trim( (string) ( $row['chunk_text'] ?? '' ) );
            if ( '' === $text ) {
                continue;
            }
            if ( ! isset( $merged[ $kid ] ) ) {
                $merged[ $kid ] = $text;
            } elseif ( mb_strlen( $merged[ $kid ] ) < self::EXPORT_CONTENT_CAP ) {
                $merged[ $kid ] .= "\n\n" . $text;
            }
        }

        return $merged;
    }

    /**
     * Get a map of knowledge_id => content_type for entries that are
     * enabled, publicly published, and within the allowed scopes.
     *
     * @since 2.3.0
     * @param array $knowledge_ids Candidate parent document IDs.
     * @param array $scopes        Allowed content types (empty = all).
     * @return array Map of allowed knowledge_id => content_type.
     */
    private function get_public_knowledge_map( array $knowledge_ids, array $scopes ) {
        global $wpdb;

        $knowledge_ids = array_values( array_filter( array_map( 'intval', $knowledge_ids ) ) );
        if ( empty( $knowledge_ids ) ) {
            return array();
        }

        $table        = $this->knowledge_table();
        $placeholders = implode( ',', array_fill( 0, count( $knowledge_ids ), '%d' ) );
        $params       = $knowledge_ids;

        $scope_sql = '';
        if ( ! empty( $scopes ) ) {
            $scope_placeholders = implode( ',', array_fill( 0, count( $scopes ), '%s' ) );
            $scope_sql          = " AND k.content_type IN ({$scope_placeholders})";
            foreach ( $scopes as $scope ) {
                $params[] = $scope;
            }
        }

        $sql = "SELECT k.id, k.content_type
                FROM {$table} k
                LEFT JOIN {$wpdb->posts} p ON p.ID = k.content_id
                WHERE k.id IN ({$placeholders})
                  AND k.is_enabled = 1
                  AND ( k.content_id IS NULL OR k.content_id = 0 OR ( p.ID IS NOT NULL AND p.post_status = 'publish' ) )
                  {$scope_sql}";

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared -- Table identifiers sanitized; values bind via prepare().
        $rows = $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A );

        $map = array();
        foreach ( (array) $rows as $row ) {
            $map[ (int) $row['id'] ] = (string) $row['content_type'];
        }

        return $map;
    }
}
