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

// #include <boost/sqlite/extension.hpp>
#include <boost/sqlite.hpp>
#include <boost/sqlite/blob.hpp>
#include <boost/sqlite/static_resultset.hpp>

#include <iostream>
#include <fstream>

struct query_result
{
    sqlite_int64 id;
    boost::sqlite::string_view regio_name;
    boost::sqlite::string_view image; // WORKS, but wrong result
    // boost::sqlite::blob_view image; // DOES NOT COMPILE
    // boost::sqlite::blob image; // DOES NOT COMPILE
    // boost::sqlite::value image; // DOES NOT WORK
};

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

  boost::system::error_code ec;
  boost::sqlite::error_info ei;
  conn.execute("create table italy (id integer primary key autoincrement, regio_name text, image blob);", 
        ec, ei);
  if (ec)
  {
      std::cerr << "(Ignoring: " << ec << " '" << ei.message() << "')" << std::endl;
  }

  conn.execute("delete from italy");

  // Get a blob from a PNG file as suggested by copilot
  auto get_blob = [](const std::string& filename) -> boost::sqlite::blob
  {
    std::ifstream file(filename, std::ios::binary);
    std::vector<char> buffer(std::istreambuf_iterator<char>(file), {});
    return boost::sqlite::blob{boost::sqlite::blob_view{buffer.data(), buffer.size()}};
};

  std::ifstream file("veneto.png", std::ios::binary);
  std::vector<char> buffer(std::istreambuf_iterator<char>(file), {});
  conn.prepare("insert into italy (regio_name, image) values (?1, ?2)").execute({"Veneto", get_blob("veneto.png")});

  // Insert another one using the tuple from step 1
  // But this does not work as simple as that,
  // Because a blob cannot be constructed without initializer.
  // Using a tuple needs parameters like ?1, ?2 and not named parameters
  // (this makes sense).
  // However, the blob is NOT inserted, somehow...
  std::tuple<std::string, boost::sqlite::blob> mytuple{"", get_blob("piedmont.png")};
  std::get<0>(mytuple) = "Piedmont";
  conn.prepare("insert into italy (regio_name, image) values (?1, ?2)").execute(mytuple);

  // Query for the blob, this time walk manually as per documentaiton.
  auto query = conn.query("select * from italy");
  do
  {
      auto row = query.current();
      std::cout << row.at(0u).get_int64()
        << " " << row.at(1u).get_text()
        << " blob size: " << row.at(2u).get_blob().size() << std::endl;
  } while (query.read_next());

  // Query for the blob using the struct.
  // This is harder, because the blob is not a string_view.
  for (auto const &row : conn.query<query_result>("select * from italy"))
  {
      std::cout << "Described " << row.id << ": "
                << " " << row.regio_name
                << " blob size: " << row.image.size()
                << std::endl;
  }

  // Again then back to the tuple approach. 
  // But this has the same problems as the struct:
  // constructor for 'boost::sqlite::static_resultset<std::tuple<int, std::string, boost::sqlite::blob_view>>::iterator' 
  // must explicitly initialize the member 'value_' which does not have a default constructor
//   auto results3 = conn.query<std::tuple<int, std::string, boost::sqlite::blob_view>>("select id, regio_name, image from italy");   
//   for (auto const& tup : results3)
//   {
//       std::cout << "Tuple: " << std::get<0>(tup)
//         << " " << std::get<1>(tup) 
//         //<< " " << std::get<2>(tup) 
//         << std::endl;
//   }


  return 0;
}