unit SQLParserEnhanced;

interface

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

type
  /// <summary>
  /// Represents information about a column in the SQL query.
  /// </summary>
  TColumnInfo = class
  private
    FFullName: string;
    FRealSchemaName: string;
    FRealTableName: string;
    FReferencedTableColumn: string;
    FQueryColumnAlias: string;
    FIsPKColumn: Boolean;
    FDataType: TFieldType;
  public
    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;
  end;

  /// <summary>
  /// Represents information about a table used in the SQL query.
  /// </summary>
  TTableInfo = class
  private
    FSchemaName: string;
    FTableName: string;
    FAlias: string;
  public
    /// <summary>
    /// Schema name of the table.
    /// </summary>
    property SchemaName: string read FSchemaName write FSchemaName;

    /// <summary>
    /// Name of the table.
    /// </summary>
    property TableName: string read FTableName write FTableName;

    /// <summary>
    /// Alias of the table as defined in the query.
    /// </summary>
    property Alias: string read FAlias write FAlias;
  end;

  /// <summary>
  /// Parses SQL SELECT statements to extract table and column information.
  /// </summary>
  TSQLParser = class
  private
    FSQLText: string;
    FTables: TObjectList<TTableInfo>;
    FColumns: TObjectList<TColumnInfo>;
    FConnection: TUniConnection;
    FPrimaryKeys: TDictionary<string, TList<string>>;
    FTableColumns: TDictionary<string, TList<string>>; // Cache for table columns

    procedure Parse;
    procedure ParseFromClause;
    procedure ParseSelectClause;
    function SplitSelectColumns(const SelectClause: string): TArray<string>;
    function IsExpression(const S: string): Boolean;
    function IsClauseKeyword(const Token: string): Boolean;
    function GetTableInfoByAliasOrName(const AliasOrName: string): TTableInfo;
    function IsJoinType(const Token: string): Boolean;
    procedure CheckPrimaryKeys; // Checks if columns are primary keys
    procedure FillPrimaryKeyDictionary; // Fills the primary key dictionary
    function GetTableColumnNames(const SchemaName, TableName: string): TList<string>; // Get column names for a table
    function IsReservedWord(const Token: string; const ReservedWords: array of string): Boolean;
  public
    procedure AssignDataTypesToColumns;
    /// <summary>
    /// Initializes a new instance of the TSQLParser class.
    /// </summary>
    /// <param name="SQLText">The SQL query text to parse.</param>
    /// <param name="AConnection">The database connection used to retrieve schema information.</param>
    constructor Create(const SQLText: string; AConnection: TUniConnection);
    /// <summary>
    /// Frees all resources used by the TSQLParser instance.
    /// </summary>
    destructor Destroy; override;

    /// <summary>
    /// Gets the list of tables parsed from the SQL query.
    /// </summary>
    property Tables: TObjectList<TTableInfo> read FTables;

    /// <summary>
    /// Gets the list of columns parsed from the SQL query.
    /// </summary>
    property Columns: TObjectList<TColumnInfo> read FColumns;
  end;

implementation

{ TSQLParser }

constructor TSQLParser.Create(const SQLText: string; AConnection: TUniConnection);
begin
  inherited Create;
  FSQLText := SQLText;
  FConnection := AConnection; // Store the provided database connection
  FTables := TObjectList<TTableInfo>.Create(True); // Owns objects
  FColumns := TObjectList<TColumnInfo>.Create(True); // Owns objects
  FPrimaryKeys := TDictionary<string, TList<string>>.Create;
  FTableColumns := TDictionary<string, TList<string>>.Create;
  Parse;
  FillPrimaryKeyDictionary; // After parsing, fill the primary key dictionary
  CheckPrimaryKeys; // Then check for primary key columns using the cache
  AssignDataTypesToColumns; // Assign data types to columns
end;

destructor TSQLParser.Destroy;
var
  ColumnsList: TList<string>;
begin
  // Free the lists inside the dictionaries
  for ColumnsList in FPrimaryKeys.Values do
    ColumnsList.Free;
  FPrimaryKeys.Free;

  for ColumnsList in FTableColumns.Values do
    ColumnsList.Free;
  FTableColumns.Free;

  FTables.Free;
  FColumns.Free;
  inherited;
end;

procedure TSQLParser.Parse;
begin
  ParseFromClause;
  ParseSelectClause;
end;

procedure TSQLParser.AssignDataTypesToColumns;
var
  Query: TUniQuery;
  Field: TField;
  ColumnInfo: TColumnInfo;
  FieldName: string;
begin
  Query := TUniQuery.Create(nil);
  try
    Query.Connection := FConnection;
    Query.SQL.Text := FSQLText;
    try
      Query.Open;
      // Iterate over fields to get field types
      for Field in Query.Fields do
      begin
        FieldName := Field.FieldName;
        // Try to find matching ColumnInfo by QueryColumnAlias or FullName
        for ColumnInfo in FColumns do
        begin
          if SameText(ColumnInfo.QueryColumnAlias, FieldName) or
             SameText(ColumnInfo.FullName, FieldName) or
             SameText(ColumnInfo.ReferencedTableColumn, FieldName) then
          begin
            ColumnInfo.DataType := Field.DataType;
            Break;
          end;
        end;
      end;
      Query.Close;
    except
      on E: Exception do
      begin
        // Handle exception, e.g., log error message
        // Optionally set DataType to ftUnknown for all columns
        for ColumnInfo in FColumns do
          ColumnInfo.DataType := ftUnknown;
      end;
    end;
  finally
    Query.Free;
  end;
end;

function TSQLParser.IsClauseKeyword(const Token: string): Boolean;
const
  ClauseKeywords: array[0..10] of string = (
    'WHERE', 'GROUP BY', 'HAVING', 'ORDER BY', 'LIMIT',
    'OFFSET', 'UNION', 'INTERSECT', 'EXCEPT', 'EXISTS', ';'
  );
var
  Keyword: string;
begin
  Result := False;
  for Keyword in ClauseKeywords do
  begin
    if SameText(Token, Keyword) then
      Exit(True);
  end;
end;

procedure TSQLParser.ParseFromClause;
var
  FromMatch, NextClauseMatch: TMatch;
  FromPos, NextClausePos: Integer;
  FromClause: string;
  Tokens: TArray<string>;
  i: Integer;
  Token: string;
  TableInfo: TTableInfo;
  NextToken: string;
  RemainingText: string;
begin
  // Find position of FROM
  FromMatch := TRegEx.Match(FSQLText, '\bFROM\b', [roIgnoreCase]);
  if not FromMatch.Success then Exit; // No FROM clause found
  FromPos := FromMatch.Index + FromMatch.Length;

  // Extract the text after FROM
  RemainingText := Copy(FSQLText, FromPos + 1, MaxInt).Trim;

  // Find position of the next clause (e.g., WHERE, GROUP BY, etc.) in RemainingText
  NextClauseMatch := TRegEx.Match(RemainingText, '\b(WHERE|GROUP BY|HAVING|ORDER BY|LIMIT|OFFSET|UNION|INTERSECT|EXCEPT|EXISTS|;)\b', [roIgnoreCase]);
  if NextClauseMatch.Success then
    NextClausePos := NextClauseMatch.Index
  else
    NextClausePos := Length(RemainingText) + 1; // No next clause found

  // Extract FROM clause
  FromClause := Copy(RemainingText, 1, NextClausePos - 1).Trim;

  // Split the FROM clause into tokens using spaces and commas, considering possible quoted identifiers
  Tokens := TRegEx.Split(FromClause, '\s+|,(?![^()]*\))'); // Split on spaces or commas not within parentheses

  i := 0;
  while i < Length(Tokens) do
  begin
    Token := Trim(Tokens[i]);
    if Token = '' then
    begin
      Inc(i);
      Continue;
    end;

    if IsJoinType(Token) or SameText(Token, 'JOIN') then
    begin
      // Skip join type and 'JOIN' keyword
      Inc(i);
      Continue;
    end
    else if SameText(Token, 'ON') then
    begin
      // Skip tokens until the next 'JOIN' or clause keyword
      Inc(i);
      while (i < Length(Tokens)) and not (IsJoinType(Tokens[i]) or SameText(Tokens[i], 'JOIN') or IsClauseKeyword(Tokens[i])) do
        Inc(i);
      Continue;
    end
    else if SameText(Token, 'USING') then
    begin
      // Skip tokens within 'USING (...)'
      Inc(i);
      while (i < Length(Tokens)) and not (Tokens[i].Contains(')')) do
        Inc(i);
      Inc(i); // Skip the token with ')'
      Continue;
    end
    else
    begin
      // Process table name (may include schema)
      TableInfo := TTableInfo.Create;

      // Check for schema.table format, considering possible quoted identifiers
      if Pos('.', Token) > 0 then
      begin
        var DotPos := LastDelimiter('.', Token);
        TableInfo.SchemaName := Copy(Token, 1, DotPos - 1).Trim(['"', '`']);
        TableInfo.TableName := Copy(Token, DotPos + 1, Length(Token)).Trim(['"', '`']);
      end
      else
      begin
        TableInfo.SchemaName := FConnection.Database; // Assume default schema
        TableInfo.TableName := Token.Trim(['"', '`']);
      end;

      // Check for alias
      if (i + 1) < Length(Tokens) then
      begin
        NextToken := Tokens[i + 1];
        if SameText(NextToken, 'AS') then
        begin
          // Alias after 'AS' keyword
          if (i + 2) < Length(Tokens) then
          begin
            TableInfo.Alias := Tokens[i + 2].Trim(['"', '`']);
            i := i + 2; // skip 'AS' and the alias
          end
          else
          begin
            // 'AS' without alias - skip 'AS'
            i := i + 1;
          end;
        end
        else if not (IsJoinType(NextToken) or SameText(NextToken, 'JOIN') or SameText(NextToken, 'ON')
          or SameText(NextToken, 'USING') or IsClauseKeyword(NextToken)) then
        begin
          // Alias without 'AS'
          TableInfo.Alias := NextToken.Trim(['"', '`']);
          Inc(i); // move to the alias
        end;
      end;

      FTables.Add(TableInfo);

      Inc(i); // move to the next token after processing
    end;
  end;
end;

function TSQLParser.SplitSelectColumns(const SelectClause: string): TArray<string>;
var
  i: Integer;
  c: Char;
  InSingleQuote, InDoubleQuote: Boolean;
  ParenthesisDepth: Integer;
  CurrentColumn: string;
  ColumnsList: TList<string>;
begin
  InSingleQuote := False;
  InDoubleQuote := False;
  ParenthesisDepth := 0;
  CurrentColumn := '';
  ColumnsList := TList<string>.Create;
  try
    i := 1;
    while i <= Length(SelectClause) do
    begin
      c := SelectClause[i];
      if (c = '''') and not InDoubleQuote then
      begin
        InSingleQuote := not InSingleQuote;
        CurrentColumn := CurrentColumn + c;
      end
      else if (c = '"') and not InSingleQuote then
      begin
        InDoubleQuote := not InDoubleQuote;
        CurrentColumn := CurrentColumn + c;
      end
      else if not InSingleQuote and not InDoubleQuote then
      begin
        if c = '(' then
        begin
          Inc(ParenthesisDepth);
          CurrentColumn := CurrentColumn + c;
        end
        else if c = ')' then
        begin
          Dec(ParenthesisDepth);
          CurrentColumn := CurrentColumn + c;
        end
        else if (c = ',') and (ParenthesisDepth = 0) then
        begin
          // Column separator
          ColumnsList.Add(Trim(CurrentColumn));
          CurrentColumn := '';
        end
        else
        begin
          CurrentColumn := CurrentColumn + c;
        end
      end
      else
      begin
        // Inside quotes
        CurrentColumn := CurrentColumn + c;
      end;
      Inc(i);
    end;
    // After the loop, add the last column
    if CurrentColumn.Trim <> '' then
      ColumnsList.Add(Trim(CurrentColumn));
    Result := ColumnsList.ToArray;
  finally
    ColumnsList.Free;
  end;
end;

procedure TSQLParser.ParseSelectClause;
var
  SelectMatch, FromMatch: TMatch;
  SelectClause: string;
  ColumnsStr: string;
  ColumnList: TArray<string>;
  ColumnStr: string;
  ColumnInfo: TColumnInfo;
  Regex: TRegEx;
  Match: TMatch;
  TableAliasOrName: string;
  ColumnName: string;
  TableInfo: TTableInfo;
  ReservedWords: array of string;
  Tokens: TArray<string>;
  i: Integer;
  SelectPos, FromPos: Integer;
  SelectLength: Integer;
begin
  // Find position of SELECT and FROM
  SelectMatch := TRegEx.Match(FSQLText, '\bSELECT\b', [roIgnoreCase]);
  FromMatch := TRegEx.Match(FSQLText, '\bFROM\b', [roIgnoreCase]);

  if (not SelectMatch.Success) or (not FromMatch.Success) then Exit;

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

  // Extract the SELECT clause content
  SelectLength := FromPos - SelectPos;
  if SelectLength <= 0 then Exit;
  SelectClause := Copy(FSQLText, SelectPos + 1, SelectLength - 1).Trim;

  // List of reserved words that may appear after SELECT
  ReservedWords := ['ALL', 'DISTINCT', 'DISTINCTROW', 'HIGH_PRIORITY', 'STRAIGHT_JOIN',
                    'SQL_SMALL_RESULT', 'SQL_BIG_RESULT', 'SQL_BUFFER_RESULT', 'SQL_CACHE',
                    'SQL_NO_CACHE', 'SQL_CALC_FOUND_ROWS'];

  // Split the SelectClause into tokens to check for reserved words at the beginning
  Tokens := TRegEx.Split(SelectClause, '\s+');
  i := 0;
  // Skip reserved words at the beginning
  while (i < Length(Tokens)) and IsReservedWord(Tokens[i], ReservedWords) do
  begin
    Inc(i);
  end;

  // Reconstruct ColumnsStr without leading reserved words
  if i >= Length(Tokens) then
    ColumnsStr := ''
  else
    ColumnsStr := String.Join(' ', Copy(Tokens, i, Length(Tokens) - i));

  if ColumnsStr.IsEmpty then Exit;

  // Use the new SplitSelectColumns function to split columns
  ColumnList := SplitSelectColumns(ColumnsStr);

  for i := 0 to Length(ColumnList) - 1 do
  begin
    ColumnStr := Trim(ColumnList[i]);

    // Regular expression to extract column expressions and aliases
    // Handles cases like "expression AS alias", "expression alias", or just "expression"
    Regex := TRegEx.Create('^(.*?)(?:\s+(?:AS\s+)?(\w+))?$', [roIgnoreCase]);
    Match := Regex.Match(ColumnStr);

    ColumnInfo := TColumnInfo.Create; // Create the ColumnInfo object

    if Match.Success then
    begin
      ColumnInfo.FullName := Match.Groups[1].Value.Trim;
      if Match.Groups.Count > 2 then
        ColumnInfo.QueryColumnAlias := Match.Groups[2].Value.Trim
      else
        ColumnInfo.QueryColumnAlias := '';
    end
    else
    begin
      ColumnInfo.FullName := ColumnStr;
      ColumnInfo.QueryColumnAlias := '';
    end;

    // Determine if the column is an expression
    if IsExpression(ColumnInfo.FullName) then
    begin
      // It's an expression
      ColumnInfo.RealSchemaName := '[none]';
      ColumnInfo.RealTableName := '[none]';
      ColumnInfo.ReferencedTableColumn := '[none]';
    end
    else
    begin
      // It's a simple column reference
      if ColumnInfo.FullName.Contains('.') then
      begin
        // Parse table alias/name and column name
        var DotPos := LastDelimiter('.', ColumnInfo.FullName);
        TableAliasOrName := Copy(ColumnInfo.FullName, 1, DotPos - 1).Trim(['"', '`']);
        ColumnName := Copy(ColumnInfo.FullName, DotPos + 1, Length(ColumnInfo.FullName)).Trim(['"', '`']);

        // Get the table info for this alias or name
        TableInfo := GetTableInfoByAliasOrName(TableAliasOrName);

        if Assigned(TableInfo) then
        begin
          ColumnInfo.RealSchemaName := TableInfo.SchemaName;
          ColumnInfo.RealTableName := TableInfo.TableName;
        end
        else
        begin
          // Table alias/name not found; mark as unknown
          ColumnInfo.RealSchemaName := '[unknown schema]';
          ColumnInfo.RealTableName := '[unknown table]';
        end;
        ColumnInfo.ReferencedTableColumn := ColumnName;
      end
      else
      begin
        // Column without table alias/name prefix
        ColumnInfo.ReferencedTableColumn := ColumnInfo.FullName.Trim(['"', '`']);
        ColumnInfo.RealTableName := '';
        ColumnInfo.RealSchemaName := '';

        if FTables.Count = 1 then
        begin
          // Assign the only table's name
          ColumnInfo.RealTableName := FTables[0].TableName;
          ColumnInfo.RealSchemaName := FTables[0].SchemaName;
        end
        else if FTables.Count > 1 then
        begin
          // More than one table: try to find the table(s) that contain this column
          var PossibleTables := TList<TTableInfo>.Create;
          try
            for var TInfo in FTables do
            begin
              var ColumnsList := GetTableColumnNames(TInfo.SchemaName, TInfo.TableName);
              if ColumnsList.Contains(LowerCase(ColumnInfo.ReferencedTableColumn)) then
                PossibleTables.Add(TInfo);
            end;

            if PossibleTables.Count = 1 then
            begin
              // Only one table contains the column, assign it
              ColumnInfo.RealTableName := PossibleTables[0].TableName;
              ColumnInfo.RealSchemaName := PossibleTables[0].SchemaName;
            end
            else if PossibleTables.Count > 1 then
            begin
              // Column is ambiguous - exists in multiple tables
              ColumnInfo.RealTableName := '[ambiguous]';
              ColumnInfo.RealSchemaName := '[ambiguous schema]';
            end
            else
            begin
              // Column doesn't exist in any known table
              ColumnInfo.RealTableName := '[unknown]';
              ColumnInfo.RealSchemaName := '[unknown schema]';
            end;
          finally
            PossibleTables.Free;
          end;
        end
        else
        begin
          // No table information available
          ColumnInfo.RealTableName := '[no tables parsed]';
          ColumnInfo.RealSchemaName := '[no tables parsed]';
        end;
      end;
    end;

    FColumns.Add(ColumnInfo);
  end;
end;

function TSQLParser.IsReservedWord(const Token: string; const ReservedWords: array of string): Boolean;
var
  Word: string;
begin
  for Word in ReservedWords do
  begin
    if SameText(Token, Word) then
      Exit(True);
  end;
  Result := False;
end;

function TSQLParser.IsExpression(const S: string): Boolean;
var
  Value: string;
  ReservedFunctions: array of string;
begin
  Value := Trim(S);

  // Check for string literal (enclosed in single quotes)
  if (Value.StartsWith('''') and Value.EndsWith('''')) then
    Exit(True);

  // Check for string literal (enclosed in double quotes)
  if (Value.StartsWith('"') and Value.EndsWith('"')) then
    Exit(True);

  // Check for numeric literal
  if TRegEx.IsMatch(Value, '^-?\d+(\.\d+)?$') then
    Exit(True);

  // Check for reserved SQL functions
  ReservedFunctions := ['CURRENT_TIMESTAMP', 'NOW', 'SYSDATE', 'GETDATE', 'CURDATE', 'CURRENT_DATE', 'CURRENT_TIME'];

  if IsReservedWord(Value, ReservedFunctions) then
    Exit(True);

  // Existing heuristic for expressions involving operators or parentheses
  Result := Value.Contains('(') or
            Value.Contains('+') or
            Value.Contains('-') or
            Value.Contains('*') or
            Value.Contains('/') or
            (Value.Contains(' ') and not Value.Contains('.'));
end;

function TSQLParser.GetTableInfoByAliasOrName(const AliasOrName: string): TTableInfo;
var
  TableInfo: TTableInfo;
begin
  Result := nil;
  for TableInfo in FTables do
  begin
    if SameText(TableInfo.Alias, AliasOrName) then
      Exit(TableInfo)
    else if (TableInfo.Alias.IsEmpty) and SameText(TableInfo.TableName, AliasOrName) then
      Exit(TableInfo);
  end;
end;

function TSQLParser.GetTableColumnNames(const SchemaName, TableName: string): TList<string>;
var
  ColumnsList: TList<string>;
  Query: TUniQuery;
  ColumnName: string;
  Key: string;
begin
  Key := LowerCase(SchemaName + '.' + TableName);
  if FTableColumns.TryGetValue(Key, ColumnsList) then
  begin
    Result := ColumnsList;
    Exit;
  end;

  // Load column names from the database schema
  ColumnsList := 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 = :SchemaName AND TABLE_NAME = :TableName';
      Query.ParamByName('SchemaName').AsString := SchemaName;
      Query.ParamByName('TableName').AsString := TableName;
      Query.Open;

      while not Query.Eof do
      begin
        ColumnName := LowerCase(Query.FieldByName('COLUMN_NAME').AsString);
        ColumnsList.Add(ColumnName);
        Query.Next;
      end;
      Query.Close;
    finally
      Query.Free;
    end;
    FTableColumns.Add(Key, ColumnsList);
    Result := ColumnsList;
  except
    ColumnsList.Free;
    raise;
  end;
end;

function TSQLParser.IsJoinType(const Token: string): Boolean;
const
  JoinTypes: array[0..7] of string = ('JOIN', 'INNER', 'LEFT', 'RIGHT', 'FULL', 'CROSS', 'NATURAL', 'OUTER');
var
  JoinType: string;
begin
  Result := False;
  if Token.IsEmpty then Exit;
  for JoinType in JoinTypes do
  begin
    if SameText(Token, JoinType) then
      Exit(True);
  end;
end;

procedure TSQLParser.FillPrimaryKeyDictionary;
var
  Query: TUniQuery;
  TableName, ColumnName, SchemaName: string;
  ColumnsList: TList<string>;
begin
  if not Assigned(FConnection) then
    Exit; // Cannot fill without a database connection

  Query := TUniQuery.Create(nil);
  try
    Query.Connection := FConnection;
    // Use the schema name from the connection if not specified
    SchemaName := FConnection.Database;

    Query.SQL.Text :=
      'SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE ' +
      'WHERE TABLE_SCHEMA = :SchemaName AND CONSTRAINT_NAME = ''PRIMARY'' ' +
      'ORDER BY TABLE_NAME, ORDINAL_POSITION';

    Query.ParamByName('SchemaName').AsString := SchemaName;
    Query.Open;

    while not Query.Eof do
    begin
      TableName := LowerCase(Query.FieldByName('TABLE_NAME').AsString);
      ColumnName := LowerCase(Query.FieldByName('COLUMN_NAME').AsString);

      if not FPrimaryKeys.TryGetValue(TableName, ColumnsList) then
      begin
        ColumnsList := TList<string>.Create;
        FPrimaryKeys.Add(TableName, ColumnsList);
      end;

      ColumnsList.Add(ColumnName);

      Query.Next;
    end;

    Query.Close;
  finally
    Query.Free;
  end;
end;

procedure TSQLParser.CheckPrimaryKeys;
var
  ColumnInfo: TColumnInfo;
  TableName: string;
  ColumnName: string;
  ColumnsList: TList<string>;
begin
  for ColumnInfo in FColumns do
  begin
    if (ColumnInfo.RealTableName <> '') and
       not (ColumnInfo.RealTableName.StartsWith('[') and ColumnInfo.RealTableName.EndsWith(']')) and
       (ColumnInfo.ReferencedTableColumn <> '') and
       not (ColumnInfo.ReferencedTableColumn.StartsWith('[') and ColumnInfo.ReferencedTableColumn.EndsWith(']')) then
    begin
      TableName := LowerCase(ColumnInfo.RealTableName);
      ColumnName := LowerCase(ColumnInfo.ReferencedTableColumn);

      if FPrimaryKeys.TryGetValue(TableName, ColumnsList) then
      begin
        ColumnInfo.IsPKColumn := ColumnsList.Contains(ColumnName);
      end
      else
      begin
        // Table not found in primary keys, so column is not a primary key
        ColumnInfo.IsPKColumn := False;
      end;
    end
    else
    begin
      // For expressions or columns without table reference
      ColumnInfo.IsPKColumn := False;
    end;
  end;
end;

end.

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

function GenerateDelphiClassFromQuery(const SQLText: string; const ClassName: string; AConnection: TUniConnection): string;
var
  Parser: TSQLParser;
  ColumnInfo: TColumnInfo;
  i: Integer;
  FieldsList: TObjectList<TColumnInfo>;
  AttrsLine: string;
  FieldName, PropertyName: string;
  DelphiFieldType: string;
  ResultList: TStringList;
  AttributesList: TList<string>;

  function CreateValidIdentifier(const Name: string): string;
  var
    i: Integer;
    c: Char;
  begin
    Result := '';
    for i := 1 to Length(Name) do
    begin
      c := Name[i];
      if c.IsLetterOrDigit or (c = '_') then
        Result := Result + c
      else
        Result := Result + '_'; // Replace invalid chars with '_'
    end;

    // Ensure the identifier does not start with a digit or invalid character
    if (Result = '') or not (Result[1].IsLetter or (Result[1] = '_')) then
      Result := '_' + Result;
  end;

begin
  ResultList := TStringList.Create;
  try
    ResultList.Add('type');
    ResultList.Add(Format(' T%s = class', [ClassName]));
    ResultList.Add(' private');

    Parser := TSQLParser.Create(SQLText, AConnection);
    try
      FieldsList := Parser.Columns;  // Get the columns info

      // Iterate over the columns to generate field declarations
      for i := 0 to FieldsList.Count - 1 do
      begin
        ColumnInfo := FieldsList[i];

        // Map TFieldType to Delphi types
        case ColumnInfo.DataType of
          ftString, ftWideString:
            DelphiFieldType := 'string';
          ftInteger, ftSmallint, ftShortInt, ftWord, ftByte:
            DelphiFieldType := 'Integer';
          ftLargeint:
            DelphiFieldType := 'Int64';
          ftFloat, ftCurrency, ftBCD, ftFMTBcd:
            DelphiFieldType := 'Double';
          ftDate, ftTime, ftDateTime, ftTimeStamp:
            DelphiFieldType := 'TDateTime';
          ftBoolean:
            DelphiFieldType := 'Boolean';
          else
            DelphiFieldType := 'Variant'; // Default to Variant for unsupported types
        end;

        // Use QueryColumnAlias as the field name or the column name
        if ColumnInfo.QueryColumnAlias <> '' then
          FieldName := ColumnInfo.QueryColumnAlias
        else
          FieldName := ColumnInfo.ReferencedTableColumn;

        // Sanitize the FieldName (remove special characters, etc.)
        FieldName := CreateValidIdentifier(FieldName);

        // Initialize the list of attributes
        AttributesList := TList<string>.Create;
        try
          // Build custom attributes
          if not ColumnInfo.RealTableName.IsEmpty and
             not ((ColumnInfo.RealTableName = '[none]') or (ColumnInfo.RealTableName = '[ambiguous]') or (ColumnInfo.RealTableName = '[unknown]')) then
          begin
            // Insert Table attribute
            AttributesList.Add(Format('Table(''%s'')', [ColumnInfo.RealTableName]));
          end;

          if ColumnInfo.IsPKColumn then
          begin
            AttributesList.Add('PrimaryKey');
          end;

          if not ColumnInfo.ReferencedTableColumn.IsEmpty and (ColumnInfo.ReferencedTableColumn <> '[none]') then
          begin
            AttributesList.Add(Format('RefColumn(''%s'')', [ColumnInfo.ReferencedTableColumn]));
          end;

          // Combine attributes
          if AttributesList.Count > 0 then
            AttrsLine := Format('    [%s]', [String.Join(', ', AttributesList.ToArray)])
          else
            AttrsLine := '';

          // Add field declaration
          if AttrsLine <> '' then
            ResultList.Add(Format('%s F%s: %s;', [AttrsLine, FieldName, DelphiFieldType]))
          else
            ResultList.Add(Format('    F%s: %s;', [FieldName, DelphiFieldType]));
        finally
          AttributesList.Free;
        end;
      end;

      // Generate public property declarations
      ResultList.Add('  public');
      for i := 0 to FieldsList.Count - 1 do
      begin
        ColumnInfo := FieldsList[i];

        // Use QueryColumnAlias as the field name or the column name
        if ColumnInfo.QueryColumnAlias <> '' then
          FieldName := ColumnInfo.QueryColumnAlias
        else
          FieldName := ColumnInfo.ReferencedTableColumn;

        // Sanitize FieldName
        FieldName := CreateValidIdentifier(FieldName);

        // Create property name (could be same as FieldName)
        PropertyName := FieldName;

        ResultList.Add(Format('    property %s: %s read F%s write F%s;', [PropertyName, DelphiFieldType, FieldName, FieldName]));
      end;

      ResultList.Add('  end;');
      ResultList.Add('');
    finally
      Parser.Free;
    end;

    Result := ResultList.Text;

  finally
    ResultList.Free;
  end;
end;

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

Create the code snippets and examples in Delphi so that I can use the Custom Attributes described on the function GenerateDelphiClassFromQuery above,
specially for a given class generated by GenerateDelphiClassFromQuery, do the following tasks:
1) list, for each table, the RefColumn that are primary keys
2) Based on the generated class, generate an SQL Update text for each table found on the Custom Attribute