PostgresSqlBuilder.cs

// Copyright (c) Microsoft. All rights reserved.

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using Microsoft.Extensions.AI;
using Microsoft.Extensions.VectorData;
using Microsoft.Extensions.VectorData.ProviderServices;
using Npgsql;
using NpgsqlTypes;

namespace Microsoft.SemanticKernel.Connectors.PgVector;

/// <summary>
/// Provides methods to build SQL commands for managing vector store collections in PostgreSQL.
/// </summary>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "We need to build the full table name using schema and collection, it does not support parameterized passing.")]
internal static class PostgresSqlBuilder
{
    internal static void BuildDoesTableExistCommand(NpgsqlCommand command, string? schema, string tableName)
    {
        Debug.Assert(command.Parameters.Count == 0);

        command.CommandText = """
            SELECT table_name
            FROM information_schema.tables
            WHERE table_schema = $1
                AND table_type = 'BASE TABLE'
                AND table_name = $2
            """;

        command.Parameters.Add(new() { Value = schema ?? "public" });
        command.Parameters.Add(new() { Value = tableName });
    }

    internal static void BuildGetTablesCommand(NpgsqlCommand command, string? schema)
    {
        Debug.Assert(command.Parameters.Count == 0);

        command.CommandText = """
            SELECT table_name
            FROM information_schema.tables
            WHERE table_schema = $1 AND table_type = 'BASE TABLE'
            """;

        command.Parameters.Add(new() { Value = schema ?? "public" });
    }

    internal static string BuildCreateTableSql(string? schema, string tableName, CollectionModel model, Version pgVersion, bool ifNotExists = true)
    {
        if (string.IsNullOrWhiteSpace(tableName))
        {
            throw new ArgumentException("Table name cannot be null or whitespace", nameof(tableName));
        }

        var keyName = model.KeyProperty.StorageName;

        StringBuilder createTableCommand = new();
        createTableCommand.Append("CREATE TABLE ");
        if (ifNotExists)
        {
            createTableCommand.Append("IF NOT EXISTS ");
        }
        createTableCommand.AppendTableName(schema, tableName).AppendLine(" (");

        // Add the key column
        var keyStoreType = PostgresPropertyMapping.GetPostgresTypeName(model.KeyProperty).PgType;
        createTableCommand.Append("    ").AppendIdentifier(keyName).Append(' ').Append(keyStoreType);
        if (model.KeyProperty.IsAutoGenerated)
        {
            switch (keyStoreType.ToUpperInvariant())
            {
                case "INTEGER":
                case "BIGINT":
                    createTableCommand.Append(" GENERATED BY DEFAULT AS IDENTITY");
                    break;
                case "UUID":
                    // UUIDv7 is superior for PostgreSQL indexing, but generating it was only added in PG18.
                    // Fall back to UUIDv4 in older PG versions.
                    createTableCommand.Append(pgVersion >= new Version(18, 0)
                        ? " DEFAULT uuidv7()"
                        : " DEFAULT gen_random_uuid()");
                    break;
                default:
                    throw new NotSupportedException($"Auto-generation of keys is not supported for key type '{keyStoreType}'. Only INTEGER, BIGINT and UUID are supported.");
            }
        }
        createTableCommand.AppendLine(",");

        // Add the data columns
        foreach (var dataProperty in model.DataProperties)
        {
            var dataPgTypeInfo = PostgresPropertyMapping.GetPostgresTypeName(dataProperty);
            createTableCommand.Append("    ").AppendIdentifier(dataProperty.StorageName).Append(' ').Append(dataPgTypeInfo.PgType);
            if (!dataPgTypeInfo.IsNullable)
            {
                createTableCommand.Append(" NOT NULL");
            }
            createTableCommand.AppendLine(",");
        }

        // Add the vector columns
        foreach (var vectorProperty in model.VectorProperties)
        {
            var vectorPgTypeInfo = PostgresPropertyMapping.GetPgVectorTypeName(vectorProperty);
            createTableCommand.Append("    ").AppendIdentifier(vectorProperty.StorageName).Append(' ').Append(vectorPgTypeInfo.PgType);
            if (!vectorPgTypeInfo.IsNullable)
            {
                createTableCommand.Append(" NOT NULL");
            }
            createTableCommand.AppendLine(",");
        }

        createTableCommand.Append("    PRIMARY KEY (").AppendIdentifier(keyName).AppendLine(")");

        createTableCommand.AppendLine(");");

        return createTableCommand.ToString();
    }

    /// <inheritdoc />
    internal static string BuildCreateIndexSql(string? schema, string tableName, string columnName, string indexKind, string distanceFunction, bool isVector, bool isFullText, string? fullTextLanguage, bool ifNotExists)
    {
        var indexName = $"{tableName}_{columnName}_index";

        StringBuilder sql = new();
        sql.Append("CREATE INDEX ");
        if (ifNotExists)
        {
            sql.Append("IF NOT EXISTS ");
        }

        if (isFullText)
        {
            // Create a GIN index for full-text search
            var language = fullTextLanguage ?? PostgresConstants.DefaultFullTextSearchLanguage;
            sql.AppendIdentifier(indexName).Append(" ON ").AppendTableName(schema, tableName)
                .Append(" USING GIN (to_tsvector(").AppendLiteral(language).Append(", ").AppendIdentifier(columnName).Append("))");
            return sql.ToString();
        }

        if (!isVector)
        {
            sql.AppendIdentifier(indexName).Append(" ON ").AppendTableName(schema, tableName)
                .Append(" (").AppendIdentifier(columnName).Append(')');
            return sql.ToString();
        }

        // Only support creating HNSW index creation through the connector.
        var indexTypeName = indexKind switch
        {
            IndexKind.Hnsw => "hnsw",
            _ => throw new NotSupportedException($"Index kind '{indexKind}' is not supported for table creation. If you need to create an index of this type, please do so manually. Only HNSW indexes are supported through the vector store.")
        };

        distanceFunction ??= PostgresConstants.DefaultDistanceFunction;  // Default to Cosine distance

        var indexOps = distanceFunction switch
        {
            DistanceFunction.CosineDistance => "vector_cosine_ops",
            DistanceFunction.CosineSimilarity => "vector_cosine_ops",
            DistanceFunction.DotProductSimilarity => "vector_ip_ops",
            DistanceFunction.EuclideanDistance => "vector_l2_ops",
            DistanceFunction.ManhattanDistance => "vector_l1_ops",
            DistanceFunction.HammingDistance => "bit_hamming_ops",

            _ => throw new NotSupportedException($"Distance function {distanceFunction} is not supported.")
        };

        sql.AppendIdentifier(indexName).Append(" ON ").AppendTableName(schema, tableName)
            .Append(" USING ").Append(indexTypeName).Append(" (").AppendIdentifier(columnName).Append(' ').Append(indexOps).Append(')');
        return sql.ToString();
    }

    /// <inheritdoc />
    internal static void BuildDropTableCommand(NpgsqlCommand command, string? schema, string tableName)
    {
        StringBuilder sql = new();
        sql.Append("DROP TABLE IF EXISTS ").AppendTableName(schema, tableName);
        command.CommandText = sql.ToString();
    }

    /// <inheritdoc />
    internal static bool BuildUpsertCommand<TKey>(
        NpgsqlBatch batch,
        string? schema,
        string tableName,
        CollectionModel model,
        IEnumerable<object> records,
        Dictionary<VectorPropertyModel, IReadOnlyList<Embedding>>? generatedEmbeddings)
    {
        // Note: since keys may be auto-generated, we can't use a single multi-value INSERT statement, since that would return
        // the generated keys in random order. Use a batch of single-value INSERT statements instead.

        string? upsertSql = null, upsertSqlWithAutoGeneratedKey = null;
        var keyProperty = model.KeyProperty;
        var recordIndex = 0;

        foreach (var record in records)
        {
            var batchCommand = batch.CreateBatchCommand();

            var autoGeneratedKey = keyProperty.IsAutoGenerated && Equals(keyProperty.GetValueAsObject(record), default(TKey));
            batchCommand.CommandText = autoGeneratedKey
                ? upsertSqlWithAutoGeneratedKey ??= GenerateSingleUpsertSql(autoGeneratedKey: true)
                : upsertSql ??= GenerateSingleUpsertSql(autoGeneratedKey: false);

            foreach (var property in model.Properties)
            {
                if (property is KeyPropertyModel && autoGeneratedKey)
                {
                    continue;
                }

                var value = property.GetValueAsObject(record);

                if (property is VectorPropertyModel vectorProperty)
                {
                    if (generatedEmbeddings?[vectorProperty] is IReadOnlyList<Embedding> ge)
                    {
                        value = ge[recordIndex];
                    }

                    value = PostgresPropertyMapping.MapVectorForStorageModel(value);
                }

                NpgsqlDbType? npgsqlDbType = null;

                if (property.Type == typeof(DateTime))
                {
                    npgsqlDbType = property.IsTimestampWithoutTimezone()
                        ? NpgsqlDbType.Timestamp
                        // DateTime properties map to PG's "timestamp with time zone" (timestamptz) by default.
                        // Npgsql would silently send non-UTC DateTimes as 'timestamp' (without timezone), and PG would
                        // implicitly cast to timestamptz using the session timezone, producing incorrect results.
                        // Explicitly set NpgsqlDbType.TimestampTz to ensure Npgsql rejects non-UTC DateTimes.
                        : NpgsqlDbType.TimestampTz;
                }

                batchCommand.Parameters.Add(npgsqlDbType.HasValue
                    ? new() { Value = value ?? DBNull.Value, NpgsqlDbType = npgsqlDbType.Value }
                    : new() { Value = value ?? DBNull.Value });
            }

            batch.BatchCommands.Add(batchCommand);
            recordIndex++;
        }

        // No records to insert, return false to indicate no command was built.
        if (recordIndex == 0)
        {
            return false;
        }

        return true;

        string GenerateSingleUpsertSql(bool autoGeneratedKey)
        {
            StringBuilder sqlBuilder = new();

            sqlBuilder
                .Append("INSERT INTO ")
                .AppendTableName(schema, tableName)
                .Append(" (");

            var i = 0;
            foreach (var property in model.Properties)
            {
                if (property is KeyPropertyModel && autoGeneratedKey)
                {
                    continue;
                }

                if (i++ > 0)
                {
                    sqlBuilder.Append(", ");
                }

                sqlBuilder.AppendIdentifier(property.StorageName);
            }

            sqlBuilder
                .AppendLine(")")
                .Append("VALUES (");

            i = 0;
            foreach (var property in model.Properties)
            {
                if (property is KeyPropertyModel && autoGeneratedKey)
                {
                    continue;
                }

                if (i++ > 0)
                {
                    sqlBuilder.Append(", ");
                }

                sqlBuilder.Append('$').Append(i);
            }

            sqlBuilder.Append(')');

            if (autoGeneratedKey)
            {
                sqlBuilder
                    .AppendLine()
                    .Append("RETURNING ")
                    .AppendIdentifier(keyProperty.StorageName);
            }
            else
            {
                sqlBuilder
                    .AppendLine()
                    .Append("ON CONFLICT (")
                    .AppendIdentifier(keyProperty.StorageName)
                    .AppendLine(")")
                    .AppendLine("DO UPDATE SET ");

                i = 0;
                foreach (var property in model.Properties)
                {
                    if (property is KeyPropertyModel)
                    {
                        continue;
                    }

                    if (i++ > 0)
                    {
                        sqlBuilder.AppendLine(", ");
                    }

                    sqlBuilder
                        .Append("    ")
                        .AppendIdentifier(property.StorageName)
                        .Append(" = EXCLUDED.")
                        .AppendIdentifier(property.StorageName);
                }
            }

            return sqlBuilder.ToString();
        }
    }

    /// <inheritdoc />
    internal static void BuildGetCommand<TKey>(NpgsqlCommand command, string? schema, string tableName, CollectionModel model, TKey key, bool includeVectors = false)
        where TKey : notnull
    {
        StringBuilder sql = new();
        sql.Append("SELECT ");

        for (var i = 0; i < model.Properties.Count; i++)
        {
            if (i > 0)
            {
                sql.Append(", ");
            }
            sql.AppendIdentifier(model.Properties[i].StorageName);
        }

        sql.AppendLine().Append("FROM ").AppendTableName(schema, tableName).AppendLine()
            .Append("WHERE ").AppendIdentifier(model.KeyProperty.StorageName).Append(" = $1;");

        command.CommandText = sql.ToString();
        Debug.Assert(command.Parameters.Count == 0);
        command.Parameters.Add(new() { Value = key });
    }

    /// <inheritdoc />
    internal static void BuildGetBatchCommand<TKey>(NpgsqlCommand command, string? schema, string tableName, CollectionModel model, List<TKey> keys, bool includeVectors = false)
        where TKey : notnull
    {
        NpgsqlDbType? keyType = PostgresPropertyMapping.GetNpgsqlDbType(model.KeyProperty) ?? throw new UnreachableException($"Unsupported key type {model.KeyProperty.Type.Name}");

        StringBuilder sql = new();
        sql.Append("SELECT ");

        var first = true;
        foreach (var property in model.Properties)
        {
            if (!includeVectors && property is VectorPropertyModel)
            {
                continue;
            }

            if (!first)
            {
                sql.Append(", ");
            }
            first = false;
            sql.AppendIdentifier(property.StorageName);
        }

        sql.AppendLine().Append("FROM ").AppendTableName(schema, tableName).AppendLine()
            .Append("WHERE ").AppendIdentifier(model.KeyProperty.StorageName).Append(" = ANY($1);");

        command.CommandText = sql.ToString();
        Debug.Assert(command.Parameters.Count == 0);
        command.Parameters.Add(new()
        {
            Value = keys.ToArray(),
            NpgsqlDbType = NpgsqlDbType.Array | keyType.Value
        });
    }

    /// <inheritdoc />
    internal static void BuildDeleteCommand<TKey>(NpgsqlCommand command, string? schema, string tableName, string keyColumn, TKey key)
    {
        StringBuilder sql = new();
        sql.Append("DELETE FROM ").AppendTableName(schema, tableName).AppendLine()
            .Append("WHERE ").AppendIdentifier(keyColumn).Append(" = $1;");

        command.CommandText = sql.ToString();
        Debug.Assert(command.Parameters.Count == 0);
        command.Parameters.Add(new() { Value = key });
    }

    /// <inheritdoc />
    internal static void BuildDeleteBatchCommand<TKey>(NpgsqlCommand command, string? schema, string tableName, KeyPropertyModel keyProperty, List<TKey> keys)
    {
        NpgsqlDbType? keyType = PostgresPropertyMapping.GetNpgsqlDbType(keyProperty) ?? throw new ArgumentException($"Unsupported key type {typeof(TKey).Name}");

        for (int i = 0; i < keys.Count; i++)
        {
            if (keys[i] == null)
            {
                throw new ArgumentException("Keys cannot contain null values", nameof(keys));
            }
        }

        StringBuilder sql = new();
        sql.Append("DELETE FROM ").AppendTableName(schema, tableName).AppendLine()
            .Append("WHERE ").AppendIdentifier(keyProperty.StorageName).Append(" = ANY($1);");

        command.CommandText = sql.ToString();
        Debug.Assert(command.Parameters.Count == 0);
        command.Parameters.Add(new() { Value = keys, NpgsqlDbType = NpgsqlDbType.Array | keyType.Value });
    }

    /// <summary>
    /// Appends a properly quoted and escaped PostgreSQL identifier to the StringBuilder.
    /// In PostgreSQL, identifiers are quoted with double quotes, and embedded double quotes are escaped by doubling them.
    /// </summary>
    internal static StringBuilder AppendIdentifier(this StringBuilder sb, string identifier)
        => sb.Append('"').Append(identifier.Replace("\"", "\"\"")).Append('"');

    /// <summary>
    /// Appends a properly quoted and escaped PostgreSQL string literal to the StringBuilder.
    /// In PostgreSQL, string literals are quoted with single quotes, and embedded single quotes are escaped by doubling them.
    /// </summary>
    internal static StringBuilder AppendLiteral(this StringBuilder sb, string value)
        => sb.Append('\'').Append(value.Replace("'", "''")).Append('\'');

    /// <summary>
    /// Gets the PostgreSQL distance operator for the specified distance function.
    /// </summary>
    private static string GetDistanceOperator(string? distanceFunction)
        => distanceFunction switch
        {
            DistanceFunction.EuclideanDistance or null => "<->",
            DistanceFunction.CosineDistance or DistanceFunction.CosineSimilarity => "<=>",
            DistanceFunction.ManhattanDistance => "<+>",
            DistanceFunction.DotProductSimilarity => "<#>",
            DistanceFunction.HammingDistance => "<~>",
            _ => throw new NotSupportedException($"Distance function {distanceFunction} is not supported.")
        };

    /// <summary>
    /// Generates filter clause from either legacy or new filter, returning condition and parameters.
    /// </summary>
#pragma warning disable CS0618 // VectorSearchFilter is obsolete
    private static (string Clause, List<object> Parameters) GenerateFilterClause<TRecord>(
        CollectionModel model,
        VectorSearchFilter? legacyFilter,
        Expression<Func<TRecord, bool>>? newFilter,
        int startParamIndex)
        => (oldFilter: legacyFilter, newFilter) switch
        {
            (not null, not null) => throw new ArgumentException("Either Filter or OldFilter can be specified, but not both"),
            (not null, null) => GenerateLegacyFilterWhereClause(model, legacyFilter, startParamIndex),
            (null, not null) => GenerateNewFilterWhereClause(model, newFilter, startParamIndex),
            _ => (Clause: string.Empty, Parameters: new List<object>())
        };

    /// <summary>
    /// Generates filter condition (without WHERE keyword) from either legacy or new filter.
    /// </summary>
    private static (string Condition, List<object> Parameters) GenerateFilterCondition<TRecord>(
        CollectionModel model,
        VectorSearchFilter? legacyFilter,
        Expression<Func<TRecord, bool>>? newFilter,
        int startParamIndex)
        => (oldFilter: legacyFilter, newFilter) switch
        {
            (not null, not null) => throw new ArgumentException("Either Filter or OldFilter can be specified, but not both"),
            (not null, null) => GenerateLegacyFilterCondition(model, legacyFilter, startParamIndex),
            (null, not null) => GenerateNewFilterCondition(model, newFilter, startParamIndex),
            _ => (Condition: string.Empty, Parameters: new List<object>())
        };
#pragma warning restore CS0618 // VectorSearchFilter is obsolete

#pragma warning disable CS0618 // VectorSearchFilter is obsolete
    /// <inheritdoc />
    internal static void BuildGetNearestMatchCommand<TRecord>(
        NpgsqlCommand command, string? schema, string tableName, CollectionModel model, VectorPropertyModel vectorProperty, object vectorValue,
        VectorSearchFilter? legacyFilter, Expression<Func<TRecord, bool>>? newFilter, int? skip, bool includeVectors, int limit,
        double? scoreThreshold = null)
    {
        // Build column list with proper escaping
        StringBuilder columns = new();
        for (var i = 0; i < model.Properties.Count; i++)
        {
            if (i > 0)
            {
                columns.Append(", ");
            }
            columns.AppendIdentifier(model.Properties[i].StorageName);
        }

        var distanceFunction = vectorProperty.DistanceFunction ?? PostgresConstants.DefaultDistanceFunction;
        var distanceOp = GetDistanceOperator(distanceFunction);

        // Start where clause params at 2, vector takes param 1.
        var (where, parameters) = GenerateFilterClause(model, legacyFilter, newFilter, startParamIndex: 2);

        StringBuilder sql = new();
        sql.Append("SELECT ").Append(columns).Append(", ").AppendIdentifier(vectorProperty.StorageName)
            .Append(' ').Append(distanceOp).Append(" $1 AS ").AppendIdentifier(PostgresConstants.DistanceColumnName).AppendLine()
            .Append("FROM ").AppendTableName(schema, tableName)
            .Append(' ').AppendLine(where)
            .Append("ORDER BY ").AppendLine(PostgresConstants.DistanceColumnName)
            .Append("LIMIT ").Append(limit);

        if (skip.HasValue)
        {
            sql.Append(" OFFSET ").Append(skip.Value);
        }

        var commandText = sql.ToString();

        // For cosine similarity, we need to take 1 - cosine distance.
        // However, we can't use an expression in the ORDER BY clause or else the index won't be used.
        // Instead we'll wrap the query in a subquery and modify the distance in the outer query.
        if (vectorProperty.DistanceFunction == DistanceFunction.CosineSimilarity)
        {
            StringBuilder outerSql = new();
            outerSql.Append("SELECT ").Append(columns).Append(", 1 - ").AppendIdentifier(PostgresConstants.DistanceColumnName)
                .Append(" AS ").AppendIdentifier(PostgresConstants.DistanceColumnName).AppendLine()
                .Append("FROM (").Append(commandText).Append(") AS subquery");
            commandText = outerSql.ToString();
        }

        // For inner product, we need to take -1 * inner product.
        // However, we can't use an expression in the ORDER BY clause or else the index won't be used.
        // Instead we'll wrap the query in a subquery and modify the distance in the outer query.
        if (vectorProperty.DistanceFunction == DistanceFunction.DotProductSimilarity)
        {
            StringBuilder outerSql = new();
            outerSql.Append("SELECT ").Append(columns).Append(", -1 * ").AppendIdentifier(PostgresConstants.DistanceColumnName)
                .Append(" AS ").AppendIdentifier(PostgresConstants.DistanceColumnName).AppendLine()
                .Append("FROM (").Append(commandText).Append(") AS subquery");
            commandText = outerSql.ToString();
        }

        // Apply score threshold filter if specified.
        // For similarity functions (higher = more similar), filter out results below the threshold.
        // For distance functions (lower = more similar), filter out results above the threshold.
        if (scoreThreshold.HasValue)
        {
            var scoreThresholdParamIndex = parameters.Count + 2;
            var comparisonOp = distanceFunction switch
            {
                DistanceFunction.CosineSimilarity or DistanceFunction.DotProductSimilarity
                    => ">=",

                DistanceFunction.EuclideanDistance
                    or DistanceFunction.CosineDistance
                    or DistanceFunction.ManhattanDistance
                    or DistanceFunction.HammingDistance
                    => "<=",

                _ => throw new UnreachableException($"Unexpected distance function: {distanceFunction}")
            };

            StringBuilder outerSql = new();
            outerSql.Append("SELECT * FROM (").Append(commandText).Append(") AS scored WHERE ")
                .AppendIdentifier(PostgresConstants.DistanceColumnName).Append(' ').Append(comparisonOp)
                .Append(" $").Append(scoreThresholdParamIndex);
            commandText = outerSql.ToString();
        }

        command.CommandText = commandText;

        Debug.Assert(command.Parameters.Count == 0);
        command.Parameters.Add(new NpgsqlParameter { Value = vectorValue });

        foreach (var parameter in parameters)
        {
            command.Parameters.Add(new NpgsqlParameter { Value = parameter });
        }

        if (scoreThreshold.HasValue)
        {
            command.Parameters.Add(new NpgsqlParameter { Value = scoreThreshold.Value });
        }
    }

    internal static void BuildSelectWhereCommand<TRecord>(
        NpgsqlCommand command, string? schema, string tableName, CollectionModel model,
        Expression<Func<TRecord, bool>> filter, int top, FilteredRecordRetrievalOptions<TRecord> options)
    {
        StringBuilder query = new(200);
        query.Append("SELECT ");
        var first = true;
        foreach (var property in model.Properties)
        {
            if (options.IncludeVectors || property is not VectorPropertyModel)
            {
                if (!first)
                {
                    query.Append(',');
                }
                first = false;
                query.AppendIdentifier(property.StorageName);
            }
        }
        query.AppendLine();
        query.Append("FROM ").AppendTableName(schema, tableName).AppendLine();

        PostgresFilterTranslator translator = new(model, filter, startParamIndex: 1, query);
        translator.Translate(appendWhere: true);
        query.AppendLine();

        var orderBy = options.OrderBy?.Invoke(new()).Values;
        if (orderBy is { Count: > 0 })
        {
            query.Append("ORDER BY ");

            var firstOrderBy = true;
            foreach (var sortInfo in orderBy)
            {
                if (!firstOrderBy)
                {
                    query.Append(',');
                }
                firstOrderBy = false;
                query.AppendIdentifier(model.GetDataOrKeyProperty(sortInfo.PropertySelector).StorageName)
                    .Append(sortInfo.Ascending ? " ASC" : " DESC");
            }

            query.AppendLine();
        }

        query.AppendFormat("OFFSET {0}", options.Skip).AppendLine();
        query.AppendFormat("LIMIT {0}", top).AppendLine();

        command.CommandText = query.ToString();

        Debug.Assert(command.Parameters.Count == 0);
        foreach (var parameter in translator.ParameterValues)
        {
            command.Parameters.Add(new NpgsqlParameter { Value = parameter });
        }
    }

    internal static (string Clause, List<object> Parameters) GenerateNewFilterWhereClause(CollectionModel model, LambdaExpression newFilter, int startParamIndex)
    {
        var (condition, parameters) = GenerateNewFilterCondition(model, newFilter, startParamIndex);
        return (string.IsNullOrEmpty(condition) ? string.Empty : $"WHERE {condition}", parameters);
    }

    internal static (string Condition, List<object> Parameters) GenerateNewFilterCondition(CollectionModel model, LambdaExpression newFilter, int startParamIndex)
    {
        PostgresFilterTranslator translator = new(model, newFilter, startParamIndex);
        translator.Translate(appendWhere: false);
        return (translator.Clause.ToString(), translator.ParameterValues);
    }

    /// <summary>
    /// Appends a schema-qualified table name. If schema is null or empty, omits the schema prefix.
    /// </summary>
    private static StringBuilder AppendTableName(this StringBuilder sb, string? schema, string tableName)
    {
        if (!string.IsNullOrEmpty(schema))
        {
            sb.AppendIdentifier(schema!).Append('.');
        }

        return sb.AppendIdentifier(tableName);
    }

#pragma warning disable CS0618 // VectorSearchFilter is obsolete
    internal static (string Clause, List<object> Parameters) GenerateLegacyFilterWhereClause(CollectionModel model, VectorSearchFilter legacyFilter, int startParamIndex)
    {
        var (condition, parameters) = GenerateLegacyFilterCondition(model, legacyFilter, startParamIndex);
        return ($"WHERE {condition}", parameters);
    }

    internal static (string Condition, List<object> Parameters) GenerateLegacyFilterCondition(CollectionModel model, VectorSearchFilter legacyFilter, int startParamIndex)
    {
        StringBuilder condition = new();
        var parameters = new List<object>();

        var paramIndex = startParamIndex;
        var first = true;

        foreach (var filterClause in legacyFilter.FilterClauses)
        {
            if (!first)
            {
                condition.Append(" AND ");
            }
            first = false;

            if (filterClause is EqualToFilterClause equalTo)
            {
                var property = model.Properties.FirstOrDefault(p => p.ModelName == equalTo.FieldName);
                if (property == null) { throw new ArgumentException($"Property {equalTo.FieldName} not found in record definition."); }

                condition.AppendIdentifier(property.StorageName).Append(" = $").Append(paramIndex);
                parameters.Add(equalTo.Value);
                paramIndex++;
            }
            else if (filterClause is AnyTagEqualToFilterClause anyTagEqualTo)
            {
                var property = model.Properties.FirstOrDefault(p => p.ModelName == anyTagEqualTo.FieldName);
                if (property == null) { throw new ArgumentException($"Property {anyTagEqualTo.FieldName} not found in record definition."); }

                if (property.Type != typeof(List<string>))
                {
                    throw new ArgumentException($"Property {anyTagEqualTo.FieldName} must be of type List<string> to use AnyTagEqualTo filter.");
                }

                condition.AppendIdentifier(property.StorageName).Append(" @> ARRAY[$").Append(paramIndex).Append("::TEXT]");
                parameters.Add(anyTagEqualTo.Value);
                paramIndex++;
            }
            else
            {
                throw new NotSupportedException($"Filter clause type {filterClause.GetType().Name} is not supported.");
            }
        }

        return (condition.ToString(), parameters);
    }
#pragma warning restore CS0618 // VectorSearchFilter is obsolete

#pragma warning disable CS0618 // VectorSearchFilter is obsolete
    /// <summary>
    /// Builds a hybrid search command that combines vector similarity search with full-text keyword search using RRF (Reciprocal Rank Fusion).
    /// </summary>
    internal static void BuildHybridSearchCommand<TRecord>(
        NpgsqlCommand command, string? schema, string tableName, CollectionModel model,
        VectorPropertyModel vectorProperty, DataPropertyModel textProperty,
        object vectorValue, ICollection<string> keywords,
        VectorSearchFilter? legacyFilter, Expression<Func<TRecord, bool>>? newFilter,
        int? skip, bool includeVectors, int top, double? scoreThreshold = null)
    {
        // RRF constant - higher values give more weight to lower-ranked results
        const int RrfConstant = 60;

        // Build column list with proper escaping (for the final SELECT)
        StringBuilder columns = new();
        for (var i = 0; i < model.Properties.Count; i++)
        {
            if (!includeVectors && model.Properties[i] is VectorPropertyModel)
            {
                continue;
            }

            if (columns.Length > 0)
            {
                columns.Append(", ");
            }

            columns.Append("t.").AppendIdentifier(model.Properties[i].StorageName);
        }

        var distanceOp = GetDistanceOperator(vectorProperty.DistanceFunction ?? PostgresConstants.DefaultDistanceFunction);

        // Parameters: $1 = keywords, $2 = vector, $3 = RRF constant
        // Additional parameters start at $4 for filters
        var (filterCondition, filterParameters) = GenerateFilterCondition(model, legacyFilter, newFilter, startParamIndex: 4);

        // Build the full table name
        var fullTableName = new StringBuilder()
            .AppendTableName(schema, tableName)
            .ToString();

        // Use a larger internal limit for the CTEs to get better ranking, then limit final results
        var internalLimit = (top + (skip ?? 0)) * 2;
        if (internalLimit < 20)
        {
            internalLimit = 20;
        }

        // Get the full-text search language from the text property
        var language = textProperty.GetFullTextSearchLanguageOrDefault();

        StringBuilder sql = new();
        sql.AppendLine()
            .Append("WITH semantic_search AS (").AppendLine()
            .Append("    SELECT ").AppendIdentifier(model.KeyProperty.StorageName).Append(", RANK () OVER (ORDER BY ").AppendIdentifier(vectorProperty.StorageName)
            .Append(' ').Append(distanceOp).Append(" $2) AS rank").AppendLine()
            .Append("    FROM ").Append(fullTableName);

        // Add filter for semantic search
        if (!string.IsNullOrEmpty(filterCondition))
        {
            sql.AppendLine().Append("    WHERE ").Append(filterCondition);
        }

        sql.AppendLine()
            .Append("    ORDER BY ").AppendIdentifier(vectorProperty.StorageName).Append(' ').Append(distanceOp).Append(" $2").AppendLine()
            .Append("    LIMIT ").Append(internalLimit).AppendLine()
            .Append("),").AppendLine()
            .Append("keyword_search AS (").AppendLine()
            .Append("    SELECT ").AppendIdentifier(model.KeyProperty.StorageName).Append(", RANK () OVER (ORDER BY ts_rank_cd(to_tsvector(").AppendLiteral(language).Append(", ").AppendIdentifier(textProperty.StorageName).Append("), query) DESC) AS rank").AppendLine()
            .Append("    FROM ").Append(fullTableName).Append(", plainto_tsquery(").AppendLiteral(language).Append(", $1) query").AppendLine()
            .Append("    WHERE to_tsvector(").AppendLiteral(language).Append(", ").AppendIdentifier(textProperty.StorageName).Append(") @@ query");

        // Add filter for keyword search (using AND since we already have a WHERE clause)
        if (!string.IsNullOrEmpty(filterCondition))
        {
            sql.Append(" AND ").Append(filterCondition);
        }

        sql.AppendLine()
            .Append("    ORDER BY ts_rank_cd(to_tsvector(").AppendLiteral(language).Append(", ").AppendIdentifier(textProperty.StorageName).Append("), query) DESC").AppendLine()
            .Append("    LIMIT ").Append(internalLimit).AppendLine()
            .Append(')').AppendLine()
            .Append("SELECT ").Append(columns).Append(',').AppendLine()
            .Append("    COALESCE(1.0 / ($3 + semantic_search.rank), 0.0) +").AppendLine()
            .Append("    COALESCE(1.0 / ($3 + keyword_search.rank), 0.0) AS ").AppendIdentifier(PostgresConstants.DistanceColumnName).AppendLine()
            .Append("FROM semantic_search").AppendLine()
            .Append("FULL OUTER JOIN keyword_search ON semantic_search.").AppendIdentifier(model.KeyProperty.StorageName).Append(" = keyword_search.").AppendIdentifier(model.KeyProperty.StorageName).AppendLine()
            .Append("JOIN ").Append(fullTableName).Append(" t ON t.").AppendIdentifier(model.KeyProperty.StorageName).Append(" = COALESCE(semantic_search.").AppendIdentifier(model.KeyProperty.StorageName).Append(", keyword_search.").AppendIdentifier(model.KeyProperty.StorageName).Append(')').AppendLine()
            .Append("ORDER BY ").AppendIdentifier(PostgresConstants.DistanceColumnName).Append(" DESC");

        var commandText = sql.ToString();

        // Apply score threshold filter if specified (higher RRF scores = better match)
        if (scoreThreshold.HasValue)
        {
            var scoreThresholdParamIndex = filterParameters.Count + 4;
            StringBuilder outerSql = new();
            outerSql.Append("SELECT * FROM (").Append(commandText).Append(") AS scored WHERE ")
                .AppendIdentifier(PostgresConstants.DistanceColumnName).Append(" >= $").Append(scoreThresholdParamIndex);
            commandText = outerSql.ToString();
        }

        // Apply LIMIT and OFFSET
        StringBuilder finalSql = new();
        finalSql.Append("SELECT * FROM (").Append(commandText).Append(") AS results LIMIT ").Append(top);
        if (skip > 0)
        {
            finalSql.Append(" OFFSET ").Append(skip.Value);
        }

        command.CommandText = finalSql.ToString();

        Debug.Assert(command.Parameters.Count == 0);

        // $1 = keywords (joined as single string for plainto_tsquery)
        command.Parameters.Add(new NpgsqlParameter { Value = string.Join(" ", keywords) });
        // $2 = vector
        command.Parameters.Add(new NpgsqlParameter { Value = vectorValue });
        // $3 = RRF constant
        command.Parameters.Add(new NpgsqlParameter { Value = RrfConstant });

        // Filter parameters starting at $4
        foreach (var parameter in filterParameters)
        {
            command.Parameters.Add(new NpgsqlParameter { Value = parameter });
        }

        // Score threshold parameter
        if (scoreThreshold.HasValue)
        {
            command.Parameters.Add(new NpgsqlParameter { Value = scoreThreshold.Value });
        }
    }
#pragma warning restore CS0618 // VectorSearchFilter is obsolete
}