using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Globalization;
using System.Linq;
using System.Threading.Tasks;
using Электронная_Фармация.Properties;

namespace Электронная_Фармация.Classes
{
    /// <summary>
    /// Synchronizes electronic invoices and refusals from API into local SQLite.
    /// </summary>
    public sealed class InvoiceSyncService
    {
        public async Task<int> SyncFromServerAsync()
        {
            var token = Settings.Default.stringToken;
            if (string.IsNullOrWhiteSpace(token))
            {
                var ex = new InvalidOperationException("Необходима авторизация перед загрузкой накладных.");
                AppDebugLog.Error("InvoiceSync", "Синхронизация накладных без токена", ex);
                throw ex;
            }

            try
            {
                var client = new ApiClient();
                client.SetToken(token);
                AppDebugLog.Info("InvoiceSync", $"Старт синхронизации накладных. Сервер={client.BaseUrl}");
                var orders = await client.GetBuyerOrdersAsync(limit: 200);
                var items = new List<InvoiceApiItem>();
                using (var con = new SQLiteConnection(AppConfig.SqliteConnectionString))
                {
                    con.Open();
                    foreach (var order in orders)
                    {
                        if (string.Equals(order.Status, "Cancelled", StringComparison.OrdinalIgnoreCase))
                        {
                            continue;
                        }

                        var detail = await client.GetBuyerOrderByIdAsync(order.OrderID);
                        if (detail == null)
                        {
                            continue;
                        }

                        detail.LocalOrderNumber = TryGetLocalOrderNumber(con, detail.OrderID);
                        items.Add(InvoiceApiItem.FromBuyerOrder(detail));
                    }
                }

                var saved = SaveInvoices(items);
                AppDebugLog.Info("InvoiceSync", $"Накладные сохранены локально: {saved} шт.");
                return saved;
            }
            catch (Exception ex)
            {
                AppDebugLog.Error("InvoiceSync", "Ошибка синхронизации накладных с API", ex);
                throw;
            }
        }

        private static int SaveInvoices(IReadOnlyList<InvoiceApiItem> items)
        {
            using (var con = new SQLiteConnection(AppConfig.SqliteConnectionString))
            {
                con.Open();
                using (var tx = con.BeginTransaction())
                {
                    using (var cleanupItems = new SQLiteCommand(@"
DELETE FROM InvoiceItem
WHERE idInvoice IN (
    SELECT idInvoice FROM Invoice WHERE ifnull(IsRequestedInvoice, 0) = 0
);", con, tx))
                    {
                        cleanupItems.ExecuteNonQuery();
                    }

                    using (var cleanupInvoices = new SQLiteCommand(
                        "DELETE FROM Invoice WHERE ifnull(IsRequestedInvoice, 0) = 0;",
                        con,
                        tx))
                    {
                        cleanupInvoices.ExecuteNonQuery();
                    }

                    foreach (var item in items)
                    {
                        var invoiceId = UpsertInvoice(con, tx, item);
                        SaveInvoiceItems(con, tx, invoiceId, item);
                    }

                    tx.Commit();
                }
            }

            return items.Count;
        }

        private static int UpsertInvoice(SQLiteConnection con, SQLiteTransaction tx, InvoiceApiItem item)
        {
            using (var findCmd = new SQLiteCommand(@"
SELECT [idInvoice]
FROM [Invoice]
WHERE cast([SourceOrderId] as text) = @sourceOrderId
LIMIT 1;", con, tx))
            {
                findCmd.Parameters.AddWithValue("@sourceOrderId", item.SourceOrderId ?? string.Empty);
                var existing = findCmd.ExecuteScalar();
                if (existing != null && existing != DBNull.Value)
                {
                    var invoiceId = Convert.ToInt32(existing, CultureInfo.InvariantCulture);
                    using (var updateCmd = new SQLiteCommand(@"
UPDATE [Invoice]
SET [InvoiceNumber] = @number,
    [InvoiceDate] = @date,
    [SupplierName] = @supplier,
    [ConsigneesName] = @consignee,
    [InvoiceSum] = @sum,
    [RefuseSum] = @refuseSum,
    [SourceOrderNumber] = @sourceOrderNumber,
    [IsRequestedInvoice] = 0
WHERE [idInvoice] = @invoiceId;", con, tx))
                    {
                        updateCmd.Parameters.AddWithValue("@number", item.InvoiceNumber ?? string.Empty);
                        updateCmd.Parameters.AddWithValue("@date", item.InvoiceDate ?? string.Empty);
                        updateCmd.Parameters.AddWithValue("@supplier", item.SupplierName ?? string.Empty);
                        updateCmd.Parameters.AddWithValue("@consignee", item.ConsigneeName ?? string.Empty);
                        updateCmd.Parameters.AddWithValue("@sum", item.InvoiceSum ?? "0");
                        updateCmd.Parameters.AddWithValue("@refuseSum", item.RefuseSum ?? "0");
                        updateCmd.Parameters.AddWithValue("@sourceOrderNumber", item.InvoiceNumber ?? string.Empty);
                        updateCmd.Parameters.AddWithValue("@invoiceId", invoiceId);
                        updateCmd.ExecuteNonQuery();
                    }

                    using (var deleteItems = new SQLiteCommand("DELETE FROM [InvoiceItem] WHERE [idInvoice] = @invoiceId;", con, tx))
                    {
                        deleteItems.Parameters.AddWithValue("@invoiceId", invoiceId);
                        deleteItems.ExecuteNonQuery();
                    }

                    return invoiceId;
                }
            }

            using (var insertCmd = new SQLiteCommand(@"
INSERT INTO [Invoice]
(
    [InvoiceNumber],
    [InvoiceDate],
    [CodeSupplier],
    [CodeConsignees],
    [SumWithoutNDS],
    [SumWithNDS],
    [SumNDS],
    [SupplierName],
    [ConsigneesName],
    [InvoiceSum],
    [RefuseSum],
    [SourceOrderId],
    [SourceOrderNumber],
    [IsRequestedInvoice]
)
VALUES
(
    @number,
    @date,
    0,
    0,
    @sum,
    @sum,
    0,
    @supplier,
    @consignee,
    @sum,
    @refuseSum,
    @sourceOrderId,
    @sourceOrderNumber,
    0
);
SELECT last_insert_rowid();", con, tx))
            {
                insertCmd.Parameters.AddWithValue("@number", item.InvoiceNumber ?? string.Empty);
                insertCmd.Parameters.AddWithValue("@date", item.InvoiceDate ?? string.Empty);
                insertCmd.Parameters.AddWithValue("@sum", item.InvoiceSum ?? "0");
                insertCmd.Parameters.AddWithValue("@supplier", item.SupplierName ?? string.Empty);
                insertCmd.Parameters.AddWithValue("@consignee", item.ConsigneeName ?? string.Empty);
                insertCmd.Parameters.AddWithValue("@refuseSum", item.RefuseSum ?? "0");
                insertCmd.Parameters.AddWithValue("@sourceOrderId", item.SourceOrderId ?? string.Empty);
                insertCmd.Parameters.AddWithValue("@sourceOrderNumber", item.InvoiceNumber ?? string.Empty);
                return Convert.ToInt32(insertCmd.ExecuteScalar(), CultureInfo.InvariantCulture);
            }
        }

        private static void SaveInvoiceItems(SQLiteConnection con, SQLiteTransaction tx, int invoiceId, InvoiceApiItem item)
        {
            foreach (var invoiceItem in item.Items ?? Enumerable.Empty<InvoiceApiOrderItem>())
            {
                using (var cmd = new SQLiteCommand(@"
INSERT INTO [InvoiceItem]
(
    [idGood],
    [GoodCode],
    [Good],
    [ProducerName],
    [CountryName],
    [idInvoice],
    [GoodsGount],
    [PriceSupplierWithoutNDS],
    [PriceSupplierWithNDS],
    [PriceProducerWithoutNDS],
    [PriceProducerWithNDS],
    [NDS],
    [JNVLS],
    [PriceReestr],
    [SumWithoutNDS],
    [SumWithNDS],
    [SumNDS],
    [Serial],
    [BestBefore],
    [Sertificate],
    [Marked],
    [GTIN]
)
VALUES
(
    null,
    @goodCode,
    @good,
    '',
    '',
    @invoiceId,
    @qty,
    @price,
    @price,
    null,
    null,
    0,
    0,
    null,
    @sum,
    @sum,
    0,
    '',
    '',
    '',
    0,
    ''
)", con, tx))
                {
                    cmd.Parameters.AddWithValue("@goodCode", ParseGoodCode(invoiceItem.GoodCode));
                    cmd.Parameters.AddWithValue("@good", invoiceItem.GoodName ?? string.Empty);
                    cmd.Parameters.AddWithValue("@invoiceId", invoiceId);
                    cmd.Parameters.AddWithValue("@qty", invoiceItem.Qty);
                    cmd.Parameters.AddWithValue("@price", invoiceItem.Price);
                    cmd.Parameters.AddWithValue("@sum", invoiceItem.Total);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        private static int ParseGoodCode(string value)
        {
            int parsed;
            return int.TryParse(value, NumberStyles.Integer, CultureInfo.InvariantCulture, out parsed) ? parsed : 0;
        }

        private static string TryGetLocalOrderNumber(SQLiteConnection con, string remoteOrderId)
        {
            if (string.IsNullOrWhiteSpace(remoteOrderId))
            {
                return null;
            }

            using (var cmd = new SQLiteCommand(@"
SELECT [OrderNumber]
FROM [Orders]
WHERE [RemoteOrderId] = @remoteOrderId
LIMIT 1;", con))
            {
                cmd.Parameters.AddWithValue("@remoteOrderId", remoteOrderId);
                var value = cmd.ExecuteScalar();
                return value == null || value == DBNull.Value ? null : value.ToString();
            }
        }
    }
}
