using System;
using System.Globalization;
using System.IO;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;

namespace Электронная_Фармация.Classes
{
    /// <summary>
    /// Exports a local invoice (header + items) to a CSV file in the configured folder.
    /// </summary>
    public sealed class InvoiceExportService
    {
        public string ExportInvoice(string invoiceId, IWin32Window owner = null)
        {
            if (string.IsNullOrWhiteSpace(invoiceId))
            {
                throw new ArgumentException("Не указана накладная для экспорта.", nameof(invoiceId));
            }

            var folder = ResolveExportFolder(owner);
            if (string.IsNullOrWhiteSpace(folder))
            {
                throw new InvalidOperationException("Не указана папка для экспорта накладных.");
            }

            if (!Directory.Exists(folder))
            {
                Directory.CreateDirectory(folder);
            }

            InvoiceHeader header;
            using (var con = new SQLiteConnection(AppConfig.SqliteConnectionString))
            {
                con.Open();
                header = LoadHeader(con, invoiceId);
                var itemsSql = @"
select
    ifnull([GoodCode], '') as [GoodCode],
    ifnull([Good], '') as [Good],
    ifnull([ProducerName], '') as [ProducerName],
    ifnull([GoodsGount], 0) as [Qty],
    ifnull([PriceSupplierWithNDS], ifnull([PriceSupplierWithoutNDS], 0)) as [Price],
    ifnull([SumWithNDS], ifnull([SumWithoutNDS], 0)) as [Sum],
    ifnull([BestBefore], '') as [BestBefore],
    ifnull([Serial], '') as [Serial]
from [InvoiceItem]
where [idInvoice] = @invoiceId
order by [Good];";

                var fileName = BuildFileName(header);
                var filePath = Path.Combine(folder, fileName);

                var sb = new StringBuilder();
                sb.AppendLine("Номер накладной;Дата;Поставщик;Грузополучатель;Номер заказа;Сумма");
                sb.AppendLine(string.Join(";",
                    Csv(header.Number),
                    Csv(header.Date),
                    Csv(header.Supplier),
                    Csv(header.Consignee),
                    Csv(header.OrderNumber),
                    Csv(header.Sum)));
                sb.AppendLine();
                sb.AppendLine("Код товара;Товар;Производитель;Количество;Цена;Сумма;Срок годности;Серия");

                using (var cmd = new SQLiteCommand(itemsSql, con))
                {
                    cmd.Parameters.AddWithValue("@invoiceId", invoiceId);
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            sb.AppendLine(string.Join(";",
                                Csv(reader["GoodCode"]),
                                Csv(reader["Good"]),
                                Csv(reader["ProducerName"]),
                                Csv(reader["Qty"]),
                                Csv(reader["Price"]),
                                Csv(reader["Sum"]),
                                Csv(reader["BestBefore"]),
                                Csv(reader["Serial"])));
                        }
                    }
                }

                // UTF-8 BOM — Excel корректно открывает кириллицу.
                File.WriteAllText(filePath, sb.ToString(), new UTF8Encoding(encoderShouldEmitUTF8Identifier: true));
                AppDebugLog.Info("InvoiceExport", $"Накладная {header.Number} экспортирована: {filePath}");
                return filePath;
            }
        }

        public static string ResolveExportFolder(IWin32Window owner = null)
        {
            var folder = AppConfig.InvoiceExportPath;
            if (!string.IsNullOrWhiteSpace(folder) && Directory.Exists(folder))
            {
                return folder;
            }

            using (var dialog = new FolderBrowserDialog())
            {
                dialog.Description = "Выберите папку для экспорта накладных";
                dialog.ShowNewFolderButton = true;
                if (!string.IsNullOrWhiteSpace(folder) && Directory.Exists(folder))
                {
                    dialog.SelectedPath = folder;
                }

                if (dialog.ShowDialog(owner) != DialogResult.OK || string.IsNullOrWhiteSpace(dialog.SelectedPath))
                {
                    return string.Empty;
                }

                AppConfig.SetInvoiceExportPath(dialog.SelectedPath);
                return dialog.SelectedPath;
            }
        }

        private static InvoiceHeader LoadHeader(SQLiteConnection con, string invoiceId)
        {
            using (var cmd = new SQLiteCommand(@"
select
    ifnull([InvoiceNumber], '') as [Number],
    ifnull([InvoiceDate], '') as [Date],
    ifnull([SupplierName], '') as [Supplier],
    ifnull([ConsigneesName], '') as [Consignee],
    ifnull([SourceOrderNumber], '') as [OrderNumber],
    ifnull([InvoiceSum], ifnull([SumWithNDS], 0)) as [Sum]
from [Invoice]
where [idInvoice] = @invoiceId
limit 1;", con))
            {
                cmd.Parameters.AddWithValue("@invoiceId", invoiceId);
                using (var reader = cmd.ExecuteReader())
                {
                    if (!reader.Read())
                    {
                        throw new InvalidOperationException("Накладная не найдена.");
                    }

                    return new InvoiceHeader
                    {
                        Number = reader["Number"]?.ToString() ?? string.Empty,
                        Date = reader["Date"]?.ToString() ?? string.Empty,
                        Supplier = reader["Supplier"]?.ToString() ?? string.Empty,
                        Consignee = reader["Consignee"]?.ToString() ?? string.Empty,
                        OrderNumber = reader["OrderNumber"]?.ToString() ?? string.Empty,
                        Sum = reader["Sum"]?.ToString() ?? "0"
                    };
                }
            }
        }

        private static string BuildFileName(InvoiceHeader header)
        {
            var number = SanitizeFileName(string.IsNullOrWhiteSpace(header.Number) ? "invoice" : header.Number);
            var stamp = DateTime.Now.ToString("yyyyMMdd-HHmmss", CultureInfo.InvariantCulture);
            return $"Накладная_{number}_{stamp}.csv";
        }

        private static string SanitizeFileName(string value)
        {
            foreach (var c in Path.GetInvalidFileNameChars())
            {
                value = value.Replace(c, '_');
            }

            return value.Trim();
        }

        private static string Csv(object value)
        {
            var text = value?.ToString() ?? string.Empty;
            if (text.IndexOfAny(new[] { ';', '"', '\r', '\n' }) >= 0)
            {
                return "\"" + text.Replace("\"", "\"\"") + "\"";
            }

            return text;
        }

        private sealed class InvoiceHeader
        {
            public string Number { get; set; }
            public string Date { get; set; }
            public string Supplier { get; set; }
            public string Consignee { get; set; }
            public string OrderNumber { get; set; }
            public string Sum { get; set; }
        }
    }
}
