---

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

const string CS = "Data Source=bookstore.db";

// ============================================================
// 1. INIT DB (Topic: DbCommand, ExecuteNonQuery)
// ============================================================
using (var conn = new SqliteConnection(CS))
{
    conn.Open();
    var cmd = conn.CreateCommand();
    cmd.CommandText = @"
        CREATE TABLE IF NOT EXISTS Books (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            Title TEXT, AuthorName TEXT, Genre TEXT,
            PublicationYear INTEGER, CostPrice REAL, RetailPrice REAL,
            IsSequel INTEGER, Stock INTEGER);
        CREATE TABLE IF NOT EXISTS Sales (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            BookId INTEGER, Date TEXT, Quantity INTEGER, UnitPrice REAL);
        CREATE TABLE IF NOT EXISTS Promotions (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            Name TEXT, DiscountPercent REAL, EndDate TEXT);
        CREATE TABLE IF NOT EXISTS Reservations (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            BookId INTEGER, BuyerName TEXT, Quantity INTEGER);";
    cmd.ExecuteNonQuery();
}

// ============================================================
// 2. AUTH  (Topic: конфигурационные файлы — hard-coded here)
// ============================================================
Console.WriteLine("=== Bookstore Manager ===");
while (true)
{
    Console.Write("Username: ");
    var u = Console.ReadLine();
    Console.Write("Password: ");
    var p = ReadPassword();
    if (u == "admin" && p == "admin123") break;
    Console.WriteLine("Invalid. Try admin / admin123\n");
}

// ============================================================
// 3. MENU
// ============================================================
while (true)
{
    Console.WriteLine(@"
=== MENU ===
 1. List inventory
 2. Add book
 3. Delete book
 4. Edit price
 5. Sell book      (transaction)
 6. Write off book
 7. Create promotion
 8. Reserve for buyer
 9. Disconnected demo (DataSet / DataAdapter)
10. Search
11. Analytics
 0. Exit
");
    Console.Write("Choice: ");
    try
    {
        switch (Console.ReadLine())
        {
            case "1": List(); break;
            case "2": Add(); break;
            case "3": Delete(); break;
            case "4": EditPrice(); break;
            case "5": Sell(); break;
            case "6": WriteOff(); break;
            case "7": Promo(); break;
            case "8": Reserve(); break;
            case "9": DisconnectedDemo(); break;
            case "10": Search(); break;
            case "11": Analytics(); break;
            case "0": return;
        }
    }
    catch (Exception ex) { Console.WriteLine($"ERROR: {ex.Message}"); }
}

// ============================================================
// HELPERS
// ============================================================
static string ReadPassword()
{
    var s = "";
    ConsoleKeyInfo k;
    while ((k = Console.ReadKey(true)).Key != ConsoleKey.Enter)
    {
        if (k.Key == ConsoleKey.Backspace && s.Length > 0) s = s[..^1];
        else if (k.Key != ConsoleKey.Backspace) s += k.KeyChar;
    }
    Console.WriteLine();
    return s;
}

static int ReadInt(string msg) { Console.Write(msg); return int.TryParse(Console.ReadLine(), out var n) ? n : 0; }
static decimal ReadDec(string msg) { Console.Write(msg); return decimal.TryParse(Console.ReadLine(), out var n) ? n : 0m; }

static SqliteConnection Open()
{
    var c = new SqliteConnection(CS);
    c.Open();
    return c;
}

// ============================================================
// 1. LIST (Topic: DbDataReader)
// ============================================================
static void List()
{
    using var conn = Open();
    var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT Id, Title, AuthorName, Genre, PublicationYear, Stock, RetailPrice FROM Books";

    using var r = cmd.ExecuteReader();
    Console.WriteLine($"\n{"ID",-4} {"Title",-30} {"Author",-18} {"Genre",-10} {"Year",-6} {"Stock",-6} {"Price",8}");
    while (r.Read())
        Console.WriteLine($"{r.GetInt32(0),-4} {r.GetString(1),-30} {r.GetString(2),-18} " +
                          $"{r.GetString(3),-10} {r.GetInt32(4),-6} {r.GetInt32(5),-6} ${r.GetDecimal(6),7:F2}");
}

// ============================================================
// 2. ADD  (Topic: DbParameter)
// ============================================================
static void Add()
{
    Console.Write("Title: "); var title = Console.ReadLine() ?? "";
    Console.Write("Author: "); var author = Console.ReadLine() ?? "";
    Console.Write("Genre: "); var genre = Console.ReadLine() ?? "";
    var year = ReadInt("Year: ");
    var cost = ReadDec("Cost price: ");
    var retail = ReadDec("Retail price: ");
    Console.Write("Sequel (y/N): "); var sequel = Console.ReadLine()?.ToLower() == "y" ? 1 : 0;
    var stock = ReadInt("Stock: ");

    using var conn = Open();
    var cmd = conn.CreateCommand();
    cmd.CommandText = @"INSERT INTO Books
        (Title, AuthorName, Genre, PublicationYear, CostPrice, RetailPrice, IsSequel, Stock)
        VALUES (@t, @a, @g, @y, @c, @r, @s, @st)";
    cmd.Parameters.AddWithValue("@t", title);
    cmd.Parameters.AddWithValue("@a", author);
    cmd.Parameters.AddWithValue("@g", genre);
    cmd.Parameters.AddWithValue("@y", year);
    cmd.Parameters.AddWithValue("@c", cost);
    cmd.Parameters.AddWithValue("@r", retail);
    cmd.Parameters.AddWithValue("@s", sequel);
    cmd.Parameters.AddWithValue("@st", stock);
    Console.WriteLine($"Added. Rows affected: {cmd.ExecuteNonQuery()}");
}

// ============================================================
// 3. DELETE
// ============================================================
static void Delete()
{
    var id = ReadInt("Book ID to delete: ");
    using var conn = Open();
    var cmd = conn.CreateCommand();
    cmd.CommandText = "DELETE FROM Books WHERE Id = @id";
    cmd.Parameters.AddWithValue("@id", id);
    Console.WriteLine($"Deleted. Rows affected: {cmd.ExecuteNonQuery()}");
}

// ============================================================
// 4. EDIT PRICE
// ============================================================
static void EditPrice()
{
    var id = ReadInt("Book ID: ");
    var price = ReadDec("New retail price: ");
    using var conn = Open();
    var cmd = conn.CreateCommand();
    cmd.CommandText = "UPDATE Books SET RetailPrice = @p WHERE Id = @id";
    cmd.Parameters.AddWithValue("@p", price);
    cmd.Parameters.AddWithValue("@id", id);
    Console.WriteLine($"Updated. Rows affected: {cmd.ExecuteNonQuery()}");
}

// ============================================================
// 5. SELL (Topic: DbTransaction)
// ============================================================
static void Sell()
{
    var id = ReadInt("Book ID: ");
    var qty = ReadInt("Quantity: ");

    using var conn = Open();
    using var tx = conn.BeginTransaction();
    try
    {
        // Check stock
        var check = conn.CreateCommand();
        check.Transaction = tx;
        check.CommandText = "SELECT Stock, RetailPrice FROM Books WHERE Id = @id";
        check.Parameters.AddWithValue("@id", id);
        using (var r = check.ExecuteReader())
        {
            if (!r.Read()) { Console.WriteLine("Book not found."); return; }
            var stock = r.GetInt32(0);
            var price = r.GetDecimal(1);
            if (stock < qty) { Console.WriteLine("Not enough stock."); return; }

            r.Close();

            // Decrement stock
            var upd = conn.CreateCommand();
            upd.Transaction = tx;
            upd.CommandText = "UPDATE Books SET Stock = Stock - @q WHERE Id = @id";
            upd.Parameters.AddWithValue("@q", qty);
            upd.Parameters.AddWithValue("@id", id);
            upd.ExecuteNonQuery();

            // Insert sale
            var ins = conn.CreateCommand();
            ins.Transaction = tx;
            ins.CommandText = @"INSERT INTO Sales (BookId, Date, Quantity, UnitPrice)
                                VALUES (@b, @d, @q, @p)";
            ins.Parameters.AddWithValue("@b", id);
            ins.Parameters.AddWithValue("@d", DateTime.UtcNow.ToString("o"));
            ins.Parameters.AddWithValue("@q", qty);
            ins.Parameters.AddWithValue("@p", price);
            ins.ExecuteNonQuery();

            tx.Commit();
            Console.WriteLine($"Sold {qty} × '{id}' for ${price * qty:F2}");
        }
    }
    catch { tx.Rollback(); throw; }
}

// ============================================================
// 6. WRITE OFF
// ============================================================
static void WriteOff()
{
    var id = ReadInt("Book ID: ");
    var qty = ReadInt("Quantity to write off: ");
    using var conn = Open();
    var cmd = conn.CreateCommand();
    cmd.CommandText = "UPDATE Books SET Stock = Stock - @q WHERE Id = @id AND Stock >= @q";
    cmd.Parameters.AddWithValue("@q", qty);
    cmd.Parameters.AddWithValue("@id", id);
    Console.WriteLine($"Written off. Rows affected: {cmd.ExecuteNonQuery()}");
}

// ============================================================
// 7. CREATE PROMOTION
// ============================================================
static void Promo()
{
    Console.Write("Promotion name: "); var name = Console.ReadLine() ?? "Promo";
    var disc = ReadDec("Discount %: ");
    var days = ReadInt("Duration (days): ");

    using var conn = Open();

    var ins = conn.CreateCommand();
    ins.CommandText = @"INSERT INTO Promotions (Name, DiscountPercent, EndDate)
                        VALUES (@n, @d, @e); SELECT last_insert_rowid();";
    ins.Parameters.AddWithValue("@n", name);
    ins.Parameters.AddWithValue("@d", disc);
    ins.Parameters.AddWithValue("@e", DateTime.UtcNow.AddDays(days).ToString("o"));
    var promoId = (long)ins.ExecuteScalar()!;

    Console.WriteLine("Enter book IDs to include (empty line to finish):");
    var ids = new List<int>();
    while (true)
    {
        Console.Write("Book ID: ");
        var line = Console.ReadLine();
        if (string.IsNullOrWhiteSpace(line)) break;
        if (int.TryParse(line, out var id)) ids.Add(id);
    }

    foreach (var id in ids)
    {
        var upd = conn.CreateCommand();
        upd.CommandText = @"UPDATE Books
                            SET RetailPrice = RetailPrice * (1 - @d/100.0)
                            WHERE Id = @id";
        upd.Parameters.AddWithValue("@d", disc);
        upd.Parameters.AddWithValue("@id", id);
        upd.ExecuteNonQuery();
    }
    Console.WriteLine($"Promotion #{promoId} applied to {ids.Count} book(s).");
}

// ============================================================
// 8. RESERVE
// ============================================================
static void Reserve()
{
    var id = ReadInt("Book ID: ");
    Console.Write("Buyer name: "); var buyer = Console.ReadLine() ?? "";
    var qty = ReadInt("Quantity: ");

    using var conn = Open();
    using var tx = conn.BeginTransaction();

    var upd = conn.CreateCommand();
    upd.Transaction = tx;
    upd.CommandText = "UPDATE Books SET Stock = Stock - @q WHERE Id = @id AND Stock >= @q";
    upd.Parameters.AddWithValue("@q", qty);
    upd.Parameters.AddWithValue("@id", id);

    if (upd.ExecuteNonQuery() == 0) { Console.WriteLine("Not enough stock."); return; }

    var ins = conn.CreateCommand();
    ins.Transaction = tx;
    ins.CommandText = "INSERT INTO Reservations (BookId, BuyerName, Quantity) VALUES (@b, @n, @q)";
    ins.Parameters.AddWithValue("@b", id);
    ins.Parameters.AddWithValue("@n", buyer);
    ins.Parameters.AddWithValue("@q", qty);
    ins.ExecuteNonQuery();

    tx.Commit();
    Console.WriteLine($"Reserved {qty} for {buyer}.");
}

// ============================================================
// 9. DISCONNECTED DEMO
//    (Topic: DataSet, DataTable, DataRow, DataColumn,
//            DbDataAdapter, SqlCommandBuilder)
// ============================================================
static void DisconnectedDemo()
{
    using var conn = Open();

    // DbDataAdapter → fills DataTable in memory (connection can close after)
    var adapter = new SqliteDataAdapter("SELECT Id, Title, AuthorName, Genre, PublicationYear, Stock, RetailPrice FROM Books", conn);

    // SqlCommandBuilder auto-generates INSERT/UPDATE/DELETE for adapter.Update()
    var builder = new SqliteCommandBuilder(adapter);

    var ds = new DataSet("Bookstore");
    adapter.Fill(ds, "Books");
    var table = ds.Tables["Books"]!;

    Console.WriteLine($"\nLoaded {table.Rows.Count} rows (disconnected).");

    // Inspect columns
    Console.WriteLine("\nColumns:");
    foreach (DataColumn c in table.Columns)
        Console.WriteLine($"  {c.ColumnName,-20} {c.DataType.Name}");

    // Modify a row (in-memory)
    if (table.Rows.Count > 0)
    {
        var row = table.Rows[0];
        Console.WriteLine($"\nFirst row before: {row["Title"]} / stock {row["Stock"]}");
        row["Stock"] = (int)row["Stock"]! + 1;
        Console.WriteLine($"First row after:  {row["Title"]} / stock {row["Stock"]}");
    }

    // Add a row (in-memory)
    var newRow = table.NewRow();
    newRow["Title"] = "Disconnected Demo Book";
    newRow["AuthorName"] = "Nobody";
    newRow["Genre"] = "Demo";
    newRow["PublicationYear"] = DateTime.UtcNow.Year;
    newRow["Stock"] = 1;
    newRow["RetailPrice"] = 9.99m;
    table.Rows.Add(newRow);
    Console.WriteLine($"\nAdded a row in memory. Total rows: {table.Rows.Count}");

    // Push changes back to the DB
    int changed = adapter.Update(ds, "Books");
    Console.WriteLine($"\nDataAdapter.Update() pushed {changed} change(s) back to DB.");
}

// ============================================================
// 10. SEARCH
// ============================================================
static void Search()
{
    Console.Write("Search by (title/author/genre): ");
    var field = Console.ReadLine()?.ToLower() ?? "title";
    var col = field switch { "author" => "AuthorName", "genre" => "Genre", _ => "Title" };

    Console.Write("Query: ");
    var q = Console.ReadLine() ?? "";

    using var conn = Open();
    var cmd = conn.CreateCommand();
    cmd.CommandText = $"SELECT Id, Title, AuthorName, Genre, RetailPrice FROM Books WHERE {col} LIKE @q";
    cmd.Parameters.AddWithValue("@q", $"%{q}%");

    using var r = cmd.ExecuteReader();
    while (r.Read())
        Console.WriteLine($"{r.GetInt32(0),-4} {r.GetString(1),-30} {r.GetString(2),-18} " +
                          $"{r.GetString(3),-10} ${r.GetDecimal(4),7:F2}");
}

// ============================================================
// 11. ANALYTICS  (Topic: LINQ over DataSet, group by, join)
// ============================================================
static void Analytics()
{
    Console.Write("Period in days (1/7/30/365): ");
    var days = int.TryParse(Console.ReadLine(), out var d) ? d : 30;
    var cutoff = DateTime.UtcNow.AddDays(-days).ToString("o");

    using var conn = Open();

    // Best sellers
    Console.WriteLine($"\n=== Best sellers (last {days} days) ===");
    var bs = conn.CreateCommand();
    bs.CommandText = @"SELECT b.Title, SUM(s.Quantity) AS Sold
                       FROM Sales s JOIN Books b ON b.Id = s.BookId
                       WHERE s.Date >= @c
                       GROUP BY b.Id ORDER BY Sold DESC LIMIT 5";
    bs.Parameters.AddWithValue("@c", cutoff);
    using (var r = bs.ExecuteReader())
        while (r.Read())
            Console.WriteLine($"  {r.GetString(0),-30} {r.GetInt32(1)} sold");

    // Top authors
    Console.WriteLine($"\n=== Top authors ===");
    var ta = conn.CreateCommand();
    ta.CommandText = @"SELECT b.AuthorName, SUM(s.Quantity) AS Sold
                       FROM Sales s JOIN Books b ON b.Id = s.BookId
                       WHERE s.Date >= @c
                       GROUP BY b.AuthorName ORDER BY Sold DESC LIMIT 5";
    ta.Parameters.AddWithValue("@c", cutoff);
    using (var r = ta.ExecuteReader())
        while (r.Read())
            Console.WriteLine($"  {r.GetString(0),-30} {r.GetInt32(1)} sold");

    // Top genres
    Console.WriteLine($"\n=== Top genres ===");
    var tg = conn.CreateCommand();
    tg.CommandText = @"SELECT b.Genre, SUM(s.Quantity) AS Sold
                       FROM Sales s JOIN Books b ON b.Id = s.BookId
                       WHERE s.Date >= @c
                       GROUP BY b.Genre ORDER BY Sold DESC LIMIT 5";
    tg.Parameters.AddWithValue("@c", cutoff);
    using (var r = tg.ExecuteReader())
        while (r.Read())
            Console.WriteLine($"  {r.GetString(0),-30} {r.GetInt32(1)} sold");
}