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

  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>;

    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);

  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

{ 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;
end;

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

procedure TSQLParser.Parse;
begin
  TSQLDebugLogger.Log('Starting SQL parsing');
  try
    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;
    TSQLDebugLogger.Log('Columns validated');
    ResolveExpressionTypes;
    TSQLDebugLogger.Log('All column types resolved');
    LogColumnTypes;
  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 the main FROM by balancing parentheses
    var InQuote := False;
    var QuoteChar := #0;
    var ParenCount := 0;
    var MainFromPos := -1;

    SelectMatch := TRegEx.Match(FSQLText, '\bSELECT\b', [roIgnoreCase]);
    if not SelectMatch.Success then
      raise Exception.Create('Cannot find SELECT keyword');

    SelectPos := SelectMatch.Index + SelectMatch.Length;

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

    if MainFromPos = -1 then
      raise Exception.Create('Cannot find main FROM clause');

    SelectClause := Copy(FSQLText, SelectPos + 1, MainFromPos - SelectPos - 1).Trim;
    TSQLDebugLogger.Log('Extracted Select Clause: ' + SelectClause);

    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);

  // Local helper function to collect alias text
  function CollectAliasText(var Position: Integer; const MaxPosition: Integer): string;
  var
    NextChar: Char;
    HasAS: Boolean;
    StartPos: Integer;
  begin
    Result := '';
    HasAS := False;

    // Skip initial spaces
    while (Position <= MaxPosition) and (SelectClause[Position] = ' ') do
      Inc(Position);

    StartPos := Position;

    // Check for AS keyword
    if Position + 1 <= MaxPosition then
    begin
      if UpperCase(Copy(SelectClause, Position, 2)) = 'AS' then
      begin
        Inc(Position, 2);
        HasAS := True;

        // Skip spaces after AS
        while (Position <= MaxPosition) and (SelectClause[Position] = ' ') do
          Inc(Position);

        // Collect the alias text
        StartPos := Position;
      end;
    end;

    // Collect until comma or end
    while Position <= MaxPosition do
    begin
      NextChar := SelectClause[Position];
      if NextChar = ',' then
        Break;
      Inc(Position);
    end;

    var AliasText := Trim(Copy(SelectClause, StartPos, Position - StartPos));
    if HasAS then
      Result := ' as ' + AliasText
    else if AliasText <> '' then
      Result := ' ' + AliasText;

    // Skip past comma if we found one
    if (Position <= MaxPosition) and (SelectClause[Position] = ',') then
      Inc(Position);

    TSQLDebugLogger.Log(Format('Collected alias text: "%s", New position: %d', [Result, Position]));
  end;

begin
  TSQLDebugLogger.Log('Processing regular columns');
  TSQLDebugLogger.Log('Input SelectClause: ' + SelectClause);

  var Columns := TStringList.Create;
  try
    var ParenCount := 0;
    var CurrentColumn := '';
    var i := 1;
    var InQuote := False;
    var QuoteChar := #0;
    var LastChar := #0;
    var InSubquery := False;
    var SubqueryStartPos := 0;
    var SkipToPos := 0;

    while i <= Length(SelectClause) do
    begin
      // Skip processing if we're in a skip range
      if (SkipToPos > 0) and (i < SkipToPos) then
      begin
        Inc(i);
        Continue;
      end
      else if SkipToPos > 0 then
        SkipToPos := 0;

      var CurrentChar := SelectClause[i];
      TSQLDebugLogger.Log(Format('Processing char: %s at pos %d, ParenCount: %d, InQuote: %s, InSubquery: %s, Current: %s',
        [CurrentChar, i, ParenCount, BoolToStr(InQuote, True), BoolToStr(InSubquery, True), CurrentColumn]));

      // Handle quotes
      if (CurrentChar in ['''', '"']) and (LastChar <> '\') then
      begin
        if InQuote and (CurrentChar = QuoteChar) then
        begin
          InQuote := False;
          TSQLDebugLogger.Log('Exiting quote');
        end
        else if not InQuote then
        begin
          InQuote := True;
          QuoteChar := CurrentChar;
          TSQLDebugLogger.Log('Entering quote with char: ' + QuoteChar);
        end;
        CurrentColumn := CurrentColumn + CurrentChar;
      end
      else if not InQuote then
      begin
        case CurrentChar of
          '(':
            begin
              Inc(ParenCount);
              CurrentColumn := CurrentColumn + CurrentChar;

              if not InSubquery then
              begin
                // Look ahead for SELECT keyword
                var NextWordStart := i + 1;
                while (NextWordStart <= Length(SelectClause)) and
                      (SelectClause[NextWordStart] = ' ') do
                  Inc(NextWordStart);

                if NextWordStart + 5 <= Length(SelectClause) then
                begin
                  var NextWord := UpperCase(Copy(SelectClause, NextWordStart, 6));
                  if NextWord = 'SELECT' then
                  begin
                    InSubquery := True;
                    SubqueryStartPos := i;
                    TSQLDebugLogger.Log(Format('Starting subquery at position: %d', [i]));
                  end;
                end;
              end;
            end;
          ')':
            begin
              Dec(ParenCount);
              CurrentColumn := CurrentColumn + CurrentChar;

              TSQLDebugLogger.Log(Format('Found closing parenthesis. ParenCount=%d, InSubquery=%s',
                [ParenCount, BoolToStr(InSubquery, True)]));

              if InSubquery and (ParenCount = 0) then
              begin
                var NextPos := i + 1;
                var AliasText := CollectAliasText(NextPos, Length(SelectClause));

                if AliasText <> '' then
                  CurrentColumn := CurrentColumn + AliasText;

                TSQLDebugLogger.Log('Found direct alias. Complete subquery: ' + CurrentColumn);

                if CurrentColumn.Trim <> '' then
                begin
                  Columns.Add(CurrentColumn.Trim);
                  CurrentColumn := '';
                end;

                InSubquery := False;
                SkipToPos := NextPos;
                i := NextPos - 1;
                Continue;
              end;
            end;
          ',':
            begin
              if (ParenCount = 0) and not InSubquery then
              begin
                if CurrentColumn.Trim <> '' then
                begin
                  TSQLDebugLogger.Log('Adding column to list: ' + CurrentColumn.Trim);
                  Columns.Add(CurrentColumn.Trim);
                end;
                CurrentColumn := '';
              end
              else
                CurrentColumn := CurrentColumn + CurrentChar;
            end;
          else
            CurrentColumn := CurrentColumn + CurrentChar;
        end;
      end
      else
        CurrentColumn := CurrentColumn + CurrentChar;

      LastChar := CurrentChar;
      Inc(i);
    end;

    // Only add the final column if it's non-empty and we're not in a skip state
    if (CurrentColumn.Trim <> '') and (SkipToPos = 0) then
    begin
      TSQLDebugLogger.Log('Adding final column: ' + CurrentColumn.Trim);
      Columns.Add(CurrentColumn.Trim);
    end;

    TSQLDebugLogger.Log('=== Final Collected Columns ===');
    for var idx := 0 to Columns.Count - 1 do
      TSQLDebugLogger.Log(Format('Column[%d]: %s', [idx, Columns[idx]]));
    TSQLDebugLogger.Log('==============================');

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

      if IsWildcard(ColExpr) then
      begin
        TSQLDebugLogger.Log('Skipping wildcard: ' + ColExpr);
        Continue;
      end;

      var ColumnInfo := ParseColumnExpression(ColExpr);
      try
        ProcessColumnInfo(ColumnInfo);
        ColumnInfo := nil;
      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]));

  // First get column names
  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 := TableInfo.SchemaName;
    Query.ParamByName('table').AsString := TableInfo.TableName;
    TSQLDebugLogger.Log('Executing column metadata query');
    Query.Open;

    // Create a test query for type resolution
    var TypeQuery := TUniQuery.Create(nil);
    try
      TypeQuery.Connection := FConnection;
      // Build query based on full table name
      var TestSQL := Format('SELECT * FROM %s.%s LIMIT 1',
        [TableInfo.SchemaName, TableInfo.TableName]);
      TSQLDebugLogger.Log('Type resolution query: ' + TestSQL);
      TypeQuery.SQL.Text := TestSQL;
      TypeQuery.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;

          NewColumn.ResolvedTableInfo := TableInfo;

          // Get field type from test query
          var Field := TypeQuery.FindField(ColumnName);
          if Assigned(Field) then
          begin
            TSQLDebugLogger.Log(Format('Found field type for %s: %s',
              [ColumnName, GetEnumName(TypeInfo(TFieldType), Ord(Field.DataType))]));

            NewColumn.DataType := Field.DataType;
            var TypeDetail: TColumnTypeDetail;
            TypeDetail.Initialize;
            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;

            NewColumn.TypeDetail := TypeDetail;

            TSQLDebugLogger.Log(Format('Column %s type details - Type: %s, Length: %d, Precision: %d, Scale: %d',
              [ColumnName, GetEnumName(TypeInfo(TFieldType), Ord(Field.DataType)),
               TypeDetail.Length, TypeDetail.Precision, TypeDetail.Scale]));
          end
          else
          begin
            TSQLDebugLogger.Log(Format('WARNING: Field %s not found in type query', [ColumnName]));
            NewColumn.DataType := ftUnknown;
          end;

          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
      TypeQuery.Free;
    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 a subquery
  if ColumnInfo.RealSchemaName = '[subquery]' then
  begin
    TSQLDebugLogger.Log('Processing subquery');
    ColumnInfo.DataType := GetExpressionType(ColumnInfo.ReferencedTableColumn);

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

    FColumns.Add(ColumnInfo);
    Exit;
  end;

  if ColumnInfo.RealSchemaName = '[expression]' then
  begin
    TSQLDebugLogger.Log('Skipping type verification for expression');
    ColumnInfo.DataType := GetExpressionType(ColumnInfo.ReferencedTableColumn);

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

    // Set appropriate details for string literals
    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;

  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 ExpressionPart := Expression;
    var AliasName := '';

    // First check for AS alias
    var AsPos := Pos(' as ', LowerCase(Expression));
    if AsPos > 0 then
    begin
      ExpressionPart := Trim(Copy(Expression, 1, AsPos - 1));
      AliasName := Trim(Copy(Expression, AsPos + 4));
    end
    else
    begin
      // Look for direct alias (last word)
      var LastSpace := LastDelimiter(' ', Expression);
      if LastSpace > 0 then
      begin
        var PotentialAlias := Trim(Copy(Expression, LastSpace + 1));
        if TRegEx.IsMatch(PotentialAlias, '^\w+$') and
           not IsSQLKeywordOrFunction(PotentialAlias) then
        begin
          ExpressionPart := Trim(Copy(Expression, 1, LastSpace - 1));
          AliasName := PotentialAlias;
        end;
      end;
    end;

    if ContainsSubquery(ExpressionPart) then
    begin
      TSQLDebugLogger.Log('Subquery detected: ' + ExpressionPart);

      Result.RealSchemaName := '[subquery]';
      Result.RealTableName := '[subquery]';
      Result.ReferencedTableColumn := ExpressionPart;
      Result.FullName := ExpressionPart;
      Result.QueryColumnAlias := AliasName;

      TSQLDebugLogger.Log(Format('Processed subquery - Base: %s, Alias: %s',
        [ExpressionPart, AliasName]));
      Exit;
    end;

    if IsExpression(ExpressionPart) then
    begin
      TSQLDebugLogger.Log('Expression detected: ' + ExpressionPart);
      Result.RealSchemaName := '[expression]';
      Result.RealTableName := '[expression]';
      Result.ReferencedTableColumn := ExpressionPart;
      Result.FullName := ExpressionPart;
      Result.QueryColumnAlias := AliasName;
      if Result.QueryColumnAlias = '' then
        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;
      Result.QueryColumnAlias := AliasName;
      if Result.QueryColumnAlias = '' then
        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;

  // First strip any trailing AS clause from column name
  var ColumnName := ColumnInfo.ReferencedTableColumn;
  var AsPos := Pos(' as ', LowerCase(ColumnName));
  if AsPos > 0 then
    ColumnName := Trim(Copy(ColumnName, 1, AsPos - 1));

  // 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, ColumnName) then
    begin
      TSQLDebugLogger.Log(Format('ERROR: Column %s not found in table %s.%s',
        [ColumnName, TableInfo.SchemaName, TableInfo.TableName]));
      raise Exception.CreateFmt('Column "%s" not found in table "%s.%s"',
        [ColumnName, 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, ColumnName) then
      begin
        if not FoundInTable then
        begin
          TableInfo := Table;
          FoundInTable := True;
          TSQLDebugLogger.Log(Format('Found unqualified column %s in table %s.%s',
            [ColumnName, Table.SchemaName, Table.TableName]));
        end
        else
        begin
          // Column exists in multiple tables
          TSQLDebugLogger.Log(Format('Column %s is ambiguous - exists in multiple tables',
            [ColumnName]));
          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',
        [ColumnName]));
      raise Exception.CreateFmt('Column "%s" not found in any referenced table',
        [ColumnName]);
    end;
  end;

  // Store the original column name without AS clause in ReferencedTableColumn
  ColumnInfo.ReferencedTableColumn := ColumnName;
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;
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;

  // 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;
      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;
      for var Column in FColumns do
      begin
        // Skip wildcard columns as they are already resolved
        if Column.IsWildcard then
          Continue;

        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;
          TSQLDebugLogger.Log(Format('Column %s type details - Type: %s, Length: %d, Precision: %d, Scale: %d',
            [FieldName, GetEnumName(TypeInfo(TFieldType), Ord(Field.DataType)),
             TypeDetail.Length, TypeDetail.Precision, TypeDetail.Scale]));
        end
        else
          TSQLDebugLogger.Log(Format('WARNING: Field not found: %s', [FieldName]));
      end;
    finally
      TestQuery.Free;
    end;
  except
    on E: Exception do
    begin
      TSQLDebugLogger.Log('Error in expression type resolution: ' + E.Message);
      raise;
    end;
  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;
begin
  // Handle subqueries
  if ContainsSubquery(Expr) then
  begin
    // For subqueries, we need to determine the type based on what's being selected
    var SelectMatch := TRegEx.Match(Expr, '\(\s*SELECT\s+(.+?)\s+FROM', [roIgnoreCase]);
    if SelectMatch.Success then
    begin
      var SelectExpr := SelectMatch.Groups[1].Value.Trim;

      // Check for aggregate functions
      if ContainsText(SelectExpr, 'MAX(') or
         ContainsText(SelectExpr, 'MIN(') or
         ContainsText(SelectExpr, 'SUM(') or
         ContainsText(SelectExpr, 'COUNT(') then
      begin
        if ContainsText(SelectExpr, 'COUNT(') then
        begin
          TSQLDebugLogger.Log('Subquery type determined as integer (COUNT)');
          Result := ftInteger;
        end
        else
        begin
          // Try to determine type based on the column being aggregated
          if ContainsText(SelectExpr, '_id)') or
             ContainsText(SelectExpr, 'INT)') then
          begin
            TSQLDebugLogger.Log('Subquery type determined as integer (aggregated ID/INT)');
            Result := ftInteger;
          end
          else
          begin
            TSQLDebugLogger.Log('Subquery type determined as float (aggregated value)');
            Result := ftFloat;
          end;
        end;
      end
      else
      begin
        // Default to float for other numeric operations
        TSQLDebugLogger.Log('Subquery type defaulting to float');
        Result := ftFloat;
      end;
      Exit;
    end;
  end;

  // Handle CAST function
  if StartsText('CAST(', Expr) then
  begin
    var TypeMatch := TRegEx.Match(Expr, 'AS\s+(\w+(?:\s+\w+)*)', [roIgnoreCase]);
    if TypeMatch.Success then
    begin
      var TargetType := UpperCase(TypeMatch.Groups[1].Value.Trim);
      if ContainsText(TargetType, 'INT') or
         ContainsText(TargetType, 'UNSIGNED') then
      begin
        TSQLDebugLogger.Log('CAST expression type determined as integer');
        Result := ftInteger;
      end
      else if ContainsText(TargetType, 'CHAR') or
              ContainsText(TargetType, 'TEXT') then
      begin
        TSQLDebugLogger.Log('CAST expression type determined as string');
        Result := ftString;
      end
      else if ContainsText(TargetType, 'DECIMAL') or
              ContainsText(TargetType, 'NUMERIC') or
              ContainsText(TargetType, 'FLOAT') or
              ContainsText(TargetType, 'DOUBLE') then
      begin
        TSQLDebugLogger.Log('CAST expression type determined as float');
        Result := ftFloat;
      end
      else
      begin
        TSQLDebugLogger.Log('Unknown CAST target type: ' + TargetType);
        Result := ftUnknown;
      end;
      Exit;
    end;
  end;

  // Rest of the existing type detection logic...
  // Check for string literals
  if TRegEx.IsMatch(Expr, '^\s*"[^"]*"\s*$') or
     TRegEx.IsMatch(Expr, '^\s*''[^'']*''\s*$') then
  begin
    TSQLDebugLogger.Log('Expression type determined as string literal');
    Result := ftString;
    Exit;
  end;

  // Check for numeric literals or expressions
  if TRegEx.IsMatch(Expr, '^\s*\d+(\.\d*)?([eE][+-]?\d+)?\s*$') or
     TRegEx.IsMatch(Expr, '.*[\+\-\*\/].*') then
  begin
    if Pos('.', Expr) > 0 then
    begin
      TSQLDebugLogger.Log('Expression type determined as float');
      Result := ftFloat;
    end
    else
    begin
      TSQLDebugLogger.Log('Expression type determined as integer');
      Result := ftInteger;
    end;
    Exit;
  end;

  // Default to unknown type
  Result := ftUnknown;
  TSQLDebugLogger.Log('Expression type could not be determined');
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;

end.

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

In the SQLParserEnhanced unit, if I try to parse this query:

SELECT
  sci.subloc_item_id,
  sci.subloc_contrato_id,
  sci.equipamento_id,
  eq.tipo_equipamento,
  eq.descricao_equipamento,
  l.identificador,
  sci.patrimonio_origem,
  sci.descricao_origem,
  sci.subloc_item_status,
  sci.subloc_item_custo_total,
CASE
    subloc_item_status 
    WHEN 0 THEN
    "Cancelado" 
    WHEN 1 THEN
    "Vigente" 
    WHEN 2 THEN
    "Encerrado" 
  END AS status_desc,
  se.nome_empresa,
  sc.numero_contrato_subloc,
  CAST(l.qtde_patio AS INTEGER) AS subloc_qtde_patio
FROM
  subloc_contrato_item sci
  INNER JOIN subloc_contrato sc ON sci.subloc_contrato_id = sc.subloc_contrato_id
  INNER JOIN subloc_empresa se ON sc.subloc_empresa_id = se.subloc_empresa_id
  INNER JOIN equipamento eq ON sci.equipamento_id = eq.equipamento_id
  LEFT JOIN locavel l ON l.subloc_item_id = sci.subloc_item_id


Then the expression
"CAST(l.qtde_patio AS INTEGER) AS subloc_qtde_patio"
is populated as "CAST(l.qtde_patio"
and its alias is populated as "INTEGER) AS subloc_qtde_patio"

Carefully review the unit code and fix it