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

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

    procedure BuildTableAliasMap;
    function GetTableInfoByAliasOrName(const AliasOrName: string): TSQLTableInfo;
    function IsExpression(const Expr: string): Boolean;
    function IsSQLKeywordOrFunction(const Word: string): Boolean;
    procedure ParseFromClause;
    procedure ParseSelectClause;
    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; Tables: TObjectList<TSQLTableInfo>);
    function ContainsWildcard(const SelectClause: string): Boolean;
    procedure ExpandWildcardColumn(const WildcardColumn: TSQLColumnInfo);
    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;
    function GetTableDisplayName(TableInfo: TSQLTableInfo): string;
    procedure LogTables;
    procedure ResolveExpressionTypes;
    procedure LogColumnTypes;

  public
    constructor Create(const SQLText: string; AConnection: TUniConnection);
    destructor Destroy; override;
    procedure Parse;

    property Tables: TObjectList<TSQLTableInfo> read FTables;
    property Columns: TObjectList<TSQLColumnInfo> read FColumns;
  end;

implementation

{ TSQLColumnInfo }

constructor TSQLColumnInfo.Create;
begin
  inherited Create;
  FResolvedTableInfo := nil;
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;

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;

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

procedure TSQLTableInfo.LoadPrimaryKeys;
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.KEY_COLUMN_USAGE ' +
      'WHERE TABLE_SCHEMA = :schema AND TABLE_NAME = :table ' +
      'AND CONSTRAINT_NAME = ''PRIMARY'' ' +
      'ORDER BY ORDINAL_POSITION';

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

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

{ TSQLParser }

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

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

procedure TSQLParser.Parse;

  function NormalizeSQLWhitespace(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;

begin
  try
    // Normalize SQL text while preserving quoted strings
    FSQLText := NormalizeSQLWhitespace(FSQLText);

    ParseFromClause;
    ParseSelectClause;
    LoadTableColumnsMetadata;
    ValidateColumns;
    ResolveExpressionTypes;
  except
    on E: Exception do
      raise Exception.Create('Error parsing SQL: ' + E.Message);
  end;
end;

procedure TSQLParser.ParseFromClause;
var
  FromMatch: TMatch;
  FromPos: Integer;
  FromClause: string;
  RemainingText: string;
begin
  try
    FromMatch := TRegEx.Match(FSQLText, '\bFROM\b', [roIgnoreCase]);
    if not FromMatch.Success then
      raise Exception.Create('Cannot find FROM clause in the query');

    FromPos := FromMatch.Index + FromMatch.Length;
    if FromPos > Length(FSQLText) then
      raise Exception.Create('Invalid FROM clause position');

    RemainingText := Copy(FSQLText, FromPos + 1, MaxInt).Trim;
    if RemainingText = '' then
      raise Exception.Create('Empty FROM clause');

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

    if ClauseEndMatch.Success then
      FromClause := Copy(RemainingText, 1, ClauseEndMatch.Index - 1).Trim
    else
      FromClause := RemainingText.Trim;

    ParseBaseTableAndJoins(FromClause);
    LoadTableColumnsMetadata;
  except
    on E: Exception do
      raise Exception.Create('Error parsing FROM clause: ' + E.Message);
  end;
end;

procedure TSQLParser.ParseBaseTableAndJoins(const FromClause: string);
var
  TableInfo: TSQLTableInfo;
  CurrentPos, NextPartStart: Integer;
  TablePart, RemainingPart: string;
begin
  if FromClause = '' then
    raise Exception.Create('Empty FROM clause');

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

    // Parse base table (everything before the first JOIN)
    TablePart := Copy(FromClause, 1, CurrentPos - 1).Trim;
    if TablePart = '' then
      raise Exception.Create('Missing base table');

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

    // Process JOINs
    RemainingPart := Copy(FromClause, CurrentPos, 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+|\w+(\.\w+)?)\s+ON\s+(.+?)(?=\s+(LEFT|RIGHT|INNER|OUTER|CROSS|FULL)?\s*JOIN\s+|$)',
        [roIgnoreCase]);

      if not JoinMatch.Success then
      begin
        // Check if there's a simple JOIN remaining
        JoinMatch := TRegEx.Match(RemainingPart,
          '^\s*JOIN\s+(\w+(\.\w+)?(\s+AS)?\s+\w+|\w+(\.\w+)?)\s+ON\s+(.+)$',
          [roIgnoreCase]);

        if not JoinMatch.Success then
          Break;
      end;

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

      // Extract table part
      TablePart := JoinMatch.Groups[2].Value.Trim;

      // Create and configure the table info
      TableInfo := ParseTableReference(TablePart);
      if Assigned(TableInfo) then
      begin
        TableInfo.JoinType := JoinType;
        TableInfo.JoinCondition := JoinMatch.Groups[6].Value.Trim;
        FTables.Add(TableInfo);
      end;

      // Move to next JOIN if any
      NextPartStart := JoinMatch.Index + JoinMatch.Length;
      if NextPartStart >= Length(RemainingPart) then
        Break;

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

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

function TSQLParser.ParseTableReference(const TableRef: string): TSQLTableInfo;
var
  Parts: TArray<string>;
  JoinRemoved: string;
const
  JOIN_KEYWORDS: array[0..6] of string = (
    'LEFT JOIN', 'RIGHT JOIN', 'INNER JOIN', 'OUTER JOIN',
    'CROSS JOIN', 'FULL JOIN', 'JOIN'
  );
begin
  if TableRef.Trim = '' then
    Exit(nil);

  Result := TSQLTableInfo.Create(FConnection);
  try
    // First, clean up the input string by removing JOIN keywords
    JoinRemoved := TableRef.Trim;
    for var JoinKeyword in JOIN_KEYWORDS do
      JoinRemoved := ReplaceStr(UpperCase(JoinRemoved), JoinKeyword, '');
    JoinRemoved := JoinRemoved.Trim;

    // Split remaining text on 'AS' keyword first
    Parts := JoinRemoved.Split([' AS ', ' as ', ' As ', ' aS ']);

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

    // Handle the table name part (first part)
    var TableNamePart := Parts[0].Trim(['"', '[', ']', ' ']);

    // Process schema.table notation
    if Pos('.', TableNamePart) > 0 then
    begin
      var SchemaParts := TableNamePart.Split(['.']);
      if Length(SchemaParts) = 2 then
      begin
        Result.SchemaName := LowerCase(SchemaParts[0].Trim(['"', '[', ']', ' ']));
        Result.TableName := LowerCase(SchemaParts[1].Trim(['"', '[', ']', ' ']));
      end
      else
      begin
        Result.SchemaName := LowerCase(FConnection.Database);
        Result.TableName := LowerCase(TableNamePart);
      end;
    end
    else
    begin
      Result.SchemaName := LowerCase(FConnection.Database);
      Result.TableName := LowerCase(TableNamePart);
    end;

    // Handle alias if present
    if Length(Parts) > 1 then
    begin
      // Get the last part after AS
      var AliasPart := Parts[High(Parts)].Trim;
      // Split on space to get first word only
      var AliasParts := AliasPart.Split([' '], TStringSplitOptions.ExcludeEmpty);
      if Length(AliasParts) > 0 then
        Result.Alias := AliasParts[0].Trim(['"', '[', ']', ' ']);
    end
    else
    begin
      // Check for space-separated alias (without AS keyword)
      Parts := TableNamePart.Split([' '], TStringSplitOptions.ExcludeEmpty);
      if Length(Parts) > 1 then
      begin
        // Update table name to be just the first part
        if Pos('.', Parts[0]) > 0 then
        begin
          var SchemaParts := Parts[0].Split(['.']);
          if Length(SchemaParts) = 2 then
          begin
            Result.SchemaName := LowerCase(SchemaParts[0].Trim(['"', '[', ']', ' ']));
            Result.TableName := LowerCase(SchemaParts[1].Trim(['"', '[', ']', ' ']));
          end;
        end
        else
        begin
          Result.SchemaName := LowerCase(FConnection.Database);
          Result.TableName := LowerCase(Parts[0].Trim(['"', '[', ']', ' ']));
        end;

        // Set alias as the last part if it's not a JOIN-related keyword
        var LastPart := Parts[High(Parts)].Trim(['"', '[', ']', ' ']);
        if (LastPart <> '') and
           (Pos('JOIN', UpperCase(LastPart)) = 0) and
           (not SameText(LastPart, 'LEFT')) and
           (not SameText(LastPart, 'RIGHT')) and
           (not SameText(LastPart, 'INNER')) and
           (not SameText(LastPart, 'OUTER')) and
           (not SameText(LastPart, 'CROSS')) and
           (not SameText(LastPart, 'FULL')) then
        begin
          Result.Alias := LastPart;
        end;
      end;
    end;

  except
    Result.Free;
    raise;
  end;
end;

// Add this helper method to TSQLParser
function TSQLParser.GetTableDisplayName(TableInfo: TSQLTableInfo): string;
begin
  Result := TableInfo.FullTableName;
end;

// Update any methods that display table information to use GetTableDisplayName
// For example, if you have a method that lists tables:
procedure TSQLParser.LogTables;
begin
  for var Table in FTables do
    WriteLn(GetTableDisplayName(Table));
end;

function CountChar(const S: string; C: Char): Integer;
var
  i: Integer;
begin
  Result := 0;
  for i := 1 to Length(S) do
    if S[i] = C then Inc(Result);
end;

procedure TSQLParser.ParseSelectClause;
var
  SelectMatch, FromMatch: TMatch;
  SelectPos, FromPos: Integer;
  SelectClause: string;
  ColumnInfo: TSQLColumnInfo;
  Columns: TStringList;
  ParenCount: Integer;
  CurrentColumn: string;
  i: Integer;
begin
  try
    // Find SELECT and FROM
    SelectMatch := TRegEx.Match(FSQLText, '\bSELECT\b', [roIgnoreCase]);
    FromMatch := TRegEx.Match(FSQLText, '\bFROM\b', [roIgnoreCase]);

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

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

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

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

    // Split the SELECT clause into individual column expressions
    Columns := TStringList.Create;
    try
      // Handle commas not within parentheses
      ParenCount := 0;
      CurrentColumn := '';
      i := 1;

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

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

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

        ColumnInfo := TSQLColumnInfo.Create;
        try
          var OriginalExpr := ColExpr;
          var AliasFound := False;
          var IsExpressionCol := False;

          // Check for numeric literal or expression first
          if IsExpression(ColExpr) then
            IsExpressionCol := True;

          // Check for "AS" alias first
          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;
            AliasFound := True;
          end
          else
          begin
            // Check for space-separated alias
            var SpaceMatch := TRegEx.Match(ColExpr, '^(.+?)\s+(\w+)$', [roIgnoreCase]);
            if SpaceMatch.Success then
            begin
              var PossibleAlias := SpaceMatch.Groups[2].Value.Trim;
              if not IsSQLKeywordOrFunction(PossibleAlias) then
              begin
                ColumnInfo.FullName := SpaceMatch.Groups[1].Value.Trim;
                ColumnInfo.QueryColumnAlias := PossibleAlias;
                AliasFound := True;
              end;
            end;
          end;

          // If no alias found, handle appropriately
          if not AliasFound then
          begin
            ColumnInfo.FullName := ColExpr;
            if IsExpressionCol then
              ColumnInfo.QueryColumnAlias := 'expr_' + IntToStr(FColumns.Count + 1)
            else
              ColumnInfo.QueryColumnAlias := ColExpr;
          end;

          // Handle expression columns
          if IsExpressionCol then
          begin
            ColumnInfo.RealSchemaName := '[expression]';
            ColumnInfo.RealTableName := '[expression]';
            ColumnInfo.ReferencedTableColumn := ColumnInfo.QueryColumnAlias;
          end
          else
          begin
            // Handle regular columns
            var FullName := ColumnInfo.FullName;
            if Pos('.', FullName) > 0 then
            begin
              var DotPos := Pos('.', FullName);
              var TableAlias := Copy(FullName, 1, DotPos - 1);
              ColumnInfo.ReferencedTableColumn := Copy(FullName, DotPos + 1, MaxInt);
              ResolveColumnTableInfo(ColumnInfo, TableAlias, FTables);
            end
            else
            begin
              ColumnInfo.ReferencedTableColumn := FullName;
              ResolveColumnTableInfo(ColumnInfo, '', FTables);
            end;
          end;

          FColumns.Add(ColumnInfo);
          ColumnInfo := nil; // Prevent double-free
        except
          ColumnInfo.Free;
          raise;
        end;
      end;

    finally
      Columns.Free;
    end;

  except
    on E: Exception do
      raise Exception.Create('Error parsing SELECT clause: ' + E.Message);
  end;
end;

procedure TSQLParser.ResolveColumnTableInfo(ColumnInfo: TSQLColumnInfo;
  const TableAlias: string; Tables: TObjectList<TSQLTableInfo>);
begin
  if not Assigned(ColumnInfo) then
    Exit;

  var ExplicitTable := TableAlias <> '';
  var FoundInJoinedTable := False;
  var ColumnTableInfo: TSQLTableInfo := nil;

  // If explicit table reference exists, try to find it first
  if ExplicitTable then
  begin
    for var TableInfo in Tables do
    begin
      if SameText(TableInfo.Alias, TableAlias) or
         ((TableInfo.Alias = '') and
          (SameText(TableInfo.TableName, TableAlias) or
           SameText(TableInfo.SchemaName + '.' + TableInfo.TableName, TableAlias))) then
      begin
        ColumnInfo.RealSchemaName := TableInfo.SchemaName;
        ColumnInfo.RealTableName := TableInfo.TableName;
        ColumnInfo.ResolvedTableInfo := TableInfo;
        Exit;
      end;
    end;
    // If explicit table not found, mark as unknown
    ColumnInfo.RealSchemaName := '[unknown]';
    ColumnInfo.RealTableName := '[unknown]';
    Exit;
  end;

  // For unqualified columns, prioritize directly joined tables
  for var TableInfo in Tables do
  begin
    if not Assigned(TableInfo) then
      Continue;

    if ColumnExistsInTable(TableInfo.SchemaName, TableInfo.TableName, ColumnInfo.ReferencedTableColumn) then
    begin
      if TableInfo.JoinType <> '' then
      begin
        // Found in a joined table
        if not FoundInJoinedTable then
        begin
          ColumnTableInfo := TableInfo;
          FoundInJoinedTable := True;
        end
        else
        begin
          // Column exists in multiple joined tables
          ColumnInfo.RealSchemaName := '[ambiguous]';
          ColumnInfo.RealTableName := '[ambiguous]';
          Exit;
        end;
      end
      else if not FoundInJoinedTable then
      begin
        // Found in main table
        ColumnTableInfo := TableInfo;
      end;
    end;
  end;

  // Assign final resolution
  if Assigned(ColumnTableInfo) then
  begin
    ColumnInfo.RealSchemaName := ColumnTableInfo.SchemaName;
    ColumnInfo.RealTableName := ColumnTableInfo.TableName;
    ColumnInfo.ResolvedTableInfo := ColumnTableInfo;
  end
  else
  begin
    ColumnInfo.RealSchemaName := '[unknown]';
    ColumnInfo.RealTableName := '[unknown]';
  end;
end;

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

procedure TSQLParser.ExpandWildcardColumn(const WildcardColumn: TSQLColumnInfo);
begin
  if WildcardColumn.RealTableName = '' then // Unqualified *
  begin
    for var TableInfo in FTables do
    begin
      for var ColumnName in TableInfo.Columns do
      begin
        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]);
          FColumns.Add(NewColumn);
        except
          NewColumn.Free;
          raise;
        end;
      end;
    end;
  end
  else // Table qualified *
  begin
    var TableInfo := GetTableInfoByAliasOrName(WildcardColumn.RealTableName);
    if Assigned(TableInfo) then
    begin
      for var ColumnName in TableInfo.Columns do
      begin
        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]);
          FColumns.Add(NewColumn);
        except
          NewColumn.Free;
          raise;
        end;
      end;
    end;
  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.IsSQLKeywordOrFunction(const Word: string): Boolean;
const
  SQLKeywords: array[0..49] 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'
  );
begin
  Result := False;
  for var Keyword in SQLKeywords do
    if SameText(Word, Keyword) then
      Exit(True);
end;

function TSQLParser.IsExpression(const Expr: string): Boolean;
const
  Operators: array[0..13] of string = (
    '+', '-', '*', '/', '=', '>', '<', '>=', '<=', '<>',
    ' AND ', ' OR ', ' IN ', ' BETWEEN '
  );
begin
  Result := False;

  if Expr = '' then
    Exit;

  var LowerExpr := LowerCase(Expr);

  // Check for numeric literals
  if TRegEx.IsMatch(Expr, '^\s*\d+(\.\d*)?([eE][+-]?\d+)?\s*$') then
    Exit(True);

  // Check for arithmetic operators
  for var Op in Operators do
    if Pos(Op, Expr) > 0 then
      Exit(True);

  // Check for functions
  if TRegEx.IsMatch(Expr, '\w+\s*\(.*\)') then
    Exit(True);

  // Check for CASE expressions
  if Pos('CASE', LowerExpr) > 0 then
    Exit(True);

  // Check for mathematical operations
  if TRegEx.IsMatch(Expr, '\d+\s*[\+\-\*\/]\s*\d+') then
    Exit(True);

  // Check for column mathematical operations
  if TRegEx.IsMatch(Expr, '\w+\s*[\+\-\*\/]\s*\w+') then
    Exit(True);

  // Check for string literals
  if TRegEx.IsMatch(Expr, '''.*''|".*"') then
    Exit(True);

  // Check for NULL literal
  if SameText(Expr.Trim, 'NULL') then
    Exit(True);

  // Check for boolean literals
  if SameText(Expr.Trim, 'TRUE') or SameText(Expr.Trim, 'FALSE') 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;

function TSQLParser.MapSQLTypeToFieldType(const SQLType: string): TFieldType;
begin
  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;
end;

procedure TSQLParser.ResolveUnknownColumns;
var
  Column: TSQLColumnInfo;
begin
  for Column in FColumns do
  begin
    if (Column.RealSchemaName = '[unknown]') or
       (Column.RealTableName = '[unknown]') then
    begin
      // 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
          var ColumnList := GetTableColumnNames(Table.SchemaName, Table.TableName);
          if Assigned(ColumnList) and
             ColumnList.Contains(LowerCase(Column.ReferencedTableColumn)) then
          begin
            if not Found then
            begin
              Column.RealSchemaName := Table.SchemaName;
              Column.RealTableName := Table.TableName;
              Found := True;
            end
            else
            begin
              // Column is ambiguous (exists in multiple tables)
              Column.RealSchemaName := '[ambiguous]';
              Column.RealTableName := '[ambiguous]';
              Break;
            end;
          end;
        end;
      end
      else
      begin
        Column.RealSchemaName := '[expression]';
        Column.RealTableName := '[expression]';
      end;
    end;
  end;
end;

procedure TSQLParser.ClearColumnCache;
var
  List: TList<string>;
begin
  for List in FColumnCache.Values do
    List.Free;
  FColumnCache.Clear;
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;

// Add helper method to check if a column exists in a table
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.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.BuildTableAliasMap;
begin
  if not Assigned(FTableAliasMap) then
    FTableAliasMap := TDictionary<string, TSQLTableInfo>.Create;

  FTableAliasMap.Clear;

  for var TableInfo in FTables do
  begin
    // Map table name
    if not FTableAliasMap.ContainsKey(LowerCase(TableInfo.TableName)) then
      FTableAliasMap.Add(LowerCase(TableInfo.TableName), TableInfo);

    // Map alias if present
    if (TableInfo.Alias <> '') and
       not FTableAliasMap.ContainsKey(LowerCase(TableInfo.Alias)) then
      FTableAliasMap.Add(LowerCase(TableInfo.Alias), TableInfo);
  end;
end;

function TSQLParser.GetTableInfoByAliasOrName(const AliasOrName: string): TSQLTableInfo;
begin
  if not Assigned(FTableAliasMap) then
    BuildTableAliasMap;

  if not FTableAliasMap.TryGetValue(LowerCase(AliasOrName), Result) then
    Result := nil;
end;

// Add this optional helper method to make debugging easier
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;

{ TColumnTypeDetail }

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;

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;

// Now update the debug output calls in ResolveExpressionTypes:

procedure TSQLParser.ResolveExpressionTypes;
var
  TestQuery: TUniQuery;
  TypeDetail: TColumnTypeDetail;
  i: Integer;
  QuerySQL: string;
begin
  if FColumns.Count = 0 then
    Exit;

  TSQLDebugLogger.Log('Starting type resolution');
  TestQuery := TUniQuery.Create(nil);
  try
    TestQuery.Connection := FConnection;

    // Build a minimal test query for expressions
    var SelectList := TStringList.Create;
    try
      SelectList.Clear;

      // Add all expressions and regular columns maintaining their aliases
      for var Column in FColumns do
      begin
        var ColExpr: string;
        if Column.RealSchemaName = '[expression]' then
          ColExpr := Format('%s AS %s', [Column.FullName, Column.QueryColumnAlias])
        else if Column.QueryColumnAlias <> '' then
          // For regular columns with aliases
          if Pos('.', Column.FullName) > 0 then
            ColExpr := Format('%s AS %s', [Column.FullName, Column.QueryColumnAlias])
          else
            ColExpr := Format('%s AS %s', [Column.ReferencedTableColumn, Column.QueryColumnAlias])
        else
          // For regular columns without aliases
          ColExpr := Column.ReferencedTableColumn;

        SelectList.Add(ColExpr);
      end;

    // Use the original query and just add LIMIT 1 if needed
    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;

      // Now process each column
      for i := 0 to FColumns.Count - 1 do
      begin
        var FieldName := FColumns[i].QueryColumnAlias;
        if FieldName = '' then
          FieldName := FColumns[i].ReferencedTableColumn;

        TSQLDebugLogger.Log(Format('Looking for field: %s (Original: %s)', [FieldName, FColumns[i].FullName]));

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

          FColumns[i].DataType := Field.DataType;
          TypeDetail.Initialize;
          TypeDetail.BaseType := Field.DataType;
          TypeDetail.IsNullable := not Field.Required;

          case Field.DataType of
            ftString, ftWideString, ftFixedChar, ftFixedWideChar:
              begin
                TypeDetail.Length := Field.Size;
                TSQLDebugLogger.Log(Format('String length: %d', [Field.Size]));
              end;

            ftBCD, ftFMTBcd:
              begin
                if Field is TBCDField then
                begin
                  TypeDetail.Precision := TBCDField(Field).Precision;
                  TypeDetail.Scale := TBCDField(Field).Size;
                  TSQLDebugLogger.Log(Format('BCD Precision: %d, Scale: %d',
                    [TypeDetail.Precision, TypeDetail.Scale]));
                end;
              end;

            ftFloat, ftCurrency:
              begin
                if Field is TFloatField then
                begin
                  TypeDetail.Precision := TFloatField(Field).Precision;
                  if TypeDetail.Precision = 0 then
                    TypeDetail.Precision := 15;
                  TypeDetail.Scale := 4;
                  TSQLDebugLogger.Log(Format('Float Precision: %d, Scale: %d',
                    [TypeDetail.Precision, TypeDetail.Scale]));
                end;
              end;

            ftInteger, ftSmallint, ftWord, ftLongWord:
              begin
                TypeDetail.Precision := 10;
                TypeDetail.Scale := 0;
                TSQLDebugLogger.Log('Integer type detected');
              end;

            ftLargeint:
              begin
                TypeDetail.Precision := 19;
                TypeDetail.Scale := 0;
                TSQLDebugLogger.Log('Large Integer type detected');
              end;

            ftDateTime, ftTimeStamp:
              begin
                TypeDetail.Precision := 0;
                TypeDetail.Scale := 0;
                TSQLDebugLogger.Log('DateTime type detected');
              end;

            ftDate:
              begin
                TypeDetail.Precision := 0;
                TypeDetail.Scale := 0;
                TSQLDebugLogger.Log('Date type detected');
              end;

            ftTime:
              begin
                TypeDetail.Precision := 0;
                TypeDetail.Scale := 0;
                TSQLDebugLogger.Log('Time type detected');
              end;

            ftBoolean:
              begin
                TypeDetail.Precision := 1;
                TypeDetail.Scale := 0;
                TSQLDebugLogger.Log('Boolean type detected');
              end;

            else
              TSQLDebugLogger.Log(Format('Other type detected: %s',
                [GetEnumName(TypeInfo(TFieldType), Ord(Field.DataType))]));
          end;

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

    finally
      SelectList.Free;
    end;

  finally
    TestQuery.Free;
    TSQLDebugLogger.Log('Finished type resolution');
    TSQLDebugLogger.Log('----------------------------------------');
  end;
end;

end.
-----------

Carefully review and enhance SQLParserEnhanced.pas so it can parse SQL queries like:  
SELECT cliente.*, employeename,employeeid,departmentid,projectid,projectname,projectbudget,projectdate,projectstartmoment,projectbudget * 2 as budgetlimit, age, older  FROM orm_employee  LEFT JOIN centauro020.cliente ON cliente.cliente_id=orm_employee.employeeid   

and can handle something like:  SELECT * FROM orm_eployee INNER JOIN department ON orm_employee.dept_id=department.dept_id   


The code must handle wildcards, each one expanding the corresponding columns of its corresponding table.
Come up with a detailed plan to achive this funcionality and then enhance the existing SQLParserEnhanced.pas, taking care and do not break the existing funcionalities
Make sure that the code is compilable on the latest versions of Delphi