NarKampen/Assets/Scripts/Database/Database.cs

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

public class Database : MonoBehaviour {
    private const string onlineQuestionsUrl = "narkampen.nordh.xyz/narKampen/dbFiles/Question.php";
    private const string serverUrl = "narkampen.nordh.xyz/narKampen/dbFiles/";
    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("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 OnlineGame {
        public string id;
        public string winNumber;
        public string answerTimer;
        public string roundTimeLimit;
        public string currentPlayer;
        public string round;
        public string startDate;
        public string LastPlayedDate;
        public string finishedDate;
        public string status;
    }

    [Serializable]
    public class OnlineGames {
        public List<OnlineGame> onlineGamesList = new List<OnlineGame>();
    }

    [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("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)
    {
        List<OnlineDatabase.UserName> result = new List<OnlineDatabase.UserName>();
        result = OnlineDatabase.Instance.FindRandomPlayer(playerId);

        return result;
    }

    private IDbCommand GetConnection() {
        if (databaseUrl == null) { // ej bra lösning för online
            SetLocalOrOnline("Local");
        }
        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;
    private string SearchString;

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


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

        if (databaseUrl == null || databaseUrl.Equals("")) {
            SetLocalOrOnline("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("Online")) {
            OnlineDatabase.Instance.SetGameFinished(gameId, gameMode);
        } 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, string gameMode) {
        if (questionTimer == -1) {
            string sql = "SELECT answerTimer FROM game WHERE id = " + gameId;
            if (databaseUrl == null) {
                SetLocalOrOnline("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 = 0;
        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) {
        WWWForm form = new WWWForm();
        form.AddField("player",currentPlayer);
        form.AddField("gameId", gameId);
    }

    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;

        List<LocalGameScript> games = new List<LocalGameScript>();
        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("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("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("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("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("Online")) {
            return OnlineDatabase.Instance.GetRoundValue(gameId);
        }
        if (this.round == -1) {
            SetLocalOrOnline("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("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("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 + "/narKampenLocal.db";
            }
        } else {
            databaseUrl = onlineQuestionsUrl;
        }
        connectionType = type;
    }

    internal int GetQuestionsLost(int gameId, string playerName, string gameMode) {
        if (gameMode.Equals("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, "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("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;
        int status = 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("Online")) {
            return OnlineDatabase.Instance.GetNewQuestion(userAnsweredQuestions, 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 == "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("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("Online")) {
            return OnlineDatabase.Instance.GetPlayerQuestions(gameId, playerNameValue);
        }
        if (databaseUrl == null) {
            SetLocalOrOnline(GetGameMode(gameId));
        }
        List<NewQuestionData> questions = new List<NewQuestionData>();

        if (connectionType.Equals("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;
        }
    }
}