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 {
    string connectionType;
    string databaseUrl;
    string gameMode;
    int winAmount = -1;

    public GameObject questionCardPrefab;

    [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>();
    }

    internal void SetLastPlayedDate(int gameId) {
        string sql = "UPDATE game SET lastPlayedDate = DATE() WHERE id = " + gameId;
        IDbConnection conn = new SqliteConnection(databaseUrl);
        conn.Open();
        IDbCommand cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();

        cmd.Dispose();
        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) {
        string sql = "SELECT answerTimer FROM game WHERE id = " + gameId;
        if (databaseUrl == null) {
            SetLocalOrOnline("Local");
        }
        IDbConnection conn = new SqliteConnection(databaseUrl);
        conn.Open();
        IDbCommand cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        IDataReader reader = cmd.ExecuteReader();

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

        return answerTime;
    }

    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;// = Instantiate(prefab, new Vector2(0, 0), Quaternion.identity) as GameObject;
        LocalGameScript lgs = null;//= localGame.GetComponent<LocalGameScript>();

        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.GetValue(7) != DBNull.Value ? reader.GetString(7) : "";
                lgs.LastPlayedDate = reader.GetValue(8) != DBNull.Value ? reader.GetString(8) : "";
                lgs.FinishedDate = reader.GetValue(9) != DBNull.Value ? 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 = "nordh.xyz/narKampen/dbFiles/dbAccess.php";
        }
        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() {
        if (connectionType == null) {
            int gameId = GameObject.Find("GameManager").GetComponent<GameManagerScript>().GameId;
            SetLocalOrOnline(GetGameMode(gameId));
        }
        if (connectionType == "Local") {
            IDbConnection conn = new SqliteConnection(databaseUrl);
            conn.Open();
            IDbCommand cmd = conn.CreateCommand();

            string sql = "SELECT questions.id, question, answer, categoryId as category, name FROM questions INNER JOIN questionToCategory ON questions.id = questionToCategory.questionId INNER JOIN category on category.id = questionToCategory.categoryId ORDER BY RANDOM() limit 1";
            cmd.CommandText = sql;
            IDataReader reader = cmd.ExecuteReader();

            while (reader.Read()) {
                int 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();
            }

            reader.Close();
            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;

        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 WHERE 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();

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

    private IEnumerator GetQuestionData(bool showAnswer) {
        UnityWebRequest www = UnityWebRequest.Get("nordh.xyz/narKampen/dbFiles/dbAccess.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);
            /*         if (qe.questionsList.Count > 0 && questionText != null) {
                         if (showAnswer && answerText != null) {
                             answerText.text = qe.questionsList[0].answer;
                         }
                         questionText.text = qe.questionsList[0].question;
                     } */
            questionString = qe.questionsList[0].question;
            answerString = qe.questionsList[0].answer;
            idString = qe.questionsList[0].id;
            categoryString = qe.questionsList[0].category;
        }
    }
}