using Dapper; // Metoda rozszerzenia Query<T>
using Microsoft.Data.SqlClient; // Klasa SqlConnection i inne
using System.Data; // CommandType

SqlConnectionStringBuilder builder = new();

builder.InitialCatalog = "Northwind";
builder.MultipleActiveResultSets = true;
builder.Encrypt = true;
builder.TrustServerCertificate = true;
builder.ConnectTimeout = 10;

WriteLine("Połączenie z bazą:");
WriteLine("  1 - SQL Server na lokalnym komputerze");
WriteLine("  2 - Azure SQL Database");
WriteLine("  3 – Azure SQL Edge");
WriteLine();
Write("Wybierz opcję: ");

ConsoleKey key = ReadKey().Key;
WriteLine(); WriteLine();

if (key is ConsoleKey.D1 or ConsoleKey.NumPad1)
{
  builder.DataSource = "."; // Lokalna baza SQL Server
  // @".\net7book"; // Lokalna baza SQL Server z nazwą instancji

}
else if (key is ConsoleKey.D2 or ConsoleKey.NumPad2)
{
  builder.DataSource = // Azure SQL Database
    "tcp:apps-services-net7.database.windows.net,1433"; 
}
else if (key is ConsoleKey.D3 or ConsoleKey.NumPad3)
{
  builder.DataSource = "tcp:127.0.0.1,1433"; // Azure SQL Edge
}
else
{
    WriteLine("Nie wybrałeś bazy.");
    return;
}

WriteLine("Metoda uwierzytelnienia:");
WriteLine("  1 - Windows Authentication");
WriteLine("  2 – przy użyciu konta, np. sa");
WriteLine();
Write("Wybierz opcję: ");

key = ReadKey().Key;
WriteLine(); WriteLine();

if (key is ConsoleKey.D1 or ConsoleKey.NumPad1)
{
  builder.IntegratedSecurity = true;
}
else if (key is ConsoleKey.D2 or ConsoleKey.NumPad2)
{
  builder.UserID = "sa"; // Azure SQL Edge
  // "markjprice"; // Wpisz nazwę swojego konta

  Write("Podaj hasło: ");
  string? password = ReadLine();
  if (string.IsNullOrWhiteSpace(password))
  {
    WriteLine("Hasło nie może być puste.");
    return;
  }

  builder.Password = password;
  builder.PersistSecurityInfo = false;
}
else
{
  WriteLine("Nie wybrałeś metody uwierzytelnienia.");
  return;
}

SqlConnection connection = new(builder.ConnectionString);

WriteLine(connection.ConnectionString);
WriteLine();

connection.StateChange += Connection_StateChange;
connection.InfoMessage += Connection_InfoMessage;

try
{
  WriteLine("Nawiązywanie połączenia. Zaczekaj {0} sekund...", 
    builder.ConnectTimeout);
  WriteLine();

  await connection.OpenAsync();

  WriteLine($"Wersja serwera SQL Server: {connection.ServerVersion}");

  connection.StatisticsEnabled = true;
}
catch (SqlException ex)
{
  WriteLine($"Wyjątek SQL: {ex.Message}");
  return;
}

Write("Podaj cenę: ");
string? priceText = ReadLine();

if (!decimal.TryParse(priceText, out decimal price))
{
  WriteLine("Podałeś błędną cenę.");
  return;
}

SqlCommand cmd = connection.CreateCommand();

WriteLine("Dostępne operacje:");
WriteLine("  1 - Zwykłe zapytanie");
WriteLine("  2 - Procedura składowana");
WriteLine();
Write("Wybierz opcję: ");

key = ReadKey().Key;
WriteLine(); WriteLine();

SqlParameter p1, p2 = new(), p3 = new();

if (key is ConsoleKey.D1 or ConsoleKey.NumPad1)
{
  cmd.CommandType = CommandType.Text;
  cmd.CommandText = "SELECT ProductId, ProductName, UnitPrice FROM Products"
    + " WHERE UnitPrice > @price";
  cmd.Parameters.AddWithValue("price", price);
}
else if (key is ConsoleKey.D2 or ConsoleKey.NumPad2)
{
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.CommandText = "GetExpensiveProducts";

  p1 = new()
  {
    ParameterName = "price",
    SqlDbType = SqlDbType.Money,
    SqlValue = price
  };

  p2 = new()
  {
    Direction = ParameterDirection.Output,
    ParameterName = "count",
    SqlDbType = SqlDbType.Int
  };

  p3 = new()
  {
    Direction = ParameterDirection.ReturnValue,
    ParameterName = "rv",
    SqlDbType = SqlDbType.Int
  };

  cmd.Parameters.Add(p1);
  cmd.Parameters.Add(p2);
  cmd.Parameters.Add(p3);
}

SqlDataReader r = await cmd.ExecuteReaderAsync();

WriteLine("----------------------------------------------------------");
WriteLine("| {0,5} | {1,-35} | {2,8} |", "Id", "Nazwa", "Cena");
WriteLine("----------------------------------------------------------");

while (await r.ReadAsync())
{
  WriteLine("| {0,5} | {1,-35} | {2,8:C} |",
    r.GetInt32("ProductId"),
    r.GetString("ProductName"),
    r.GetDecimal("UnitPrice"));
}
WriteLine("----------------------------------------------------------");

await r.CloseAsync();

WriteLine($"Wyjściowa liczba wierszy: {p2.Value}");
WriteLine($"Zwrócony wynik: {p3.Value}");

WriteLine("**********************");
WriteLine("*       Dapper       *");
WriteLine("**********************");

// Klasa Supplier jest zdefiniowana w pliku Supplier.cs

IEnumerable<Supplier> suppliers = connection.Query<Supplier>(
  sql: "SELECT * FROM Suppliers WHERE Country=@Country",
  param: new { Country = "Germany" });

foreach (Supplier supplier in suppliers)
{
  WriteLine("{0}: {1}, {2}, {3}",
    supplier.SupplierId, supplier.CompanyName,
    supplier.City, supplier.Country);
}

await connection.CloseAsync();