// Boost.Sqlite review
// Copyright (c) 2024 Barend Gehrels, Amsterdam, the Netherlands.

#include <boost/sqlite.hpp>

#if __cplusplus < 202002L
#include <boost/describe.hpp>
#endif

#include <iostream>

struct query_result
{
    int64_t id;
    std::string regio_name;
    int64_t population;
    double latitude;
    double longitude;
};
#if __cplusplus < 202002L
BOOST_DESCRIBE_STRUCT(query_result, (), (id, regio_name, population, latitude, longitude)); // For C++14 only
#endif

int main(int argc, char *argv[])
{
  boost::sqlite::connection conn{"/tmp/step1.db"};

  try
  {
    conn.execute(R"(
    create table italy (
        id         integer primary key autoincrement,
        regio_name text,
        population integer,
        latitude   real,
        longitude  real
    );
    )");
  }
  catch(const std::exception& e)
  {
    std::cerr << e.what() << '\n';
  }

  conn.execute("delete from italy");


  // Simple insert
  conn.prepare("insert into italy (regio_name, population, latitude, longitude) values (?1, ?2, ?3, ?4)")
    .execute({"Veneto", 4865380, 45.733333, 11.85});

  // Using a transcation and named parameters
  conn.query("begin transaction;");
  auto statement = conn.prepare("insert into italy (regio_name, population, latitude, longitude) values ($regio, $pop, $lat, $lon)");

  statement.execute({{"regio", "Tuscany"}, {"pop", 3722729}, {"lat", 43.7711}, {"lon", 11.2486}});
  statement.execute({{"regio", "Lombardy"}, {"pop", 10060574}, {"lat", 45.4642}, {"lon", 9.1900}});

  conn.query("commit;");

  // Enter fields on by one. First try does not work, it needs an initializer list, 
  // not a vector.
  if (false)
  {
    std::vector<std::pair<boost::sqlite::string_view, boost::sqlite::param_ref>> myparams;
    myparams.emplace_back("regio", "Sicily");
    myparams.emplace_back("pop", 4999891);
    myparams.emplace_back("lat", 37.599993);
    myparams.emplace_back("lon", 14.015356);

    // This does not compile
    // statement.execute(myparams);
  }

  // Second try, inspecting the source code and bind_impl - using tuples - this works
  std::tuple<std::string, int64_t, double, double> mytuple;
  std::get<0>(mytuple) = "Sardinia";
  std::get<1>(mytuple) = 1639591;
  std::get<2>(mytuple) = 39.2153;
  std::get<3>(mytuple) = 9.1213;
  statement.execute(mytuple);


  // Simple query
  auto results1 = conn.query("select id, regio_name, population from italy");  
  for (auto const& row : results1)
  {
      std::cout << "Simple: " << row.at(0u).get_int64() << " " << row.at(1u).get_text() << " " << row.at(2u).get_text() << std::endl;
  }

  // Prepared query (this is not documented yet)
  auto statement2 = conn.prepare("select id, regio_name, population from italy where regio_name = $regio");
  // You cannot reuse results1
  auto results2 = statement2.execute({{"regio", "Tuscany"}});
  for (auto const& row : results2)
  {
      std::cout << "Prepared: " << row.at(0u).get_int64() << " " << row.at(1u).get_text() << " " << row.at(2u).get_int64() << std::endl;
  }    

  // Query with tuples, this is awesome.
  auto statement3 = conn.prepare("select id, regio_name, population, longitude, latitude from italy where regio_name = $regio");
  auto results3 = statement3.execute<std::tuple<int, std::string, int64_t, double, double>>({{"regio", "Veneto"}});
  for (auto const& tup : results3)
  {
      std::cout << "Tuple: " << std::get<0>(tup)
        << " " << std::get<1>(tup) << " " << std::get<2>(tup) 
        << " " << std::get<3>(tup) << " " << std::get<4>(tup) 
        << std::endl;
  }

  // Query with a struct, this is really cool!!!
  // And it works for both C++14 and C++20.
  // No need for an ORM
  for (auto q : conn.query<query_result>("select * from italy"))
  {
      std::cout << "Described " << q.id 
        << ": " << q.regio_name << " " << q.population
        << " " << q.latitude << " " << q.longitude
        << std::endl;
  }

  // Can we insert as well? No, that is not possible (yet).
  // query_result qr;
  // statement.execute(qr);

  return 0;
}