unit orm_search;

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, 
  AdvCGrid, Vcl.ComCtrls, Vcl.WinXPickers, orm_utils, ORM_CustomAttributes,
  System.Rtti, System.DateUtils, DM1, Data.DB, Uni, Vcl.Grids, AdvObj,
  BaseGrid, Vcl.Mask, pixel_utils, interposer_class, pixel_classes;

const
  FILTER_PANEL_HEIGHT = 35;
  FILTER_PANEL_SPACING = 5;
  MIN_ACTIVE_FILTERS_HEIGHT = 80;
  MAX_ACTIVE_FILTERS_HEIGHT = 200;
  PANEL_MARGIN = 5;
  GRID_MIN_HEIGHT = 200;

type
  TSearchColumn = 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;

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

  TActiveFilter = class
  private
    FFieldName: string;
    FDisplayName: string;
    FFilterOperator: TFilterOperator;
    FValue1: string;
    FValue2: string;
    FFilterPanel: TscGPPanel;
    FFilterLabel: TLabel;
    FDeleteButton: TscGPCharGlyphButton;
    FDataType: TORMFormatType;
    FIsOrCondition: Boolean;
    FGroupLevel: Integer;
    FGroupStarted: Boolean;
    FGroupEnded: Boolean;
  public
    constructor Create;
    destructor Destroy; override;
    property FieldName: string read FFieldName write FFieldName;
    property DisplayName: string read FDisplayName write FDisplayName;
    property FilterOperator: TFilterOperator read FFilterOperator write FFilterOperator;
    property Value1: string read FValue1 write FValue1;
    property Value2: string read FValue2 write FValue2;
    property FilterPanel: TscGPPanel read FFilterPanel write FFilterPanel;
    property FilterLabel: TLabel read FFilterLabel write FFilterLabel;
    property DeleteButton: TscGPCharGlyphButton read FDeleteButton write FDeleteButton;
    property DataType: TORMFormatType read FDataType write FDataType;
    property IsOrCondition: Boolean read FIsOrCondition write FIsOrCondition;
    property GroupStarted: Boolean read FGroupStarted write FGroupStarted;
    property GroupEnded: Boolean read FGroupEnded write FGroupEnded;
    function ToSQL: string;
  end;

  TORMSearchForm = class(TForm)
    pnlTop: TPanel;
    pnlFilters: TscGPPanel;
    pnlActiveFilters: TscGPPanel;
    pnlGrid: TsPanel;
    pnlBottom: TPanel;
    
    cboField: TscComboBox;
    cboOperator: TscComboBox;
    edtValue1: TscGPEdit;
    edtValue2: TscGPEdit;
    dateValue1: TscGPDateEdit;
    dateValue2: TscGPDateEdit;
    btnAddFilter: TscGPCharGlyphButton;
    
    SearchGrid: TAdvColumnGrid;
    
    btnSearch: TscGPCharGlyphButton;
    btnClose: TscGPCharGlyphButton;
    btnClearFilters: TscGPCharGlyphButton;
    
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure cboFieldChange(Sender: TObject);
    procedure cboOperatorChange(Sender: TObject);
    procedure btnAddFilterClick(Sender: TObject);
    procedure btnSearchClick(Sender: TObject);
    procedure btnCloseClick(Sender: TObject);
    procedure btnClearFiltersClick(Sender: TObject);
    procedure SearchGridDblClick(Sender: TObject);
    procedure FilterDeleteButtonClick(Sender: TObject);

  private
    FActiveFilters: TObjectList<TActiveFilter>;
    FSearchColumns: TObjectList<TSearchColumn>;
    FSelectedSearch: TSelectedSearch;
    FBaseQuery: string;
    FConnection: TUniConnection;
    FQuery: TUniQuery;
    FIsORMMode: Boolean;
    FORMFilter: TORMFilter;
    FFieldTypes: TDictionary<string, TORMFormatType>;
    FAnimation: TTimer;
    FTargetHeight: Integer;
    
    procedure SetupGrid;
    procedure UpdateValueControls;
    procedure RemoveActiveFilter(Filter: TActiveFilter);
    procedure ArrangeFilterPanels;
    procedure ClearActiveFilters;
    procedure UpdatePanelLayouts;
    procedure AdjustFilterPanelHeight;
    procedure CreateFilterPanel(Filter: TActiveFilter);
    procedure LoadData;
    procedure SetupFieldTypes;
    procedure PopulateOperators(ADataType: TORMFormatType);
    function BuildWhereClause: string;
    function GetFilteredSQL: string;
    procedure UpdateSelectedSearch;
    function GetSelectedRow: Integer;
    procedure SetupColumns;
    procedure UpdateGridData;
    function ValidateFilterValue(const Value: string; DataType: TORMFormatType): Boolean;
    procedure SetupORMFilter;

    procedure SetupVisualStyles;
    procedure ValidateInputs;
    procedure ShowValidationError(const Msg: string);
    procedure UpdateControlStates;
    function GetOperatorDisplayText(AOperator: TFilterOperator): string;
    
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    
    class function Execute(AConnection: TUniConnection; const ABaseQuery: string;
      const ASearchColumns: array of TSearchColumn; var ASelectedSearch: TSelectedSearch): Boolean;
    
    procedure InitializeSearch(AConnection: TUniConnection; const ABaseQuery: string;
      const ASearchColumns: array of TSearchColumn);
  end;

var
  ORMSearchForm: TORMSearchForm;

implementation

uses Math;

{$R *.dfm}

procedure LogToFile(const Msg: string);
var
  LogFile: TextFile;
  LogFileName: string;
begin
  LogFileName := ExtractFilePath(ParamStr(0)) + 'orm_search.log';
  AssignFile(LogFile, LogFileName);
  try
    if FileExists(LogFileName) then
      Append(LogFile)
    else
      Rewrite(LogFile);
    WriteLn(LogFile, Format('[%s] %s', [FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz', Now), Msg]));
  finally
    CloseFile(LogFile);
  end;
end;

{ TSearchColumn }

constructor TSearchColumn.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; // Default, will be updated when field types are loaded
end;

{ TSelectedSearch }

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

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

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

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

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

constructor TORMSearchForm.Create(AOwner: TComponent);
begin
  inherited;
  FActiveFilters := TObjectList<TActiveFilter>.Create(True);
  FSearchColumns := TObjectList<TSearchColumn>.Create(True);
  FFieldTypes := TDictionary<string, TORMFormatType>.Create;
  FIsORMMode := False;

  SetupVisualStyles;
end;

procedure TORMSearchForm.SetupVisualStyles;
begin
  // Panel styling
  pnlActiveFilters.Padding.Left := PANEL_MARGIN;
  pnlActiveFilters.Padding.Right := PANEL_MARGIN;
  pnlActiveFilters.Padding.Top := PANEL_MARGIN;
  pnlActiveFilters.Padding.Bottom := PANEL_MARGIN;

  // Grid styling
  SearchGrid.Constraints.MinHeight := GRID_MIN_HEIGHT;

  // Input controls
  edtValue1.Options.ShapeCornerRadius := 3;
  edtValue2.Options.ShapeCornerRadius := 3;
  dateValue1.Options.ShapeCornerRadius := 3;
  dateValue2.Options.ShapeCornerRadius := 3;

  // Buttons
  btnAddFilter.Options.ShapeCornerRadius := 3;
  btnSearch.Options.ShapeCornerRadius := 3;
  btnClearFilters.Options.ShapeCornerRadius := 3;
  btnClose.Options.ShapeCornerRadius := 3;
end;

procedure TORMSearchForm.ValidateInputs;
var
  ErrorMsg: string;
  IsValid: Boolean;
  SelectedColumn: TSearchColumn;
  FieldType: TORMFormatType;
begin
  IsValid := True;
  ErrorMsg := '';

  if cboField.ItemIndex < 0 then
  begin
    ErrorMsg := 'Please select a field';
    IsValid := False;
  end
  else if cboOperator.ItemIndex < 0 then
  begin
    ErrorMsg := 'Please select an operator';
    IsValid := False;
  end
  else
  begin
    // Find the selected column to get its field name
    SelectedColumn := nil;
    for var Column in FSearchColumns do
    begin
      if Column.DisplayName = cboField.Text then
      begin
        SelectedColumn := Column;
        Break;
      end;
    end;

    if Assigned(SelectedColumn) and FFieldTypes.TryGetValue(SelectedColumn.FieldName, FieldType) then
    begin
      case FieldType of
        tiInteger, tiFloat, tiMoney:
          begin
            if not ValidateFilterValue(edtValue1.Text, FieldType) then
            begin
              ErrorMsg := 'Please enter a valid number';
              IsValid := False;
            end
            else if (cboOperator.Text = 'Between') and
                    not ValidateFilterValue(edtValue2.Text, FieldType) then
            begin
              ErrorMsg := 'Please enter a valid number for the second value';
              IsValid := False;
            end;
          end;

        tiDate, tiDateTime:
          begin
            if dateValue1.Visible and (dateValue1.Date = 0) then
            begin
              ErrorMsg := 'Please enter a valid date';
              IsValid := False;
            end
            else if dateValue2.Visible and (dateValue2.Date = 0) then
            begin
              ErrorMsg := 'Please enter a valid date for the second value';
              IsValid := False;
            end;
          end;
      end;
    end
    else
    begin
      ErrorMsg := 'Invalid field type configuration';
      IsValid := False;
    end;
  end;

  if not IsValid then
    ShowValidationError(ErrorMsg);

  btnAddFilter.Enabled := IsValid;
end;

procedure TORMSearchForm.ShowValidationError(const Msg: string);
begin
  // Implement visual feedback for validation errors
  // Could show error icon, highlight invalid fields, etc.
  MessageDlg(Msg, mtError, [mbOK], 0);
end;

procedure TORMSearchForm.UpdateControlStates;
begin
  btnClearFilters.Enabled := FActiveFilters.Count > 0;
  btnSearch.Enabled := FActiveFilters.Count > 0;

  ValidateInputs;
end;

{ TActiveFilter }

constructor TActiveFilter.Create;
begin
  inherited;
  FIsOrCondition := False;
  FGroupStarted := False;
  FGroupEnded := False;
  FGroupLevel := 0;
  FFilterOperator := foEqual;
  FDataType := tiString;
end;

destructor TActiveFilter.Destroy;
begin
  try
    // Free the panel if it exists
    if Assigned(FFilterPanel) then
    begin
      LogToFile(Format('Destroying filter panel %s', [FFilterPanel.Name]));
      FFilterPanel.Free;
      FFilterPanel := nil;
    end;
  except
    on E: Exception do
      LogToFile(Format('Error in TActiveFilter.Destroy: %s', [E.Message]));
  end;

  inherited;
end;

function TActiveFilter.ToSQL: string;
var
  FieldExpression: string;
begin
  FieldExpression := FFieldName;

  case FilterOperator of
    foEqual:
      if Value1 = '' then
        Result := Format('%s IS NULL', [FieldExpression])
      else case DataType of
        tiString: Result := Format('%s = %s', [FieldExpression, QuotedStr(Value1)]);
        tiInteger: Result := Format('%s = %s', [FieldExpression, Value1]);
        tiFloat, tiMoney: Result := Format('%s = %s', [FieldExpression, Value1]);
        tiDate: Result := Format('%s = %s', [FieldExpression, QuoteDate_SQL(Value1)]);
        tiDateTime: Result := Format('%s = %s', [FieldExpression, QuoteDateTime_SQL(Value1)]);
        else Result := Format('%s = %s', [FieldExpression, QuotedStr(Value1)]);
      end;

    foNotEqual:
      if Value1 = '' then
        Result := Format('%s IS NOT NULL', [FieldExpression])
      else case DataType of
        tiString: Result := Format('%s <> %s', [FieldExpression, QuotedStr(Value1)]);
        tiInteger: Result := Format('%s <> %s', [FieldExpression, Value1]);
        tiFloat, tiMoney: Result := Format('%s <> %s', [FieldExpression, Value1]);
        tiDate: Result := Format('%s <> %s', [FieldExpression, QuoteDate_SQL(Value1)]);
        tiDateTime: Result := Format('%s <> %s', [FieldExpression, QuoteDateTime_SQL(Value1)]);
        else Result := Format('%s <> %s', [FieldExpression, QuotedStr(Value1)]);
      end;

    foGreaterThan:
      case DataType of
        tiString: Result := Format('%s > %s', [FieldExpression, QuotedStr(Value1)]);
        tiInteger: Result := Format('%s > %s', [FieldExpression, Value1]);
        tiFloat, tiMoney: Result := Format('%s > %s', [FieldExpression, Value1]);
        tiDate: Result := Format('%s > %s', [FieldExpression, QuoteDate_SQL(Value1)]);
        tiDateTime: Result := Format('%s > %s', [FieldExpression, QuoteDateTime_SQL(Value1)]);
        else Result := Format('%s > %s', [FieldExpression, QuotedStr(Value1)]);
      end;

    foLessThan:
      case DataType of
        tiString: Result := Format('%s < %s', [FieldExpression, QuotedStr(Value1)]);
        tiInteger: Result := Format('%s < %s', [FieldExpression, Value1]);
        tiFloat, tiMoney: Result := Format('%s < %s', [FieldExpression, Value1]);
        tiDate: Result := Format('%s < %s', [FieldExpression, QuoteDate_SQL(Value1)]);
        tiDateTime: Result := Format('%s < %s', [FieldExpression, QuoteDateTime_SQL(Value1)]);
        else Result := Format('%s < %s', [FieldExpression, QuotedStr(Value1)]);
      end;

    foGreaterEqual:
      case DataType of
        tiString: Result := Format('%s >= %s', [FieldExpression, QuotedStr(Value1)]);
        tiInteger: Result := Format('%s >= %s', [FieldExpression, Value1]);
        tiFloat, tiMoney: Result := Format('%s >= %s', [FieldExpression, Value1]);
        tiDate: Result := Format('%s >= %s', [FieldExpression, QuoteDate_SQL(Value1)]);
        tiDateTime: Result := Format('%s >= %s', [FieldExpression, QuoteDateTime_SQL(Value1)]);
        else Result := Format('%s >= %s', [FieldExpression, QuotedStr(Value1)]);
      end;

    foLessEqual:
      case DataType of
        tiString: Result := Format('%s <= %s', [FieldExpression, QuotedStr(Value1)]);
        tiInteger: Result := Format('%s <= %s', [FieldExpression, Value1]);
        tiFloat, tiMoney: Result := Format('%s <= %s', [FieldExpression, Value1]);
        tiDate: Result := Format('%s <= %s', [FieldExpression, QuoteDate_SQL(Value1)]);
        tiDateTime: Result := Format('%s <= %s', [FieldExpression, QuoteDateTime_SQL(Value1)]);
        else Result := Format('%s <= %s', [FieldExpression, QuotedStr(Value1)]);
      end;

    foBetween:
      case DataType of
        tiString: Result := Format('%s BETWEEN %s AND %s', [FieldExpression, QuotedStr(Value1), QuotedStr(Value2)]);
        tiInteger: Result := Format('%s BETWEEN %s AND %s', [FieldExpression, Value1, Value2]);
        tiFloat, tiMoney: Result := Format('%s BETWEEN %s AND %s', [FieldExpression, Value1, Value2]);
        tiDate: Result := Format('%s BETWEEN %s AND %s', [FieldExpression, QuoteDate_SQL(Value1), QuoteDate_SQL(Value2)]);
        tiDateTime: Result := Format('%s BETWEEN %s AND %s', [FieldExpression, QuoteDateTime_SQL(Value1), QuoteDateTime_SQL(Value2)]);
        else Result := Format('%s BETWEEN %s AND %s', [FieldExpression, QuotedStr(Value1), QuotedStr(Value2)]);
      end;

    foContains:
      Result := Format('%s LIKE ''%%%s%%''', [FieldExpression, Value1]);

    foBeginsWith:
      Result := Format('%s LIKE ''%s%%''', [FieldExpression, Value1]);

    foEndsWith:
      Result := Format('%s LIKE ''%%%s''', [FieldExpression, Value1]);

    foIsNull:
      Result := Format('%s IS NULL', [FieldExpression]);

    foIsNotNull:
      Result := Format('%s IS NOT NULL', [FieldExpression]);
  end;
end;

{ TORMSearchForm }

destructor TORMSearchForm.Destroy;
begin
  FActiveFilters.Free;
  FSearchColumns.Free;
  FFieldTypes.Free;
  FQuery.Free;
  inherited;
end;

class function TORMSearchForm.Execute(AConnection: TUniConnection; 
  const ABaseQuery: string; const ASearchColumns: array of TSearchColumn;
  var ASelectedSearch: TSelectedSearch): Boolean;
var
  SearchForm: TORMSearchForm;
begin
  Result := False;
  SearchForm := TORMSearchForm.Create(nil);
  try
    SearchForm.InitializeSearch(AConnection, ABaseQuery, ASearchColumns);
    if SearchForm.ShowModal = mrOk then
    begin
      SearchForm.UpdateSelectedSearch;
      ASelectedSearch := SearchForm.FSelectedSearch;
      Result := True;
    end;
  finally
    SearchForm.Free;
  end;
end;

procedure TORMSearchForm.InitializeSearch(AConnection: TUniConnection;
  const ABaseQuery: string; const ASearchColumns: array of TSearchColumn);
var
  i: Integer;
begin
  FConnection := AConnection;
  FBaseQuery := ABaseQuery;
  
  // Clear existing columns
  FSearchColumns.Clear;
  
  // Copy search columns
  for i := 0 to High(ASearchColumns) do
    FSearchColumns.Add(TSearchColumn.Create(
      ASearchColumns[i].FieldName,
      ASearchColumns[i].DisplayName,
      ASearchColumns[i].Width,
      ASearchColumns[i].Visible,
      ASearchColumns[i].UsableAsFilter
    ));

  SetupColumns;
  SetupFieldTypes;
  LoadData;
end;

procedure TORMSearchForm.SetupFieldTypes;
begin
  if not Assigned(FQuery) then
    FQuery := TUniQuery.Create(nil);

  try
    FQuery.Connection := FConnection;
    FQuery.SQL.Text := FBaseQuery + ' WHERE 1=0'; // Just get structure
    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 TORMSearchForm.SetupColumns;
begin
  // Setup grid columns based on search columns
  SearchGrid.ColCount := FSearchColumns.Count;
  SearchGrid.FixedRows := 1;
  
  for var i := 0 to FSearchColumns.Count - 1 do
  begin
    SearchGrid.Cells[i, 0] := FSearchColumns[i].DisplayName;
    SearchGrid.ColWidths[i] := FSearchColumns[i].Width;
    if not FSearchColumns[i].Visible then
      SearchGrid.ColWidths[i] := 0;
  end;
  
  // Setup field combo
  cboField.Items.Clear;
  for var Column in FSearchColumns do
    if Column.UsableAsFilter then
      cboField.Items.Add(Column.DisplayName);
      
  if cboField.Items.Count > 0 then
    cboField.ItemIndex := 0;
end;

procedure TORMSearchForm.cboFieldChange(Sender: TObject);
var
  SelectedColumn: TSearchColumn;
begin
  if cboField.ItemIndex < 0 then
    Exit;

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

  if Assigned(SelectedColumn) then
  begin
    PopulateOperators(SelectedColumn.DataType);
    UpdateValueControls;
  end;
end;

procedure TORMSearchForm.PopulateOperators(ADataType: TORMFormatType);
begin
  cboOperator.Items.Clear;
  
  case ADataType of
    tiString:
      begin
        cboOperator.Items.AddStrings(['Equals', 'Contains', 'Starts with', 'Ends with', 'Is empty', 'Is not empty']);
      end;
      
    tiInteger, tiFloat, tiMoney:
      begin
        cboOperator.Items.AddStrings(['Equals', 'Not equals', 'Greater than', 'Less than', 
          'Greater or equal', 'Less or equal', 'Between', 'Is empty', 'Is not empty']);
      end;
      
    tiDate, tiDateTime:
      begin
        cboOperator.Items.AddStrings(['Equals', 'Not equals', 'Greater than', 'Less than',
          'Greater or equal', 'Less or equal', 'Between', 'Is empty', 'Is not empty']);
      end;
      
    tiBoolean:
      begin
        cboOperator.Items.AddStrings(['True', 'False', 'Is empty', 'Is not empty']);
      end;
  end;
  
  if cboOperator.Items.Count > 0 then
    cboOperator.ItemIndex := 0;
end;

function TORMSearchForm.ValidateFilterValue(const Value: string; DataType: TORMFormatType): Boolean;
var
  DummyInt: Integer;
  DummyFloat: Extended;
  DummyDate: TDateTime;
begin
  if Value.Trim = '' then
  begin
    Result := True;
    Exit;
  end;

  case DataType of
    tiInteger:
      Result := TryStrToInt(Value, DummyInt);
      
    tiFloat, tiMoney:
      Result := TryStrToFloat(Value, DummyFloat);
      
    tiDate, tiDateTime:
      Result := TryStrToDateTime(Value, DummyDate);
      
    tiBoolean:
      Result := SameText(Value, 'True') or SameText(Value, 'False');
      
    else
      Result := True;
  end;
end;

procedure TORMSearchForm.UpdateValueControls;
var
  Column: TSearchColumn;
  IsBetween: Boolean;
begin
  if cboField.ItemIndex < 0 then Exit;
  
  // Find corresponding column
  for Column in FSearchColumns do
    if Column.DisplayName = cboField.Text then
    begin
      IsBetween := cboOperator.Text = 'Between';
      
      case Column.DataType of
        tiDate, tiDateTime:
          begin
            dateValue1.Visible := True;
            dateValue2.Visible := IsBetween;
            edtValue1.Visible := False;
            edtValue2.Visible := False;
          end;

        else
          begin
            dateValue1.Visible := False;
            dateValue2.Visible := False;
            edtValue1.Visible := True;
            edtValue2.Visible := IsBetween;
          end;
      end;
      Break;
    end;
end;

procedure TORMSearchForm.CreateFilterPanel(Filter: TActiveFilter);
begin
  Filter.FilterPanel := TscGPPanel.Create(pnlActiveFilters);
  with Filter.FilterPanel do
  begin
    Name := Format('FilterPanel_%d', [GetTickCount]);
    Parent := pnlActiveFilters;
    BevelOuter := bvNone;

    // Set initial size and position
    Height := FILTER_PANEL_HEIGHT;
    Align := alNone;
    Left := PANEL_MARGIN;
    Width := pnlActiveFilters.ClientWidth - (2 * PANEL_MARGIN);

    // Style settings
    FluentUIOpaque := False;
    BackgroundStyle := gppbsColor;
    FillColor := clWhite;
    FillColorAlpha := 230;
    FrameColor := clGray;
    FrameColorAlpha := 40;
    FrameRadius := 3;

    // Padding
    Padding.Left := 5;
    Padding.Right := 5;
    Padding.Top := 2;
    Padding.Bottom := 2;
  end;

  // Create label
  Filter.FilterLabel := TLabel.Create(Filter.FilterPanel);
  with Filter.FilterLabel do
  begin
    Parent := Filter.FilterPanel;
    Left := 10;
    Top := (Filter.FilterPanel.Height - Height) div 2;
    AutoSize := True;
    Caption := Format('%s %s %s', [
      Filter.DisplayName,
      GetOperatorDisplayText(Filter.FilterOperator),
      Filter.Value1 + IfThen(Filter.FilterOperator = foBetween, ' AND ' + Filter.Value2, '')
    ]);
  end;

  // Create delete button
  Filter.DeleteButton := TscGPCharGlyphButton.Create(Filter.FilterPanel);
  with Filter.DeleteButton do
  begin
    Parent := Filter.FilterPanel;
    Width := 24;
    Height := 24;
    Top := (Filter.FilterPanel.Height - Height) div 2;
    Align := alRight;
    Margins.Right := 5;
    Caption := '';

    // Button style
    Options.NormalColor := clBtnFace;
    Options.HotColor := clBtnFace;
    Options.PressedColor := clBtnFace;
    Options.FocusedColor := clBtnFace;

    // Glyph settings
    GlyphOptions.NormalColor := clGray;
    GlyphOptions.HotColor := clRed;
    GlyphOptions.PressedColor := clMaroon;
    GlyphOptions.Size := 10;
    GlyphOptions.Index := 13; // 'X' glyph

    OnClick := FilterDeleteButtonClick;
    Tag := NativeInt(Filter);
  end;

  LogToFile(Format('Created new filter panel: %s', [Filter.FilterPanel.Name]));
end;

function TORMSearchForm.GetOperatorDisplayText(AOperator: TFilterOperator): string;
begin
  case AOperator of
    foEqual: Result := '=';
    foNotEqual: Result := '<>';
    foGreaterThan: Result := '>';
    foLessThan: Result := '<';
    foGreaterEqual: Result := '>=';
    foLessEqual: Result := '<=';
    foBetween: Result := 'between';
    foContains: Result := 'contains';
    foBeginsWith: Result := 'starts with';
    foEndsWith: Result := 'ends with';
    foIsNull: Result := 'is empty';
    foIsNotNull: Result := 'is not empty';
    else Result := '=';
  end;
end;

procedure TORMSearchForm.RemoveActiveFilter(Filter: TActiveFilter);
var
  idx: Integer;
  PanelToRemove: TscGPPanel;
begin
  LogToFile('Starting RemoveActiveFilter');

  if not Assigned(Filter) then Exit;

  try
    // Find filter index
    idx := FActiveFilters.IndexOf(Filter);
    if idx < 0 then Exit;

    // Save panel reference and clear it from filter
    PanelToRemove := Filter.FilterPanel;
    Filter.FilterPanel := nil;  // Prevent double-free

    // Remove filter from collection
    FActiveFilters.Delete(idx);
    LogToFile(Format('Removed filter. Remaining count: %d', [FActiveFilters.Count]));

    // Free the panel
    if Assigned(PanelToRemove) then
    begin
      PanelToRemove.Parent := nil;
      PanelToRemove.Free;
    end;

    // Update layout and data
    UpdatePanelLayouts;
    LoadData;

    LogToFile('Filter removal completed successfully');

  except
    on E: Exception do
      LogToFile(Format('Error removing filter: %s', [E.Message]));
  end;
end;

procedure TORMSearchForm.ArrangeFilterPanels;
const
  PADDING_TOP = 5;
  PADDING_BOTTOM = 5;
var
  RequiredHeight: Integer;
  i: Integer;
  FilterPanel: TscGPPanel;
  YPosition: Integer;
begin
  LogToFile(Format('Starting ArrangeFilterPanels with %d filters', [FActiveFilters.Count]));

  // Hide panel if no filters
  if FActiveFilters.Count = 0 then
  begin
    pnlActiveFilters.Height := 0;
    pnlActiveFilters.Visible := False;
    pnlTop.Height := pnlFilters.Height;
    LogToFile('No filters - hiding panel');
    Exit;
  end;

  // Calculate total height needed
  RequiredHeight := PADDING_TOP + PADDING_BOTTOM +
                   (FActiveFilters.Count * FILTER_PANEL_HEIGHT) +
                   (Max(0, FActiveFilters.Count - 1) * FILTER_PANEL_SPACING);

  LogToFile(Format('Required height calculated: %d for %d filters',
    [RequiredHeight, FActiveFilters.Count]));

  // Set panel heights - IMPORTANT: Set Constraints first
  pnlActiveFilters.Constraints.MinHeight := RequiredHeight;
  pnlActiveFilters.Constraints.MaxHeight := RequiredHeight;

  // Now set the actual heights
  pnlActiveFilters.DisableAlign;
  try
    pnlActiveFilters.Visible := True;
    pnlActiveFilters.Height := RequiredHeight;
    pnlTop.Height := pnlFilters.Height + RequiredHeight;

    // Position each filter panel
    YPosition := PADDING_TOP;
    for i := FActiveFilters.Count - 1 downto 0 do
    begin
      FilterPanel := FActiveFilters[i].FilterPanel;
      if Assigned(FilterPanel) then
      begin
        // Explicitly set panel properties
        FilterPanel.DisableAlign;
        try
          FilterPanel.Parent := pnlActiveFilters;
          FilterPanel.Align := alNone;
          FilterPanel.Anchors := [akLeft, akTop, akRight]; // Add anchors
          FilterPanel.Top := YPosition;
          FilterPanel.Left := PANEL_MARGIN;
          FilterPanel.Width := pnlActiveFilters.ClientWidth - (2 * PANEL_MARGIN);
          FilterPanel.Height := FILTER_PANEL_HEIGHT;
          FilterPanel.Visible := True;
          FilterPanel.BringToFront;

          LogToFile(Format('Positioned filter panel %d at Y=%d', [i, FilterPanel.Top]));

          Inc(YPosition, FILTER_PANEL_HEIGHT + FILTER_PANEL_SPACING);
        finally
          FilterPanel.EnableAlign;
        end;
      end;
    end;

  finally
    pnlActiveFilters.EnableAlign;
  end;

  // Force size
  pnlActiveFilters.Height := RequiredHeight;
  LogToFile(Format('Set final heights - Active Filters Panel: %d, Required: %d',
    [pnlActiveFilters.Height, RequiredHeight]));

  // Ensure visibility and update
  pnlActiveFilters.Invalidate;
  pnlTop.Invalidate;
  Application.ProcessMessages;

  // Double-check height after processing
  if pnlActiveFilters.Height <> RequiredHeight then
  begin
    LogToFile('Height mismatch detected - forcing correction');
    pnlActiveFilters.Height := RequiredHeight;
  end;

  LogToFile(Format('Finished arranging. Final panel height: %d', [pnlActiveFilters.Height]));
end;

procedure TORMSearchForm.UpdatePanelLayouts;
begin
  LogToFile('Starting UpdatePanelLayouts');

  pnlActiveFilters.DisableAlign;
  try
    // Rearrange all filter panels
    ArrangeFilterPanels;

    // Ensure parent panels are properly sized
    pnlActiveFilters.Realign;
    pnlTop.Realign;

  finally
    pnlActiveFilters.EnableAlign;
  end;

  // Force visual update
  pnlActiveFilters.Refresh;
  pnlTop.Refresh;

  LogToFile(Format('Panel layouts updated. Active filters: %d, Panel height: %d',
    [FActiveFilters.Count, pnlActiveFilters.Height]));
end;

procedure TORMSearchForm.FilterDeleteButtonClick(Sender: TObject);
var
  Filter: TActiveFilter;
begin
  if not (Sender is TscGPCharGlyphButton) then
    Exit;

  // Get filter from button's tag
  Filter := TActiveFilter(TscGPCharGlyphButton(Sender).Tag);
  if Assigned(Filter) then
    RemoveActiveFilter(Filter);
end;

function TORMSearchForm.BuildWhereClause: string;
var
  SQLBuilder: TStringBuilder;
  i: Integer;
  Filter: TActiveFilter;
  NeedsAND: Boolean;
begin
  SQLBuilder := TStringBuilder.Create;
  try
    NeedsAND := False;

    for i := 0 to FActiveFilters.Count - 1 do
    begin
      Filter := FActiveFilters[i];

      if NeedsAND then
        SQLBuilder.Append(' AND ');

      // Add current filter condition
      case Filter.DataType of
        tiString:
          case Filter.FilterOperator of
            foEqual: SQLBuilder.AppendFormat('%s = %s',
              [Filter.FieldName, QuotedStr(Filter.Value1)]);
            foContains: SQLBuilder.AppendFormat('%s LIKE %s',
              [Filter.FieldName, QuotedStr('%' + Filter.Value1 + '%')]);
            foBeginsWith: SQLBuilder.AppendFormat('%s LIKE %s',
              [Filter.FieldName, QuotedStr(Filter.Value1 + '%')]);
            foEndsWith: SQLBuilder.AppendFormat('%s LIKE %s',
              [Filter.FieldName, QuotedStr('%' + Filter.Value1)]);
            foIsNull: SQLBuilder.AppendFormat('%s IS NULL', [Filter.FieldName]);
            foIsNotNull: SQLBuilder.AppendFormat('%s IS NOT NULL', [Filter.FieldName]);
          end;

        tiInteger, tiFloat, tiMoney:
          case Filter.FilterOperator of
            foEqual: SQLBuilder.AppendFormat('%s = %s',
              [Filter.FieldName, Filter.Value1]);
            foGreaterThan: SQLBuilder.AppendFormat('%s > %s',
              [Filter.FieldName, Filter.Value1]);
            foLessThan: SQLBuilder.AppendFormat('%s < %s',
              [Filter.FieldName, Filter.Value1]);
            foBetween: SQLBuilder.AppendFormat('%s BETWEEN %s AND %s',
              [Filter.FieldName, Filter.Value1, Filter.Value2]);
            foIsNull: SQLBuilder.AppendFormat('%s IS NULL', [Filter.FieldName]);
            foIsNotNull: SQLBuilder.AppendFormat('%s IS NOT NULL', [Filter.FieldName]);
          end;

        tiDate, tiDateTime:
          case Filter.FilterOperator of
            foEqual: SQLBuilder.AppendFormat('%s = %s',
              [Filter.FieldName, QuotedStr(Filter.Value1)]);
            foGreaterThan: SQLBuilder.AppendFormat('%s > %s',
              [Filter.FieldName, QuotedStr(Filter.Value1)]);
            foLessThan: SQLBuilder.AppendFormat('%s < %s',
              [Filter.FieldName, QuotedStr(Filter.Value1)]);
            foBetween: SQLBuilder.AppendFormat('%s BETWEEN %s AND %s',
              [Filter.FieldName, QuotedStr(Filter.Value1), QuotedStr(Filter.Value2)]);
            foIsNull: SQLBuilder.AppendFormat('%s IS NULL', [Filter.FieldName]);
            foIsNotNull: SQLBuilder.AppendFormat('%s IS NOT NULL', [Filter.FieldName]);
          end;
      end;

      NeedsAND := True;
    end;

    if SQLBuilder.Length > 0 then
      Result := ' WHERE ' + SQLBuilder.ToString
    else
      Result := '';

  finally
    SQLBuilder.Free;
  end;
end;

procedure TORMSearchForm.btnSearchClick(Sender: TObject);
begin
  LoadData;
end;

procedure TORMSearchForm.LoadData;
var
  SQL: string;
begin
  if not Assigned(FQuery) then
    FQuery := TUniQuery.Create(nil);

  try
    SQL := FBaseQuery;

    // Add WHERE clause if there are filters
    if FActiveFilters.Count > 0 then
      SQL := SQL + BuildWhereClause;

    FQuery.Close;
    FQuery.SQL.Text := SQL;
    FQuery.Open;

    UpdateGridData;
  except
    on E: Exception do
    begin
      ShowMessage('Error loading data: ' + E.Message);
      FQuery.Close;
    end;
  end;
end;

function TORMSearchForm.GetFilteredSQL: string;
begin
  Result := FBaseQuery;
  if FActiveFilters.Count > 0 then
    Result := Result + BuildWhereClause;
end;

procedure TORMSearchForm.UpdateGridData;
var
  Row: Integer;
begin
  SearchGrid.RowCount := FQuery.RecordCount + 1;
  
  Row := 1;
  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] := DateToStr(Field.AsDateTime);
            tiDateTime:
              SearchGrid.Cells[i, Row] := DateTimeToStr(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;
end;

procedure TORMSearchForm.UpdateSelectedSearch;
var
  Row: Integer;
begin
  Row := GetSelectedRow;
  if Row < 1 then Exit;
  
  FSelectedSearch := TSelectedSearch.Create;
  
  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, Row]
      );
    end;
  end;
end;

function TORMSearchForm.GetSelectedRow: Integer;
begin
  Result := SearchGrid.Row;
  if Result >= SearchGrid.RowCount then
    Result := -1;
end;

procedure TORMSearchForm.SearchGridDblClick(Sender: TObject);
begin
  if GetSelectedRow > 0 then
    ModalResult := mrOk;
end;

procedure TORMSearchForm.btnCloseClick(Sender: TObject);
begin
  ModalResult := mrCancel;
end;

procedure TORMSearchForm.btnClearFiltersClick(Sender: TObject);
begin
  FActiveFilters.Clear;
  UpdatePanelLayouts;
  LoadData;
end;

procedure TORMSearchForm.FormCreate(Sender: TObject);
begin
  inherited;
  FActiveFilters := TObjectList<TActiveFilter>.Create(True);
  FSearchColumns := TObjectList<TSearchColumn>.Create(True);
  FFieldTypes := TDictionary<string, TORMFormatType>.Create;
  FIsORMMode := False;

  // Initialize panel properties
  pnlActiveFilters.Visible := False;
  pnlActiveFilters.Height := 0;
  pnlActiveFilters.Align := alTop;

  SetupGrid;
end;

procedure TORMSearchForm.FormDestroy(Sender: TObject);
begin
  ClearActiveFilters;
  FActiveFilters.Free;
  FSearchColumns.Free;
  FFieldTypes.Free;
  FQuery.Free;
end;

procedure TORMSearchForm.cboOperatorChange(Sender: TObject);
begin
  UpdateValueControls;
end;

procedure TORMSearchForm.btnAddFilterClick(Sender: TObject);
var
  Filter: TActiveFilter;
  Column: TSearchColumn;
  CurrentValue1, CurrentValue2: string;
begin
  if cboField.ItemIndex < 0 then
    Exit;

  // Get current values
  if dateValue1.Visible then
    CurrentValue1 := DateToStr(dateValue1.Date)
  else
    CurrentValue1 := edtValue1.Text;

  if dateValue2.Visible then
    CurrentValue2 := DateToStr(dateValue2.Date)
  else
    CurrentValue2 := edtValue2.Text;

  // Find corresponding column
  for Column in FSearchColumns do
  begin
    if Column.DisplayName = cboField.Text then
    begin
      // Validate values
      if not ValidateFilterValue(CurrentValue1, Column.DataType) or
         ((cboOperator.Text = 'Between') and not ValidateFilterValue(CurrentValue2, Column.DataType)) then
      begin
        ShowMessage('Please enter valid values for the selected data type');
        Exit;
      end;

      Filter := TActiveFilter.Create;
      try
        // Set basic properties
        Filter.FieldName := Column.FieldName;
        Filter.DisplayName := Column.DisplayName;
        Filter.DataType := Column.DataType;
        Filter.Value1 := CurrentValue1;
        Filter.Value2 := CurrentValue2;

        // Map operator string to enum
        case cboOperator.ItemIndex of
          0: Filter.FilterOperator := foEqual;
          1: Filter.FilterOperator := foContains;
          2: Filter.FilterOperator := foBeginsWith;
          3: Filter.FilterOperator := foEndsWith;
          4: Filter.FilterOperator := foGreaterThan;
          5: Filter.FilterOperator := foLessThan;
          6: Filter.FilterOperator := foBetween;
          7: Filter.FilterOperator := foIsNull;
          8: Filter.FilterOperator := foIsNotNull;
        end;

        // Create visual components
        CreateFilterPanel(Filter);
        FActiveFilters.Add(Filter);
        UpdatePanelLayouts;

        // Clear inputs
        edtValue1.Clear;
        edtValue2.Clear;
        if dateValue1.Visible then dateValue1.Date := Date;
        if dateValue2.Visible then dateValue2.Date := Date;

      except
        Filter.Free;
        raise;
      end;
      Break;
    end;
  end;

  // Refresh data with new filter
  LoadData;
end;

procedure TORMSearchForm.SetupGrid;
begin
  with SearchGrid do
  begin
    Options := [goFixedVertLine, goFixedHorzLine, goVertLine, goHorzLine,
                goRangeSelect, goRowSelect];
    FixedRows := 1;
    RowCount := 2;
    ColCount := 2; // Will be updated when columns are added

    // Basic styling
    Font.Name := 'Segoe UI';
    Font.Size := 9;
    DefaultRowHeight := 25;
    FixedFont.Name := 'Segoe UI';
    FixedFont.Style := [fsBold];

    // Colors
    FixedColor := clBtnFace;
    Color := clWhite;
  end;
end;

procedure TORMSearchForm.ClearActiveFilters;
begin
  for var Filter in FActiveFilters do
  begin
    if Assigned(Filter.FilterPanel) then
      Filter.FilterPanel.Free;
  end;
  FActiveFilters.Clear;
  UpdatePanelLayouts;
end;

procedure TORMSearchForm.AdjustFilterPanelHeight;
var
  TotalHeight: Integer;
begin
  if FActiveFilters.Count = 0 then
  begin
    pnlActiveFilters.Visible := False;
    pnlActiveFilters.Height := 0;
    Exit;
  end;

  TotalHeight := FActiveFilters.Count * (FILTER_PANEL_HEIGHT + FILTER_PANEL_SPACING);


  pnlTop.Height:= pnlFilters.Height + pnlActiveFilters.Height;

  pnlActiveFilters.Visible := True;
end;

procedure TORMSearchForm.SetupORMFilter;
begin
  if FIsORMMode then
  begin
    if not Assigned(FORMFilter) then
      FORMFilter := TORMFilter.Create(nil);

    // Clear existing filters
    FORMFilter.Clear;

    // Add filters from active filters
    for var Filter in FActiveFilters do
    begin
      case Filter.FilterOperator of
        foEqual:
          FORMFilter.Where(Filter.FieldName).EqualTo(Filter.Value1);
        foNotEqual:
          FORMFilter.Where(Filter.FieldName).NotEqualTo(Filter.Value1);
        foGreaterThan:
          FORMFilter.Where(Filter.FieldName).GreaterThan(Filter.Value1);
        foLessThan:
          FORMFilter.Where(Filter.FieldName).LessThan(Filter.Value1);
        foGreaterEqual:
          FORMFilter.Where(Filter.FieldName).GreaterOrEqual(Filter.Value1);
        foLessEqual:
          FORMFilter.Where(Filter.FieldName).LessOrEqual(Filter.Value1);
        foBetween:
          FORMFilter.Where(Filter.FieldName).Between(Filter.Value1, Filter.Value2);
        foContains:
          FORMFilter.Where(Filter.FieldName).Contains(Filter.Value1);
        foBeginsWith:
          FORMFilter.Where(Filter.FieldName).BeginsWith(Filter.Value1);
        foEndsWith:
          FORMFilter.Where(Filter.FieldName).EndsWith(Filter.Value1);
        foIsNull:
          FORMFilter.Where(Filter.FieldName).IsNull;
        foIsNotNull:
          FORMFilter.Where(Filter.FieldName).IsNotNull;
      end;
    end;
  end;
end;

end.

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

check which parts of the above unit are not used