CenterControl_TCP/src/SqliteDB.cpp - master - yaseeeeechka/CenterControl

CenterControl_TCP/src/SqliteDB.cpp
265 строк8 KB
Yaroslava
New structure of Repo. TCP impl
11 дек 2025, 21:12
11 дек 2025, 21:126fdf9d7
#include <mutex>
#include <iostream>
#include <sstream>
#include <iomanip>
#include <string>

#include "SqliteDB.h"
#include "sha_256.h"

static sqlite3* g_db = nullptr;
static std::mutex g_db_mutex;

// ---------------- SHA-256 ----------------
std::string HashPassword(const std::string& password) {
    SHA256_CTX ctx;
    BYTE hash[32];  // SHA256 -> 32 байта
    sha256_init(&ctx);
    sha256_update(&ctx, reinterpret_cast<const BYTE*>(password.c_str()), password.size());
    sha256_final(&ctx, hash);

    std::stringstream ss;
    for (int i = 0; i < 32; ++i) {
        ss << std::hex << std::setw(2) << std::setfill('0') << (int)hash[i];
    }
    return ss.str();
}

// ---------------- Инициализация БД ----------------
bool InitDatabase() {
    std::lock_guard<std::mutex> lock(g_db_mutex);

    if (sqlite3_open("users.db", &g_db) != SQLITE_OK) {
        std::cerr << "[DB] Ошибка открытия БД: " << sqlite3_errmsg(g_db) << std::endl;
        return false;
    }
    char* errmsg = nullptr;

    const char* sql_create =
        "CREATE TABLE IF NOT EXISTS users ("
        "login TEXT PRIMARY KEY,"
        "password_hash TEXT NOT NULL"
        ");";

    if (sqlite3_exec(g_db, sql_create, nullptr, nullptr, &errmsg) != SQLITE_OK) {
        std::cerr << "[DB] Ошибка создания таблицы: " << errmsg << std::endl;
        sqlite3_free(errmsg);
        return false;
    }

    const char* sql_create_scores =
    "CREATE TABLE IF NOT EXISTS scores ("
    "login TEXT NOT NULL PRIMARY KEY,"
    "score INTEGER NOT NULL,"
    "created_at TEXT DEFAULT CURRENT_TIMESTAMP"
    ");";

    if (sqlite3_exec(g_db, sql_create_scores, nullptr, nullptr, &errmsg) != SQLITE_OK) {
        std::cerr << "[DB] Ошибка создания таблицы scores: " << errmsg << std::endl;
        sqlite3_free(errmsg);
        return false;
    }

    std::cout << "[DB] База и таблицы готовы.\n";
    return true;
}

void CloseDatabase() {
    std::lock_guard<std::mutex> lock(g_db_mutex);
    if (g_db) {
        sqlite3_close(g_db);
        g_db = nullptr;
    }
}

// ---------------- Регистрация ----------------
bool RegisterUser(const std::string& login, const std::string& password) {
    std::lock_guard<std::mutex> lock(g_db_mutex);
    if (!g_db) return false;

    const char* sql = "INSERT INTO users(login, password_hash) VALUES(?, ?);";
    sqlite3_stmt* stmt = nullptr;

    if (sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr) != SQLITE_OK) {
        std::cerr << "[DB] prepare() error: " << sqlite3_errmsg(g_db) << std::endl;
        return false;
    }

    std::string hash = HashPassword(password);

    sqlite3_bind_text(stmt, 1, login.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, hash.c_str(), -1, SQLITE_TRANSIENT);

    bool success = true;
    if (sqlite3_step(stmt) != SQLITE_DONE) {
        std::cerr << "[DB] Ошибка регистрации: " << sqlite3_errmsg(g_db) << std::endl;
        success = false;
    }

    sqlite3_finalize(stmt);
    return success;
}

// ---------------- Авторизация ----------------
bool AuthorizeUser(const std::string& login, const std::string& password) {
    std::lock_guard<std::mutex> lock(g_db_mutex);
    if (!g_db) return false;

    const char* sql = "SELECT password_hash FROM users WHERE login = ?;";
    sqlite3_stmt* stmt = nullptr;

    if (sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr) != SQLITE_OK) {
        std::cerr << "[DB] prepare() error: " << sqlite3_errmsg(g_db) << std::endl;
        return false;
    }

    sqlite3_bind_text(stmt, 1, login.c_str(), -1, SQLITE_TRANSIENT);

    bool authorized = false;
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        const unsigned char* db_hash = sqlite3_column_text(stmt, 0);
        if (db_hash) {
            std::string hash = HashPassword(password);
            if (hash == reinterpret_cast<const char*>(db_hash)) {
                authorized = true;
            }
        }
    }

    sqlite3_finalize(stmt);
    return authorized;
}

bool SaveUserScore(const std::string& login, int score) {
    if (score <= 0) return true; // можно не писать нулевые результаты

    std::lock_guard<std::mutex> lock(g_db_mutex);
    if (!g_db) return false;

    // 1. читаем старый score (если был)
    int oldScore = 0;
    bool hasOld = false;

    {
        const char* sqlSel = "SELECT score FROM scores WHERE login = ?;";
        sqlite3_stmt* stmt = nullptr;
        if (sqlite3_prepare_v2(g_db, sqlSel, -1, &stmt, nullptr) == SQLITE_OK) {
            sqlite3_bind_text(stmt, 1, login.c_str(), -1, SQLITE_TRANSIENT);
            if (sqlite3_step(stmt) == SQLITE_ROW) {
                oldScore = sqlite3_column_int(stmt, 0);
                hasOld = true;
            }
        } else {
            std::cerr << "[DB] prepare() error (select score): "
                      << sqlite3_errmsg(g_db) << std::endl;
        }
        if (stmt) sqlite3_finalize(stmt);
    }

    {
        const char* sql =
            "INSERT INTO scores(login, score) "
            "VALUES(?, ?) "
            "ON CONFLICT(login) DO UPDATE "
            "SET score = MAX(score, excluded.score);";

        sqlite3_stmt* stmt = nullptr;

        if (sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr) != SQLITE_OK) {
            std::cerr << "[DB] prepare() error (scores): " << sqlite3_errmsg(g_db) << std::endl;
            return false;
        }

        sqlite3_bind_text(stmt, 1, login.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_int(stmt, 2, score);

        bool ok = (sqlite3_step(stmt) == SQLITE_DONE);
        if (!ok) {
            std::cerr << "[DB] Ошибка записи очков: " << sqlite3_errmsg(g_db) << std::endl;
        }

        sqlite3_finalize(stmt);
    }
    return true;
}

std::vector<ScoreDTO> GetTopScores(int limit) {
    std::vector<ScoreDTO> out;
    if (limit <= 0) {
        limit = 100;
    }

    sqlite3_stmt *stmt = nullptr;
    const char *sql =
        "SELECT login, score, created_at "
        "FROM scores "
        "ORDER BY score DESC, created_at ASC "
        "LIMIT ?;";

    int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) {
        // можно залогировать ошибку
        return out;
    }

    sqlite3_bind_int(stmt, 1, limit);

    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        const unsigned char *u = sqlite3_column_text(stmt, 0);
        int sc = sqlite3_column_int(stmt, 1);
        const unsigned char *dt = sqlite3_column_text(stmt, 2);

        ScoreDTO row;
        row.user = u ? reinterpret_cast<const char*>(u) : "";
        row.score = sc;
        row.datetime = dt ? reinterpret_cast<const char*>(dt) : "";

        out.push_back(std::move(row));
    }

    sqlite3_finalize(stmt);
    return out;
}

int GetUserStars(const std::string& login) {
    std::lock_guard<std::mutex> lock(g_db_mutex);
    if (!g_db) return 0;

    // Берём личный максимальный счёт игрока
    const char* sql =
        "SELECT score FROM scores WHERE login = ?;";

    sqlite3_stmt* stmt = nullptr;
    int bestScore = 0;
    bool hasScore = false;

    if (sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr) != SQLITE_OK) {
        std::cerr << "[DB] prepare() error (GetUserStars): "
                  << sqlite3_errmsg(g_db) << std::endl;
        if (stmt) sqlite3_finalize(stmt);
        return 0;
    }

    sqlite3_bind_text(stmt, 1, login.c_str(), -1, SQLITE_TRANSIENT);

    if (sqlite3_step(stmt) == SQLITE_ROW) {
        bestScore = sqlite3_column_int(stmt, 0);
        hasScore = true;
    }

    sqlite3_finalize(stmt);

    if (!hasScore) {
        return 0;
    }

    // Маппинг: 30 → 1 звезда, 100 → 2, 200 → 3
    if (bestScore >= 200) {
        return 3;
    } else if (bestScore >= 100) {
        return 2;
    } else if (bestScore >= 30) {
        return 1;
    }
    return 0;
}