---

using System.Data;
using Microsoft.Data.SqlClient;

namespace WinFormsApp1
{
    public partial class Form1 : Form
    {
        private string connStr = @"Server=USERS\SQLEXPRESS;Database=demo;Trusted_Connection=True;TrustServerCertificate=True;";
        private string imagesPath = Path.Combine(Application.StartupPath, "Images");

        public Form1()
        {
            InitializeComponent();
            try
            {
                LoadProducts();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Ошибка загрузки данных: " + ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void LoadProducts()
        {
            DateTime monthStart = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            DateTime prevMonthStart = monthStart.AddMonths(-1);

            string sql = @"
SELECT p.id,
       p.[Наименование товара] AS Name,
       p.Изображение AS Image,
       p.Состав AS Composition,
       p.Цена AS Price,
       c.Категория AS Category,
       m.Производство AS Manufacturer,
       ISNULL((SELECT SUM(s.[count]) FROM Stock_items s WHERE s.id_tovar = p.id), 0) AS TotalCount,
       (SELECT COUNT(*) FROM Order_stock os
        JOIN Stock_items s ON s.id = os.id_stock_item
        JOIN Orders_import o ON o.id = os.id_order
        WHERE s.id_tovar = p.id
          AND o.[Дата заказа] >= @from
          AND o.[Дата заказа] < @to) AS OrdersLastMonth
FROM Products p
JOIN Categories c ON c.id = p.id_category
JOIN Manufactures m ON m.id = p.id_manufactures";

            var table = new DataTable();
            using (var conn = new SqlConnection(connStr))
            {
                var adapter = new SqlDataAdapter(sql, conn);
                adapter.SelectCommand.Parameters.AddWithValue("@from", prevMonthStart);
                adapter.SelectCommand.Parameters.AddWithValue("@to", monthStart);
                adapter.Fill(table);
            }

            flpProducts.Controls.Clear();
            foreach (DataRow row in table.Rows)
            {
                flpProducts.Controls.Add(CreateCard(row));
            }
        }

        private decimal CalculatePrice(decimal price, int ordersLastMonth)
        {
            if (ordersLastMonth == 0)
            {
                return Math.Round(price * 0.75m, 2);
            }
            return price;
        }

        private Image LoadImage(string fileName)
        {
            string path = Path.Combine(imagesPath, fileName);
            if (string.IsNullOrWhiteSpace(fileName) || !File.Exists(path))
            {
                path = Path.Combine(imagesPath, "picture.png");
            }
            return Image.FromFile(path);
        }

        private ProductCard CreateCard(DataRow row)
        {
            int totalCount = Convert.ToInt32(row["TotalCount"]);
            int ordersLastMonth = Convert.ToInt32(row["OrdersLastMonth"]);
            decimal price = Convert.ToDecimal(row["Price"]);
            decimal finalPrice = CalculatePrice(price, ordersLastMonth);

            var card = new ProductCard();
            card.Width = flpProducts.ClientSize.Width - 30;
            if (totalCount <= 3)
            {
                card.BackColor = Color.FromArgb(255, 204, 204);
            }

            card.SetData(
                row["Manufacturer"] + " | " + row["Name"],
                row["Category"].ToString(),
                totalCount > 5 ? "много" : "мало",
                row["Composition"].ToString(),
                finalPrice.ToString("0.00") + " ₽",
                LoadImage(row["Image"].ToString().Trim()));

            return card;
        }
    }
}