using System.Text;

using Npgsql;

namespace NpgsqlRest;

public class RoutineSource(

string? schemaSimilarTo = null,

string? schemaNotSimilarTo = null,

string[]? includeSchemas = null,

string[]? excludeSchemas = null,

string? nameSimilarTo = null,

string? nameNotSimilarTo = null,

string[]? includeNames = null,

string[]? excludeNames = null,

string? query = null,

CommentsMode? commentsMode = null,

string? customTypeParameterSeparator = "_",

string[]? includeLanguages = null,

string[]? excludeLanguages = null,

bool nestedJsonForCompositeTypes = false,

bool resolveNestedCompositeTypes = true) : IRoutineSource

{

public string? SchemaSimilarTo { get; set; } = schemaSimilarTo;

public string? SchemaNotSimilarTo { get; set; } = schemaNotSimilarTo;

public string[]? IncludeSchemas { get; set; } = includeSchemas;

public string[]? ExcludeSchemas { get; set; } = excludeSchemas;

public string? NameSimilarTo { get; set; } = nameSimilarTo;

public string? NameNotSimilarTo { get; set; } = nameNotSimilarTo;

public string[]? IncludeNames { get; set; } = includeNames;

public string[]? ExcludeNames { get; set; } = excludeNames;

public string? Query { get; set; } = query ?? RoutineSourceQuery.Query;

public CommentsMode? CommentsMode { get; set; } = commentsMode;

public string? CustomTypeParameterSeparator { get; set; } = customTypeParameterSeparator;

public string[]? IncludeLanguages { get; set; } = includeLanguages;

public string[]? ExcludeLanguages { get; set; } = excludeLanguages;

public bool NestedJsonForCompositeTypes { get; set; } = nestedJsonForCompositeTypes;

/// <summary>

/// When true, nested composite types and arrays of composite types within composite fields

/// are serialized as JSON objects/arrays instead of PostgreSQL tuple strings.

/// For example, a nested composite "(1,x)" becomes {"id":1,"name":"x"}.

/// Default: true (nested composites are serialized as proper JSON objects).

/// Set to false for backwards compatibility or to reduce startup overhead for schemas

/// with thousands of composite types.

/// </summary>

public bool ResolveNestedCompositeTypes { get; set; } = resolveNestedCompositeTypes;

public IEnumerable<(Routine, IRoutineSourceParameterFormatter)> Read(IServiceProvider? serviceProvider, RetryStrategy? retryStrategy)

{

bool shouldDispose = true;

NpgsqlConnection? connection = null;

try

{

Options.CreateAndOpenSourceConnection(serviceProvider, ref connection, ref shouldDispose, nameof(RoutineSource));

if (connection is null)

{

yield break;

}

// Initialize composite type cache if deep nested type resolution is enabled

if (ResolveNestedCompositeTypes)

{

CompositeTypeCache.Initialize(connection, Options.NameConverter);

}

using var command = connection.CreateCommand();

Query ??= RoutineSourceQuery.Query;

if (Query.Contains(Consts.Space) is false)

{

command.CommandText = string.Concat("select * from ", Query, "($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)");

}

else

{

command.CommandText = Query;

}

command.AddParameter(SchemaSimilarTo ?? Options.SchemaSimilarTo); // $1

command.AddParameter(SchemaNotSimilarTo ?? Options.SchemaNotSimilarTo); // $2

command.AddParameter(IncludeSchemas ?? Options.IncludeSchemas, true); // $3

command.AddParameter(ExcludeSchemas ?? Options.ExcludeSchemas, true); // $4

command.AddParameter(NameSimilarTo ?? Options.NameSimilarTo); // $5

command.AddParameter(NameNotSimilarTo ?? Options.NameNotSimilarTo); // $6

command.AddParameter(IncludeNames ?? Options.IncludeNames, true); // $7

command.AddParameter(ExcludeNames ?? Options.ExcludeNames, true); // $8

command.AddParameter(IncludeLanguages, true); // $9

command.AddParameter(ExcludeLanguages is null ? ["c", "internal"] : ExcludeLanguages, true); // $10

command.LogCommand(nameof(RoutineSource));

using NpgsqlDataReader reader = command.ExecuteReaderWithRetry(retryStrategy);

while (reader.Read())

{

var type = reader.Get<string>(0);//"type");

var paramTypes = reader.Get<string[]>(14);// "param_types");

var returnType = reader.Get<string>(7);// "return_type");

var name = reader.Get<string>(2);// "name");

var volatility = reader.Get<char>(5);//"volatility_option");

var hasGet =

name.Contains("_get_", StringComparison.OrdinalIgnoreCase) ||

name.StartsWith("get_", StringComparison.OrdinalIgnoreCase) ||

name.EndsWith("_get", StringComparison.OrdinalIgnoreCase);

var crudType = hasGet ? CrudType.Select : (volatility == 'v' ? CrudType.Update : CrudType.Select);

var originalParamNames = reader.Get<string[]>(13);//"param_names");

var isVoid = string.Equals(returnType, "void", StringComparison.Ordinal);

var schema = reader.Get<string>(1);//"schema");

var returnsSet = reader.Get<bool>(6);//"returns_set");

var returnRecordNames = reader.Get<string[]>(9);//"return_record_names");

string[] convertedRecordNames = new string[returnRecordNames.Length];

for (int i = 0; i < returnRecordNames.Length; i++)

{

convertedRecordNames[i] = Options.NameConverter(returnRecordNames[i]) ?? returnRecordNames[i];

}

var returnRecordTypes = reader.Get<string[]>(10);//"return_record_types");

var isUnnamedRecord = reader.Get<bool>(11);// "is_unnamed_record");

string[] expNames = new string[returnRecordNames.Length];

Dictionary<int, (string[] FieldNames, TypeDescriptor[] FieldDescriptors, string OriginalColumnName, int[] ExpandedColumnIndices)>? compositeColumnInfo = null;

var customRecTypeNames = reader.Get<string?[]>(21); //custom_rec_type_names

var compositeOutParamNames = reader.Get<string[]?>(23); //composite_out_param_names

if (customRecTypeNames is not null && customRecTypeNames.Length > 0)

{

var customRecTypeTypes = reader.Get<string?[]>(22); //custom_rec_type_types

// Track composite column metadata for potential nested JSON serialization

// Group fields by their original column (they share the same returnRecordNames prefix)

Dictionary<string, List<(int OriginalIndex, string FieldName, string FieldType)>>? fieldsByColumn = null;

for (var i = 0; i < convertedRecordNames.Length; i++)

{

var customName = customRecTypeNames[i];

if (customName is not null)

{

expNames[i] = string.Concat("(", returnRecordNames[i], ").", customName);

convertedRecordNames[i] = Options.NameConverter(customName) ?? customName;

returnRecordTypes[i] = customRecTypeTypes[i] ?? returnRecordTypes[i];

// Build composite metadata in single pass

fieldsByColumn ??= new();

var columnName = returnRecordNames[i];

if (!fieldsByColumn.TryGetValue(columnName, out var fieldList))

{

fieldList = new List<(int, string, string)>();

fieldsByColumn[columnName] = fieldList;

}

fieldList.Add((i, customName, customRecTypeTypes[i] ?? returnRecordTypes[i]));

}

else

{

expNames[i] = returnRecordNames[i];

}

}

if (fieldsByColumn is not null)

{

// Build composite info without LINQ allocations

compositeColumnInfo = new(fieldsByColumn.Count);

foreach (var (columnName, fields) in fieldsByColumn)

{

var count = fields.Count;

var fieldNames = new string[count];

var fieldDescriptors = new TypeDescriptor[count];

var expandedIndices = new int[count];

for (var j = 0; j < count; j++)

{

var field = fields[j];

fieldNames[j] = Options.NameConverter(field.FieldName) ?? field.FieldName;

fieldDescriptors[j] = new TypeDescriptor(field.FieldType);

expandedIndices[j] = field.OriginalIndex;

}

compositeColumnInfo[fields[0].OriginalIndex] = (

fieldNames,

fieldDescriptors,

Options.NameConverter(columnName) ?? columnName,

expandedIndices

);

}

}

}

else if (compositeOutParamNames is not null && compositeOutParamNames.Length > 0)

{

// Handle case where return is composite type with named OUT params

// e.g., returns table (req nested_request) - all columns belong to one composite

// returnRecordNames contains the composite field names, compositeOutParamNames has the column name

for (var i = 0; i < returnRecordNames.Length; i++)

{

expNames[i] = returnRecordNames[i];

}

// Group all fields under each OUT param name

// For single composite column: compositeOutParamNames = ['req'], returnRecordNames = ['id', 'text_value', 'flag']

if (compositeOutParamNames.Length == 1)

{

// All fields belong to single composite column

var columnName = compositeOutParamNames[0];

var count = returnRecordNames.Length;

var fieldNames = new string[count];

var fieldDescriptors = new TypeDescriptor[count];

var expandedIndices = new int[count];

for (var j = 0; j < count; j++)

{

fieldNames[j] = convertedRecordNames[j];

fieldDescriptors[j] = new TypeDescriptor(returnRecordTypes[j]);

expandedIndices[j] = j;

}

compositeColumnInfo = new(1)

{

[0] = (fieldNames, fieldDescriptors, Options.NameConverter(columnName) ?? columnName, expandedIndices)

};

}

}

else if (string.Equals(returnType, "record", StringComparison.OrdinalIgnoreCase) && !isUnnamedRecord && !returnsSet && returnRecordNames.Length >= 1)

{

// Handle case where function returns a named composite type directly (not via OUT params)

// e.g., CREATE FUNCTION get_single_bool_field_type() RETURNS single_bool_field_type

// In this case, returnType is "record" and all returnRecordNames belong to the composite

// We use the FROM syntax: select field1, field2 from schema.function()

// This expands the composite fields so they can be serialized individually

for (var i = 0; i < returnRecordNames.Length; i++)

{

expNames[i] = returnRecordNames[i];

}

// Note: We intentionally do NOT set compositeColumnInfo here because we want the fields

// to be serialized directly as a flat JSON object, not nested under a composite column name

}

// Read array composite type info (columns 25, 26, 27)

// These are populated when OUT params include arrays of composite types

Dictionary<int, (string[] FieldNames, TypeDescriptor[] FieldDescriptors)>? arrayCompositeColumnInfo = null;

var arrayColumnIndices = reader.Get<int[]?>(25); // array_column_indices (1-based from SQL)

if (arrayColumnIndices is not null && arrayColumnIndices.Length > 0)

{

var arrayFieldNamesJson = reader.Get<string?>(26); // array_field_names_json (JSON string)

var arrayFieldTypesJson = reader.Get<string?>(27); // array_field_types_json (JSON string)

if (arrayFieldNamesJson is not null && arrayFieldTypesJson is not null)

{

// Parse JSON arrays: [["field1","field2"],["field1","field2"]]

var arrayFieldNames = System.Text.Json.JsonSerializer.Deserialize(arrayFieldNamesJson, NpgsqlRestSerializerContext.Default.StringArrayArray);

var arrayFieldTypes = System.Text.Json.JsonSerializer.Deserialize(arrayFieldTypesJson, NpgsqlRestSerializerContext.Default.StringArrayArray);

if (arrayFieldNames is not null && arrayFieldTypes is not null)

{

arrayCompositeColumnInfo = new(arrayColumnIndices.Length);

for (var i = 0; i < arrayColumnIndices.Length; i++)

{

var colIndex = arrayColumnIndices[i] - 1; // Convert to 0-based

var fieldNames = arrayFieldNames[i];

var fieldTypes = arrayFieldTypes[i];

var convertedFieldNames = new string[fieldNames.Length];

var fieldDescriptors = new TypeDescriptor[fieldNames.Length];

for (var j = 0; j < fieldNames.Length; j++)

{

convertedFieldNames[j] = Options.NameConverter(fieldNames[j]) ?? fieldNames[j];

fieldDescriptors[j] = new TypeDescriptor(fieldTypes[j]);

// Resolve nested composite types if option is enabled

if (ResolveNestedCompositeTypes)

{

ResolveNestedTypesForDescriptor(fieldDescriptors[j]);

}

}

arrayCompositeColumnInfo[colIndex] = (convertedFieldNames, fieldDescriptors);

}

}

}

}

TypeDescriptor[] returnTypeDescriptor;

if (isVoid)

{

returnTypeDescriptor = [];

}

else

{

returnTypeDescriptor = [.. returnRecordTypes.Select(x => new TypeDescriptor(x))];

}

var routineType = type.GetEnum<RoutineType>();

var callIdent = routineType == RoutineType.Procedure ? "call " : "select ";

var paramCount = reader.Get<int>(12);// "param_count");

var argumentDef = reader.Get<string>(15);

string?[] paramDefaults = new string?[paramCount];

bool[] hasParamDefaults = new bool[paramCount];

if (string.IsNullOrEmpty(argumentDef) is false)

{

const string defaultArgExp = " DEFAULT ";

var defParts = argumentDef.Split(Consts.Comma, StringSplitOptions.TrimEntries);

for (int i = 0; i < paramCount; i++)

{

string paramName = originalParamNames[i];

if (paramName is null)

{

continue;

}

if (i < defParts.Length)

{

if (defParts[i].Contains(paramName) is false)

{

continue;

}

int idx = defParts[i].IndexOf(defaultArgExp);

if (idx != -1)

{

string defaultValue = defParts[i][(idx + defaultArgExp.Length)..];

paramDefaults[i] = defaultValue;

hasParamDefaults[i] = true;

}

else

{

paramDefaults[i] = null;

hasParamDefaults[i] = false;

}

}

}

}

var returnRecordCount = returnRecordNames.Length; // Use actual array length (may have changed for nested JSON)

var variadic = reader.Get<bool>(16);// "has_variadic");

string from;

// When function returns a named composite type (returnType == "record" but not unnamed),

// we need field expansion via FROM syntax even when there's only one field,

// otherwise PostgreSQL returns the composite representation like "(value)"

bool hasNamedCompositeReturn = string.Equals(returnType, "record", StringComparison.OrdinalIgnoreCase) && !isUnnamedRecord;

if (isVoid || (returnRecordCount == 1 && !hasNamedCompositeReturn))

{

from = callIdent;

}

else

{

//from = string.Concat(callIdent, string.Join(",", returnRecordNames), " from ");

StringBuilder sb = new();

for (int i = 0; i < returnRecordCount; i++)

{

sb.Append(expNames[i] ?? returnRecordNames[i]);

if (i < returnRecordCount - 1)

{

sb.Append(Consts.Comma);

}

}

from = string.Concat(callIdent, sb.ToString(), " from ");

}

var expression = string.Concat(

from,

schema,

".",

name,

"(",

variadic && paramCount > 0 ? "variadic " : "");

var simpleDefinition = new StringBuilder();

simpleDefinition.AppendLine(string.Concat(

routineType.ToString().ToLower(), " ",

schema, ".",

name, "(",

paramCount == 0 ? ")" : ""));

var customTypeNames = reader.Get<string?[]>(18);

var customTypeTypes = reader.Get<string?[]>(19);

var customTypePositions = reader.Get<short?[]>(20);

NpgsqlRestParameter[] parameters = new NpgsqlRestParameter[paramCount];

bool hasCustomType = false;

if (paramCount > 0)

{

for (var i = 0; i < paramCount; i++)

{

var paramName = originalParamNames[i];

var originalParameterName = paramName;

var customTypeName = customTypeNames[i];

string? customType;

if (customTypeName != null)

{

customType = paramTypes[i];

paramTypes[i] = customTypeTypes[i] ?? customType;

paramName = string.Concat(paramName, CustomTypeParameterSeparator, customTypeName);

originalParamNames[i] = paramName;

if (hasCustomType is false)

{

hasCustomType = true;

}

}

else

{

customType = null;

}

var defaultValue = paramDefaults[i];

var paramType = paramTypes[i];

var fullParamType = defaultValue == null ? paramType : $"{paramType} DEFAULT {defaultValue}";

simpleDefinition

.AppendLine(string.Concat(" ", paramName, " ", fullParamType, i == paramCount - 1 ? "" : ","));

var convertedName = Options.NameConverter(paramName);

if (string.IsNullOrEmpty(convertedName))

{

convertedName = $"${i + 1}";

}

var descriptor = new TypeDescriptor(

paramType,

hasDefault: hasParamDefaults[i],

customType: customType,

customTypePosition: customTypePositions[i],

originalParameterName: originalParameterName,

customTypeName: customTypeName);

//parameters[i] = new NpgsqlRestParameter

//{

// Ordinal = i,

// NpgsqlDbType = descriptor.ActualDbType,

// ConvertedName = convertedName,

// ActualName = originalParameterName,

// TypeDescriptor = descriptor

//};

parameters[i] = new NpgsqlRestParameter(

ordinal: i,

convertedName: convertedName,

actualName: originalParameterName,

typeDescriptor: descriptor);

}

simpleDefinition.AppendLine(")");

}

if (!returnsSet)

{

simpleDefinition.AppendLine(string.Concat("returns ", returnType));

}

else

{

if (isUnnamedRecord)

{

simpleDefinition.AppendLine(string.Concat($"returns setof {returnType}"));

}

else

{

simpleDefinition.AppendLine("returns table(");

for (var i = 0; i < returnRecordCount; i++)

{

var returnParamName = returnRecordNames[i];

var returnParamType = returnRecordTypes[i];

simpleDefinition

.AppendLine(string.Concat(" ", returnParamName, " ", returnParamType, i == returnRecordCount - 1 ? "" : ","));

}

simpleDefinition.AppendLine(")");

}

}

IRoutineSourceParameterFormatter formatter;

if (hasCustomType is false)

{

formatter = new RoutineSourceParameterFormatter();

}

else

{

formatter = new RoutineSourceCustomTypesParameterFormatter();

}

string[] jsonColumnNames = new string[convertedRecordNames.Length];

for (int i = 0; i < convertedRecordNames.Length; i++)

{

jsonColumnNames[i] = PgConverters.SerializeString(convertedRecordNames[i]);

}

yield return (

new Routine

{

Type = routineType,

Schema = schema,

Name = name,

Comment = reader.Get<string>(3),//"comment"),

IsStrict = reader.Get<bool>(4),//"is_strict"),

CrudType = crudType,

ReturnsRecordType = string.Equals(returnType, "record", StringComparison.OrdinalIgnoreCase),

ReturnsSet = returnsSet,

ColumnCount = returnRecordCount,

OriginalColumnNames = returnRecordNames,

ColumnNames = convertedRecordNames,

JsonColumnNames = jsonColumnNames,

ReturnsUnnamedSet = isUnnamedRecord,

ColumnsTypeDescriptor = returnTypeDescriptor,

IsVoid = isVoid,

ParamCount = paramCount,

Parameters = parameters,

ParamsHash = [.. parameters.Select(p => p.ConvertedName)],

OriginalParamsHash = [.. parameters.Select(p => p.ActualName)],

Expression = expression,

FullDefinition = reader.Get<string>(17),//"definition"),

SimpleDefinition = simpleDefinition.ToString(),

Immutable = volatility == 'i',

Tags = [routineType.ToString().ToLowerInvariant(), volatility switch

{

'v' => "volatile",

's' => "stable",

'i' => "immutable",

_ => "other"

}],

FormatUrlPattern = null,

EndpointHandler = null,

Metadata = null,

CompositeColumnInfo = compositeColumnInfo,

ArrayCompositeColumnInfo = arrayCompositeColumnInfo

},

formatter);

}

}

finally

{

if (connection is not null && shouldDispose is true)

{

connection.Dispose();

}

}

}

/// <summary>

/// Resolves nested composite type metadata for a field descriptor.

/// Looks up the field type in the CompositeTypeCache and populates

/// nested field names and descriptors if the field is a composite type.

/// </summary>

private static void ResolveNestedTypesForDescriptor(TypeDescriptor descriptor)

{

// Check if this is a composite type

var compositeMetadata = CompositeTypeCache.GetType(descriptor.OriginalType);

if (compositeMetadata != null)

{

descriptor.CompositeFieldNames = compositeMetadata.ConvertedFieldNames;

descriptor.CompositeFieldDescriptors = compositeMetadata.FieldDescriptors;

return;

}

// Check if this is an array of composite types

if (descriptor.IsArray)

{

var elementMetadata = CompositeTypeCache.GetArrayElementType(descriptor.OriginalType);

if (elementMetadata != null)

{

descriptor.ArrayCompositeFieldNames = elementMetadata.ConvertedFieldNames;

descriptor.ArrayCompositeFieldDescriptors = elementMetadata.FieldDescriptors;

}

}

}

}