#include <format>
#include <memory>
#include <string>
#include <expected>

#include <mw/database.hpp>
#include <mw/error.hpp>
#include <mw/utils.hpp>
#include <utility>

#include "data.hpp"

// id, owner_id, parent_id, name, url, desc, visibility, time.
#define LINK_TUPLE_TYPES int64_t,int64_t,int64_t,std::string,std::string,std::string,int,int64_t

namespace
{
using LinkTuple = std::tuple<LINK_TUPLE_TYPES>;
LinkItem linkFromTuple(const LinkTuple& t)
{
    LinkItem l;
    l.id = std::get<0>(t);
    l.owner_id = std::get<1>(t);
    int64_t p = std::get<2>(t);
    if(p == 0)
    {
        l.parent_id = std::nullopt;
    }
    else
    {
        l.parent_id = p;
    }
    l.name = std::get<3>(t);
    l.url = std::get<4>(t);
    l.description = std::get<5>(t);
    l.visibility = static_cast<LinkItem::Visibility>(std::get<6>(t));
    l.time = mw::secondsToTime(std::get<7>(t));
    return l;
}

} // namespace

std::string LinkItem::visibilityToStr(Visibility v)
{
    switch(v)
    {
    case PUBLIC:
        return "public";
    case PRIVATE:
        return "private";
    }
    std::unreachable();
}

mw::E<std::unique_ptr<DataSourceSQLite>>
DataSourceSQLite::fromFile(const std::string& db_file)
{
    auto data_source = std::make_unique<DataSourceSQLite>();
    ASSIGN_OR_RETURN(data_source->db, mw::SQLite::connectFile(db_file));
    DO_OR_RETURN(data_source->db->execute("PRAGMA foreign_keys = ON;"));
    // Perform schema upgrade here.
    //
    // data_source->upgradeSchema1To2();

    // Update this line when schema updates.
    DO_OR_RETURN(data_source->setSchemaVersion(1));
    DO_OR_RETURN(data_source->db->execute(
        "CREATE TABLE IF NOT EXISTS Users "
        "(id INTEGER PRIMARY KEY, openid_uid TEXT UNIQUE, name TEXT UNIQUE);"));
    DO_OR_RETURN(data_source->db->execute(
        "CREATE TABLE IF NOT EXISTS LinkItems "
        "(id INTEGER PRIMARY KEY, owner_id INTEGER NOT NULL,"
        " parent_id INTEGER,"
        " name TEXT NOT NULL, url TEXT, description TEXT,"
        " visibility INTEGER NOT NULL, time INTEGER NOT NULL,"
        " FOREIGN KEY(owner_id) REFERENCES Users(id),"
        " FOREIGN KEY(parent_id) REFERENCES LinkItems(id));"));
    return data_source;
}

mw::E<std::unique_ptr<DataSourceSQLite>> DataSourceSQLite::newFromMemory()
{
    return fromFile(":memory:");
}

mw::E<int64_t> DataSourceSQLite::getSchemaVersion() const
{
    return db->evalToValue<int64_t>("PRAGMA user_version;");
}

mw::E<void> DataSourceSQLite::setSchemaVersion(int64_t v) const
{
    return db->execute(std::format("PRAGMA user_version = {};", v));
}

mw::E<std::optional<User>>
DataSourceSQLite::userByOpenIDUID(const std::string& uid) const
{
    if(db == nullptr)
    {
        return std::unexpected(mw::runtimeError("Database is not connected."));
    }
    ASSIGN_OR_RETURN(mw::SQLiteStatement sql, db->statementFromStr(
        "SELECT id, openid_uid, name FROM Users WHERE openid_uid = ?;"));
    DO_OR_RETURN(sql.bind(uid));
    ASSIGN_OR_RETURN(auto users, (db->eval<int64_t, std::string, std::string>(
        std::move(sql))));
    if(users.empty())
    {
        return std::nullopt;
    }
    if(users.size() > 1)
    {
        return std::unexpected(mw::runtimeError("Found duplicated users"));
    }
    User u;
    u.id = std::get<0>(users[0]);
    u.openid_uid = std::get<1>(users[0]);
    u.name = std::get<2>(users[0]);
    return u;
}

mw::E<int64_t> DataSourceSQLite::addUser(User&& u) const
{
    if(db == nullptr)
    {
        return std::unexpected(mw::runtimeError("Database is not connected."));
    }
    ASSIGN_OR_RETURN(mw::SQLiteStatement sql, db->statementFromStr(
        "INSERT INTO Users (id, openid_uid, name) VALUES (NULL, ?, ?)"));
    DO_OR_RETURN(sql.bind(u.openid_uid, u.name));
    DO_OR_RETURN(db->execute(std::move(sql)));
    return db->lastInsertRowID();
}

mw::E<std::optional<User>> DataSourceSQLite::userByID(const int64_t id) const
{
    if(db == nullptr)
    {
        return std::unexpected(mw::runtimeError("Database is not connected."));
    }
    ASSIGN_OR_RETURN(mw::SQLiteStatement sql, db->statementFromStr(
        "SELECT id, openid_uid, name FROM Users WHERE id = ?;"));
    DO_OR_RETURN(sql.bind(id));
    ASSIGN_OR_RETURN(auto users, (db->eval<int64_t, std::string, std::string>(
        std::move(sql))));
    if(users.empty())
    {
        return std::nullopt;
    }
    if(users.size() > 1)
    {
        return std::unexpected(mw::runtimeError("Found duplicated users"));
    }
    User u;
    u.id = std::get<0>(users[0]);
    u.openid_uid = std::get<1>(users[0]);
    u.name = std::get<2>(users[0]);
    return u;
}

mw::E<std::optional<User>> DataSourceSQLite::userByName(const std::string& name)
    const
{
    if(db == nullptr)
    {
        return std::unexpected(mw::runtimeError("Database is not connected."));
    }
    ASSIGN_OR_RETURN(mw::SQLiteStatement sql, db->statementFromStr(
        "SELECT id, openid_uid, name FROM Users WHERE name = ?;"));
    DO_OR_RETURN(sql.bind(name));
    ASSIGN_OR_RETURN(auto users, (db->eval<int64_t, std::string, std::string>(
        std::move(sql))));
    if(users.empty())
    {
        return std::nullopt;
    }
    if(users.size() > 1)
    {
        return std::unexpected(mw::runtimeError("Found duplicated users"));
    }
    User u;
    u.id = std::get<0>(users[0]);
    u.openid_uid = std::get<1>(users[0]);
    u.name = std::get<2>(users[0]);
    return u;
}

mw::E<std::optional<LinkItem>> DataSourceSQLite::itemByID(int64_t id) const
{
    if(db == nullptr)
    {
        return std::unexpected(mw::runtimeError("Database is not connected."));
    }
    std::vector<LinkTuple> links;
    // Querying root-level links.
    ASSIGN_OR_RETURN(mw::SQLiteStatement stat, db->statementFromStr(
        "SELECT id, owner_id, parent_id, name, url, description, visibility,"
        " time from LinkItems WHERE id = ?;"));
    DO_OR_RETURN(stat.bind(id));
    ASSIGN_OR_RETURN(links, (db->eval<LINK_TUPLE_TYPES>(std::move(stat))));
    if(links.empty())
    {
        return std::nullopt;
    }
    if(links.size() > 1)
    {
        return std::unexpected(mw::runtimeError("Duplicate item ID"));
    }

    return linkFromTuple(links[0]);
}

mw::E<std::vector<LinkItem>> DataSourceSQLite::itemsByParent(int64_t parent)
    const
{
    if(db == nullptr)
    {
        return std::unexpected(mw::runtimeError("Database is not connected."));
    }
    std::vector<LinkTuple> links;
    // Querying root-level links.
    ASSIGN_OR_RETURN(mw::SQLiteStatement stat, db->statementFromStr(
        "SELECT id, owner_id, parent_id, name, url, description, visibility,"
        " time from LinkItems WHERE parent_id = ?;"));
    DO_OR_RETURN(stat.bind(parent));
    ASSIGN_OR_RETURN(links, (db->eval<LINK_TUPLE_TYPES>(std::move(stat))));
    std::vector<LinkItem> result;
    for(const LinkTuple& t : links)
    {
        result.push_back(linkFromTuple(t));
    }
    return result;
}

mw::E<std::vector<LinkItem>>
DataSourceSQLite::itemsTopLevelByUser(int64_t user_id) const
{
    if(db == nullptr)
    {
        return std::unexpected(mw::runtimeError("Database is not connected."));
    }
    std::vector<LinkTuple> links;
    // Querying root-level links.
    ASSIGN_OR_RETURN(mw::SQLiteStatement stat, db->statementFromStr(
        "SELECT id, owner_id, parent_id, name, url, description, visibility,"
        " time from LinkItems WHERE parent_id IS NULL AND owner_id = ?;"));
    DO_OR_RETURN(stat.bind(user_id));
    ASSIGN_OR_RETURN(links, (db->eval<LINK_TUPLE_TYPES>(std::move(stat))));
    std::vector<LinkItem> result;
    for(const LinkTuple& t : links)
    {
        result.push_back(linkFromTuple(t));
    }
    return result;
}

mw::E<int64_t> DataSourceSQLite::addLink(LinkItem&& link) const
{
    if(db == nullptr)
    {
        return std::unexpected(mw::runtimeError("Database is not connected."));
    }
    ASSIGN_OR_RETURN(mw::SQLiteStatement sql, db->statementFromStr(
        "INSERT INTO LinkItems (id, owner_id, parent_id, name, url,"
        " description, visibility, time) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"));
    if(link.parent_id.has_value())
    {
        DO_OR_RETURN(sql.bind(
            std::nullopt, link.owner_id, *link.parent_id, link.name, link.url,
            link.description, static_cast<int>(link.visibility),
            mw::timeToSeconds(mw::Clock::now())));
    }
    else
    {
        DO_OR_RETURN(sql.bind(
            std::nullopt, link.owner_id, std::nullopt, link.name, link.url,
            link.description, static_cast<int>(link.visibility),
            mw::timeToSeconds(mw::Clock::now())));
    }

    DO_OR_RETURN(db->execute(std::move(sql)));
    return db->lastInsertRowID();
}