---

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

namespace BookStoreApp
{
    class Program
    {
        // Database connection string
        static string connectionString = "Data Source=bookstore.db";

        static void Main(string[] args)
        {
            // 1. Login
            if (!Login()) return;

            // 2. Initialize DB
            InitDatabase();

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

                switch (Console.ReadLine())
                {
                    case "1": AddBook(); break;
                    case "2": ShowBooks(); break;
                    case "3": SellBook(); break;
                    case "4": SearchBooks(); break;
                    case "5": ShowNewArrivals(); break;
                    case "0": return;
                }
            }
        }

        // --- DATABASE METHODS ---

        static bool Login()
        {
            Console.Write("Логин: "); string login = Console.ReadLine();
            Console.Write("Пароль: "); string pass = Console.ReadLine();
            
            if (login == "admin" && pass == "123") return true;
            Console.WriteLine("Неверный логин или пароль! (Используйте admin / 123)");
            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, Publisher TEXT, Pages INTEGER,
                    Genre TEXT, Year INTEGER, CostPrice REAL, SalePrice REAL,
                    IsContinuation INTEGER, 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("Это продолжение? (1-да, 0-нет): "); 
            int isCont = int.Parse(Console.ReadLine() ?? "0");

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

        static void ShowBooks()
        {
            using var connection = new SqliteConnection(connectionString);
            connection.Open();
            using var command = new SqliteCommand("SELECT Id, Title, Author, Year, SalePrice, IsOnSale FROM Books", connection);
            
            // Using DataTable to simulate Disconnected Mode
            DataTable dt = new DataTable();
            using var reader = command.ExecuteReader();
            dt.Load(reader);

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

        static void SellBook()
        {
            Console.Write("Введите ID книги для продажи: ");
            if (!int.TryParse(Console.ReadLine(), out int id)) return;

            using var connection = new SqliteConnection(connectionString);
            connection.Open();
            
            // Get price and check for discount
            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);
                if (reader.GetInt32(1) == 1) price *= 0.9; // 10% discount
                reader.Close();

                // Log the sale
                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();

                // Delete from inventory
                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("Книга с таким ID не найдена.");
            }
        }

        static void SearchBooks()
        {
            Console.Write("Введите запрос (название, автор или жанр): ");
            string q = "%" + (Console.ReadLine() ?? "") + "%";

            using var connection = new SqliteConnection(connectionString);
            connection.Open();
            using var command = new SqliteCommand(
                "SELECT Title, Author, Genre FROM Books WHERE Title LIKE @q OR Author LIKE @q OR Genre LIKE @q", 
                connection);
            command.Parameters.AddWithValue("@q", q);

            DataTable dt = new DataTable();
            using var reader = command.ExecuteReader();
            dt.Load(reader);

            if (dt.Rows.Count == 0) Console.WriteLine("Ничего не найдено.");
            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 command = new SqliteCommand("SELECT Title, Author, Year FROM Books ORDER BY Year DESC LIMIT 5", connection);
            
            DataTable dt = new DataTable();
            using var reader = command.ExecuteReader();
            dt.Load(reader);

            Console.WriteLine("\n--- Список новинок ---");
            foreach (DataRow row in dt.Rows)
                Console.WriteLine($"{row["Year"]}: {row["Title"]} ({row["Author"]})");
        }
    }
}