---

using System;
using System.Data;
using Microsoft.Data.Sqlite;

namespace BookStoreApp
{
    class Program
    {
        // This creates a database file right next to your .exe file
        static string connectionString = "Data Source=bookstore.db";

        static void Main(string[] args)
        {
            if (!Login()) return;
            InitDatabase();

            while (true)
            {
                Console.WriteLine("\n--- КНИЖНЫЙ МАГАЗИН ---");
                Console.WriteLine("1. Добавить книгу");
                Console.WriteLine("2. Показать все книги");
                Console.WriteLine("3. Продать книгу (со скидкой 10%)");
                Console.WriteLine("4. Поиск (название/автор/жанр)");
                Console.WriteLine("5. Новинки");
                Console.WriteLine("0. Выход");
                Console.Write("Выбор: ");

                string choice = Console.ReadLine();
                if (choice == "1") AddBook();
                else if (choice == "2") ShowBooks();
                else if (choice == "3") SellBook();
                else if (choice == "4") SearchBooks();
                else if (choice == "5") ShowNewArrivals();
                else if (choice == "0") break;
            }
        }

        static bool Login()
        {
            Console.Write("Логин (admin): "); string login = Console.ReadLine();
            Console.Write("Пароль (123): "); string pass = Console.ReadLine();
            if (login == "admin" && pass == "123") return true;
            Console.WriteLine("Неверный логин или пароль!");
            return false;
        }

        static void InitDatabase()
        {
            using (var connection = new SqliteConnection(connectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = @"
                        CREATE TABLE IF NOT EXISTS Books (
                            Id INTEGER PRIMARY KEY AUTOINCREMENT,
                            Title TEXT, Author TEXT, Genre TEXT, Year INTEGER, 
                            SalePrice REAL, IsOnSale INTEGER
                        );
                        CREATE TABLE IF NOT EXISTS Sales (
                            Id INTEGER PRIMARY KEY AUTOINCREMENT,
                            BookId INTEGER, SaleDate TEXT, Amount REAL
                        );";
                    command.ExecuteNonQuery();
                }
            }
        }

        static void AddBook()
        {
            Console.Write("Название: "); string title = Console.ReadLine() ?? "";
            Console.Write("Автор: "); string author = Console.ReadLine() ?? "";
            Console.Write("Жанр: "); string genre = Console.ReadLine() ?? "";
            Console.Write("Год: "); int year = int.Parse(Console.ReadLine() ?? "0");
            Console.Write("Цена: "); double price = double.Parse(Console.ReadLine() ?? "0");
            Console.Write("Участвует в акции (10%)? (1-да, 0-нет): "); int sale = int.Parse(Console.ReadLine() ?? "0");

            using (var connection = new SqliteConnection(connectionString))
            {
                connection.Open();
                using (var cmd = connection.CreateCommand())
                {
                    cmd.CommandText = "INSERT INTO Books (Title, Author, Genre, Year, SalePrice, IsOnSale) VALUES (@t, @a, @g, @y, @p, @s)";
                    cmd.Parameters.AddWithValue("@t", title);
                    cmd.Parameters.AddWithValue("@a", author);
                    cmd.Parameters.AddWithValue("@g", genre);
                    cmd.Parameters.AddWithValue("@y", year);
                    cmd.Parameters.AddWithValue("@p", price);
                    cmd.Parameters.AddWithValue("@s", sale);
                    cmd.ExecuteNonQuery();
                }
            }
            Console.WriteLine("Книга добавлена!");
        }

        static void ShowBooks()
        {
            using (var connection = new SqliteConnection(connectionString))
            {
                connection.Open();
                // Disconnected mode simulation using DataTable
                using (var adapter = new SqliteDataAdapter("SELECT Id, Title, Author, Year, SalePrice, IsOnSale FROM Books", connection))
                {
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);

                    Console.WriteLine("\nID | Название | Автор | Год | Цена | Акция");
                    foreach (DataRow row in dt.Rows)
                    {
                        string saleStr = row["IsOnSale"].ToString() == "1" ? "ДА" : "Нет";
                        Console.WriteLine($"{row["Id"]} | {row["Title"]} | {row["Author"]} | {row["Year"]} | {row["SalePrice"]} | {saleStr}");
                    }
                }
            }
        }

        static void SellBook()
        {
            Console.Write("Введите ID книги для продажи: ");
            int id = int.Parse(Console.ReadLine() ?? "0");

            using (var connection = new SqliteConnection(connectionString))
            {
                connection.Open();
                using (var getCmd = connection.CreateCommand())
                {
                    getCmd.CommandText = "SELECT SalePrice, IsOnSale FROM Books WHERE Id = @id";
                    getCmd.Parameters.AddWithValue("@id", id);
                    
                    using (var reader = getCmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            double price = reader.GetDouble(0);
                            int isSale = reader.GetInt32(1);
                            if (isSale == 1) price *= 0.9; // 10% discount
                            reader.Close();

                            using (var saleCmd = connection.CreateCommand())
                            {
                                saleCmd.CommandText = "INSERT INTO Sales (BookId, SaleDate, Amount) VALUES (@id, @date, @amt)";
                                saleCmd.Parameters.AddWithValue("@id", id);
                                saleCmd.Parameters.AddWithValue("@date", DateTime.Now.ToString("yyyy-MM-dd"));
                                saleCmd.Parameters.AddWithValue("@amt", price);
                                saleCmd.ExecuteNonQuery();
                            }

                            using (var delCmd = connection.CreateCommand())
                            {
                                delCmd.CommandText = "DELETE FROM Books WHERE Id = @id";
                                delCmd.Parameters.AddWithValue("@id", id);
                                delCmd.ExecuteNonQuery();
                            }
                            Console.WriteLine($"Продано за {price} руб. и списано.");
                        }
                        else Console.WriteLine("Книга не найдена.");
                    }
                }
            }
        }

        static void SearchBooks()
        {
            Console.Write("Поиск: ");
            string q = "%" + (Console.ReadLine() ?? "") + "%";

            using (var connection = new SqliteConnection(connectionString))
            {
                connection.Open();
                using (var adapter = new SqliteDataAdapter("SELECT Title, Author, Genre FROM Books WHERE Title LIKE @q OR Author LIKE @q OR Genre LIKE @q", connection))
                {
                    adapter.SelectCommand.Parameters.AddWithValue("@q", q);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);

                    foreach (DataRow row in dt.Rows)
                        Console.WriteLine($"{row["Title"]} - {row["Author"]} ({row["Genre"]})");
                }
            }
        }

        static void ShowNewArrivals()
        {
            using (var connection = new SqliteConnection(connectionString))
            {
                connection.Open();
                using (var adapter = new SqliteDataAdapter("SELECT Title, Year FROM Books ORDER BY Year DESC LIMIT 5", connection))
                {
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    foreach (DataRow row in dt.Rows)
                        Console.WriteLine($"{row["Year"]}: {row["Title"]}");
                }
            }
        }
    }
}