using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using Elfisa.UI.Theming;
using Электронная_Фармация.Classes;

namespace Электронная_Фармация.Forms
{
    public partial class FDebug : Form
    {
        private sealed class SqlTemplate
        {
            public string Title { get; set; }
            public string Sql { get; set; }
            public string[] Parameters { get; set; }
        }

        private readonly List<SqlTemplate> _templates = new List<SqlTemplate>();
        private readonly Dictionary<string, TextBox> _paramBoxes = new Dictionary<string, TextBox>(StringComparer.OrdinalIgnoreCase);
        private readonly Timer _highlightTimer = new Timer();
        private bool _highlighting;

        public FDebug()
        {
            InitializeComponent();
            _highlightTimer.Interval = 180;
            _highlightTimer.Tick += (_, __) =>
            {
                _highlightTimer.Stop();
                ApplySqlHighlight();
            };
        }

        private void FDebug_Load(object sender, EventArgs e)
        {
            UiThemeHelper.ApplyToControlTree(this);
            Text = "SQL-отладка";
            lblDbPath.Text = AppConfig.SqliteDbPath;
            RTXT_Sql.DetectUrls = false;
            RTXT_Sql.AcceptsTab = true;
            RTXT_Sql.TextChanged += RTXT_Sql_TextChanged;
            BuildTemplates();
            listTemplates.Items.Clear();
            foreach (var template in _templates)
            {
                listTemplates.Items.Add(template.Title);
            }

            SetStatus("Готово. Выберите шаблон или введите свой SQL.");
            ApplySqlHighlight();
            FormClosed += (_, __) =>
            {
                _highlightTimer.Stop();
                _highlightTimer.Dispose();
            };
        }

        private void RTXT_Sql_TextChanged(object sender, EventArgs e)
        {
            if (_highlighting)
            {
                return;
            }

            _highlightTimer.Stop();
            _highlightTimer.Start();
        }

        private void ApplySqlHighlight()
        {
            if (_highlighting || RTXT_Sql == null || RTXT_Sql.IsDisposed)
            {
                return;
            }

            _highlighting = true;
            try
            {
                SqlSyntaxHighlighter.Apply(RTXT_Sql, ThemeManager.IsDarkProductivityActive);
            }
            finally
            {
                _highlighting = false;
            }
        }

        private void BuildTemplates()
        {
            _templates.Clear();
            _templates.Add(new SqlTemplate
            {
                Title = "Счётчик прайса",
                Sql = "SELECT COUNT(*) AS [Позиций] FROM PriceList;"
            });
            _templates.Add(new SqlTemplate
            {
                Title = "Прайс по поставщикам",
                Sql = @"SELECT SupplierName AS [Поставщик],
       COUNT(*) AS [Позиций],
       MIN(Price) AS [МинЦена],
       MAX(Price) AS [МаксЦена]
FROM PriceList
GROUP BY SupplierName
ORDER BY COUNT(*) DESC;"
            });
            _templates.Add(new SqlTemplate
            {
                Title = "Заказы по статусам",
                Sql = @"SELECT OrderState AS [Статус], COUNT(*) AS [Кол-во]
FROM Orders
GROUP BY OrderState
ORDER BY COUNT(*) DESC;"
            });
            _templates.Add(new SqlTemplate
            {
                Title = "Грузополучатели + LocationId",
                Sql = @"SELECT codeConsignees AS [Код],
       ConsigneesName AS [Аптека],
       ConsigneesAddress AS [Адрес],
       ifnull(LocationId, '') AS [LocationId],
       ConsigneesUseAsDefault AS [ПоУмолчанию]
FROM Consignees
ORDER BY ConsigneesUseAsDefault DESC, ConsigneesName;"
            });
            _templates.Add(new SqlTemplate
            {
                Title = "Корзина TempOrderItems",
                Sql = @"SELECT COUNT(*) AS [Строк],
       ifnull(SUM(CAST(SummaZakaza AS REAL)), 0) AS [Сумма]
FROM TempOrderItems;"
            });
            _templates.Add(new SqlTemplate
            {
                Title = "Поиск по наименованию",
                Sql = @"SELECT DrugName, SupplierName, Price, Quantity, supplier_price_id
FROM PriceList
WHERE DrugName LIKE '%' || @q || '%'
ORDER BY DrugName
LIMIT 200;",
                Parameters = new[] { "q" }
            });
            _templates.Add(new SqlTemplate
            {
                Title = "Последние заказы",
                Sql = @"SELECT Id_Order, OrderNumber, OrderDate, SupplierName, ConsigneeName, OrderState, SummaZakaza
FROM Orders
ORDER BY Id_Order DESC
LIMIT 50;"
            });
            _templates.Add(new SqlTemplate
            {
                Title = "Список таблиц",
                Sql = @"SELECT name AS [Таблица], type AS [Тип]
FROM sqlite_master
WHERE type IN ('table', 'view')
ORDER BY name;"
            });
            _templates.Add(new SqlTemplate
            {
                Title = "Позиции заказа",
                Sql = @"SELECT oi.Id_Order, oi.DrugName, oi.Zakaz, oi.Price, oi.supplier_price_id, o.ConsigneeName, o.OrderState
FROM OrderItems oi
INNER JOIN Orders o ON o.Id_Order = oi.Id_Order
WHERE oi.Id_Order = @orderId
ORDER BY oi.DrugName;",
                Parameters = new[] { "orderId" }
            });
            _templates.Add(new SqlTemplate
            {
                Title = "Очистить корзину (DML)",
                Sql = "DELETE FROM TempOrderItems;"
            });
        }

        private void listTemplates_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (listTemplates.SelectedIndex < 0 || listTemplates.SelectedIndex >= _templates.Count)
            {
                return;
            }

            var template = _templates[listTemplates.SelectedIndex];
            RTXT_Sql.Text = template.Sql;
            RebuildParamEditors(template.Parameters);
            SetStatus($"Шаблон: {template.Title}");
        }

        private void RebuildParamEditors(string[] parameters)
        {
            panelParams.Controls.Clear();
            _paramBoxes.Clear();

            if (parameters == null || parameters.Length == 0)
            {
                var empty = new Label
                {
                    Text = "Параметры не требуются. Можно писать {{name}} или @name в SQL.",
                    AutoSize = true,
                    MaximumSize = new Size(panelParams.Width - 20, 0),
                    Location = new Point(8, 8)
                };
                panelParams.Controls.Add(empty);
                return;
            }

            int y = 8;
            foreach (var name in parameters)
            {
                var lbl = new Label
                {
                    Text = "@" + name,
                    AutoSize = true,
                    Location = new Point(8, y + 4)
                };
                var box = new TextBox
                {
                    Name = "param_" + name,
                    Width = Math.Max(180, panelParams.Width - 140),
                    Location = new Point(120, y)
                };
                panelParams.Controls.Add(lbl);
                panelParams.Controls.Add(box);
                _paramBoxes[name] = box;
                y += 34;
            }
        }

        private void btnCleanSqlScript_Click(object sender, EventArgs e)
        {
            RTXT_Sql.Text = string.Empty;
            dgvSelectCommand.DataSource = null;
            RebuildParamEditors(null);
            SetStatus("Очищено.");
        }

        private void btnExecuteSqlScript_Click(object sender, EventArgs e)
        {
            ExecuteSql(forceSelect: false);
        }

        private void btnExecuteSelectSqlScript_Click(object sender, EventArgs e)
        {
            ExecuteSql(forceSelect: true);
        }

        private void ExecuteSql(bool forceSelect)
        {
            var sql = (RTXT_Sql.Text ?? string.Empty).Trim();
            if (string.IsNullOrWhiteSpace(sql))
            {
                SetStatus("Пустой SQL.");
                return;
            }

            sql = ApplyMustachePlaceholders(sql);
            bool isSelect = forceSelect || LooksLikeSelect(sql);
            var sw = Stopwatch.StartNew();

            using (var connection = new SQLiteConnection(AppConfig.SqliteConnectionString))
            {
                try
                {
                    connection.Open();
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        BindParameters(command, sql);

                        if (isSelect)
                        {
                            using (var adapter = new SQLiteDataAdapter(command))
                            {
                                var table = new DataTable();
                                adapter.Fill(table);
                                dgvSelectCommand.DataSource = table;
                                sw.Stop();
                                SetStatus($"SELECT: {table.Rows.Count} строк за {sw.ElapsedMilliseconds} мс");
                            }
                        }
                        else
                        {
                            int affected = command.ExecuteNonQuery();
                            dgvSelectCommand.DataSource = null;
                            sw.Stop();
                            SetStatus($"Выполнено. Затронуто строк: {affected}. {sw.ElapsedMilliseconds} мс");
                        }
                    }
                }
                catch (Exception ex)
                {
                    sw.Stop();
                    SetStatus("Ошибка: " + ex.Message);
                    MessageBox.Show($"При выполнении запроса возникла ошибка.\n{ex}", "SQL-отладка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        private static bool LooksLikeSelect(string sql)
        {
            var trimmed = sql.TrimStart();
            return trimmed.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase)
                   || trimmed.StartsWith("PRAGMA", StringComparison.OrdinalIgnoreCase)
                   || trimmed.StartsWith("WITH", StringComparison.OrdinalIgnoreCase)
                   || trimmed.StartsWith("EXPLAIN", StringComparison.OrdinalIgnoreCase);
        }

        private string ApplyMustachePlaceholders(string sql)
        {
            return Regex.Replace(sql, @"\{\{\s*([A-Za-z_][A-Za-z0-9_]*)\s*\}\}", match =>
            {
                var name = match.Groups[1].Value;
                if (_paramBoxes.TryGetValue(name, out var box))
                {
                    return (box.Text ?? string.Empty).Replace("'", "''");
                }

                return match.Value;
            });
        }

        private void BindParameters(SQLiteCommand command, string sql)
        {
            var names = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
            foreach (Match match in Regex.Matches(sql, @"@([A-Za-z_][A-Za-z0-9_]*)"))
            {
                names.Add(match.Groups[1].Value);
            }

            foreach (var name in names)
            {
                object value = string.Empty;
                if (_paramBoxes.TryGetValue(name, out var box))
                {
                    value = box.Text ?? string.Empty;
                }

                command.Parameters.AddWithValue("@" + name, value);
            }

            // Параметры из панели, даже если в SQL их ещё нет (удобно для шаблонов).
            foreach (var pair in _paramBoxes)
            {
                if (!names.Contains(pair.Key))
                {
                    command.Parameters.AddWithValue("@" + pair.Key, pair.Value.Text ?? string.Empty);
                }
            }
        }

        private void SetStatus(string text)
        {
            lblStatus.Text = text;
        }

        private void btnScanParams_Click(object sender, EventArgs e)
        {
            var sql = RTXT_Sql.Text ?? string.Empty;
            var names = Regex.Matches(sql, @"@([A-Za-z_][A-Za-z0-9_]*)")
                .Cast<Match>()
                .Select(m => m.Groups[1].Value)
                .Concat(Regex.Matches(sql, @"\{\{\s*([A-Za-z_][A-Za-z0-9_]*)\s*\}\}")
                    .Cast<Match>()
                    .Select(m => m.Groups[1].Value))
                .Distinct(StringComparer.OrdinalIgnoreCase)
                .ToArray();

            var existing = _paramBoxes.ToDictionary(p => p.Key, p => p.Value.Text, StringComparer.OrdinalIgnoreCase);
            RebuildParamEditors(names);
            foreach (var pair in existing)
            {
                if (_paramBoxes.TryGetValue(pair.Key, out var box))
                {
                    box.Text = pair.Value;
                }
            }

            SetStatus(names.Length == 0 ? "Параметры в SQL не найдены." : $"Найдено параметров: {names.Length}");
        }
    }
}
