---

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

Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;

string connString = "Data Source=app.db";

while (true)
{
    Console.WriteLine("\n1. Все бренды | 2. Все напитки с брендами | 3. Напитки бренда | 0. Выход");
    Console.Write("Выбор: ");
    string choice = Console.ReadLine();

    if (choice == "0") break;

    using (var db = new SqliteConnection(connString))
    {
        if (choice == "1")
        {
            var brands = db.Query("SELECT id, name FROM brands");
            foreach (var b in brands)
            {
                Console.WriteLine($"[{b.id}] {b.name}");
            }
        }
        else if (choice == "2")
        {
            string sql = "SELECT d.id, d.name, d.has_sugar, d.alcohol_percentage, b.name AS brand_name " +
                         "FROM drinks d JOIN brands b ON d.brand_id = b.id";
            var drinks = db.Query(sql);
            
            foreach (var d in drinks)
            {
                string sugar = d.has_sugar == 1 ? "С сахаром" : "Без сахара";
                Console.WriteLine($"ID: {d.id} | {d.name} | Бренд: {d.brand_name} | {sugar} | Алк: {d.alcohol_percentage}%");
            }
        }
        else if (choice == "3")
        {
            Console.Write("Введите ID бренда: ");
            int brandId = int.Parse(Console.ReadLine());

            var drinks = db.Query("SELECT id, name, has_sugar, alcohol_percentage FROM drinks WHERE brand_id = @Id", new { Id = brandId });
            
            foreach (var d in drinks)
            {
                string sugar = d.has_sugar == 1 ? "С сахаром" : "Без сахара";
                Console.WriteLine($"ID: {d.id} | {d.name} | {sugar} | Алк: {d.alcohol_percentage}%");
            }
        }
    }
}