NarKampen/Assets/Scripts/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 = "nordh.xyz/narKampen/dbFiles/Question.php";
    string connectionType;
    string databaseUrl;
    string gameMode;
    int winAmount = -1;
    int questionTimer = -1;

    public GameObject questionCardPrefab;

    IDbConnection conn;

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

    internal List<CategoryPanel.Category> GetCategories() {
        List<CategoryPanel.Category> list = new List<CategoryPanel.Category>();
        string sql = "SELECT name, r, g, b, a, id FROM category";
        IDbCommand cmd = GetConnection();
        cmd.CommandText = sql;

        IDataReader reader = cmd.ExecuteReader();
        
        while (reader.Read()) {
            CategoryPanel.Category cat = new CategoryPanel.Category();
            
            byte r = (byte)reader.GetInt32(1);
            byte g = (byte)reader.GetInt32(2);
            byte b = (byte)reader.GetInt32(3);
            byte a = (byte)reader.GetInt32(4);
            cat.color = new Color32(r, g, b, a);
            cat.name = reader.GetString(0);
            cat.id = reader.GetInt32(5);

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

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

    internal void SetLastPlayedDate(int gameId) {
        string sql = "UPDATE game SET lastPlayedDate = DATE() WHERE id = " + gameId;
        IDbCommand cmd = GetConnection();
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();

        cmd.Dispose();
        CloseConnection();
    }

    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;

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


    private void Start() {
        if (databaseUrl == null || databaseUrl.Equals("")) {
            SetLocalOrOnline("Local"); // Temporary, shoild not be needed after local testing
        }
    }

    internal int GetQuestionTimer(int gameId) {
        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 void GetOnlineGames(int userId) {
        string sql = "SELECT * FROM games WHERE userId = " + userId;
    }

    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.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 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 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 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 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 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) {
        if (this.round == -1) {
            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 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 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);
        }
        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(questionId.ToString(), player, gameId);

        }
        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) {
        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 NewQuestion GetNewQuestion(List<int> userAnsweredQuestions, string userName) {
        int gameId = GameObject.Find("GameManager").GetComponent<GameManagerScript>().GameId;
        Color32 questionCategoryColor = new Color32(0,0,20,20);
        if (connectionType == null) {
            SetLocalOrOnline(GetGameMode(gameId));
        }
        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 = '" + userName + "')) ORDER BY RANDOM() limit 1";
            cmd.CommandText = sql;
            IDataReader reader = cmd.ExecuteReader();

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

                idString = id.ToString();
                questionString = question;
                categoryString = catergoryId.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 = '" + userName + "'))";
            cmd.CommandText = saveSentQuestionSql;
            cmd.ExecuteNonQuery();

            cmd.Dispose();
            conn.Close();
        } else { // Connect Through db
            Debug.Log("Online Call");
            StartCoroutine(GetQuestionData(false));
        }
        NewQuestion q = NewQuestion.Instance();
        q.questionString = questionString;
        q.answerString = answerString;
        q.categoryString = categoryString;
        q.idString = idString;
        q.SetQuestionCategoryColor(questionCategoryColor);

        return q;
    }

    public void SavePlayersQuestion(string questionId, string playerNameValue, int gameId) {
        if (databaseUrl == null) {
            SetLocalOrOnline(GetGameMode(gameId));
        }
        Int32.TryParse(questionId, out int qId);
        string sql = "INSERT OR IGNORE INTO usersLockedQuestions (playerName, questionId, gameId) VALUES ('" + playerNameValue + "'," + qId + "," + gameId + ")";
        IDbConnection conn = new SqliteConnection(databaseUrl);
        conn.Open();
        IDbCommand cmd = conn.CreateCommand();
        cmd.CommandText = sql;

        cmd.ExecuteReader();

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

    public List<QuestionCard> GetPlayerQuestions(int gameId, string playerNameValue) {
        if (databaseUrl == null) {
            SetLocalOrOnline(GetGameMode(gameId));
        }
        List<QuestionCard> questions = new List<QuestionCard>();

        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()) {
                GameObject question = Instantiate(questionCardPrefab, new Vector2(0, 0), Quaternion.identity) as GameObject;
                QuestionCard q = question.GetComponent<QuestionCard>();

                q.SetAnswerText(reader.GetInt32(2).ToString());
                q.SetQuestionText(reader.GetString(1));
                q.idString = reader.GetInt32(0).ToString();
                Color32 questionCategoryColor = new Color32((byte)reader.GetInt32(7), (byte)reader.GetInt32(8), (byte)reader.GetInt32(9), (byte)reader.GetInt32(10));

                q.SetQuestionCategoryColor(questionCategoryColor);

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

    private IEnumerator GetQuestionData(bool showAnswer) {
        UnityWebRequest www = UnityWebRequest.Get("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;
        }
    }
}