using Microsoft.UI.Xaml;
using Microsoft.UI.Xaml.Controls;
using Microsoft.UI.Xaml.Controls.Primitives;
using Microsoft.UI.Xaml.Input;
using Microsoft.UI.Xaml.Media;
using Microsoft.UI.Xaml.Media.Imaging;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using Windows.System;

namespace Sonex.Client.Controls.Filters;

public sealed partial class SqlFilterBuilder : UserControl
{
    private sealed class FilterRow
    {
        public required Grid Root { get; init; }
        public required Button RemoveButton { get; init; }
        public required ComboBox ColumnCombo { get; init; }
        public required DropDownButton ComparisonButton { get; init; }
        public required ContentControl ValueHost { get; init; }
        public required ContentControl ValueHostTo { get; init; }
        public ComparisonOption? SelectedComparison { get; set; }
    }

    private sealed class ComparisonOption
    {
        public ComparisonOption(SqlFilterComparison comparison, string label, string iconPath)
        {
            Comparison = comparison;
            Label = label;
            IconPath = iconPath;
        }

        public SqlFilterComparison Comparison { get; }
        public string Label { get; }
        public string IconPath { get; }
    }

    private sealed class BoolOption
    {
        public BoolOption(object value, string label)
        {
            Value = value;
            Label = label;
        }

        public object Value { get; }
        public string Label { get; }
    }

    private enum BoolFilterValue
    {
        True,
        False,
        Empty,
        NotEmpty
    }

    private const int DefaultRowCount = 3;
    private const string FilterIconsBaseUri = "ms-appx:///Assets/Icons/FilterOperators/";
    private const double ComparisonIconWidth = 24;
    private const double ComparisonIconHeight = 16;
    private const double FilterControlHeight = 28;
    private const double FilterControlFontSize = 12;
    private const double RemoveButtonWidth = 32;
    private const double ColumnComboWidth = 140;
    private const double ComparisonButtonWidth = 42;
    private const double ValueEditorWidth = 110;
    private const double ValueEditorFullWidth = 110*2+4;
    private static readonly Thickness ValueEditorBorderThickness = new(0);
    private static readonly Thickness FocusedValueEditorBorderThickness = new(0, 0, 0, 1);
    private readonly List<FilterRow> _rows = [];
    private readonly List<SqlFilterColumn> _columns = [];
    private readonly List<SqlFilterDefaultRule> _defaultRules = [];
    private bool _isUpdating;

    public static readonly DependencyProperty MaxSegmentCountProperty =
        DependencyProperty.Register(
            nameof(MaxSegmentCount),
            typeof(int),
            typeof(SqlFilterBuilder),
            new PropertyMetadata(6, OnMaxSegmentCountChanged));

    public static readonly DependencyProperty AutoApplyDefaultFiltersProperty =
        DependencyProperty.Register(
            nameof(AutoApplyDefaultFilters),
            typeof(bool),
            typeof(SqlFilterBuilder),
            new PropertyMetadata(false));

    public SqlFilterBuilder()
    {
        InitializeComponent();
        RebuildRowsFromDefaults();
    }

    public event EventHandler? FilterChanged;

    public int MaxSegmentCount
    {
        get => (int)GetValue(MaxSegmentCountProperty);
        set => SetValue(MaxSegmentCountProperty, value);
    }

    public bool AutoApplyDefaultFilters
    {
        get => (bool)GetValue(AutoApplyDefaultFiltersProperty);
        set => SetValue(AutoApplyDefaultFiltersProperty, value);
    }

    private int EffectiveMaxSegmentCount => Math.Max(1, MaxSegmentCount);

    public void SetColumns(IEnumerable<SqlFilterColumn> columns)
    {
        SetColumns(columns, null);
    }

    public void SetColumns(IEnumerable<SqlFilterColumn> columns, IEnumerable<SqlFilterDefaultRule>? defaultRules)
    {
        SetColumns(columns, defaultRules, AutoApplyDefaultFilters);
    }

    public void SetColumns(
        IEnumerable<SqlFilterColumn> columns,
        IEnumerable<SqlFilterDefaultRule>? defaultRules,
        bool applyDefaultFilters)
    {
        ArgumentNullException.ThrowIfNull(columns);

        _columns.Clear();
        _columns.AddRange(columns);

        _defaultRules.Clear();
        if (defaultRules != null)
        {
            _defaultRules.AddRange(defaultRules);
        }

        RebuildRowsFromDefaults();
        if (applyDefaultFilters)
        {
            RequestApplyFilter();
        }
    }

    public SqlFilterResult BuildSql()
    {
        var parts = new List<string>();
        var parameters = new Dictionary<string, object?>(StringComparer.OrdinalIgnoreCase);

        foreach (var row in _rows)
        {
            var column = row.ColumnCombo.SelectedItem as SqlFilterColumn;
            var comparison = row.SelectedComparison?.Comparison;
            if (column == null || comparison == null)
            {
                continue;
            }

            var clause = BuildClause(row, column, comparison.Value, parameters);
            if (!string.IsNullOrWhiteSpace(clause))
            {
                parts.Add($"({clause})");
            }
        }

        return new SqlFilterResult(
            string.Join(" AND ", parts),
            parameters);
    }

    private static void OnMaxSegmentCountChanged(DependencyObject sender, DependencyPropertyChangedEventArgs e)
    {
        if (sender is SqlFilterBuilder filterBuilder)
        {
            filterBuilder.ApplyMaxSegmentCount();
        }
    }

    private void ApplyMaxSegmentCount()
    {
        var maxSegmentCount = EffectiveMaxSegmentCount;
        while (_rows.Count > maxSegmentCount)
        {
            var lastIndex = _rows.Count - 1;
            RowsPanel.Children.Remove(_rows[lastIndex].Root);
            _rows.RemoveAt(lastIndex);
        }

        if (_rows.Count == 0)
        {
            AddRow(_columns.FirstOrDefault(), null);
            return;
        }

        UpdateAddRuleButtonState();
    }

    private void RebuildRowsFromDefaults()
    {
        _isUpdating = true;
        try
        {
            ClearRows();
            _rows.Clear();

            var rowCount = Math.Min(
                EffectiveMaxSegmentCount,
                Math.Max(DefaultRowCount, _defaultRules.Count));
            for (int i = 0; i < rowCount; i++)
            {
                var row = CreateRow();
                _rows.Add(row);
                var defaultRule = i < _defaultRules.Count ? _defaultRules[i] : null;
                ApplyDefaultRule(row, defaultRule);
            }
        }
        finally
        {
            _isUpdating = false;
        }

        for (int i = 0; i < _rows.Count; i++)
        {
            var row = _rows[i];
            ConfigureComparison(row);
            var defaultRule = i < _defaultRules.Count ? _defaultRules[i] : null;
            ApplyDefaultValues(row, defaultRule);
        }

        UpdateAddRuleButtonState();
    }

    private FilterRow CreateRow()
    {
        var grid = new Grid
        {
            ColumnSpacing = 0,
            Margin = new Thickness(0, 0, 0, 0),
            Padding = new Thickness(0, 0, 0, 0),
            BorderBrush = (Brush)Application.Current.Resources["ControlElevationBorderBrush"],
            BorderThickness = new Thickness(1),
            CornerRadius = new CornerRadius(4),
            Background = (Brush)Application.Current.Resources["CardBackgroundFillColorDefaultBrush"]

        };

        grid.ColumnDefinitions.Add(new ColumnDefinition { Width = new GridLength(RemoveButtonWidth) });
        grid.ColumnDefinitions.Add(new ColumnDefinition { Width = new GridLength(ColumnComboWidth) });
        grid.ColumnDefinitions.Add(new ColumnDefinition { Width = new GridLength(ComparisonButtonWidth) });
        grid.ColumnDefinitions.Add(new ColumnDefinition { Width = new GridLength(ValueEditorWidth) });
        grid.ColumnDefinitions.Add(new ColumnDefinition { Width = new GridLength(ValueEditorWidth) });

        var removeButton = new Button
        {
            Content = new FontIcon
            {
                Glyph = "\uE894",
                FontSize = 10,
                VerticalAlignment = VerticalAlignment.Center,
                HorizontalAlignment = HorizontalAlignment.Center
            },
            Style = (Style)Application.Current.Resources["SubtleButtonStyle"],
            Foreground = (Brush)Application.Current.Resources["SystemFillColorCriticalBrush"],
            Width = RemoveButtonWidth,
            Height = FilterControlHeight,
            MinHeight = FilterControlHeight,
            MaxHeight = FilterControlHeight,
            FontSize = FilterControlFontSize,
            VerticalAlignment = VerticalAlignment.Top,
            HorizontalAlignment = HorizontalAlignment.Center,
            CornerRadius = new CornerRadius(0)
        };

        var columnCombo = new ComboBox
        {
            PlaceholderText = "Column",
            DisplayMemberPath = nameof(SqlFilterColumn.Label),
            ItemsSource = _columns,
            Height = FilterControlHeight,
            MinHeight = FilterControlHeight,
            MaxHeight = FilterControlHeight,
            FontSize = FilterControlFontSize,
            HorizontalAlignment = HorizontalAlignment.Stretch,
            VerticalAlignment = VerticalAlignment.Top,
            BorderThickness = new Thickness(0),
            CornerRadius = new CornerRadius(0)
        };

        var comparisonButton = new DropDownButton
        {
            Width = ComparisonButtonWidth,
            Height = FilterControlHeight,
            MinHeight = FilterControlHeight,
            MaxHeight = FilterControlHeight,
            FontSize = FilterControlFontSize,
            HorizontalAlignment = HorizontalAlignment.Center,
            VerticalAlignment = VerticalAlignment.Top,
            Style = (Style)Application.Current.Resources["SubtleButtonStyle"],
            Padding = new Thickness(0),
            IsEnabled = false,
            CornerRadius = new CornerRadius(0)
        };

        var valueHost = new ContentControl
        {
            HorizontalAlignment = HorizontalAlignment.Left,
            HorizontalContentAlignment = HorizontalAlignment.Stretch,
            Width = ValueEditorWidth
        };

        var valueHostTo = new ContentControl
        {
            HorizontalAlignment = HorizontalAlignment.Left,
            HorizontalContentAlignment = HorizontalAlignment.Stretch,
            Width = ValueEditorWidth,
            Visibility = Visibility.Collapsed
        };

        Grid.SetColumn(removeButton, 0);
        Grid.SetColumn(columnCombo, 1);
        Grid.SetColumn(comparisonButton, 2);
        Grid.SetColumn(valueHost, 3);
        Grid.SetColumn(valueHostTo, 4);

        grid.Children.Add(removeButton);
        grid.Children.Add(columnCombo);
        grid.Children.Add(comparisonButton);
        grid.Children.Add(valueHost);
        grid.Children.Add(valueHostTo);
        AddRowToPanel(grid);

        var row = new FilterRow
        {
            Root = grid,
            RemoveButton = removeButton,
            ColumnCombo = columnCombo,
            ComparisonButton = comparisonButton,
            ValueHost = valueHost,
            ValueHostTo = valueHostTo
        };

        columnCombo.SelectionChanged += (_, _) => ConfigureComparison(row);
        removeButton.Click += (_, _) => RemoveRow(row);

        return row;
    }

    private void ClearRows()
    {
        foreach (var row in _rows)
        {
            RowsPanel.Children.Remove(row.Root);
        }
    }

    private void AddRowToPanel(Grid grid)
    {
        var actionButtonsIndex = RowsPanel.Children.IndexOf(ActionButtonsPanel);
        if (actionButtonsIndex < 0)
        {
            RowsPanel.Children.Add(grid);
            return;
        }

        RowsPanel.Children.Insert(actionButtonsIndex, grid);
    }

    private void ApplyDefaultRule(FilterRow row, SqlFilterDefaultRule? defaultRule)
    {
        row.ColumnCombo.ItemsSource = _columns;

        if (defaultRule == null)
        {
            row.ColumnCombo.SelectedIndex = -1;
            row.SelectedComparison = null;
            return;
        }

        var column = _columns.FirstOrDefault(c => c.SqlName.Equals(defaultRule.SqlName, StringComparison.OrdinalIgnoreCase));
        row.ColumnCombo.SelectedItem = column;
        row.SelectedComparison = column == null
            ? null
            : new ComparisonOption(defaultRule.Comparison, string.Empty, string.Empty);
    }

    private static void ApplyDefaultValues(FilterRow row, SqlFilterDefaultRule? defaultRule)
    {
        if (defaultRule == null)
        {
            return;
        }

        var column = row.ColumnCombo.SelectedItem as SqlFilterColumn;
        var comparison = row.SelectedComparison?.Comparison;
        if (column == null || comparison == null)
        {
            return;
        }

        ApplyDefaultValue(row.ValueHost.Content, comparison.Value, defaultRule.Value);
        if (comparison == SqlFilterComparison.Between)
        {
            ApplyDefaultValue(row.ValueHostTo.Content, SqlFilterComparison.Equals, defaultRule.ValueTo);
        }
    }

    private void RemoveRow(FilterRow row)
    {
        RowsPanel.Children.Remove(row.Root);
        _rows.Remove(row);

        if (_rows.Count > 0)
        {
            UpdateAddRuleButtonState();
            return;
        }

        AddRow(_columns.FirstOrDefault(), null);
    }

    private void AddRow(SqlFilterColumn? selectedColumn, SqlFilterComparison? selectedComparison)
    {
        if (_rows.Count >= EffectiveMaxSegmentCount)
        {
            UpdateAddRuleButtonState();
            return;
        }

        var row = CreateRow();
        _rows.Add(row);

        _isUpdating = true;
        row.ColumnCombo.SelectedItem = selectedColumn ?? _columns.FirstOrDefault();
        _isUpdating = false;

        ConfigureComparison(row, selectedComparison);
        UpdateAddRuleButtonState();
    }

    private void UpdateAddRuleButtonState()
    {
        AddRuleButton.IsEnabled = _rows.Count < EffectiveMaxSegmentCount;
    }

    private void ConfigureComparison(FilterRow row, SqlFilterComparison? preferredComparison = null)
    {
        if (_isUpdating)
        {
            return;
        }

        var column = row.ColumnCombo.SelectedItem as SqlFilterColumn;
        if (column == null)
        {
            ResetRow(row);
            return;
        }

        _isUpdating = true;
        try
        {
            var options = GetComparisonOptions(column.ValueType);
            var selectedComparison = preferredComparison ?? row.SelectedComparison?.Comparison;
            var selected = options.FirstOrDefault(x => x.Comparison == selectedComparison)
                ?? options.FirstOrDefault();

            ConfigureComparisonMenu(row, options, selected);
        }
        finally
        {
            _isUpdating = false;
        }

        ConfigureValueEditors(row);
    }

    private void ConfigureValueEditors(FilterRow row)
    {
        if (_isUpdating)
        {
            return;
        }

        var column = row.ColumnCombo.SelectedItem as SqlFilterColumn;
        var comparison = row.SelectedComparison?.Comparison;
        if (column == null || comparison == null)
        {
            row.ValueHost.Content = null;
            row.ValueHostTo.Content = null;
            row.ValueHostTo.Visibility = Visibility.Collapsed;
            return;
        }

        var isBetween = comparison == SqlFilterComparison.Between;
        ConfigureValueHostsLayout(row, isBetween);

        var valueEditor = CreateValueEditor(column.ValueType, comparison.Value, isBetween ? "From" : "Value");
        ApplyEditorLayout(valueEditor, row.ValueHost.Width);
        row.ValueHost.Content = valueEditor;

        row.ValueHostTo.Content = isBetween
            ? CreateValueEditor(column.ValueType, SqlFilterComparison.Equals, "To")
            : null;
        ApplyEditorLayout(row.ValueHostTo.Content as FrameworkElement, row.ValueHostTo.Width);
        row.ValueHostTo.Visibility = isBetween ? Visibility.Visible : Visibility.Collapsed;
    }

    private void ResetRow(FilterRow row)
    {
        row.SelectedComparison = null;
        row.ComparisonButton.Flyout = null;
        row.ComparisonButton.Content = null;
        row.ComparisonButton.IsEnabled = false;
        ToolTipService.SetToolTip(row.ComparisonButton, null);
        row.ValueHost.Content = null;
        row.ValueHostTo.Content = null;
        row.ValueHostTo.Visibility = Visibility.Collapsed;
        ConfigureValueHostsLayout(row, false);
    }

    private static void ConfigureValueHostsLayout(FilterRow row, bool isBetween)
    {
        Grid.SetColumn(row.ValueHost, 3);
        Grid.SetColumn(row.ValueHostTo, 4);

        if (isBetween)
        {
            Grid.SetColumnSpan(row.ValueHost, 1);
            row.ValueHost.Width = ValueEditorWidth;
            row.ValueHost.MinWidth = ValueEditorWidth;
            row.ValueHost.MaxWidth = ValueEditorWidth;
            row.ValueHostTo.Width = ValueEditorWidth;
            row.ValueHostTo.MinWidth = ValueEditorWidth;
            row.ValueHostTo.MaxWidth = ValueEditorWidth;
            return;
        }

        Grid.SetColumnSpan(row.ValueHost, 2);
        row.ValueHost.Width = ValueEditorFullWidth;
        row.ValueHost.MinWidth = ValueEditorFullWidth;
        row.ValueHost.MaxWidth = ValueEditorFullWidth;
        row.ValueHostTo.Width = 0;
        row.ValueHostTo.MinWidth = 0;
        row.ValueHostTo.MaxWidth = 0;
    }

    private static void ApplyEditorLayout(FrameworkElement? editor, double width)
    {
        if (editor == null)
        {
            return;
        }

        editor.HorizontalAlignment = HorizontalAlignment.Left;
        editor.Width = width;
        editor.MinWidth = width;
        editor.MaxWidth = width;
        editor.Height = FilterControlHeight;
        editor.MinHeight = FilterControlHeight;
        editor.MaxHeight = FilterControlHeight;
        

        if (editor is Control control)
        {
            control.FontSize = FilterControlFontSize;
            control.CornerRadius = new CornerRadius(0);
            ConfigureValueEditorBorder(control);
        }
    }

    private static void ConfigureValueEditorBorder(Control control)
    {
        control.UseSystemFocusVisuals = false;
        control.BorderThickness = ValueEditorBorderThickness;
        control.Resources["TextControlBorderThemeThicknessFocused"] = FocusedValueEditorBorderThickness;
        control.Resources["ComboBoxBackgroundBorderBrushFocused"] = new SolidColorBrush(Microsoft.UI.Colors.Transparent);

        control.GotFocus += ValueEditor_GotFocus;
        control.LostFocus += ValueEditor_LostFocus;
    }

    private static void ValueEditor_GotFocus(object sender, RoutedEventArgs e)
    {
        if (sender is Control control)
        {
            control.BorderThickness = FocusedValueEditorBorderThickness;
        }
    }

    private static void ValueEditor_LostFocus(object sender, RoutedEventArgs e)
    {
        if (sender is Control control)
        {
            control.BorderThickness = ValueEditorBorderThickness;
        }
    }

    private void ConfigureComparisonMenu(FilterRow row, IReadOnlyList<ComparisonOption> options, ComparisonOption? selected)
    {
        var menu = new MenuFlyout
        {
            Placement = FlyoutPlacementMode.Bottom
        };
        var comparisonItemStyle = RootGrid.Resources.TryGetValue("ComparisonMenuFlyoutItemStyle", out var styleValue)
            ? styleValue as Style
            : null;

        foreach (var option in options)
        {
            var selectedOption = option;
            var item = new MenuFlyoutItem
            {
                Text = selectedOption.Label,
                Icon = new ImageIcon
                {
                    Source = CreateComparisonSvgSource(selectedOption.IconPath),
                    Width = ComparisonIconWidth,
                    Height = ComparisonIconHeight
                }
            };

            if (comparisonItemStyle != null)
            {
                item.Style = comparisonItemStyle;
            }

            item.Click += (_, _) => OnComparisonSelected(row, selectedOption);
            menu.Items.Add(item);
        }

        row.ComparisonButton.Flyout = menu;
        SetSelectedComparison(row, selected ?? options.FirstOrDefault());
    }

    private void SetSelectedComparison(FilterRow row, ComparisonOption? option)
    {
        row.SelectedComparison = option;

        if (option == null)
        {
            row.ComparisonButton.Content = null;
            row.ComparisonButton.IsEnabled = false;
            ToolTipService.SetToolTip(row.ComparisonButton, null);
            return;
        }

        row.ComparisonButton.IsEnabled = true;
        row.ComparisonButton.Content = new Image
        {
            Width = ComparisonIconWidth,
            Height = ComparisonIconHeight,
            Source = CreateComparisonSvgSource(option.IconPath)
        };
        ToolTipService.SetToolTip(row.ComparisonButton, option.Label);
    }

    private void OnComparisonSelected(FilterRow row, ComparisonOption selectedOption)
    {
        if (_isUpdating || row.SelectedComparison?.Comparison == selectedOption.Comparison)
        {
            return;
        }

        SetSelectedComparison(row, selectedOption);
        ConfigureValueEditors(row);
    }

    private static SvgImageSource CreateComparisonSvgSource(string iconPath)
    {
        return new SvgImageSource(new Uri($"{FilterIconsBaseUri}{iconPath}"));
    }

    private FrameworkElement CreateValueEditor(
        Type valueType,
        SqlFilterComparison comparison,
        string placeholderText)
    {
        if (comparison == SqlFilterComparison.IsEmpty)
        {
            var options = new List<BoolOption>
            {
                new(true, "Yes"),
                new(false, "No")
            };

            var combo = new ComboBox
            {
                DisplayMemberPath = nameof(BoolOption.Label),
                ItemsSource = options,
                SelectedIndex = 0
            };
            combo.KeyDown += ValueEditor_KeyDown;
            return combo;
        }

        var effectiveType = Nullable.GetUnderlyingType(valueType) ?? valueType;
        if (effectiveType == typeof(bool))
        {
            var options = new List<BoolOption>
            {
                new(BoolFilterValue.True, "True"),
                new(BoolFilterValue.False, "False"),
                new(BoolFilterValue.Empty, "Empty"),
                new(BoolFilterValue.NotEmpty, "Not empty")
            };

            var combo = new ComboBox
            {
                DisplayMemberPath = nameof(BoolOption.Label),
                ItemsSource = options
            };
            combo.KeyDown += ValueEditor_KeyDown;
            return combo;
        }

        if (effectiveType == typeof(DateTime) || effectiveType == typeof(DateOnly))
        {
            var datePicker = new CalendarDatePicker
            {
                PlaceholderText = placeholderText
            };
            datePicker.KeyDown += ValueEditor_KeyDown;
            return datePicker;
        }

        var textBox = new TextBox
        {
            PlaceholderText = placeholderText,
            AcceptsReturn = false
        };
        textBox.KeyDown += ValueEditor_KeyDown;
        return textBox;
    }

    private string? BuildClause(
        FilterRow row,
        SqlFilterColumn column,
        SqlFilterComparison comparison,
        Dictionary<string, object?> parameters)
    {
        var sqlName = column.SqlName;
        if (!IsSafeSqlName(sqlName))
        {
            return null;
        }

        if (comparison == SqlFilterComparison.IsEmpty)
        {
            var shouldBeEmpty = ReadBoolOption(row.ValueHost.Content) ?? true;
            if (IsTextType(column.ValueType))
            {
                return shouldBeEmpty
                    ? $"{sqlName} IS NULL OR {sqlName} = ''"
                    : $"{sqlName} IS NOT NULL AND {sqlName} <> ''";
            }

            return shouldBeEmpty
                ? $"{sqlName} IS NULL"
                : $"{sqlName} IS NOT NULL";
        }

        if (IsBoolType(column.ValueType) && comparison == SqlFilterComparison.Equals)
        {
            return BuildBoolClause(row, sqlName, parameters);
        }

        var value = ReadValue(row.ValueHost.Content, column.ValueType);
        if (value == null)
        {
            return null;
        }

        if (IsDateTimeType(column.ValueType) && value is DateTime dateTimeValue)
        {
            return BuildDateTimeClause(row, sqlName, comparison, dateTimeValue, parameters);
        }

        if (comparison == SqlFilterComparison.Between)
        {
            var valueTo = ReadValue(row.ValueHostTo.Content, column.ValueType);
            if (valueTo == null)
            {
                return null;
            }

            var fromName = AddParameter(parameters, value);
            var toName = AddParameter(parameters, valueTo);
            return $"{sqlName} BETWEEN @{fromName} AND @{toName}";
        }

        var parameterName = AddParameter(parameters, BuildParameterValue(value, comparison, column.ValueType));
        if (IsTextType(column.ValueType))
        {
            return comparison switch
            {
                SqlFilterComparison.Contains => $"{sqlName} ILIKE @{parameterName} ESCAPE '\\'",
                SqlFilterComparison.StartsWith => $"{sqlName} ILIKE @{parameterName} ESCAPE '\\'",
                SqlFilterComparison.EndsWith => $"{sqlName} ILIKE @{parameterName} ESCAPE '\\'",
                SqlFilterComparison.Equals => $"{sqlName} ILIKE @{parameterName} ESCAPE '\\'",
                SqlFilterComparison.NotEquals => $"{sqlName} IS NULL OR {sqlName} NOT ILIKE @{parameterName} ESCAPE '\\'",
                _ => null
            };
        }

        return comparison switch
        {
            SqlFilterComparison.Equals => $"{sqlName} = @{parameterName}",
            SqlFilterComparison.NotEquals => $"{sqlName} IS NULL OR {sqlName} <> @{parameterName}",
            SqlFilterComparison.GreaterThan => $"{sqlName} > @{parameterName}",
            SqlFilterComparison.GreaterOrEqual => $"{sqlName} >= @{parameterName}",
            SqlFilterComparison.LessThan => $"{sqlName} < @{parameterName}",
            SqlFilterComparison.LessOrEqual => $"{sqlName} <= @{parameterName}",
            _ => null
        };
    }

    private static string? BuildDateTimeClause(
        FilterRow row,
        string sqlName,
        SqlFilterComparison comparison,
        DateTime value,
        Dictionary<string, object?> parameters)
    {
        var from = value.Date;
        var to = GetNextDateStart(from);

        if (comparison == SqlFilterComparison.Between)
        {
            var valueTo = ReadValue(row.ValueHostTo.Content, typeof(DateTime));
            if (valueTo is not DateTime dateTimeTo)
            {
                return null;
            }

            var fromName = AddParameter(parameters, from);
            var toName = AddParameter(parameters, GetNextDateStart(dateTimeTo.Date));
            return $"{sqlName} >= @{fromName} AND {sqlName} < @{toName}";
        }

        return comparison switch
        {
            SqlFilterComparison.Equals => BuildDateRangeClause(sqlName, parameters, from, to),
            SqlFilterComparison.NotEquals => BuildDateOutsideRangeClause(sqlName, parameters, from, to),
            SqlFilterComparison.GreaterThan => $"{sqlName} >= @{AddParameter(parameters, to)}",
            SqlFilterComparison.GreaterOrEqual => $"{sqlName} >= @{AddParameter(parameters, from)}",
            SqlFilterComparison.LessThan => $"{sqlName} < @{AddParameter(parameters, from)}",
            SqlFilterComparison.LessOrEqual => $"{sqlName} < @{AddParameter(parameters, to)}",
            _ => null
        };
    }

    private static string BuildDateRangeClause(
        string sqlName,
        Dictionary<string, object?> parameters,
        DateTime from,
        DateTime to)
    {
        var fromName = AddParameter(parameters, from);
        var toName = AddParameter(parameters, to);
        return $"{sqlName} >= @{fromName} AND {sqlName} < @{toName}";
    }

    private static string BuildDateOutsideRangeClause(
        string sqlName,
        Dictionary<string, object?> parameters,
        DateTime from,
        DateTime to)
    {
        var fromName = AddParameter(parameters, from);
        var toName = AddParameter(parameters, to);
        return $"{sqlName} IS NULL OR {sqlName} < @{fromName} OR {sqlName} >= @{toName}";
    }

    private static DateTime GetNextDateStart(DateTime value)
    {
        var date = value.Date;
        return date == DateTime.MaxValue.Date
            ? DateTime.MaxValue
            : date.AddDays(1);
    }

    private static string? BuildBoolClause(
        FilterRow row,
        string sqlName,
        Dictionary<string, object?> parameters)
    {
        var value = ReadBoolFilterValue(row.ValueHost.Content);
        if (value == null)
        {
            return null;
        }

        return value.Value switch
        {
            BoolFilterValue.True => BuildBoolEqualsClause(sqlName, parameters, true),
            BoolFilterValue.False => BuildBoolEqualsClause(sqlName, parameters, false),
            BoolFilterValue.Empty => $"{sqlName} IS NULL",
            BoolFilterValue.NotEmpty => $"{sqlName} IS NOT NULL",
            _ => null
        };
    }

    private static string BuildBoolEqualsClause(
        string sqlName,
        Dictionary<string, object?> parameters,
        bool value)
    {
        var parameterName = AddParameter(parameters, value);
        return $"{sqlName} = @{parameterName}";
    }

    private static object BuildParameterValue(
        object value,
        SqlFilterComparison comparison,
        Type valueType)
    {
        if (!IsTextType(valueType))
        {
            return value;
        }

        var text = EscapeLikeValue(Convert.ToString(value, CultureInfo.CurrentCulture) ?? string.Empty);
        return comparison switch
        {
            SqlFilterComparison.Contains => $"%{text}%",
            SqlFilterComparison.StartsWith => $"{text}%",
            SqlFilterComparison.EndsWith => $"%{text}",
            _ => text
        };
    }

    private static object? ReadValue(object? editor, Type valueType)
    {
        var effectiveType = Nullable.GetUnderlyingType(valueType) ?? valueType;

        if (editor is ComboBox combo)
        {
            if (combo.SelectedItem is BoolOption boolOption)
            {
                return boolOption.Value;
            }

            return null;
        }

        if (editor is CalendarDatePicker datePicker)
        {
            if (datePicker.Date is not DateTimeOffset date)
            {
                return null;
            }

            if (effectiveType == typeof(DateOnly))
            {
                return DateOnly.FromDateTime(date.DateTime);
            }

            return date.DateTime;
        }

        if (editor is not TextBox textBox)
        {
            return null;
        }

        var text = textBox.Text?.Trim();
        if (string.IsNullOrWhiteSpace(text))
        {
            return null;
        }

        if (effectiveType == typeof(string))
        {
            return text;
        }

        if (effectiveType == typeof(int) && int.TryParse(text, NumberStyles.Integer, CultureInfo.CurrentCulture, out var intValue))
        {
            return intValue;
        }

        if (effectiveType == typeof(long) && long.TryParse(text, NumberStyles.Integer, CultureInfo.CurrentCulture, out var longValue))
        {
            return longValue;
        }

        if (effectiveType == typeof(decimal) && decimal.TryParse(text, NumberStyles.Number, CultureInfo.CurrentCulture, out var decimalValue))
        {
            return decimalValue;
        }

        if (effectiveType == typeof(double) && double.TryParse(text, NumberStyles.Number, CultureInfo.CurrentCulture, out var doubleValue))
        {
            return doubleValue;
        }

        if (effectiveType == typeof(float) && float.TryParse(text, NumberStyles.Number, CultureInfo.CurrentCulture, out var floatValue))
        {
            return floatValue;
        }

        if (effectiveType == typeof(DateTime) && DateTime.TryParse(text, CultureInfo.CurrentCulture, DateTimeStyles.None, out var dateTimeValue))
        {
            return dateTimeValue;
        }

        return null;
    }

    private static bool? ReadBoolOption(object? editor)
    {
        return editor is ComboBox { SelectedItem: BoolOption { Value: bool value } }
            ? value
            : null;
    }

    private static BoolFilterValue? ReadBoolFilterValue(object? editor)
    {
        return editor is ComboBox { SelectedItem: BoolOption { Value: BoolFilterValue value } }
            ? value
            : null;
    }

    private static string AddParameter(Dictionary<string, object?> parameters, object value)
    {
        var name = $"p{parameters.Count}";
        parameters.Add(name, value);
        return name;
    }

    private static void ApplyDefaultValue(
        object? editor,
        SqlFilterComparison comparison,
        object? value)
    {
        if (editor == null || value == null)
        {
            return;
        }

        if (editor is ComboBox combo)
        {
            ApplyDefaultComboValue(combo, comparison, value);
            return;
        }

        if (editor is CalendarDatePicker datePicker)
        {
            datePicker.Date = ConvertToDateTimeOffset(value);
            return;
        }

        if (editor is TextBox textBox)
        {
            textBox.Text = Convert.ToString(value, CultureInfo.CurrentCulture) ?? string.Empty;
        }
    }

    private static void ApplyDefaultComboValue(
        ComboBox combo,
        SqlFilterComparison comparison,
        object value)
    {
        foreach (var item in combo.Items)
        {
            if (item is not BoolOption option)
            {
                continue;
            }

            if (IsMatchingComboValue(option.Value, comparison, value))
            {
                combo.SelectedItem = option;
                return;
            }
        }
    }

    private static bool IsMatchingComboValue(
        object optionValue,
        SqlFilterComparison comparison,
        object value)
    {
        if (comparison == SqlFilterComparison.IsEmpty)
        {
            return optionValue is bool optionBool
                && TryConvertToBool(value, out var valueBool)
                && optionBool == valueBool;
        }

        if (optionValue is BoolFilterValue optionFilterValue)
        {
            return TryConvertToBoolFilterValue(value, out var valueFilterValue)
                && optionFilterValue == valueFilterValue;
        }

        return Equals(optionValue, value);
    }

    private static bool TryConvertToBool(object value, out bool result)
    {
        if (value is bool boolValue)
        {
            result = boolValue;
            return true;
        }

        return bool.TryParse(Convert.ToString(value, CultureInfo.CurrentCulture), out result);
    }

    private static bool TryConvertToBoolFilterValue(object value, out BoolFilterValue result)
    {
        if (value is BoolFilterValue filterValue)
        {
            result = filterValue;
            return true;
        }

        if (value is bool boolValue)
        {
            result = boolValue ? BoolFilterValue.True : BoolFilterValue.False;
            return true;
        }

        var text = Convert.ToString(value, CultureInfo.CurrentCulture);
        if (Enum.TryParse(text?.Replace(" ", string.Empty, StringComparison.Ordinal), true, out result))
        {
            return true;
        }

        result = default;
        return false;
    }

    private static DateTimeOffset? ConvertToDateTimeOffset(object value)
    {
        if (value is DateTimeOffset dateTimeOffset)
        {
            return dateTimeOffset;
        }

        if (value is DateTime dateTime)
        {
            return new DateTimeOffset(dateTime);
        }

        if (value is DateOnly dateOnly)
        {
            return new DateTimeOffset(dateOnly.ToDateTime(TimeOnly.MinValue));
        }

        return DateTimeOffset.TryParse(
            Convert.ToString(value, CultureInfo.CurrentCulture),
            CultureInfo.CurrentCulture,
            DateTimeStyles.None,
            out var parsed)
            ? parsed
            : null;
    }

    private static IReadOnlyList<ComparisonOption> GetComparisonOptions(Type valueType)
    {
        var effectiveType = Nullable.GetUnderlyingType(valueType) ?? valueType;

        if (effectiveType == typeof(string))
        {
            return
            [
                new(SqlFilterComparison.Contains, "Contains", "contains.svg"),
                new(SqlFilterComparison.StartsWith, "Starts with", "starts_with.svg"),
                new(SqlFilterComparison.EndsWith, "Ends with", "ends_with.svg"),
                new(SqlFilterComparison.Equals, "Equals", "equals.svg"),
                new(SqlFilterComparison.NotEquals, "Does not equal", "not_equals.svg"),
                new(SqlFilterComparison.IsEmpty, "Is empty", "is_empty.svg")
            ];
        }

        if (effectiveType == typeof(bool))
        {
            return
            [
                new(SqlFilterComparison.Equals, "Equals", "equals.svg")
            ];
        }

        if (IsRangeType(effectiveType))
        {
            return
            [
                new(SqlFilterComparison.Equals, "Equals", "equals.svg"),
                new(SqlFilterComparison.NotEquals, "Does not equal", "not_equals.svg"),
                new(SqlFilterComparison.GreaterThan, "Greater than", "greater_than.svg"),
                new(SqlFilterComparison.GreaterOrEqual, "Greater than or equal", "greater_or_equal.svg"),
                new(SqlFilterComparison.LessThan, "Less than", "less_than.svg"),
                new(SqlFilterComparison.LessOrEqual, "Less than or equal", "less_or_equal.svg"),
                new(SqlFilterComparison.Between, "Between", "between.svg"),
                new(SqlFilterComparison.IsEmpty, "Is empty", "is_empty.svg")
            ];
        }

        return
        [
            new(SqlFilterComparison.Equals, "Equals", "equals.svg"),
            new(SqlFilterComparison.NotEquals, "Does not equal", "not_equals.svg"),
            new(SqlFilterComparison.IsEmpty, "Is empty", "is_empty.svg")
        ];
    }

    private static bool IsTextType(Type valueType)
    {
        var effectiveType = Nullable.GetUnderlyingType(valueType) ?? valueType;
        return effectiveType == typeof(string);
    }

    private static bool IsBoolType(Type valueType)
    {
        var effectiveType = Nullable.GetUnderlyingType(valueType) ?? valueType;
        return effectiveType == typeof(bool);
    }

    private static bool IsDateTimeType(Type valueType)
    {
        var effectiveType = Nullable.GetUnderlyingType(valueType) ?? valueType;
        return effectiveType == typeof(DateTime);
    }

    private static bool IsRangeType(Type valueType)
    {
        return valueType == typeof(int)
            || valueType == typeof(long)
            || valueType == typeof(decimal)
            || valueType == typeof(double)
            || valueType == typeof(float)
            || valueType == typeof(DateTime)
            || valueType == typeof(DateOnly);
    }

    private static bool IsSafeSqlName(string value)
    {
        if (string.IsNullOrWhiteSpace(value))
        {
            return false;
        }

        foreach (var part in value.Split('.', StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries))
        {
            if (part.Length == 0 || !IsIdentifierStart(part[0]))
            {
                return false;
            }

            for (int i = 1; i < part.Length; i++)
            {
                if (!IsIdentifierPart(part[i]))
                {
                    return false;
                }
            }
        }

        return true;
    }

    private static bool IsIdentifierStart(char value)
    {
        return value == '_' || char.IsAsciiLetter(value);
    }

    private static bool IsIdentifierPart(char value)
    {
        return value == '_' || char.IsAsciiLetterOrDigit(value);
    }

    private static string EscapeLikeValue(string value)
    {
        return value
            .Replace("\\", "\\\\", StringComparison.Ordinal)
            .Replace("%", "\\%", StringComparison.Ordinal)
            .Replace("_", "\\_", StringComparison.Ordinal);
    }

    private void ClearButton_Click(object sender, RoutedEventArgs e)
    {
        RebuildRowsFromDefaults();
        ApplyFilter();
    }

    private void AddRuleButton_Click(object sender, RoutedEventArgs e)
    {
        AddRow(_columns.FirstOrDefault(), null);
    }

    private void ApplyButton_Click(object sender, RoutedEventArgs e)
    {
        ApplyFilter();
    }

    private void ValueEditor_KeyDown(object sender, KeyRoutedEventArgs e)
    {
        if (e.Key != VirtualKey.Enter)
        {
            return;
        }

        e.Handled = true;
        ApplyFilter();
    }

    private void RequestApplyFilter()
    {
        DispatcherQueue.TryEnqueue(ApplyFilter);
    }

    private void ApplyFilter()
    {
        FilterChanged?.Invoke(this, EventArgs.Empty);
    }
}

public sealed class SqlFilterColumn
{
    public SqlFilterColumn(string label, string sqlName, Type valueType)
    {
        Label = string.IsNullOrWhiteSpace(label) ? sqlName : label;
        SqlName = sqlName;
        ValueType = Nullable.GetUnderlyingType(valueType) ?? valueType;
    }

    public string Label { get; }
    public string SqlName { get; }
    public Type ValueType { get; }
}

public sealed record SqlFilterResult(
    string WhereSql,
    IReadOnlyDictionary<string, object?> Parameters)
{
    public bool HasFilter => !string.IsNullOrWhiteSpace(WhereSql);
}

public sealed record SqlFilterDefaultRule(
    string SqlName,
    SqlFilterComparison Comparison,
    object? Value = null,
    object? ValueTo = null);

public enum SqlFilterComparison
{
    Contains = 0,
    StartsWith = 1,
    EndsWith = 2,
    Equals = 3,
    NotEquals = 4,
    GreaterThan = 5,
    GreaterOrEqual = 6,
    LessThan = 7,
    LessOrEqual = 8,
    Between = 9,
    IsEmpty = 10
}
