NarKampen/Assets/Scripts/Database/Database.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using Mono.Data.Sqlite;
using UnityEngine;
using UnityEngine.Networking;

public class Database : MonoBehaviour
{
    private const string onlineQuestionsUrl = "narkampen.nordh.xyz/narKampen/dbFiles/Question.php"; // TODO update
    string connectionType;
    string databaseUrl;
    string gameMode;
    int winAmount = -1;
    int questionTimer = -1;

    public GameObject questionCardPrefab;

    private static Database instance;
    public static Database Instance
    {
        get
        {
            if (instance == null)
            {
                instance = GameObject.FindObjectOfType<Database>();
                if (instance == null)
                {
                    GameObject container = new GameObject("Database");
                    instance = container.AddComponent<Database>();
                }
            }
            return instance;
        }
    }

    IDbConnection conn;

    internal void GetCategories(List<CategoryPanel.Category> list, string gameMode, int gameId)
    {
        if (gameMode.Equals(Constants.ONLINE))
        {
            OnlineDatabase.Instance.GetCategories(list, gameId);
        }
        else
        {
            //string sql = "SELECT name, r, g, b, a, id FROM category";
            string sql = "SELECT category.*, count(*) as count FROM questions " +
                            "INNER JOIN questionToCategory ON questions.id = questionToCategory.questionId " +
                            "INNER JOIN category ON questionToCategory.categoryId = category.id " +
                            "GROUP BY category.name";
            IDbCommand cmd = GetConnection();
            cmd.CommandText = sql;

            IDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                CategoryPanel.Category cat = new CategoryPanel.Category();

                byte r = (byte)reader.GetInt32(2);
                byte g = (byte)reader.GetInt32(3);
                byte b = (byte)reader.GetInt32(4);
                byte a = (byte)reader.GetInt32(5);
                cat.color = new Color32(r, g, b, a);
                cat.name = reader.GetString(1);
                cat.id = reader.GetInt32(0);
                cat.questionCount = reader.GetInt32(6);

                list.Add(cat);
            }
            CloseConnection();
        }
    }

    [Serializable]
    public class Question
    {
        public string question;
        public string answer;
        public string id;
        public string category;
    }

    [Serializable]
    public class Questions
    {
        public List<Question> questionsList = new List<Question>();
    }

    [Serializable]
    public class PlayerInfo
    {
        public string username;
        public string status;
    }

    [Serializable]
    public class PlayerInfos
    {
        public List<PlayerInfo> playerInfoList = new List<PlayerInfo>();
    }

    [Serializable]
    public class Category
    {
        public int r;
        public int g;
        public int b;
        public int a;
        public int id;
        public string name;
    }

    [Serializable]
    public class Categories
    {
        public List<Category> categoryList = new List<Category>();
    }

    [Serializable]
    public class UserName
    {
        public string id;
        public string username;
    }

    [Serializable]
    public class UserNames
    {
        public List<UserName> usernamesList = new List<UserName>();
    }

    internal void SetLastPlayedDate(int gameId)
    {
        if (gameMode.Equals(Constants.ONLINE))
        {

        }
        string sql = "UPDATE game SET lastPlayedDate = DATE() WHERE id = " + gameId;
        IDbCommand cmd = GetConnection();
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();

        cmd.Dispose();
        CloseConnection();
    }

    internal List<OnlineDatabase.UserName> FindRandomPlayer(int playerId)
    {
        return OnlineDatabase.Instance.FindRandomPlayer(playerId);
    }

    private IDbCommand GetConnection()
    {
        if (databaseUrl == null)
        { // TODO ej bra lösning för online
            SetLocalOrOnline(Constants.LOCAL);
        }
        Debug.Log("Database url " + databaseUrl);
        conn = new SqliteConnection(databaseUrl);
        conn.Open();
        IDbCommand cmd = conn.CreateCommand();
        return cmd;
    }



    private void CloseConnection()
    {
        conn.Close();
    }

    string questionString = "";
    string answerString = "";
    string idString = "";
    string categoryString = "";
    private int round = -1;

    public string QuestionString { get => questionString; set => questionString = value; }


    private void Start()
    {
        if (instance == null)
        {
            instance = this;
        }

        if (databaseUrl == null || databaseUrl.Equals(""))
        {
            SetLocalOrOnline(Constants.LOCAL); // Temporary, should not be needed after local testing
        }
    }

    internal void SignIn(string username, int userId, bool keepSigendIn)
    {
        int ksi = 0;
        if (keepSigendIn)
        {
            ksi = 1;
        }
        string sql = "INSERT OR REPLACE INTO userSettings (username, userId, keepSignedIn) VALUES ('" + username + "', " + userId + "," + ksi + ")";
        IDbCommand cmd = GetConnection();
        cmd.CommandText = sql;

        cmd.ExecuteNonQuery();

        sql = "INSERT OR REPLACE INTO settings (name, value) VALUES ('signedInUser', '" + username + "')";
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();

        CloseConnection();
    }

    internal void SetGameFinished(int gameId, string gameMode)
    {
        if (gameMode.Equals(Constants.ONLINE))
        {
            OnlineDatabase.Instance.SetGameFinished(gameId);
        }
        else
        {
            string sql = "UPDATE game SET finishedDate = DATETIME() WHERE id = " + gameId;
            IDbCommand cmd = GetConnection();
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
        }
    }

    internal bool IsKeepSignedIn()
    {
        string sql = "SELECT keepSignedIn FROM userSettings";
        IDbCommand cmd = GetConnection();
        cmd.CommandText = sql;

        IDataReader reader = cmd.ExecuteReader();
        int res = 0;
        while (reader.Read())
        {
            res = reader.GetInt32(0);
        }
        return res == 1;
    }

    internal KeyValuePair<int, string> GetSignedInUser()
    {
        // string sql = "SELECT userId, username FROM userSettings";
        string sql = "SELECT userId, username FROM userSettings WHERE username = (SELECT value FROM settings WHERE name = 'signedInUser')";
        IDbCommand cmd = GetConnection();
        cmd.CommandText = sql;
        int userId = -1;
        string username = "";
        IDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            userId = reader.GetInt32(0);
            username = reader.GetString(1);
        }

        CloseConnection();
        return new KeyValuePair<int, string>(userId, username);
    }

    internal void LogoutUser()
    {
        // string sql = "UPDATE userSettings SET keepSignedIn = 0";
        string sql = "DELETE FROM userSettings";
        IDbCommand cmd = GetConnection();
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();

        sql = "DELETE FROM settings WHERE name = 'signedInUser'";
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();

        CloseConnection();


    }

    internal int GetQuestionTimer(int gameId)
    {
        if (questionTimer == -1)
        {
            string sql = "SELECT answerTimer FROM game WHERE id = " + gameId;
            if (databaseUrl == null)
            {
                SetLocalOrOnline(Constants.LOCAL);
            }
            IDbCommand cmd = GetConnection();
            cmd.CommandText = sql;
            IDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                questionTimer = reader.GetInt32(0);
            }
            reader.Close();
            cmd.Dispose();
        }
        CloseConnection();
        return this.questionTimer;
    }

    internal List<LocalGameScript> GetLocalGames(GameObject prefab)
    {
        List<LocalGameScript> games = new List<LocalGameScript>();
        string sql = "SELECT game.*, name, localUsers.id as userId FROM game INNER JOIN localGamePlayers on game.id = localGamePlayers.gameId INNER JOIN localUsers on playerId = localUsers.id order by game.id ASC, userId ASC";
        IDbConnection conn = new SqliteConnection(databaseUrl);
        conn.Open();
        IDbCommand cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        IDataReader reader = cmd.ExecuteReader();
        int currentGame = -1;
        GameObject localGame;
        LocalGameScript lgs = null;

        string currentPlayerName;
        int currentPoints;
        while (reader.Read())
        {
            if (currentGame != reader.GetInt32(0))
            {
                localGame = Instantiate(prefab, new Vector2(0, 0), Quaternion.identity) as GameObject;
                lgs = localGame.GetComponent<LocalGameScript>();

                lgs.GameId = reader.GetInt32(0);
                lgs.GameMode = reader.GetString(1);
                lgs.QuestionsNeededToWin = reader.GetInt32(2).ToString();
                lgs.AnswerTimer = reader.GetInt32(3);
                lgs.NumberOfPlayers = reader.GetInt32(4).ToString();
                lgs.CurrentPlayer = reader.GetString(5);
                lgs.Round = reader.GetInt32(6).ToString();
                lgs.StartDate = reader.GetString(7);
                lgs.LastPlayedDate = reader.GetString(8);
                lgs.FinishedDate = reader.IsDBNull(9) ? "" : reader.GetString(9);

                currentPlayerName = reader.GetString(10);
                currentPoints = GetPlayerPoints(reader.GetInt32(0), currentPlayerName);
                lgs.AddPlayer(currentPlayerName, currentPoints);
                games.Add(lgs);
            }
            else if (currentGame == reader.GetInt32(0))
            {
                currentPlayerName = reader.GetString(10);
                currentPoints = GetPlayerPoints(currentGame, currentPlayerName);
                lgs.AddPlayer(currentPlayerName, currentPoints);
            }

            currentGame = reader.GetInt32(0);
        }
        reader.Close();
        cmd.Dispose();
        conn.Close();

        return games;
    }

    internal void IncreasePlayerRound(int gameId, string currentPlayer)
    {

        if (gameMode.Equals(Constants.ONLINE))
        {
            OnlineDatabase.Instance.IncreasePlayerRound(gameId, currentPlayer);
        }
        else
        {
            String sql = "UPDATE localGamePlayers SET playerRound = playerRound + 1 WHERE gameId = " + gameId + " AND playerId = (SELECT id FROM localUsers WHERE name = '" + currentPlayer + "')";
            IDbConnection conn = new SqliteConnection(databaseUrl);
            conn.Open();
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;

            cmd.ExecuteNonQuery();

            cmd.Dispose();
            conn.Close();
        }

    }

    internal void SetQuestionsLost(int gameId, string playerName, int questionsLost)
    {

        string sql = "UPDATE localGamePlayers SET questionsLost = questionsLost + " + questionsLost + " WHERE gameId = " + gameId + " AND playerId = (SELECT id FROM localUsers WHERE  name = '" + playerName + "')";
        IDbConnection conn = new SqliteConnection(databaseUrl);
        conn.Open();
        IDbCommand cmd = conn.CreateCommand();
        cmd.CommandText = sql;

        cmd.ExecuteNonQuery();

        cmd.Dispose();
        conn.Close();

    }

    internal void RemoveGame(int gameId)
    {
        string sql = "DELETE FROM game WHERE id = " + gameId;

        IDbConnection conn = new SqliteConnection(databaseUrl);
        conn.Open();
        IDbCommand cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();

        string deleteLockedQuestionsSql = "DELETE FROM usersLockedQuestions WHERE gameId = " + gameId;
        cmd.CommandText = deleteLockedQuestionsSql;
        cmd.ExecuteNonQuery();

        cmd.Dispose();
        conn.Close();
    }

    public string GetCurrentPlayer(int gameId, string gameMode)
    {
        if (gameMode.Equals(Constants.ONLINE))
        {
            return OnlineDatabase.Instance.GetCurrentPlayer(gameId);
        }
        string sql = "SELECT currentPlayer FROM game WHERE id = " + gameId;
        IDbConnection conn = new SqliteConnection(databaseUrl);
        conn.Open();
        IDbCommand cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        IDataReader reader = cmd.ExecuteReader();

        string currentPlayer = "";
        while (reader.Read())
        {
            currentPlayer = reader.GetString(0);
        }
        reader.Close();
        cmd.Dispose();
        conn.Close();

        return currentPlayer;
    }

    public void SetCurrentPlayer(int gameId, string currentPlayer, string gameMode)
    {
        if (gameMode.Equals(Constants.ONLINE))
        {
            OnlineDatabase.Instance.SetCurrentPlayer(gameId, currentPlayer);
        }
        else
        {
            string sql = "UPDATE game SET currentPlayer = '" + currentPlayer + "' WHERE id = " + gameId;
            IDbConnection conn = new SqliteConnection(databaseUrl);
            conn.Open();
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            cmd.Dispose();
            conn.Close();
        }
    }

    internal int GetPlayerPoints(int gameId, string playerName)
    {
        string sql = "SELECT count(*) FROM usersLockedQuestions WHERE gameId = " + gameId + " AND playerName = '" + playerName + "'";
        IDbConnection conn = new SqliteConnection(databaseUrl);
        conn.Open();
        IDbCommand cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        IDataReader reader = cmd.ExecuteReader();

        int points = 0;
        while (reader.Read())
        {
            points = reader.GetInt32(0);
        }
        reader.Close();
        cmd.Dispose();
        conn.Close();

        return points;
    }

    internal void SetFinishedDate(int gameId, string finishedDate, string gameMode)
    {
        if (gameMode.Equals(Constants.ONLINE))
        {
            OnlineDatabase.Instance.SetFinishedDate(gameId, finishedDate);
        }
        else
        {
            string sql = "UPDATE game SET finishedDate = '" + finishedDate + "' WHERE id = " + gameId;
            IDbConnection conn = new SqliteConnection(databaseUrl);
            conn.Open();
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            cmd.Dispose();
            conn.Close();
        }
    }

    internal void SetRoundValue(int gameId, int round, string gameMode)
    {
        if (gameMode.Equals(Constants.ONLINE))
        {
            OnlineDatabase.Instance.SetRoundValue(gameId, round);
        }
        else
        {
            string sql = "UPDATE game SET round = " + round + " WHERE id = " + gameId;
            IDbConnection conn = new SqliteConnection(databaseUrl);
            conn.Open();
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            cmd.Dispose();
            conn.Close();
        }
    }

    internal int GetRoundValue(int gameId, string gameMode)
    {
        if (gameMode.Equals(Constants.ONLINE))
        {
            return OnlineDatabase.Instance.GetRoundValue(gameId);
        }
        if (this.round == -1)
        {
            SetLocalOrOnline(Constants.LOCAL);
            string sql = "SELECT round FROM game WHERE id = " + gameId;
            IDbConnection conn = new SqliteConnection(databaseUrl);
            conn.Open();
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            IDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                this.round = reader.GetInt32(0);
            }
            reader.Close();
            cmd.Dispose();
            conn.Close();
        }
        return this.round;
    }

    internal List<KeyValuePair<string, int>> GetPlayersForGame(int gameId, string gameMode)
    {
        if (gameMode.Equals(Constants.ONLINE))
        {
            return OnlineDatabase.Instance.GetPlayersForGame(gameId);
        }
        string sql = "SELECT name, (SELECT count(*) FROM usersLockedQuestions WHERE playerName = localUsers.name AND gameId = " + gameId + ") as numAnswers  FROM localGamePlayers " +
            "LEFT JOIN localUsers ON localGamePlayers.playerId = localUsers.id " +
            "WHERE gameId = " + gameId;
        IDbConnection conn = new SqliteConnection(databaseUrl);
        conn.Open();
        IDbCommand cmd = conn.CreateCommand();
        cmd.CommandText = sql;

        IDataReader reader = cmd.ExecuteReader();

        List<KeyValuePair<string, int>> returnList = new List<KeyValuePair<string, int>>();
        while (reader.Read())
        {
            KeyValuePair<string, int> player = new KeyValuePair<string, int>(reader.GetString(0), reader.GetInt32(1));
            returnList.Add(player);
        }

        return returnList;
    }

    public string AnswerString { get => answerString; set => answerString = value; }
    public string IdString { get => idString; set => idString = value; }
    public string CategoryString { get => categoryString; set => categoryString = value; }

    public void SetLocalOrOnline(string type)
    {
        gameMode = type;
        if (type.Equals(Constants.LOCAL))
        {
            string databaseName = "narKampenLocal.db";
            if (Application.platform == RuntimePlatform.Android)
            {
                databaseUrl = Application.persistentDataPath + "/" + databaseName;
                if (!File.Exists(databaseUrl))
                {
                    UnityWebRequest load = UnityWebRequest.Get("jar:file://" + Application.dataPath + "!/assets/" + databaseName);
                    load.SendWebRequest();
                    while (!load.isDone) { }

                    File.WriteAllBytes(databaseUrl, load.downloadHandler.data);
                }
                databaseUrl = "URI=file:" + databaseUrl;
            }
            else
            {
                databaseUrl = "URI=file:" + Application.dataPath + "/" + databaseName;
            }
        }
        else
        {
            databaseUrl = onlineQuestionsUrl;
        }
        connectionType = type;

        Debug.Log("Database url " + databaseUrl);
    }

    internal int GetQuestionsLost(int gameId, string playerName, string gameMode)
    {
        if (gameMode.Equals(Constants.ONLINE))
        {
            return OnlineDatabase.Instance.GetQuestionsLost(gameId, playerName);
        }
        string sql = "SELECT questionsLost FROM localGamePlayers WHERE gameId = " + gameId + " AND playerId = (SELECT id from localUsers WHERE name = '" + playerName + "')";
        IDbConnection conn = new SqliteConnection(databaseUrl);
        conn.Open();
        IDbCommand cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        IDataReader reader = cmd.ExecuteReader();
        int returnValue = 0;
        while (reader.Read())
        {
            returnValue = reader.GetInt32(0);
        }
        reader.Close();
        cmd.Dispose();
        conn.Close();

        return returnValue;
    }

    public string GetGameMode(int gameId)
    {
        if (this.gameMode == null)
        {
            string sql = "SELECT gameMode FROM game WHERE id = " + gameId;
            IDbConnection conn = new SqliteConnection(databaseUrl);
            conn.Open();
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            IDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                this.gameMode = reader.GetString(0);
            }
            reader.Close();
            cmd.Dispose();
            conn.Close();
        }
        return this.gameMode;
    }

    public void LinkPlayersToLocalGame(List<string> playerNames, int gameId)
    {

        IDbConnection conn = new SqliteConnection(databaseUrl);
        conn.Open();
        string questionSql = "SELECT id FROM questions order by random() limit 1";
        IDbCommand cmd = conn.CreateCommand();
        cmd.CommandText = questionSql;
        IDataReader reader = cmd.ExecuteReader();
        int questionId = -1;
        while (reader.Read())
        {
            questionId = reader.GetInt32(0);
        }
        List<int> l = new List<int>();
        l.Add(questionId);
        foreach (string player in playerNames)
        {
            string sql = "SELECT id FROM localUsers WHERE name = '" + player + "'";
            int playerId;
            cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                playerId = reader.GetInt32(0);
            }
            else
            {
                reader.Close();
                sql = "INSERT INTO localUsers (name) VALUES ('" + player + "')";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT last_insert_rowid()";
                Int64 lastInsert64 = (Int64)cmd.ExecuteScalar();
                playerId = (int)lastInsert64;
            }
            cmd.Dispose();
            reader.Close();

            LinkPlayerToGame(playerId, gameId);
            SavePlayersQuestion(l, player, gameId, Constants.LOCAL);

        }
        IDbCommand cmd2 = conn.CreateCommand();
        cmd2.CommandText = "UPDATE game SET currentPlayer = '" + playerNames[0] + "' WHERE id = " + gameId;
        cmd2.ExecuteNonQuery();
        cmd2.Dispose();
        conn.Close();
    }

    private void LinkPlayerToGame(int playerId, int gameId)
    {
        string sql = "INSERT INTO localGamePlayers (gameId, playerId) VALUES (" + gameId + ", " + playerId + ")";
        SqliteConnection conn2 = new SqliteConnection(databaseUrl);
        conn2.Open();
        IDbCommand cmd = conn2.CreateCommand();
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        conn2.Close();
    }

    internal int GetWinCondition(int gameId, string gameMode)
    {
        if (gameMode.Equals(Constants.ONLINE))
        {
            return OnlineDatabase.Instance.GetWinCondition(gameId);
        }
        if (winAmount == -1)
        {
            string sql = "SELECT winNumber FROM game WHERE id = " + gameId;
            IDbConnection conn = new SqliteConnection(databaseUrl);
            conn.Open();
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            IDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                this.winAmount = reader.GetInt32(0);
            }
            reader.Close();
            cmd.Dispose();
            conn.Close();
        }
        return this.winAmount;
    }

    public int SetupNewLocalGame(int winNumber, int numberOfPlayers, int questionTimer)
    {
        string sql = "INSERT INTO game (winNumber, numberOfPlayers, answerTimer, gameMode, startedDate) VALUES (" + winNumber + "," + numberOfPlayers + "," + questionTimer + ", 'Local', DATE())";
        IDbConnection conn = new SqliteConnection(databaseUrl);
        conn.Open();
        IDbCommand cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();

        cmd.CommandText = "SELECT last_insert_rowid()";
        Int64 lastInsert64 = (Int64)cmd.ExecuteScalar();

        return (int)lastInsert64;
    }

    public NewQuestionData GetNewQuestion(List<int> userAnsweredQuestions, KeyValuePair<int, String> user, string gameMode)
    {
        if (gameMode.Equals(Constants.ONLINE))
        {
            return OnlineDatabase.Instance.GetNewQuestion(user.Key);
        }
        int gameId = GameObject.Find("GameManager").GetComponent<GameManagerScript>().GameId;
        Color32 questionCategoryColor = new Color32(0, 0, 20, 20);
        if (connectionType == null)
        {
            SetLocalOrOnline(GetGameMode(gameId));
        }

        int id = -1;
        int categoryId = -1;
        string categoryName = "";
        if (connectionType == Constants.LOCAL)
        {
            IDbConnection conn = new SqliteConnection(databaseUrl);
            conn.Open();
            IDbCommand cmd = conn.CreateCommand();

            string answeredIds = String.Join(",", userAnsweredQuestions);

            string sql = "SELECT questions.id, question, answer, categoryId as category, name, category.R, category.G, category.B, category.A FROM questions INNER JOIN questionToCategory ON questions.id = questionToCategory.questionId INNER JOIN category on category.id = questionToCategory.categoryId WHERE questions.id NOT IN (" + answeredIds + ") AND questions.id NOT IN (SELECT questionId FROM questionsInGame WHERE gameId = " + gameId + " AND userId = (SELECT id from localUsers WHERE name = '" + user.Value + "')) ORDER BY RANDOM() limit 1";
            cmd.CommandText = sql;
            IDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                id = reader.GetInt32(0);
                string question = reader.GetString(1);
                int answer = reader.GetInt32(2);
                categoryId = reader.GetInt32(3);
                categoryName = reader.GetString(4);

                idString = id.ToString();
                questionString = question;
                categoryString = categoryId.ToString();
                answerString = answer.ToString();

                byte r = (byte)reader.GetInt32(5);
                byte g = (byte)reader.GetInt32(6);
                byte b = (byte)reader.GetInt32(7);
                byte a = (byte)reader.GetInt32(8);

                questionCategoryColor = new Color32(r, g, b, a);
            }
            reader.Close();

            string saveSentQuestionSql = "INSERT INTO questionsInGame (gameId, questionId, userId) VALUES (" + gameId + ", " + id + ", (SELECT id FROM localUsers WHERE name = '" + user.Value + "'))";
            cmd.CommandText = saveSentQuestionSql;
            cmd.ExecuteNonQuery();

            cmd.Dispose();
            conn.Close();
        }
        NewQuestionData q = new NewQuestionData(answerString, questionString, categoryName, categoryId, id, questionCategoryColor, false);

        return q;
    }

    public void SavePlayersQuestion(List<int> questionsToSave, string playerNameValue, int gameId, string gameMode)
    {
        if (gameMode.Equals(Constants.ONLINE))
        {
            OnlineDatabase.Instance.SavePlayersQuestion(questionsToSave, playerNameValue, gameId);
        }
        else
        {
            SetLocalOrOnline(gameMode);

            string values = "";
            foreach (int questionId in questionsToSave)
            {
                values += "('" + playerNameValue + "'," + questionId + "," + gameId + "),";
            }
            values = values.Substring(0, values.Length - 1);
            string sql = "INSERT OR IGNORE INTO usersLockedQuestions (playerName, questionId, gameId) VALUES " + values;
            IDbConnection conn = new SqliteConnection(databaseUrl);
            conn.Open();
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;

            cmd.ExecuteReader();

            cmd.Dispose();
            conn.Close();
        }
    }

    public List<NewQuestionData> GetPlayerQuestions(int gameId, string playerNameValue, string gameMode)
    {
        if (gameMode.Equals(Constants.ONLINE))
        {
            return OnlineDatabase.Instance.GetPlayerQuestions(gameId, playerNameValue);
        }
        if (databaseUrl == null)
        {
            SetLocalOrOnline(GetGameMode(gameId));
        }
        List<NewQuestionData> questions = new List<NewQuestionData>();

        if (connectionType.Equals(Constants.LOCAL))
        {
            string sql = "SELECT * FROM questions inner join questionToCategory on questions.id = questionToCategory.questionId INNER JOIN category ON category.id = questionToCategory.categoryId WHERE questions.id IN ( SELECT questionId FROM usersLockedQuestions WHERE gameId = " + gameId + " AND playerName = '" + playerNameValue + "') ORDER BY answer ASC";
            IDbConnection conn = new SqliteConnection(databaseUrl);
            conn.Open();
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;

            IDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Color32 questionCategoryColor = new Color32((byte)reader.GetInt32(7), (byte)reader.GetInt32(8), (byte)reader.GetInt32(9), (byte)reader.GetInt32(10));

                NewQuestionData questionData = new NewQuestionData(
                    reader.GetInt32(2).ToString(),
                    reader.GetString(1),
                    reader.GetString(6),
                    reader.GetInt32(5),
                    reader.GetInt32(0),
                    questionCategoryColor,
                    true);


                questions.Add(questionData);
            }
            cmd.Dispose();
            conn.Close();
        }
        return questions;
    }

    /*     private IEnumerator GetQuestionData(bool showAnswer) {
            UnityWebRequest www = UnityWebRequest.Get("narkampen.nordh.xyz/narKampen/dbFiles/Question.php");

            yield return www.SendWebRequest();

            if (www.isNetworkError || www.isHttpError) {
                Debug.Log(www.error);
            } else {
                while (!www.isDone) {
                    yield return null;
                }
                // Show result
                string jsonData = www.downloadHandler.text;

                jsonData = "{\"questionsList\" : [ " + jsonData + " ]}";

                Questions qe = new Questions();
                JsonUtility.FromJsonOverwrite(jsonData, qe);

                questionString = qe.questionsList[0].question;
                answerString = qe.questionsList[0].answer;
                idString = qe.questionsList[0].id;
                categoryString = qe.questionsList[0].category;
            }
        } */
}