using System.Data;
using System.Text;
using Npgsql;
using TravelAgencyWinForms.Data;
using TravelAgencyWinForms.Models;
using TravelAgencyWinForms.Utils;

namespace TravelAgencyWinForms.Forms;

public partial class TableEditorForm : Form
{
    private readonly EntityConfig _config;
    private readonly bool _canEdit;
    private DataTable _sourceTable = new();

    public TableEditorForm(EntityConfig config, bool canEdit)
    {
        _config = config;
        _canEdit = canEdit && !config.ReadOnly;

        InitializeComponent();
        ApplyStyle();
        ConfigureForm();
        Load += (_, _) => LoadData();
    }

    private void ApplyStyle()
    {
        AppTheme.ApplyForm(this);
        AppTheme.StyleGrid(dataGrid);
        AppTheme.StyleTextBox(searchTextBox);
        AppTheme.StylePrimaryButton(refreshButton);
        AppTheme.StyleSecondaryButton(addButton);
        AppTheme.StyleSecondaryButton(editButton);
        AppTheme.StyleDangerButton(deleteButton);

        headerPanel.BackColor = AppTheme.Surface;
        titleLabel.ForeColor = AppTheme.Text;
        subtitleLabel.ForeColor = AppTheme.Muted;
        searchLabel.ForeColor = AppTheme.Muted;
        infoLabel.ForeColor = AppTheme.Muted;
        emptyLabel.ForeColor = AppTheme.Muted;

        var image = AppTheme.LoadImage("empty_results.png");
        if (image != null)
            emptyPictureBox.Image = image;
    }

    private void ConfigureForm()
    {
        Text = _config.Title;
        titleLabel.Text = _config.Title;
        subtitleLabel.Text = _config.ReadOnly ? "Просмотр данных" : "Просмотр и редактирование данных";

        addButton.Enabled = _canEdit;
        editButton.Enabled = _canEdit && _config.Fields.Any(f => !f.IsKey);
        deleteButton.Enabled = _canEdit;
    }

    private void RefreshButton_Click(object? sender, EventArgs e)
    {
        LoadData();
    }

    private void AddButton_Click(object? sender, EventArgs e)
    {
        AddRecord();
    }

    private void EditButton_Click(object? sender, EventArgs e)
    {
        EditRecord();
    }

    private void DeleteButton_Click(object? sender, EventArgs e)
    {
        DeleteRecord();
    }

    private void SearchTextBox_TextChanged(object? sender, EventArgs e)
    {
        ApplySearch();
    }

    private void DataGrid_SelectionChanged(object? sender, EventArgs e)
    {
        UpdateCalculatedPanel();
    }

    private void LoadData()
    {
        Cursor = Cursors.WaitCursor;
        try
        {
            var (sql, parameters) = BuildSelectSql();
            _sourceTable = Db.Query(sql, parameters.ToArray());
            dataGrid.DataSource = _sourceTable;
            ApplyGridPresentation();
            UpdateEmptyState(_sourceTable.Rows.Count == 0);
            UpdateCalculatedPanel();
            infoLabel.Text = $"Записей: {_sourceTable.Rows.Count}";
        }
        catch (Exception ex)
        {
            MessageBox.Show(ErrorHelper.ToUserMessage(ex), "Загрузка", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }
        finally
        {
            Cursor = Cursors.Default;
        }
    }

    private (string Sql, List<NpgsqlParameter> Parameters) BuildSelectSql()
    {
        var parameters = new List<NpgsqlParameter>();
        string selectPart;

        if (_config.Fields.Count == 0)
        {
            selectPart = "*";
        }
        else
        {
            selectPart = string.Join(", ", _config.GridFields.Select(f => f.Name == "role" && _config.TableName == "app_users" ? "role::text AS role" : Q(f.Name)));
        }

        var sb = new StringBuilder();
        sb.Append("SELECT ").Append(selectPart).Append(" FROM ").Append(Q(_config.SourceName));

        var where = new List<string>();
        if (_config.RestrictByCurrentEmployee && !string.IsNullOrWhiteSpace(_config.RestrictColumn))
        {
            if (CurrentUser.ClientId == null)
            {
                where.Add("1 = 0");
            }
            else
            {
                where.Add(Q(_config.RestrictColumn) + " = @client_id");
                parameters.Add(new NpgsqlParameter("client_id", CurrentUser.ClientId.Value));
            }
        }

        if (where.Count > 0)
            sb.Append(" WHERE ").Append(string.Join(" AND ", where));

        if (!string.IsNullOrWhiteSpace(_config.OrderBy))
            sb.Append(" ORDER BY ").Append(_config.OrderBy);

        return (sb.ToString(), parameters);
    }

    private void ApplyGridPresentation()
    {
        PresentationHelper.ApplyHeaders(dataGrid);

        if (_config.Fields.Count == 0 || _config.ReadOnly)
            PresentationHelper.HideViewColumns(dataGrid);

        foreach (DataGridViewColumn column in dataGrid.Columns)
            if (CalculatedFields.IsCalculated(column.Name))
                column.Visible = false;

        if (dataGrid.Rows.Count > 0 && dataGrid.Columns.Cast<DataGridViewColumn>().Any(c => c.Visible))
        {
            var firstVisible = dataGrid.Columns.Cast<DataGridViewColumn>().First(c => c.Visible);
            dataGrid.CurrentCell = dataGrid.Rows[0].Cells[firstVisible.Index];
        }
    }

    private void UpdateEmptyState(bool isEmpty)
    {
        dataGrid.Visible = !isEmpty;
        emptyPanel.Visible = isEmpty;
    }

    private void UpdateCalculatedPanel()
    {
        calculatedPanel.Controls.Clear();
        var table = GetCurrentGridTable();
        var calculatedColumns = CalculatedFields.GetColumns(table);

        if (calculatedColumns.Count == 0)
        {
            calculatedPanel.Visible = false;
            return;
        }

        calculatedPanel.Visible = true;
        var selectedRow = GetSelectedRow();

        foreach (var columnName in calculatedColumns)
        {
            var value = "—";
            if (selectedRow != null && selectedRow.Table.Columns.Contains(columnName))
                value = CalculatedFields.FormatValue(columnName, selectedRow[columnName]);

            calculatedPanel.Controls.Add(AppTheme.CreateCard(CalculatedFields.GetLabel(columnName), value));
        }
    }

    private DataTable GetCurrentGridTable()
    {
        return dataGrid.DataSource switch
        {
            DataTable table => table,
            DataView view => view.ToTable(),
            _ => _sourceTable
        };
    }

    private void ApplySearch()
    {
        if (_sourceTable.Rows.Count == 0)
            return;

        var search = searchTextBox.Text.Trim().ToLowerInvariant();
        if (string.IsNullOrWhiteSpace(search))
        {
            dataGrid.DataSource = _sourceTable;
            ApplyGridPresentation();
            UpdateEmptyState(_sourceTable.Rows.Count == 0);
            UpdateCalculatedPanel();
            infoLabel.Text = $"Записей: {_sourceTable.Rows.Count}";
            return;
        }

        var filtered = _sourceTable.Clone();
        foreach (DataRow row in _sourceTable.Rows)
        {
            var haystack = string.Join(" ", row.ItemArray.Select(v => v?.ToString() ?? string.Empty)).ToLowerInvariant();
            if (haystack.Contains(search))
                filtered.ImportRow(row);
        }

        dataGrid.DataSource = filtered;
        ApplyGridPresentation();
        UpdateEmptyState(filtered.Rows.Count == 0);
        UpdateCalculatedPanel();
        infoLabel.Text = $"Найдено: {filtered.Rows.Count}";
    }

    private DataRow? GetSelectedRow()
    {
        if (dataGrid.CurrentRow?.DataBoundItem is DataRowView view)
            return view.Row;

        return null;
    }

    private void AddRecord()
    {
        using var dialog = new EntityEditDialog(_config);
        if (dialog.ShowDialog(this) != DialogResult.OK)
            return;

        try
        {
            Db.ResetIdentitySequences();
            var (sql, parameters) = BuildInsertSql(dialog.Values, dialog.PasswordWasEntered);
            Db.SetAppContext(CurrentUser.Login);
            Db.Execute(sql, parameters.ToArray());
            LoadData();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ErrorHelper.ToUserMessage(ex), "Добавление", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }
    }

    private void EditRecord()
    {
        var row = GetSelectedRow();
        if (row == null)
        {
            MessageBox.Show("Сначала выберите запись.", "Изменение", MessageBoxButtons.OK, MessageBoxIcon.Information);
            return;
        }

        using var dialog = new EntityEditDialog(_config, row);
        if (dialog.ShowDialog(this) != DialogResult.OK)
            return;

        try
        {
            var (sql, parameters) = BuildUpdateSql(dialog.Values, dialog.PasswordWasEntered, row);
            Db.SetAppContext(CurrentUser.Login);
            Db.Execute(sql, parameters.ToArray());
            LoadData();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ErrorHelper.ToUserMessage(ex), "Изменение", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }
    }

    private void DeleteRecord()
    {
        var row = GetSelectedRow();
        if (row == null)
        {
            MessageBox.Show("Сначала выберите запись.", "Удаление", MessageBoxButtons.OK, MessageBoxIcon.Information);
            return;
        }

        if (MessageBox.Show("Удалить выбранную запись?", "Удаление", MessageBoxButtons.YesNo, MessageBoxIcon.Question) != DialogResult.Yes)
            return;

        try
        {
            var (sql, parameters) = BuildDeleteSql(row);
            Db.SetAppContext(CurrentUser.Login);
            Db.Execute(sql, parameters.ToArray());
            LoadData();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ErrorHelper.ToUserMessage(ex), "Удаление", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }
    }

    private (string Sql, List<NpgsqlParameter> Parameters) BuildInsertSql(Dictionary<string, object?> values, bool passwordWasEntered)
    {
        var insertFields = _config.Fields
            .Where(f => !(f.IsKey && f.AutoIdentity))
            .Where(f => f.Kind != FieldKind.Password || passwordWasEntered)
            .ToList();

        var columns = new List<string>();
        var valueSql = new List<string>();
        var parameters = new List<NpgsqlParameter>();

        foreach (var field in insertFields)
        {
            columns.Add(Q(field.Name));
            valueSql.Add(SqlValueExpression(field));
            parameters.Add(MakeParameter(field.Name, values.GetValueOrDefault(field.Name)));
        }

        var sql = $"INSERT INTO {Q(_config.TableName)} ({string.Join(", ", columns)}) VALUES ({string.Join(", ", valueSql)});";
        return (sql, parameters);
    }

    private (string Sql, List<NpgsqlParameter> Parameters) BuildUpdateSql(Dictionary<string, object?> values, bool passwordWasEntered, DataRow originalRow)
    {
        var updateFields = _config.Fields
            .Where(f => !f.IsKey)
            .Where(f => f.Kind != FieldKind.Password || passwordWasEntered)
            .ToList();

        var setParts = new List<string>();
        var parameters = new List<NpgsqlParameter>();

        foreach (var field in updateFields)
        {
            setParts.Add($"{Q(field.Name)} = {SqlValueExpression(field)}");
            parameters.Add(MakeParameter(field.Name, values.GetValueOrDefault(field.Name)));
        }

        var whereParts = new List<string>();
        foreach (var key in _config.KeyFields)
        {
            var paramName = "key_" + key.Name;
            whereParts.Add($"{Q(key.Name)} = @{paramName}");
            parameters.Add(MakeParameter(paramName, originalRow[key.Name]));
        }

        var sql = $"UPDATE {Q(_config.TableName)} SET {string.Join(", ", setParts)} WHERE {string.Join(" AND ", whereParts)};";
        return (sql, parameters);
    }

    private (string Sql, List<NpgsqlParameter> Parameters) BuildDeleteSql(DataRow row)
    {
        var whereParts = new List<string>();
        var parameters = new List<NpgsqlParameter>();

        foreach (var key in _config.KeyFields)
        {
            var paramName = "key_" + key.Name;
            whereParts.Add($"{Q(key.Name)} = @{paramName}");
            parameters.Add(MakeParameter(paramName, row[key.Name]));
        }

        var sql = $"DELETE FROM {Q(_config.TableName)} WHERE {string.Join(" AND ", whereParts)};";
        return (sql, parameters);
    }

    private string SqlValueExpression(FieldConfig field)
    {
        if (_config.TableName == "app_users" && field.Name == "password_hash")
            return "fn_hash_password(@password_hash)";

        if (_config.TableName == "app_users" && field.Name == "role")
            return "@role::app_role";

        return "@" + field.Name;
    }

    private static NpgsqlParameter MakeParameter(string name, object? value)
    {
        return new NpgsqlParameter(name, value ?? DBNull.Value);
    }

    private static string Q(string identifier)
    {
        return "\"" + identifier.Replace("\"", "\"\"") + "\"";
    }
}