unit pixel_classes;

interface

uses
 System.SysUtils, Rtti, TypInfo;

{$TYPEINFO ON}

type
 TZString = type string;

type
  TNullValue = record
  end;

  TNullable<T{: record}> = record
  public
    Value: T;
    HasValue: Boolean;

    class operator Implicit(const AValue: T): TNullable<T>;
    class operator Implicit(const AValue: TNullable<T>): T;
    class operator Implicit(const AValue: TNullValue): TNullable<T>;
    class operator Explicit(const AValue: T): TNullable<T>;

    // add these...
    class operator Equal(const A: TNullable<T>; const B: TNullValue): Boolean;
    class operator NotEqual(const A: TNullable<T>; const B: TNullValue): Boolean;
  end;

var NullValue: TNullValue;

implementation

class operator TNullable<T>.Implicit(const AValue: T): TNullable<T>;
begin
  Result.Value := AValue;
  Result.HasValue := True;
end;

class operator TNullable<T>.Implicit(const AValue: TNullable<T>): T;
begin
  if AValue.HasValue then
    Result := AValue.Value
  else
    Result := Default(T); // or raise an exception
end;

class operator TNullable<T>.Implicit(const AValue: TNullValue): TNullable<T>;
begin
  Result.Value := Default(T);
  Result.HasValue := False;
end;

class operator TNullable<T>.Explicit(const AValue: T): TNullable<T>;
begin
  Result.Value := AValue;
  Result.HasValue := True;
end;

class operator TNullable<T>.Equal(const A: TNullable<T>; const B: TNullValue): Boolean;
begin
  Result := not A.HasValue;
end;

class operator TNullable<T>.NotEqual(const A: TNullable<T>; const B: TNullValue): Boolean;
begin
  Result := A.HasValue;
end;

end.

-----------

unit orm_utils;

interface

uses
  System.SysUtils,
  System.Classes,
  System.Generics.Collections,
  System.Generics.Defaults,
  System.Rtti,
  System.TypInfo,
  Data.DB,
  Uni,
  pixel_classes,
  orm_customattributes,
  Vcl.Controls,
  Vcl.Forms,
  Dialogs,
  Variants;

type
  TQryRecInfo = class;

  // Define the event type
  TPropertyChangedEvent = procedure(Sender: TQryRecInfo; const PropertyName: string) of object;

  EORMUtilsException = class(Exception);

  /// <summary>
  /// Represents the key used in the component-ORM mapping dictionary.
  /// Combines a reference to an ORM record with a specific field name.
  /// </summary>
  TORMKey = record
    ORMRec: TQryRecInfo;
    FieldName: string;
  end;

  /// <summary>
  /// Custom comparer for TORMKey to handle equality checks and hashing.
  /// </summary>
  TORMKeyComparer = class(TEqualityComparer<TORMKey>)
    function Equals(const Left, Right: TORMKey): Boolean; override;
    function GetHashCode(const Key: TORMKey): Integer; override;
  end;

  /// <summary>
  /// Manages the binding between UI components and ORM records.
  /// </summary>
  TORMManager = class
  private
    FComponentDict: TDictionary<TORMKey, TComponent>;
  public
    constructor Create;
    destructor Destroy; override;

    /// <summary>
    /// Registers a component with its corresponding ORM record and field name.
    /// </summary>
    procedure RegisterComponent(Component: TComponent; ORMRec: TQryRecInfo; const FieldName: string);

    /// <summary>
    /// Unregisters a component from ORM binding.
    /// </summary>
    procedure UnregisterComponent(Component: TComponent);

    /// <summary>
    /// Finds the component associated with a specific ORM record and field name.
    /// </summary>
    function FindComponent(ORMRec: TQryRecInfo; const FieldName: string): TComponent;

    /// <summary>
    /// Retrieves all registered components.
    /// </summary>
    function GetAllComponents: TArray<TComponent>;

    /// <summary>
    /// Updates a component's UI based on the latest ORM record data.
    /// </summary>
    procedure UpdateComponentFromORM(Component: TComponent; ORMRec: TQryRecInfo; const FieldName: string);

    /// <summary>
    /// Retrieves the ORM record associated with a given component.
    /// </summary>
    function GetORMRecForComponent(Component: TComponent): TQryRecInfo;

    /// <summary>
    /// Retrieves the field name associated with a given component.
    /// </summary>
    function GetFieldNameForComponent(Component: TComponent): string;

    /// <summary>
    /// Registers components based on their Hint property.
    /// </summary>
    procedure RegisterComponentsByHint(MyForm: TForm; ORMRec: TQryRecInfo);
  end;

  /// <summary>
  /// Helper methods extending TComponent to interface with ORMManager.
  /// </summary>
  TComponentHelper = class helper for TComponent
  public
    /// <summary>
    /// Retrieves the ORM record associated with the component.
    /// </summary>
    function GetORMRec: TQryRecInfo;

    /// <summary>
    /// Retrieves the field name associated with the component.
    /// </summary>
    function GetORMRecField: string;
  end;

  /// <summary>
  /// Base class for query record information
  /// Use descendants of this class to represent specific database records.
  /// </summary>
  TQryRecInfo = class
  private
    FDirtyFields: TDictionary<string, Boolean>;
    FPreviousValues: TDictionary<string, TValue>;
    FOnPropertyChanged: TPropertyChangedEvent; // New event
    FIsNewRec: Boolean; // New field

    procedure SetInfo(Index: Integer; const Value: TValue);
    // Other SetInfo overloads...

  public
    constructor Create;
    destructor Destroy; override;

    // Event property
    property OnPropertyChanged: TPropertyChangedEvent read FOnPropertyChanged write FOnPropertyChanged;

    // New property
    property IsNewRec: Boolean read FIsNewRec;

    // Methods to set nullable fields
    procedure SetInfoNullableString(Index: Integer; const Value: TNullable<string>);
    procedure SetInfoNullableInteger(Index: Integer; const Value: TNullable<Integer>);
    procedure SetInfoNullableInt64(Index: Integer; const Value: TNullable<Int64>);
    procedure SetInfoNullableExtended(Index: Integer; const Value: TNullable<Extended>);
    procedure SetInfoNullableCurrency(Index: Integer; const Value: TNullable<Currency>);
    procedure SetInfoNullableDateTime(Index: Integer; const Value: TNullable<TDateTime>);
    procedure SetInfoNullableBoolean(Index: Integer; const Value: TNullable<Boolean>);
    procedure SetInfoNullableVariant(Index: Integer; const Value: TNullable<Variant>);

    // Methods to set non-nullable simple types
    procedure SetInfoCurrency(Index: Integer; const Value: Currency);
    procedure SetInfoString(Index: Integer; const Value: string);
    procedure SetInfoInteger(Index: Integer; const Value: Integer);
    procedure SetInfoInt64(Index: Integer; const Value: Int64);
    procedure SetInfoExtended(Index: Integer; const Value: Extended);
    procedure SetInfoDateTime(Index: Integer; const Value: TDateTime);
    procedure SetInfoBoolean(Index: Integer; const Value: Boolean);
    procedure SetInfoVariant(Index: Integer; const Value: Variant);

    // Methods to handle dirty fields
    function IsFieldDirty(const FieldName: string): Boolean;
    procedure ClearDirtyFlags;
    property DirtyFields: TDictionary<string, Boolean> read FDirtyFields;

    // Methods to handle previous values
    function GetPreviousValue(const FieldName: string): TValue;
    procedure ClearPreviousValues;
    property PreviousValues: TDictionary<string, TValue> read FPreviousValues;

    /// <summary>
    /// Retrieves the formatted text for a given field based on the FmtProp attribute.
    /// </summary>
    function GetFormattedText(const FieldName: string): string;

    /// <summary>
    /// Fetches data for this record from the database based on the provided SQL.
    /// </summary>
    procedure Fetch(DBConn: TUniConnection; const QryTxt: string);

    /// <summary>
    /// Retrieves the value of a property by name.
    /// </summary>
    /// <param name="PropertyName">The name of the property to retrieve.</param>
    /// <returns>The value of the specified property as TValue.</returns>
    function GetPropertyValue(const PropertyName: string): TValue; // New method
  end;

  /// <summary>
  /// Generic class to handle arrays of TQryRecInfo descendants.
  /// </summary>
  TQryRecArray<T: TQryRecInfo, constructor> = class
  private
    FQryRecInfoList: TObjectList<T>;
    FFilterSQL: string;
    FDBConn: TUniConnection;
    function GetCount: Integer;
  public
    constructor Create;
    destructor Destroy; override;

    /// <summary>
    /// Fetches all records based on the provided SQL text and populates the array.
    /// </summary>
    /// <param name="QryTxt">The base SQL query text.</param>
    procedure FetchAllInstance(DBConn: TUniConnection; const QryTxt: string);

    /// <summary>
    /// Clears any SQL filters applied.
    /// </summary>
    procedure ClearFilter;

    /// <summary>
    /// Accesses a record at the specified index.
    /// </summary>
    /// <param name="Index">The zero-based index of the record.</param>
    /// <returns>The record at the specified index.</returns>
    function Rec(Index: Integer): T;

    /// <summary>
    /// Number of records in the array.
    /// </summary>
    property RecCount: Integer read GetCount;

    /// <summary>
    /// Database connection used for fetching records.
    /// </summary>
    property DBConn: TUniConnection read FDBConn write FDBConn;

    /// <summary>
    /// Additional SQL filter to apply to the query.
    /// </summary>
    property SQLFilter: string read FFilterSQL write FFilterSQL;

    /// <summary>
    /// Provides support for for-in loops by returning an enumerator.
    /// </summary>
    function GetEnumerator: TEnumerator<T>;

    /// <summary>
    /// Factory method to create and fetch records in a single step.
    /// </summary>
    /// <param name="DBConn">Database connection.</param>
    /// <param name="QryTxt">SQL query text.</param>
    /// <returns>Populated instance of TQryRecArray<T>.</returns>
    class function FetchAll(DBConn: TUniConnection; const QryTxt: string): TQryRecArray<T>; static;
  end;

// Declare ORMManager as a global variable in the interface for accessibility
var
  ORMManager: TORMManager;

implementation

uses
  pixel_utils,
  System.Math,
  System.Types,
  Windows,
  Vcl.StdCtrls,
  Vcl.WinXCtrls,
  Vcl.WinXPickers,
  StrUtils,
  DateUtils,
  System.Hash;

// =============================
// TORMKeyComparer Implementation
// =============================

function TORMKeyComparer.Equals(const Left, Right: TORMKey): Boolean;
begin
  Result := (Left.ORMRec = Right.ORMRec) and SameText(Left.FieldName, Right.FieldName);
end;

function TORMKeyComparer.GetHashCode(const Key: TORMKey): Integer;
begin
  Result := Integer(NativeUInt(Key.ORMRec)) xor THashBobJenkins.GetHashValue(LowerCase(Key.FieldName));
end;

// =============================
// TORMManager Implementation
// =============================

constructor TORMManager.Create;
begin
  inherited Create;
  FComponentDict := TDictionary<TORMKey, TComponent>.Create(TORMKeyComparer.Create);
end;

destructor TORMManager.Destroy;
begin
  FComponentDict.Free;
  inherited;
end;

procedure TORMManager.RegisterComponent(Component: TComponent; ORMRec: TQryRecInfo; const FieldName: string);
var
  Key: TORMKey;
begin
  Key.ORMRec := ORMRec;
  Key.FieldName := FieldName;

  if not FComponentDict.ContainsKey(Key) then
  begin
    FComponentDict.Add(Key, Component);
  end;
end;

procedure TORMManager.UnregisterComponent(Component: TComponent);
var
  Key: TORMKey;
  CopyKeys: TArray<TORMKey>;
  i: Integer;
begin
  // To safely remove components, first copy the keys
  CopyKeys := FComponentDict.Keys.ToArray;
  for i := 0 to Length(CopyKeys) - 1 do
  begin
    Key := CopyKeys[i];
    if FComponentDict[Key] = Component then
      FComponentDict.Remove(Key);
  end;
end;

function TORMManager.FindComponent(ORMRec: TQryRecInfo; const FieldName: string): TComponent;
var
  Key: TORMKey;
begin
  Key.ORMRec := ORMRec;
  Key.FieldName := FieldName;
  if FComponentDict.TryGetValue(Key, Result) then
    Exit
  else
    Result := nil;
end;

function TORMManager.GetAllComponents: TArray<TComponent>;
var
  Comp: TComponent;
  CompList: TList<TComponent>;
begin
  CompList := TList<TComponent>.Create;
  try
    for Comp in FComponentDict.Values do
      CompList.Add(Comp);
    Result := CompList.ToArray;
  finally
    CompList.Free;
  end;
end;

// Helper function to format information based on type
function FormataInfo(info: Variant; tipo: TFormatType): string;
begin
  if VarIsNull(info) or VarIsEmpty(info) then
    Exit('');

  case tipo of
    tiString:
      Result := VarToStr(info);
    tiInteger:
      Result := IntToStr(Integer(info));
    tiMoney:
      Result := FormatFloat(FMT_MONEY, Currency(info));
    tiFloat:
      Result := FloatToStr(info);
    tiDate:
      if VarIsType(info, [varDate, varDouble]) then
      begin
        if VarToDateTime(info) < EncodeDate(1900, 1, 1) then
          Result := ''
        else
          Result := FormatDateTime('dd/mm/yyyy', VarToDateTime(info));
      end
      else
        raise EORMUtilsException.CreateFmt('FormataInfo: Cannot treat information [%s] as tiDate.', [VarToStr(info)]);
    tiDateTime:
      if VarIsType(info, [varDate, varDouble]) then
      begin
        if VarToDateTime(info) = 0 then
          Result := ''
        else
          Result := FormatDateTime('dd/mm/yyyy hh:mm:ss', VarToDateTime(info));
      end
      else
        raise EORMUtilsException.CreateFmt('FormataInfo: Cannot treat information [%s] as tiDateTime.', [VarToStr(info)]);
    tiBoolean:
      Result := IfThen(Boolean(info), 'S', 'N');
    tiByteArray:
      Result := ByteArrayToHex(info);
    tiNone:
      Result := '';
  else
    raise EORMUtilsException.CreateFmt('FormataInfo: Format type [%s] not recognized.', [GetEnumName(TypeInfo(TFormatType), Integer(tipo))]);
  end;
end;

procedure SetValueToComponent(aComponent: TComponent; TipoProp: TFormatType; Value: Variant);
begin
 if not Assigned(aComponent) then Exit;

 if TipoProp in [tiString, tiInteger, tiMoney, tiFloat, tiByteArray] then
 begin
  if IsPublishedProp(aComponent, 'Text') then SetPropValue(aComponent, 'Text', FormataInfo(Value, TipoProp))
  else if IsPublishedProp(aComponent, 'Caption') then SetPropValue(aComponent, 'Caption', FormataInfo(Value, TipoProp))
  else if IsPublishedProp(aComponent, 'Lines') then
  begin
   var Ctx: TRttiContext;
   Ctx:= TRttiContext.Create;
   var
   LineObjProp:= Ctx.GetType(aComponent.ClassType).GetProperty('Lines');
   var
   Strings_Obj:= TStrings(LineObjProp.GetValue(aComponent).AsObject);
   Strings_Obj.Text:= FormataInfo(Value, TipoProp);
   // LineObjProp.SetValue(aComponent, Strings_Obj);
  end;

 end
 else if TipoProp in [tiDate, tiDateTime] then
 begin
  if IsPublishedProp(aComponent, 'Date') then
  begin
   if (DateOf(Value) < EncodeDate(1900, 1, 1)) and (not IsPublishedProp(aComponent, 'Text')) then
   begin
    SetPropValue(aComponent, 'Date', DateOf(Now));
    var Ctx: TRttiContext;
    Ctx:= TRttiContext.Create;
    Ctx.GetType(aComponent.ClassType).GetMethod('Clear').Invoke(aComponent, []); // Limpa o campo Date
   end
   else SetPropValue(aComponent, 'Date', Value);
  end
  else if IsPublishedProp(aComponent, 'Text') then SetPropValue(aComponent, 'Text', FormataInfo(Value, TipoProp))
  else if IsPublishedProp(aComponent, 'Caption') then SetPropValue(aComponent, 'Caption', FormataInfo(Value, TipoProp));
 end
 else if TipoProp = tiBoolean then
 begin
  if IsPublishedProp(aComponent, 'Checked') then SetPropValue(aComponent, 'Checked', Boolean(Value))
  else if IsPublishedProp(aComponent, 'Text') then SetPropValue(aComponent, 'Text', FormataInfo(Value, TipoProp))
  else if IsPublishedProp(aComponent, 'Caption') then SetPropValue(aComponent, 'Caption', FormataInfo(Value, TipoProp));
 end
 else raise Exception.Create('SetValueToComponent: o Componente [' + aComponent.Name + ': ' + aComponent.ClassName + '] não está tratado');
end;

procedure TORMManager.UpdateComponentFromORM(Component: TComponent; ORMRec: TQryRecInfo; const FieldName: string);
var
  Ctx: TRttiContext;
  RttiType: TRttiType;
  Prop: TRttiProperty;
  Attr: TCustomAttribute;
  FmtAttr: FmtPropAttribute;
begin
  var ORMValue:= ORMRec.GetPropertyValue(FieldName).AsVariant;

  Ctx := TRttiContext.Create;

    RttiType := Ctx.GetType(ORMRec.ClassType);
    Prop := RttiType.GetProperty(FieldName);
    if not Assigned(Prop) then
      raise EORMUtilsException.CreateFmt('Property "%s" not found.', [FieldName]);

  var FmtType := tiNone;

    // Check for FmtProp attribute on the property
    for Attr in Prop.GetAttributes do
    begin
      if Attr is FmtPropAttribute then
      begin
        FmtAttr := FmtPropAttribute(Attr);
        FmtType := FmtAttr.FormatType;
        Break;
      end;
    end;

    // If not found on property, check on the corresponding field
    if FmtType = tiNone then
    begin
      var Field := RttiType.GetField('_' + FieldName);
      if Assigned(Field) then
      begin
        for Attr in Field.GetAttributes do
        begin
          if Attr is FmtPropAttribute then
          begin
            FmtAttr := FmtPropAttribute(Attr);
            FmtType := FmtAttr.FormatType;
            Break;
          end;
        end;
      end;
    end;

 // Determine default FmtType based on property type if not set by attribute
    if FmtType = tiNone then
    begin
     var UnderlyingType := Prop.PropertyType;

      // Handle TNullable<T>
      if UnderlyingType.TypeKind = tkRecord then
      begin
        var ValueField := UnderlyingType.GetField('Value');
        if Assigned(ValueField) then
          UnderlyingType := ValueField.FieldType;
      end;

      case UnderlyingType.TypeKind of
        tkInteger, tkInt64:
          FmtType := tiInteger;
        tkFloat:
          if UnderlyingType.Handle = TypeInfo(TDateTime) then
            FmtType := tiDateTime
          else
            FmtType := tiFloat;
        tkString, tkLString, tkWString, tkUString:
          FmtType := tiString;
        tkEnumeration:
          if UnderlyingType.Handle = TypeInfo(Boolean) then
            FmtType := tiBoolean
          else
            FmtType := tiString;
        tkVariant:
          FmtType := tiString;
      else
        FmtType := tiNone;
      end;
    end;

  // showmessage(GetEnumName(TypeInfo(TFormatType), Integer(FmtType)));

  if not VarIsNull(ORMValue) then
   begin
    if IsPublishedProp(Component, 'Date') then
    begin
     if TDateTime(ORMValue) >= EncodeDate(1900, 1, 1) then SetValueToComponent(Component, FmtType, ORMValue)
     else SetValueToComponent(Component, FmtType, EncodeDate(1899, 01, 01));
    end
    else SetValueToComponent(Component, FmtType, ORMValue);
   end
   else
   begin
    SetValueToComponent(Component, FmtType, ORMValue.NullToDefault);
    if IsPublishedProp(Component, 'Date') then
    begin
     if IsPublishedProp(Component, 'Text') then
     begin
      var Ctx2: TRttiContext;
      Ctx2:= TRttiContext.Create;
      Ctx2.GetType(Component.ClassType).GetProperty('Text').SetValue(Pointer(Component), TValue.FromVariant('')); // Limpa o campo Text
     end
     else
     begin
      var Ctx2: TRttiContext;
      Ctx2:= TRttiContext.Create;
      Ctx2.GetType(Component.ClassType).GetMethod('Clear').Invoke(Component, []); // Limpa o campo Date
     end;
    end;
   end;
end;

function TORMManager.GetORMRecForComponent(Component: TComponent): TQryRecInfo;
var
  Key: TORMKey;
begin
  Result := nil;
  for Key in FComponentDict.Keys do
    if FComponentDict[Key] = Component then
    begin
      Result := Key.ORMRec;
      Exit;
    end;
end;

function TORMManager.GetFieldNameForComponent(Component: TComponent): string;
var
  Key: TORMKey;
begin
  Result := '';
  for Key in FComponentDict.Keys do
    if FComponentDict[Key] = Component then
    begin
      Result := Key.FieldName;
      Exit;
    end;
end;

procedure TORMManager.RegisterComponentsByHint(MyForm: TForm; ORMRec: TQryRecInfo);
var
  Context: TRttiContext;
  RttiType: TRttiType;
  RttiProperty: TRttiProperty;
  Component: TComponent;
  HintValue: TValue;
begin
  Context := TRttiContext.Create;
  try
    for Component in MyForm do
    begin
      RttiType := Context.GetType(Component.ClassType);
      RttiProperty := RttiType.GetProperty('Hint');

      if Assigned(RttiProperty) then
      begin
        HintValue := RttiProperty.GetValue(Component);
        if HintValue.IsEmpty then
          Continue;

        RttiType := Context.GetType(ORMRec.ClassType);
        for RttiProperty in RttiType.GetProperties do
        begin
          if (RttiProperty.Visibility = mvPublished) then
          begin
            if SameText(HintValue.AsString, RttiProperty.Name) then
            begin
              RegisterComponent(Component, ORMRec, RttiProperty.Name);
              Break;
            end;
          end;
        end;
      end;
    end;
  finally
    Context.Free;
  end;
end;

// =============================
// TComponentHelper Implementation
// =============================

function TComponentHelper.GetORMRec: TQryRecInfo;
begin
  Result := ORMManager.GetORMRecForComponent(Self);
end;

function TComponentHelper.GetORMRecField: string;
begin
  Result := ORMManager.GetFieldNameForComponent(Self);
end;

// =============================
// TQryRecInfo Implementation
// =============================

constructor TQryRecInfo.Create;
begin
  inherited Create;
  FDirtyFields := TDictionary<string, Boolean>.Create;
  FPreviousValues := TDictionary<string, TValue>.Create;
  FIsNewRec := False; // Initialize as not new
end;

destructor TQryRecInfo.Destroy;
begin
  FDirtyFields.Free;
  FPreviousValues.Free;
  inherited;
end;

procedure TQryRecInfo.ClearDirtyFlags;
begin
  FDirtyFields.Clear;
  FIsNewRec := False; // Reset IsNewRec when clearing dirty flags
end;

procedure TQryRecInfo.ClearPreviousValues;
begin
  FPreviousValues.Clear;
end;

function TQryRecInfo.IsFieldDirty(const FieldName: string): Boolean;
begin
  Result := FDirtyFields.ContainsKey(FieldName) and FDirtyFields[FieldName];
end;

function TQryRecInfo.GetPreviousValue(const FieldName: string): TValue;
begin
  if not FPreviousValues.TryGetValue(FieldName, Result) then
    raise EORMUtilsException.CreateFmt('No previous value found for field "%s".', [FieldName]);
end;

function TQryRecInfo.GetFormattedText(const FieldName: string): string;
var
  Ctx: TRttiContext;
  RttiType: TRttiType;
  Prop: TRttiProperty;
  Field: TRttiField;
  Value: TValue;
  FmtType: TFormatType;
  UnderlyingType: TRttiType;
  HasValueField: TRttiField;
  ValueField: TRttiField;
  HasValue: Boolean;
  ActualValue: TValue;
  Attr: TCustomAttribute;
  FmtAttr: FmtPropAttribute;
begin
  Result := '';
  Ctx := TRttiContext.Create;
  try
    RttiType := Ctx.GetType(Self.ClassType);

    // Find the property by name
    Prop := RttiType.GetProperty(FieldName);
    if not Assigned(Prop) then
      raise EORMUtilsException.CreateFmt('Property "%s" not found.', [FieldName]);

    // Get the value of the property
    Value := Prop.GetValue(Self);
    FmtType := tiNone;

    // Check for FmtProp attribute on the property
    for Attr in Prop.GetAttributes do
    begin
      if Attr is FmtPropAttribute then
      begin
        FmtAttr := FmtPropAttribute(Attr);
        FmtType := FmtAttr.FormatType;
        Break;
      end;
    end;

    // If not found on property, check on the corresponding field
    if FmtType = tiNone then
    begin
      Field := RttiType.GetField('_' + FieldName);
      if Assigned(Field) then
      begin
        for Attr in Field.GetAttributes do
        begin
          if Attr is FmtPropAttribute then
          begin
            FmtAttr := FmtPropAttribute(Attr);
            FmtType := FmtAttr.FormatType;
            Break;
          end;
        end;
      end;
    end;

    // Determine default FmtType based on property type if not set by attribute
    if FmtType = tiNone then
    begin
      UnderlyingType := Prop.PropertyType;

      // Handle TNullable<T>
      if UnderlyingType.TypeKind = tkRecord then
      begin
        ValueField := UnderlyingType.GetField('Value');
        if Assigned(ValueField) then
          UnderlyingType := ValueField.FieldType;
      end;

      case UnderlyingType.TypeKind of
        tkInteger, tkInt64:
          FmtType := tiInteger;
        tkFloat:
          if UnderlyingType.Handle = TypeInfo(TDateTime) then
            FmtType := tiDateTime
          else
            FmtType := tiFloat;
        tkString, tkLString, tkWString, tkUString:
          FmtType := tiString;
        tkEnumeration:
          if UnderlyingType.Handle = TypeInfo(Boolean) then
            FmtType := tiBoolean
          else
            FmtType := tiString;
        tkVariant:
          FmtType := tiString;
      else
        FmtType := tiNone;
      end;
    end;

    // Extract actual value from TNullable<T> if necessary
    if Value.Kind = tkRecord then
    begin
      // Assuming TNullable<T> has 'HasValue' and 'Value' fields
      UnderlyingType := Prop.PropertyType;
      HasValueField := UnderlyingType.GetField('HasValue');
      ValueField := UnderlyingType.GetField('Value');

      if Assigned(HasValueField) and Assigned(ValueField) then
      begin
        HasValue := HasValueField.GetValue(Value.GetReferenceToRawData).AsBoolean;
        if HasValue then
        begin
          ActualValue := ValueField.GetValue(Value.GetReferenceToRawData);
          // Format the actual value
          Result := FormataInfo(ActualValue.AsVariant, FmtType);
        end
        else
          Result := '';
      end
      else
      begin
        // Not TNullable<T>, handle value directly
        if not Value.IsEmpty then
          Result := FormataInfo(Value.AsVariant, FmtType)
        else
          Result := '';
      end;
    end
    else
    begin
      // Not a record, handle value directly
      if not Value.IsEmpty then
        Result := FormataInfo(Value.AsVariant, FmtType)
      else
        Result := '';
    end;
  finally
    // No need to free TRttiContext as it's a record
  end;
end;

procedure TQryRecInfo.Fetch(DBConn: TUniConnection; const QryTxt: string);
var
  qr: TUniQuery;
  Ctx: TRttiContext;
  ObjType: TRttiType;
  Prop: TRttiProperty;
  Field: TField;
  ORMFieldName: string;
begin
  if not Assigned(DBConn) or Trim(QryTxt).IsEmpty then
    Exit;

  qr := TUniQuery.Create(nil);
  try
    qr.Connection := DBConn;
    qr.SpecificOptions.Values['FetchAll'] := 'True';
    qr.SpecificOptions.Values['CreateConnection'] := 'False';
    qr.SQL.Text := QryTxt;
    qr.Open;

    if qr.RecordCount > 0 then
    begin
      Ctx := TRttiContext.Create;
      try
        ObjType := Ctx.GetType(Self.ClassType);

        for Prop in ObjType.GetProperties do
        begin
          if not Prop.IsWritable then
            Continue;

          // Attempt to find the corresponding field in the query
          Field := qr.FindField(Prop.Name);
          if not Assigned(Field) then
            Continue; // Field not found, skip

          ORMFieldName := '_' + Prop.Name;
          // Handle TNullable<T> types
          if Prop.PropertyType.TypeKind = tkRecord then
          begin
            // Expecting TNullable<T>
            // Attempt to find the backing field for the nullable property
            if ObjType.GetField(ORMFieldName) = nil then
              Continue; // Backing field not found, skip

            if Prop.PropertyType.Name.StartsWith('TNullable<') then
            begin
              // Use RTTI to determine the generic type parameter
              if Field.IsNull then
                ObjType.GetField(ORMFieldName).SetValue(Self, TValue.From<TNullable<string>>(NullValue))
              else
              begin
                if Prop.PropertyType.Name = 'TNullable<System.string>' then
                  ObjType.GetField(ORMFieldName).SetValue(Self, TValue.From<TNullable<string>>(Field.AsString))
                else if Prop.PropertyType.Name = 'TNullable<System.Integer>' then
                  ObjType.GetField(ORMFieldName).SetValue(Self, TValue.From<TNullable<Integer>>(Field.AsInteger))
                else if Prop.PropertyType.Name = 'TNullable<System.Int64>' then
                  ObjType.GetField(ORMFieldName).SetValue(Self, TValue.From<TNullable<Int64>>(Field.AsLargeInt))
                else if Prop.PropertyType.Name = 'TNullable<System.Extended>' then
                  ObjType.GetField(ORMFieldName).SetValue(Self, TValue.From<TNullable<Extended>>(Field.AsFloat))
                else if Prop.PropertyType.Name = 'TNullable<System.Currency>' then
                  ObjType.GetField(ORMFieldName).SetValue(Self, TValue.From<TNullable<Currency>>(Field.AsCurrency))
                else if Prop.PropertyType.Name = 'TNullable<System.TDateTime>' then
                  ObjType.GetField(ORMFieldName).SetValue(Self, TValue.From<TNullable<TDateTime>>(Field.AsDateTime))
                else if Prop.PropertyType.Name = 'TNullable<System.Boolean>' then
                  ObjType.GetField(ORMFieldName).SetValue(Self, TValue.From<TNullable<Boolean>>(Field.AsBoolean))
                else if Prop.PropertyType.Name = 'TNullable<System.Variant>' then
                  ObjType.GetField(ORMFieldName).SetValue(Self, TValue.From<TNullable<Variant>>(Field.Value))
                else
                  raise EORMUtilsException.CreateFmt('Unsupported TNullable type: %s', [Prop.PropertyType.Name]);
              end;
            end
            else
              raise EORMUtilsException.CreateFmt('Unsupported record type: %s', [Prop.PropertyType.Name]);
          end
          else
          begin
            // Handle non-nullable types
            case Prop.PropertyType.TypeKind of
              tkString, tkLString, tkWString, tkUString:
                ObjType.GetField(ORMFieldName).SetValue(Self, Field.AsString);
              tkInteger:
                ObjType.GetField(ORMFieldName).SetValue(Self, Field.AsInteger);
              tkInt64:
                ObjType.GetField(ORMFieldName).SetValue(Self, Field.AsLargeInt);
              tkFloat:
                if Prop.PropertyType.Handle = TypeInfo(Currency) then
                  ObjType.GetField(ORMFieldName).SetValue(Self, Field.AsCurrency)
                else
                  ObjType.GetField(ORMFieldName).SetValue(Self, Field.AsFloat);
              tkVariant:
                ObjType.GetField(ORMFieldName).SetValue(Self, TValue.FromVariant(Field.AsVariant));
              tkEnumeration:
                if Prop.PropertyType.Handle = TypeInfo(Boolean) then
                  ObjType.GetField(ORMFieldName).SetValue(Self, Field.AsBoolean)
                else
                  raise EORMUtilsException.CreateFmt('Unsupported enumeration type: %s', [Prop.PropertyType.Name]);
            else
              // Add additional handling for other types if necessary
            end;
          end;
        end;

        // After fetching, reset dirty flags and IsNewRec
        ClearDirtyFlags;
      finally
        // No need to free TRttiContext as it's a record
      end;
    end;
  finally
    qr.Free;
  end;
end;

procedure TQryRecInfo.SetInfo(Index: Integer; const Value: TValue);
var
  PropName: string;
  PropKind: TTypeKind;
  Ctx: TRttiContext;
  Prop: TRttiProperty;
  Field: TRttiField;
  OldValue: TValue;
begin
  // Retrieve property name and kind based on index
  // Assuming that the index corresponds to the property order
  Ctx := TRttiContext.Create;
  try
    Prop := Ctx.GetType(Self.ClassType).GetProperties()[Index];
    if not Assigned(Prop) then
      raise EORMUtilsException.CreateFmt('Error: The index "%d" does not correspond to any property in class "%s".', [Index, ClassName]);

    PropName := Prop.Name;
    PropKind := Prop.PropertyType.TypeKind;

    Field := Ctx.GetType(Self.ClassType).GetField('_' + PropName);
    if not Assigned(Field) then
      raise EORMUtilsException.CreateFmt('Error: Field "_%s" not found in class "%s".', [PropName, ClassName]);

    // If the field is not yet dirty, store the previous value
    if not FDirtyFields.ContainsKey(PropName) then
    begin
      OldValue := Field.GetValue(Self);
      FPreviousValues.AddOrSetValue(PropName, OldValue);
      // Since a field is being modified, mark IsNewRec as true
      FIsNewRec := True;
    end;

    // Set the new value
    Field.SetValue(Self, Value);

    // Mark the property as dirty
    FDirtyFields.AddOrSetValue(PropName, True);

    // Trigger the OnPropertyChanged event
    if Assigned(FOnPropertyChanged) then
      FOnPropertyChanged(Self, PropName);

    // Update corresponding component if any
    var UpdatedComp := ORMManager.FindComponent(Self, PropName);
    if Assigned(UpdatedComp) then
      ORMManager.UpdateComponentFromORM(UpdatedComp, ORMManager.GetORMRecForComponent(UpdatedComp), ORMManager.GetFieldNameForComponent(UpdatedComp));

  finally
    // No need to free TRttiContext as it's a record
  end;
end;

procedure TQryRecInfo.SetInfoCurrency(Index: Integer; const Value: Currency);
begin
  SetInfo(Index, TValue.From<Currency>(Value));
end;

procedure TQryRecInfo.SetInfoString(Index: Integer; const Value: string);
begin
  SetInfo(Index, TValue.From<string>(Value));
end;

procedure TQryRecInfo.SetInfoInteger(Index: Integer; const Value: Integer);
begin
  SetInfo(Index, TValue.From<Integer>(Value));
end;

procedure TQryRecInfo.SetInfoInt64(Index: Integer; const Value: Int64);
begin
  SetInfo(Index, TValue.From<Int64>(Value));
end;

procedure TQryRecInfo.SetInfoExtended(Index: Integer; const Value: Extended);
begin
  SetInfo(Index, TValue.From<Extended>(Value));
end;

procedure TQryRecInfo.SetInfoDateTime(Index: Integer; const Value: TDateTime);
begin
  SetInfo(Index, TValue.From<TDateTime>(Value));
end;

procedure TQryRecInfo.SetInfoBoolean(Index: Integer; const Value: Boolean);
begin
  SetInfo(Index, TValue.From<Boolean>(Value));
end;

procedure TQryRecInfo.SetInfoVariant(Index: Integer; const Value: Variant);
begin
  SetInfo(Index, TValue.From<Variant>(Value));
end;

procedure TQryRecInfo.SetInfoNullableBoolean(Index: Integer; const Value: TNullable<Boolean>);
begin
  SetInfo(Index, TValue.From<TNullable<Boolean>>(Value));
end;

procedure TQryRecInfo.SetInfoNullableCurrency(Index: Integer; const Value: TNullable<Currency>);
begin
  SetInfo(Index, TValue.From<TNullable<Currency>>(Value));
end;

procedure TQryRecInfo.SetInfoNullableDateTime(Index: Integer; const Value: TNullable<TDateTime>);
begin
  SetInfo(Index, TValue.From<TNullable<TDateTime>>(Value));
end;

procedure TQryRecInfo.SetInfoNullableExtended(Index: Integer; const Value: TNullable<Extended>);
begin
  SetInfo(Index, TValue.From<TNullable<Extended>>(Value));
end;

procedure TQryRecInfo.SetInfoNullableInteger(Index: Integer; const Value: TNullable<Integer>);
begin
  SetInfo(Index, TValue.From<TNullable<Integer>>(Value));
end;

procedure TQryRecInfo.SetInfoNullableInt64(Index: Integer; const Value: TNullable<Int64>);
begin
  SetInfo(Index, TValue.From<TNullable<Int64>>(Value));
end;

procedure TQryRecInfo.SetInfoNullableString(Index: Integer; const Value: TNullable<string>);
begin
  SetInfo(Index, TValue.From<TNullable<string>>(Value));
end;

procedure TQryRecInfo.SetInfoNullableVariant(Index: Integer; const Value: TNullable<Variant>);
begin
  SetInfo(Index, TValue.From<TNullable<Variant>>(Value));
end;

function TQryRecInfo.GetPropertyValue(const PropertyName: string): TValue;
var
  ctx: TRttiContext;
  rttiType: TRttiType;
  prop: TRttiProperty;
  UnderlyingType: TRttiType;
  Value: TValue;
  ValueField: TRttiField;
  HasValueField: TRttiField;
  HasValue: Boolean;
  ActualValue: TValue;
begin
  // Initialize the result
  Result := TValue.Empty;

  ctx := TRttiContext.Create;
  try
    rttiType := ctx.GetType(Self.ClassType);
    prop := rttiType.GetProperty(PropertyName);
    if not Assigned(prop) then
      raise EORMUtilsException.CreateFmt('Property "%s" not found in class "%s".', [PropertyName, Self.ClassName]);

    // Get the value of the property
    Value := prop.GetValue(Self);

    // Check if the property is a TNullable<T>
    if prop.PropertyType.TypeKind = tkRecord then
    begin
      // Assuming TNullable<T> has 'HasValue' and 'Value' fields
      ValueField := prop.PropertyType.GetField('Value');
      HasValueField := prop.PropertyType.GetField('HasValue');

      if Assigned(ValueField) and Assigned(HasValueField) then
      begin
        HasValue := HasValueField.GetValue(Value.GetReferenceToRawData).AsBoolean;
        if HasValue then
        begin
          ActualValue := ValueField.GetValue(Value.GetReferenceToRawData);
          Result := ActualValue;
        end
        else
        begin
          // Represent null value appropriately, e.g., using TValue.Empty or a custom indicator
          Result := TValue.Empty; // Or you can use a specific value or structure to indicate null
        end;
      end
      else
      begin
        // Not a TNullable<T>, return the value directly
        Result := Value;
      end;
    end
    else
    begin
      // Non-record types, return the value directly
      Result := Value;
    end;
  finally
    // TRttiContext is a record; no need to free
  end;
end;

// =============================
// TQryRecArray<T> Implementation
// =============================

constructor TQryRecArray<T>.Create;
begin
  inherited Create;
  FQryRecInfoList := TObjectList<T>.Create(True); // Owns objects
end;

destructor TQryRecArray<T>.Destroy;
begin
  FQryRecInfoList.Free;
  inherited;
end;

procedure TQryRecArray<T>.ClearFilter;
begin
  FFilterSQL := '';
end;

function TQryRecArray<T>.GetCount: Integer;
begin
  Result := FQryRecInfoList.Count;
end;

function TQryRecArray<T>.Rec(Index: Integer): T;
begin
  if (Index < 0) or (Index >= FQryRecInfoList.Count) then
    raise EORMUtilsException.CreateFmt('Index %d out of bounds.', [Index]);

  Result := FQryRecInfoList[Index];
end;

procedure TQryRecArray<T>.FetchAllInstance(DBConn: TUniConnection; const QryTxt: string);
var
  qr: TUniQuery;
  RecInstance: T;
  Ctx: TRttiContext;
  ObjType: TRttiType;
  Prop: TRttiProperty;
  Field: TField;
  ORMFieldName: string;
begin
  if not Assigned(DBConn) then
    raise EORMUtilsException.Create('Database connection (DBConn) is not assigned.');

  if Trim(QryTxt).IsEmpty then
    raise EORMUtilsException.Create('Query text (QryTxt) cannot be empty.');

  qr := TUniQuery.Create(nil);
  try
    qr.Connection := DBConn;
    qr.SpecificOptions.Values['FetchAll'] := 'True';
    qr.SpecificOptions.Values['CreateConnection'] := 'False';
    qr.SQL.Text := QryTxt;
    qr.Open;

    if qr.IsEmpty then
      Exit; // No records to fetch

    Ctx := TRttiContext.Create;
    ObjType := Ctx.GetType(T);

    try
      while not qr.Eof do
      begin
        RecInstance := T.Create;
        try
          // Iterate over each property of T and assign values from the query
          for Prop in ObjType.GetProperties do
          begin
            if not Prop.IsWritable then
              Continue;

            // Attempt to find the corresponding field in the query
            Field := qr.FindField(Prop.Name);
            if not Assigned(Field) then
              Continue; // Field not found, skip

            ORMFieldName := '_' + Prop.Name;

            // Handle TNullable<T> types
            if Prop.PropertyType.TypeKind = tkRecord then
            begin
              // Expecting TNullable<T>
              // Attempt to find the backing field for the nullable property
              if ObjType.GetField(ORMFieldName) = nil then
                Continue; // Backing field not found, skip

              if Prop.PropertyType.Name.StartsWith('TNullable<') then
              begin
                // Use RTTI to determine the generic type parameter
                if Field.IsNull then
                  ObjType.GetField(ORMFieldName).SetValue(Pointer(RecInstance), TValue.From<TNullable<string>>(NullValue))
                else
                begin
                  if Prop.PropertyType.Name = 'TNullable<System.string>' then
                    ObjType.GetField(ORMFieldName).SetValue(Pointer(RecInstance), TValue.From<TNullable<string>>(Field.AsString))
                  else if Prop.PropertyType.Name = 'TNullable<System.Integer>' then
                    ObjType.GetField(ORMFieldName).SetValue(Pointer(RecInstance), TValue.From<TNullable<Integer>>(Field.AsInteger))
                  else if Prop.PropertyType.Name = 'TNullable<System.Int64>' then
                    ObjType.GetField(ORMFieldName).SetValue(Pointer(RecInstance), TValue.From<TNullable<Int64>>(Field.AsLargeInt))
                  else if Prop.PropertyType.Name = 'TNullable<System.Extended>' then
                    ObjType.GetField(ORMFieldName).SetValue(Pointer(RecInstance), TValue.From<TNullable<Extended>>(Field.AsFloat))
                  else if Prop.PropertyType.Name = 'TNullable<System.Currency>' then
                    ObjType.GetField(ORMFieldName).SetValue(Pointer(RecInstance), TValue.From<TNullable<Currency>>(Field.AsCurrency))
                  else if Prop.PropertyType.Name = 'TNullable<System.TDateTime>' then
                    ObjType.GetField(ORMFieldName).SetValue(Pointer(RecInstance), TValue.From<TNullable<TDateTime>>(Field.AsDateTime))
                  else if Prop.PropertyType.Name = 'TNullable<System.Boolean>' then
                    ObjType.GetField(ORMFieldName).SetValue(Pointer(RecInstance), TValue.From<TNullable<Boolean>>(Field.AsBoolean))
                  else if Prop.PropertyType.Name = 'TNullable<System.Variant>' then
                    ObjType.GetField(ORMFieldName).SetValue(Pointer(RecInstance), TValue.From<TNullable<Variant>>(Field.Value))
                  else
                    raise EORMUtilsException.CreateFmt('Unsupported TNullable type: %s', [Prop.PropertyType.Name]);
                end;
              end
              else
                raise EORMUtilsException.CreateFmt('Unsupported record type: %s', [Prop.PropertyType.Name]);
            end
            else
            begin
              // Handle non-nullable types
              case Prop.PropertyType.TypeKind of
                tkString, tkLString, tkWString, tkUString:
                  ObjType.GetField(ORMFieldName).SetValue(Pointer(RecInstance), Field.AsString);
                tkInteger:
                  ObjType.GetField(ORMFieldName).SetValue(Pointer(RecInstance), Field.AsInteger);
                tkInt64:
                  ObjType.GetField(ORMFieldName).SetValue(Pointer(RecInstance), Field.AsLargeInt);
                tkFloat:
                  if Prop.PropertyType.Handle = TypeInfo(Currency) then
                    ObjType.GetField(ORMFieldName).SetValue(Pointer(RecInstance), Field.AsCurrency)
                  else if Prop.PropertyType.Handle = TypeInfo(TDateTime) then
                    ObjType.GetField(ORMFieldName).SetValue(Pointer(RecInstance), Field.AsDateTime)
                  else
                    ObjType.GetField(ORMFieldName).SetValue(Pointer(RecInstance), Field.AsFloat);
                tkVariant:
                  ObjType.GetField(ORMFieldName).SetValue(Pointer(RecInstance), TValue.FromVariant(Field.AsVariant));
                tkEnumeration:
                  if Prop.PropertyType.Handle = TypeInfo(Boolean) then
                    ObjType.GetField(ORMFieldName).SetValue(Pointer(RecInstance), Field.AsBoolean)
                  else
                    ObjType.GetField(ORMFieldName).SetValue(Pointer(RecInstance), Field.AsString);
              else
                // Unsupported type; you can extend this as needed
                Continue;
              end;
            end;
          end;

          // After setting properties, clear dirty flags and IsNewRec
          RecInstance.ClearDirtyFlags;

          // Add the populated record to the list
          FQryRecInfoList.Add(RecInstance);
        except
          RecInstance.Free;
          raise; // Re-raise the exception after cleanup
        end;
        qr.Next;
      end;
    finally
      // No need to free TRttiContext as it's a record
    end;
  finally
    qr.Free;
  end;
end;

function TQryRecArray<T>.GetEnumerator: TEnumerator<T>;
begin
  Result := FQryRecInfoList.GetEnumerator;
end;

class function TQryRecArray<T>.FetchAll(DBConn: TUniConnection; const QryTxt: string): TQryRecArray<T>;
var
  Instance: TQryRecArray<T>;
begin
  Instance := TQryRecArray<T>.Create;
  try
    Instance.FetchAllInstance(DBConn, QryTxt);
    Result := Instance;
  except
    Instance.Free;
    raise;
  end;
end;

// =============================
// Initialization and Finalization
// =============================

initialization
  ORMManager := TORMManager.Create;

finalization
  ORMManager.Free;

end.



-----------

unit orm_binding;

interface

uses
  System.Classes,
  System.Rtti,
  System.Generics.Collections,
  orm_utils,
  AdvCGrid,
  Variants,
  SysUtils,
  StrUtils,
  Dialogs,
  pixel_classes,
  interposer_class;

type
  /// <summary>
  /// Manages the binding between a TAdvColumnGrid and a TQryRecArray<TQryRecInfo> for bidirectional synchronization.
  /// </summary>
  TORMBinding = class
  private
    FGrid: TAdvColumnGrid;
    FData: TQryRecArray<TQryRecInfo>;
    FColumnFields: TDictionary<Integer, string>; // Maps grid column indices to ORM property names
    FColumnTypes: TDictionary<string, TRttiType>; // Maps ORM property names to their RTTI types
    FRowMapping: TDictionary<Integer, TQryRecInfo>; // Maps grid row indices to ORM records
    FCtx: TRttiContext; // RTTI context for dynamic property access

    /// <summary>
    /// Binds grid columns to ORM properties based on the ORMField property.
    /// </summary>
    procedure BindColumns;

    /// <summary>
    /// Populates the grid with data from TQryRecArray and maps each row to its corresponding ORM record.
    /// </summary>
    procedure PopulateAndMapGrid;

    /// <summary>
    /// Sets up event handlers for synchronizing changes between the grid and ORM data.
    /// </summary>
    procedure SetupEventHandlers;

    /// <summary>
    /// Event handler triggered when a grid cell edit is completed.
    /// Updates the corresponding ORM property.
    /// </summary>
    procedure GridCellChanged(Sender: TObject; ACol, ARow: Integer);

    /// <summary>
    /// Event handler triggered when an ORM property is changed.
    /// Updates the corresponding grid cell.
    /// </summary>
    procedure ORMPropertyChanged(Sender: TQryRecInfo; const PropertyName: string);

    /// <summary>
    /// Sets the ORM property based on the grid cell change.
    /// </summary>
    procedure SetORMProperty(ORMRec: TQryRecInfo; const FieldName: string; const Value: TValue);

    /// <summary>
    /// Updates a specific grid cell based on ORM changes.
    /// </summary>
    procedure UpdateGridCell(ACol: Integer; ARow: Integer);
  public
    /// <summary>
    /// Creates an instance of TORMBinding and initializes the binding between the grid and data array.
    /// </summary>
    constructor Create(AGrid: TAdvColumnGrid; AData: TQryRecArray<TQryRecInfo>);

    /// <summary>
    /// Frees resources and unsubscribes from events.
    /// </summary>
    destructor Destroy; override;

    /// <summary>
    /// Initializes the binding by populating the grid and setting up mappings.
    /// </summary>
    procedure InitializeBinding;
  end;

implementation

{ TORMBinding }

constructor TORMBinding.Create(AGrid: TAdvColumnGrid; AData: TQryRecArray<TQryRecInfo>);
begin
  inherited Create;
  FGrid := AGrid;
  FData := AData;
  FColumnFields := TDictionary<Integer, string>.Create;
  FColumnTypes := TDictionary<string, TRttiType>.Create;
  FRowMapping := TDictionary<Integer, TQryRecInfo>.Create;
  FCtx := TRttiContext.Create;

  BindColumns;
  InitializeBinding;
  SetupEventHandlers;
end;

destructor TORMBinding.Destroy;
var
  ORMRec: TQryRecInfo;
begin
  // Unsubscribe from ORM property changed events to prevent memory leaks
  for ORMRec in FRowMapping.Values do
    ORMRec.OnPropertyChanged := nil;

  FRowMapping.Free;
  FColumnFields.Free;
  FColumnTypes.Free;
  FCtx.Free;
  inherited;
end;

/// <summary>
/// Binds grid columns to ORM properties based on the ORMField property.
/// </summary>
procedure TORMBinding.BindColumns;
var
  i: Integer;
  PropName: string;
  RttiProp: TRttiProperty;
  ORMRec: TQryRecInfo;
begin
  if FData.RecCount = 0 then
    Exit; // No data to bind

  ORMRec := FData.Rec(0); // Assuming all records are of the same type

  for i := 0 to FGrid.ColCount - 1 do
  begin
    PropName := FGrid.ORMField[i];
    if PropName <> '' then
    begin
      if not FColumnFields.ContainsKey(i) then
        FColumnFields.Add(i, PropName)
      else
        FColumnFields[i] := PropName; // Update if already exists

      // Use RTTI to get the property type
      RttiProp := FCtx.GetType(ORMRec.ClassType).GetProperty(PropName);
      if Assigned(RttiProp) then
        FColumnTypes.AddOrSetValue(PropName, RttiProp.PropertyType)
      else
        raise Exception.CreateFmt('Property "%s" not found in class "%s".', [PropName, ORMRec.ClassName]);
    end;
  end;
end;

/// <summary>
/// Initializes the binding by populating the grid and setting up row mappings.
/// </summary>
procedure TORMBinding.InitializeBinding;
begin
  PopulateAndMapGrid;
end;

/// <summary>
/// Populates the grid with data from TQryRecArray and maps each row to its corresponding ORM record.
/// </summary>
procedure TORMBinding.PopulateAndMapGrid;
var
  i, j: Integer;
  ORMRec: TQryRecInfo;
  PropName: string;
  RttiProp: TRttiProperty;
  Value: TValue;
begin
  // Set grid row count (+1 if there's a header row; adjust as needed)
  FGrid.RowCount := FData.RecCount + 1;
  FRowMapping.Clear;

  for i := 0 to FData.RecCount - 1 do
  begin
    ORMRec := FData.Rec(i);
    FRowMapping.Add(i, ORMRec);

    // Subscribe to ORM property changed event
    ORMRec.OnPropertyChanged := ORMPropertyChanged;

    for j := 0 to FGrid.ColCount - 1 do
    begin
      if FColumnFields.TryGetValue(j, PropName) then
      begin
        RttiProp := FCtx.GetType(ORMRec.ClassType).GetProperty(PropName);
        if Assigned(RttiProp) then
        begin
          Value := ORMRec.GetPropertyValue(PropName);
          case RttiProp.PropertyType.TypeKind of
            tkInteger, tkInt64:
              FGrid.Cells[j, i + 1] := Value.ToString; // Adjust for header row
            tkFloat:
              FGrid.Cells[j, i + 1] := FloatToStr(Value.AsExtended);
            tkString, tkUString, tkLString, tkWString:
              FGrid.Cells[j, i + 1] := Value.AsString;
            tkEnumeration:
              if RttiProp.PropertyType.Handle = TypeInfo(Boolean) then
                FGrid.Cells[j, i + 1] := IfThen(Value.AsBoolean, 'True', 'False')
              else
                FGrid.Cells[j, i + 1] := Value.ToString;
            tkVariant:
              FGrid.Cells[j, i + 1] := VarToStr(Value.AsVariant);
            else
              FGrid.Cells[j, i + 1] := Value.ToString;
          end;
        end
        else
          FGrid.Cells[j, i + 1] := ''; // Clear cell if property not found
      end
      else
        FGrid.Cells[j, i + 1] := ''; // Clear cell if no mapping exists
    end;
  end;
end;

/// <summary>
/// Sets up event handlers for synchronizing changes between the grid and ORM data.
/// </summary>
procedure TORMBinding.SetupEventHandlers;
begin
  // Assign the grid's OnEditCellDone event to handle cell changes
  FGrid.OnEditCellDone := GridCellChanged;
end;

/// <summary>
/// Event handler triggered when a grid cell edit is completed.
/// Updates the corresponding ORM property.
/// </summary>
procedure TORMBinding.GridCellChanged(Sender: TObject; ACol, ARow: Integer);
var
  NewValue: string;
  ORMRec: TQryRecInfo;
  PropName: string;
  RttiProp: TRttiProperty;
  TValueToSet: TValue;
begin
  // Ignore header row or invalid rows
  if ARow < 1 then Exit; // Assuming first row (0) is header

  if not FRowMapping.TryGetValue(ARow - 1, ORMRec) then Exit; // Adjust for header row
  if not FColumnFields.TryGetValue(ACol, PropName) then Exit;

  RttiProp := FCtx.GetType(ORMRec.ClassType).GetProperty(PropName);
  if not Assigned(RttiProp) then Exit;

  // Get the new value from the grid
  NewValue := FGrid.Cells[ACol, ARow];

  // Convert the string to the appropriate TValue based on property type
  case RttiProp.PropertyType.TypeKind of
    tkInteger:
      TValueToSet := TValue.From<Integer>(StrToIntDef(NewValue, 0));
    tkInt64:
      TValueToSet := TValue.From<Int64>(StrToInt64Def(NewValue, 0));
    tkFloat:
      TValueToSet := TValue.From<Extended>(StrToFloatDef(NewValue, 0.0));
    tkString, tkUString, tkLString, tkWString:
      TValueToSet := TValue.From<string>(NewValue);
    tkEnumeration:
      if RttiProp.PropertyType.Handle = TypeInfo(Boolean) then
        TValueToSet := TValue.From<Boolean>(SameText(NewValue, 'True') or SameText(NewValue, '1'))
      else
        TValueToSet := TValue.From<string>(NewValue);
    tkVariant:
      TValueToSet := TValue.FromVariant(NewValue);
    else
      TValueToSet := TValue.From<string>(NewValue); // Default to string for unsupported types
  end;

  // Update the ORM property using the integrated SetORMProperty procedure
  SetORMProperty(ORMRec, PropName, TValueToSet);
end;

/// <summary>
/// Event handler triggered when an ORM property is changed.
/// Updates the corresponding grid cell.
/// </summary>
procedure TORMBinding.ORMPropertyChanged(Sender: TQryRecInfo; const PropertyName: string);
var
  ARow: Integer;
  ACol: Integer;
  RttiProp: TRttiProperty;
  Value: TValue;
begin
  // Find the row corresponding to the ORM record
  ARow := -1;
  for var Key in FRowMapping.Keys do
  begin
    if FRowMapping[Key] = Sender then
    begin
      ARow := Key;
      Break;
    end;
  end;

  if ARow = -1 then Exit; // Record not found

  // Find the column corresponding to the property
  ACol := -1;
  for var Col in FColumnFields.Keys do
  begin
    if SameText(FColumnFields[Col], PropertyName) then
    begin
      ACol := Col;
      Break;
    end;
  end;

  if ACol = -1 then Exit; // Column not found

  RttiProp := FCtx.GetType(Sender.ClassType).GetProperty(PropertyName);
  if not Assigned(RttiProp) then Exit;

  // Get the new value from ORM
  Value := Sender.GetPropertyValue(PropertyName);

  // Update the grid cell based on the property type
  case RttiProp.PropertyType.TypeKind of
    tkInteger, tkInt64:
      FGrid.Cells[ACol, ARow + 1] := Value.ToString; // Adjust for header row
    tkFloat:
      FGrid.Cells[ACol, ARow + 1] := FloatToStr(Value.AsExtended);
    tkString, tkUString, tkLString, tkWString:
      FGrid.Cells[ACol, ARow + 1] := Value.AsString;
    tkEnumeration:
      if RttiProp.PropertyType.Handle = TypeInfo(Boolean) then
        FGrid.Cells[ACol, ARow + 1] := IfThen(Value.AsBoolean, 'True', 'False')
      else
        FGrid.Cells[ACol, ARow + 1] := Value.ToString;
    tkVariant:
      FGrid.Cells[ACol, ARow + 1] := VarToStr(Value.AsVariant);
    else
      FGrid.Cells[ACol, ARow + 1] := Value.ToString;
  end;
end;

/// <summary>
/// Sets the ORM property based on the grid cell change.
/// </summary>
procedure TORMBinding.SetORMProperty(ORMRec: TQryRecInfo; const FieldName: string; const Value: TValue);
var
  Ctx: TRttiContext;
  Prop: TRttiProperty;
  ORMFieldName: string;
  ObjType: TRttiType;
begin
  Ctx := TRttiContext.Create;
  try
    ObjType := Ctx.GetType(ORMRec.ClassType);
    Prop := ObjType.GetProperty(FieldName);

    if Assigned(Prop) and Prop.IsWritable then
    begin
      ORMFieldName := '_' + FieldName;

      if ObjType.GetField(ORMFieldName) = nil then
        Exit; // Backing field not found, skip

      if Prop.PropertyType.TypeKind = tkRecord then
      begin
        // Check if the property is a TNullable<T>
        if Prop.PropertyType.Name.StartsWith('TNullable<') then
        begin
          if Value.IsEmpty or VarIsNull(Value.AsVariant) then
          begin
            // Set the property to null
            if Prop.PropertyType.Name = 'TNullable<System.string>' then
              Prop.SetValue(Pointer(ORMRec), TValue.From<TNullable<string>>(NullValue))
            else if Prop.PropertyType.Name = 'TNullable<System.Integer>' then
              Prop.SetValue(Pointer(ORMRec), TValue.From<TNullable<Integer>>(NullValue))
            else if Prop.PropertyType.Name = 'TNullable<System.Int64>' then
              Prop.SetValue(Pointer(ORMRec), TValue.From<TNullable<Int64>>(NullValue))
            else if Prop.PropertyType.Name = 'TNullable<System.Extended>' then
              Prop.SetValue(Pointer(ORMRec), TValue.From<TNullable<Extended>>(NullValue))
            else if Prop.PropertyType.Name = 'TNullable<System.Currency>' then
              Prop.SetValue(Pointer(ORMRec), TValue.From<TNullable<Currency>>(NullValue))
            else if Prop.PropertyType.Name = 'TNullable<System.TDateTime>' then
              Prop.SetValue(Pointer(ORMRec), TValue.From<TNullable<TDateTime>>(NullValue))
            else if Prop.PropertyType.Name = 'TNullable<System.Boolean>' then
              Prop.SetValue(Pointer(ORMRec), TValue.From<TNullable<Boolean>>(NullValue))
            else if Prop.PropertyType.Name = 'TNullable<System.Variant>' then
              Prop.SetValue(Pointer(ORMRec), TValue.From<TNullable<Variant>>(NullValue));
          end
          else
          begin
            // Set the property with the actual value, adjust types
            if Prop.PropertyType.Name = 'TNullable<System.string>' then
              Prop.SetValue(Pointer(ORMRec), TValue.From<TNullable<string>>(Value.AsString))
            else if Prop.PropertyType.Name = 'TNullable<System.Integer>' then
              Prop.SetValue(Pointer(ORMRec), TValue.From<TNullable<Integer>>(Value.AsInteger))
            else if Prop.PropertyType.Name = 'TNullable<System.Int64>' then
              Prop.SetValue(Pointer(ORMRec), TValue.From<TNullable<Int64>>(Value.AsInt64))
            else if Prop.PropertyType.Name = 'TNullable<System.Extended>' then
              Prop.SetValue(Pointer(ORMRec), TValue.From<TNullable<Extended>>(Value.AsExtended))
            else if Prop.PropertyType.Name = 'TNullable<System.Currency>' then
              Prop.SetValue(Pointer(ORMRec), TValue.From<TNullable<Currency>>(Value.AsCurrency))
            else if Prop.PropertyType.Name = 'TNullable<System.TDateTime>' then
              Prop.SetValue(Pointer(ORMRec), TValue.From<TNullable<TDateTime>>(Value.AsType<TDateTime>))
            else if Prop.PropertyType.Name = 'TNullable<System.Boolean>' then
              Prop.SetValue(Pointer(ORMRec), TValue.From<TNullable<Boolean>>(Value.AsBoolean))
            // No need to handle TNullable<Variant> since no conversion required except as fallback
            else
              raise EORMUtilsException.CreateFmt('Unsupported nullable type: %s', [Prop.PropertyType.Name]);
          end;
        end
        else
          raise EORMUtilsException.CreateFmt('Unsupported record type: %s', [Prop.PropertyType.Name]);
      end
      else
      begin
        // Handle non-nullable types
        case Prop.PropertyType.TypeKind of
          tkString, tkLString, tkWString, tkUString:
            ObjType.GetField(ORMFieldName).SetValue(Pointer(ORMRec), Value.AsString);
          tkInteger:
            ObjType.GetField(ORMFieldName).SetValue(Pointer(ORMRec), Value.AsInteger);
          tkInt64:
            ObjType.GetField(ORMFieldName).SetValue(Pointer(ORMRec), Value.AsInt64);
          tkFloat:
            if Prop.PropertyType.Handle = TypeInfo(Currency) then
              ObjType.GetField(ORMFieldName).SetValue(Pointer(ORMRec), Value.AsCurrency)
            else if Prop.PropertyType.Handle = TypeInfo(TDateTime) then
              ObjType.GetField(ORMFieldName).SetValue(Pointer(ORMRec), TValue.From<TDateTime>(Value.AsType<TDateTime>))
            else
              ObjType.GetField(ORMFieldName).SetValue(Pointer(ORMRec), Value.AsExtended);
          tkEnumeration:
            if Prop.PropertyType.Handle = TypeInfo(Boolean) then
              ObjType.GetField(ORMFieldName).SetValue(Pointer(ORMRec), Value.AsBoolean)
            else
              ObjType.GetField(ORMFieldName).SetValue(Pointer(ORMRec), Value.AsString);
        else
          raise EORMUtilsException.CreateFmt('Unsupported type for field "%s" in record "%s".', [FieldName, ORMRec.ClassName]);
        end;
      end;

      // After setting the ORM property, find the associated component and update it
      var Component := ORMManager.FindComponent(ORMRec, FieldName);
      if Assigned(Component) then
      begin
        ORMManager.UpdateComponentFromORM(Component, ORMRec, FieldName);
      end;

    end
    else
      raise EORMUtilsException.CreateFmt('Property "%s" not found or not writable in record "%s".', [FieldName, ORMRec.ClassName]);
  finally
    // TRttiContext is a record and managed automatically
  end;
end;

end.

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

TAdvColumnGrid event where the logic will be used:
procedure TMainForm.GridNomesEditCellDone(Sender: TObject; ACol, ARow: Integer);

    Core Improvements:
    Review and implement event handler for updating respective property of the TQryRecInfo instance associated with this Row.

    New Features:
    Record management (add, delete, move)


Based on the included information, please carefully review everything and suggest and implement enhancements on units orm_binding and orm_utils.