---

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SQLite;

namespace ExpirationControl
{
    public partial class Form1 : Form
    {
        string connectionString =
            "Data Source=products.db;Version=3;";

        public Form1()
        {
            InitializeComponent();

            CreateTable();
        }

        // СОЗДАНИЕ ТАБЛИЦЫ
        private void CreateTable()
        {
            using (SQLiteConnection connection =
                new SQLiteConnection(connectionString))
            {
                connection.Open();

                string sql = @"
                CREATE TABLE IF NOT EXISTS Products(
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    ProductName TEXT,
                    Quantity INTEGER,
                    ExpirationDate TEXT
                )";

                SQLiteCommand command =
                    new SQLiteCommand(sql, connection);

                command.ExecuteNonQuery();
            }
        }

        // ДОБАВЛЕНИЕ ТОВАРА
        private void btnAdd_Click(
            object sender,
            EventArgs e)
        {
            using (SQLiteConnection connection =
                new SQLiteConnection(connectionString))
            {
                connection.Open();

                string sql = @"
                INSERT INTO Products
                (ProductName, Quantity, ExpirationDate)
                VALUES
                (@name, @quantity, @date)";

                SQLiteCommand command =
                    new SQLiteCommand(sql, connection);

                command.Parameters.AddWithValue(
                    "@name",
                    txtName.Text);

                command.Parameters.AddWithValue(
                    "@quantity",
                    txtQuantity.Text);

                command.Parameters.AddWithValue(
                    "@date",
                    dtExpiration.Value.ToString(
                        "yyyy-MM-dd"));

                command.ExecuteNonQuery();

                MessageBox.Show(
                    "Товар успешно добавлен");

                LoadProducts();
            }
        }

        // ЗАГРУЗКА ТОВАРОВ
        private void LoadProducts()
        {
            using (SQLiteConnection connection =
                new SQLiteConnection(connectionString))
            {
                connection.Open();

                string sql =
                    "SELECT * FROM Products";

                SQLiteDataAdapter adapter =
                    new SQLiteDataAdapter(
                        sql,
                        connection);

                DataTable table =
                    new DataTable();

                adapter.Fill(table);

                dgvProducts.DataSource =
                    table;
            }
        }

        // КНОПКА ЗАГРУЗКИ
        private void btnLoad_Click(
            object sender,
            EventArgs e)
        {
            LoadProducts();
        }

        // ПРОСРОЧЕННЫЕ ТОВАРЫ
        private void ShowExpiredProducts()
        {
            using (SQLiteConnection connection =
                new SQLiteConnection(connectionString))
            {
                connection.Open();

                string sql = @"
                SELECT * FROM Products
                WHERE date(ExpirationDate)
                < date('now')";

                SQLiteDataAdapter adapter =
                    new SQLiteDataAdapter(
                        sql,
                        connection);

                DataTable table =
                    new DataTable();

                adapter.Fill(table);

                dgvProducts.DataSource =
                    table;
            }
        }

        // КНОПКА ПРОСРОЧЕННЫХ
        private void btnExpired_Click(
            object sender,
            EventArgs e)
        {
            ShowExpiredProducts();
        }
    }
}