#include "matador/sql/column.hpp"
#include "matador/sql/condition.hpp"
#include "matador/sql/schema.hpp"
#include "matador/sql/connection.hpp"
#include "matador/sql/entity.hpp"

#include "matador/utils/access.hpp"
#include "matador/utils/enum_mapper.hpp"

#include "matador/sql/query_helper.hpp"

#include <iostream>
#include <string>

struct author {
    unsigned long id{};
    std::string first_name;
    std::string last_name;
    std::string date_of_birth;
    unsigned short year_of_birth{};
    bool distinguished{false};

    template<typename Operator>
    void process( Operator& op ) {
        namespace field = matador::utils::access;
        field::primary_key( op, "id", id );
        field::attribute( op, "first_name", first_name, 63 );
        field::attribute( op, "last_name", last_name, 63 );
        field::attribute( op, "date_of_birth", date_of_birth, 31 );
        field::attribute( op, "year_of_birth", year_of_birth );
        field::attribute( op, "distinguished", distinguished );
    }
};

struct book {
    unsigned long id{};
    matador::sql::entity<author> book_author;
    std::string title;
    unsigned short published_in{};

    template<typename Operator>
    void process( Operator& op ) {
        namespace field = matador::utils::access;
        field::primary_key( op, "id", id );
        field::attribute( op, "title", title, 511 );
        field::has_one( op, "author_id", book_author, matador::utils::default_foreign_attributes );
        field::attribute( op, "published_in", published_in );
    }
};

struct payload {
    unsigned long id{};

    template<typename Operator>
    void process( Operator& op ) {
        namespace field = matador::utils::access;
        field::primary_key( op, "id", id );
    }
};

struct job {
    enum class job_state {
        Pending,
        Running,
        Succeeded,
        Failed,
        Canceled
    };

    enum class job_mode {
        Foreground,
        Background
    };

    unsigned long id{};
    matador::sql::entity<payload> payload;
    std::string type;
    std::string description;
    job_state state;
    job_mode mode;

    template<typename Operator>
    void process( Operator& op ) {
        namespace field = matador::utils::access;
        field::primary_key( op, "id", id );
        field::belongs_to( op, "payload", payload, matador::utils::default_foreign_attributes );
        field::attribute( op, "type", type, 511 );
        field::attribute( op, "description", description, 511 );
        field::attribute( op, "state", state );
        field::attribute( op, "mode", mode );
    }
};

static const matador::utils::enum_mapper<job::job_state> job_state_enum({
                                                           {job::job_state::Pending,  "Pending"},
                                                           {job::job_state::Running,  "Running"},
                                                           {job::job_state::Succeeded,  "Succeeded"},
                                                           {job::job_state::Failed,  "Failed"},
                                                           {job::job_state::Canceled,  "Canceled"}
                                                           });

static const matador::utils::enum_mapper<job::job_mode> job_mode_enum({
                                                           {job::job_mode::Foreground,  "Foreground"},
                                                           {job::job_mode::Background,  "Background"}
                                                           });

template<>
struct matador::sql::data_type_traits<job::job_state, void>
{
    inline static data_type_t builtin_type(std::size_t size)
    { return data_type_traits<std::string>::builtin_type(size); }

    static void read_value(query_result_reader &reader, const char *id, size_t index, job::job_state &value)
    {
        std::string enum_string;
        reader.read_value(id, index, enum_string, 64);
        if (const auto enum_opt = job_state_enum.to_enum(enum_string)) {
            value = enum_opt.value();
        }
    }

    static any_type create_value(const job::job_state &value)
    {
        return job_state_enum.to_string(value);
    }

    static void bind_value(parameter_binder &binder, size_t index, job::job_state &value)
    {
        binder.bind(index, job_state_enum.to_string(value));
    }
};

template<>
struct matador::sql::data_type_traits<job::job_mode, void>
{
    inline static data_type_t builtin_type(std::size_t size)
    { return data_type_traits<std::string>::builtin_type(size); }

    static void read_value(query_result_reader &reader, const char *id, size_t index, job::job_mode &value)
    {
        std::string enum_string;
        reader.read_value(id, index, enum_string, 64);
        if (const auto enum_opt = job_mode_enum.to_enum(enum_string)) {
            value = enum_opt.value();
        }
    }

    static any_type create_value(const job::job_mode &value)
    {
        return job_mode_enum.to_string(value);
    }

    static void bind_value(parameter_binder &binder, size_t index, job::job_mode &value)
    {
        binder.bind(index, job_mode_enum.to_string(value));
    }
};

QUERY_HELPER( authors, id, first_name, last_name, date_of_birth, year_of_birth, distinguished )

QUERY_HELPER( books, id, author_id, title, published_in )

QUERY_HELPER( job, id, payload, type, description, state, mode )

QUERY_HELPER( payload, id )

QUERY_HELPER( temporary_table, id );

int main() {
    using namespace matador::sql;
    using namespace matador;

    const std::string env_var{"MATADOR_BACKENDS_PATH"};

    std::string dns{"sqlite://demo.db"};
    schema s( "main" );
    s.attach<author>( "authors" );
    s.attach<book>( "books" );

    connection c( dns );
    c.open();
    s.create( c );

    auto create_authors_sql = c.query( s )
                               .create()
                               .table<author>( qh::authors )
                               .execute();

    c.query( s )
     .create()
     .table<book>( qh::books )
     .execute();

    std::cout << "SQL: " << create_authors_sql << "\n";

    author mc;
    mc.id = 1;
    mc.first_name = "Michael";
    mc.last_name = "Crichton";
    mc.date_of_birth = "19.8.1954";
    mc.year_of_birth = 1954;
    mc.distinguished = true;
    auto insert_authors_sql = c.query( s )
                               .insert()
                               .into( qh::authors )
                               .values( mc )
                               .execute();

    std::cout << "SQL: " << insert_authors_sql << "\n";

    auto result = c.query( s )
                   .select( qh::authors.columns )
                   .from( qh::authors )
                   .fetch_all();

    for (const auto& row: result) { std::cout << "Author " << row.at( qh::authors.first_name ) << "\n"; }

    auto update_authors_sql = c.query( s )
                               .update( qh::authors )
                               .set( {{qh::authors.first_name, "Stephen"},
                                      {qh::authors.last_name, "King"}} )
                               .where( qh::authors.last_name == "Crichton" )
                               .execute();

    std::cout << "SQL: " << update_authors_sql << "\n";

    auto authors = c.query( s )
                    .select( qh::authors.columns )
                    .from( qh::authors )
                    .fetch_all<author>();

    for (const auto& a: authors) { std::cout << "Author " << a.first_name << "\n"; }

    c.query( s )
     .insert()
     .into( qh::books )
     .values( {2, "It", mc.id, 1980} )
     .execute();

    c.query( s )
     .insert()
     .into( qh::books )
     .values( {3, "Misery", mc.id, 1984} )
     .execute();

    auto select_books_sql = c.query( s )
                             .select( qh::books.columns, {qh::authors.last_name} )
                             .from( qh::books )
                             .join_left( qh::authors )
                             .on( qh::books.author_id == qh::authors.id )
                             .where( qh::books.published_in < 2008 && qh::authors.last_name == "King" )
                             .group_by( qh::books.published_in )
                             .order_by( qh::books.title ).asc()
                             .limit( 5 )
                             .offset( 2 )
                             .fetch_all();

    for (const auto& r: select_books_sql) { std::cout << "R: " << r.at( qh::books.title ) << ", " << r.at( qh::authors.last_name ) << "\n"; }
    // SELECT book.title, book.id, book.author_id, book.published_in, author.name
    // FROM book
    // INNER JOIN author ON book.author_id = author.id
    // WHERE book.published_in < 2008 AND author.name = "Michael Crichton"
    // ORDER BY "book.title" ASC
    // OFFSET 2 LIMIT 5

    c.query( s ).drop().table( qh::books ).execute();

    auto drop_authors_sql = c.query( s )
                             .drop()
                             .table( qh::authors )
                             .execute();

    std::cout << "SQL: " << drop_authors_sql << "\n";

    auto res = c.query( s )
                .select( {qh::payload.id} )
                .from( qh::payload )
                .join_left( qh::job )
                .on( qh::job.payload == qh::payload.id )
                .where(
                        in( qh::payload.id, c.query( s )
                                             .select( {qh::job.state} )
                                             .from( qh::job )
                                             .where( qh::job.state == job::job_state::Running )
                                ) &&
                        in( qh::payload.id, c.query( s )
                                             .select( {qh::temporary_table.id} )
                                             .from( qh::temporary_table ) )
                        )
                .build();
    // .fetch_value<unsigned long>();
    std::cout << "SQL: " << res.sql << "\n";

    return 0;
}