unit SQLParserEnhanced;

interface

uses
  System.Classes, System.SysUtils, System.Generics.Collections, System.RegularExpressions,
  Uni, System.StrUtils, Data.DB, System.IOUtils, System.TypInfo;

type
  TSQLParser = class; // Forward declaration

  TSubqueryInfo = record
    FullExpression: string;
    AggregateFunction: string;
    Column: string;
    Alias: string;
    TableName: string;
    DataType: TFieldType;
    procedure Initialize;
  end;

  TExpressionParts = record
    Expression: string;
    Alias: string;
    HasAlias: Boolean;
  end;

  TSQLDebugLogger = class
  private
    class var FLogFileName: string;
    class var FLogFile: TextFile;
    class var FIsInitialized: Boolean;
    class var FEnabled: Boolean;
  public
    class constructor Create;
    class destructor Destroy;
    class procedure Initialize;
    class procedure Log(const Msg: string);
    class property Enabled: Boolean read FEnabled write FEnabled;
  end;

  TColumnMetadata = record
    SchemaName: string;
    TableName: string;
    ColumnName: string;
    IsNullable: Boolean;
    IsPrimaryKey: Boolean;
    DataType: string;
    MaxLength: Integer;
    NumericPrecision: Integer;
    NumericScale: Integer;
  end;

  { Base class for table information }
  TSQLTableInfo = class
  private
    FSchemaName: string;
    FTableName: string;
    FAlias: string;
    FColumns: TList<string>;
    FPrimaryKeys: TList<string>;
    FConnection: TUniConnection;
    FJoinType: string;
    FJoinCondition: string;
    function GetFullTableName: string;
  protected
    procedure LoadColumns;
  public
    constructor Create(AConnection: TUniConnection);
    destructor Destroy; override;

    property SchemaName: string read FSchemaName write FSchemaName;
    property TableName: string read FTableName write FTableName;
    property FullTableName: string read GetFullTableName;
    property Alias: string read FAlias write FAlias;
    property Columns: TList<string> read FColumns;
    property PrimaryKeys: TList<string> read FPrimaryKeys;
    property JoinType: string read FJoinType write FJoinType;
    property JoinCondition: string read FJoinCondition write FJoinCondition;
  end;

  { Column type detail information }
  TColumnTypeDetail = record
    BaseType: TFieldType;
    Length: Integer;
    Precision: Integer;
    Scale: Integer;
    IsNullable: Boolean;
    HasDefault: Boolean;
    DefaultValue: string;
    CharacterSet: string;
    Collation: string;
    procedure Initialize;
  end;

  { Enhanced column information }
  TSQLColumnInfo = class
  private
    FFullName: string;
    FRealSchemaName: string;
    FRealTableName: string;
    FReferencedTableColumn: string;
    FQueryColumnAlias: string;
    FIsPKColumn: Boolean;
    FDataType: TFieldType;
    FTypeDetail: TColumnTypeDetail;
    FParentParser: TSQLParser;
    FResolvedTableInfo: TSQLTableInfo;
    FIsWildcard: Boolean;
  public
    constructor Create;
    property FullName: string read FFullName write FFullName;
    property RealSchemaName: string read FRealSchemaName write FRealSchemaName;
    property RealTableName: string read FRealTableName write FRealTableName;
    property ReferencedTableColumn: string read FReferencedTableColumn write FReferencedTableColumn;
    property QueryColumnAlias: string read FQueryColumnAlias write FQueryColumnAlias;
    property IsPKColumn: Boolean read FIsPKColumn write FIsPKColumn;
    property DataType: TFieldType read FDataType write FDataType;
    property TypeDetail: TColumnTypeDetail read FTypeDetail write FTypeDetail;
    property ParentParser: TSQLParser read FParentParser write FParentParser;
    property ResolvedTableInfo: TSQLTableInfo read FResolvedTableInfo write FResolvedTableInfo;
    property IsWildcard: Boolean read FIsWildcard write FIsWildcard;
  end;

  { Enhanced SQL Parser }
  TSQLParser = class
  private
    FSQLText: string;
    FTables: TObjectList<TSQLTableInfo>;
    FColumns: TObjectList<TSQLColumnInfo>;
    FConnection: TUniConnection;
    FTypeCache: TDictionary<string, TColumnTypeDetail>;
    FColumnCache: TDictionary<string, TList<string>>;
    FTableAliasMap: TDictionary<string, TSQLTableInfo>;
    FProcessedWildcards: TDictionary<string, Boolean>;
    FSubqueryCache: TDictionary<string, TSubqueryInfo>;

    procedure BuildTableAliasMap;
    function GetTableInfoByAliasOrName(const AliasOrName: string): TSQLTableInfo;
    function IsExpression(const Expr: string): Boolean;
    function IsSQLKeywordOrFunction(const Word: string): Boolean;
    function IsWildcard(const ColExpr: string): Boolean;
    function ContainsWildcard(const SelectClause: string): Boolean;
    procedure ParseFromClause;
    procedure ParseSelectClause;
    procedure ProcessRegularColumns(SelectClause: string);
    procedure ProcessWildcards(SelectClause: string);
    procedure LoadTableColumnsMetadata;
    function MapSQLTypeToFieldType(const SQLType: string): TFieldType;
    procedure ValidateColumns;
    procedure ParseBaseTableAndJoins(const FromClause: string);
    function ParseTableReference(const TableRef: string): TSQLTableInfo;
    procedure ResolveColumnTableInfo(ColumnInfo: TSQLColumnInfo; const TableAlias: string);
    procedure ExpandWildcardColumn(const WildcardExpr: string);
    procedure ExpandTableColumns(TableInfo: TSQLTableInfo);
    function GetTypeDetailFromDB(const SchemaName, TableName, ColumnName: string): TColumnTypeDetail;
    procedure CacheTypeInfo(const Key: string; const TypeDetail: TColumnTypeDetail);
    procedure ResolveUnknownColumns;
    function GetTableColumnNames(const SchemaName, TableName: string): TList<string>;
    procedure ClearColumnCache;
    function ColumnExistsInTable(const SchemaName, TableName, ColumnName: string): Boolean;
    procedure ProcessColumnInfo(ColumnInfo: TSQLColumnInfo);
    procedure ResolveExpressionTypes;
    procedure LogColumnTypes;
    procedure LogParsedTables;
    procedure LogParsedColumns;
    procedure HandleColumnAlias(ColumnInfo: TSQLColumnInfo; const ColExpr: string);
    function ParseColumnExpression(const Expression: string): TSQLColumnInfo;
    procedure HandleAmbiguousColumns;
    procedure LogTableAliasMap;
    procedure ValidateJoinColumn(const ColumnRef: string);
    function NormalizeSQLText(const SQL: string): string;
    function ContainsOperators(const Expr: string): Boolean;
    function GetExpressionType(const Expr: string): TFieldType;
    function SplitExpressionAndAlias(const Input: string): TExpressionParts;
    function ContainsSubquery(const Expr: string): Boolean;
    procedure ParseSubquerySchema(var Subquery: string);
    function ParseSubquery(const SubqueryExpr: string): TSubqueryInfo;
    function GetAggregateReturnType(const AggFunc, Column: string): TFieldType;
    function ExtractSubqueryAlias(const Expr: string; out Subquery, Alias: string): Boolean;
    function IsAggregateFunction(const Func: string): Boolean;
    procedure ProcessSubqueryExpression(ColumnInfo: TSQLColumnInfo);


  protected
    procedure ValidateTableReferences;
    function IsValidColumnName(const ColumnName: string): Boolean;
    procedure LogParsingError(const ErrorMessage: string; const Context: string = '');
    function GetEffectiveSchemaName(const SchemaName: string): string;
    procedure ProcessJoinConditions;

  public
    constructor Create(const SQLText: string; AConnection: TUniConnection);
    destructor Destroy; override;
    procedure Parse;
    procedure DumpMetadata(const FileName: string = '');
    function ValidateQuery: Boolean;
    procedure ResolveColumnTypes;
    function GetFullColumnName(const ColumnInfo: TSQLColumnInfo): string;
    procedure ClearCache;

    property Tables: TObjectList<TSQLTableInfo> read FTables;
    property Columns: TObjectList<TSQLColumnInfo> read FColumns;
    property SQLText: string read FSQLText write FSQLText;
    property Connection: TUniConnection read FConnection write FConnection;
  end;

implementation

{ Implementation follows - I'll break it into sections for readability }

{ TSQLColumnInfo }

constructor TSQLColumnInfo.Create;
begin
  inherited Create;
  FResolvedTableInfo := nil;
  FIsWildcard := False;
end;

{ TSQLTableInfo }

constructor TSQLTableInfo.Create(AConnection: TUniConnection);
begin
  inherited Create;
  FConnection := AConnection;
  FColumns := TList<string>.Create;
  FPrimaryKeys := TList<string>.Create;
  FAlias := '';
  FJoinType := '';
  FJoinCondition := '';
end;

destructor TSQLTableInfo.Destroy;
begin
  FColumns.Free;
  FPrimaryKeys.Free;
  inherited;
end;

function TSQLTableInfo.GetFullTableName: string;
begin
  if FSchemaName <> '' then
    Result := LowerCase(FSchemaName) + '.' + LowerCase(FTableName)
  else
    Result := LowerCase(FTableName);
end;

procedure TSQLTableInfo.LoadColumns;
var
  Query: TUniQuery;
begin
  if not Assigned(FConnection) or (FSchemaName = '') or (FTableName = '') then
    Exit;

  Query := TUniQuery.Create(nil);
  try
    Query.Connection := FConnection;
    Query.SQL.Text :=
      'SELECT COLUMN_NAME ' +
      'FROM INFORMATION_SCHEMA.COLUMNS ' +
      'WHERE TABLE_SCHEMA = :schema AND TABLE_NAME = :table ' +
      'ORDER BY ORDINAL_POSITION';

    Query.ParamByName('schema').AsString := LowerCase(FSchemaName);
    Query.ParamByName('table').AsString := LowerCase(FTableName);

    Query.Open;
    while not Query.Eof do
    begin
      FColumns.Add(LowerCase(Query.FieldByName('COLUMN_NAME').AsString));
      Query.Next;
    end;
  finally
    Query.Free;
  end;
end;

{ TSQLParser - Main Methods }

constructor TSQLParser.Create(const SQLText: string; AConnection: TUniConnection);
begin
  inherited Create;
  FSQLText := SQLText;
  FConnection := AConnection;
  FTables := TObjectList<TSQLTableInfo>.Create(True);
  FColumns := TObjectList<TSQLColumnInfo>.Create(True);
  FTypeCache := TDictionary<string, TColumnTypeDetail>.Create;
  FColumnCache := TDictionary<string, TList<string>>.Create;
  FTableAliasMap := TDictionary<string, TSQLTableInfo>.Create;
  FProcessedWildcards := TDictionary<string, Boolean>.Create;
  FSubqueryCache := TDictionary<string, TSubqueryInfo>.Create;
end;

destructor TSQLParser.Destroy;
begin
  FSubqueryCache.Free;
  FProcessedWildcards.Free;
  FTableAliasMap.Free;
  FColumnCache.Free;
  FTypeCache.Free;
  FColumns.Free;
  FTables.Free;
  inherited;
end;

procedure TSQLParser.Parse;
begin
  TSQLDebugLogger.Log('Starting SQL parsing');
  try
    // Normalize SQL text while preserving quoted strings
    FSQLText := NormalizeSQLText(FSQLText);
    TSQLDebugLogger.Log('Normalized SQL: ' + FSQLText);

    ParseFromClause;
    TSQLDebugLogger.Log('FROM clause parsed successfully');
    LogParsedTables;

    ParseSelectClause;
    TSQLDebugLogger.Log('SELECT clause parsed successfully');
    LogParsedColumns;

    LoadTableColumnsMetadata;
    TSQLDebugLogger.Log('Table metadata loaded');

    ValidateColumns;
    ResolveExpressionTypes;
  except
    on E: Exception do
    begin
      TSQLDebugLogger.Log('Error parsing SQL: ' + E.Message);
      raise Exception.Create('Error parsing SQL: ' + E.Message);
    end;
  end;
end;

procedure TSQLParser.ParseSelectClause;
var
  SelectMatch, FromMatch: TMatch;
  SelectPos, FromPos: Integer;
  SelectClause: string;
begin
  TSQLDebugLogger.Log('Starting ParseSelectClause');
  try
    // Find SELECT and FROM
    SelectMatch := TRegEx.Match(FSQLText, '\bSELECT\b', [roIgnoreCase]);
    FromMatch := TRegEx.Match(FSQLText, '\bFROM\b', [roIgnoreCase]);

    if not SelectMatch.Success then
      raise Exception.Create('Cannot find SELECT keyword');
    if not FromMatch.Success then
      raise Exception.Create('Cannot find FROM keyword');

    SelectPos := SelectMatch.Index + SelectMatch.Length;
    FromPos := FromMatch.Index;

    if SelectPos >= FromPos then
      raise Exception.Create('Invalid SELECT clause position');

    SelectClause := Copy(FSQLText, SelectPos + 1, FromPos - SelectPos - 1).Trim;
    if SelectClause = '' then
      raise Exception.Create('Empty SELECT clause');

    // Process regular columns first
    ProcessRegularColumns(SelectClause);

    // Then handle wildcards if present
    if ContainsWildcard(SelectClause) then
      ProcessWildcards(SelectClause);

  except
    on E: Exception do
    begin
      TSQLDebugLogger.Log('ERROR in ParseSelectClause: ' + E.Message);
      raise Exception.Create('Error parsing SELECT clause: ' + E.Message);
    end;
  end;
end;

procedure TSQLParser.ProcessRegularColumns(SelectClause: string);
var
  Columns: TStringList;
  ParenCount: Integer;
  CurrentColumn: string;
  i: Integer;
begin
  TSQLDebugLogger.Log('Processing regular columns');
  Columns := TStringList.Create;
  try
    // Split columns while respecting parentheses
    ParenCount := 0;
    CurrentColumn := '';
    i := 1;

    while i <= Length(SelectClause) do
    begin
      case SelectClause[i] of
        '(': Inc(ParenCount);
        ')': if ParenCount > 0 then Dec(ParenCount);
        ',': if ParenCount = 0 then
             begin
               if CurrentColumn.Trim <> '' then
                 Columns.Add(CurrentColumn.Trim);
               CurrentColumn := '';
               Inc(i);
               Continue;
             end;
      end;
      CurrentColumn := CurrentColumn + SelectClause[i];
      Inc(i);
    end;

    if CurrentColumn.Trim <> '' then
      Columns.Add(CurrentColumn.Trim);

    // Process each regular column
    for var ColExpr in Columns do
    begin
      if ColExpr.Trim = '' then
        Continue;

      // Skip wildcards - they'll be handled separately
      if IsWildcard(ColExpr) then
        Continue;

      var ColumnInfo := ParseColumnExpression(ColExpr);
      try
        ProcessColumnInfo(ColumnInfo);
        ColumnInfo := nil; // Prevent double-free
      except
        ColumnInfo.Free;
        raise;
      end;
    end;

  finally
    Columns.Free;
  end;
end;

procedure TSQLParser.ProcessWildcards(SelectClause: string);
var
  Columns: TStringList;
begin
  TSQLDebugLogger.Log('Processing wildcards');
  Columns := TStringList.Create;
  try
    // Split on commas and process each potential wildcard
    Columns.DelimitedText := SelectClause;
    for var ColExpr in Columns do
    begin
      if IsWildcard(ColExpr) then
      begin
        // Only process each unique wildcard once
        if not FProcessedWildcards.ContainsKey(ColExpr) then
        begin
          TSQLDebugLogger.Log('Expanding wildcard: ' + ColExpr);
          ExpandWildcardColumn(ColExpr);
          FProcessedWildcards.Add(ColExpr, True);
        end;
      end;
    end;
finally
    Columns.Free;
  end;
end;

procedure TSQLParser.ExpandWildcardColumn(const WildcardExpr: string);
begin
  TSQLDebugLogger.Log('Starting wildcard expansion: ' + WildcardExpr);

  if WildcardExpr = '*' then
  begin
    TSQLDebugLogger.Log('Processing global wildcard (*)');
    for var TableInfo in FTables do
    begin
      TSQLDebugLogger.Log(Format('Expanding columns for table: %s.%s',
        [TableInfo.SchemaName, TableInfo.TableName]));
      ExpandTableColumns(TableInfo);
    end;
  end
  else
  begin
    // Handle qualified wildcard (table.*)
    var TableRef := Copy(WildcardExpr, 1, Length(WildcardExpr) - 2); // Remove .*
    TSQLDebugLogger.Log(Format('Processing qualified wildcard for: %s', [TableRef]));

    var TableInfo := GetTableInfoByAliasOrName(TableRef);
    if Assigned(TableInfo) then
    begin
      TSQLDebugLogger.Log(Format('Found matching table: %s.%s',
        [TableInfo.SchemaName, TableInfo.TableName]));
      ExpandTableColumns(TableInfo);
    end
    else
    begin
      TSQLDebugLogger.Log('ERROR: Table not found for wildcard expansion');
      raise Exception.CreateFmt('Table or alias "%s" not found for wildcard expansion',
        [TableRef]);
    end;
  end;
end;

procedure TSQLParser.ExpandTableColumns(TableInfo: TSQLTableInfo);
var
  Query: TUniQuery;
begin
  TSQLDebugLogger.Log(Format('Expanding columns for table: %s.%s',
    [TableInfo.SchemaName, TableInfo.TableName]));

  Query := TUniQuery.Create(nil);
  try
    Query.Connection := FConnection;
    Query.SQL.Text :=
      'SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, ' +
      'NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE, COLUMN_DEFAULT, ' +
      'CHARACTER_SET_NAME, COLLATION_NAME ' +
      'FROM INFORMATION_SCHEMA.COLUMNS ' +
      'WHERE TABLE_SCHEMA = :schema AND TABLE_NAME = :table ' +
      'ORDER BY ORDINAL_POSITION';

    Query.ParamByName('schema').AsString := TableInfo.SchemaName;
    Query.ParamByName('table').AsString := TableInfo.TableName;

    TSQLDebugLogger.Log('Executing column metadata query');
    Query.Open;

    while not Query.Eof do
    begin
      var ColumnName := Query.FieldByName('COLUMN_NAME').AsString;
      TSQLDebugLogger.Log(Format('Processing wildcard column: %s', [ColumnName]));

      var NewColumn := TSQLColumnInfo.Create;
      try
        NewColumn.RealSchemaName := TableInfo.SchemaName;
        NewColumn.RealTableName := TableInfo.TableName;
        NewColumn.ReferencedTableColumn := ColumnName;
        NewColumn.FullName := Format('%s.%s.%s',
          [TableInfo.SchemaName, TableInfo.TableName, ColumnName]);
        NewColumn.IsWildcard := True;

        // Set column alias based on table alias
        if TableInfo.Alias <> '' then
          NewColumn.QueryColumnAlias := Format('%s_%s', [TableInfo.Alias, ColumnName])
        else
          NewColumn.QueryColumnAlias := ColumnName;

        // Set the column type details directly from the query
        NewColumn.DataType := MapSQLTypeToFieldType(Query.FieldByName('DATA_TYPE').AsString);

        var TypeDetail: TColumnTypeDetail;
        TypeDetail.Initialize;
        TypeDetail.BaseType := NewColumn.DataType;
        TypeDetail.Length := Query.FieldByName('CHARACTER_MAXIMUM_LENGTH').AsInteger;
        TypeDetail.Precision := Query.FieldByName('NUMERIC_PRECISION').AsInteger;
        TypeDetail.Scale := Query.FieldByName('NUMERIC_SCALE').AsInteger;
        TypeDetail.IsNullable := SameText(Query.FieldByName('IS_NULLABLE').AsString, 'YES');
        TypeDetail.HasDefault := not Query.FieldByName('COLUMN_DEFAULT').IsNull;
        TypeDetail.DefaultValue := Query.FieldByName('COLUMN_DEFAULT').AsString;
        TypeDetail.CharacterSet := Query.FieldByName('CHARACTER_SET_NAME').AsString;
        TypeDetail.Collation := Query.FieldByName('COLLATION_NAME').AsString;

        NewColumn.TypeDetail := TypeDetail;
        NewColumn.ResolvedTableInfo := TableInfo;

        // Cache the type information
        var TypeKey := Format('%s.%s.%s',
          [TableInfo.SchemaName, TableInfo.TableName, ColumnName]);
        CacheTypeInfo(TypeKey, TypeDetail);

        // Log the type information
        TSQLDebugLogger.Log(Format('Column %s type details:', [ColumnName]));
        TSQLDebugLogger.Log(Format('  Data type: %s',
          [GetEnumName(TypeInfo(TFieldType), Ord(NewColumn.DataType))]));
        TSQLDebugLogger.Log(Format('  Length: %d', [TypeDetail.Length]));
        TSQLDebugLogger.Log(Format('  Precision: %d', [TypeDetail.Precision]));
        TSQLDebugLogger.Log(Format('  Scale: %d', [TypeDetail.Scale]));

        // Add to columns collection
        FColumns.Add(NewColumn);
        NewColumn := nil; // Prevent double-free
      except
        on E: Exception do
        begin
          TSQLDebugLogger.Log('ERROR expanding column: ' + E.Message);
          NewColumn.Free;
          raise;
        end;
      end;

      Query.Next;
    end;

  finally
    Query.Free;
  end;
  TSQLDebugLogger.Log('Finished expanding columns');
end;

procedure TSQLParser.ProcessColumnInfo(ColumnInfo: TSQLColumnInfo);
begin
  TSQLDebugLogger.Log('Processing column info: ' + ColumnInfo.ReferencedTableColumn);

  // Set parent parser reference
  ColumnInfo.ParentParser := Self;

  // Check if it's an expression (including subqueries and aggregates)
  if IsExpression(ColumnInfo.ReferencedTableColumn) then
  begin
    TSQLDebugLogger.Log('Processing as expression');
    ColumnInfo.RealSchemaName := '[expression]';
    ColumnInfo.RealTableName := '[expression]';
    ColumnInfo.DataType := GetExpressionType(ColumnInfo.ReferencedTableColumn);

    var TypeDetail: TColumnTypeDetail;
    TypeDetail.Initialize;
    TypeDetail.BaseType := ColumnInfo.DataType;

    // Set appropriate details for strings
    if (ColumnInfo.DataType = ftString) then
    begin
      var StringValue := ColumnInfo.ReferencedTableColumn;
      StringValue := StringValue.Trim(['"', '''']);
      TypeDetail.Length := Length(StringValue);
    end;

    ColumnInfo.TypeDetail := TypeDetail;
    FColumns.Add(ColumnInfo);
    Exit;
  end;

  // Rest of the existing validation logic...
  if (ColumnInfo.RealSchemaName <> '') and
     (ColumnInfo.RealTableName <> '') then
  begin
    if not ColumnExistsInTable(ColumnInfo.RealSchemaName,
                             ColumnInfo.RealTableName,
                             ColumnInfo.ReferencedTableColumn) then
    begin
      TSQLDebugLogger.Log(Format('Column not found: %s in table %s.%s',
        [ColumnInfo.ReferencedTableColumn, ColumnInfo.RealSchemaName, ColumnInfo.RealTableName]));
      raise Exception.CreateFmt('Column "%s" not found in table "%s.%s"',
        [ColumnInfo.ReferencedTableColumn, ColumnInfo.RealSchemaName, ColumnInfo.RealTableName]);
    end;

    // Load type information
    var TypeKey := Format('%s.%s.%s',
      [ColumnInfo.RealSchemaName, ColumnInfo.RealTableName, ColumnInfo.ReferencedTableColumn]);

    var TypeDetail: TColumnTypeDetail;
    if FTypeCache.TryGetValue(TypeKey, TypeDetail) then
    begin
      TSQLDebugLogger.Log('Found type info in cache');
      ColumnInfo.TypeDetail := TypeDetail;
      ColumnInfo.DataType := TypeDetail.BaseType;
    end
    else
    begin
      TSQLDebugLogger.Log('Getting type info from database');
      TypeDetail := GetTypeDetailFromDB(ColumnInfo.RealSchemaName,
                                      ColumnInfo.RealTableName,
                                      ColumnInfo.ReferencedTableColumn);
      ColumnInfo.TypeDetail := TypeDetail;
      ColumnInfo.DataType := TypeDetail.BaseType;
      CacheTypeInfo(TypeKey, TypeDetail);
    end;
  end;

  FColumns.Add(ColumnInfo);
  TSQLDebugLogger.Log('Column info processed and added');
end;

function TSQLParser.ParseColumnExpression(const Expression: string): TSQLColumnInfo;
begin
  Result := TSQLColumnInfo.Create;
  try
    TSQLDebugLogger.Log('Parsing column expression: ' + Expression);

    var OriginalExpr := Expression;
    var HasAlias := False;
    var AliasName := '';
    var ExpressionPart := Expression;

    // Handle CAST function
    if StartsText('CAST(', ExpressionPart) then
    begin
      // Find the matching closing parenthesis
      var ParenCount := 1;
      var EndPos := 6; // Position after 'CAST('

      while (EndPos <= Length(ExpressionPart)) and (ParenCount > 0) do
      begin
        case ExpressionPart[EndPos] of
          '(': Inc(ParenCount);
          ')': Dec(ParenCount);
        end;
        Inc(EndPos);
      end;

      if ParenCount = 0 then
      begin
        // Check for text after the closing parenthesis
        var RemainderText := Copy(ExpressionPart, EndPos).Trim;

        if RemainderText <> '' then
        begin
          // Check for "AS alias" format
          var AsMatch := TRegEx.Match(RemainderText, '^AS\s+(\w+)$', [roIgnoreCase]);
          if AsMatch.Success then
          begin
            ExpressionPart := Copy(ExpressionPart, 1, EndPos - 1);
            AliasName := AsMatch.Groups[1].Value.Trim;
            HasAlias := True;
            TSQLDebugLogger.Log(Format('Found AS alias after CAST: %s', [AliasName]));
          end
          // Check for direct alias (without AS)
          else if TRegEx.IsMatch(RemainderText, '^\w+$') then
          begin
            ExpressionPart := Copy(ExpressionPart, 1, EndPos - 1);
            AliasName := RemainderText;
            HasAlias := True;
            TSQLDebugLogger.Log(Format('Found direct alias after CAST: %s', [AliasName]));
          end;
        end;
      end;
    end
    else
    begin
      // Regular AS alias handling
      var AsMatch := TRegEx.Match(Expression, '(.+?)\s+AS\s+(\w+)$', [roIgnoreCase]);
      if AsMatch.Success then
      begin
        ExpressionPart := AsMatch.Groups[1].Value.Trim;
        AliasName := AsMatch.Groups[2].Value.Trim;
        HasAlias := True;
        TSQLDebugLogger.Log(Format('Found AS alias - Expression: %s, Alias: %s',
          [ExpressionPart, AliasName]));
      end
      else
      begin
        // Space-separated alias (without AS keyword)
        var Parts := SplitExpressionAndAlias(ExpressionPart);
        if Parts.HasAlias then
        begin
          ExpressionPart := Parts.Expression;
          AliasName := Parts.Alias;
          HasAlias := True;
          TSQLDebugLogger.Log(Format('Found space-separated alias - Expression: %s, Alias: %s',
            [ExpressionPart, AliasName]));
        end;
      end;
    end;

    // Check if it's an expression
    if IsExpression(ExpressionPart) then
    begin
      TSQLDebugLogger.Log('Expression detected: ' + ExpressionPart);
      Result.RealSchemaName := '[expression]';
      Result.RealTableName := '[expression]';
      Result.ReferencedTableColumn := ExpressionPart;
      Result.FullName := ExpressionPart;
      if HasAlias then
        Result.QueryColumnAlias := AliasName
      else
        Result.QueryColumnAlias := 'expr_' + IntToStr(FColumns.Count + 1);
    end
    else
    begin
      // Handle normal column reference
      if Pos('.', ExpressionPart) > 0 then
      begin
        var Parts := ExpressionPart.Split(['.']);
        if Length(Parts) = 2 then
        begin
          Result.ReferencedTableColumn := Parts[1].Trim;
          ResolveColumnTableInfo(Result, Parts[0].Trim);
        end;
      end
      else
      begin
        Result.ReferencedTableColumn := ExpressionPart;
        ResolveColumnTableInfo(Result, '');
      end;

      Result.FullName := ExpressionPart;
      if HasAlias then
        Result.QueryColumnAlias := AliasName
      else
        Result.QueryColumnAlias := Result.ReferencedTableColumn;
    end;

  except
    Result.Free;
    raise;
  end;
end;

procedure TSQLParser.HandleColumnAlias(ColumnInfo: TSQLColumnInfo; const ColExpr: string);
begin
  TSQLDebugLogger.Log('Processing column expression for alias: ' + ColExpr);

  // Check for "AS" alias first with expression support
  var AsMatch := TRegEx.Match(ColExpr, '(.+?)\s+AS\s+(\w+)$', [roIgnoreCase]);
  if AsMatch.Success then
  begin
    ColumnInfo.FullName := AsMatch.Groups[1].Value.Trim;
    ColumnInfo.QueryColumnAlias := AsMatch.Groups[2].Value.Trim;
    TSQLDebugLogger.Log(Format('Found AS alias - Expression/Column: %s, Alias: %s',
      [ColumnInfo.FullName, ColumnInfo.QueryColumnAlias]));
    Exit;
  end;

  // Check for space-separated alias, being careful with expressions
  var SpaceMatch := TRegEx.Match(ColExpr, '^(.+?)\s+(\w+)$', [roIgnoreCase]);
  if SpaceMatch.Success then
  begin
    var PossibleAlias := SpaceMatch.Groups[2].Value.Trim;
    var ExpressionPart := SpaceMatch.Groups[1].Value.Trim;

    // Only treat as alias if it's not part of an expression and not a keyword
    if not IsSQLKeywordOrFunction(PossibleAlias) and
       not ContainsOperators(ExpressionPart) then
    begin
      ColumnInfo.FullName := ExpressionPart;
      ColumnInfo.QueryColumnAlias := PossibleAlias;
      TSQLDebugLogger.Log(Format('Found space-separated alias - Expression/Column: %s, Alias: %s',
        [ColumnInfo.FullName, ColumnInfo.QueryColumnAlias]));
      Exit;
    end;
  end;

  // No alias found or expression without alias
  ColumnInfo.FullName := ColExpr;
  if IsExpression(ColExpr) then
  begin
    ColumnInfo.QueryColumnAlias := 'expr_' + IntToStr(FColumns.Count + 1);
    TSQLDebugLogger.Log(Format('Generated alias for expression: %s -> %s',
      [ColExpr, ColumnInfo.QueryColumnAlias]));
  end
  else
    ColumnInfo.QueryColumnAlias := ColExpr;

  TSQLDebugLogger.Log(Format('Final column setup - FullName: %s, Alias: %s',
    [ColumnInfo.FullName, ColumnInfo.QueryColumnAlias]));
end;

function TSQLParser.ContainsOperators(const Expr: string): Boolean;
const
  Operators: array[0..5] of string = ('+', '-', '*', '/', '(', ')');
begin
  Result := False;
  for var Op in Operators do
    if Pos(Op, Expr) > 0 then
    begin
      Result := True;
      Break;
    end;
end;


procedure TSQLParser.ResolveColumnTableInfo(ColumnInfo: TSQLColumnInfo; const TableAlias: string);
begin
  TSQLDebugLogger.Log(Format('Resolving column info - Column: %s, TableAlias: %s',
    [ColumnInfo.ReferencedTableColumn, TableAlias]));

  var QualifiedColumn := TableAlias <> '';
  var TableInfo: TSQLTableInfo := nil;

  // Handle table qualified columns (table.column or alias.column)
  if QualifiedColumn then
  begin
    TableInfo := GetTableInfoByAliasOrName(TableAlias);
    if not Assigned(TableInfo) then
    begin
      TSQLDebugLogger.Log(Format('ERROR: Table or alias not found: %s', [TableAlias]));
      raise Exception.CreateFmt('Table or alias "%s" not found', [TableAlias]);
    end;

    TSQLDebugLogger.Log(Format('Found table for alias %s: %s.%s',
      [TableAlias, TableInfo.SchemaName, TableInfo.TableName]));

    if not ColumnExistsInTable(TableInfo.SchemaName, TableInfo.TableName,
                             ColumnInfo.ReferencedTableColumn) then
    begin
      TSQLDebugLogger.Log(Format('ERROR: Column %s not found in table %s.%s',
        [ColumnInfo.ReferencedTableColumn, TableInfo.SchemaName, TableInfo.TableName]));
      raise Exception.CreateFmt('Column "%s" not found in table "%s.%s"',
        [ColumnInfo.ReferencedTableColumn, TableInfo.SchemaName, TableInfo.TableName]);
    end;

    ColumnInfo.RealSchemaName := TableInfo.SchemaName;
    ColumnInfo.RealTableName := TableInfo.TableName;
    ColumnInfo.ResolvedTableInfo := TableInfo;
  end
  else
  begin
    // Handle unqualified columns
    var FoundInTable := False;

    for var Table in FTables do
    begin
      if ColumnExistsInTable(Table.SchemaName, Table.TableName,
                            ColumnInfo.ReferencedTableColumn) then
      begin
        if not FoundInTable then
        begin
          TableInfo := Table;
          FoundInTable := True;
          TSQLDebugLogger.Log(Format('Found unqualified column %s in table %s.%s',
            [ColumnInfo.ReferencedTableColumn, Table.SchemaName, Table.TableName]));
        end
        else
        begin
          // Column exists in multiple tables
          TSQLDebugLogger.Log(Format('Column %s is ambiguous - exists in multiple tables',
            [ColumnInfo.ReferencedTableColumn]));
          ColumnInfo.RealSchemaName := '[ambiguous]';
          ColumnInfo.RealTableName := '[ambiguous]';
          Exit;
        end;
      end;
    end;

    if FoundInTable and Assigned(TableInfo) then
    begin
      ColumnInfo.RealSchemaName := TableInfo.SchemaName;
      ColumnInfo.RealTableName := TableInfo.TableName;
      ColumnInfo.ResolvedTableInfo := TableInfo;
    end
    else
    begin
      TSQLDebugLogger.Log(Format('ERROR: Column %s not found in any table',
        [ColumnInfo.ReferencedTableColumn]));
      raise Exception.CreateFmt('Column "%s" not found in any referenced table',
        [ColumnInfo.ReferencedTableColumn]);
    end;
  end;
end;

{ Helper functions for wildcard handling }

function TSQLParser.IsWildcard(const ColExpr: string): Boolean;
begin
  Result := (ColExpr = '*') or TRegEx.IsMatch(ColExpr, '^\w+\.\*$');
end;

function TSQLParser.ContainsWildcard(const SelectClause: string): Boolean;
begin
  Result := TRegEx.IsMatch(SelectClause, '\*', []);
end;

{ Table parsing and validation methods }

procedure TSQLParser.ParseFromClause;
begin
  TSQLDebugLogger.Log('Starting FROM clause parsing');

  try
    // Find the actual FROM clause by skipping any subqueries in SELECT
    var SelectPos := Pos('SELECT', UpperCase(FSQLText));
    var MainFromPos := -1;
    var InParentheses := 0;
    var InQuote := False;
    var QuoteChar := #0;

    // Scan for the main FROM clause
    for var I := SelectPos to Length(FSQLText) do
    begin
      case FSQLText[I] of
        '(': if not InQuote then Inc(InParentheses);
        ')': if not InQuote then Dec(InParentheses);
        '''', '"':
          begin
            if InQuote then
            begin
              if FSQLText[I] = QuoteChar then
                InQuote := False;
            end
            else
            begin
              InQuote := True;
              QuoteChar := FSQLText[I];
            end;
          end;
        'F', 'f':
          if (InParentheses = 0) and not InQuote then
          begin
            if I + 3 <= Length(FSQLText) then
            begin
              var Word := UpperCase(Copy(FSQLText, I, 4));
              if Word = 'FROM' then
              begin
                MainFromPos := I;
                Break;
              end;
            end;
          end;
      end;
    end;

    if MainFromPos = -1 then
    begin
      TSQLDebugLogger.Log('ERROR: Cannot find main FROM clause');
      raise Exception.Create('Cannot find FROM clause in the query');
    end;

    TSQLDebugLogger.Log('Found main FROM clause position: ' + IntToStr(MainFromPos));

    // Extract everything after the main FROM
    var RemainingText := Copy(FSQLText, MainFromPos + 4, MaxInt).Trim;
    TSQLDebugLogger.Log('Found FROM clause. Remaining text: ' + RemainingText);

    // Find the end of the FROM clause
    var ClauseEndMatch := TRegEx.Match(RemainingText,
      '\b(WHERE|GROUP\s+BY|HAVING|ORDER\s+BY|LIMIT|OFFSET)\b',
      [roIgnoreCase]);

    var FromClause: string;
    if ClauseEndMatch.Success then
    begin
      FromClause := Copy(RemainingText, 1, ClauseEndMatch.Index - 1).Trim;
      TSQLDebugLogger.Log('FROM clause with end marker: ' + FromClause);
    end
    else
    begin
      FromClause := RemainingText.Trim;
      TSQLDebugLogger.Log('FROM clause without end marker: ' + FromClause);
    end;

    ParseBaseTableAndJoins(FromClause);
    TSQLDebugLogger.Log('Base table and joins parsed');

    BuildTableAliasMap;
    TSQLDebugLogger.Log('Table alias map built');
    LogTableAliasMap;

    ValidateTableReferences;
    TSQLDebugLogger.Log('Table references validated');

  except
    on E: Exception do
    begin
      TSQLDebugLogger.Log('ERROR in ParseFromClause: ' + E.Message);
      raise Exception.Create('Error parsing FROM clause: ' + E.Message);
    end;
  end;
end;

procedure TSQLParser.BuildTableAliasMap;
begin
  TSQLDebugLogger.Log('Building table alias map');
  FTableAliasMap.Clear;

  for var TableInfo in FTables do
  begin
    // Map the table name itself
    var TableName := LowerCase(TableInfo.TableName);
    TSQLDebugLogger.Log(Format('Mapping table name: %s', [TableName]));
    if not FTableAliasMap.ContainsKey(TableName) then
      FTableAliasMap.Add(TableName, TableInfo);

    // Map the schema.table format
    var FullName := LowerCase(Format('%s.%s',
      [TableInfo.SchemaName, TableInfo.TableName]));
    TSQLDebugLogger.Log(Format('Mapping full name: %s', [FullName]));
    if not FTableAliasMap.ContainsKey(FullName) then
      FTableAliasMap.Add(FullName, TableInfo);

    // Map the alias if present
    if TableInfo.Alias <> '' then
    begin
      var Alias := LowerCase(TableInfo.Alias);
      TSQLDebugLogger.Log(Format('Mapping alias: %s', [Alias]));
      if FTableAliasMap.ContainsKey(Alias) then
      begin
        TSQLDebugLogger.Log(Format('WARNING: Duplicate alias found: %s', [Alias]));
        raise Exception.CreateFmt('Duplicate alias found: %s', [Alias]);
      end;
      FTableAliasMap.Add(Alias, TableInfo);
      TSQLDebugLogger.Log(Format('Added alias mapping: %s -> %s.%s',
        [Alias, TableInfo.SchemaName, TableInfo.TableName]));
    end;

    TSQLDebugLogger.Log(Format('Table fully mapped: %s.%s (Alias: %s)',
      [TableInfo.SchemaName, TableInfo.TableName, TableInfo.Alias]));
  end;
end;

procedure TSQLParser.LogTableAliasMap;
begin
  TSQLDebugLogger.Log('--- Table Alias Map ---');
  for var Pair in FTableAliasMap do
  begin
    TSQLDebugLogger.Log(Format('Alias/Name: %s -> Table: %s.%s',
      [Pair.Key, Pair.Value.SchemaName, Pair.Value.TableName]));
  end;
  TSQLDebugLogger.Log('--------------------');
end;

procedure TSQLParser.ValidateTableReferences;
var
  Query: TUniQuery;
begin
  TSQLDebugLogger.Log('Validating table references');
  Query := TUniQuery.Create(nil);
  try
    Query.Connection := FConnection;
    for var TableInfo in FTables do
    begin
      TSQLDebugLogger.Log(Format('Validating table: %s.%s',
        [TableInfo.SchemaName, TableInfo.TableName]));

      Query.SQL.Text :=
        'SELECT 1 FROM information_schema.tables ' +
        'WHERE table_schema = :schema AND table_name = :table';
      Query.ParamByName('schema').AsString := TableInfo.SchemaName;
      Query.ParamByName('table').AsString := TableInfo.TableName;

      try
        Query.Open;
        if Query.IsEmpty then
        begin
          TSQLDebugLogger.Log(Format('ERROR: Table %s.%s does not exist',
            [TableInfo.SchemaName, TableInfo.TableName]));
          raise Exception.CreateFmt('Table %s.%s does not exist',
            [TableInfo.SchemaName, TableInfo.TableName]);
        end;
      except
        on E: Exception do
        begin
          LogParsingError('Error validating table reference', E.Message);
          raise;
        end;
      end;
    end;
  finally
    Query.Free;
  end;
  TSQLDebugLogger.Log('Table reference validation completed');
end;

procedure TSQLParser.ProcessJoinConditions;
begin
  TSQLDebugLogger.Log('Processing join conditions');
  for var TableInfo in FTables do
  begin
    if TableInfo.JoinCondition <> '' then
    begin
      TSQLDebugLogger.Log(Format('Processing join condition for table %s: %s',
        [TableInfo.TableName, TableInfo.JoinCondition]));

      // Validate join condition syntax
      if not TRegEx.IsMatch(TableInfo.JoinCondition,
        '^\s*\w+(\.\w+)?\s*=\s*\w+(\.\w+)?\s*$') then
      begin
        TSQLDebugLogger.Log(Format('Invalid join condition format: %s',
          [TableInfo.JoinCondition]));
        raise Exception.CreateFmt('Invalid join condition format: %s',
          [TableInfo.JoinCondition]);
      end;

      // Extract and validate referenced columns
      var Parts := TableInfo.JoinCondition.Split(['=']);
      if Length(Parts) = 2 then
      begin
        var LeftSide := Parts[0].Trim;
        var RightSide := Parts[1].Trim;

        ValidateJoinColumn(LeftSide);
        ValidateJoinColumn(RightSide);
      end;
    end;
  end;
  TSQLDebugLogger.Log('Join condition processing completed');
end;

procedure TSQLParser.ValidateJoinColumn(const ColumnRef: string);
begin
  var TableAlias := '';
  var ColumnName := ColumnRef;

  // Handle table-qualified columns
  if Pos('.', ColumnRef) > 0 then
  begin
    var Parts := ColumnRef.Split(['.']);
    if Length(Parts) = 2 then
    begin
      TableAlias := Parts[0];
      ColumnName := Parts[1];
    end;
  end;

  var TableInfo: TSQLTableInfo := nil;
  if TableAlias <> '' then
  begin
    TableInfo := GetTableInfoByAliasOrName(TableAlias);
    if not Assigned(TableInfo) then
    begin
      TSQLDebugLogger.Log(Format('Invalid table reference in join condition: %s',
        [TableAlias]));
      raise Exception.CreateFmt('Invalid table reference in join condition: %s',
        [TableAlias]);
    end;
  end;

  if Assigned(TableInfo) then
  begin
    if not ColumnExistsInTable(TableInfo.SchemaName, TableInfo.TableName, ColumnName) then
    begin
      TSQLDebugLogger.Log(Format('Invalid column in join condition: %s.%s',
        [TableInfo.TableName, ColumnName]));
      raise Exception.CreateFmt('Invalid column in join condition: %s.%s',
        [TableInfo.TableName, ColumnName]);
    end;
  end;
end;

{ Column validation and resolution }

procedure TSQLParser.HandleAmbiguousColumns;
var
  ColumnMap: TDictionary<string, TList<TSQLColumnInfo>>;
begin
  TSQLDebugLogger.Log('Checking for ambiguous columns');
  ColumnMap := TDictionary<string, TList<TSQLColumnInfo>>.Create;
  try
    // Group columns by name
    for var Column in FColumns do
    begin
      if Column.RealSchemaName = '[expression]' then
        Continue;

      var NormalizedName := LowerCase(Column.ReferencedTableColumn);
      if not ColumnMap.ContainsKey(NormalizedName) then
        ColumnMap.Add(NormalizedName, TList<TSQLColumnInfo>.Create);
      ColumnMap[NormalizedName].Add(Column);
    end;

    // Check for ambiguous columns
    for var Entry in ColumnMap do
    begin
      if Entry.Value.Count > 1 then
      begin
        var AmbiguousColumns := TStringList.Create;
        try
          for var Column in Entry.Value do
          begin
            AmbiguousColumns.Add(Format('%s.%s',
              [Column.RealTableName, Column.ReferencedTableColumn]));
          end;

          var ColumnList := string.Join(', ', AmbiguousColumns.ToStringArray);
          TSQLDebugLogger.Log(Format('Ambiguous column found: %s in tables: %s',
            [Entry.Key, ColumnList]));
          raise Exception.CreateFmt('Ambiguous column reference: %s appears in multiple tables: %s',
            [Entry.Key, ColumnList]);
        finally
          AmbiguousColumns.Free;
        end;
      end;
    end;

  finally
    for var List in ColumnMap.Values do
      List.Free;
    ColumnMap.Free;
  end;
  TSQLDebugLogger.Log('Ambiguous column check completed');
end;

procedure TSQLParser.ResolveUnknownColumns;
var
  Column: TSQLColumnInfo;
begin
  TSQLDebugLogger.Log('Resolving unknown columns');
  for Column in FColumns do
  begin
    if (Column.RealSchemaName = '[unknown]') or
       (Column.RealTableName = '[unknown]') then
    begin
      TSQLDebugLogger.Log(Format('Processing unknown column: %s',
        [Column.ReferencedTableColumn]));

      // Skip expressions
      if not IsExpression(Column.ReferencedTableColumn) then
      begin
        // Try to find the column in available tables
        var Found := False;
        for var Table in FTables do
        begin
          if ColumnExistsInTable(Table.SchemaName, Table.TableName,
             Column.ReferencedTableColumn) then
          begin
            if not Found then
            begin
              Column.RealSchemaName := Table.SchemaName;
              Column.RealTableName := Table.TableName;
              Column.ResolvedTableInfo := Table;
              Found := True;
              TSQLDebugLogger.Log(Format('Column resolved to table: %s.%s',
                [Table.SchemaName, Table.TableName]));
            end
            else
            begin
              // Column exists in multiple tables
              Column.RealSchemaName := '[ambiguous]';
              Column.RealTableName := '[ambiguous]';
              TSQLDebugLogger.Log(Format('Column %s is ambiguous',
                [Column.ReferencedTableColumn]));
              Break;
            end;
          end;
        end;

        if not Found then
        begin
          TSQLDebugLogger.Log(Format('Column %s not found in any table',
            [Column.ReferencedTableColumn]));
          raise Exception.CreateFmt('Column "%s" not found in any referenced table',
            [Column.ReferencedTableColumn]);
        end;
      end
      else
      begin
        Column.RealSchemaName := '[expression]';
        Column.RealTableName := '[expression]';
        TSQLDebugLogger.Log(Format('Column %s marked as expression',
          [Column.ReferencedTableColumn]));
      end;
    end;
  end;
  TSQLDebugLogger.Log('Unknown column resolution completed');
end;

function TSQLParser.ValidateQuery: Boolean;
begin
  Result := False;
  TSQLDebugLogger.Log('Starting query validation');

  try
    ValidateTableReferences;
    TSQLDebugLogger.Log('Table references validated');

    ValidateColumns;
    TSQLDebugLogger.Log('Columns validated');

    ProcessJoinConditions;
    TSQLDebugLogger.Log('Join conditions processed');

    HandleAmbiguousColumns;
    TSQLDebugLogger.Log('Ambiguous columns handled');

    Result := True;
    TSQLDebugLogger.Log('Query validation completed successfully');
  except
    on E: Exception do
    begin
      LogParsingError('Query validation failed', E.Message);
      Result := False;
    end;
  end;
end;

{ Utility methods }

function TSQLParser.GetFullColumnName(const ColumnInfo: TSQLColumnInfo): string;
begin
  if ColumnInfo.RealSchemaName = '[expression]' then
    Result := ColumnInfo.FullName
  else
    Result := Format('%s.%s.%s',
      [ColumnInfo.RealSchemaName, ColumnInfo.RealTableName, ColumnInfo.ReferencedTableColumn]);
end;

procedure TSQLParser.ClearCache;
begin
  TSQLDebugLogger.Log('Clearing parser caches');
  ClearColumnCache;
  FTypeCache.Clear;
  FProcessedWildcards.Clear;
  TSQLDebugLogger.Log('Parser caches cleared');
end;

procedure TSQLParser.DumpMetadata(const FileName: string = '');
var
  OutputFile: TextFile;
  OutputFileName: string;
begin
  if FileName = '' then
    OutputFileName := ChangeFileExt(ParamStr(0), '.sqlmetadata')
  else
    OutputFileName := FileName;

  TSQLDebugLogger.Log('Dumping metadata to: ' + OutputFileName);

  AssignFile(OutputFile, OutputFileName);
  try
    Rewrite(OutputFile);
    WriteLn(OutputFile, 'SQL Parser Metadata Dump');
    WriteLn(OutputFile, '=====================');
    WriteLn(OutputFile);

    WriteLn(OutputFile, 'Original SQL:');
    WriteLn(OutputFile, FSQLText);
    WriteLn(OutputFile);

    WriteLn(OutputFile, 'Tables:');
    for var Table in FTables do
    begin
      WriteLn(OutputFile, Format('- %s.%s (Alias: %s)',
        [Table.SchemaName, Table.TableName, Table.Alias]));
      if Table.JoinType <> '' then
      begin
        WriteLn(OutputFile, Format('  Join Type: %s', [Table.JoinType]));
        WriteLn(OutputFile, Format('  Join Condition: %s', [Table.JoinCondition]));
      end;
    end;

    WriteLn(OutputFile);
    WriteLn(OutputFile, 'Columns:');
    for var Column in FColumns do
    begin
      WriteLn(OutputFile, Format('- %s.%s.%s -> %s',
[Column.RealSchemaName,
         Column.RealTableName,
         Column.ReferencedTableColumn,
         Column.QueryColumnAlias]));
      WriteLn(OutputFile, Format('  Type: %s',
        [GetEnumName(TypeInfo(TFieldType), Ord(Column.DataType))]));
      WriteLn(OutputFile, Format('  Length: %d, Precision: %d, Scale: %d',
        [Column.TypeDetail.Length,
         Column.TypeDetail.Precision,
         Column.TypeDetail.Scale]));
      if Column.IsPKColumn then
        WriteLn(OutputFile, '  Primary Key: Yes');
      if Column.IsWildcard then
        WriteLn(OutputFile, '  Expanded from wildcard');
    end;
  finally
    CloseFile(OutputFile);
  end;
  TSQLDebugLogger.Log('Metadata dump completed');
end;

procedure TSQLParser.LogParsedTables;
begin
  TSQLDebugLogger.Log('--- Parsed Tables ---');
  for var Table in FTables do
  begin
    TSQLDebugLogger.Log(Format('Table: %s.%s (Alias: %s)',
      [Table.SchemaName, Table.TableName, Table.Alias]));
    TSQLDebugLogger.Log('  Join Type: ' + Table.JoinType);
    if Table.JoinCondition <> '' then
      TSQLDebugLogger.Log('  Join Condition: ' + Table.JoinCondition);

    // Log columns if loaded
    if Table.Columns.Count > 0 then
    begin
      TSQLDebugLogger.Log('  Columns:');
      for var Column in Table.Columns do
        TSQLDebugLogger.Log('    - ' + Column);
    end;

    // Log primary keys if loaded
    if Table.PrimaryKeys.Count > 0 then
    begin
      TSQLDebugLogger.Log('  Primary Keys:');
      for var PK in Table.PrimaryKeys do
        TSQLDebugLogger.Log('    - ' + PK);
    end;
  end;
  TSQLDebugLogger.Log('------------------');
end;

procedure TSQLParser.LogParsedColumns;
begin
  TSQLDebugLogger.Log('--- Parsed Columns ---');
  for var Column in FColumns do
  begin
    TSQLDebugLogger.Log(Format('Column: %s', [Column.FullName]));
    TSQLDebugLogger.Log(Format('  Referenced Table: %s.%s',
      [Column.RealSchemaName, Column.RealTableName]));
    TSQLDebugLogger.Log(Format('  Referenced Column: %s',
      [Column.ReferencedTableColumn]));
    TSQLDebugLogger.Log(Format('  Alias: %s', [Column.QueryColumnAlias]));
    TSQLDebugLogger.Log(Format('  Is PK: %s', [BoolToStr(Column.IsPKColumn, True)]));
    TSQLDebugLogger.Log(Format('  Type: %s',
      [GetEnumName(TypeInfo(TFieldType), Ord(Column.DataType))]));
    if Column.IsWildcard then
      TSQLDebugLogger.Log('  Expanded from wildcard');
  end;
  TSQLDebugLogger.Log('------------------');
end;

{ SQL Text Normalization }

function TSQLParser.NormalizeSQLText(const SQL: string): string;
var
  InQuote: Boolean;
  QuoteChar: Char;
  I: Integer;
  PrevChar: Char;
  ResultStr: TStringBuilder;
  CurrentChar: Char;
  LastCharWasSpace: Boolean;
begin
  ResultStr := TStringBuilder.Create;
  try
    InQuote := False;
    QuoteChar := #0;
    PrevChar := #0;
    LastCharWasSpace := False;

    for I := 1 to Length(SQL) do
    begin
      CurrentChar := SQL[I];

      // Handle quotes
      if (CurrentChar in ['"', '''']) then
      begin
        if InQuote then
        begin
          if (CurrentChar = QuoteChar) and (PrevChar <> '\') then
          begin
            InQuote := False;
            QuoteChar := #0;
          end;
        end
        else
        begin
          InQuote := True;
          QuoteChar := CurrentChar;
        end;
        ResultStr.Append(CurrentChar);
        LastCharWasSpace := False;
      end
      // Handle whitespace
      else if CharInSet(CurrentChar, [#9, #10, #13, #32]) then
      begin
        if InQuote then
        begin
          // Preserve spaces in quotes
          ResultStr.Append(CurrentChar);
          LastCharWasSpace := False;
        end
        else if not LastCharWasSpace then
        begin
          // Convert any whitespace sequence to a single space
          ResultStr.Append(' ');
          LastCharWasSpace := True;
        end;
      end
      else
      begin
        ResultStr.Append(CurrentChar);
        LastCharWasSpace := False;
      end;

      PrevChar := CurrentChar;
    end;

    Result := ResultStr.ToString.Trim;
  finally
    ResultStr.Free;
  end;
end;

{ Type handling methods }

procedure TSQLParser.ResolveColumnTypes;
begin
  TSQLDebugLogger.Log('Starting column type resolution');
  ResolveExpressionTypes;
  TSQLDebugLogger.Log('Column type resolution completed');
end;

function TSQLParser.MapSQLTypeToFieldType(const SQLType: string): TFieldType;
begin
  // Handle common SQL types
  if ContainsText(SQLType, 'char') or ContainsText(SQLType, 'text') then
    Result := ftString
  else if ContainsText(SQLType, 'int') then
  begin
    if ContainsText(SQLType, 'tiny') then
      Result := ftSmallint
    else if ContainsText(SQLType, 'small') then
      Result := ftSmallint
    else if ContainsText(SQLType, 'big') then
      Result := ftLargeint
    else
      Result := ftInteger;
  end
  else if ContainsText(SQLType, 'float') or ContainsText(SQLType, 'double') then
    Result := ftFloat
  else if ContainsText(SQLType, 'decimal') or ContainsText(SQLType, 'numeric') then
    Result := ftBCD
  else if ContainsText(SQLType, 'date') then
    Result := ftDate
  else if ContainsText(SQLType, 'time') then
  begin
    if ContainsText(SQLType, 'timestamp') then
      Result := ftDateTime
    else
      Result := ftTime;
  end
  else if ContainsText(SQLType, 'bool') then
    Result := ftBoolean
  else if ContainsText(SQLType, 'blob') or ContainsText(SQLType, 'binary') then
    Result := ftBlob
  else
    Result := ftUnknown;

  TSQLDebugLogger.Log(Format('Mapped SQL type %s to field type %s',
    [SQLType, GetEnumName(TypeInfo(TFieldType), Ord(Result))]));
end;

{ Additional helper methods }

function TSQLParser.IsValidColumnName(const ColumnName: string): Boolean;
begin
  Result := TRegEx.IsMatch(ColumnName, '^[a-zA-Z_][a-zA-Z0-9_]*$');
end;

procedure TSQLParser.LogParsingError(const ErrorMessage: string; const Context: string = '');
begin
  if Context <> '' then
    TSQLDebugLogger.Log(Format('Error: %s (Context: %s)', [ErrorMessage, Context]))
  else
    TSQLDebugLogger.Log('Error: ' + ErrorMessage);
end;

function TSQLParser.GetEffectiveSchemaName(const SchemaName: string): string;
begin
  if SchemaName = '' then
    Result := LowerCase(FConnection.Database)
  else
    Result := LowerCase(SchemaName);
end;

{ Column Type Detail initialization }

procedure TColumnTypeDetail.Initialize;
begin
  BaseType := ftUnknown;
  Length := 0;
  Precision := 0;
  Scale := 0;
  IsNullable := True;
  HasDefault := False;
  DefaultValue := '';
  CharacterSet := '';
  Collation := '';
end;

class constructor TSQLDebugLogger.Create;
begin
  FIsInitialized := False;
  FEnabled := True;  // Enable logging by default
end;

class destructor TSQLDebugLogger.Destroy;
begin
  if FIsInitialized then
  begin
    CloseFile(FLogFile);
    FIsInitialized := False;
  end;
end;

class procedure TSQLDebugLogger.Initialize;
begin
  if not FIsInitialized then
  begin
    FLogFileName := TPath.Combine(
      TPath.GetDirectoryName(ParamStr(0)),
      'SQLParser_' + FormatDateTime('yyyymmdd', Now) + '.log'
    );


    if not FileExists(FLogFileName) then
    begin
      AssignFile(FLogFile, FLogFileName);
      Rewrite(FLogFile);
    end
    else
    begin
      AssignFile(FLogFile, FLogFileName);
      Append(FLogFile);
    end;

    FIsInitialized := True;

    // Write header when creating new file
    if TFile.GetSize(FLogFileName) = 0 then
      Log('SQL Parser Debug Log - Started at ' + DateTimeToStr(Now));
  end;
end;

function TSQLParser.GetTableInfoByAliasOrName(const AliasOrName: string): TSQLTableInfo;
begin
  TSQLDebugLogger.Log('Looking up table by alias/name: ' + AliasOrName);
  Result := nil;

  var LowerName := LowerCase(AliasOrName);
  TSQLDebugLogger.Log('Normalized name for lookup: ' + LowerName);

  // Try in alias map
  if FTableAliasMap.TryGetValue(LowerName, Result) then
  begin
    TSQLDebugLogger.Log(Format('Found in alias map: %s -> %s.%s',
      [LowerName, Result.SchemaName, Result.TableName]));
    Exit;
  end;

  // Try by table name or alias directly
  for var Table in FTables do
  begin
    TSQLDebugLogger.Log(Format('Checking table: %s (Alias: %s)',
      [Table.TableName, Table.Alias]));

    if SameText(Table.TableName, AliasOrName) or
       SameText(Table.Alias, AliasOrName) then
    begin
      TSQLDebugLogger.Log('Found match by direct comparison');
      Exit(Table);
    end;
  end;

  // Try by full name
  for var Table in FTables do
  begin
    var FullName := Format('%s.%s', [Table.SchemaName, Table.TableName]);
    if SameText(FullName, AliasOrName) then
    begin
      TSQLDebugLogger.Log('Found match by full name');
      Exit(Table);
    end;
  end;

  TSQLDebugLogger.Log(Format('No table found for alias/name: %s', [AliasOrName]));
end;

class procedure TSQLDebugLogger.Log(const Msg: string);
begin
  if not FEnabled then
    Exit;

  if not FIsInitialized then
    Initialize;

  try
    WriteLn(FLogFile, FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz', Now) + ' - ' + Msg);
    Flush(FLogFile);
  except
    // Silently ignore errors in debug logging
  end;
end;

function TSQLParser.ContainsSubquery(const Expr: string): Boolean;
var
  ParenCount: Integer;
  InQuote: Boolean;
  QuoteChar: Char;
  I: Integer;
  SelectFound: Boolean;
begin
  Result := False;
  ParenCount := 0;
  InQuote := False;
  QuoteChar := #0;
  SelectFound := False;

  // First check if we have a SELECT somewhere after an opening parenthesis
  var UpperExpr := UpperCase(Expr);
  if not (Pos('(SELECT', UpperExpr) > 0) then
    Exit;

  for I := 1 to Length(Expr) do
  begin
    case Expr[I] of
      '''', '"':
        begin
          if InQuote then
          begin
            if Expr[I] = QuoteChar then
              InQuote := False;
          end
          else
          begin
            InQuote := True;
            QuoteChar := Expr[I];
          end;
        end;

      '(':
        if not InQuote then
          Inc(ParenCount);

      ')':
        if not InQuote then
          Dec(ParenCount);

      'S', 's':
        if not InQuote and (ParenCount > 0) then
        begin
          if I + 5 <= Length(Expr) then
          begin
            var NextWord := UpperCase(Copy(Expr, I, 6));
            if NextWord = 'SELECT' then
            begin
              SelectFound := True;
              Break;
            end;
          end;
        end;
    end;
  end;

  Result := SelectFound;
  if Result then
    TSQLDebugLogger.Log('Subquery detected in expression');
end;

function TSQLParser.IsExpression(const Expr: string): Boolean;
// Handle aggregate functions as expressions
const AggregateFunctions: array[0..8] of string = ('COUNT(', 'SUM(', 'AVG(', 'MIN(', 'MAX(', 'GROUP_CONCAT(', 'STDDEV(', 'VARIANCE(', 'COUNT_DISTINCT(');
begin
  Result := False;

  if Expr = '' then
    Exit;

  var NormalizedExpr := Expr;

  // Handle subqueries
  if ContainsSubquery(NormalizedExpr) then
  begin
    TSQLDebugLogger.Log('Expression identified as subquery');
    Exit(True);
  end;

  for var Func in AggregateFunctions do
  begin
    if ContainsText(NormalizedExpr, Func) then
    begin
      TSQLDebugLogger.Log('Expression identified as aggregate function: ' + Func);
      Exit(True);
    end;
  end;

  // Handle window functions as expressions
  if ContainsText(NormalizedExpr, 'OVER(') or
     ContainsText(NormalizedExpr, 'OVER (') then
  begin
    TSQLDebugLogger.Log('Expression identified as window function');
    Exit(True);
  end;

  // Handle CAST function specially
  if StartsText('CAST(', NormalizedExpr) then
  begin
    TSQLDebugLogger.Log('Expression identified as CAST function');
    Exit(True);
  end;

  // Check for string literals (quoted strings)
  if TRegEx.IsMatch(NormalizedExpr, '^\s*"[^"]*"\s*$') or
     TRegEx.IsMatch(NormalizedExpr, '^\s*''[^'']*''\s*$') then
  begin
    TSQLDebugLogger.Log('Expression identified as string literal');
    Exit(True);
  end;

  // Check for numeric literals
  if TRegEx.IsMatch(NormalizedExpr, '^\s*\d+(\.\d*)?([eE][+-]?\d+)?\s*$') then
  begin
    TSQLDebugLogger.Log('Expression identified as numeric literal');
    Exit(True);
  end;

  // Check for functions
  if TRegEx.IsMatch(NormalizedExpr, '\w+\s*\(.*\)') then
  begin
    TSQLDebugLogger.Log('Expression identified as function call');
    Exit(True);
  end;

  // Check for CASE expressions
  if Pos('CASE', UpperCase(NormalizedExpr)) > 0 then
  begin
    TSQLDebugLogger.Log('Expression identified as CASE statement');
    Exit(True);
  end;

  // Check for arithmetic operators
  for var Op in ['+', '-', '*', '/', '=', '>', '<', '>=', '<=', '<>'] do
    if Pos(Op, NormalizedExpr) > 0 then
    begin
      TSQLDebugLogger.Log('Expression identified by operator: ' + Op);
      Exit(True);
    end;

  // Check for logical operators
  for var Op in [' AND ', ' OR ', ' IN ', ' BETWEEN '] do
    if Pos(Op, UpperCase(NormalizedExpr)) > 0 then
    begin
      TSQLDebugLogger.Log('Expression identified by logical operator: ' + Op);
      Exit(True);
    end;
end;

function TSQLParser.IsSQLKeywordOrFunction(const Word: string): Boolean;
const
  SQLKeywords: array[0..54] of string = (
    'SELECT', 'FROM', 'WHERE', 'GROUP', 'BY', 'HAVING', 'ORDER',
    'LIMIT', 'OFFSET', 'JOIN', 'INNER', 'OUTER', 'LEFT', 'RIGHT',
    'FULL', 'CROSS', 'UNION', 'ALL', 'DISTINCT', 'AS', 'ON', 'AND',
    'OR', 'NOT', 'IN', 'EXISTS', 'BETWEEN', 'LIKE', 'IS', 'NULL',
    'TRUE', 'FALSE', 'ASC', 'DESC', 'COUNT', 'SUM', 'AVG', 'MIN',
    'MAX', 'CASE', 'WHEN', 'THEN', 'ELSE', 'END', 'CAST', 'COALESCE',
    'NULLIF', 'EXTRACT', 'SUBSTRING', 'UPPER', 'INTEGER', 'UNSIGNED',
    'DECIMAL', 'NUMERIC', 'FLOAT'
  );
begin
  Result := False;
  for var Keyword in SQLKeywords do
    if SameText(Word, Keyword) then
      Exit(True);
end;

procedure TSQLParser.LoadTableColumnsMetadata;
var
  PKQuery: TUniQuery;
  SchemaList: TStringList;
  PKDict: TDictionary<string, TStringList>;
begin
  if not Assigned(FConnection) or (FTables.Count = 0) then
    Exit;

  TSQLDebugLogger.Log('Starting metadata load');

  PKDict := TDictionary<string, TStringList>.Create;
  SchemaList := TStringList.Create;
  PKQuery := TUniQuery.Create(nil);
  try
    PKQuery.Connection := FConnection;

    // Build list of unique schemas
    for var TableInfo in FTables do
      if (TableInfo.SchemaName <> '') and (SchemaList.IndexOf(TableInfo.SchemaName) = -1) then
        SchemaList.Add(TableInfo.SchemaName);

    if SchemaList.Count = 0 then
    begin
      TSQLDebugLogger.Log('No schemas found in tables');
      Exit;
    end;

    // Get PK information for each schema
    for var Schema in SchemaList do
    begin
      TSQLDebugLogger.Log('Getting PK info for schema: ' + Schema);

      PKQuery.SQL.Text :=
        'SELECT TABLE_NAME, GROUP_CONCAT(COLUMN_NAME) as pk_columns ' +
        'FROM information_schema.KEY_COLUMN_USAGE ' +
        'WHERE TABLE_SCHEMA = :schema AND CONSTRAINT_NAME = ''PRIMARY'' ' +
        'GROUP BY TABLE_NAME';

      PKQuery.ParamByName('schema').AsString := Schema;
      TSQLDebugLogger.Log('PK Query: ' + PKQuery.SQL.Text);

      PKQuery.Open;

      while not PKQuery.Eof do
      begin
        var TableName := LowerCase(PKQuery.FieldByName('TABLE_NAME').AsString);
        var PKColumns := TStringList.Create;
        PKColumns.Delimiter := ',';
        PKColumns.StrictDelimiter := True;
        PKColumns.DelimitedText := LowerCase(PKQuery.FieldByName('pk_columns').AsString);

        var Key := LowerCase(Schema + '.' + TableName);
        PKDict.Add(Key, PKColumns);

        TSQLDebugLogger.Log(Format('Found PKs for %s: %s',
          [Key, PKQuery.FieldByName('pk_columns').AsString]));

        PKQuery.Next;
      end;

      PKQuery.Close;
    end;

    // Update PK status for columns
    for var Column in FColumns do
    begin
      if (Column.RealSchemaName <> '[expression]') and
         (Column.RealSchemaName <> '[unknown]') and
         (Column.RealSchemaName <> '') then
      begin
        var TableKey := LowerCase(Column.RealSchemaName + '.' + Column.RealTableName);
        var ColumnName := LowerCase(Column.ReferencedTableColumn);

        if PKDict.ContainsKey(TableKey) then
        begin
          Column.IsPKColumn := PKDict[TableKey].IndexOf(ColumnName) >= 0;
          if Column.IsPKColumn then
            TSQLDebugLogger.Log(Format('Marked as PK: %s.%s.%s',
              [Column.RealSchemaName, Column.RealTableName, Column.ReferencedTableColumn]));
        end;
      end;
    end;

  finally
    // Clean up
    for var List in PKDict.Values do
      List.Free;
    PKDict.Free;
    SchemaList.Free;
    PKQuery.Free;

    TSQLDebugLogger.Log('Finished metadata load');
    TSQLDebugLogger.Log('----------------------------------------');
  end;
end;

procedure TSQLParser.ValidateColumns;
var
  Column: TSQLColumnInfo;
begin
  for Column in FColumns do
  begin
    // Skip expressions and already validated columns
    if IsExpression(Column.ReferencedTableColumn) or
       (Column.RealSchemaName = '[expression]') or
       (Column.RealTableName = '[expression]') then
      Continue;

    // Try to resolve unresolved columns
    if (Column.RealSchemaName = '[unknown]') or
       (Column.RealTableName = '[unknown]') then
    begin
      var FoundInAnyTable := False;
      var AmbiguousColumn := False;

      for var Table in FTables do
      begin
        if ColumnExistsInTable(Table.SchemaName, Table.TableName, Column.ReferencedTableColumn) then
        begin
          if not FoundInAnyTable then
          begin
            Column.RealSchemaName := Table.SchemaName;
            Column.RealTableName := Table.TableName;
            FoundInAnyTable := True;
          end
          else
          begin
            // Column exists in multiple tables
            Column.RealSchemaName := '[ambiguous]';
            Column.RealTableName := '[ambiguous]';
            AmbiguousColumn := True;
            Break;
          end;
        end;
      end;

      if not FoundInAnyTable and not AmbiguousColumn then
      begin
        // Column not found in any table
        raise Exception.CreateFmt(
          'Column "%s" not found in any referenced table',
          [Column.ReferencedTableColumn]
        );
      end;
    end
    else
    begin
      // Validate that the column exists in its assigned table
      if not ColumnExistsInTable(Column.RealSchemaName, Column.RealTableName, Column.ReferencedTableColumn) then
      begin
        raise Exception.CreateFmt(
          'Column "%s" not found in table "%s.%s"',
          [Column.ReferencedTableColumn, Column.RealSchemaName, Column.RealTableName]
        );
      end;
    end;
  end;
end;

procedure TSQLParser.ParseBaseTableAndJoins(const FromClause: string);
begin
  TSQLDebugLogger.Log('Parsing base table and joins: ' + FromClause);
  if FromClause = '' then
    raise Exception.Create('Empty FROM clause');

  try
    // Find the first JOIN if exists
    var JoinPos := Pos(' JOIN ', UpperCase(FromClause + ' '));
    if JoinPos = 0 then
    begin
      // No JOINs - just parse the single table
      var TableInfo := ParseTableReference(FromClause.Trim);
      if Assigned(TableInfo) then
        FTables.Add(TableInfo);
      Exit;
    end;

    // Parse base table (everything before the first JOIN)
    var BaseTablePart := Copy(FromClause, 1, JoinPos - 1).Trim;
    TSQLDebugLogger.Log('Parsing base table: ' + BaseTablePart);

    if BaseTablePart = '' then
      raise Exception.Create('Missing base table');

    var TableInfo := ParseTableReference(BaseTablePart);
    if Assigned(TableInfo) then
      FTables.Add(TableInfo);

    // Process JOINs
    var RemainingPart := Copy(FromClause, JoinPos, MaxInt).Trim;
    while RemainingPart <> '' do
    begin
      var JoinMatch := TRegEx.Match(RemainingPart,
        '^\s*(LEFT|RIGHT|INNER|OUTER|CROSS|FULL)?\s*JOIN\s+(\w+(?:\.\w+)?(?:\s+(?:AS\s+)?\w+)?)\s+ON\s+(.+?)(?=\s+(?:LEFT|RIGHT|INNER|OUTER|CROSS|FULL)?\s*JOIN\s+|$)',
        [roIgnoreCase]);

      if not JoinMatch.Success then
      begin
        // Try simpler pattern for basic JOIN
        JoinMatch := TRegEx.Match(RemainingPart,
          '^\s*JOIN\s+(\w+(?:\.\w+)?(?:\s+(?:AS\s+)?\w+)?)\s+ON\s+(.+)$',
          [roIgnoreCase]);

        if not JoinMatch.Success then
          Break;
      end;

      var JoinType := JoinMatch.Groups[1].Value.Trim.ToUpper;
      if JoinType = '' then
        JoinType := 'INNER';

      var TableRef := JoinMatch.Groups[2].Value.Trim;
      TSQLDebugLogger.Log(Format('Processing join: Type=%s, Table=%s',
        [JoinType, TableRef]));

      TableInfo := ParseTableReference(TableRef);
      if Assigned(TableInfo) then
      begin
        TableInfo.JoinType := JoinType;
        TableInfo.JoinCondition := JoinMatch.Groups[3].Value.Trim;
        FTables.Add(TableInfo);
        TSQLDebugLogger.Log(Format('Added joined table: %s (Alias: %s)',
          [TableInfo.TableName, TableInfo.Alias]));
      end;

      var NextPartStart := JoinMatch.Index + JoinMatch.Length;
      if NextPartStart >= Length(RemainingPart) then
        Break;

      RemainingPart := Copy(RemainingPart, NextPartStart + 1, MaxInt).Trim;
    end;

  except
    on E: Exception do
    begin
      TSQLDebugLogger.Log('Error parsing tables and joins: ' + E.Message);
      raise;
    end;
  end;
end;

function TSQLParser.ParseTableReference(const TableRef: string): TSQLTableInfo;
begin
  TSQLDebugLogger.Log('Parsing table reference: ' + TableRef);
  if TableRef.Trim = '' then
    Exit(nil);

  Result := TSQLTableInfo.Create(FConnection);
  try
    var Parts: TArray<string>;
    var TablePart: string;

    // Split by spaces but handle expressions and quoted strings
    var SplitPos := 1;
    var InQuote := False;
    var QuoteChar := #0;
    var InParens := 0;
    var SpaceParts := TList<string>.Create;
    try
      var CurrentPart := '';

      for var I := 1 to Length(TableRef) do
      begin
        case TableRef[I] of
          '''', '"':
            begin
              if InQuote then
              begin
                if TableRef[I] = QuoteChar then
                  InQuote := False;
              end
              else
              begin
                InQuote := True;
                QuoteChar := TableRef[I];
              end;
              CurrentPart := CurrentPart + TableRef[I];
            end;
          '(':
            begin
              if not InQuote then
                Inc(InParens);
              CurrentPart := CurrentPart + TableRef[I];
            end;
          ')':
            begin
              if not InQuote then
                Dec(InParens);
              CurrentPart := CurrentPart + TableRef[I];
            end;
          ' ':
            begin
              if not InQuote and (InParens = 0) then
              begin
                if CurrentPart <> '' then
                begin
                  SpaceParts.Add(CurrentPart);
                  CurrentPart := '';
                end;
              end
              else
                CurrentPart := CurrentPart + TableRef[I];
            end;
          else
            CurrentPart := CurrentPart + TableRef[I];
        end;
      end;

      if CurrentPart <> '' then
        SpaceParts.Add(CurrentPart);

      // Convert to array
      Parts := SpaceParts.ToArray;
    finally
      SpaceParts.Free;
    end;

    if Length(Parts) = 0 then
    begin
      Result.Free;
      Exit(nil);
    end;

    // Get table part (first element)
    TablePart := Parts[0];
    TSQLDebugLogger.Log('Table part: ' + TablePart);

    // Handle schema.table format
    if Pos('.', TablePart) > 0 then
    begin
      var SchemaParts := TablePart.Split(['.']);
      if Length(SchemaParts) = 2 then
      begin
        Result.SchemaName := LowerCase(SchemaParts[0].Trim(['"', '[', ']', ' ']));
        Result.TableName := LowerCase(SchemaParts[1].Trim(['"', '[', ']', ' ', ')']));
        TSQLDebugLogger.Log(Format('Found schema.table: %s.%s',
          [Result.SchemaName, Result.TableName]));
      end;
    end
    else
    begin
      Result.SchemaName := LowerCase(FConnection.Database);
      Result.TableName := LowerCase(TablePart.Trim(['"', '[', ']', ' ', ')']));
      TSQLDebugLogger.Log(Format('Using default schema: %s.%s',
        [Result.SchemaName, Result.TableName]));
    end;

    // Handle alias - look for the alias part after the table name
    if Length(Parts) > 1 then
    begin
      // Check each part after the table name
      for var i := 1 to High(Parts) do
      begin
        var CurrentPart := Parts[i].ToUpper;
        // Skip join-related keywords
        if (CurrentPart = 'AS') or
           (CurrentPart = 'INNER') or
           (CurrentPart = 'LEFT') or
           (CurrentPart = 'RIGHT') or
           (CurrentPart = 'OUTER') or
           (CurrentPart = 'JOIN') or
           (CurrentPart = 'CROSS') or
           (CurrentPart = 'FULL') then
          Continue;

        // Found alias
        Result.Alias := Parts[i].Trim(['"', '[', ']', ' ', ')']);
        TSQLDebugLogger.Log('Found alias: ' + Result.Alias);
        Break;
      end;
    end;

    TSQLDebugLogger.Log(Format('Table reference parsed: Schema=%s, Table=%s, Alias=%s',
      [Result.SchemaName, Result.TableName, Result.Alias]));

  except
    Result.Free;
    raise;
  end;
end;

function TSQLParser.GetTypeDetailFromDB(const SchemaName, TableName,
  ColumnName: string): TColumnTypeDetail;
var
  Query: TUniQuery;
begin
  Result.Initialize;

  if (SchemaName = '') or (TableName = '') or (ColumnName = '') then
    Exit;

  Query := TUniQuery.Create(nil);
  try
    Query.Connection := FConnection;
    Query.SQL.Text :=
      'SELECT DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, ' +
      'NUMERIC_SCALE, IS_NULLABLE, COLUMN_DEFAULT, ' +
      'CHARACTER_SET_NAME, COLLATION_NAME ' +
      'FROM INFORMATION_SCHEMA.COLUMNS ' +
      'WHERE TABLE_SCHEMA = :schema ' +
      'AND TABLE_NAME = :table ' +
      'AND COLUMN_NAME = :column';

    Query.ParamByName('schema').AsString := SchemaName;
    Query.ParamByName('table').AsString := TableName;
    Query.ParamByName('column').AsString := ColumnName;

    Query.Open;
    if not Query.IsEmpty then
    begin
      Result.BaseType := MapSQLTypeToFieldType(Query.FieldByName('DATA_TYPE').AsString);
      Result.Length := Query.FieldByName('CHARACTER_MAXIMUM_LENGTH').AsInteger;
      Result.Precision := Query.FieldByName('NUMERIC_PRECISION').AsInteger;
      Result.Scale := Query.FieldByName('NUMERIC_SCALE').AsInteger;
      Result.IsNullable := SameText(Query.FieldByName('IS_NULLABLE').AsString, 'YES');
      Result.HasDefault := not Query.FieldByName('COLUMN_DEFAULT').IsNull;
      Result.DefaultValue := Query.FieldByName('COLUMN_DEFAULT').AsString;
      Result.CharacterSet := Query.FieldByName('CHARACTER_SET_NAME').AsString;
      Result.Collation := Query.FieldByName('COLLATION_NAME').AsString;
    end;
  finally
    Query.Free;
  end;
end;

procedure TSQLParser.CacheTypeInfo(const Key: string; const TypeDetail: TColumnTypeDetail);
begin
  if not FTypeCache.ContainsKey(Key) then
    FTypeCache.Add(Key, TypeDetail);
end;

function TSQLParser.GetTableColumnNames(const SchemaName, TableName: string): TList<string>;
var
  Query: TUniQuery;
  CacheKey: string;
  ColumnList: TList<string>;
begin
  Result := nil;

  if (SchemaName = '') or (TableName = '') then
    Exit;

  // Create cache key
  CacheKey := LowerCase(Format('%s.%s', [SchemaName, TableName]));

  // Check cache first
  if FColumnCache.TryGetValue(CacheKey, Result) then
    Exit;

  // Create new list for this table
  ColumnList := TList<string>.Create;
  try
    Query := TUniQuery.Create(nil);
    try
      Query.Connection := FConnection;
      Query.SQL.Text :=
        'SELECT COLUMN_NAME ' +
        'FROM INFORMATION_SCHEMA.COLUMNS ' +
        'WHERE TABLE_SCHEMA = :schema ' +
        'AND TABLE_NAME = :table ' +
        'ORDER BY ORDINAL_POSITION';

      Query.ParamByName('schema').AsString := SchemaName;
      Query.ParamByName('table').AsString := TableName;

      try
        Query.Open;
        while not Query.Eof do
        begin
          ColumnList.Add(LowerCase(Query.FieldByName('COLUMN_NAME').AsString));
          Query.Next;
        end;

        // Add to cache
        FColumnCache.Add(CacheKey, ColumnList);
        Result := ColumnList;
      except
        on E: Exception do
        begin
          ColumnList.Free;
          Exit(nil);
        end;
      end;

    finally
      Query.Free;
    end;
  except
    ColumnList.Free;
    raise;
  end;
end;

procedure TSQLParser.ClearColumnCache;
var
  List: TList<string>;
begin
  for List in FColumnCache.Values do
    List.Free;
  FColumnCache.Clear;
end;

function TSQLParser.ColumnExistsInTable(const SchemaName, TableName, ColumnName: string): Boolean;
var
  Query: TUniQuery;
  FullTableName: string;
begin
  Result := False;

  if (SchemaName = '') or (TableName = '') or (ColumnName = '') then
    Exit;

  // Check cache first
  FullTableName := LowerCase(SchemaName + '.' + TableName);
  if FColumnCache.ContainsKey(FullTableName) then
    Exit(FColumnCache[FullTableName].Contains(LowerCase(ColumnName)));

  // If not in cache, query the database
  Query := TUniQuery.Create(nil);
  try
    Query.Connection := FConnection;
    Query.SQL.Text :=
      'SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS ' +
      'WHERE TABLE_SCHEMA = :schema ' +
      'AND TABLE_NAME = :table ' +
      'AND COLUMN_NAME = :column';

    Query.ParamByName('schema').AsString := SchemaName;
    Query.ParamByName('table').AsString := TableName;
    Query.ParamByName('column').AsString := ColumnName;

    try
      Query.Open;
      Result := not Query.IsEmpty;

      // Cache the result
      if Result and not FColumnCache.ContainsKey(FullTableName) then
      begin
        var ColumnList := TList<string>.Create;
        var ColsQuery := TUniQuery.Create(nil);
        try
          ColsQuery.Connection := FConnection;
          ColsQuery.SQL.Text :=
            'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS ' +
            'WHERE TABLE_SCHEMA = :schema AND TABLE_NAME = :table';
          ColsQuery.ParamByName('schema').AsString := SchemaName;
          ColsQuery.ParamByName('table').AsString := TableName;
          ColsQuery.Open;

          while not ColsQuery.Eof do
          begin
            ColumnList.Add(LowerCase(ColsQuery.FieldByName('COLUMN_NAME').AsString));
            ColsQuery.Next;
          end;

          FColumnCache.Add(FullTableName, ColumnList);
        finally
          ColsQuery.Free;
        end;
      end;

    except
      Result := False;
    end;
  finally
    Query.Free;
  end;
end;

procedure TSQLParser.ResolveExpressionTypes;
begin
  try
    var TestQuery := TUniQuery.Create(nil);
    try
      TestQuery.Connection := FConnection;

      // Build test query
      var QuerySQL := FSQLText;
      if Pos(' LIMIT ', UpperCase(QuerySQL)) = 0 then
        QuerySQL := QuerySQL + ' LIMIT 1';

      TSQLDebugLogger.Log('Expression test query: ' + QuerySQL);
      TestQuery.SQL.Text := QuerySQL;
      TestQuery.Open;

      // Process field types
      for var Column in FColumns do
      begin
        var FieldName := Column.QueryColumnAlias;
        if FieldName = '' then
          FieldName := Column.ReferencedTableColumn;

        var Field := TestQuery.FindField(FieldName);
        if Assigned(Field) then
        begin
          TSQLDebugLogger.Log(Format('Found field %s type: %s',
            [FieldName, GetEnumName(TypeInfo(TFieldType), Ord(Field.DataType))]));

          Column.DataType := Field.DataType;
          var TypeDetail := Column.TypeDetail;
          TypeDetail.BaseType := Field.DataType;

          case Field.DataType of
            ftString, ftWideString, ftFixedChar, ftFixedWideChar:
              TypeDetail.Length := Field.Size;

            ftBCD, ftFMTBcd:
              if Field is TBCDField then
              begin
                TypeDetail.Precision := TBCDField(Field).Precision;
                TypeDetail.Scale := TBCDField(Field).Size;
              end;

            ftFloat, ftCurrency:
              if Field is TFloatField then
              begin
                TypeDetail.Precision := TFloatField(Field).Precision;
                if TypeDetail.Precision = 0 then
                  TypeDetail.Precision := 15;
                TypeDetail.Scale := 4;
              end;
          end;

          Column.TypeDetail := TypeDetail;
        end
        else
          TSQLDebugLogger.Log(Format('WARNING: Field not found: %s', [FieldName]));
      end;

    finally
      TestQuery.Free;
    end;
  except
    on E: Exception do
      TSQLDebugLogger.Log('Error in expression type resolution: ' + E.Message);
  end;
end;

procedure TSQLParser.LogColumnTypes;
var
  i: Integer;
begin
  for i := 0 to FColumns.Count - 1 do
  begin
    TSQLDebugLogger.Log(PChar(Format(
      'Column: %s, Alias: %s, Type: %d, Length: %d, Precision: %d, Scale: %d',
      [FColumns[i].ReferencedTableColumn,
       FColumns[i].QueryColumnAlias,
       Ord(FColumns[i].DataType),
       FColumns[i].TypeDetail.Length,
       FColumns[i].TypeDetail.Precision,
       FColumns[i].TypeDetail.Scale])));
  end;
end;

function TSQLParser.GetExpressionType(const Expr: string): TFieldType;
  // Handle aggregate functions
  const AggregateFunctions: array[0..4] of string = ('SUM(', 'AVG(', 'MIN(', 'MAX(', 'COUNT(');
begin
  // Handle subqueries
  if ContainsSubquery(Expr) then
  begin
    // For subqueries, determine type based on aggregate functions or COUNT
    if ContainsText(Expr, 'COUNT(') then
    begin
      TSQLDebugLogger.Log('Subquery type determined as integer (COUNT)');
      Result := ftInteger;
      Exit;
    end;

    // Default to float for other subqueries
    TSQLDebugLogger.Log('Subquery type defaulting to float');
    Result := ftFloat;
    Exit;
  end;

  for var Func in AggregateFunctions do
  begin
    if ContainsText(Expr, Func) then
    begin
      if Func = 'COUNT(' then
      begin
        Result := ftInteger;
        TSQLDebugLogger.Log('Aggregate function type determined as integer (COUNT)');
      end
      else
      begin
        Result := ftFloat;
        TSQLDebugLogger.Log('Aggregate function type determined as float');
      end;
      Exit;
    end;
  end;

  // Handle CAST function
  if StartsText('CAST(', Expr) then
  begin
    var TypeMatch := TRegEx.Match(Expr, 'AS\s+(\w+)', [roIgnoreCase]);
    if TypeMatch.Success then
    begin
      var TargetType := UpperCase(TypeMatch.Groups[1].Value);
      if ContainsText(TargetType, 'INT') or
         ContainsText(TargetType, 'UNSIGNED') then
        Result := ftInteger
      else if ContainsText(TargetType, 'CHAR') or
              ContainsText(TargetType, 'TEXT') then
        Result := ftString
      else if ContainsText(TargetType, 'DECIMAL') or
              ContainsText(TargetType, 'NUMERIC') or
              ContainsText(TargetType, 'FLOAT') or
              ContainsText(TargetType, 'DOUBLE') then
        Result := ftFloat
      else
        Result := ftUnknown;
      Exit;
    end;
  end;

  // Handle string literals
  if TRegEx.IsMatch(Expr, '^\s*"[^"]*"\s*$') or
     TRegEx.IsMatch(Expr, '^\s*''[^'']*''\s*$') then
  begin
    Result := ftString;
    Exit;
  end;

  // Handle numeric expressions and literals
  if TRegEx.IsMatch(Expr, '^\s*\d+(\.\d*)?([eE][+-]?\d+)?\s*$') or
     TRegEx.IsMatch(Expr, '.*[\+\-\*\/].*') then
  begin
    if Pos('.', Expr) > 0 then
      Result := ftFloat
    else
      Result := ftInteger;
    Exit;
  end;

  // Default to unknown type
  Result := ftUnknown;
end;

function TSQLParser.SplitExpressionAndAlias(const Input: string): TExpressionParts;
var
  InParentheses: Integer;
  InQuote: Boolean;
  QuoteChar: Char;
  CurrentChar: Char;
  LastWordStart: Integer;
  I: Integer;
begin
  Result.Expression := Input;
  Result.Alias := '';
  Result.HasAlias := False;

  InParentheses := 0;
  InQuote := False;
  QuoteChar := #0;
  LastWordStart := -1;

  // Scan from right to left to find the last word
  for I := Length(Input) downto 1 do
  begin
    CurrentChar := Input[I];

    // Handle quotes
    if CurrentChar in ['''', '"'] then
    begin
      if InQuote then
      begin
        if CurrentChar = QuoteChar then
          InQuote := False;
      end
      else
      begin
        InQuote := True;
        QuoteChar := CurrentChar;
      end;
      Continue;
    end;

    // Skip if we're inside quotes
    if InQuote then
      Continue;

    // Handle parentheses
    case CurrentChar of
      ')': Inc(InParentheses);
      '(': Dec(InParentheses);
    end;

    // Skip if we're inside parentheses
    if InParentheses > 0 then
      Continue;

    // Found a space outside of parentheses and quotes
    if CurrentChar = ' ' then
    begin
      if LastWordStart > 0 then
      begin
        var LastWord := Copy(Input, I + 1, LastWordStart - I);
        // Verify the last word is a valid identifier and not a keyword
        if TRegEx.IsMatch(LastWord, '^\w+$') and
           not IsSQLKeywordOrFunction(LastWord) then
        begin
          Result.Expression := Trim(Copy(Input, 1, I));
          Result.Alias := LastWord;
          Result.HasAlias := True;
          Exit;
        end;
        Break;
      end;
    end
    else if LastWordStart = -1 then
      LastWordStart := I + 1;
  end;
end;

procedure TSQLParser.ParseSubquerySchema(var Subquery: string);
begin
  // Find all table references in the subquery and add schema if missing
  var TablePattern := '\bFROM\s+(\w+)\b|\bJOIN\s+(\w+)\b';
  var Matches := TRegEx.Matches(Subquery, TablePattern, [roIgnoreCase]);

  var ReplacedTables := TDictionary<string, Boolean>.Create;
  try
    for var Match in Matches do
    begin
      var TableName := '';

      // Check which group captured the table name
      if Match.Groups[1].Success then
        TableName := Match.Groups[1].Value
      else if Match.Groups[2].Success then
        TableName := Match.Groups[2].Value;

      if (TableName <> '') and not ReplacedTables.ContainsKey(TableName) then
      begin
        // Check if table exists in our main query's tables
        var FullTableName := '';
        for var Table in FTables do
        begin
          if SameText(Table.TableName, TableName) then
          begin
            FullTableName := Format('%s.%s', [Table.SchemaName, Table.TableName]);
            Break;
          end;
        end;

        if FullTableName <> '' then
        begin
          // Replace the table name with the fully qualified name
          var Pattern := Format('\b%s\b', [TableName]);
          Subquery := TRegEx.Replace(Subquery, Pattern, FullTableName);
          ReplacedTables.Add(TableName, True);

          TSQLDebugLogger.Log(Format('Qualified table in subquery: %s -> %s',
            [TableName, FullTableName]));
        end;
      end;
    end;
  finally
    ReplacedTables.Free;
  end;
end;

procedure TSubqueryInfo.Initialize;
begin
  FullExpression := '';
  AggregateFunction := '';
  Column := '';
  Alias := '';
  TableName := '';
  DataType := ftUnknown;
end;

function TSQLParser.ParseSubquery(const SubqueryExpr: string): TSubqueryInfo;
var
  SelectMatch: TMatch;
  FunctionMatch: TMatch;
  TableMatch: TMatch;
begin
  Result.Initialize;
  Result.FullExpression := SubqueryExpr;

  // Extract the aggregate function and column
  SelectMatch := TRegEx.Match(SubqueryExpr,
    '\(\s*SELECT\s+((?:COUNT|MAX|MIN|AVG|SUM)\s*\(\s*(?:\*|\w+)\s*\))\s+FROM\s+(\w+)(?:\s+(?:AS\s+)?(\w+))?\s*\)',
    [roIgnoreCase]);

  if SelectMatch.Success then
  begin
    // Get the function expression (e.g., "COUNT(*)" or "MAX(cliente_id)")
    var FuncExpr := SelectMatch.Groups[1].Value;

    // Parse the function and column
    FunctionMatch := TRegEx.Match(FuncExpr,
      '(COUNT|MAX|MIN|AVG|SUM)\s*\(\s*(\*|\w+)\s*\)',
      [roIgnoreCase]);

    if FunctionMatch.Success then
    begin
      Result.AggregateFunction := UpperCase(FunctionMatch.Groups[1].Value);
      Result.Column := FunctionMatch.Groups[2].Value;
    end;

    // Get table name and optional alias
    Result.TableName := SelectMatch.Groups[2].Value;
    if SelectMatch.Groups[3].Success then
      Result.TableName := SelectMatch.Groups[3].Value;

    // Determine the return type based on the aggregate function
    Result.DataType := GetAggregateReturnType(Result.AggregateFunction, Result.Column);
  end;

  TSQLDebugLogger.Log(Format('Parsed subquery - Function: %s, Column: %s, Table: %s, Type: %s',
    [Result.AggregateFunction, Result.Column, Result.TableName,
     GetEnumName(TypeInfo(TFieldType), Ord(Result.DataType))]));
end;

function TSQLParser.GetAggregateReturnType(const AggFunc, Column: string): TFieldType;
begin
  // Set default
  Result := ftUnknown;

  if UpperCase(AggFunc) = 'COUNT' then
    Result := ftLargeint

  else if (UpperCase(AggFunc) = 'MAX') or (UpperCase(AggFunc) = 'MIN') then
  begin
    if Column = '*' then
      Result := ftInteger
    else if EndsText('_id', Column) then
      Result := ftInteger
    else
      Result := ftFloat;  // Default to float for unknown columns in MAX/MIN
  end

  else if UpperCase(AggFunc) = 'SUM' then
  begin
    if Column = '*' then
      Result := ftInteger
    else if EndsText('_id', Column) then
      Result := ftLargeint
    else
      Result := ftFloat;
  end

  else if UpperCase(AggFunc) = 'AVG' then
    Result := ftFloat;

  // Log the result using if/else instead of case
  var TypeStr: string;
  if Result = ftUnknown then
    TypeStr := 'ftUnknown'
  else if Result = ftString then
    TypeStr := 'ftString'
  else if Result = ftSmallint then
    TypeStr := 'ftSmallint'
  else if Result = ftInteger then
    TypeStr := 'ftInteger'
  else if Result = ftLargeint then
    TypeStr := 'ftLargeint'
  else if Result = ftFloat then
    TypeStr := 'ftFloat'
  else if Result = ftBCD then
    TypeStr := 'ftBCD'
  else
    TypeStr := 'Other field type';

  TSQLDebugLogger.Log(Format('Aggregate return type for %s(%s): %s',
    [AggFunc, Column, TypeStr]));
end;

function TSQLParser.ExtractSubqueryAlias(const Expr: string; out Subquery, Alias: string): Boolean;
var
  Match: TMatch;
begin
  // Match pattern: (SELECT ...) alias or (SELECT ...) AS alias
  Match := TRegEx.Match(Expr,
    '(\(SELECT[^()]*\))\s+(?:AS\s+)?(\w+)$',
    [roIgnoreCase]);

  if Match.Success then
  begin
    Subquery := Match.Groups[1].Value;
    Alias := Match.Groups[2].Value;
    Result := True;

    TSQLDebugLogger.Log(Format('Extracted subquery alias - Subquery: %s, Alias: %s',
      [Subquery, Alias]));
  end
  else
  begin
    Subquery := Expr;
    Alias := '';
    Result := False;

    TSQLDebugLogger.Log('No alias found for subquery');
  end;
end;

procedure TSQLParser.ProcessSubqueryExpression(ColumnInfo: TSQLColumnInfo);
var
  Subquery, Alias: string;
  SubqueryInfo: TSubqueryInfo;
begin
  TSQLDebugLogger.Log('Processing subquery expression: ' + ColumnInfo.ReferencedTableColumn);

  // Extract alias if present
  if ExtractSubqueryAlias(ColumnInfo.ReferencedTableColumn, Subquery, Alias) then
  begin
    ColumnInfo.QueryColumnAlias := Alias;
    TSQLDebugLogger.Log('Using explicit alias: ' + Alias);
  end
  else
  begin
    // Generate default alias if none provided
    ColumnInfo.QueryColumnAlias := 'subq_' + IntToStr(FColumns.Count + 1);
    TSQLDebugLogger.Log('Generated default alias: ' + ColumnInfo.QueryColumnAlias);
  end;

  // Parse the subquery if not already cached
  if not FSubqueryCache.TryGetValue(Subquery, SubqueryInfo) then
  begin
    SubqueryInfo := ParseSubquery(Subquery);
    FSubqueryCache.Add(Subquery, SubqueryInfo);
  end;

  // Set column type information
  ColumnInfo.DataType := SubqueryInfo.DataType;

  var TypeDetail: TColumnTypeDetail;
  TypeDetail.Initialize;
  TypeDetail.BaseType := SubqueryInfo.DataType;

  // Set appropriate type details based on the aggregate function using if/else
  var AggFunc := UpperCase(SubqueryInfo.AggregateFunction);

  if AggFunc = 'COUNT' then
  begin
    TypeDetail.Precision := 0;
    TypeDetail.Scale := 0;
  end
  else if AggFunc = 'AVG' then
  begin
    TypeDetail.Precision := 15;
    TypeDetail.Scale := 4;
  end
  else if AggFunc = 'SUM' then
  begin
    TypeDetail.Precision := 18;
    TypeDetail.Scale := 2;
  end
  else if (AggFunc = 'MAX') or (AggFunc = 'MIN') then
  begin
    if (SubqueryInfo.DataType = ftInteger) or
       (SubqueryInfo.DataType = ftSmallint) or
       (SubqueryInfo.DataType = ftLargeint) then
    begin
      TypeDetail.Precision := 0;
      TypeDetail.Scale := 0;
    end
    else
    begin
      TypeDetail.Precision := 15;
      TypeDetail.Scale := 4;
    end;
  end;

  ColumnInfo.TypeDetail := TypeDetail;

  // Get string representation of the field type for logging
  var TypeStr: string;
  if ColumnInfo.DataType = ftUnknown then
    TypeStr := 'ftUnknown'
  else if ColumnInfo.DataType = ftString then
    TypeStr := 'ftString'
  else if ColumnInfo.DataType = ftSmallint then
    TypeStr := 'ftSmallint'
  else if ColumnInfo.DataType = ftInteger then
    TypeStr := 'ftInteger'
  else if ColumnInfo.DataType = ftLargeint then
    TypeStr := 'ftLargeint'
  else if ColumnInfo.DataType = ftFloat then
    TypeStr := 'ftFloat'
  else if ColumnInfo.DataType = ftBCD then
    TypeStr := 'ftBCD'
  else
    TypeStr := 'Other field type';

  TSQLDebugLogger.Log(Format('Subquery processed - Alias: %s, Type: %s, Precision: %d, Scale: %d',
    [ColumnInfo.QueryColumnAlias,
     TypeStr,
     TypeDetail.Precision,
     TypeDetail.Scale]));
end;

function TSQLParser.IsAggregateFunction(const Func: string): Boolean;
const
  AggregateFunctions: array[0..4] of string = ('COUNT', 'MAX', 'MIN', 'AVG', 'SUM');
begin
  Result := False;
  var UpperFunc := UpperCase(Func);
  for var AggFunc in AggregateFunctions do
    if UpperFunc = AggFunc then
      Exit(True);
end;

end.

-------------

There are 2 problems in this Delphi unit:
The column alias is not correctly populated when it is a subquery
The Data Type is not correctly populated when it is a subquery. It should be get from the Test query, like it is with an expression
Review and then fix it

SQL example:
SELECT lcc.*,contrato_numero,tipo_contrato,nome,devolucao_prevista,"NewYork" defcity, count(*) tot1, CASE WHEN cv.num_versao > 0 THEN 'Positive' ELSE 'Negative' END sign_status, (SELECT max(cliente_id) FROM centauro020.cliente) as maxcli FROM locavel_contrato_consolidado lcc INNER JOIN contrato c ON lcc.contrato_id = c.contrato_id INNER JOIN cliente ON c.cliente_id = cliente.cliente_id
   LEFT JOIN contrato_versao cv ON (c.contrato_id=cv.contrato_id AND cv.num_versao=(SELECT MAX(b.num_versao) FROM contrato_versao b WHERE cv.contrato_id=b.contrato_id)) WHERE locavel_id = 10
   ORDER BY tipo_contrato,devolucao_prevista,tipo_contrato,contrato_numero

------------