unit orm_advsearch;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, StrUtils,
  System.Classes, Vcl.Graphics, Vcl.Controls, Vcl.Forms, Vcl.Dialogs,
  System.Generics.Collections, Vcl.StdCtrls, Vcl.ExtCtrls, scGPExtControls,
  scControls, scGPFontControls, scGPControls, sPanel, AdvUtil, AdvGrid,
  AdvObj, BaseGrid, Vcl.ComCtrls, Vcl.WinXPickers, orm_utils, ORM_CustomAttributes,
  System.Rtti, System.DateUtils, DM1, Data.DB, Uni, Vcl.Grids, AdvCGrid, Math,
  Vcl.Mask, pixel_utils, interposer_class, pixel_classes, System.Types,
  System.UITypes;

type
  TFilterManager = class;

  TSearchAdvColumn = class
  private
    FFieldName: string;
    FDisplayName: string;
    FWidth: Integer;
    FVisible: Boolean;
    FUsableAsFilter: Boolean;
    FDataType: TORMFormatType;
  public
    constructor Create(const AFieldName, ADisplayName: string; AWidth: Integer;
      AVisible: Boolean = True; AUsableAsFilter: Boolean = True);
    property FieldName: string read FFieldName;
    property DisplayName: string read FDisplayName;
    property Width: Integer read FWidth;
    property Visible: Boolean read FVisible;
    property UsableAsFilter: Boolean read FUsableAsFilter;
    property DataType: TORMFormatType read FDataType write FDataType;
  end;

  TSelectedAdvSearch = class
  private
    FValues: TDictionary<string, TValue>;
    FDisplayValues: TDictionary<string, string>;
  public
    constructor Create;
    destructor Destroy; override;
    procedure SetValue(const AFieldName: string; const AValue: TValue; const ADisplayValue: string);
    function GetValue(const AFieldName: string): TValue;
    function GetDisplayValue(const AFieldName: string): string;
    property Values[const AFieldName: string]: TValue read GetValue; default;
  end;

  TFilterGroupType = (fgtAnd, fgtOr);

  TFilterOperationType = (fotEqual, fotNotEqual, fotGreaterThan, fotLessThan,
    fotGreaterEqual, fotLessEqual, fotBetween, fotContains, fotStartsWith,
    fotEndsWith, fotIsNull, fotIsNotNull);

  TFilterValueInfo = record
    FieldName: string;
    OperationType: TFilterOperationType;
    Value1: Variant;
    Value2: Variant;
    DataType: TORMFormatType;
  end;

  TFilterPanel = class
  private
    FId: Integer;
    FGroupType: TFilterGroupType;
    FIsGroup: Boolean;
    FParentGroup: TFilterPanel;
    FPanel: TscGPPanel;
    FHeaderPanel: TscGPPanel;
    FContentPanel: TscGPPanel;
    FExpandButton: TscGPCharGlyphButton;
    FGroupTypeButton: TscGPCharGlyphButton;
    FCloseButton: TscGPCharGlyphButton;
    FDragHandle: TscGPCharGlyphButton;
    FFieldCombo: TscComboBox;
    FOperatorCombo: TscComboBox;
    FValueEdit: TscGPEdit;
    FDateValue: TscGPDateEdit;
    FValueEdit2: TscGPEdit;
    FDateValue2: TscGPDateEdit;
    FChildFilters: TObjectList<TFilterPanel>;
    FFilterID: TGUID;
    FIsExpanded: Boolean;
    FLevel: Integer;
    FFilterInfo: TFilterValueInfo;
    procedure SetupVisuals(AOwner: TComponent);
    procedure UpdateLayout;
    procedure ExpandButtonClick(Sender: TObject);
    procedure GroupTypeButtonClick(Sender: TObject);
    procedure CloseButtonClick(Sender: TObject);
    procedure HandleDragStart(Sender: TObject; Button: TMouseButton;
      Shift: TShiftState; X, Y: Integer);
    procedure HandleDragOver(Sender, Source: TObject; X, Y: Integer;
      State: TDragState; var Accept: Boolean);
    procedure HandleDragDrop(Sender, Source: TObject; X, Y: Integer);
    procedure UpdateGroupTypeVisuals;
    procedure FieldComboChange(Sender: TObject);
    procedure OperatorComboChange(Sender: TObject);
    procedure UpdateOperators;
    procedure UpdateValueControls;
    function GetFilterCondition: string;
    function ValidateFilter: Boolean;
    function FormatValue(const Value: Variant): string;
    function QuoteValue(const Value: Variant; ADataType: TORMFormatType): string;
    procedure SetupGroupControls(PanelSpacing, ButtonSize, ControlHeight: Integer);
    procedure SetupFilterControls(PanelSpacing, ButtonSize, ControlHeight: Integer);
  public
    constructor Create(AOwner: TComponent; ALevel: Integer;
      AParent: TFilterPanel = nil; AIsGroup: Boolean = False);
    destructor Destroy; override;
    procedure AddChild(Child: TFilterPanel);
    procedure RemoveChild(Child: TFilterPanel);
    procedure ToggleExpanded;
    procedure SetPosition(const Top: Integer);
    function CalculateHeight: Integer;
    function GetFilterInfo: TFilterValueInfo;
    property Id: Integer read FId;
    property GroupType: TFilterGroupType read FGroupType write FGroupType;
    property IsGroup: Boolean read FIsGroup;
    property ParentGroup: TFilterPanel read FParentGroup write FParentGroup;
    property Panel: TscGPPanel read FPanel;
    property FilterID: TGUID read FFilterID;
    property Level: Integer read FLevel;
    property IsExpanded: Boolean read FIsExpanded;
    property ChildFilters: TObjectList<TFilterPanel> read FChildFilters;
  end;

  TORMSearchAdvForm = class(TForm)
    pnlMain: TscGPPanel;
    pnlHeader: TscGPPanel;
    pnlFilters: TscGPPanel;
    pnlBottom: TscGPPanel;
    lblTitle: TLabel;
    btnAddAndGroup: TscGPCharGlyphButton;
    btnAddOrGroup: TscGPCharGlyphButton;
    btnAddFilter: TscGPCharGlyphButton;
    btnClearAll: TscGPCharGlyphButton;
    btnApply: TscGPCharGlyphButton;
    SearchGrid: TAdvColumnGrid;
    pnlFilterContainer: TscScrollBox;

    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure btnAddAndGroupClick(Sender: TObject);
    procedure btnAddOrGroupClick(Sender: TObject);
    procedure btnAddFilterClick(Sender: TObject);
    procedure btnClearAllClick(Sender: TObject);
    procedure btnApplyClick(Sender: TObject);
    procedure SearchGridDblClick(Sender: TObject);

  private
    FFilterManager: TFilterManager;
    FSearchColumns: TObjectList<TSearchAdvColumn>;
    FSelectedSearch: TSelectedAdvSearch;
    FBaseQuery: string;
    FConnection: TUniConnection;
    FQuery: TUniQuery;
    FRecordClass: TClass;
    FFieldTypes: TDictionary<string, TORMFormatType>;

    procedure SetupVisuals;
    procedure UpdateLayout;
    procedure UpdateGridData;
    procedure LoadData;
    procedure BuildFilter;
    procedure UpdateSelectedSearch;
    function GetFilteredSQL: string;
    procedure SetupFieldTypes;
    function ValidateFilters: Boolean;
    procedure UpdateControlStates;
    procedure SetupGrid;
    property FilterManager: TFilterManager read FFilterManager;
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    procedure InitializeSearch(
      AConnection: TUniConnection;
      const ABaseQuery: string;
      const ASearchColumns: array of TSearchAdvColumn;
      ARecordClass: TClass = nil);
    class function Execute(
      AConnection: TUniConnection;
      const ABaseQuery: string;
      const ASearchColumns: array of TSearchAdvColumn;
      var ASelectedSearch: TSelectedAdvSearch;
      ARecordClass: TClass = nil): Boolean;
  end;

  TFilterManager = class
  private
    FForm: TORMSearchAdvForm;
    FRootFilters: TObjectList<TFilterPanel>;
    FNextId: Integer;
    FDragPanel: TFilterPanel;
    function CreateUniqueId: Integer;
    procedure UpdateFilterLayout;
    procedure HandleFilterRemoved(Filter: TFilterPanel);
    function FindFilterById(Id: Integer): TFilterPanel;
    procedure MoveFilter(Source, Target: TFilterPanel; Position: TPoint);
    function IsFilterInHierarchy(Parent, Child: TFilterPanel): Boolean;
  public
    constructor Create(AForm: TORMSearchAdvForm);
    destructor Destroy; override;
    function CreateFilterGroup(GroupType: TFilterGroupType;
      Parent: TFilterPanel = nil): TFilterPanel;
    function CreateFilter(Parent: TFilterPanel = nil): TFilterPanel;
    procedure RemoveFilter(Filter: TFilterPanel);
    procedure UpdateLayout;
    procedure BeginDrag(Filter: TFilterPanel);
    procedure EndDrag;
    function BuildFilterSQL: string;
    function ValidateFilters: Boolean;
    property RootFilters: TObjectList<TFilterPanel> read FRootFilters;
  end;

var
  ORMSearchAdvForm: TORMSearchAdvForm;

implementation

{$R *.dfm}

const
  PANEL_MARGIN = 8;
  PANEL_SPACING = 4;
  BUTTON_SIZE = 24;
  INDENT_WIDTH = 24;
  CONTROL_HEIGHT = 26;

{ TFilterPanel Implementation }

constructor TFilterPanel.Create(AOwner: TComponent; ALevel: Integer; 
  AParent: TFilterPanel = nil; AIsGroup: Boolean = False);
begin
  inherited Create;
  FId := TORMSearchAdvForm(AOwner).FilterManager.CreateUniqueId;
  FLevel := ALevel;
  FParentGroup := AParent;
  FIsGroup := AIsGroup;
  FIsExpanded := True;
  CreateGUID(FFilterID);
  
  if FIsGroup then
    FChildFilters := TObjectList<TFilterPanel>.Create(False)
  else
    FChildFilters := nil;
    
  SetupVisuals(AOwner);
end;

destructor TFilterPanel.Destroy;
begin
  if Assigned(FChildFilters) then
    FreeAndNil(FChildFilters);
  
  if Assigned(FPanel) then
  begin
    FPanel.Parent := nil;
    FreeAndNil(FPanel);
  end;
  
  inherited;
end;

procedure TFilterPanel.SetupVisuals(AOwner: TComponent);
begin
  // Create main panel
  FPanel := TscGPPanel.Create(AOwner);
  with FPanel do
  begin
    Parent := TORMSearchAdvForm(AOwner).pnlFilterContainer;
    Left := PANEL_MARGIN + (FLevel * INDENT_WIDTH);
    Height := 40;
    Align := alTop;

    // Panel styling
    FluentUIOpaque := False;
    StorePaintBuffer := True;

    // Group styling based on type
    if FIsGroup then
    begin
      if GroupType = fgtAnd then
      begin
        FillColor := RGB(238, 242, 255);  // Light indigo
        FrameColor := RGB(79, 70, 229);   // Indigo
      end else begin
        FillColor := RGB(254, 242, 242);  // Light red
        FrameColor := RGB(220, 38, 38);   // Red
      end;
      FillColorAlpha := 230;
    end else begin
      FillColor := clWhite;
      FillColorAlpha := 255;
      FrameColor := RGB(229, 231, 235);  // Light gray
    end;

    FrameColorAlpha := 40;
    FrameWidth := 1;
    FrameRadius := 6;

    DragMode := dmAutomatic;
    OnDragOver := HandleDragOver;
    OnDragDrop := HandleDragDrop;
  end;

  // Create drag handle
  FDragHandle := TscGPCharGlyphButton.Create(FPanel);
  with FDragHandle do
  begin
    Parent := FPanel;
    Width := BUTTON_SIZE;
    Height := BUTTON_SIZE;
    Left := PANEL_SPACING;
    Top := (FPanel.Height - Height) div 2;
    FluentUIOpaque := False;

    GlyphOptions.Index := 597;  // Grip icon
    GlyphOptions.NormalColor := RGB(156, 163, 175);
    GlyphOptions.NormalColorAlpha := 200;
    GlyphOptions.Size := 16;

    OnMouseDown := HandleDragStart;
  end;

  if FIsGroup then
  begin
    SetupGroupControls(PANEL_SPACING, BUTTON_SIZE, CONTROL_HEIGHT);
  end else begin
    SetupFilterControls(PANEL_SPACING, BUTTON_SIZE, CONTROL_HEIGHT);
  end;

  // Create close button last so it's always on top
  FCloseButton := TscGPCharGlyphButton.Create(FPanel);
  with FCloseButton do
  begin
    Parent := FPanel;
    Width := BUTTON_SIZE;
    Height := BUTTON_SIZE;
    Left := FPanel.Width - Width - PANEL_SPACING;
    Top := (FPanel.Height - Height) div 2;

    GlyphOptions.Index := 13;  // X icon
    GlyphOptions.NormalColor := RGB(156, 163, 175);
    GlyphOptions.HotColor := RGB(239, 68, 68);
    GlyphOptions.Size := 16;

    OnClick := CloseButtonClick;
  end;
end;

procedure TFilterPanel.HandleDragStart(Sender: TObject; Button: TMouseButton;
  Shift: TShiftState; X, Y: Integer);
begin
  if Button = mbLeft then
  begin
    TORMSearchAdvForm(FPanel.Owner).FilterManager.BeginDrag(Self);
    FPanel.BeginDrag(False);
  end;
end;

procedure TFilterPanel.HandleDragOver(Sender, Source: TObject; X, Y: Integer;
  State: TDragState; var Accept: Boolean);
var
  SourcePanel: TFilterPanel;
begin
  Accept := False;
  if not (Source is TscGPPanel) then Exit;
  
  SourcePanel := TORMSearchAdvForm(FPanel.Owner).FilterManager.FDragPanel;
  if not Assigned(SourcePanel) or (SourcePanel = Self) then Exit;
  
  // Prevent dropping parent into child
  if IsGroup and SourcePanel.IsGroup then
  begin
    var TestPanel := ParentGroup;
    while Assigned(TestPanel) do
    begin
      if TestPanel = SourcePanel then Exit;
      TestPanel := TestPanel.ParentGroup;
    end;
  end;
  
  Accept := True;
end;

procedure TFilterPanel.HandleDragDrop(Sender, Source: TObject; X, Y: Integer);
var
  SourcePanel: TFilterPanel;
  DropPos: TPoint;
begin
  if not (Source is TscGPPanel) then Exit;
  
  SourcePanel := TORMSearchAdvForm(FPanel.Owner).FilterManager.FDragPanel;
  if not Assigned(SourcePanel) or (SourcePanel = Self) then Exit;
  
  DropPos := Point(X, Y);
  TORMSearchAdvForm(FPanel.Owner).FilterManager.MoveFilter(SourcePanel, Self, DropPos);
end;

procedure TFilterPanel.ExpandButtonClick(Sender: TObject);
begin
  ToggleExpanded;
end;

procedure TFilterPanel.GroupTypeButtonClick(Sender: TObject);
begin
  if GroupType = fgtAnd then
    GroupType := fgtOr
  else
    GroupType := fgtAnd;
    
  UpdateGroupTypeVisuals;
end;

procedure TFilterPanel.CloseButtonClick(Sender: TObject);
begin
  TORMSearchAdvForm(FPanel.Owner).FilterManager.RemoveFilter(Self);
end;

procedure TFilterPanel.UpdateGroupTypeVisuals;
begin
  if not FIsGroup then Exit;
  
  case GroupType of
    fgtAnd:
      begin
        FGroupTypeButton.Caption := 'AND';
        FGroupTypeButton.Options.NormalColor := RGB(238, 242, 255);
        FGroupTypeButton.Options.FontNormalColor := RGB(79, 70, 229);
      end;
    fgtOr:
      begin
        FGroupTypeButton.Caption := 'OR';
        FGroupTypeButton.Options.NormalColor := RGB(254, 242, 242);
        FGroupTypeButton.Options.FontNormalColor := RGB(220, 38, 38);
      end;
  end;
end;

procedure TFilterPanel.ToggleExpanded;
begin
  if not FIsGroup then Exit;
  
  FIsExpanded := not FIsExpanded;
  FContentPanel.Visible := FIsExpanded;
  
  if Assigned(FExpandButton) then
  begin
    if FIsExpanded then
      FExpandButton.GlyphOptions.Index := 263  // chevron down
    else
      FExpandButton.GlyphOptions.Index := 261; // chevron right
  end;
  
  TORMSearchAdvForm(FPanel.Owner).FilterManager.UpdateLayout;
end;

procedure TFilterPanel.AddChild(Child: TFilterPanel);
begin
  if not FIsGroup then Exit;
  
  Child.ParentGroup := Self;
  FChildFilters.Add(Child);
  
  if Assigned(FContentPanel) then
    Child.Panel.Parent := FContentPanel;
    
  TORMSearchAdvForm(FPanel.Owner).FilterManager.UpdateLayout;
end;

procedure TFilterPanel.RemoveChild(Child: TFilterPanel);
begin
  if not FIsGroup then Exit;
  
  FChildFilters.Extract(Child);
  Child.ParentGroup := nil;
  
  TORMSearchAdvForm(FPanel.Owner).FilterManager.UpdateLayout;
end;

procedure TFilterPanel.SetPosition(const Top: Integer);
begin
  FPanel.Top := Top;
end;

function TFilterPanel.CalculateHeight: Integer;
begin
  Result := FPanel.Height;
  
  if FIsGroup and FIsExpanded and Assigned(FChildFilters) then
  begin
    for var Child in FChildFilters do
      Inc(Result, Child.CalculateHeight + 4); // 4px spacing
  end;
end;

function TFilterPanel.GetFilterCondition: string;
var
  SelectedColumn: TSearchAdvColumn;
  Form: TORMSearchAdvForm;
begin
  Result := '';
  if FIsGroup then Exit;

  Form := TORMSearchAdvForm(FPanel.Owner);
  if not Assigned(Form) then Exit;

  // Find selected column
  SelectedColumn := nil;
  for var Column in Form.FSearchColumns do
  begin
    if Column.DisplayName = FFieldCombo.Text then
    begin
      SelectedColumn := Column;
      Break;
    end;
  end;

  if not Assigned(SelectedColumn) then Exit;

  // Build condition based on operator
  case FFilterInfo.OperationType of
    fotEqual:
      Result := Format('%s = %s', [SelectedColumn.FieldName, QuoteValue(FFilterInfo.Value1, SelectedColumn.DataType)]);
    fotNotEqual:
      Result := Format('%s <> %s', [SelectedColumn.FieldName, QuoteValue(FFilterInfo.Value1, SelectedColumn.DataType)]);
    fotGreaterThan:
      Result := Format('%s > %s', [SelectedColumn.FieldName, QuoteValue(FFilterInfo.Value1, SelectedColumn.DataType)]);
    fotLessThan:
      Result := Format('%s < %s', [SelectedColumn.FieldName, QuoteValue(FFilterInfo.Value1, SelectedColumn.DataType)]);
    fotGreaterEqual:
      Result := Format('%s >= %s', [SelectedColumn.FieldName, QuoteValue(FFilterInfo.Value1, SelectedColumn.DataType)]);
    fotLessEqual:
      Result := Format('%s <= %s', [SelectedColumn.FieldName, QuoteValue(FFilterInfo.Value1, SelectedColumn.DataType)]);
    fotBetween:
      Result := Format('(%s BETWEEN %s AND %s)', [SelectedColumn.FieldName,
        QuoteValue(FFilterInfo.Value1, SelectedColumn.DataType),
        QuoteValue(FFilterInfo.Value2, SelectedColumn.DataType)]);
    fotContains:
      Result := Format('%s LIKE ''%%%s%%''', [SelectedColumn.FieldName, QuoteEsc(FFilterInfo.Value1)]);
    fotStartsWith:
      Result := Format('%s LIKE ''%s%%''', [SelectedColumn.FieldName, QuoteEsc(FFilterInfo.Value1)]);
    fotEndsWith:
      Result := Format('%s LIKE ''%%%s''', [SelectedColumn.FieldName, QuoteEsc(FFilterInfo.Value1)]);
    fotIsNull:
      Result := Format('(%s IS NULL)', [SelectedColumn.FieldName]);
    fotIsNotNull:
      Result := Format('(%s IS NOT NULL)', [SelectedColumn.FieldName]);
  end;
end;

function TFilterPanel.ValidateFilter: Boolean;
begin
  Result := False;
  if FIsGroup then Exit(True);

  // Basic validation
  if FFieldCombo.ItemIndex < 0 then Exit;
  if FOperatorCombo.ItemIndex < 0 then Exit;

  // Find selected column
  var Form := TORMSearchAdvForm(FPanel.Owner);
  var SelectedColumn: TSearchAdvColumn := nil;

  for var Column in Form.FSearchColumns do
  begin
    if Column.DisplayName = FFieldCombo.Text then
    begin
      SelectedColumn := Column;
      Break;
    end;
  end;

  if not Assigned(SelectedColumn) then Exit;

  // Skip value validation for null checks
  if (FOperatorCombo.Text = 'Is Empty') or
     (FOperatorCombo.Text = 'Is Not Empty') then
  begin
    Result := True;
    Exit;
  end;

  // Validate based on data type
  case SelectedColumn.DataType of
    tiString:
      // String values are always valid
      Result := True;

    tiInteger:
      begin
        var IntValue: Int64;
        Result := TryStrToInt64(FValueEdit.Text, IntValue);
      end;

    tiFloat, tiMoney:
      begin
        var FloatValue: Extended;
        Result := TryStrToFloat(FValueEdit.Text, FloatValue);
      end;

    tiDate, tiDateTime:
      begin
        Result := FDateValue.Date > 0;
      end;

    tiBoolean:
      begin
        var BoolStr := LowerCase(FValueEdit.Text);
        Result := (BoolStr = 'true') or (BoolStr = 'false') or
                 (BoolStr = '1') or (BoolStr = '0') or
                 (BoolStr = 'yes') or (BoolStr = 'no');
      end;
  end;
end;

function TFilterPanel.QuoteValue(const Value: Variant; ADataType: TORMFormatType): string;
begin
  if Value.Trim = '' then
    Exit('NULL');

  case ADataType of
    tiString:
      Result := QuotedStr(Value);
      
    tiInteger, tiFloat, tiMoney:
      begin
        if ADataType = tiMoney then
          Result := QuoteNum(GetMoney(Value))
        else
          Result := QuoteNum(Value);
      end;
      
    tiDate:
      Result := QuotedStr(FormatDateTime('yyyy-mm-dd', FDateValue.Date));
      
    tiDateTime:
      Result := QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', FDateValue.Date));
      
    tiBoolean:
      begin
        var BoolStr := LowerCase(Value);
        if (BoolStr = 'true') or (BoolStr = '1') or (BoolStr = 'yes') then
          Result := 'TRUE'
        else
          Result := 'FALSE';
      end;
      
    else
      Result := QuotedStr(Value);
  end;
end;

procedure TFilterPanel.UpdateLayout;
begin
  if not FIsGroup then Exit;
  
  var YPos := FPanel.Height;
  if FIsExpanded and Assigned(FChildFilters) then
  begin
    for var Child in FChildFilters do
    begin
      Child.Panel.Top := YPos;
      Inc(YPos, Child.Panel.Height + 4); // 4px spacing
      Child.UpdateLayout;
    end;
  end;
  
  if Assigned(FContentPanel) then
    FContentPanel.Height := YPos;
end;

{ TFilterManager Implementation }

constructor TFilterManager.Create(AForm: TORMSearchAdvForm);
begin
  inherited Create;
  FForm := AForm;
  FRootFilters := TObjectList<TFilterPanel>.Create(True);
  FNextId := 1;
  FDragPanel := nil;
end;

destructor TFilterManager.Destroy;
begin
  FRootFilters.Free;
  inherited;
end;

function TFilterManager.CreateUniqueId: Integer;
begin
  Result := FNextId;
  Inc(FNextId);
end;

function TFilterManager.CreateFilterGroup(GroupType: TFilterGroupType; 
  Parent: TFilterPanel = nil): TFilterPanel;
begin
 if Assigned(Parent) then
   Result := TFilterPanel.Create(FForm, Parent.Level + 1,
    Parent, True)
  else Result := TFilterPanel.Create(FForm, 0,
    Parent, True);
    
  Result.GroupType := GroupType;
  
  if Assigned(Parent) then
    Parent.AddChild(Result)
  else
    FRootFilters.Add(Result);
    
  UpdateLayout;
end;

function TFilterManager.CreateFilter(Parent: TFilterPanel = nil): TFilterPanel;
begin
 if Assigned(Parent) then
   Result := TFilterPanel.Create(FForm, Parent.Level + 1,
    Parent, False)
  else Result := TFilterPanel.Create(FForm, 0,
    Parent, False);
    
  if Assigned(Parent) then
    Parent.AddChild(Result)
  else
    FRootFilters.Add(Result);
    
  UpdateLayout;
end;

procedure TFilterManager.RemoveFilter(Filter: TFilterPanel);
begin
  if not Assigned(Filter) then Exit;
  
  // First remove from parent if it has one
  if Assigned(Filter.ParentGroup) then
    Filter.ParentGroup.RemoveChild(Filter)
  else
    FRootFilters.Extract(Filter);
    
  // Handle removal of child filters if this is a group
  if Filter.IsGroup and Assigned(Filter.ChildFilters) then
  begin
    while Filter.ChildFilters.Count > 0 do
    begin
      var Child := Filter.ChildFilters[0];
      Filter.RemoveChild(Child);
      if not Assigned(Child.ParentGroup) then
        Child.Free;
    end;
  end;
  
  // Clean up the filter panel
  Filter.Panel.Parent := nil;
  Filter.Free;
  
  UpdateLayout;
  HandleFilterRemoved(Filter);
end;

procedure TFilterManager.HandleFilterRemoved(Filter: TFilterPanel);
begin
  // Update UI state
  FForm.UpdateLayout;
  
  // Clear any active drag operation
  if FDragPanel = Filter then
    FDragPanel := nil;
end;

function TFilterManager.FindFilterById(Id: Integer): TFilterPanel;

  function SearchInGroup(Group: TFilterPanel): TFilterPanel;
  begin
    Result := nil;
    if not Assigned(Group) or not Group.IsGroup then Exit;
    
    for var Child in Group.ChildFilters do
    begin
      if Child.Id = Id then
        Exit(Child);
        
      if Child.IsGroup then
      begin
        Result := SearchInGroup(Child);
        if Assigned(Result) then
          Exit;
      end;
    end;
  end;
  
begin
  Result := nil;
  
  // Search in root filters first
  for var Filter in FRootFilters do
  begin
    if Filter.Id = Id then
      Exit(Filter);
      
    if Filter.IsGroup then
    begin
      Result := SearchInGroup(Filter);
      if Assigned(Result) then
        Exit;
    end;
  end;
end;

procedure TFilterManager.MoveFilter(Source, Target: TFilterPanel; Position: TPoint);
var
  OldParent: TFilterPanel;
  InsertIndex: Integer;
begin
  if not Assigned(Source) or not Assigned(Target) then Exit;
  
  // Don't allow dropping on self or invalid hierarchy
  if (Source = Target) or
     (Target.IsGroup and IsFilterInHierarchy(Target, Source)) then
    Exit;
    
  OldParent := Source.ParentGroup;
  
  // Remove from old parent
  if Assigned(OldParent) then
    OldParent.RemoveChild(Source)
  else
    FRootFilters.Extract(Source);
    
  // Determine if dropping into group or between filters
  if Target.IsGroup and (Position.Y < Target.Panel.Height) then
  begin
    // Dropping into group
    Target.AddChild(Source);
  end
  else
  begin
    // Dropping between filters
    if Assigned(Target.ParentGroup) then
    begin
      InsertIndex := Target.ParentGroup.ChildFilters.IndexOf(Target);
      if Position.Y > Target.Panel.Height div 2 then
        Inc(InsertIndex);
        
      Source.ParentGroup := Target.ParentGroup;
      Target.ParentGroup.ChildFilters.Insert(InsertIndex, Source);
    end
    else
    begin
      InsertIndex := FRootFilters.IndexOf(Target);
      if Position.Y > Target.Panel.Height div 2 then
        Inc(InsertIndex);
        
      Source.ParentGroup := nil;
      FRootFilters.Insert(InsertIndex, Source);
    end;
  end;
  
  UpdateLayout;
end;

function TFilterManager.IsFilterInHierarchy(Parent, Child: TFilterPanel): Boolean;
var
  Current: TFilterPanel;
begin
  Result := False;
  if not Assigned(Parent) or not Assigned(Child) then Exit;
  
  Current := Child;
  while Assigned(Current) do
  begin
    if Current = Parent then
      Exit(True);
    Current := Current.ParentGroup;
  end;
end;

procedure TFilterManager.UpdateLayout;
var 
  TotalHeight: Integer;
begin
  TotalHeight := 0;
  
  for var Filter in FRootFilters do
  begin
    Filter.SetPosition(TotalHeight);
    Inc(TotalHeight, Filter.CalculateHeight + 4); // 4px spacing
    Filter.UpdateLayout;
  end;
  
  // Update container size
  if TotalHeight > 0 then
    Inc(TotalHeight, 8); // Add margins
    
  FForm.pnlFilterContainer.Height := Max(TotalHeight, 100);
  
  // Force visual update
  FForm.pnlFilterContainer.Invalidate;
  Application.ProcessMessages;
end;

procedure TFilterManager.BeginDrag(Filter: TFilterPanel);
begin
  FDragPanel := Filter;
end;

procedure TFilterManager.EndDrag;
begin
  FDragPanel := nil;
end;

function TFilterManager.BuildFilterSQL: string;
var
  SQL: TStringBuilder;

  procedure AddGroupFilters(Group: TFilterPanel);
  var
    IsFirst: Boolean;
  begin
    if not Group.IsGroup then Exit;
    
    IsFirst := True;
    SQL.Append('(');
    
    for var Child in Group.ChildFilters do
    begin
      if not IsFirst then
      begin
        if Group.GroupType = fgtAnd then
          SQL.Append(' AND ')
        else
          SQL.Append(' OR ');
      end;
      
      if Child.IsGroup then
        AddGroupFilters(Child)
      else
      begin
        // Add individual filter condition
        SQL.Append(Child.GetFilterCondition);
      end;
      
      IsFirst := False;
    end;
    
    SQL.Append(')');
  end;
  
begin
  SQL := TStringBuilder.Create;
  try
    var IsFirst := True;
    
    for var Filter in FRootFilters do
    begin
      if not IsFirst then
        SQL.Append(' AND ');
        
      if Filter.IsGroup then
        AddGroupFilters(Filter)
      else
        SQL.Append(Filter.GetFilterCondition);
        
      IsFirst := False;
    end;
    
    Result := SQL.ToString;
  finally
    SQL.Free;
  end;
end;

function TFilterManager.ValidateFilters: Boolean;

  function ValidateGroup(Group: TFilterPanel): Boolean;
  begin
    Result := False;
    if not Group.IsGroup then Exit;
    
    // Group must have at least one child
    if Group.ChildFilters.Count = 0 then Exit;
    
    // Validate each child
    for var Child in Group.ChildFilters do
    begin
      if Child.IsGroup then
      begin
        if not ValidateGroup(Child) then Exit;
      end
      else
      begin
        if not Child.ValidateFilter then Exit;
      end;
    end;
    
    Result := True;
  end;
  
begin
  Result := True;
  
  // Must have at least one filter
  if FRootFilters.Count = 0 then
  begin
    Result := False;
    Exit;
  end;
  
  // Validate all root filters
  for var Filter in FRootFilters do
  begin
    if Filter.IsGroup then
    begin
      if not ValidateGroup(Filter) then
      begin
        Result := False;
        Break;
      end;
    end
    else
    begin
      if not Filter.ValidateFilter then
      begin
        Result := False;
        Break;
      end;
    end;
  end;
end;

{ TORMSearchAdvForm Implementation }

procedure TORMSearchAdvForm.SetupVisuals;
begin
  // Configure form appearance
  Font.Name := 'Segoe UI';
  Font.Size := 9;
  
  // Style the main container
  pnlMain.FillColor := RGB(249, 250, 251);
  pnlMain.FillColorAlpha := 255;
  
  // Configure buttons
  with btnAddAndGroup do
  begin
    GlyphOptions.Size := 16;
    GlyphOptions.Index := 85; // Plus icon
    Caption := 'Add AND Group';
    Options.NormalColor := clWhite;
    Options.NormalColorAlpha := 255;
    Options.ShapeCornerRadius := 4;
  end;
  
  with btnAddOrGroup do
  begin
    GlyphOptions.Size := 16;
    GlyphOptions.Index := 85; // Plus icon
    Caption := 'Add OR Group';
    Options.NormalColor := clWhite;
    Options.NormalColorAlpha := 255;
    Options.ShapeCornerRadius := 4;
  end;
  
  with btnClearAll do
  begin
    GlyphOptions.Size := 16;
    GlyphOptions.Index := 13; // X icon
    Caption := 'Clear All';
    Options.NormalColor := clWhite;
    Options.NormalColorAlpha := 255;
    Options.ShapeCornerRadius := 4;
    Enabled := False;
  end;
  
  with btnApply do
  begin
    GlyphOptions.Size := 16;
    GlyphOptions.Index := 2; // Search icon
    Caption := 'Apply Filters';
    Options.NormalColor := RGB(79, 70, 229);
    Options.NormalColorAlpha := 255;
    Options.FontNormalColor := clWhite;
    Options.ShapeCornerRadius := 4;
    Enabled := False;
  end;
  
  // Configure filter container
  with pnlFilterContainer do
  begin
    Color := RGB(249, 250, 251);
    VertScrollBar.Tracking := True;
    HorzScrollBar.Visible := False;
    BorderStyle := bsNone;
  end;
  
  // Configure search grid
  with SearchGrid do
  begin
    Font.Name := 'Segoe UI';
    Font.Size := 9;
    
    FixedFont.Name := 'Segoe UI';
    FixedFont.Size := 9;
    FixedFont.Style := [fsBold];
    
    DefaultRowHeight := 28;
    FixedRows := 1;
    
    Options := [goFixedVertLine, goFixedHorzLine, goVertLine, goHorzLine, 
                goRangeSelect, goRowSelect];
  end;
end;

procedure TORMSearchAdvForm.InitializeSearch(
  AConnection: TUniConnection;
  const ABaseQuery: string;
  const ASearchColumns: array of TSearchAdvColumn;
  ARecordClass: TClass = nil);
begin
  FConnection := AConnection;
  FBaseQuery := ABaseQuery;
  FRecordClass := ARecordClass;
  
  // Clear existing columns
  FSearchColumns.Clear;
  
  // Copy search columns
  for var Column in ASearchColumns do
    FSearchColumns.Add(TSearchAdvColumn.Create(
      Column.FieldName,
      Column.DisplayName,
      Column.Width,
      Column.Visible,
      Column.UsableAsFilter
    ));
    
  SetupFieldTypes;
  SetupGrid;
  LoadData;
end;

procedure TORMSearchAdvForm.SetupFieldTypes;
begin
  if not Assigned(FQuery) then
    FQuery := TUniQuery.Create(nil);
    
  try
    FQuery.Connection := FConnection;
    FQuery.SQL.Text := FBaseQuery + ' WHERE 1=0';
    FQuery.Open;
    
    FFieldTypes.Clear;
    for var Column in FSearchColumns do
    begin
      var Field := FQuery.FindField(Column.FieldName);
      if Assigned(Field) then
      begin
        case Field.DataType of
          ftString, ftWideString, ftMemo, ftWideMemo:
            Column.DataType := tiString;
          ftSmallint, ftInteger, ftWord, ftLongWord, ftShortint:
            Column.DataType := tiInteger;
          ftFloat, ftCurrency, ftBCD:
            Column.DataType := tiFloat;
          ftDate:
            Column.DataType := tiDate;
          ftDateTime, ftTimeStamp:
            Column.DataType := tiDateTime;
          ftBoolean:
            Column.DataType := tiBoolean;
          else
            Column.DataType := tiString;
        end;
        FFieldTypes.Add(Column.FieldName, Column.DataType);
      end;
    end;
  finally
    FQuery.Close;
  end;
end;

procedure TORMSearchAdvForm.SetupGrid;
begin
  SearchGrid.BeginUpdate;
  try
    // Setup columns
    SearchGrid.ColCount := FSearchColumns.Count;
    SearchGrid.FixedRows := 1;
    
for var i := 0 to FSearchColumns.Count - 1 do
begin
  with SearchGrid do
  begin
    // Set column header
    Cells[i, 0] := FSearchColumns[i].DisplayName;

    // Configure column width
    ColWidths[i] := FSearchColumns[i].Width;

    // Hide column if not visible
    if not FSearchColumns[i].Visible then
      ColWidths[i] := 0;

    // Set column alignment based on data type
    case FSearchColumns[i].DataType of
      tiInteger, tiFloat, tiMoney:
        Columns[i].Alignment := taRightJustify;
      tiDate, tiDateTime:
        Columns[i].Alignment := taCenter;
      else
        Columns[i].Alignment := taLeftJustify;
    end;
  end;
end;
  finally
    SearchGrid.EndUpdate;
  end;
end;

procedure TORMSearchAdvForm.LoadData;
begin
  if not Assigned(FQuery) then
    FQuery := TUniQuery.Create(nil);
    
  Screen.Cursor := crHourGlass;
  try
    FQuery.Close;
    FQuery.SQL.Text := GetFilteredSQL;
    FQuery.Connection := FConnection;
    FQuery.Open;

    UpdateGridData;
    UpdateControlStates;
  finally
    Screen.Cursor := crDefault;
  end;
end;

function TORMSearchAdvForm.GetFilteredSQL: string;
var
  FilterSQL: string;
begin
  Result := FBaseQuery;
  FilterSQL := FFilterManager.BuildFilterSQL;
  
  if FilterSQL <> '' then
  begin
    if Pos('WHERE', UpperCase(Result)) > 0 then
      Result := Result + ' AND (' + FilterSQL + ')'
    else
      Result := Result + ' WHERE ' + FilterSQL;
  end;
end;

procedure TORMSearchAdvForm.UpdateGridData;
begin
  SearchGrid.BeginUpdate;
  try
    SearchGrid.RowCount := FQuery.RecordCount + SearchGrid.FixedRows;
    
    var Row := SearchGrid.FixedRows;
    FQuery.First;
    
    while not FQuery.Eof do
    begin
      for var i := 0 to FSearchColumns.Count - 1 do
      begin
        var Field := FQuery.FieldByName(FSearchColumns[i].FieldName);
        if Assigned(Field) then
        begin
          if Field.IsNull then
            SearchGrid.Cells[i, Row] := ''
          else
            case FSearchColumns[i].DataType of
              tiDate:
                SearchGrid.Cells[i, Row] := FormatDateTime('dd/mm/yyyy', Field.AsDateTime);
              tiDateTime:
                SearchGrid.Cells[i, Row] := FormatDateTime('dd/mm/yyyy hh:nn:ss', Field.AsDateTime);
              tiFloat, tiMoney:
                SearchGrid.Cells[i, Row] := FormatFloat('#,##0.00', Field.AsFloat);
              else
                SearchGrid.Cells[i, Row] := Field.AsString;
            end;
        end;
      end;
      
      Inc(Row);
      FQuery.Next;
    end;
  finally
    SearchGrid.EndUpdate;
  end;
end;

procedure TORMSearchAdvForm.FormCreate(Sender: TObject);
begin
  SetupVisuals;
end;

procedure TORMSearchAdvForm.FormDestroy(Sender: TObject);
begin
  // Cleanup handled in destructor
end;

procedure TORMSearchAdvForm.btnAddAndGroupClick(Sender: TObject);
begin
  FFilterManager.CreateFilterGroup(fgtAnd);
  UpdateControlStates;
end;

procedure TORMSearchAdvForm.btnAddOrGroupClick(Sender: TObject);
begin
  FFilterManager.CreateFilterGroup(fgtOr);
  UpdateControlStates;
end;

procedure TORMSearchAdvForm.btnAddFilterClick(Sender: TObject);
begin
  FFilterManager.CreateFilter;
  UpdateControlStates;
end;

procedure TORMSearchAdvForm.btnClearAllClick(Sender: TObject);
begin
  FFilterManager.FRootFilters.Clear;
  FFilterManager.UpdateLayout;
  LoadData;
  UpdateControlStates;
end;

procedure TORMSearchAdvForm.btnApplyClick(Sender: TObject);
begin
  if ValidateFilters then
  begin
    LoadData;
    UpdateControlStates;
  end;
end;

procedure TORMSearchAdvForm.UpdateControlStates;
var
  HasFilters: Boolean;
begin
  HasFilters := FFilterManager.FRootFilters.Count > 0;
  btnApply.Enabled := HasFilters;
  btnClearAll.Enabled := HasFilters;
end;

function TORMSearchAdvForm.ValidateFilters: Boolean;
begin
  Result := FFilterManager.ValidateFilters;
  if not Result then
  begin
    MessageDlg('Please ensure all filters are properly configured.',
      mtWarning, [mbOK], 0);
  end;
end;

procedure TORMSearchAdvForm.SearchGridDblClick(Sender: TObject);
begin
  if SearchGrid.Row >= SearchGrid.FixedRows then
  begin
    UpdateSelectedSearch;
    ModalResult := mrOk;
  end;
end;

procedure TORMSearchAdvForm.UpdateSelectedSearch;
begin
  if SearchGrid.Row < SearchGrid.FixedRows then Exit;
  
  // Create new selected search
  FreeAndNil(FSelectedSearch);
  FSelectedSearch := TSelectedAdvSearch.Create;
  
  // Calculate record number
  var RecNo := SearchGrid.Row - SearchGrid.FixedRows;
  
  // Move query to correct record
  FQuery.First;
  for var i := 0 to RecNo - 1 do
    FQuery.Next;
    
  // Get values from the record
  for var i := 0 to FSearchColumns.Count - 1 do
  begin
    var Field := FQuery.FieldByName(FSearchColumns[i].FieldName);
    if Assigned(Field) then
    begin
      var Value: TValue;
      if Field.IsNull then
        Value := TValue.Empty
      else
        case Field.DataType of
          ftString, ftWideString:
            Value := TValue.From<string>(Field.AsString);
          ftInteger, ftSmallint:
            Value := TValue.From<Integer>(Field.AsInteger);
          ftFloat, ftCurrency:
            Value := TValue.From<Double>(Field.AsFloat);
          ftDateTime, ftDate:
            Value := TValue.From<TDateTime>(Field.AsDateTime);
          ftBoolean:
            Value := TValue.From<Boolean>(Field.AsBoolean);
          else
            Value := TValue.From<string>(Field.AsString);
        end;
        
      FSelectedSearch.SetValue(
        FSearchColumns[i].FieldName,
        Value,
        SearchGrid.Cells[i, SearchGrid.Row]
      );
    end;
  end;
end;

class function TORMSearchAdvForm.Execute(
  AConnection: TUniConnection;
  const ABaseQuery: string;
  const ASearchColumns: array of TSearchAdvColumn;
  var ASelectedSearch: TSelectedAdvSearch;
  ARecordClass: TClass = nil): Boolean;
var
  SearchForm: TORMSearchAdvForm;
begin
  Result := False;
  SearchForm := TORMSearchAdvForm.Create(nil);
  try
    SearchForm.InitializeSearch(AConnection, ABaseQuery, ASearchColumns, ARecordClass);
    
    if SearchForm.ShowModal = mrOk then
    begin
      ASelectedSearch := SearchForm.FSelectedSearch;
      SearchForm.FSelectedSearch := nil; // Prevent double free
      Result := True;
    end;
  finally
    SearchForm.Free;
  end;
end;

{ TORMSearchAdvForm Implementation }

constructor TORMSearchAdvForm.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FSearchColumns := TObjectList<TSearchAdvColumn>.Create(True);
  FFieldTypes := TDictionary<string, TORMFormatType>.Create;
  FFilterManager := TFilterManager.Create(Self);
  SetupVisuals;
end;

destructor TORMSearchAdvForm.Destroy;
begin
  FreeAndNil(FFilterManager);
  FreeAndNil(FSearchColumns);
  FreeAndNil(FFieldTypes);
  FreeAndNil(FQuery);
  inherited;
end;

{ TSearchAdvColumn }

constructor TSearchAdvColumn.Create(const AFieldName, ADisplayName: string; AWidth: Integer;
  AVisible: Boolean = True; AUsableAsFilter: Boolean = True);
begin
  inherited Create;
  FFieldName := AFieldName;
  FDisplayName := ADisplayName;
  FWidth := AWidth;
  FVisible := AVisible;
  FUsableAsFilter := AUsableAsFilter;
  FDataType := tiString;
end;

{ TSelectedAdvSearch }

constructor TSelectedAdvSearch.Create;
begin
  inherited Create;
  FValues := TDictionary<string, TValue>.Create;
  FDisplayValues := TDictionary<string, string>.Create;
end;

destructor TSelectedAdvSearch.Destroy;
begin
  FValues.Free;
  FDisplayValues.Free;
  inherited;
end;

procedure TSelectedAdvSearch.SetValue(const AFieldName: string; const AValue: TValue;
  const ADisplayValue: string);
begin
  FValues.AddOrSetValue(AFieldName, AValue);
  FDisplayValues.AddOrSetValue(AFieldName, ADisplayValue);
end;

function TSelectedAdvSearch.GetValue(const AFieldName: string): TValue;
begin
  if not FValues.TryGetValue(AFieldName, Result) then
    Result := TValue.Empty;
end;

function TSelectedAdvSearch.GetDisplayValue(const AFieldName: string): string;
begin
  if not FDisplayValues.TryGetValue(AFieldName, Result) then
    Result := '';
end;

{ TFilterPanel }

procedure TFilterPanel.FieldComboChange(Sender: TObject);
var
  Form: TORMSearchAdvForm;
begin
  if not Assigned(FFieldCombo) then Exit;

  Form := TORMSearchAdvForm(FPanel.Owner);
  // Update the filter info with new field
  for var Column in Form.FSearchColumns do
  begin
    if Column.DisplayName = FFieldCombo.Text then
    begin
      FFilterInfo.FieldName := Column.FieldName;
      FFilterInfo.DataType := Column.DataType;
      Break;
    end;
  end;

  UpdateOperators;
  UpdateValueControls;
end;

procedure TFilterPanel.OperatorComboChange(Sender: TObject);
begin
  if FOperatorCombo.Text =
    'Equals' then FFilterInfo.OperationType := fotEqual
  else if FOperatorCombo.Text =
    'Not Equals' then FFilterInfo.OperationType := fotNotEqual
  else if FOperatorCombo.Text =
    'Greater Than'then FFilterInfo.OperationType := fotGreaterThan
  else if FOperatorCombo.Text =
    'Less Than' then FFilterInfo.OperationType := fotLessThan
  else if FOperatorCombo.Text =
    'Greater or Equal' then FFilterInfo.OperationType := fotGreaterEqual
  else if FOperatorCombo.Text =
    'Less or Equal' then FFilterInfo.OperationType := fotLessEqual
  else if FOperatorCombo.Text =
    'Between' then FFilterInfo.OperationType := fotBetween
  else if FOperatorCombo.Text =
    'Contains' then FFilterInfo.OperationType := fotContains
  else if FOperatorCombo.Text =
    'Starts With' then FFilterInfo.OperationType := fotStartsWith
  else if FOperatorCombo.Text =
    'Ends With' then FFilterInfo.OperationType := fotEndsWith
  else if FOperatorCombo.Text =
    'Is Empty' then FFilterInfo.OperationType := fotIsNull
  else if FOperatorCombo.Text =
    'Is Not Empty' then FFilterInfo.OperationType := fotIsNotNull;

  UpdateValueControls;
end;

procedure TFilterPanel.UpdateOperators;
begin
  if not Assigned(FOperatorCombo) then Exit;

  FOperatorCombo.Items.Clear;

  // Common operators for all types
  FOperatorCombo.Items.Add('Is Empty');
  FOperatorCombo.Items.Add('Is Not Empty');

  case FFilterInfo.DataType of
    tiString:
      begin
        FOperatorCombo.Items.AddStrings(['Equals', 'Not Equals',
          'Contains', 'Starts With', 'Ends With']);
      end;

    tiInteger, tiFloat, tiMoney:
      begin
        FOperatorCombo.Items.AddStrings(['Equals', 'Not Equals',
          'Greater Than', 'Less Than', 'Greater or Equal',
          'Less or Equal', 'Between']);
      end;

    tiDate, tiDateTime:
      begin
        FOperatorCombo.Items.AddStrings(['Equals', 'Not Equals',
          'Greater Than', 'Less Than', 'Greater or Equal',
          'Less or Equal', 'Between']);
      end;

    tiBoolean:
      begin
        FOperatorCombo.Items.AddStrings(['Equals', 'Not Equals']);
      end;
  end;

  if FOperatorCombo.Items.Count > 0 then
    FOperatorCombo.ItemIndex := 0;
end;

procedure TFilterPanel.UpdateValueControls;
var
  ShowValue1, ShowValue2: Boolean;
begin
  if not (Assigned(FValueEdit) and Assigned(FDateValue)) then Exit;

  // Determine which controls to show
  ShowValue1 := not (FFilterInfo.OperationType in [fotIsNull, fotIsNotNull]);
  ShowValue2 := FFilterInfo.OperationType = fotBetween;

  // Show appropriate controls based on data type
  case FFilterInfo.DataType of
    tiDate, tiDateTime:
      begin
        FDateValue.Visible := ShowValue1;
        FDateValue2.Visible := ShowValue2;
        FValueEdit.Visible := False;
        FValueEdit2.Visible := ShowValue2;
      end;
    else
      begin
        FDateValue.Visible := False;
        FDateValue2.Visible := False;
        FValueEdit.Visible := ShowValue1;
        FValueEdit2.Visible := ShowValue2;
      end;
  end;
end;

function TFilterPanel.FormatValue(const Value: Variant): string;
begin
  if VarIsNull(Value) or VarIsEmpty(Value) then
    Exit('NULL');

  case FFilterInfo.DataType of
    tiString:
      Result := QuotedStr(VarToStr(Value));

    tiInteger:
      Result := Value;

    tiFloat, tiMoney:
      Result := FormatFloat('#,##0.00', Value);

    tiDate:
      Result := QuotedStr(FormatDateTime('yyyy-mm-dd', VarToDateTime(Value)));

    tiDateTime:
      Result := QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', VarToDateTime(Value)));

    tiBoolean:
      Result := IfThen(Boolean(Value), 'TRUE', 'FALSE');
  end;
end;

function TFilterPanel.GetFilterInfo: TFilterValueInfo;
begin
  // Update values before returning
  if FFilterInfo.DataType in [tiDate, tiDateTime] then
  begin
    if FDateValue.Visible then
      FFilterInfo.Value1 := FDateValue.Date;
    if (FFilterInfo.OperationType = fotBetween) and FDateValue2.Visible then
      FFilterInfo.Value2 := FDateValue2.Date;
  end
  else
  begin
    if FValueEdit.Visible then
      FFilterInfo.Value1 := FValueEdit.Text;
    if (FFilterInfo.OperationType = fotBetween) and FValueEdit2.Visible then
      FFilterInfo.Value2 := FValueEdit2.Text;
  end;

  Result := FFilterInfo;
end;

{ TORMSearchAdvForm }

procedure TORMSearchAdvForm.UpdateLayout;
begin
  if Assigned(FFilterManager) then
    FFilterManager.UpdateLayout;
end;

procedure TORMSearchAdvForm.BuildFilter;
begin
  LoadData;
end;

{ TFilterManager }

procedure TFilterManager.UpdateFilterLayout;
begin
  var TotalHeight := 0;

  for var Filter in FRootFilters do
  begin
    Filter.SetPosition(TotalHeight);
    Inc(TotalHeight, Filter.CalculateHeight + 4); // 4px spacing
    Filter.UpdateLayout;
  end;

  // Update container size
  if TotalHeight > 0 then
    Inc(TotalHeight, 8); // Add margins

  FForm.pnlFilterContainer.Height := Max(TotalHeight, 100);

  // Force visual update
  FForm.pnlFilterContainer.Invalidate;
  Application.ProcessMessages;
end;

procedure TFilterPanel.SetupGroupControls(PanelSpacing, ButtonSize, ControlHeight: Integer);
begin
  // Expand/collapse button
  FExpandButton := TscGPCharGlyphButton.Create(FPanel);
  with FExpandButton do
  begin
    Parent := FPanel;
    Width := ButtonSize;
    Height := ButtonSize;
    Left := FDragHandle.Left + FDragHandle.Width + PanelSpacing;
    Top := (FPanel.Height - Height) div 2;

    GlyphOptions.Index := IfThen(FIsExpanded, 257, 261); // Down/Right chevron
    GlyphOptions.NormalColor := RGB(107, 114, 128);
    GlyphOptions.Size := 16;

    OnClick := ExpandButtonClick;
  end;

  // Group type button (AND/OR)
  FGroupTypeButton := TscGPCharGlyphButton.Create(FPanel);
  with FGroupTypeButton do
  begin
    Parent := FPanel;
    Width := 60;
    Height := ControlHeight;
    Left := FExpandButton.Left + FExpandButton.Width + PanelSpacing;
    Top := (FPanel.Height - Height) div 2;
    ShowCaption := True;
    Caption := IfThen(GroupType = fgtAnd, 'AND', 'OR');
    Font.Style := [fsBold];

    Options.ShapeStyle := scgpRect;
    Options.ShapeCornerRadius := 4;
    Options.NormalColor := IfThen(GroupType = fgtAnd,
      RGB(238, 242, 255),  // Light indigo for AND
      RGB(254, 242, 242)); // Light red for OR
    Options.NormalColorAlpha := 255;
    Options.FontNormalColor := IfThen(GroupType = fgtAnd,
      RGB(79, 70, 229),    // Indigo for AND
      RGB(220, 38, 38));   // Red for OR

    OnClick := GroupTypeButtonClick;
  end;

  // Content panel for child filters
  FContentPanel := TscGPPanel.Create(FPanel);
  with FContentPanel do
  begin
    Parent := FPanel;
    Left := INDENT_WIDTH;
    Top := FPanel.Height;
    Width := FPanel.Width - Left - PanelSpacing;
    Align := alCustom;
    Visible := FIsExpanded;

    FluentUIOpaque := False;
    StorePaintBuffer := True;
    FillColor := clWhite;
    FillColorAlpha := 50;
    FrameWidth := 0;
  end;
end;

procedure TFilterPanel.SetupFilterControls(PanelSpacing, ButtonSize, ControlHeight: Integer);
const
  FIELD_WIDTH = 150;
  OPERATOR_WIDTH = 120;
  VALUE_WIDTH = 150;
begin
  // Field selector
  FFieldCombo := TscComboBox.Create(FPanel);
  with FFieldCombo do
  begin
    Parent := FPanel;
    Width := FIELD_WIDTH;
    Height := ControlHeight;
    Left := FDragHandle.Left + FDragHandle.Width + PanelSpacing;
    Top := (FPanel.Height - Height) div 2;
    Style := csDropDownList;

    // Style the dropdown
    StyleKind := scscbPushButton;
    {
    Options.ShapeCornerRadius := 4;
    Options.FrameNormalColor := RGB(209, 213, 219);
    Options.FrameWidth := 1;
    }
    // Populate with available fields
    Items.Clear;
    for var Column in TORMSearchAdvForm(FPanel.Owner).FSearchColumns do
      if Column.UsableAsFilter then
        Items.Add(Column.DisplayName);

    if Items.Count > 0 then
      ItemIndex := 0;

    OnChange := FieldComboChange;
  end;

  // Operator selector
  FOperatorCombo := TscComboBox.Create(FPanel);
  with FOperatorCombo do
  begin
    Parent := FPanel;
    Width := OPERATOR_WIDTH;
    Height := ControlHeight;
    Left := FFieldCombo.Left + FFieldCombo.Width + PanelSpacing;
    Top := (FPanel.Height - Height) div 2;
    Style := csDropDownList;

    StyleKind := scscbPushButton;
    {
    Options.ShapeCornerRadius := 4;
    Options.FrameNormalColor := RGB(209, 213, 219);
    Options.FrameWidth := 1;
    }
    OnChange := OperatorComboChange;
  end;

  // Value input
  FValueEdit := TscGPEdit.Create(FPanel);
  with FValueEdit do
  begin
    Parent := FPanel;
    Width := VALUE_WIDTH;
    Height := ControlHeight;
    Left := FOperatorCombo.Left + FOperatorCombo.Width + PanelSpacing;
    Top := (FPanel.Height - Height) div 2;

    Options.ShapeCornerRadius := 4;
    Options.NormalColor := clWhite;
    Options.FrameNormalColor := RGB(209, 213, 219);
    Options.FrameWidth := 1;
  end;

  // Date picker (initially hidden)
  FDateValue := TscGPDateEdit.Create(FPanel);
  with FDateValue do
  begin
    Parent := FPanel;
    Width := VALUE_WIDTH;
    Height := ControlHeight;
    Left := FValueEdit.Left;
    Top := FValueEdit.Top;
    Visible := False;

    Options.ShapeCornerRadius := 4;
    Options.NormalColor := clWhite;
    Options.FrameNormalColor := RGB(209, 213, 219);
    Options.FrameWidth := 1;
  end;

  // Second Value input (hidden by default)
  FValueEdit2 := TscGPEdit.Create(FPanel);
  with FValueEdit2 do
  begin
    Parent := FPanel;
    Width := VALUE_WIDTH;
    Height := ControlHeight;
    Left := FValueEdit.Left + FValueEdit.Width + PanelSpacing;
    Top := FValueEdit.Top;

    Options.ShapeCornerRadius := 4;
    Options.NormalColor := clWhite;
    Options.FrameNormalColor := RGB(209, 213, 219);
    Options.FrameWidth := 1;
    Visible := False;
  end;

  // Date picker for second value (hidden by default)
  FDateValue2 := TscGPDateEdit.Create(FPanel);
  with FDateValue2 do
  begin
    Parent := FPanel;
    Width := VALUE_WIDTH;
    Height := ControlHeight;
    Left := FDateValue.Left + FDateValue.Width + PanelSpacing;
    Top := FDateValue.Top;
    Visible := False;

    Options.ShapeCornerRadius := 4;
    Options.NormalColor := clWhite;
    Options.FrameNormalColor := RGB(209, 213, 219);
    Options.FrameWidth := 1;
  end;
end;

end.

---

carefully review the unit above and check for bugs