<?php

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

class Niwa_AI_Content_Store implements Niwa_AI_Content_Store_Interface {
    const TABLE_NAME = 'niwa_ai_content_index';

    /**
     * Portable binary embedding representation: IEEE-754 float32, little-endian.
     *
     * The JSON column remains the rollback/read-compatibility source during the
     * transition. No dimensionality reduction is performed by this codec.
     */
    const EMBEDDING_BINARY_FORMAT = 'f32le-v1';
    const EMBEDDING_FLOAT_BYTES   = 4;

    /** @var array<string,bool> Per-request schema capability cache by table. */
    private $binary_embedding_columns_available = array();

    /**
     * Canonical list of site (non-product) post types used for scope filtering.
     */
    public static function site_post_types() {
        return array(
            'page',
            'post',
            'elementor_library',
            'wp_template',
            'wp_template_part',
            'wp_block',
            'wp_navigation',
            'site_knowledge',
        );
    }

    /**
     * Cheap catalog fingerprint used to invalidate the precomputed frontend
     * semantic lexicon whenever indexed product evidence changes.
     */
    public function get_product_semantic_lexicon_hash() {
        global $wpdb;

        $table = $this->get_table_name();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $stats = $wpdb->get_row(
            "SELECT COUNT(*) AS row_count, COUNT(DISTINCT post_id) AS product_count, MAX(id) AS max_id, MAX(updated_at) AS max_updated, SUM(updated_at) AS updated_sum FROM {$table} WHERE post_type = 'product'",
            ARRAY_A
        );
        if ( ! is_array( $stats ) || intval( $stats['product_count'] ?? 0 ) <= 0 ) {
            return '';
        }

        return hash(
            'sha256',
            implode(
                '|',
                array(
                    intval( $stats['row_count'] ?? 0 ),
                    intval( $stats['product_count'] ?? 0 ),
                    intval( $stats['max_id'] ?? 0 ),
                    intval( $stats['max_updated'] ?? 0 ),
                    (string) ( $stats['updated_sum'] ?? '0' ),
                )
            )
        );
    }

    /**
     * Build a bounded, catalog-native facet snapshot for one-time LLM lexicon
     * generation. The live chat hot path reads only the resulting option and
     * the cheap fingerprint above; it never reparses the whole catalog.
     */
    public function get_product_semantic_lexicon_source( $max_rows = 1600 ) {
        global $wpdb;

        $max_rows = max( 1, min( 3000, intval( $max_rows ) ) );
        $table    = $this->get_table_name();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $rows = $wpdb->get_results(
            "SELECT categories_text, taxonomy_json, attributes_text, brand FROM {$table} WHERE post_type = 'product' ORDER BY updated_at DESC LIMIT {$max_rows}",
            ARRAY_A
        );
        $rows = is_array( $rows ) ? $rows : array();

        $categories = array();
        $attributes = array();
        $brands     = array();
        foreach ( $rows as $row ) {
            foreach ( preg_split( '/\s*,\s*/u', (string) ( $row['categories_text'] ?? '' ) ) as $category ) {
                $this->append_semantic_facet_value( $categories, $category, 220 );
            }
            $brand = trim( (string) ( $row['brand'] ?? '' ) );
            if ( '' !== $brand ) {
                $this->append_semantic_facet_value( $brands, $brand, 120 );
            }

            $taxonomy = json_decode( (string) ( $row['taxonomy_json'] ?? '' ), true );
            foreach ( is_array( $taxonomy ) ? $taxonomy : array() as $taxonomy_key => $terms ) {
                if ( 'product_cat' === $taxonomy_key ) {
                    foreach ( (array) $terms as $term ) {
                        $this->append_semantic_facet_value( $categories, is_array( $term ) ? ( $term['name'] ?? '' ) : '', 220 );
                    }
                    continue;
                }
                if ( 0 !== strpos( (string) $taxonomy_key, 'pa_' ) ) {
                    continue;
                }
				$label = $this->normalize_semantic_facet_label( str_replace( array( 'pa_', '-', '_' ), array( '', ' ', ' ' ), (string) $taxonomy_key ) );
                if ( '' === $label ) {
                    continue;
                }
                if ( ! isset( $attributes[ $label ] ) ) {
                    $attributes[ $label ] = array();
                }
                foreach ( (array) $terms as $term ) {
                    $this->append_semantic_facet_value( $attributes[ $label ], is_array( $term ) ? ( $term['name'] ?? '' ) : '', 140 );
                }
            }

            // The first segment contains the canonical parent attributes in
            // `label: value/value, label: value` form. Later segments repeat
            // variation evidence and are intentionally omitted here.
            $attribute_head = explode( ';', (string) ( $row['attributes_text'] ?? '' ), 2 )[0];
            if ( preg_match_all( '/(?:^|,\s*)([^,:;]{1,80}):\s*([^,;]+)/u', $attribute_head, $matches, PREG_SET_ORDER ) ) {
                foreach ( $matches as $match ) {
					$label = $this->normalize_semantic_facet_label( $match[1] );
                    if ( '' === $label ) {
                        continue;
                    }
                    if ( ! isset( $attributes[ $label ] ) ) {
                        $attributes[ $label ] = array();
                    }
                    foreach ( preg_split( '/\s*\/\s*/u', (string) $match[2] ) as $value ) {
                        $this->append_semantic_facet_value( $attributes[ $label ], $value, 140 );
                    }
                }
            }
        }

        natcasesort( $categories );
        natcasesort( $brands );
        ksort( $attributes, SORT_NATURAL | SORT_FLAG_CASE );
        foreach ( $attributes as &$values ) {
            natcasesort( $values );
            $values = array_values( $values );
        }
        unset( $values );

        return array(
            'source_hash' => $this->get_product_semantic_lexicon_hash(),
            'products'    => count( $rows ),
            'categories'  => array_values( $categories ),
            'attributes'  => $attributes,
            'brands'      => array_values( $brands ),
        );
    }

	private function append_semantic_facet_value( &$target, $value, $limit ) {
        $value = trim( wp_strip_all_tags( html_entity_decode( (string) $value, ENT_QUOTES | ENT_HTML5, 'UTF-8' ) ) );
        if ( '' === $value || mb_strlen( $value ) > 120 || count( $target ) >= intval( $limit ) ) {
            return;
		}
        $key = mb_strtolower( $value, 'UTF-8' );
        if ( function_exists( 'remove_accents' ) ) {
            $key = remove_accents( $key );
        }
        foreach ( $target as $existing ) {
            $existing_key = mb_strtolower( (string) $existing, 'UTF-8' );
            if ( function_exists( 'remove_accents' ) ) {
                $existing_key = remove_accents( $existing_key );
            }
            if ( $existing_key === $key ) {
                return;
            }
        }
		$target[] = $value;
	}

	/** Merge case/accent variants of the same attribute label before LLM input. */
	private function normalize_semantic_facet_label( $label ) {
		$label = mb_strtolower( trim( wp_strip_all_tags( (string) $label ) ), 'UTF-8' );
		if ( function_exists( 'remove_accents' ) ) {
			$label = remove_accents( $label );
		}
		$label = trim( preg_replace( '/\s+/u', ' ', (string) $label ) );
		return mb_strlen( $label ) > 80 ? mb_substr( $label, 0, 80 ) : $label;
	}

    private function get_table_name() {
        global $wpdb;
        return $wpdb->prefix . self::TABLE_NAME;
    }

    public function ensure_tables() {
        global $wpdb;

        $table_name      = $this->get_table_name();
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE {$table_name} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            post_id BIGINT UNSIGNED NOT NULL,
            post_type VARCHAR(20) NOT NULL DEFAULT 'post',
            doc_key VARCHAR(80) NOT NULL,
            title VARCHAR(500) NOT NULL DEFAULT '',
            slug VARCHAR(200) NOT NULL DEFAULT '',
            url VARCHAR(500) NOT NULL DEFAULT '',
            excerpt TEXT,
            short_desc TEXT,
            content_text MEDIUMTEXT,
            rlm_text MEDIUMTEXT,
            meta_json MEDIUMTEXT,
            taxonomy_json MEDIUMTEXT,
            image_json TEXT,
            price DECIMAL(10,2) DEFAULT NULL,
            sale_price DECIMAL(10,2) DEFAULT NULL,
            brand VARCHAR(255) DEFAULT '',
            categories_text VARCHAR(1000) DEFAULT '',
            tags_text VARCHAR(1000) DEFAULT '',
            attributes_text VARCHAR(2000) DEFAULT '',
            attributes_norm VARCHAR(2000) DEFAULT '',
            stock_status VARCHAR(20) DEFAULT 'instock',
            memory_text MEDIUMTEXT,
            embedding_json MEDIUMTEXT,
            embedding_blob BLOB,
            embedding_dimensions SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            embedding_format VARCHAR(16) NOT NULL DEFAULT '',
            embedding_hash CHAR(64) DEFAULT '',
            embedding_updated_at INT UNSIGNED DEFAULT 0,
            token_estimate INT UNSIGNED DEFAULT 0,
            indexed_at INT UNSIGNED NOT NULL,
            updated_at INT UNSIGNED NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY doc_key (doc_key),
            KEY post_id (post_id),
            KEY post_type (post_type),
            KEY price (price),
            KEY brand (brand(50)),
            FULLTEXT KEY ft_search (title, short_desc, content_text, categories_text, tags_text, attributes_text, brand)
        ) ENGINE=InnoDB {$charset_collate};";

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

        // dbDelta() can return without throwing while an ALTER was rejected.
        // Invalidate only this blog/table and verify the actual resulting
        // columns before callers advance any schema-version stamp.
        unset( $this->binary_embedding_columns_available[ $table_name ] );
        return true === $this->get_binary_embedding_schema_capability();
    }

    public function upsert( $row ) {
        if ( empty( $row ) || ! is_array( $row ) ) {
            return false;
        }

        return $this->upsert_batch( array( $row ) );
    }

    public function upsert_batch( $rows ) {
        global $wpdb;

        if ( empty( $rows ) || ! is_array( $rows ) ) {
            return false;
        }

        $table = $this->get_table_name();
        $binary_schema = $this->get_binary_embedding_schema_capability();
        if ( null === $binary_schema ) {
            // A transient SHOW COLUMNS failure is not proof of a legacy
            // schema. Abort rather than update the hash while leaving a
            // possibly existing BLOB paired with older content.
            return false;
        }

        $columns = array(
            'post_id',
            'post_type',
            'doc_key',
            'title',
            'slug',
            'url',
            'excerpt',
            'short_desc',
            'content_text',
            'rlm_text',
            'meta_json',
            'taxonomy_json',
            'image_json',
            'price',
            'sale_price',
            'brand',
            'categories_text',
            'tags_text',
            'attributes_text',
            'attributes_norm',
            'stock_status',
            'memory_text',
            'embedding_json',
            'embedding_hash',
            'embedding_updated_at',
            'token_estimate',
            'indexed_at',
            'updated_at',
        );

        if ( true === $binary_schema ) {
            $embedding_json_position = array_search( 'embedding_json', $columns, true );
            array_splice(
                $columns,
                false === $embedding_json_position ? count( $columns ) : $embedding_json_position + 1,
                0,
                array( 'embedding_blob', 'embedding_dimensions', 'embedding_format' )
            );
        }

        $now = time();

        $value_sets = array();
        foreach ( $rows as $row ) {
            if ( empty( $row['doc_key'] ) ) {
                continue;
            }

            $normalized = $this->normalize_row( $row, $now );
            $tokens = array();
            foreach ( $columns as $col ) {
                $val = array_key_exists( $col, $normalized ) ? $normalized[ $col ] : null;
                if ( null === $val ) {
                    $tokens[] = 'NULL';
                    continue;
                }
                $tokens[] = $this->prepare_value( $col, $val );
            }
            $value_sets[] = '(' . implode( ', ', $tokens ) . ')';
        }

        if ( empty( $value_sets ) ) {
            return false;
        }

        $updates = array();
        foreach ( $columns as $col ) {
            if ( 'embedding_json' === $col ) {
                $updates[] = "embedding_json = IF(VALUES(embedding_hash) = embedding_hash, embedding_json, NULL)";
                continue;
            }
            if ( 'embedding_blob' === $col ) {
                $updates[] = "embedding_blob = IF(VALUES(embedding_hash) = embedding_hash, embedding_blob, NULL)";
                continue;
            }
            if ( 'embedding_dimensions' === $col ) {
                $updates[] = "embedding_dimensions = IF(VALUES(embedding_hash) = embedding_hash, embedding_dimensions, 0)";
                continue;
            }
            if ( 'embedding_format' === $col ) {
                $updates[] = "embedding_format = IF(VALUES(embedding_hash) = embedding_hash, embedding_format, '')";
                continue;
            }
            if ( 'embedding_updated_at' === $col ) {
                $updates[] = "embedding_updated_at = IF(VALUES(embedding_hash) = embedding_hash, embedding_updated_at, 0)";
                continue;
            }
            $updates[] = $col . ' = VALUES(' . $col . ')';
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $sql = "INSERT INTO {$table} (" . implode( ', ', $columns ) . ") VALUES " . implode( ', ', $value_sets ) . " ON DUPLICATE KEY UPDATE " . implode( ', ', $updates );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        return (bool) $wpdb->query( $sql );
    }

    public function delete( $doc_key ) {
        global $wpdb;

        if ( empty( $doc_key ) ) {
            return false;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        return (bool) $wpdb->delete( $this->get_table_name(), array( 'doc_key' => (string) $doc_key ), array( '%s' ) );
    }

    public function delete_by_post_type( $post_type ) {
        global $wpdb;
        if ( empty( $post_type ) ) {
            return false;
        }
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        return (bool) $wpdb->delete( $this->get_table_name(), array( 'post_type' => (string) $post_type ), array( '%s' ) );
    }

    /**
     * Delete all rows whose doc_key starts with the given prefix.
     *
     * Used to remove all chunks of a document (e.g. page:42, page:42:c0, page:42:c1).
     *
     * @param string $prefix The doc_key prefix to match (e.g. "page:42").
     * @return bool
     */
    public function delete_by_doc_key_prefix( $prefix ) {
        global $wpdb;
        if ( empty( $prefix ) ) {
            return false;
        }
        $table = $this->get_table_name();
        $like  = $wpdb->esc_like( (string) $prefix ) . '%';
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery
        return (bool) $wpdb->query(
            $wpdb->prepare( "DELETE FROM {$table} WHERE doc_key LIKE %s", $like )
        );
    }

    public function fulltext_search( $query, $limit = 12, $filters = array() ) {
        global $wpdb;

        $query = trim( (string) $query );
        if ( '' === $query ) {
            return array();
        }

        $limit = max( 1, min( 30, intval( $limit ) ) );

        $table = $this->get_table_name();
        $where = array();
        $params = array();

        $where[]  = 'MATCH(title, short_desc, content_text, categories_text, tags_text, attributes_text, brand) AGAINST (%s IN NATURAL LANGUAGE MODE)';
        $params[] = $query;

        if ( ! empty( $filters['post_type'] ) ) {
            $post_type = $filters['post_type'];
            if ( is_array( $post_type ) ) {
                $in = implode( ', ', array_fill( 0, count( $post_type ), '%s' ) );
                $where[] = "post_type IN ({$in})";
                foreach ( $post_type as $pt ) {
                    $params[] = $pt;
                }
            } else {
                $where[] = 'post_type = %s';
                $params[] = (string) $post_type;
            }
        }

        if ( ! empty( $filters['brand'] ) ) {
            $brands = is_array( $filters['brand'] ) ? $filters['brand'] : array( $filters['brand'] );
            $brands = array_values( array_filter( $brands ) );
            if ( ! empty( $brands ) ) {
                $in = implode( ', ', array_fill( 0, count( $brands ), '%s' ) );
                $where[] = "brand IN ({$in})";
                foreach ( $brands as $b ) {
                    $params[] = $b;
                }
            }
        }

        if ( isset( $filters['price_min'] ) && is_numeric( $filters['price_min'] ) ) {
            $where[]  = 'price >= %f';
            $params[] = floatval( $filters['price_min'] );
        }

        if ( isset( $filters['price_max'] ) && is_numeric( $filters['price_max'] ) ) {
            $where[]  = 'price <= %f';
            $params[] = floatval( $filters['price_max'] );
        }

        if ( ! empty( $filters['categories'] ) ) {
            $cats = is_array( $filters['categories'] ) ? $filters['categories'] : array( $filters['categories'] );
            $cats = array_values( array_filter( $cats ) );
            if ( ! empty( $cats ) ) {
                $cat_clauses = array();
                foreach ( $cats as $cat ) {
                    $cat_clauses[] = 'categories_text LIKE %s';
                    $params[]      = '%' . $wpdb->esc_like( (string) $cat ) . '%';
                }
                $where[] = '(' . implode( ' OR ', $cat_clauses ) . ')';
            }
        }

        if ( ! empty( $filters['attributes_norm'] ) ) {
            $attrs = is_array( $filters['attributes_norm'] ) ? $filters['attributes_norm'] : array( $filters['attributes_norm'] );
            $attrs = array_values( array_filter( $attrs ) );
            if ( ! empty( $attrs ) ) {
                foreach ( $attrs as $attr ) {
                    $where[]  = 'attributes_norm LIKE %s';
                    $params[] = '%' . $wpdb->esc_like( (string) $attr ) . '%';
                }
            }
        }

        $where_sql = implode( ' AND ', $where );

        $sql = "SELECT *, MATCH(title, short_desc, content_text, categories_text, tags_text, attributes_text, brand) AGAINST (%s IN NATURAL LANGUAGE MODE) AS _score
            FROM {$table}
            WHERE {$where_sql}
            ORDER BY _score DESC
            LIMIT {$limit}";

        array_unshift( $params, $query );

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $prepared = $wpdb->prepare( $sql, $params );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $rows = $wpdb->get_results( $prepared, ARRAY_A );

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

    public function like_search( $query, $limit = 12, $post_type = null ) {
        global $wpdb;

        $query = trim( (string) $query );
        if ( '' === $query ) {
            return array();
        }

        $terms = preg_split( '/\s+/u', $this->normalize_text( $query ) );
        $terms = array_values( array_filter( $terms ) );
        if ( empty( $terms ) ) {
            return array();
        }

        $limit = max( 1, min( 30, intval( $limit ) ) );
        $table = $this->get_table_name();

        $score_parts  = array();
        $where_parts  = array();
        $score_params = array();
        $where_params = array();
        $type_clauses = array();
        $type_params  = array();

        // Post type filter — supports null (all types), string (one type), array (multiple types).
        if ( is_array( $post_type ) ) {
            $types = array_values( array_filter( array_map( 'strval', $post_type ) ) );
            if ( ! empty( $types ) ) {
                $in = implode( ', ', array_fill( 0, count( $types ), '%s' ) );
                $type_clauses[] = "post_type IN ({$in})";
                foreach ( $types as $t ) {
                    $type_params[] = $t;
                }
            }
        } elseif ( null !== $post_type && '' !== $post_type ) {
            $type_clauses[] = 'post_type = %s';
            $type_params[]  = (string) $post_type;
        }

        foreach ( $terms as $term ) {
            if ( mb_strlen( $term ) < 2 ) {
                continue;
            }
            foreach ( $this->build_like_term_variants( $term ) as $variant ) {
                $like = '%' . $wpdb->esc_like( $variant['text'] ) . '%';
                $score_parts[]  = floatval( $variant['weight'] ) . ' * CASE WHEN rlm_text LIKE %s THEN 1 ELSE 0 END';
                $where_parts[]  = 'rlm_text LIKE %s';
                $score_params[] = $like;
                $where_params[] = $like;
            }
        }

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

        $score_sql = implode( ' + ', $score_parts );
        $like_sql  = '(' . implode( ' OR ', $where_parts ) . ')';

        $where_all = $like_sql;
        if ( ! empty( $type_clauses ) ) {
            $where_all = implode( ' AND ', $type_clauses ) . ' AND ' . $like_sql;
        }

        // SELECT score placeholders occur before WHERE placeholders. Keep the
        // parameter order aligned with the generated SQL when post_type is set.
        $params = array_merge( $score_params, $type_params, $where_params );

        $sql = "SELECT *, ({$score_sql}) AS _score FROM {$table} WHERE {$where_all} HAVING _score > 0 ORDER BY _score DESC LIMIT {$limit}";

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $prepared = $wpdb->prepare( $sql, $params );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $rows = $wpdb->get_results( $prepared, ARRAY_A );

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

    /**
     * Resolve several lexical variants with one table scan.
     *
     * Each query keeps its own weighted LIKE score and result ordering; the
     * outer query only builds a shared candidate pool. This preserves the
     * per-variant recall contract without repeating the same catalog scan.
     */
    public function like_search_many( $queries, $limit = 30, $post_type = null ) {
        global $wpdb;

        $unique_queries = array();
        $seen           = array();
        foreach ( (array) $queries as $query ) {
            $query = trim( (string) $query );
            $key   = $this->normalize_text( $query );
            if ( '' !== $query && '' !== $key && ! isset( $seen[ $key ] ) ) {
                $seen[ $key ]    = true;
                $unique_queries[] = $query;
            }
        }
        if ( empty( $unique_queries ) ) {
            return array();
        }

        $limit = max( 1, min( 30, intval( $limit ) ) );
        $table = $this->get_table_name();
        $query_variants = array();
        $common_variants = array();
        foreach ( $unique_queries as $index => $query ) {
            $query_variants[ $index ] = array();
            $terms = array_values( array_filter( preg_split( '/\s+/u', $this->normalize_text( $query ) ) ) );
            foreach ( $terms as $term ) {
                if ( mb_strlen( $term ) < 2 ) {
                    continue;
                }
                foreach ( $this->build_like_term_variants( $term ) as $variant ) {
                    $text   = (string) $variant['text'];
                    $weight = floatval( $variant['weight'] );
                    $query_variants[ $index ][ $text ] = max( $weight, floatval( $query_variants[ $index ][ $text ] ?? 0 ) );
                    $common_variants[ $text ]          = max( $weight, floatval( $common_variants[ $text ] ?? 0 ) );
                }
            }
        }
        if ( empty( $common_variants ) ) {
            return array_fill_keys( $unique_queries, array() );
        }

        $score_parts  = array();
        $where_parts  = array();
        $score_params = array();
        $where_params = array();
        foreach ( $common_variants as $text => $weight ) {
            $like = '%' . $wpdb->esc_like( $text ) . '%';
            $score_parts[]  = floatval( $weight ) . ' * CASE WHEN rlm_text LIKE %s THEN 1 ELSE 0 END';
            $where_parts[]  = 'rlm_text LIKE %s';
            $score_params[] = $like;
            $where_params[] = $like;
        }

        $type_clauses = array();
        $type_params  = array();
        if ( is_array( $post_type ) ) {
            $types = array_values( array_filter( array_map( 'strval', $post_type ) ) );
            if ( ! empty( $types ) ) {
                $type_clauses[] = 'post_type IN (' . implode( ', ', array_fill( 0, count( $types ), '%s' ) ) . ')';
                $type_params    = $types;
            }
        } elseif ( null !== $post_type && '' !== $post_type ) {
            $type_clauses[] = 'post_type = %s';
            $type_params[]  = (string) $post_type;
        }

        $where_sql = '(' . implode( ' OR ', $where_parts ) . ')';
        if ( ! empty( $type_clauses ) ) {
            $where_sql = implode( ' AND ', $type_clauses ) . ' AND ' . $where_sql;
        }
        $pool_limit = min( 140, max( 40, $limit * count( $unique_queries ) ) );
        $sql = "SELECT *, (" . implode( ' + ', $score_parts ) . ") AS _batch_like_score FROM {$table} WHERE {$where_sql} HAVING _batch_like_score > 0 ORDER BY _batch_like_score DESC LIMIT {$pool_limit}";

        // SELECT score placeholders precede type and WHERE placeholders.
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $prepared = $wpdb->prepare( $sql, array_merge( $score_params, $type_params, $where_params ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $rows = $wpdb->get_results( $prepared, ARRAY_A );
        $rows = is_array( $rows ) ? $rows : array();

        $results = array_fill_keys( $unique_queries, array() );
        foreach ( $unique_queries as $index => $query ) {
            if ( empty( $query_variants[ $index ] ) ) {
                continue;
            }
            foreach ( $rows as $row ) {
                $haystack = $this->normalize_text( isset( $row['rlm_text'] ) ? (string) $row['rlm_text'] : '' );
                $score = 0.0;
                foreach ( $query_variants[ $index ] as $text => $weight ) {
                    if ( '' !== $haystack && false !== mb_strpos( $haystack, $text ) ) {
                        $score += floatval( $weight );
                    }
                }
                if ( $score <= 0 ) {
                    continue;
                }
                unset( $row['_batch_like_score'] );
                $row['_score'] = $score;
                $results[ $query ][] = $row;
            }
            usort(
                $results[ $query ],
                static function ( $left, $right ) {
                    return floatval( $right['_score'] ?? 0 ) <=> floatval( $left['_score'] ?? 0 );
                }
            );
            $results[ $query ] = array_slice( $results[ $query ], 0, $limit );
        }

        return $results;
    }

    /**
     * Build language-neutral suffix-tolerant variants for lexical recall.
     * Exact tokens keep the strongest weight; progressively shorter prefixes
     * only widen candidate discovery and never decide the final answer.
     */
    private function build_like_term_variants( $term ) {
        $term = trim( (string) $term );
        if ( '' === $term ) {
            return array();
        }

        $variants = array(
            array( 'text' => $term, 'weight' => 3.0 ),
        );
        $length = mb_strlen( $term );
        if ( $length >= 4 && ! preg_match( '/\p{N}/u', $term ) ) {
            $variants[] = array( 'text' => mb_substr( $term, 0, $length - 1 ), 'weight' => 1.25 );
        }
        if ( $length >= 7 && ! preg_match( '/\p{N}/u', $term ) ) {
            $variants[] = array( 'text' => mb_substr( $term, 0, $length - 2 ), 'weight' => 0.6 );
        }

        $out  = array();
        $seen = array();
        foreach ( $variants as $variant ) {
            $variant_length = mb_strlen( $variant['text'] );
            if ( $variant_length < 2 || ( $variant['text'] !== $term && $variant_length < 3 ) || isset( $seen[ $variant['text'] ] ) ) {
                continue;
            }
            $seen[ $variant['text'] ] = true;
            $out[] = $variant;
        }

        return $out;
    }

    public function get_product_detail( $post_id ) {
        global $wpdb;
        $table = $this->get_table_name();

        // Fetch all chunks for this post, ordered by doc_key (base row first, then :c0, :c1...).
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery
        $rows = $wpdb->get_results(
            $wpdb->prepare( "SELECT * FROM {$table} WHERE post_id = %d ORDER BY doc_key ASC", intval( $post_id ) ),
            ARRAY_A
        );

        if ( ! is_array( $rows ) || empty( $rows ) ) {
            return array();
        }

        // Single row — return as-is.
        if ( 1 === count( $rows ) ) {
            return $rows[0];
        }

        // Multiple chunks — use the first row for all metadata, concatenate text fields.
        $combined = $rows[0];
        $content_parts = array();
        $rlm_parts     = array();

        foreach ( $rows as $r ) {
            if ( ! empty( $r['content_text'] ) ) {
                $content_parts[] = trim( (string) $r['content_text'] );
            }
            if ( ! empty( $r['rlm_text'] ) ) {
                $rlm_parts[] = trim( (string) $r['rlm_text'] );
            }
        }

        $combined['content_text'] = implode( "\n\n", $content_parts );
        $combined['rlm_text']     = implode( "\n\n", $rlm_parts );

        return $combined;
    }

    public function get_products_by_ids( $ids ) {
        global $wpdb;

        $ids = array_map( 'intval', is_array( $ids ) ? $ids : array() );
        $ids = array_values( array_filter( $ids ) );
        if ( empty( $ids ) ) {
            return array();
        }

        $table = $this->get_table_name();
        $in = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );
        $order = implode( ',', $ids );

        $sql = "SELECT * FROM {$table} WHERE post_id IN ({$in}) ORDER BY FIELD(post_id, {$order})";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $prepared = $wpdb->prepare( $sql, $ids );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $rows = $wpdb->get_results( $prepared, ARRAY_A );

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

    public function get_store_overview() {
        $cached = get_transient( 'niwa_ai_store_overview' );
        if ( is_array( $cached ) ) {
            return $cached;
        }

        $overview = array(
            'total_products' => $this->count_by_type( 'product' ),
            'price_min' => null,
            'price_max' => null,
            'brands' => array(),
            'categories' => array(),
            'attributes' => array(),
        );

        $range = $this->get_price_range();
        $overview['price_min'] = isset( $range['min'] ) ? $range['min'] : null;
        $overview['price_max'] = isset( $range['max'] ) ? $range['max'] : null;

        $overview['brands'] = $this->get_top_brands( 10 );

        if ( function_exists( 'get_terms' ) ) {
            $cats = get_terms(
                array(
                    'taxonomy' => 'product_cat',
                    'hide_empty' => true,
                    'number' => 12,
                )
            );
            if ( ! is_wp_error( $cats ) && ! empty( $cats ) ) {
                foreach ( $cats as $c ) {
                    $overview['categories'][] = array(
                        'name'  => $c->name,
                        'count' => intval( $c->count ),
                    );
                }
            }

            $overview['attributes'] = array(
                'colors' => $this->count_terms( 'pa_color' ),
                'sizes' => $this->count_terms( 'pa_size' ),
                'materials' => $this->count_terms( 'pa_material' ),
            );
        }

        set_transient( 'niwa_ai_store_overview', $overview, 5 * MINUTE_IN_SECONDS );
        return $overview;
    }

    public function count_by_type( $post_type ) {
        global $wpdb;
        $table = $this->get_table_name();

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $count = $wpdb->get_var(
            $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE post_type = %s", (string) $post_type )
        );

        return intval( $count );
    }

    public function get_name_to_id_map() {
        global $wpdb;

        $cached = get_transient( 'niwa_ai_name_to_id_map' );
        if ( is_array( $cached ) ) {
            return $cached;
        }

        $table = $this->get_table_name();

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $rows = $wpdb->get_results(
            $wpdb->prepare( "SELECT post_id, title FROM {$table} WHERE post_type = %s", 'product' ),
            ARRAY_A
        );

        $map = array();
        if ( ! empty( $rows ) ) {
            foreach ( $rows as $r ) {
                $title = isset( $r['title'] ) ? (string) $r['title'] : '';
                $id    = isset( $r['post_id'] ) ? intval( $r['post_id'] ) : 0;
                if ( '' === $title || $id <= 0 ) {
                    continue;
                }
                $norm = $this->normalize_text( $title );
                if ( '' !== $norm ) {
                    $map[ $norm ] = array(
                        'id'    => $id,
                        'title' => $title,
                    );
                }
            }
        }

        set_transient( 'niwa_ai_name_to_id_map', $map, 5 * MINUTE_IN_SECONDS );
        return $map;
    }

    /**
     * Get summaries of all non-product content for the site knowledge blob.
     * Returns title + short content snippet for each site_knowledge, page, and post entry.
     * Includes rlm_text fallback for richer snippets and doc_key for source grouping.
     */
    public function get_site_content_summaries( $snippet_length = 300 ) {
        global $wpdb;
        $table = $this->get_table_name();

        $site_types = array( 'site_knowledge', 'page', 'post', 'elementor_library' );
        $in         = implode( ', ', array_fill( 0, count( $site_types ), '%s' ) );

        $sql = "SELECT title, post_type, doc_key, content_text, rlm_text, short_desc, url
            FROM {$table}
            WHERE post_type IN ({$in})
            ORDER BY FIELD(post_type, 'site_knowledge', 'page', 'post', 'elementor_library'), title ASC";

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $prepared = $wpdb->prepare( $sql, $site_types );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $rows = $wpdb->get_results( $prepared, ARRAY_A );

        if ( ! is_array( $rows ) ) {
            return array();
        }

        $snippet_length = max( 50, min( 1500, intval( $snippet_length ) ) );
        $out = array();
        foreach ( $rows as $row ) {
            $title   = isset( $row['title'] ) ? trim( (string) $row['title'] ) : '';
            if ( '' === $title ) {
                continue;
            }

            $content = '';
            if ( ! empty( $row['short_desc'] ) ) {
                $content = trim( (string) $row['short_desc'] );
            }
            if ( '' === $content && ! empty( $row['content_text'] ) ) {
                $content = trim( (string) $row['content_text'] );
            }
            if ( '' === $content && ! empty( $row['rlm_text'] ) ) {
                $content = trim( (string) $row['rlm_text'] );
            }

            if ( mb_strlen( $content ) > $snippet_length ) {
                $content = rtrim( mb_substr( $content, 0, $snippet_length ) ) . '...';
            }

            $doc_key     = isset( $row['doc_key'] ) ? (string) $row['doc_key'] : '';
            $source_type = '';
            if ( preg_match( '/^site:([^:]+):/', $doc_key, $m ) ) {
                $source_type = $m[1];
            }

            $out[] = array(
                'title'       => $title,
                'type'        => isset( $row['post_type'] ) ? (string) $row['post_type'] : '',
                'source_type' => $source_type,
                'snippet'     => $content,
                'url'         => isset( $row['url'] ) ? (string) $row['url'] : '',
                'doc_key'     => $doc_key,
            );
        }

        return $out;
    }

    /**
     * Get the home page content from the site_knowledge index.
     *
     * Combines the home_page entry (WordPress post_content) with the
     * frontend_snapshot entry (full rendered HTML including footer/widgets)
     * to provide the richest possible home page summary.  Builder pages
     * (Elementor, WPBakery, etc.) often store only shortcodes/JSON in
     * post_content while the real visible text — including newsletter
     * signup, footer info sections, and membership banners — only appears
     * in the frontend snapshot.
     *
     * @param int $max_chars Maximum characters to return.
     * @return string Home page text or empty string.
     */
    public function get_home_page_summary( $max_chars = 1500 ) {
        // Primary: post_content based home page extraction.
        $home = $this->get_site_knowledge_by_source( 'home_page', $max_chars * 2 );

        // Secondary: full rendered HTML snapshot (includes footer, widgets, newsletter).
        $snapshot = $this->get_site_knowledge_by_source( 'frontend_snapshot', $max_chars * 2 );

        // If both exist, use the longer one (more complete).
        // The frontend snapshot is almost always richer for builder pages.
        if ( '' !== $home && '' !== $snapshot ) {
            $text = mb_strlen( $snapshot ) > mb_strlen( $home ) ? $snapshot : $home;
        } elseif ( '' !== $snapshot ) {
            $text = $snapshot;
        } elseif ( '' !== $home ) {
            $text = $home;
        } else {
            return '';
        }

        $max_chars = max( 100, intval( $max_chars ) );
        if ( mb_strlen( $text ) > $max_chars ) {
            $text = rtrim( mb_substr( $text, 0, $max_chars ) ) . '...';
        }

        return $text;
    }

    /**
     * Get the agent profile data from the site_knowledge index.
     *
     * @param int $max_chars Maximum characters to return.
     * @return string Agent profile text or empty string.
     */
    public function get_agent_profile_summary( $max_chars = 1500 ) {
        return $this->get_site_knowledge_by_source( 'agent_profile', $max_chars );
    }

    /**
     * Get the navigation menu structure from the site_knowledge index.
     *
     * @return string Menu text or empty string.
     */
    public function get_navigation_summary() {
        return $this->get_site_knowledge_by_source( 'menus', 2000 );
    }

    /**
     * Retrieve a site_knowledge entry by its source_type (from the doc_key pattern site:{source}:{n}).
     *
     * @param string $source   Source type key (e.g. home_page, agent_profile, menus).
     * @param int    $max_chars Max characters to return.
     * @return string
     */
    private function get_site_knowledge_by_source( $source, $max_chars = 1500 ) {
        global $wpdb;
        $table    = $this->get_table_name();
        $max_chars = max( 100, intval( $max_chars ) );

        $like_pattern = 'site:' . $wpdb->esc_like( (string) $source ) . ':%';

        // Fetch ALL rows (chunks) for this source, ordered by doc_key.
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT content_text, rlm_text FROM {$table} WHERE post_type = %s AND doc_key LIKE %s ORDER BY doc_key ASC",
                'site_knowledge',
                $like_pattern
            ),
            ARRAY_A
        );

        if ( ! is_array( $rows ) || empty( $rows ) ) {
            return '';
        }

        // Concatenate content from all chunks.
        $parts = array();
        foreach ( $rows as $row ) {
            $chunk = '';
            if ( ! empty( $row['content_text'] ) ) {
                $chunk = trim( (string) $row['content_text'] );
            }
            if ( '' === $chunk && ! empty( $row['rlm_text'] ) ) {
                $chunk = trim( (string) $row['rlm_text'] );
            }
            if ( '' !== $chunk ) {
                $parts[] = $chunk;
            }
        }

        $text = implode( "\n\n", $parts );

        if ( mb_strlen( $text ) > $max_chars ) {
            $text = rtrim( mb_substr( $text, 0, $max_chars ) ) . '...';
        }

        return $text;
    }

    public function get_pending_embeddings( $limit = 20 ) {
        global $wpdb;
        $limit = max( 1, min( 100, intval( $limit ) ) );
        $table = $this->get_table_name();

        // Legacy rows could carry memory_text with an empty hash. Derive the
        // target hash in SQL so update_embedding() can promote them once while
        // still using an exact empty-hash optimistic guard.
        $sql = "SELECT doc_key, memory_text,
                CASE WHEN embedding_hash = '' THEN SHA2(memory_text, 256) ELSE embedding_hash END AS embedding_hash
            FROM {$table}
            WHERE memory_text IS NOT NULL AND memory_text <> ''
              AND (embedding_json IS NULL OR embedding_json = '' OR embedding_hash = '')
            LIMIT {$limit}";

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $rows = $wpdb->get_results( $sql, ARRAY_A );
        return is_array( $rows ) ? $rows : array();
    }

    public function update_embedding( $doc_key, $embedding, $hash ) {
        global $wpdb;
        $hash = trim( (string) $hash );
        if ( empty( $doc_key ) || empty( $embedding ) || ! is_array( $embedding ) || '' === $hash ) {
            return false;
        }

        $binary = self::encode_embedding_binary( $embedding );
        if ( false === $binary ) {
            return false;
        }

        $json = wp_json_encode( array_values( $embedding ) );
        if ( false === $json || '' === $json ) {
            return false;
        }

        $binary_schema = $this->get_binary_embedding_schema_capability();
        if ( null === $binary_schema ) {
            return false;
        }
        $has_binary_columns = true === $binary_schema;
        $data = array(
            'embedding_json' => $json,
            'embedding_hash' => $hash,
            'embedding_updated_at' => time(),
        );
        $formats = array( '%s', '%s', '%d' );

        if ( $has_binary_columns ) {
            // Invalidate the previous BLOB in the same statement that publishes
            // the new JSON/hash. Readers can never pair an old vector with a
            // newly embedded document while the second write is in flight.
            $data['embedding_blob']       = null;
            $data['embedding_dimensions'] = 0;
            $data['embedding_format']     = '';
            $formats[] = '%s';
            $formats[] = '%d';
            $formats[] = '%s';
        }

        $where = array(
            'doc_key'        => (string) $doc_key,
            'embedding_hash' => $hash,
        );

        // Write the rollback-compatible JSON first. Sites whose schema upgrade
        // has not completed yet keep working and can be backfilled later.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $json_updated = $wpdb->update(
            $this->get_table_name(),
            $data,
            $where,
            $formats,
            array( '%s', '%s' )
        );
        if ( false === $json_updated ) {
            return false;
        }

        if ( 0 === intval( $json_updated ) && ! $this->embedding_hash_matches( $doc_key, $hash ) ) {
            // Explicit legacy bridge: get_pending_embeddings() derives a
            // SHA-256 target for rows whose current hash is empty. Only that
            // exact empty state may be promoted; a different non-empty hash is
            // a newer document revision and must never be overwritten.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $legacy_updated = $wpdb->update(
                $this->get_table_name(),
                $data,
                array(
                    'doc_key'        => (string) $doc_key,
                    'embedding_hash' => '',
                ),
                $formats,
                array( '%s', '%s' )
            );
            if ( false === $legacy_updated ) {
                return false;
            }
            if ( 0 === intval( $legacy_updated ) && ! $this->embedding_hash_matches( $doc_key, $hash ) ) {
                return false;
            }
        }

        if ( ! $has_binary_columns ) {
            return true;
        }

        // The hash predicate prevents an older concurrent writer from pairing
        // its vector with a newer document revision.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $binary_updated = $wpdb->update(
            $this->get_table_name(),
            array(
                'embedding_blob'       => $binary['blob'],
                'embedding_dimensions' => $binary['dimensions'],
                'embedding_format'     => $binary['format'],
            ),
            array(
                'doc_key'        => (string) $doc_key,
                'embedding_hash' => $hash,
            ),
            array( '%s', '%d', '%s' ),
            array( '%s', '%s' )
        );

        // JSON is a complete and readable embedding if the binary write is
        // temporarily unavailable. A resumable backfill repairs that row.
        if ( false === $binary_updated && function_exists( 'do_action' ) ) {
            do_action( 'niwa_ai_embedding_binary_write_failed', (string) $doc_key );
        }
        if ( false === $binary_updated || 0 === intval( $binary_updated ) ) {
            return $this->embedding_hash_matches( $doc_key, $hash );
        }
        return true;
    }

    public function get_embeddings_by_post_ids( $ids ) {
        global $wpdb;

        $ids = array_map( 'intval', is_array( $ids ) ? $ids : array() );
        $ids = array_values( array_filter( $ids ) );
        if ( empty( $ids ) ) {
            return array();
        }

        $table = $this->get_table_name();
        $in = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );

        $embedding_columns = $this->embedding_select_columns();
        $presence_sql      = $this->embedding_presence_sql();
        $sql = "SELECT post_id, doc_key, {$embedding_columns} FROM {$table} WHERE post_id IN ({$in}) AND {$presence_sql}";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $prepared = $wpdb->prepare( $sql, $ids );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $rows = $wpdb->get_results( $prepared, ARRAY_A );

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

    public function get_embedding_batch( $limit = 200, $cursor = array(), $filters = array() ) {
        global $wpdb;
        $limit = max( 1, min( 500, intval( $limit ) ) );
        $cursor = is_array( $cursor ) ? $cursor : array();
        $table = $this->get_table_name();

        $where = array( $this->embedding_presence_sql() );
        $params = array();

        if ( ! empty( $filters['post_type'] ) ) {
            if ( is_array( $filters['post_type'] ) ) {
                $types = array_values( array_filter( array_map( 'strval', $filters['post_type'] ) ) );
                if ( ! empty( $types ) ) {
                    $in = implode( ', ', array_fill( 0, count( $types ), '%s' ) );
                    $where[] = "post_type IN ({$in})";
                    foreach ( $types as $type ) {
                        $params[] = $type;
                    }
                }
            } else {
                $where[] = 'post_type = %s';
                $params[] = (string) $filters['post_type'];
            }
        }

        $cursor_post_id = isset( $cursor['post_id'] ) ? max( 0, intval( $cursor['post_id'] ) ) : 0;
        $cursor_doc_key = isset( $cursor['doc_key'] ) ? (string) $cursor['doc_key'] : '';
        if ( array_key_exists( 'post_id', $cursor ) && array_key_exists( 'doc_key', $cursor ) ) {
            $where[]  = '(post_id > %d OR (post_id = %d AND doc_key > %s))';
            $params[] = $cursor_post_id;
            $params[] = $cursor_post_id;
            $params[] = $cursor_doc_key;
        }

        $where_sql = implode( ' AND ', $where );
        $embedding_columns = $this->embedding_select_columns();
        $sql = "SELECT post_id, doc_key, title, url, price, sale_price, brand, categories_text, tags_text, attributes_text, short_desc, content_text, memory_text, {$embedding_columns}
            FROM {$table}
            WHERE {$where_sql}
            ORDER BY post_id ASC, doc_key ASC
            LIMIT {$limit}";

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $prepared = ! empty( $params ) ? $wpdb->prepare( $sql, $params ) : $sql;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $rows = $wpdb->get_results( $prepared, ARRAY_A );

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

    public function get_embedding_stats() {
        global $wpdb;
        $table = $this->get_table_name();

        $total = $wpdb->get_var(
            "SELECT COUNT(*) FROM {$table} WHERE memory_text IS NOT NULL AND memory_text <> ''"
        );
        $presence_sql = $this->embedding_presence_sql();
        $done = $wpdb->get_var(
            "SELECT COUNT(*) FROM {$table} WHERE memory_text IS NOT NULL AND memory_text <> '' AND {$presence_sql}"
        );

        $total = intval( $total );
        $done = intval( $done );
        $pending = max( 0, $total - $done );

        return array(
            'total' => $total,
            'done' => $done,
            'pending' => $pending,
        );
    }

    /**
     * Encode a vector without changing its dimensionality.
     *
     * Values are serialized as IEEE-754 float32 in an explicit little-endian
     * order. This is lossless relative to the float32 representation used for
     * cosine computation and avoids JSON parsing on the retrieval hot path.
     *
     * @param array $embedding Numeric embedding vector.
     * @return array|false {blob:string, dimensions:int, format:string}.
     */
    public static function encode_embedding_binary( $embedding ) {
        if ( empty( $embedding ) || ! is_array( $embedding ) ) {
            return false;
        }

        $dimensions = count( $embedding );
        // MySQL BLOB has a 65,535-byte ceiling. Keep the check explicit so a
        // future model cannot silently truncate a larger vector.
        if ( $dimensions > floor( 65535 / self::EMBEDDING_FLOAT_BYTES ) ) {
            return false;
        }

        $values = array();
        foreach ( array_values( $embedding ) as $value ) {
            if ( ! is_numeric( $value ) ) {
                return false;
            }
            $value = (float) $value;
            if ( ! is_finite( $value ) ) {
                return false;
            }
            $values[] = $value;
        }

        $blob = pack( 'g*', ...$values );
        if ( false === $blob || strlen( $blob ) !== $dimensions * self::EMBEDDING_FLOAT_BYTES ) {
            return false;
        }

        return array(
            'blob'       => $blob,
            'dimensions' => $dimensions,
            'format'     => self::EMBEDDING_BINARY_FORMAT,
        );
    }

    /**
     * Decode one row using binary first and legacy JSON as a safe fallback.
     *
     * The metadata and byte length must agree before unpacking. Corrupt,
     * partial or unknown-format BLOBs never shadow a valid legacy JSON vector.
     *
     * @param array $row Content-index row.
     * @return array Valid finite numeric vector, or an empty array.
     */
    public static function decode_embedding_row( $row ) {
        if ( ! is_array( $row ) ) {
            return array();
        }

        $blob       = isset( $row['embedding_blob'] ) ? $row['embedding_blob'] : null;
        $dimensions = isset( $row['embedding_dimensions'] ) ? intval( $row['embedding_dimensions'] ) : 0;
        $format     = isset( $row['embedding_format'] ) ? (string) $row['embedding_format'] : '';
        $byte_count = $dimensions * self::EMBEDDING_FLOAT_BYTES;

        if (
            is_string( $blob )
            && self::EMBEDDING_BINARY_FORMAT === $format
            && $dimensions > 0
            && $byte_count > 0
            && strlen( $blob ) === $byte_count
        ) {
            $decoded = unpack( 'g*', $blob );
            $decoded = is_array( $decoded ) ? array_values( $decoded ) : array();
            if ( count( $decoded ) === $dimensions && self::is_valid_embedding_vector( $decoded ) ) {
                return $decoded;
            }
        }

        return self::decode_embedding_json( isset( $row['embedding_json'] ) ? $row['embedding_json'] : '' );
    }

    /**
     * Fetch only the rollback JSON for the rare case where a projected BLOB
     * passes SQL metadata checks but fails finite-value validation in PHP.
     *
     * @param string $doc_key Content document key.
     * @return string Legacy JSON payload, or an empty string.
     */
    public function get_embedding_json_by_doc_key( $doc_key ) {
        global $wpdb;

        $doc_key = trim( (string) $doc_key );
        if ( '' === $doc_key ) {
            return '';
        }

        $table = $this->get_table_name();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared
        $json = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT embedding_json FROM {$table} WHERE doc_key = %s LIMIT 1",
                $doc_key
            )
        );

        return is_string( $json ) ? $json : '';
    }

    /**
     * Backfill legacy JSON rows in a bounded, resumable and idempotent batch.
     *
     * The caller persists next_cursor and invokes the method again. Re-running
     * from cursor zero is safe: rows with a valid current-format BLOB are
     * skipped. JSON is deliberately retained for rollback compatibility.
     *
     * @param int $limit    Maximum rows to inspect (1-500).
     * @param int $after_id Resume after this content-index primary key.
     * @return array Migration progress and failure details.
     */
    public function backfill_embedding_binary( $limit = 100, $after_id = 0 ) {
        global $wpdb;

        $limit    = max( 1, min( 500, intval( $limit ) ) );
        $after_id = max( 0, intval( $after_id ) );

        $binary_schema = $this->get_binary_embedding_schema_capability();
        if ( null === $binary_schema ) {
            return array(
                'migrated'    => 0,
                'failed'      => 0,
                'stale'       => 0,
                'processed'   => 0,
                'next_cursor' => $after_id,
                'done'        => true,
                'error'       => 'binary_schema_check_failed',
                'failed_ids'  => array(),
            );
        }

        if ( ! $binary_schema ) {
            return array(
                'migrated'   => 0,
                'failed'     => 0,
                'stale'      => 0,
                'processed'  => 0,
                'next_cursor'=> $after_id,
                // This batch cannot make forward progress. Mark it complete
                // with an error so the scheduler can apply bounded retries
                // instead of creating a tight endless cron loop.
                'done'       => true,
                'error'      => 'binary_columns_unavailable',
                'failed_ids' => array(),
            );
        }

        $table = $this->get_table_name();
        $sql   = "SELECT id, embedding_hash, embedding_json
            FROM {$table}
            WHERE id > %d
              AND embedding_json IS NOT NULL
              AND embedding_json <> ''
              AND NOT (
                  embedding_blob IS NOT NULL
                  AND embedding_dimensions > 0
                  AND OCTET_LENGTH(embedding_blob) = embedding_dimensions * " . self::EMBEDDING_FLOAT_BYTES . "
                  AND embedding_format = %s
              )
            ORDER BY id ASC
            LIMIT %d";

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $prepared = $wpdb->prepare( $sql, $after_id, self::EMBEDDING_BINARY_FORMAT, $limit );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $rows = $wpdb->get_results( $prepared, ARRAY_A );
        if ( ! is_array( $rows ) || ! empty( $wpdb->last_error ) ) {
            return array(
                'migrated'    => 0,
                'failed'      => 0,
                'stale'       => 0,
                'processed'   => 0,
                'next_cursor' => $after_id,
                'done'        => true,
                'error'       => 'binary_backfill_query_failed',
                'failed_ids'  => array(),
            );
        }

        $migrated  = 0;
        $stale     = 0;
        $failed_ids = array();
        $cursor    = $after_id;

        foreach ( $rows as $row ) {
            $id     = isset( $row['id'] ) ? intval( $row['id'] ) : 0;
            $cursor = max( $cursor, $id );
            $vector = self::decode_embedding_json( isset( $row['embedding_json'] ) ? $row['embedding_json'] : '' );
            $binary = self::encode_embedding_binary( $vector );

            if ( $id <= 0 || false === $binary ) {
                $failed_ids[] = $id;
                continue;
            }

            // Compare both hash and JSON so a concurrent re-embedding cannot
            // receive a stale BLOB from this migration worker.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $updated = $wpdb->update(
                $table,
                array(
                    'embedding_blob'       => $binary['blob'],
                    'embedding_dimensions' => $binary['dimensions'],
                    'embedding_format'     => $binary['format'],
                ),
                array(
                    'id'             => $id,
                    'embedding_hash' => isset( $row['embedding_hash'] ) ? (string) $row['embedding_hash'] : '',
                    'embedding_json' => isset( $row['embedding_json'] ) ? (string) $row['embedding_json'] : '',
                ),
                array( '%s', '%d', '%s' ),
                array( '%d', '%s', '%s' )
            );

            if ( false === $updated ) {
                $failed_ids[] = $id;
            } elseif ( 0 === intval( $updated ) ) {
                ++$stale;
            } else {
                ++$migrated;
            }
        }

        return array(
            'migrated'    => $migrated,
            'failed'      => count( $failed_ids ),
            'stale'       => $stale,
            'processed'   => count( $rows ),
            'next_cursor' => $cursor,
            'done'        => count( $rows ) < $limit,
            'error'       => '',
            'failed_ids'  => $failed_ids,
        );
    }

    public function migrate_from_option() {
        if ( ! defined( 'Niwa_AI_Index_Worker::OPTION_KEY' ) ) {
            return array( 'migrated' => 0 );
        }

        $docs = get_option( Niwa_AI_Index_Worker::OPTION_KEY, array() );
        if ( empty( $docs ) || ! is_array( $docs ) ) {
            return array( 'migrated' => 0 );
        }

        $rows = array();
        foreach ( $docs as $doc ) {
            if ( ! is_array( $doc ) ) {
                continue;
            }
            $row = $this->build_row_from_doc( $doc );
            if ( ! empty( $row ) ) {
                $rows[] = $row;
            }
            if ( count( $rows ) >= 100 ) {
                $this->upsert_batch( $rows );
                $rows = array();
            }
        }

        if ( ! empty( $rows ) ) {
            $this->upsert_batch( $rows );
        }

        return array( 'migrated' => count( $docs ) );
    }

    public function build_row_from_doc( $doc ) {
        if ( empty( $doc ) || ! is_array( $doc ) ) {
            return array();
        }

        $post_id   = isset( $doc['post_id'] ) ? intval( $doc['post_id'] ) : 0;
        $post_type = isset( $doc['post_type'] ) ? (string) $doc['post_type'] : 'post';

        if ( empty( $doc['doc_key'] ) ) {
            return array();
        }
        if ( $post_id <= 0 && 'site_knowledge' !== $post_type ) {
            return array();
        }

        $meta     = isset( $doc['meta'] ) && is_array( $doc['meta'] ) ? $doc['meta'] : array();
        $taxonomy = isset( $doc['taxonomy'] ) && is_array( $doc['taxonomy'] ) ? $doc['taxonomy'] : array();
        $custom_attributes = isset( $doc['custom_attributes'] ) && is_array( $doc['custom_attributes'] )
            ? $doc['custom_attributes']
            : $this->extract_custom_attributes_from_meta( $meta );
        $product_search_evidence = isset( $doc['product_search_evidence'] ) && is_array( $doc['product_search_evidence'] )
            ? $doc['product_search_evidence']
            : array();
        $image    = isset( $doc['image'] ) && is_array( $doc['image'] ) ? $doc['image'] : array();

        $price = null;
        if ( isset( $meta['_price'][0] ) && is_numeric( $meta['_price'][0] ) ) {
            $price = floatval( $meta['_price'][0] );
        }

        $sale_price = null;
        if ( isset( $meta['_sale_price'][0] ) && is_numeric( $meta['_sale_price'][0] ) ) {
            $sale_price = floatval( $meta['_sale_price'][0] );
        }

        $brand = $this->extract_brand( $doc );

        $categories = $this->taxonomy_to_text( $taxonomy, 'product_cat' );
        $tags       = $this->taxonomy_to_text( $taxonomy, 'product_tag' );
        $attributes = $this->attributes_to_text( $taxonomy, $custom_attributes );
        $attributes_norm = $this->build_normalized_attributes( $taxonomy, $custom_attributes );
        if ( ! empty( $product_search_evidence['attributes_text'] ) ) {
            $attributes = trim( $attributes . '; ' . (string) $product_search_evidence['attributes_text'], '; ' );
        }
        if ( ! empty( $product_search_evidence['attributes_norm'] ) ) {
            $attributes_norm = trim( $attributes_norm . ' ' . (string) $product_search_evidence['attributes_norm'] );
        }

        $content_text = '';
        if ( isset( $doc['content_rendered'] ) && '' !== $doc['content_rendered'] ) {
            $content_text = (string) $doc['content_rendered'];
        } elseif ( isset( $doc['content'] ) ) {
            $content_text = (string) $doc['content'];
        }
        if ( ! empty( $doc['meta_text'] ) ) {
            $content_text = trim( $content_text . "\n" . (string) $doc['meta_text'] );
        }

        $short_desc = isset( $doc['excerpt'] ) ? (string) $doc['excerpt'] : '';

        $rlm_text = isset( $doc['rlm_text'] ) ? (string) $doc['rlm_text'] : '';

        $memory_text = $this->build_memory_text(
            array(
                'title'           => isset( $doc['title'] ) ? (string) $doc['title'] : '',
                'brand'           => $brand,
                'categories_text' => $categories,
                'tags_text'       => $tags,
                'attributes_text' => $attributes,
                'short_desc'      => $short_desc,
                'content_text'    => $content_text,
                'price'           => $price,
                'sale_price'      => $sale_price,
                'url'             => isset( $doc['url'] ) ? (string) $doc['url'] : '',
                'rlm_text'        => $rlm_text,
            )
        );
        $memory_hash = $this->hash_text( $memory_text );

        $row = array(
            'post_id'         => $post_id,
            'post_type'       => $post_type,
            'doc_key'         => (string) $doc['doc_key'],
            'title'           => isset( $doc['title'] ) ? (string) $doc['title'] : '',
            'slug'            => isset( $doc['slug'] ) ? (string) $doc['slug'] : '',
            'url'             => isset( $doc['url'] ) ? (string) $doc['url'] : '',
            'excerpt'         => isset( $doc['excerpt'] ) ? (string) $doc['excerpt'] : '',
            'short_desc'      => $short_desc,
            'content_text'    => $content_text,
            'rlm_text'        => $rlm_text,
            'meta_json'       => wp_json_encode( $meta ),
            'taxonomy_json'   => wp_json_encode( $taxonomy ),
            'image_json'      => wp_json_encode( $image ),
            'price'           => $price,
            'sale_price'      => $sale_price,
            'brand'           => $brand,
            'categories_text' => $categories,
            'tags_text'       => $tags,
            'attributes_text' => $attributes,
            'attributes_norm' => $attributes_norm,
            'stock_status'    => $this->get_effective_product_stock_status( $post_id, $post_type, $meta ),
            'memory_text'     => $memory_text,
            'embedding_json'  => null,
            'embedding_blob'  => null,
            'embedding_dimensions' => 0,
            'embedding_format'=> '',
            'embedding_hash'  => $memory_hash,
            'embedding_updated_at' => 0,
            'token_estimate'  => $this->estimate_tokens( $rlm_text ),
            'indexed_at'      => time(),
            'updated_at'      => time(),
        );

        return $row;
    }

    private function get_effective_product_stock_status( $post_id, $post_type, $meta ) {
        $raw_status = isset( $meta['_stock_status'][0] ) ? (string) $meta['_stock_status'][0] : 'instock';
        if ( 'product' !== (string) $post_type || ! function_exists( 'wc_get_product' ) ) {
            return '' !== $raw_status ? $raw_status : 'instock';
        }

        $product = wc_get_product( intval( $post_id ) );
        if ( ! $product || ! method_exists( $product, 'get_stock_status' ) ) {
            return '' !== $raw_status ? $raw_status : 'instock';
        }

        if ( method_exists( $product, 'is_type' ) && $product->is_type( 'variable' ) && method_exists( $product, 'get_children' ) ) {
            foreach ( (array) $product->get_children() as $variation_id ) {
                $variation = wc_get_product( intval( $variation_id ) );
                if ( ! $variation || ! method_exists( $variation, 'is_type' ) || ! $variation->is_type( 'variation' ) ) {
                    continue;
                }
                if ( method_exists( $variation, 'get_status' ) && ! in_array( (string) $variation->get_status(), array( 'publish', 'private' ), true ) ) {
                    continue;
                }
                if ( method_exists( $variation, 'is_in_stock' ) && $variation->is_in_stock() ) {
                    return 'instock';
                }
                if ( method_exists( $variation, 'get_stock_status' ) && 'outofstock' !== (string) $variation->get_stock_status() ) {
                    return (string) $variation->get_stock_status();
                }
            }

            return 'outofstock';
        }

        return (string) $product->get_stock_status();
    }

    public function build_normalized_attributes( $taxonomy, $custom_attributes = array() ) {
        if ( empty( $taxonomy ) && empty( $custom_attributes ) ) {
            return '';
        }

        $tokens = array();
        foreach ( $taxonomy as $tax_name => $terms ) {
            if ( 0 !== strpos( $tax_name, 'pa_' ) || ! is_array( $terms ) ) {
                continue;
            }

            $attr_key = $this->normalize_attribute_key( str_replace( 'pa_', '', $tax_name ) );
            if ( '' === $attr_key ) {
                continue;
            }

            foreach ( $terms as $term ) {
                $val = isset( $term['name'] ) ? (string) $term['name'] : '';
                $this->append_attribute_tokens( $tokens, $attr_key, $val );
            }
        }

        if ( ! empty( $custom_attributes ) && is_array( $custom_attributes ) ) {
            foreach ( $custom_attributes as $key => $vals ) {
                $attr_key = $this->normalize_attribute_key( $key );
                if ( '' === $attr_key ) {
                    continue;
                }
                $vals = $this->normalize_attribute_values( $vals );
                foreach ( $vals as $val ) {
                    $this->append_attribute_tokens( $tokens, $attr_key, $val );
                }
            }
        }

        $tokens = array_values( array_unique( $tokens ) );
        return implode( ' ', $tokens );
    }

    public function extract_brand( $doc ) {
        $taxonomy = isset( $doc['taxonomy'] ) && is_array( $doc['taxonomy'] ) ? $doc['taxonomy'] : array();
        $meta     = isset( $doc['meta'] ) && is_array( $doc['meta'] ) ? $doc['meta'] : array();

        $brand_tax = array( 'pa_brand', 'product_brand', 'brand' );
        foreach ( $brand_tax as $tax ) {
            if ( ! empty( $taxonomy[ $tax ] ) && is_array( $taxonomy[ $tax ] ) ) {
                $term = $taxonomy[ $tax ][0];
                if ( ! empty( $term['name'] ) ) {
                    return (string) $term['name'];
                }
            }
        }

        if ( isset( $meta['_brand'][0] ) ) {
            return (string) $meta['_brand'][0];
        }
        if ( isset( $meta['brand'][0] ) ) {
            return (string) $meta['brand'][0];
        }

        return '';
    }

    private function taxonomy_to_text( $taxonomy, $tax_key ) {
        if ( empty( $taxonomy[ $tax_key ] ) || ! is_array( $taxonomy[ $tax_key ] ) ) {
            return '';
        }

        $names = array();
        foreach ( $taxonomy[ $tax_key ] as $term ) {
            if ( ! empty( $term['name'] ) ) {
                $names[] = (string) $term['name'];
            }
        }

        return implode( ', ', array_filter( $names ) );
    }

    private function attributes_to_text( $taxonomy, $custom_attributes = array() ) {
        if ( empty( $taxonomy ) && empty( $custom_attributes ) ) {
            return '';
        }

        $parts = array();
        foreach ( $taxonomy as $tax_name => $terms ) {
            if ( 0 !== strpos( $tax_name, 'pa_' ) || ! is_array( $terms ) ) {
                continue;
            }

            $label = function_exists( 'wc_attribute_label' )
                ? wc_attribute_label( $tax_name )
                : ucfirst( str_replace( 'pa_', '', $tax_name ) );
            $values = array();
            foreach ( $terms as $t ) {
                if ( ! empty( $t['name'] ) ) {
                    $values[] = (string) $t['name'];
                }
            }
            if ( ! empty( $values ) ) {
                $parts[] = $label . ': ' . implode( '/', $values );
            }
        }

        if ( ! empty( $custom_attributes ) && is_array( $custom_attributes ) ) {
            foreach ( $custom_attributes as $key => $vals ) {
                $label = $this->format_attribute_label( $key );
                $vals  = $this->normalize_attribute_values( $vals );
                if ( ! empty( $vals ) ) {
                    $parts[] = $label . ': ' . implode( '/', $vals );
                }
            }
        }

        return implode( ', ', $parts );
    }

    private function extract_custom_attributes_from_meta( $meta ) {
        if ( empty( $meta['_product_attributes'] ) || ! is_array( $meta['_product_attributes'] ) ) {
            return array();
        }

        $out = array();
        foreach ( $meta['_product_attributes'] as $key => $attr ) {
            if ( empty( $attr ) || ! is_array( $attr ) ) {
                continue;
            }
            if ( ! empty( $attr['is_taxonomy'] ) ) {
                continue;
            }
            $name = isset( $attr['name'] ) ? (string) $attr['name'] : (string) $key;
            $value = isset( $attr['value'] ) ? (string) $attr['value'] : '';
            if ( '' === $name || '' === $value ) {
                continue;
            }
            $parts = preg_split( '/\s*\|\s*|\s*,\s*/', $value );
            $parts = is_array( $parts ) ? array_values( array_filter( array_map( 'trim', $parts ) ) ) : array();
            if ( ! empty( $parts ) ) {
                $out[ $name ] = $parts;
            }
        }

        return $out;
    }

    private function normalize_attribute_values( $vals ) {
        if ( is_string( $vals ) ) {
            $vals = preg_split( '/\s*\|\s*|\s*,\s*/', $vals );
        }
        $vals = is_array( $vals ) ? $vals : array();
        $vals = array_values( array_filter( array_map( 'trim', $vals ) ) );
        return $vals;
    }

    private function normalize_attribute_key( $key ) {
        $key = $this->normalize_text( $key );
        if ( '' === $key ) {
            return '';
        }

        return $key;
    }

    private function format_attribute_label( $key ) {
        $key = $this->normalize_attribute_key( $key );
        if ( '' === $key ) {
            return '';
        }
        return ucfirst( $key );
    }

    private function append_attribute_tokens( &$tokens, $attr_key, $val ) {
        $attr_key = $this->normalize_attribute_key( $attr_key );
        $val_norm = $this->normalize_text( $val );
        if ( '' === $attr_key || '' === $val_norm ) {
            return;
        }

        $tokens[] = $attr_key . ':' . $val_norm;

        if ( false !== strpos( $val_norm, ' ' ) ) {
            $tokens[] = $attr_key . ':' . str_replace( ' ', '_', $val_norm );
            foreach ( preg_split( '/\s+/', $val_norm ) as $part ) {
                $part = trim( $part );
                if ( '' !== $part ) {
                    $tokens[] = $attr_key . ':' . $part;
                }
            }
        }
    }

    private function build_memory_text( $data ) {
        if ( empty( $data ) || ! is_array( $data ) ) {
            return '';
        }

        // Detect if this is product content (has brand, attributes, or price).
        $has_product_fields = ! empty( $data['brand'] )
            || ! empty( $data['attributes_text'] )
            || ( isset( $data['price'] ) && '' !== $data['price'] && null !== $data['price'] );

        $parts = array();
        if ( ! empty( $data['title'] ) ) {
            $parts[] = 'Name: ' . (string) $data['title'];
        }
        if ( ! empty( $data['brand'] ) ) {
            $parts[] = 'Brand: ' . (string) $data['brand'];
        }
        if ( ! empty( $data['categories_text'] ) ) {
            $parts[] = 'Categories: ' . (string) $data['categories_text'];
        }
        if ( ! empty( $data['tags_text'] ) ) {
            $parts[] = 'Tags: ' . (string) $data['tags_text'];
        }
        if ( ! empty( $data['attributes_text'] ) ) {
            $parts[] = 'Attributes: ' . (string) $data['attributes_text'];
        }
        if ( isset( $data['price'] ) && '' !== $data['price'] && null !== $data['price'] ) {
            $parts[] = 'Price: ' . (string) $data['price'];
        }
        if ( isset( $data['sale_price'] ) && '' !== $data['sale_price'] && null !== $data['sale_price'] ) {
            $parts[] = 'Sale price: ' . (string) $data['sale_price'];
        }
        if ( ! empty( $data['short_desc'] ) ) {
            $parts[] = 'Short description: ' . (string) $data['short_desc'];
        }
        if ( ! empty( $data['content_text'] ) ) {
            $content = (string) $data['content_text'];
            // For non-product content, allow richer text for better embeddings.
            if ( ! $has_product_fields ) {
                $content = mb_substr( $content, 0, 4000 );
            }
            $parts[] = 'Content: ' . $content;
        }

        // For non-product content: supplement with rlm_text if content_text is thin.
        if ( ! $has_product_fields && ! empty( $data['rlm_text'] ) ) {
            $content_len = ! empty( $data['content_text'] ) ? mb_strlen( (string) $data['content_text'] ) : 0;
            if ( $content_len < 200 ) {
                $rlm_snippet = mb_substr( (string) $data['rlm_text'], 0, 3000 );
                $parts[] = 'Additional context: ' . $rlm_snippet;
            }
        }

        if ( ! empty( $data['url'] ) ) {
            $parts[] = 'URL: ' . (string) $data['url'];
        }

        $text = trim( implode( "\n", $parts ) );
        return $this->filter_index_noise( $text );
    }

    private function hash_text( $text ) {
        $text = (string) $text;
        if ( '' === $text ) {
            return '';
        }
        return hash( 'sha256', $text );
    }

    private function filter_index_noise( $text ) {
        $text = (string) $text;
        $text = preg_replace( '/\s{2,}/', ' ', $text );
        return trim( $text );
    }

    private function estimate_tokens( $text ) {
        $text = (string) $text;
        if ( '' === $text ) {
            return 0;
        }
        return (int) ceil( mb_strlen( $text ) / 4 );
    }

    private function get_price_range() {
        global $wpdb;
        $table = $this->get_table_name();

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $row = $wpdb->get_row(
            $wpdb->prepare( "SELECT MIN(price) AS min_price, MAX(price) AS max_price FROM {$table} WHERE post_type = %s AND price IS NOT NULL", 'product' ),
            ARRAY_A
        );

        return array(
            'min' => isset( $row['min_price'] ) ? $row['min_price'] : null,
            'max' => isset( $row['max_price'] ) ? $row['max_price'] : null,
        );
    }

    private function get_top_brands( $limit = 10 ) {
        global $wpdb;
        $limit = max( 1, min( 50, intval( $limit ) ) );
        $table = $this->get_table_name();

        $sql = "SELECT brand, COUNT(*) AS cnt FROM {$table} WHERE post_type = %s AND brand <> '' GROUP BY brand ORDER BY cnt DESC LIMIT {$limit}";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $prepared = $wpdb->prepare( $sql, 'product' );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $rows = $wpdb->get_results( $prepared, ARRAY_A );

        $out = array();
        if ( ! empty( $rows ) ) {
            foreach ( $rows as $r ) {
                $out[] = array(
                    'name'  => isset( $r['brand'] ) ? (string) $r['brand'] : '',
                    'count' => isset( $r['cnt'] ) ? intval( $r['cnt'] ) : 0,
                );
            }
        }

        return $out;
    }

    private function count_terms( $taxonomy ) {
        if ( ! function_exists( 'get_terms' ) ) {
            return 0;
        }

        $terms = get_terms(
            array(
                'taxonomy' => $taxonomy,
                'hide_empty' => true,
                'fields' => 'ids',
            )
        );

        if ( is_wp_error( $terms ) || empty( $terms ) ) {
            return 0;
        }

        return count( $terms );
    }

    private function normalize_row( $row, $now ) {
        if ( ! array_key_exists( 'embedding_blob', $row ) ) {
            $row['embedding_blob'] = null;
        }
        if ( ! array_key_exists( 'embedding_dimensions', $row ) ) {
            $row['embedding_dimensions'] = 0;
        }
        if ( ! array_key_exists( 'embedding_format', $row ) ) {
            $row['embedding_format'] = '';
        }
        $row['indexed_at'] = isset( $row['indexed_at'] ) ? intval( $row['indexed_at'] ) : $now;
        $row['updated_at'] = $now;
        return $row;
    }

    private function prepare_value( $col, $val ) {
        global $wpdb;

        $formats = array(
            'post_id' => '%d',
            'token_estimate' => '%d',
            'indexed_at' => '%d',
            'updated_at' => '%d',
            'embedding_updated_at' => '%d',
            'embedding_dimensions' => '%d',
            'price' => '%f',
            'sale_price' => '%f',
        );

        $format = isset( $formats[ $col ] ) ? $formats[ $col ] : '%s';
        return $wpdb->prepare( $format, $val );
    }

    private function normalize_text( $text ) {
        $text = function_exists( 'mb_strtolower' ) ? mb_strtolower( (string) $text, 'UTF-8' ) : strtolower( (string) $text );
        if ( function_exists( 'remove_accents' ) ) {
            $text = remove_accents( $text );
        }
        $text = preg_replace( '/[^\p{L}\p{N}\s]/u', ' ', $text );
        $text = preg_replace( '/\s{2,}/u', ' ', $text );
        return trim( $text );
    }

    /**
     * Validate a decoded embedding without coercing invalid values to zero.
     *
     * @param array $embedding Candidate vector.
     * @return bool
     */
    private static function is_valid_embedding_vector( $embedding ) {
        if ( empty( $embedding ) || ! is_array( $embedding ) ) {
            return false;
        }

        foreach ( $embedding as $value ) {
            if ( ! is_numeric( $value ) || ! is_finite( (float) $value ) ) {
                return false;
            }
        }

        return true;
    }

    /**
     * Decode and validate a legacy JSON vector.
     *
     * @param string $json JSON embedding.
     * @return array
     */
    private static function decode_embedding_json( $json ) {
        if ( ! is_string( $json ) || '' === $json ) {
            return array();
        }

        $decoded = json_decode( $json, true );
        if ( ! self::is_valid_embedding_vector( $decoded ) ) {
            return array();
        }

        return array_map( 'floatval', array_values( $decoded ) );
    }

    /**
     * Check whether a document revision still owns an embedding write.
     *
     * This is used only to distinguish a harmless no-op update from an
     * optimistic-concurrency miss; successful updates do not pay this query.
     *
     * @param string $doc_key Document key.
     * @param string $hash    Expected content hash.
     * @return bool
     */
    private function embedding_hash_matches( $doc_key, $hash ) {
        global $wpdb;

        $table = $this->get_table_name();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared
        $found = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT 1 FROM {$table} WHERE doc_key = %s AND embedding_hash = %s LIMIT 1",
                (string) $doc_key,
                (string) $hash
            )
        );

        return null !== $found;
    }

    /**
     * Check all binary columns together so partially applied schema upgrades
     * fall back to the legacy JSON contract instead of failing reads.
     *
     * @return bool
     */
    private function has_binary_embedding_columns() {
        return true === $this->get_binary_embedding_schema_capability();
    }

    /**
     * Resolve binary-column capability for the current blog table.
     *
     * A successful SHOW COLUMNS result (including a confirmed legacy/partial
     * schema) is cached by full table name. Query errors return null and are
     * deliberately not cached so a later operation can recover in-process.
     *
     * @return bool|null True for all columns, false for confirmed legacy or
     *                   partial schema, null for an introspection DB error.
     */
    private function get_binary_embedding_schema_capability() {
        global $wpdb;

        $table = $this->get_table_name();
        if ( array_key_exists( $table, $this->binary_embedding_columns_available ) ) {
            return $this->binary_embedding_columns_available[ $table ];
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared
        $columns = $wpdb->get_col( "SHOW COLUMNS FROM `{$table}`" );
        if ( ! is_array( $columns ) || ! empty( $wpdb->last_error ) ) {
            return null;
        }

        $columns = is_array( $columns ) ? array_map( 'strval', $columns ) : array();
        $required = array( 'embedding_blob', 'embedding_dimensions', 'embedding_format' );

        $this->binary_embedding_columns_available[ $table ] = count( array_intersect( $required, $columns ) ) === count( $required );
        return $this->binary_embedding_columns_available[ $table ];
    }

    /**
     * Embedding projection compatible with upgraded and legacy schemas.
     *
     * @return string
     */
    private function embedding_select_columns() {
        if ( ! $this->has_binary_embedding_columns() ) {
            return 'embedding_json';
        }

        // Do not transfer the large JSON payload once a valid compact vector
        // exists. Legacy, partial and invalid binary rows still carry JSON so
        // decode_embedding_row() can fall back without a second query.
        $binary_valid = $this->embedding_binary_validity_sql();
        return "CASE WHEN {$binary_valid} THEN NULL ELSE embedding_json END AS embedding_json, "
            . 'embedding_blob, embedding_dimensions, embedding_format';
    }

    /**
     * Strict SQL presence predicate. Invalid BLOB metadata falls back to JSON.
     *
     * @return string
     */
    private function embedding_presence_sql() {
        $json = "(embedding_json IS NOT NULL AND embedding_json <> '')";
        if ( ! $this->has_binary_embedding_columns() ) {
            return $json;
        }

        return '(' . $this->embedding_binary_validity_sql() . " OR {$json})";
    }

    /**
     * SQL validation shared by projection and presence checks.
     *
     * @return string
     */
    private function embedding_binary_validity_sql() {
        $format = self::EMBEDDING_BINARY_FORMAT;
        return "(embedding_blob IS NOT NULL AND embedding_dimensions > 0 AND OCTET_LENGTH(embedding_blob) = embedding_dimensions * "
            . self::EMBEDDING_FLOAT_BYTES
            . " AND embedding_format = '{$format}')";
    }

    /* ================================================================
     * Tool index support for runtime tool metadata.
     * ============================================================= */

    /**
     * Get a single row by doc_key.
     *
     * @param string $doc_key
     * @return array|null Row data or null.
     */
    public function get_by_doc_key( $doc_key ) {
        global $wpdb;
        $table = $this->get_table_name();

        $row = $wpdb->get_row(
            $wpdb->prepare( "SELECT * FROM `{$table}` WHERE doc_key = %s LIMIT 1", $doc_key ),
            ARRAY_A
        );

        return is_array( $row ) ? $row : null;
    }

    /**
     * Upsert a tool row into the content index.
     *
     * @param string $doc_key     e.g. "tool:update_simple_product"
     * @param string $title       Tool name.
     * @param string $memory_text Text to embed.
     * @param string $hash        SHA-256 of memory_text.
     */
    public function upsert_tool_row( $doc_key, $title, $memory_text, $hash ) {
        $this->upsert( array(
            'post_id'        => 0,
            'post_type'      => 'tool',
            'doc_key'        => sanitize_text_field( $doc_key ),
            'title'          => sanitize_text_field( $title ),
            'memory_text'    => $memory_text,
            'embedding_hash' => $hash,
        ) );
    }

    /**
     * Get all tool embeddings from the index.
     *
     * @return array Array of rows with doc_key and binary/JSON embedding data.
     */
    public function get_tool_embeddings() {
        global $wpdb;
        $table = $this->get_table_name();

        $embedding_columns = $this->embedding_select_columns();
        $presence_sql      = $this->embedding_presence_sql();
        $results = $wpdb->get_results(
            "SELECT doc_key, {$embedding_columns} FROM `{$table}` WHERE post_type = 'tool' AND {$presence_sql}",
            ARRAY_A
        );

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