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;
  PANEL_MARGIN = 5;
  GRID_MIN_HEIGHT = 200;

type
  /// <summary>
  /// Represents search column configuration
  /// </summary>
  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;

  /// <summary>
  /// Represents selected search result
  /// </summary>
  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;

  /// <summary>
  /// Visual representation of an applied filter
  /// </summary>
  TFilterPanel = class
  private
    FId: Integer;  // Unique identifier
    FPanel: TscGPPanel;
    FLabel: TLabel;
    FDeleteButton: TscGPCharGlyphButton;
    FFieldName: string;
    FDisplayText: string;
    FPanelIndex: Integer;  // Add this to track panels
    FFilterID: TGUID;  // Add GUID field
  public
    constructor Create(AOwner: TComponent; const AFieldName, ADisplayText: string;
      APanelIndex: Integer; const AFilterID: TGUID);
    destructor Destroy; override;
    procedure UpdatePosition(const Top: Integer);
    property Panel: TscGPPanel read FPanel;
    property FieldName: string read FFieldName;
    property DisplayText: string read FDisplayText;
    property PanelIndex: Integer read FPanelIndex;
    property FilterID: TGUID read FFilterID;
  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
    FFilter: IORMFilter;
    FFilterPanels: TObjectList<TFilterPanel>;
    FSearchColumns: TObjectList<TSearchColumn>;
    FSelectedSearch: TSelectedSearch;
    FBaseQuery: string;
    FConnection: TUniConnection;
    FQuery: TUniQuery;
    FRecordClass: TClass;
    FFieldTypes: TDictionary<string, TORMFormatType>;
    FLastOperatorType: TFilterOperator;  // Track last used operator
    FCurrentFieldType: TORMFormatType;   // Track current field type
    FFilterMapping: TDictionary<string, string>; // Maps panel ID to filter field name
    FNextPanelIndex: Integer;  // Add this to generate unique panel IDs

    procedure SetupGrid;
    procedure UpdateValueControls;
    procedure RemoveFilterPanel(const AFieldName: string);
    procedure ArrangeFilterPanels;
    procedure ClearFilters;
    procedure CreateFilterPanel(const AFieldName, ADisplayText: string);
    procedure LoadData;
    procedure SetupFieldTypes;
    procedure PopulateOperators(ADataType: TORMFormatType);
    function GetFilteredSQL: string;
    procedure UpdateSelectedSearch;
    function GetSelectedRow: Integer;
    procedure SetupColumns;
    procedure UpdateGridData;
    function ValidateFilterValues(DataType: TORMFormatType): Boolean;
    function GetFilterValue1: Variant;
    function GetFilterValue2: Variant;
    function GetFilterDisplayText: string;
    procedure ClearFilterInputs;

    procedure SetupVisualStyles;
    procedure ValidateInputs;
    procedure ShowValidationError(const Msg: string);
    procedure UpdateControlStates;
    function GetOperatorDisplayText(AOperator: TFilterOperator): string;
    function ExtractTableName(const SQL: string): string;
    function GetOperatorFromText(const OpText: string): TFilterOperator;
    function GetOperatorSQL(Op: 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;
  ARecordClass: TClass = nil): Boolean;

procedure InitializeSearch(
  AConnection: TUniConnection;
  const ABaseQuery: string;
  const ASearchColumns: array of TSearchColumn;
  ARecordClass: TClass = nil);
  end;

implementation

{$R *.dfm}

uses Math;

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

function FormatFilterValue(const Value: Variant; DataType: TORMFormatType): string;
begin
  if VarIsNull(Value) or VarIsEmpty(Value) then
    Exit('NULL');

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

    tiInteger, tiFloat:
      if VarIsNumeric(Value) then
        Result := VarToStr(Value)
      else
        Result := 'NULL';

    tiDate, tiDateTime:
      if VarIsType(Value, varDate) then
        if DataType = tiDate then
          Result := QuotedStr(FormatDateTime('yyyy-mm-dd', VarToDateTime(Value)))
        else
          Result := QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', VarToDateTime(Value)))
      else
        Result := 'NULL';

    tiBoolean:
      if VarIsType(Value, varBoolean) then
        Result := IfThen(Value, 'TRUE', 'FALSE')
      else
        Result := 'NULL';

    else
      Result := QuotedStr(VarToStr(Value));
  end;
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;

{ TFilterPanel }

constructor TFilterPanel.Create(AOwner: TComponent; const AFieldName, ADisplayText: string; APanelIndex: Integer; const AFilterID: TGUID);
begin
  inherited Create;
  FId := APanelIndex;
  FFieldName := AFieldName;
  FDisplayText := ADisplayText;
  FPanelIndex := APanelIndex;
  FFilterID := AFilterID;  // Store the filter GUID

  // Create panel
  FPanel := TscGPPanel.Create(AOwner);
  with FPanel do
  begin
    Parent := TWinControl(AOwner);
    Name := Format('FilterPanel_%d', [APanelIndex]);
    Height := FILTER_PANEL_HEIGHT;
    Align := alNone;
    Tag := APanelIndex;  // Store panel index in Tag

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

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

  // Create label
  FLabel := TLabel.Create(FPanel);
  with FLabel do
  begin
    Parent := FPanel;
    Name := Format('FilterLabel_%d', [APanelIndex]);
    Left := 10;
    Top := (FPanel.Height - Height) div 2;
    AutoSize := True;
    Caption := ADisplayText;
    Tag := APanelIndex;  // Store panel index in Tag
  end;

  // Create delete button
  FDeleteButton := TscGPCharGlyphButton.Create(FPanel);
  with FDeleteButton do
  begin
    Parent := FPanel;
    Name := Format('FilterButton_%d', [APanelIndex]);
    Width := 24;
    Height := 24;
    Top := (FPanel.Height - Height) div 2;
    Align := alRight;
    Margins.Right := 5;
    Caption := '';
    Tag := NativeInt(Self); // Store reference to FilterPane

    // Button style settings remain the same
    Options.NormalColor := clBtnFace;
    Options.HotColor := clBtnFace;
    Options.PressedColor := clBtnFace;
    Options.FocusedColor := clBtnFace;

    GlyphOptions.NormalColor := clGray;
    GlyphOptions.HotColor := clRed;
    GlyphOptions.PressedColor := clMaroon;
    GlyphOptions.Size := 10;
    GlyphOptions.Index := 13;
  end;
end;

destructor TFilterPanel.Destroy;
begin
  // Components will be automatically freed as they are owned by the form
  inherited;
end;

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

{ TORMSearchForm }

constructor TORMSearchForm.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FFilterMapping := TDictionary<string, string>.Create;
  FFilterPanels := TObjectList<TFilterPanel>.Create(True);
  FSearchColumns := TObjectList<TSearchColumn>.Create(True);
  FFieldTypes := TDictionary<string, TORMFormatType>.Create;
  FNextPanelIndex := 1;  // Initialize panel index counter

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

  SetupGrid;
  SetupVisualStyles;
end;

destructor TORMSearchForm.Destroy;
begin
  try
    // Clear filters first
    if Assigned(FFilter) then
      FFilter.Clear;

    // Then safely free all panels
    if Assigned(FFilterPanels) then
    begin
      while FFilterPanels.Count > 0 do
      begin
        if Assigned(FFilterPanels[0]) then
        begin
          if Assigned(FFilterPanels[0].Panel) then
          begin
            FFilterPanels[0].Panel.Parent := nil;
            FFilterPanels[0].Panel.Free;
          end;
          FFilterPanels.Delete(0);
        end;
      end;
      FreeAndNil(FFilterPanels);
    end;

    // Free other objects
    FSearchColumns.Free;
    FFieldTypes.Free;

  except
    on E: Exception do
    begin
      ShowMessage('Error during cleanup: ' + E.Message);
    end;
  end;

  inherited;
end;

// Helper function to extract table name from SQL
function TORMSearchForm.ExtractTableName(const SQL: string): string;
var
  FromPos, SpacePos: Integer;
  TablePart: string;
begin
  // Simple SQL parsing - might need enhancement for complex queries
  FromPos := Pos('FROM', UpperCase(SQL));
  if FromPos > 0 then
  begin
    TablePart := Copy(SQL, FromPos + 5, Length(SQL));
    SpacePos := Pos(' ', TablePart);
    if SpacePos > 0 then
      Result := Copy(TablePart, 1, SpacePos - 1)
    else
      Result := TablePart;

    // Clean up any schema prefixes or quotation marks
    Result := Result.Replace('[', '').Replace(']', '').Replace('"', '');
    if Pos('.', Result) > 0 then
      Result := Result.Split(['.'])[1];

    Result := Result.Trim;
  end
  else
    Result := '';
end;

procedure TORMSearchForm.InitializeSearch(
  AConnection: TUniConnection;
  const ABaseQuery: string;
  const ASearchColumns: array of TSearchColumn;
  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(TSearchColumn.Create(
      Column.FieldName,
      Column.DisplayName,
      Column.Width,
      Column.Visible,
      Column.UsableAsFilter
    ));

  // Create filter if record class is provided
  if Assigned(FRecordClass) then
    FFilter := TORMFilterFactory.GetInstance.CreateFilterInterfaceForClass(FRecordClass);

  SetupColumns;
  SetupFieldTypes;
  LoadData;
end;

procedure TORMSearchForm.btnAddFilterClick(Sender: TObject);
var
  Column: TSearchColumn;
  Op: TFilterOperator;
begin
  if cboField.ItemIndex < 0 then Exit;

  // Find corresponding column
  for Column in FSearchColumns do
  begin
    if Column.DisplayName = cboField.Text then
    begin
      // Validate values first
      if not ValidateFilterValues(Column.DataType) then Exit;

      // Get the operator
      Op := GetOperatorFromText(cboOperator.Text);

      // Handle special case for boolean values
      if (Column.DataType = tiBoolean) and
         (cboOperator.Text = 'Is True') then
        FFilter.Where(Column.FieldName).EqualTo(True)
      else if (Column.DataType = tiBoolean) and
              (cboOperator.Text = 'Is False') then
        FFilter.Where(Column.FieldName).EqualTo(False)
      // Handle regular cases
      else case Op of
        foEqual:
          FFilter.Where(Column.FieldName).EqualTo(GetFilterValue1);

        foNotEqual:
          FFilter.Where(Column.FieldName).NotEqualTo(GetFilterValue1);

        foGreaterThan:
          FFilter.Where(Column.FieldName).GreaterThan(GetFilterValue1);

        foLessThan:
          FFilter.Where(Column.FieldName).LessThan(GetFilterValue1);

        foGreaterEqual:
          FFilter.Where(Column.FieldName).GreaterOrEqual(GetFilterValue1);

        foLessEqual:
          FFilter.Where(Column.FieldName).LessOrEqual(GetFilterValue1);

        foBetween:
          FFilter.Where(Column.FieldName).Between(GetFilterValue1, GetFilterValue2);

        foContains:
          FFilter.Where(Column.FieldName).Contains(GetFilterValue1);

        foBeginsWith:
          FFilter.Where(Column.FieldName).BeginsWith(GetFilterValue1);

        foEndsWith:
          FFilter.Where(Column.FieldName).EndsWith(GetFilterValue1);

        foIsNull:
          FFilter.Where(Column.FieldName).IsNull;

        foIsNotNull:
          FFilter.Where(Column.FieldName).IsNotNull;
      end;

      // Update visual filter panel
      CreateFilterPanel(Column.FieldName, GetFilterDisplayText);

      // Clear inputs
      ClearFilterInputs;

      // Refresh data
      LoadData;
      Break;
    end;
  end;
end;

function TORMSearchForm.GetFilterValue1: Variant;
begin
  if dateValue1.Visible then
    Result := dateValue1.Date
  else
    Result := edtValue1.Text;
end;

function TORMSearchForm.GetFilterValue2: Variant;
begin
  if dateValue2.Visible then
    Result := dateValue2.Date
  else
    Result := edtValue2.Text;
end;

function TORMSearchForm.GetFilterDisplayText: string;
begin
  Result := Format('%s %s', [cboField.Text, cboOperator.Text]);

  case GetOperatorFromText(cboOperator.Text) of
    foIsNull, foIsNotNull:
      ; // No additional text needed

    foBetween:
      begin
        if dateValue1.Visible then
          Result := Result + Format(' %s AND %s',
            [DateToStr(dateValue1.Date), DateToStr(dateValue2.Date)])
        else
          Result := Result + Format(' %s AND %s',
            [edtValue1.Text, edtValue2.Text]);
      end;

    else
      begin
        if dateValue1.Visible then
          Result := Result + ' ' + DateToStr(dateValue1.Date)
        else
          Result := Result + ' ' + edtValue1.Text;
      end;
  end;
end;

procedure TORMSearchForm.ClearFilterInputs;
begin
  edtValue1.Clear;
  edtValue2.Clear;
  dateValue1.Date := Date;
  dateValue2.Date := Date;
end;

procedure TORMSearchForm.CreateFilterPanel(const AFieldName, ADisplayText: string);
var
  FilterPanel: TFilterPanel;
  FilterID: TGUID;
begin
  FilterID := FFilter.GetLastConditionID;

  FilterPanel := TFilterPanel.Create(pnlActiveFilters, AFieldName,
    ADisplayText, FNextPanelIndex, FilterID);
  Inc(FNextPanelIndex);

  FilterPanel.Panel.Width := pnlActiveFilters.ClientWidth - (2 * PANEL_MARGIN);
  FilterPanel.FDeleteButton.OnClick := FilterDeleteButtonClick;
  FilterPanel.FDeleteButton.Tag := NativeInt(FilterPanel);

  FFilterPanels.Add(FilterPanel);

  if FFilterPanels.Count = 1 then
    pnlActiveFilters.Visible := True;

  ArrangeFilterPanels;
end;

procedure TORMSearchForm.FilterDeleteButtonClick(Sender: TObject);
var
  Button: TscGPCharGlyphButton;
  Panel: TscGPPanel;
  FilterPanel: TFilterPanel;
  i: Integer;
begin
  if not (Sender is TscGPCharGlyphButton) then Exit;

  Button := TscGPCharGlyphButton(Sender);
  Panel := TscGPPanel(Button.Parent);

  FilterPanel := nil;
  for i := 0 to FFilterPanels.Count - 1 do
  begin
    if FFilterPanels[i].Panel = Panel then
    begin
      FilterPanel := FFilterPanels[i];
      Break;
    end;
  end;

  if not Assigned(FilterPanel) then Exit;

  try
    if Assigned(FFilter) then
      FFilter.RemoveFieldConditions(FilterPanel.FilterID);

    // Free panel before removing from collection
    FilterPanel.Panel.Parent := nil;
    FilterPanel.Panel.Free;

    // Remove from collection
    i := FFilterPanels.IndexOf(FilterPanel);
    if i >= 0 then
      FFilterPanels.Delete(i);

    // Reset panel heights before rearranging
    pnlActiveFilters.DisableAlign;
    pnlTop.DisableAlign;
    try
      // Calculate new base height
      var newHeight := PANEL_MARGIN * 2 +
                      (FFilterPanels.Count * FILTER_PANEL_HEIGHT) +
                      (Max(0, FFilterPanels.Count - 1) * FILTER_PANEL_SPACING);

      pnlActiveFilters.Height := newHeight;
      pnlTop.Height := pnlFilters.Height + newHeight;
    finally
      pnlTop.EnableAlign;
      pnlActiveFilters.EnableAlign;
    end;

    // Update UI
    pnlActiveFilters.Visible := FFilterPanels.Count > 0;
    ArrangeFilterPanels;
    LoadData;
    UpdateControlStates;

  except
    on E: Exception do
    begin
      ArrangeFilterPanels;
    end;
  end;
end;

procedure TORMSearchForm.RemoveFilterPanel(const AFieldName: string);
var
  i: Integer;
  PanelToRemove: TFilterPanel;
begin
  PanelToRemove := nil;

  // First find the panel without removing it
  for i := 0 to FFilterPanels.Count - 1 do
  begin
    if SameText(FFilterPanels[i].FieldName, AFieldName) then
    begin
      PanelToRemove := FFilterPanels[i];
      Break;
    end;
  end;

  if Assigned(PanelToRemove) then
  begin
    try
      // Remove from the list first
      FFilterPanels.Extract(PanelToRemove);

      // Then free the panel safely
      if Assigned(PanelToRemove.Panel) then
      begin
        // Ensure the panel is removed from its parent
        if Assigned(PanelToRemove.Panel.Parent) then
        begin
          PanelToRemove.Panel.Parent := nil;
          PanelToRemove.Panel.Free;
        end;
      end;

      // Finally free the container object
      PanelToRemove.Free;

    except
      on E: Exception do
      begin
        ShowMessage('Error removing panel: ' + E.Message);
      end;
    end;
  end;

  // Update panel visibility regardless of any errors
  pnlActiveFilters.Visible := FFilterPanels.Count > 0;

  // Rearrange remaining panels
  ArrangeFilterPanels;
end;

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

  try
    FQuery.Close;
    FQuery.SQL.Text := GetFilteredSQL;
    FQuery.Connection := FConnection;
    FQuery.Open;

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

function TORMSearchForm.GetFilteredSQL: string;
var
  WherePos: Integer;
  FilterSQL: string;
begin
  Result := FBaseQuery;

  if Assigned(FFilter) then
  begin
    FilterSQL := FFilter.GetSQL;
    if FilterSQL <> '' then
    begin
      WherePos := Pos(' WHERE ', UpperCase(Result));

      if WherePos > 0 then
        // Add to existing WHERE
        Result := Format('%s AND (%s)',
          [Copy(Result, 1, WherePos + 6), FilterSQL])
      else
      begin
        // Find appropriate insertion point
        var GroupPos := Pos(' GROUP BY ', UpperCase(Result));
        var OrderPos := Pos(' ORDER BY ', UpperCase(Result));
        var InsertPos := Length(Result) + 1;

        if GroupPos > 0 then
          InsertPos := GroupPos
        else if OrderPos > 0 then
          InsertPos := OrderPos;

        // Insert new WHERE
        Result := Format('%s WHERE %s %s',
          [Copy(Result, 1, InsertPos - 1),
           FilterSQL,
           Copy(Result, InsertPos, MaxInt)]);
      end;
    end;
  end;
end;

procedure TORMSearchForm.btnSearchClick(Sender: TObject);
begin
  pnlActiveFilters.Height:= pnlTop.Height - pnlFilters.Height;
  Showmessage(GetFilteredSQL);
  LoadData;
end;

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

procedure TORMSearchForm.ClearFilters;
begin
  if Assigned(FFilter) then
    FFilter.Clear;

  // Clear panels safely
  while FFilterPanels.Count > 0 do
  begin
    if Assigned(FFilterPanels[0]) and Assigned(FFilterPanels[0].Panel) then
      FFilterPanels[0].Panel.Free;
    FFilterPanels.Delete(0);
  end;

  // Reset panel container
  pnlActiveFilters.Height := 0;
  pnlActiveFilters.Visible := False;
  pnlTop.Height := pnlFilters.Height;

  // Update controls
  UpdateControlStates;
end;

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

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

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

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

function TORMSearchForm.ValidateFilterValues(DataType: TORMFormatType): Boolean;
var
  DummyInt: Int64;  // Use Int64 to handle all integer sizes
  DummyFloat: Extended;
  DummyDate: TDateTime;
  Op: TFilterOperator;
begin
  Result := True;

  // Handle null checks differently
  if (cboOperator.Text = 'Is Empty') or (cboOperator.Text = 'Is Not Empty') then
    Exit(True);

  // Get current operator
  Op := GetOperatorFromText(cboOperator.Text);

  case DataType of
    tiInteger:
      begin
        if edtValue1.Text <> '' then
          Result := TryStrToInt64(edtValue1.Text, DummyInt);

        if Result and (Op = foBetween) and (edtValue2.Text <> '') then
          Result := TryStrToInt64(edtValue2.Text, DummyInt);
      end;

    tiFloat, tiMoney:
      begin
        if edtValue1.Text <> '' then
          Result := TryStrToFloat(edtValue1.Text, DummyFloat);

        if Result and (Op = foBetween) and (edtValue2.Text <> '') then
          Result := TryStrToFloat(edtValue2.Text, DummyFloat);
      end;

    tiDate, tiDateTime:
      begin
        if dateValue1.Visible then
          Result := dateValue1.Date > 0;

        if Result and dateValue2.Visible then
          Result := dateValue2.Date > 0;

        // Additional date validation
        if Result and (Op = foBetween) then
          Result := dateValue1.Date <= dateValue2.Date;
      end;

    tiString, tiBoolean:
      Result := True; // Always valid
  end;

  if not Result then
    ShowMessage('Please enter valid values for the selected data type');
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.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;
  RecNo: Integer;
begin
  Row := GetSelectedRow;
  if Row < 1 then Exit;

  // Create new selected search
  FSelectedSearch.Free; // Ensure we don't leak memory
  FSelectedSearch := TSelectedSearch.Create;

  // Calculate record number (subtract fixed row)
  RecNo := Row - SearchGrid.FixedRows;

  // Move query to correct record
  FQuery.First;
  for var i := 0 to RecNo - 1 do
    FQuery.Next;

  // Now get values from the correct 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, Row]
      );
    end;
  end;
end;

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

procedure TORMSearchForm.FormCreate(Sender: TObject);
begin
  SetupGrid;
  SetupVisualStyles;
  UpdateControlStates;
end;

procedure TORMSearchForm.FormDestroy(Sender: TObject);
begin
  FQuery.Free;
  // Other cleanup is handled in destructor
end;

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

  // Find corresponding column
  for SelectedColumn in FSearchColumns do
  begin
    if SelectedColumn.DisplayName = cboField.Text then
    begin
      PopulateOperators(SelectedColumn.DataType);
      UpdateValueControls;
      Break;
    end;
  end;
end;

procedure TORMSearchForm.cboOperatorChange(Sender: TObject);
begin
  UpdateValueControls;
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.UpdateValueControls;
var
  IsBetweenOperator: Boolean;
  Column: TSearchColumn;
begin
  if cboField.ItemIndex < 0 then Exit;

  IsBetweenOperator := cboOperator.Text = 'Between';

  // Find corresponding column
  for Column in FSearchColumns do
  begin
    if Column.DisplayName = cboField.Text then
    begin
      case Column.DataType of
        tiDate, tiDateTime:
          begin
            dateValue1.Visible := True;
            dateValue2.Visible := IsBetweenOperator;
            edtValue1.Visible := False;
            edtValue2.Visible := False;
          end;
        else
          begin
            dateValue1.Visible := False;
            dateValue2.Visible := False;
            edtValue1.Visible := True;
            edtValue2.Visible := IsBetweenOperator;
          end;
      end;
      Break;
    end;
  end;
end;

procedure TORMSearchForm.ArrangeFilterPanels;
var
  YPos: Integer;
  RequiredHeight: Integer;
  i: Integer;
begin
  if not Assigned(FFilterPanels) then Exit;

  // First handle panel visibility
  pnlActiveFilters.Visible := FFilterPanels.Count > 0;

  if FFilterPanels.Count = 0 then
  begin
    pnlTop.DisableAlign;
    try
      pnlActiveFilters.Height := 0;
      pnlTop.Height := pnlFilters.Height;
    finally
      pnlTop.EnableAlign;
    end;
    Exit;
  end;

  try
    // Calculate required height
    RequiredHeight := PANEL_MARGIN * 2; // Top and bottom margins
    for i := 0 to FFilterPanels.Count - 1 do
    begin
      Inc(RequiredHeight, FILTER_PANEL_HEIGHT);
      if i < FFilterPanels.Count - 1 then
        Inc(RequiredHeight, FILTER_PANEL_SPACING);
    end;

    // Disable alignments while updating
    pnlActiveFilters.DisableAlign;
    pnlTop.DisableAlign;
    try
      // First set container height
      pnlActiveFilters.Height := RequiredHeight;

      // Position panels from top to bottom
      YPos := PANEL_MARGIN;
      for i := 0 to FFilterPanels.Count - 1 do
      begin
        if not Assigned(FFilterPanels[i]) then Continue;

        with FFilterPanels[i] do
        begin
          if not Assigned(Panel) then Continue;

          // Ensure panel has correct parent
          if Panel.Parent <> pnlActiveFilters then
            Panel.Parent := pnlActiveFilters;

          Panel.DisableAlign;
          try
            Panel.BoundsRect := Rect(
              PANEL_MARGIN,
              YPos,
              pnlActiveFilters.ClientWidth - PANEL_MARGIN,
              YPos + FILTER_PANEL_HEIGHT
            );
            Panel.Visible := True;
          finally
            Panel.EnableAlign;
          end;

          Inc(YPos, FILTER_PANEL_HEIGHT + FILTER_PANEL_SPACING);
        end;
      end;

      // Update parent panel height at the end
      pnlTop.Height := pnlFilters.Height + pnlActiveFilters.Height;

    finally
      pnlActiveFilters.EnableAlign;
      pnlTop.EnableAlign;
    end;

    // Force visual update
    pnlActiveFilters.Realign;
    pnlActiveFilters.Invalidate;
    pnlTop.Realign;
    pnlTop.Invalidate;
    Application.ProcessMessages;

  except
    on E: Exception do
    begin
      // Ensure valid state
      pnlActiveFilters.Visible := FFilterPanels.Count > 0;
      if pnlActiveFilters.Visible then
      begin
        if pnlActiveFilters.Height < FILTER_PANEL_HEIGHT then
          pnlActiveFilters.Height := FILTER_PANEL_HEIGHT;
        pnlTop.Height := pnlFilters.Height + pnlActiveFilters.Height;
      end else
        pnlTop.Height := pnlFilters.Height;
    end;
  end;
end;

procedure TORMSearchForm.PopulateOperators(ADataType: TORMFormatType);
begin
  FCurrentFieldType := ADataType;
  cboOperator.Items.Clear;

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

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

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

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

    tiBoolean:
      begin
        cboOperator.Items.AddStrings(['Is True', 'Is False']);
      end;
  end;

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

  UpdateValueControls;
end;

procedure TORMSearchForm.SetupColumns;
begin
  // Setup grid 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.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 styling
  edtValue1.Options.ShapeCornerRadius := 3;
  edtValue2.Options.ShapeCornerRadius := 3;
  dateValue1.Options.ShapeCornerRadius := 3;
  dateValue2.Options.ShapeCornerRadius := 3;

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

procedure TORMSearchForm.ValidateInputs;
var
  Column: TSearchColumn;
begin
  if cboField.ItemIndex < 0 then
  begin
    ShowValidationError('Please select a field');
    Exit;
  end;

  if cboOperator.ItemIndex < 0 then
  begin
    ShowValidationError('Please select an operator');
    Exit;
  end;

  // Find column and validate according to its type
  for Column in FSearchColumns do
  begin
    if Column.DisplayName = cboField.Text then
    begin
      if not ValidateFilterValues(Column.DataType) then
        Exit;
      Break;
    end;
  end;
end;

procedure TORMSearchForm.ShowValidationError(const Msg: string);
begin
  MessageDlg(Msg, mtError, [mbOK], 0);
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;

function TORMSearchForm.GetOperatorFromText(const OpText: string): TFilterOperator;
begin
  if OpText = 'Equals' then
    Result := foEqual
  else if OpText = 'Not Equals' then
    Result := foNotEqual
  else if OpText = 'Greater Than' then
    Result := foGreaterThan
  else if OpText = 'Less Than' then
    Result := foLessThan
  else if OpText = 'Greater or Equal' then
    Result := foGreaterEqual
  else if OpText = 'Less or Equal' then
    Result := foLessEqual
  else if OpText = 'Between' then
    Result := foBetween
  else if OpText = 'Contains' then
    Result := foContains
  else if OpText = 'Starts With' then
    Result := foBeginsWith
  else if OpText = 'Ends With' then
    Result := foEndsWith
  else if OpText = 'Is Empty' then
    Result := foIsNull
  else if OpText = 'Is Not Empty' then
    Result := foIsNotNull
  else if OpText = 'Is True' then
    Result := foEqual
  else if OpText = 'Is False' then
    Result := foEqual
  else
    Result := foEqual; // Default case
end;

function TORMSearchForm.GetOperatorSQL(Op: TFilterOperator): string;
begin
  case Op of
    foEqual: Result := '=';
    foNotEqual: Result := '<>';
    foGreaterThan: Result := '>';
    foLessThan: Result := '<';
    foGreaterEqual: Result := '>=';
    foLessEqual: Result := '<=';
    foBetween: Result := 'BETWEEN';
    foContains: Result := 'LIKE';
    foBeginsWith: Result := 'LIKE';
    foEndsWith: Result := 'LIKE';
    foIsNull: Result := 'IS NULL';
    foIsNotNull: Result := 'IS NOT NULL';
    else Result := '=';
  end;
end;

end.

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

give me a cleaned-up and organized version of this unit