// *table: users
struct User
{
    // by default all the fields are not null
    // to make field nullable wrap it into ngrest::Nullable

    // *pk: true
    // *autoincrement: true
    int id;

    std::string name;

    // *unique: true
    std::string email;
};

#include <ngrest/db/SQLiteDb.h>
#include <ngrest/db/Table.h>

// ...

ngrest::SQLiteDb db("test.db"); // database connection
ngrest::Table<User> users(db); // object to work with users table

// exclude "id" from fields when inserting to make auto-increment working
users.setInsertFieldsInclusion({"id"}, ngrest::FieldsInclusion::Exclude);

// insert few users into 'users' table
// note: id is generated with AUTOINCREMENT
users << User {0, "John", "[email protected]"}
      << User {0, "James", "[email protected]"}
      << User {0, "Jane", "[email protected]"};

// inserting the list using stream operators is possible too.
// also this is the prefered way to insert large amount of items
users << std::list<User>({
    {0, "Martin", "[email protected]"},
    {0, "Marta", "[email protected]"}
});

// select a user by id
users("id = ?", 1) >> user; // selects John

// std::ostream operators are generated too
std::cout << "User with id = 1 is: " << user << std::endl;

// select multiple users
std::list<User> userList;
users("name LIKE ?", "Ja%") >> userList; // selects James, Jane

// create table by schema if not exists
users.create();

// delete all data from the table
users.deleteAll();

// insert users using 'insert' method
users.insert({0, "Willy", "[email protected]"});
users.insert({0, "Sally", "[email protected]"});

// select all
const std::list<User>& resList0 = users.select();

// the count of query arguments is not limited (works with stream operators as well)
// selects Willy, Sally
const std::list<User>& resList2 = users.select("id > ? AND name LIKE ?", 2, "%lly");

// select only specified fields, other fields are has default initialization
const std::list<User>& resList6 = users.selectFields({"id", "name"}, "id = ?", 1);

// select one user
const User& resOne2 = users.selectOne("id = ?", 1);


// query using std::tuple
typedef std::tuple<int, std::string, std::string> UserInfo;

const std::list<UserInfo>& resRaw2 =
    users.selectTuple<UserInfo>({"id", "name", "email"}, "id > ?", 3);

for (const UserInfo& info : resRaw2) {
    int id;
    std::string name;
    std::string email;

    std::tie(id, name, email) = info;

    std::cout << id << " " << name << " " << email << std::endl;
}

// query one item using std::tuple

const UserInfo& resRaw3 = users.selectOneTuple<UserInfo>({"id", "name", "email"}, "id = ?", 2);

int id;
std::string name;
std::string email;

std::tie(id, name, email) = resRaw3;

std::cout << id << " " << name << " " << email << std::endl;

#ifndef NOTES_H
#define NOTES_H

#include <list>
#include <unordered_map>
#include <ngrest/common/Service.h>

// *table: notes
struct Note
{
    // *pk: true
    // *autoincrement: true
    int id;

    // *unique: true
    std::string title;

    std::string text;
};

// *location: notes
class Notes: public ngrest::Service
{
public:
    //! add a note, returns inserted id
    // *method: POST
    int add(const std::string& title, const std::string& text);

    //! remove node by id
    // *method: DELETE
    void remove(int id);

    //! get note by id
    Note get(int id);

    //! get list of ids of all notes
    std::list<int> ids();

    //! get map of notes: id, title
    std::unordered_map<int, std::string> list();

    //! find notes containing title
    std::list<Note> find(const std::string& title);
};


#endif // NOTES_H

#include <ngrest/db/SQLiteDb.h>
#include <ngrest/db/DbManager.h>

#include "Notes.h"

// singleton to access notes db
class NotesDb: public ::ngrest::DbManager< ::ngrest::SQLiteDb >
{
public:
    static NotesDb& inst()
    {
        static NotesDb instance;
        return instance;
    }

private:
    NotesDb():
        DbManager("notes.db")
    {
        // perform DB initialization here
        createAllTables(); // create all missing tables
    }
};


int Notes::add(const std::string& title, const std::string& text)
{
    return NotesDb::inst().getTable<Note>()
            .insert({0, title, text})
            .lastInsertId();
}

void Notes::remove(int id)
{
    NotesDb::inst().getTable<Note>().deleteWhere("id = ?", id);
}

Note Notes::get(int id)
{
    return NotesDb::inst().getTable<Note>().selectOne("id = ?", id);
}

std::list<int> Notes::ids()
{
    return NotesDb::inst().getTable<Note>().selectTuple<int>({"id"});
}

std::unordered_map<int, std::string> Notes::list()
{
    // example of access to DB without using Table<>
    std::unordered_map<int, std::string> result;

    ngrest::Query query(NotesDb::inst().db());
    query.prepare("SELECT id, title FROM notes");

    while (query.next()) {
        // read id from result(0), title from result(1)
        query.resultString(1, result[query.resultInt(0)]);
    }
    return result;
}

std::list<Note> Notes::find(const std::string& title)
{
    return NotesDb::inst().getTable<Note>().select("title LIKE ?", "%" + title + "%");
}