brandling-safari/server/Database.cpp

#include "Database.h"

#include <nlohmann/json.hpp>
#include <sqlite3.h>

#include <cstdio>
#include <filesystem>

namespace bs {

namespace {

const char* kSchema = R"SQL(
CREATE TABLE IF NOT EXISTS players(
    id INTEGER PRIMARY KEY,
    token TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    appearance TEXT NOT NULL,
    pos_x REAL NOT NULL,
    pos_z REAL NOT NULL,
    yaw REAL NOT NULL DEFAULT 0,
    capsules INTEGER NOT NULL DEFAULT 0,
    intro_done INTEGER NOT NULL DEFAULT 0,
    created_at INTEGER NOT NULL,
    last_seen INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS creatures(
    uid INTEGER PRIMARY KEY AUTOINCREMENT,
    owner INTEGER NOT NULL REFERENCES players(id),
    species INTEGER NOT NULL,
    caught_at INTEGER NOT NULL,
    in_party INTEGER NOT NULL DEFAULT 0);
CREATE INDEX IF NOT EXISTS creatures_owner ON creatures(owner);
CREATE TABLE IF NOT EXISTS dex(
    owner INTEGER NOT NULL REFERENCES players(id),
    species INTEGER NOT NULL,
    seen INTEGER NOT NULL DEFAULT 0,
    caught INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY(owner, species));
CREATE TABLE IF NOT EXISTS world(
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL);
)SQL";

std::string text(sqlite3_stmt* s, int col) {
    const unsigned char* t = sqlite3_column_text(s, col);
    return t ? reinterpret_cast<const char*>(t) : "";
}

void bindText(sqlite3_stmt* s, int i, const std::string& v) {
    sqlite3_bind_text(s, i, v.data(), int(v.size()), SQLITE_TRANSIENT);
}

}  // namespace

std::string appearanceToJson(const Appearance& a) {
    nlohmann::json j;
    j["body"] = a.body;
    j["skin"] = a.skin;
    nlohmann::json items = nlohmann::json::object(), colors = nlohmann::json::object();
    for (int s = 0; s < kSlotCount; ++s) {
        items[slotName(s)] = a.items[s];
        colors[slotName(s)] = hexColor(a.colors[s]);
    }
    j["items"] = items;
    j["colors"] = colors;
    return j.dump();
}

Appearance appearanceFromJson(const std::string& s) {
    Appearance a;
    try {
        nlohmann::json j = nlohmann::json::parse(s);
        a.body = j.value("body", a.body);
        a.skin = uint8_t(j.value("skin", 0));
        for (int i = 0; i < kSlotCount; ++i) {
            if (j.contains("items")) a.items[i] = j["items"].value(slotName(i), "");
            if (j.contains("colors")) a.colors[i] = parseHexColor(j["colors"].value(slotName(i), "#ffffff"));
        }
    } catch (const std::exception&) {
    }
    return a;
}

Database::~Database() { close(); }

bool Database::open(const std::string& path, std::string* error) {
    std::error_code ec;
    std::filesystem::path p(path);
    if (p.has_parent_path()) std::filesystem::create_directories(p.parent_path(), ec);
    if (sqlite3_open(path.c_str(), &db_) != SQLITE_OK) {
        if (error) *error = db_ ? sqlite3_errmsg(db_) : "sqlite3_open failed";
        close();
        return false;
    }
    sqlite3_busy_timeout(db_, 2000);
    exec("PRAGMA journal_mode=WAL;");
    exec("PRAGMA synchronous=NORMAL;");
    exec("PRAGMA foreign_keys=ON;");
    if (!exec(kSchema)) {
        if (error) *error = sqlite3_errmsg(db_);
        close();
        return false;
    }
    return true;
}

void Database::close() {
    for (auto& [sql, s] : stmts_) sqlite3_finalize(s);
    stmts_.clear();
    if (db_) sqlite3_close(db_);
    db_ = nullptr;
}

bool Database::exec(const char* sql) {
    char* err = nullptr;
    if (sqlite3_exec(db_, sql, nullptr, nullptr, &err) != SQLITE_OK) {
        std::fprintf(stderr, "[db] %s\n", err ? err : "error");
        sqlite3_free(err);
        return false;
    }
    return true;
}

sqlite3_stmt* Database::prepare(const char* sql) {
    if (!db_) return nullptr;
    auto it = stmts_.find(sql);
    if (it != stmts_.end()) {
        sqlite3_reset(it->second);
        sqlite3_clear_bindings(it->second);
        return it->second;
    }
    sqlite3_stmt* s = nullptr;
    if (sqlite3_prepare_v2(db_, sql, -1, &s, nullptr) != SQLITE_OK) {
        std::fprintf(stderr, "[db] prepare failed: %s\n", sqlite3_errmsg(db_));
        return nullptr;
    }
    stmts_[sql] = s;
    return s;
}

void Database::begin() {
    if (db_) exec("BEGIN;");
}

void Database::commit() {
    if (db_) exec("COMMIT;");
}

bool Database::loadPlayer(const std::string& token, PlayerRow& out) {
    sqlite3_stmt* s = prepare(
        "SELECT id, name, appearance, pos_x, pos_z, yaw, capsules, intro_done, created_at, last_seen "
        "FROM players WHERE token = ?");
    if (!s) return false;
    bindText(s, 1, token);
    bool found = sqlite3_step(s) == SQLITE_ROW;
    if (found) {
        out.id = sqlite3_column_int64(s, 0);
        out.token = token;
        out.name = text(s, 1);
        out.look = appearanceFromJson(text(s, 2));
        out.x = float(sqlite3_column_double(s, 3));
        out.z = float(sqlite3_column_double(s, 4));
        out.yaw = float(sqlite3_column_double(s, 5));
        out.capsules = sqlite3_column_int(s, 6);
        out.introDone = sqlite3_column_int(s, 7) != 0;
        out.createdAt = sqlite3_column_int64(s, 8);
        out.lastSeen = sqlite3_column_int64(s, 9);
    }
    sqlite3_reset(s);
    return found;
}

int64_t Database::createPlayer(const PlayerRow& r) {
    sqlite3_stmt* s = prepare(
        "INSERT INTO players(token, name, appearance, pos_x, pos_z, yaw, capsules, intro_done, created_at, last_seen) "
        "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    if (!s) return 0;
    bindText(s, 1, r.token);
    bindText(s, 2, r.name);
    bindText(s, 3, appearanceToJson(r.look));
    sqlite3_bind_double(s, 4, r.x);
    sqlite3_bind_double(s, 5, r.z);
    sqlite3_bind_double(s, 6, r.yaw);
    sqlite3_bind_int(s, 7, r.capsules);
    sqlite3_bind_int(s, 8, r.introDone);
    sqlite3_bind_int64(s, 9, r.createdAt);
    sqlite3_bind_int64(s, 10, r.lastSeen);
    int64_t id = 0;
    if (sqlite3_step(s) == SQLITE_DONE) id = sqlite3_last_insert_rowid(db_);
    else std::fprintf(stderr, "[db] createPlayer: %s\n", sqlite3_errmsg(db_));
    sqlite3_reset(s);
    return id;
}

void Database::savePlayer(const PlayerRow& r) {
    sqlite3_stmt* s = prepare(
        "UPDATE players SET name = ?, appearance = ?, pos_x = ?, pos_z = ?, yaw = ?, capsules = ?, intro_done = ?, "
        "last_seen = ? WHERE id = ?");
    if (!s) return;
    bindText(s, 1, r.name);
    bindText(s, 2, appearanceToJson(r.look));
    sqlite3_bind_double(s, 3, r.x);
    sqlite3_bind_double(s, 4, r.z);
    sqlite3_bind_double(s, 5, r.yaw);
    sqlite3_bind_int(s, 6, r.capsules);
    sqlite3_bind_int(s, 7, r.introDone);
    sqlite3_bind_int64(s, 8, r.lastSeen);
    sqlite3_bind_int64(s, 9, r.id);
    if (sqlite3_step(s) != SQLITE_DONE) std::fprintf(stderr, "[db] savePlayer: %s\n", sqlite3_errmsg(db_));
    sqlite3_reset(s);
}

void Database::savePosition(int64_t id, float x, float z, float yaw) {
    sqlite3_stmt* s = prepare("UPDATE players SET pos_x = ?, pos_z = ?, yaw = ?, last_seen = strftime('%s','now') WHERE id = ?");
    if (!s) return;
    sqlite3_bind_double(s, 1, x);
    sqlite3_bind_double(s, 2, z);
    sqlite3_bind_double(s, 3, yaw);
    sqlite3_bind_int64(s, 4, id);
    sqlite3_step(s);
    sqlite3_reset(s);
}

std::vector<OwnedCreature> Database::loadCreatures(int64_t owner) {
    std::vector<OwnedCreature> out;
    sqlite3_stmt* s = prepare("SELECT uid, species, in_party, caught_at FROM creatures WHERE owner = ? ORDER BY uid");
    if (!s) return out;
    sqlite3_bind_int64(s, 1, owner);
    while (sqlite3_step(s) == SQLITE_ROW) {
        OwnedCreature c;
        c.uid = uint32_t(sqlite3_column_int64(s, 0));
        c.species = uint16_t(sqlite3_column_int(s, 1));
        c.inParty = sqlite3_column_int(s, 2) != 0;
        c.caughtAt = sqlite3_column_int64(s, 3);
        out.push_back(c);
    }
    sqlite3_reset(s);
    return out;
}

uint32_t Database::insertCreature(int64_t owner, uint16_t species, int64_t caughtAt, bool inParty) {
    sqlite3_stmt* s = prepare("INSERT INTO creatures(owner, species, caught_at, in_party) VALUES(?, ?, ?, ?)");
    if (!s) return 0;
    sqlite3_bind_int64(s, 1, owner);
    sqlite3_bind_int(s, 2, species);
    sqlite3_bind_int64(s, 3, caughtAt);
    sqlite3_bind_int(s, 4, inParty);
    uint32_t uid = 0;
    if (sqlite3_step(s) == SQLITE_DONE) uid = uint32_t(sqlite3_last_insert_rowid(db_));
    else std::fprintf(stderr, "[db] insertCreature: %s\n", sqlite3_errmsg(db_));
    sqlite3_reset(s);
    return uid;
}

void Database::setInParty(uint32_t uid, bool inParty) {
    sqlite3_stmt* s = prepare("UPDATE creatures SET in_party = ? WHERE uid = ?");
    if (!s) return;
    sqlite3_bind_int(s, 1, inParty);
    sqlite3_bind_int64(s, 2, uid);
    sqlite3_step(s);
    sqlite3_reset(s);
}

void Database::loadDex(int64_t owner, std::set<uint16_t>& seen, std::set<uint16_t>& caught) {
    seen.clear();
    caught.clear();
    sqlite3_stmt* s = prepare("SELECT species, seen, caught FROM dex WHERE owner = ?");
    if (!s) return;
    sqlite3_bind_int64(s, 1, owner);
    while (sqlite3_step(s) == SQLITE_ROW) {
        uint16_t sp = uint16_t(sqlite3_column_int(s, 0));
        if (sqlite3_column_int(s, 1)) seen.insert(sp);
        if (sqlite3_column_int(s, 2)) caught.insert(sp);
    }
    sqlite3_reset(s);
}

void Database::markSeen(int64_t owner, uint16_t species) {
    sqlite3_stmt* s = prepare(
        "INSERT INTO dex(owner, species, seen, caught) VALUES(?, ?, 1, 0) "
        "ON CONFLICT(owner, species) DO UPDATE SET seen = 1");
    if (!s) return;
    sqlite3_bind_int64(s, 1, owner);
    sqlite3_bind_int(s, 2, species);
    sqlite3_step(s);
    sqlite3_reset(s);
}

void Database::markCaught(int64_t owner, uint16_t species) {
    sqlite3_stmt* s = prepare(
        "INSERT INTO dex(owner, species, seen, caught) VALUES(?, ?, 1, 1) "
        "ON CONFLICT(owner, species) DO UPDATE SET seen = 1, caught = 1");
    if (!s) return;
    sqlite3_bind_int64(s, 1, owner);
    sqlite3_bind_int(s, 2, species);
    sqlite3_step(s);
    sqlite3_reset(s);
}

bool Database::getWorld(const std::string& key, std::string& value) {
    sqlite3_stmt* s = prepare("SELECT value FROM world WHERE key = ?");
    if (!s) return false;
    bindText(s, 1, key);
    bool found = sqlite3_step(s) == SQLITE_ROW;
    if (found) value = text(s, 0);
    sqlite3_reset(s);
    return found;
}

void Database::setWorld(const std::string& key, const std::string& value) {
    sqlite3_stmt* s = prepare(
        "INSERT INTO world(key, value) VALUES(?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value");
    if (!s) return;
    bindText(s, 1, key);
    bindText(s, 2, value);
    sqlite3_step(s);
    sqlite3_reset(s);
}

}  // namespace bs