DataBase.cpp

//File:
//DataBase.cpp

#include "DataBase.h"


DataBase::DataBase()
{
    //resetDataBase();
    int rc;
    char* errMsg;

    rc = sqlite3_open(DB_FILENAME, &_db);
    if (rc)
    {
        string error = string("Can't open database: ") + sqlite3_errmsg(_db);
        sqlite3_close(_db);
        throw exception(error.c_str());
    }
    string sql = "select count(*) from t_games;";
    rc = sqlite3_exec(_db, sql.c_str(), callbackCount, &currGameID, &errMsg);
    if (rc != SQLITE_OK)
    {
        string error = string("SQL error: ") + sqlite3_errmsg(_db);
        sqlite3_free(errMsg);
        throw exception(error.c_str());
    }
}


DataBase::~DataBase()
{
    sqlite3_close(_db);
}

bool DataBase::isUserExists(string username)
{
    unordered_map<string, vector<string>> results;
    char* errMsg;
    string sql = "select username from t_users where username = \"" + username + "\";";
    int rc = sqlite3_exec(_db, sql.c_str(), callbackPutIntoResults, &results, &errMsg);
    if (rc != SQLITE_OK)
    {
        string error = string("SQL error: ") + sqlite3_errmsg(_db);
        sqlite3_free(errMsg);
        throw exception(error.c_str());
    }
    return results.size();
}

bool DataBase::addNewUser(string username, string password, string email)
{
    char* errMsg;
    string sql = "insert into t_users (username,password,email) values (\"" + username + "\",\"" + password + "\",\"" + email + "\");";
    int rc = sqlite3_exec(_db,sql.c_str() , nullptr, nullptr, &errMsg);
    if (rc != SQLITE_OK)
    {
        sqlite3_free(errMsg);
        return false;
    }
    return true;
}

bool DataBase::isUserAndPassMatch(string username, string password)
{
    /*if (!isUserExists(username))
        return false;  //Useless condition(?) */
    unordered_map<string, vector<string>> results;
    char* errMsg;
    string sql = "select username,password from t_users where username = \"" + username + "\" and password = \"" + password + "\";";
    int rc = sqlite3_exec(_db, sql.c_str(), callbackPutIntoResults, &results, &errMsg);
    if (rc != SQLITE_OK)
    {
        string error = string("SQL error: ") + sqlite3_errmsg(_db);
        sqlite3_free(errMsg);
        throw exception(error.c_str());
    }
    return results.size();
}

vector<Question*> DataBase::initQuestions(int questionNo)
{
    vector<Question*> ans;
    char *errMsg;
    string sql = "select * from t_questions order by random() limit "+to_string(questionNo)+";";
    int rc = sqlite3_exec(_db, sql.c_str(), callbackQuestions, &ans , &errMsg);
    if (rc != SQLITE_OK)
    {
        string error = string("SQL error: ") + sqlite3_errmsg(_db);
        sqlite3_free(errMsg);
        throw exception(error.c_str());
    }
    return ans;
}

vector<string> DataBase::getBestScores()
{
    char *errMsg;
    string sql = "select username,sum(is_correct) as score from t_players_answers where is_correct = 1 group by username order by score desc limit 3;";
    vector<string> ans;
    int rc = sqlite3_exec(_db, sql.c_str(), callBackScoresAndStatus, &ans , &errMsg);
    if (rc != SQLITE_OK)
    {
        string error = string("SQL error: ") + sqlite3_errmsg(_db);
        sqlite3_free(errMsg);
        throw exception(error.c_str());
    }
    return ans;
}

vector<string> DataBase::getPersonalStatus(string username)
{
    char *errMsg;
    string sql = "select count(distinct game_id) as NumOfGames ,sum(is_correct) as NumOfRightAnswers ,count(username)-sum(is_correct) as NumOfWrongAnswers ,avg(answer_time) as AvgTimeForAnswer from t_players_answers where username = \"" + username + "\";";
    vector<string> ans;
    int rc = sqlite3_exec(_db, sql.c_str(), callBackScoresAndStatus, &ans, &errMsg);
    if (rc != SQLITE_OK)
    {
        string error = string("SQL error: ") + sqlite3_errmsg(_db);
        sqlite3_free(errMsg);
        throw exception(error.c_str());
    }
    return ans;
}

int DataBase::insertNewGame()
{
    cout <<"CurrGameID: " <<  currGameID << endl;
    char* errMsg;
    string sql = "insert into t_games (game_id, status, start_time) values ("+to_string(currGameID)+",0,\""+getTime()+"\");";
    cout << "command:" << endl << sql << endl;
    int rc = sqlite3_exec(_db, sql.c_str(), nullptr, nullptr, &errMsg);
    if (rc != SQLITE_OK && rc != SQLITE_DONE)
    {

        cout << "SQLError: " << rc << endl;
        string error = string("SQL error: ") + sqlite3_errmsg(_db);
        cout << "Error: " << endl << error << endl;
        sqlite3_free(errMsg);
        throw exception(error.c_str());
    }
    return currGameID++;
}

bool DataBase::updateGameStatus(int gameID)
{
    char* errMsg;
    string sql = "update t_games set status = 1, end_time = " + getTime() + " where game_id = " + to_string(gameID) + ";";
    int rc = sqlite3_exec(_db, sql.c_str(), nullptr, nullptr, &errMsg);
    if (rc != SQLITE_OK)
    {
        sqlite3_free(errMsg);
        return false;
    }
    return true;
}

bool DataBase::addAnswerToPlayer(int gameID, string username, int questionID, string answer, bool isCorrect, int answerTime)
{
    char* errMsg;
    string sql = "insert into t_players_answers (game_id,username,question_id,player_answer,is_correct,answer_time) values (" + to_string(gameID) + ",\"" + username + "\"," + to_string(questionID) + ",\"" + answer + "\"," + to_string(isCorrect) + "," + to_string(answerTime) + ");";
    int rc = sqlite3_exec(_db, sql.c_str(), nullptr, nullptr, &errMsg);
    if (rc != SQLITE_OK)
    {
        sqlite3_free(errMsg);
        return false;
    }
    return true;
}



string DataBase::getTime()
{
    time_t rawtime;
    struct tm * timeinfo = new struct tm();
    char buffer[80];

    time(&rawtime);
    //timeinfo = localtime(&rawtime);
    localtime_s(timeinfo, &rawtime);

    strftime(buffer, sizeof(buffer), "%Y-%m-%d %H:%M:%S", timeinfo);
    string str(buffer);
    return str;
}

int DataBase::callbackPutIntoResults(void * _results, int argc, char ** argv, char ** azCol)
{
    unordered_map<string, vector<string>> *results = (unordered_map<string, vector<string>>*) _results;
    for (int i = 0; i < argc; i++)
    {
        auto it = results->find(azCol[i]);
        if (it != results->end())
        {
            it->second.push_back(argv[i]);
        }
        else
        {
            pair<string, vector<string>> p;
            p.first = azCol[i];
            p.second.push_back(argv[i]);
            results->insert(p);
        }
    }
    return 0;
}

int DataBase::callbackQuestions(void * ans, int argc, char ** argv, char ** azCol)
{
    ((vector<Question*>*)ans)->push_back(new Question(atoi(argv[0]),argv[1],argv[2],argv[3],argv[4],argv[5]));
    return 0;
}

int DataBase::callBackScoresAndStatus(void * ans, int argc, char ** argv, char ** azCol)
{
    if (argv[0] == string("0"))
        for (int i = 0; i < 4; i++)
            ((vector<string>*)ans)->push_back("0");
    else
        for (int i = 0; i < argc; i++)
            ((vector<string>*)ans)->push_back(argv[i]);
    return 0;
}

int DataBase::callbackCount(void * ans, int argc, char ** argv, char ** azCol)
{
    *(int*)ans = atoi(argv[0]) + 1;
    return 0;
}