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

namespace Электронная_Фармация.UserControls
{
    public partial class UCOrderItems : UserControl
    {
        public UCPriceList ParentForm { get; set; }
        string _supplierName;
        string _consigneeName;
        string sumOfOrder;
        string minSumOrder;
        BindingSource bsOrderItem = new BindingSource();
        DataAdapter daOrderItem;
        DataTable tableOrderItem;

        string connectionStringToLocalDB = AppConfig.SqliteConnectionString;


        public UCOrderItems(string SupplierName)
        {
            InitializeComponent();
            _supplierName = SupplierName;
            //_consigneeName = ConsigneeName;
        }


        public void loadDataAboutOrderV2(string Supplier)
        {
            var consigneeSafe = (_consigneeName ?? string.Empty).Replace("'", "''");
            string qShowMeOrderItems = $@"
SELECT
    [guid_es],
    [es_code],
    [supplier_id],
    [DrugName],
    [SupplierName],
    [Price],
    [Quantity],
    [ExpiryPeriod],
    [Description],
    [Zakaz],
    [SummaZakaza],
    [id_PriceList_Item],
    [supplier_price_id]
FROM [TempOrderItems]
WHERE [ConsigneeName] = '{consigneeSafe}'";

            //string qShowMeSumOfOrder = "select cast(sum(Price * Zakaz) as real) from TempOrderItems";

            DataTable TableTempOrderItem = new DataTable();

            using(SQLiteConnection conTempOrder = new SQLiteConnection(connectionStringToLocalDB))
            {
                try
                {
                    conTempOrder.Open();

                    if (Supplier != string.Empty && Supplier != "Корзина")
                    {
                        var supplierSafe = Supplier.Replace("'", "''");
                        qShowMeOrderItems += $" and [SupplierName] = '{supplierSafe}'";
                    }

                    SQLiteCommand cmdShowMeTempOrderItems = new SQLiteCommand(qShowMeOrderItems, conTempOrder);
                    SQLiteDataAdapter daTempOrderItems = new SQLiteDataAdapter(cmdShowMeTempOrderItems);
                    daTempOrderItems.Fill(TableTempOrderItem);


                    TableTempOrderItem.Columns[0].ColumnName = "ИД товара";
                    TableTempOrderItem.Columns[1].ColumnName = "Код товара";
                    TableTempOrderItem.Columns[2].ColumnName = "ИД поставщика";
                    TableTempOrderItem.Columns[3].ColumnName = "Наименование";
                    TableTempOrderItem.Columns[4].ColumnName = "Поставщик";
                    TableTempOrderItem.Columns[5].ColumnName = "Цена";
                    TableTempOrderItem.Columns[6].ColumnName = "Остаток";
                    TableTempOrderItem.Columns[7].ColumnName = "Срок годности";
                    TableTempOrderItem.Columns[8].ColumnName = "Описание";
                    TableTempOrderItem.Columns[9].ColumnName = "Заказ";
                    TableTempOrderItem.Columns[10].ColumnName = "Сумма";
                    

                    dgvOrder.DataSource = TableTempOrderItem;

                    dgvOrder.Columns[0].Visible = false;
                    dgvOrder.Columns[1].Visible = false;
                    dgvOrder.Columns[2].Visible = false;
                    dgvOrder.Columns[6].Visible = false;
                    dgvOrder.Columns[7].Visible = false;
                    dgvOrder.Columns[8].Visible = false;
                    dgvOrder.Columns[11].Visible = false;
                    dgvOrder.Columns[12].Visible = false;

                    Font customFont = new Font("Segoe UI", 13, FontStyle.Regular);

                    dgvOrder.Font = customFont;


                    dgvOrder.Columns[3].Width = 950;
                    dgvOrder.Columns[4].Width = 150;
                    dgvOrder.Columns[5].Width = 120;
                    dgvOrder.Columns[9].Width = 150;
                    dgvOrder.Columns[10].Width = 150;


                    double sumOrder = 0;
                    if (dgvOrder.Rows.Count > 0)
                    {
                        for (int i = 0; i < dgvOrder.Rows.Count; i++)
                        {
                            sumOrder += Convert.ToDouble(dgvOrder.Rows[i].Cells[10].Value.ToString());
                        }
                    }

                    //SQLiteCommand cmdShowMeSumOfOrder = new SQLiteCommand(qShowMeSumOfOrder, conTempOrder);
                    //string sumOrder = cmdShowMeSumOfOrder.ExecuteScalar().ToString();
                    lblSumOfOrder.Text = $"Сумма заказа: {sumOrder} Руб";
                }
                catch (Exception ex) { MessageBox.Show(ex.ToString()); }
                finally
                {
                    conTempOrder.Close();
                }
            }
        }

        #region староеОтображениеЗаказа
        /*
        void loadDataAboutOrderV1(string supplierName, string consigneeName)
        {
            string qShowMeTempOrderItems = $@"
select
[GoodCode] as [Код товара],
[GoodName] as [Наименование],
[SupplierName] as [Поставщик],
[ConsigneesName] as [Грузополучатель],
[SupplierPrice] as [Цена поставщика],
[OrderedCountItems] as [К-во заказ.],
[SumOrderedItems] as [Сумма заказа],
[BestBefore] as [Срок годности]
from [TempOrderItems]
where OrderedCountItems > 0
and ConsigneesName = '{consigneeName}'
";

            string qShowMeSumOfOrder = $@"
select sum(SumOrderedItems) 
from TempOrderItems
where OrderedCountItems > 0
and ConsigneesName = '{consigneeName}'
";

            string qShowMeMinimalSumOfOrder;

            

            

            string connectionStringToLocalDB = AppConfig.SqliteConnectionString;
            using (SQLiteConnection conTempOrderItems = new SQLiteConnection(connectionStringToLocalDB))
            {
                try
                {
                    conTempOrderItems.Open();

                    if (supplierName != string.Empty)
                    {
                        qShowMeTempOrderItems += $" and SupplierName = '{supplierName}'";
                        qShowMeSumOfOrder += $" and SupplierName = '{supplierName}'";
                        qShowMeMinimalSumOfOrder = $" select MinimalSumOfOrder from Suppliers where SuppliersName = '{supplierName}'";


                        SQLiteCommand cmdShowMeMinimalOrderSum = new SQLiteCommand(qShowMeMinimalSumOfOrder, conTempOrderItems);
                        minSumOrder = cmdShowMeMinimalOrderSum.ExecuteScalar().ToString();

                        if (minSumOrder != "")
                        {
                            lblMinimalSumOfOrder.Text = $"Минимальная сумма заказа: {minSumOrder} Руб";
                        }
                    }
                    else
                    {
                        lblMinimalSumOfOrder.Text = "Минимальная сумма заказа не указана";
                    }


                    SQLiteCommand cmdShowMeTempOrderItems = new SQLiteCommand(qShowMeTempOrderItems, conTempOrderItems);
                    SQLiteDataAdapter daTempOrderItems = new SQLiteDataAdapter(cmdShowMeTempOrderItems);
                    DataTable tableTempOrderItems = new DataTable();
                    daTempOrderItems.Fill(tableTempOrderItems);
                    dgvOrder.DataSource = tableTempOrderItems;

                    if(dgvOrder.RowCount > 0)
                    {
                        dgvOrder.DefaultCellStyle.Font = new Font("Segoe UI", 10, FontStyle.Regular);
                        dgvOrder.Columns["Наименование"].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                        dgvOrder.Columns["Грузополучатель"].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                        dgvOrder.Columns["Поставщик"].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                        dgvOrder.Columns["Срок годности"].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                    }

                  

                    SQLiteCommand cmdShowMeSumOfOrder = new SQLiteCommand(qShowMeSumOfOrder, conTempOrderItems);
                    sumOfOrder = cmdShowMeSumOfOrder.ExecuteScalar().ToString();

                    if (sumOfOrder == string.Empty)
                    {
                        sumOfOrder = "0,00";
                    }

                    lblSumOfOrder.Text = $"Сумма корзины: {sumOfOrder} Руб";
                }
                catch (Exception ex)
                {
                    MessageBox.Show($"Возникла ошибка при загрузке корзины. Пожалуйста, обратитесь в службу технической поддержки.\nТекст ошибки:\n{ex.ToString()}", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally
                {
                    conTempOrderItems.Close();
                }
            }


        }
        */
        #endregion
        private void UCOrderItems_Load(object sender, EventArgs e)
        {
            UiThemeHelper.ApplyToControlTree(this);
            _consigneeName = ParentForm?.ConsigneeName ?? string.Empty;
            loadDataAboutOrderV2(_supplierName);
            dgvOrder.ApplyTheme(ThemeManager.Colors);
        }

        private void btnEraseOrder_Click(object sender, EventArgs e)
        {
            string messageAboutEraseOrder;

            if (_supplierName == string.Empty)
            {
                messageAboutEraseOrder = "Вы уверены, что хотите очистить ВСЮ корзину?";
            }
            else
            {
                messageAboutEraseOrder = $"Вы уверены, что хотите очистить корзину для поставщика {_supplierName}?";
            }

            DialogResult drAreYouSeriesAboutEraseOrder = UiDialogs.ConfirmYesNo(messageAboutEraseOrder, "Сообщение", FindForm());

            if (drAreYouSeriesAboutEraseOrder == DialogResult.Yes)
            {
                EraseOrder(_supplierName);
            }
        }

        private void btnDeleteGood_Click(object sender, EventArgs e)
        {
            if (dgvOrder.SelectedRows.Count == 0 || dgvOrder.CurrentRow == null)
            {
                ToastNotification.ShowCustom("Выберите наименование в корзине для удаления.", Color.DarkOrange, Color.White);
                return;
            }

            var row = dgvOrder.SelectedRows[0];
            string idPriceListItem = null;

            if (dgvOrder.Columns.Contains("id_PriceList_Item"))
            {
                idPriceListItem = row.Cells["id_PriceList_Item"]?.Value?.ToString();
            }
            else if (row.Cells.Count > 11)
            {
                idPriceListItem = row.Cells[11]?.Value?.ToString();
            }

            if (string.IsNullOrWhiteSpace(idPriceListItem))
            {
                ToastNotification.ShowCustom("Не удалось определить позицию для удаления.", Color.DarkOrange, Color.White);
                return;
            }

            var drugName = dgvOrder.Columns.Contains("Наименование")
                ? row.Cells["Наименование"]?.Value?.ToString()
                : row.Cells.Count > 3
                    ? row.Cells[3]?.Value?.ToString()
                    : null;

            DeleteCartItem(idPriceListItem, drugName);
        }

        private void DeleteCartItem(string idPriceListItem, string drugName)
        {
            var safeId = idPriceListItem.Replace("'", "''");
            var consigneeSafe = (_consigneeName ?? string.Empty).Replace("'", "''");
            string qClearPriceList = $"update [PriceList] set [Zakaz] = null, [SummaZakaza] = null where id_PriceList_Item = '{safeId}'";
            string qDeleteFromTempOrder = $"delete from [TempOrderItems] where [id_PriceList_Item] = '{safeId}' and [ConsigneeName] = '{consigneeSafe}'";

            using (var sqlCon = new SQLiteConnection(connectionStringToLocalDB))
            {
                try
                {
                    sqlCon.Open();
                    using (var cmdClear = new SQLiteCommand(qClearPriceList, sqlCon))
                    using (var cmdDelete = new SQLiteCommand(qDeleteFromTempOrder, sqlCon))
                    {
                        cmdClear.ExecuteNonQuery();
                        cmdDelete.ExecuteNonQuery();
                    }

                    if (!string.IsNullOrWhiteSpace(drugName))
                    {
                        ToastNotification.ShowSuccess($"Удалено: {drugName}");
                    }
                }
                catch (Exception ex)
                {
                    ToastNotification.ShowError($"Ошибка удаления товара: {ex.Message}");
                }
                finally
                {
                    sqlCon.Close();
                    loadDataAboutOrderV2(_supplierName);
                    ParentForm?.loadPriceListV2();
                    ParentForm?.loadTempOrderItems();
                }
            }
        }

        void EraseOrder(string Supplier) 
        { 
            using(SQLiteConnection sqlConForDeleteTempOrder = new SQLiteConnection(connectionStringToLocalDB))
            {
                var supplierSafe = (Supplier ?? string.Empty).Replace("'", "''");
                var consigneeSafe = (_consigneeName ?? string.Empty).Replace("'", "''");
                string qDeletePriceListItemForASupplier;
                string qDeleteTempOrderForASupplier;

                if (supplierSafe != string.Empty)
                {
                    qDeletePriceListItemForASupplier =
                        $"update [PriceList] set [Zakaz] = null, [SummaZakaza] = null where [id_PriceList_Item] in " +
                        $"(select [id_PriceList_Item] from [TempOrderItems] where [SupplierName] = '{supplierSafe}' and [ConsigneeName] = '{consigneeSafe}')";
                    qDeleteTempOrderForASupplier =
                        $"delete from [TempOrderItems] where [SupplierName] = '{supplierSafe}' and [ConsigneeName] = '{consigneeSafe}'";
                }
                else
                {
                    qDeletePriceListItemForASupplier =
                        $"update [PriceList] set [Zakaz] = null, [SummaZakaza] = null " +
                        $"where [id_PriceList_Item] in (select [id_PriceList_Item] from [TempOrderItems] where [ConsigneeName] = '{consigneeSafe}')";
                    qDeleteTempOrderForASupplier =
                        $"delete from [TempOrderItems] where [ConsigneeName] = '{consigneeSafe}'";
                }




                    try
                    {
                        sqlConForDeleteTempOrder.Open();

                        SQLiteCommand cmdDeletePriceListItem = new SQLiteCommand(qDeletePriceListItemForASupplier, sqlConForDeleteTempOrder);
                        SQLiteCommand cmdDeleteTempOrder = new SQLiteCommand(qDeleteTempOrderForASupplier, sqlConForDeleteTempOrder);

                        cmdDeletePriceListItem.ExecuteNonQuery();
                        cmdDeleteTempOrder.ExecuteNonQuery();

                    }
                    catch (Exception ex)
                    {
                        ToastNotification.ShowError($"Ошибка очистки корзины: {ex.Message}");
                    }
                    finally
                    {
                        sqlConForDeleteTempOrder.Close();
                        loadDataAboutOrderV2(_supplierName);
                        ParentForm.loadPriceListV2();
                        ParentForm.loadTempOrderItems();
                    }
            }
        }


        #region СтараяОчисткаЗаказа
        /*
        void EraseOrderV1(string supplierName, string consigneeName)
        {
            string connectionStringToLocalDB = AppConfig.SqliteConnectionString;

            string qUpdatePriceListBuyingItems = $@"
update PriceList set 
[CountIWantBuy] = null,
[SumIWantBuy] = null
from (select GoodCode, GoodName, SupplierName, SupplierPrice from TempOrderItems where ConsigneesName = '{consigneeName}') as tempOrder
where
tempOrder.GoodCode = PriceList.GoodCode
and tempOrder.GoodName = PriceList.GoodName
";
            string qEraseOrder = $@"
delete from TempOrderItems 
where 
ConsigneesName = '{consigneeName}'
";

            if (supplierName != string.Empty)
            {
                qUpdatePriceListBuyingItems += $" and PriceList.SupplierName = '{supplierName}'";
                qEraseOrder += $" and SupplierName = '{supplierName}'";
            }

            using (SQLiteConnection conEraseOrder = new SQLiteConnection(connectionStringToLocalDB))
            {
                try
                {
                    conEraseOrder.Open();
                    SQLiteCommand cmdUpdateBuyingItems = new SQLiteCommand(qUpdatePriceListBuyingItems, conEraseOrder);
                    SQLiteCommand cmdEraseOrder = new SQLiteCommand(qEraseOrder, conEraseOrder);
                    cmdUpdateBuyingItems.ExecuteNonQuery();
                    cmdEraseOrder.ExecuteNonQuery();
                    loadDataAboutOrder(_supplierName, _consigneeName);
                    this.ParentForm.loadPriceListV2();
                    //this.ParentForm.loadPriceList(string.Empty);
                    this.ParentForm.loadTempOrderItems(_consigneeName);
                }
                catch (Exception ex)
                {
                    MessageBox.Show($"Возникла ошибка при очищении корзины. Пожалуйста, обратитесь в службу технической поддержки.\nТекст ошибки:\n{ex}", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally
                {
                    conEraseOrder.Close();
                }
            }
        }
        */
        #endregion

        private void btnSendOrder_Click(object sender, EventArgs e)
        {
            var hasToken = !string.IsNullOrWhiteSpace(Settings.Default.stringToken);
            if (!hasToken)
            {
                UiDialogs.ShowInfo("Сначала выполните авторизацию, чтобы создать заказ.", "Авторизация", FindForm());
                return;
            }

            string connectionStringToLocalDB = AppConfig.SqliteConnectionString;

            string SupplierName = _supplierName;
            string consigneeName = _consigneeName;
            var consigneeSafe = (consigneeName ?? string.Empty).Replace("'", "''");


            


           List<String> SuppliersFromOrder = new List<String>();

            string filterAddon = string.Empty;

//            string qShowMeSuppliersFromOrder = $@"
//select 
//SupplierName
//from TempOrderItems
//where Quantity > 0
//and ConsigneesName = '{consigneeName}'
//{filterAddon}
//group by SupplierName
//";

            

            string messageForCreatingOrder;

            if (SupplierName != string.Empty)
            {
                filterAddon = $" and SupplierName = '{SupplierName}'";
                messageForCreatingOrder = $"Будет сформирован заказ по поставщику {SupplierName}.\nПродолжить?";
            }
            else
            {
                messageForCreatingOrder = $"Будет сформирован заказ по ВСЕМ поставщикам из корзины.\nПродолжить?";
            }

            string qShowMeSuppliersFromOrder = $@"
select 
SupplierName
from TempOrderItems
where zakaz > 0
and [ConsigneeName] = '{consigneeSafe}'
{filterAddon}
group by SupplierName
";

            DialogResult drCreateOrder = UiDialogs.ConfirmYesNo(messageForCreatingOrder, "Сообщение", FindForm());

            if (drCreateOrder == DialogResult.Yes)
            {
                using (SQLiteConnection conSuppliersTempOrderGroup = new SQLiteConnection(connectionStringToLocalDB))
                {
                    try
                    {
                        conSuppliersTempOrderGroup.Open();
                        SQLiteCommand cmdShowMeSuppliersFromOrder = new SQLiteCommand(qShowMeSuppliersFromOrder, conSuppliersTempOrderGroup);
                        SQLiteDataReader dr = cmdShowMeSuppliersFromOrder.ExecuteReader();

                        while (dr.Read())
                        {
                            SuppliersFromOrder.Add(dr.GetString(0));
                        }
                    }
                    catch (Exception ex)
                    {
                        ToastNotification.ShowError($"Ошибка при подготовке заказа: {ex.Message}");
                    }
                    finally
                    {
                        conSuppliersTempOrderGroup.Close();

                        string supplierNameForCreatingOrder;

                        for (int i = 0; i < SuppliersFromOrder.Count; i++)
                        {

                            supplierNameForCreatingOrder = SuppliersFromOrder[i].ToString();

                            createOrderForNextSuppliers(supplierNameForCreatingOrder);
                        }
                    }
                }
            }


            //if (Convert.ToDouble(sumOfOrder) < Convert.ToDouble(minSumOrder))
            //{
            //    MessageBox.Show($"Операция была отменена.\nМинимальная сумма заказа для поставщика {SupplierName} составляет {minSumOrder} Руб. На данный момент сумма заказа равна {sumOfOrder} Руб", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            //}
        }


        void createOrderForNextSuppliers(string SupName)
        {
            // Глобальный номер EX-0000001… (сервер / локальный fallback).
            string OrderNumber = OrderNumberHelper.AllocateForNewOrder();
            string OrderDate = DateTime.Now.ToString("yyyy-MM-dd");
            _consigneeName = ParentForm?.ConsigneeName ?? _consigneeName ?? string.Empty;
            var consigneeSafe = (_consigneeName ?? string.Empty).Replace("'", "''");
            var supNameSafe = (SupName ?? string.Empty).Replace("'", "''");

            if (HasStockIssues(SupName, out var stockIssue))
            {
                ToastNotification.ShowError(stockIssue);
                return;
            }

            string qShowMeCountOrderedItems = $@"
select 
count(*) 
from TempOrderItems
where Zakaz > 0
and SupplierName = '{supNameSafe}'
and [ConsigneeName] = '{consigneeSafe}'
";
            string qShowMeSumOrderedItems = $@"
select
ifnull(sum(cast(replace(SummaZakaza, ',', '.') as real)), 0)
from TempOrderItems 
where Zakaz > 0
and SupplierName = '{supNameSafe}'
and [ConsigneeName] = '{consigneeSafe}'
";
            string qShowMeMinimalSumOfOrder = $@"
select 
MinimalSumOfOrder
from Suppliers
where SuppliersName = '{SupName.Replace("'", "''")}'
";

            string qDisposeTempOrder = $@"
delete from TempOrderItems where SupplierName = '{supNameSafe}' and [ConsigneeName] = '{consigneeSafe}'
";

            string connectionStringToLocalDB = AppConfig.SqliteConnectionString;

            using (SQLiteConnection conCreateOrders = new SQLiteConnection(connectionStringToLocalDB))
            {
                try
                {
                    conCreateOrders.Open();


                    SQLiteCommand cmdShowMeCountOfOrderedItems = new SQLiteCommand(qShowMeCountOrderedItems, conCreateOrders);
                    SQLiteCommand cmdShowMeSumOfOrderedItems = new SQLiteCommand(qShowMeSumOrderedItems, conCreateOrders);
                    SQLiteCommand cmdShowMeMinSumOfOrderForSupplier = new SQLiteCommand(qShowMeMinimalSumOfOrder, conCreateOrders);

                    qShowMeCountOrderedItems = Convert.ToString(cmdShowMeCountOfOrderedItems.ExecuteScalar());
                    qShowMeSumOrderedItems = Convert.ToString(cmdShowMeSumOfOrderedItems.ExecuteScalar());
                    var minSumValue = cmdShowMeMinSumOfOrderForSupplier.ExecuteScalar();

                    double sumOrder = 0;
                    double.TryParse(
                        qShowMeSumOrderedItems?.Replace(',', '.'),
                        System.Globalization.NumberStyles.Any,
                        System.Globalization.CultureInfo.InvariantCulture,
                        out sumOrder);

                    if (minSumValue != null && minSumValue != DBNull.Value)
                    {
                        double minSum = Convert.ToDouble(minSumValue);
                        if (sumOrder < minSum)
                        {
                            ToastNotification.ShowError(
                                $"Минимальная сумма заказа для поставщика {SupName} — {minSum} руб. Сейчас {sumOrder} руб.");
                            return;
                        }
                    }

                    string qCreateOrder = $@"
insert into [Orders] (OrderNumber, OrderDate, Id_Supplier, SupplierName, OrderedCountItems, SummaZakaza, OrderState, ConsigneeName)
select
'{OrderNumber}',
'{OrderDate}',
supplier_id,
SupplierName,
(select count(*) from TempOrderItems where SupplierName = '{supNameSafe}' and [ConsigneeName] = '{consigneeSafe}'),
'{sumOrder.ToString(System.Globalization.CultureInfo.InvariantCulture)}',
'НОВЫЙ',
'{_consigneeName.Replace("'", "''")}'
from TempOrderItems
where SupplierName = '{supNameSafe}'
  and [ConsigneeName] = '{consigneeSafe}'
group by supplier_id, SupplierName
";

                        string qFillOrderItems = $@"
insert into [OrderItems] (Id_Order, guid_es, es_code, DrugName, Price, Zakaz, SummaZakaza, ExpiryPeriod, supplier_price_id)
select
(select Id_Order from Orders where OrderNumber = '{OrderNumber}'),
[guid_es],
[es_code],
[DrugName],
[Price],
[Zakaz],
[SummaZakaza],
[ExpiryPeriod],
[supplier_price_id]
from TempOrderItems
where SupplierName = '{supNameSafe}' and [ConsigneeName] = '{consigneeSafe}'
";

                        #region старыеЗапросыСозданияЗаказа
//                        string qCreateOrder = $@"
//    insert into [Orders] (OrderNumber, OrderDate, codeSuppliers, codeConsignees, OrderedItemsCount, OrderSum, OrderState)
//    values
//    (
//    '{OrderNumber}',
//    '{OrderDate}',
//    (select codeSuppliers from Suppliers where SuppliersName = '{SupName}'),
//    (select codeConsignees from Consignees where ConsigneesName = '{_consigneeName}'),
//    '{qShowMeCountOrderedItems}',
//    '{qShowMeSumOrderedItems}',
//    'SAVE'
//    )
//    ";
//                        string qFillOrderItems = $@"
//insert into [OrderItems] (idOrder, GoodCode, GoodName, OrderedCountItems, SupplierPrice, SumOrderedItems, BestBefore, ProducerName)
//select
//(select idOrder from Orders where OrderNumber = '{OrderNumber}'),
//tempOrder.GoodCode,
//tempOrder.GoodName,
//tempOrder.Zakaz,
//tempOrder.Price,
//tempOrder.SummaZakaza,
//tempOrder.ExpiryPeriod,
//from
//TempOrderItems tempOrder
//inner join PriceList pl on pl.GoodCode = tempOrder.GoodCode and pl.GoodName = tempOrder.GoodName
//where tempOrder.OrderedCountItems > 0
//and tempOrder.SupplierName = '{SupName}'
//and pl.Quantity > 0
//";
                        #endregion

                        SQLiteCommand cmdCreateOrder = new SQLiteCommand(qCreateOrder, conCreateOrders);
                        cmdCreateOrder.ExecuteNonQuery();




                        SQLiteCommand cmdCreateOrderItems = new SQLiteCommand(qFillOrderItems, conCreateOrders);
                        cmdCreateOrderItems.ExecuteNonQuery();

                        //SQLiteCommand cmdDisposeTempOrder = new SQLiteCommand(qDisposeTempOrder, conCreateOrders);
                        //cmdDisposeTempOrder.ExecuteNonQuery();

                        EraseOrder(SupName);

                        ToastNotification.ShowSuccess($"Заказ {OrderNumber} был успешно создан!");

                    //EraseOrder(SupName, _consigneeName);
                    //}
                }
                catch (Exception ex)
                {
                    ToastNotification.ShowError("Не удалось сохранить: " + ex.Message);
                    //MessageBox.Show($"Возникла ошибка при формировании заказа для поставщика {SupName}. Текст ошибки:\n{ex.ToString()}", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally
                {
                    conCreateOrders.Close();
                }
            }
        }

        private bool HasStockIssues(string supplierName, out string message)
        {
            message = string.Empty;
            string qCheckStock = $@"
select t.DrugName, t.Zakaz, p.Quantity
from TempOrderItems t
inner join PriceList p on t.id_PriceList_Item = p.id_PriceList_Item
where cast(t.Zakaz as integer) > 0
and t.SupplierName = '{supplierName.Replace("'", "''")}'
and cast(t.Zakaz as integer) > cast(p.Quantity as integer)
limit 1";

            using (var con = new SQLiteConnection(connectionStringToLocalDB))
            {
                con.Open();
                using (var cmd = new SQLiteCommand(qCheckStock, con))
                using (var reader = cmd.ExecuteReader())
                {
                    if (!reader.Read())
                    {
                        return false;
                    }

                    var drugName = reader["DrugName"]?.ToString() ?? "товар";
                    var ordered = reader["Zakaz"]?.ToString() ?? "0";
                    var available = reader["Quantity"]?.ToString() ?? "0";
                    message = $"Недостаточно остатка: {drugName}. Заказано {ordered}, доступно {available}.";
                    return true;
                }
            }
        }

    }
}
