NarKampen/Assets/Scripts/Database/OnlineDatabase.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 OnlineDatabase : MonoBehaviour {
    private const string onlineQuestionsUrl = "nordh.xyz/narKampen/dbFiles/Question.php";
    private const string serverUrl = "nordh.xyz/narKampen/dbFiles/";
    string databaseUrl;
    string gameMode;
    int winAmount = -1;
    int questionTimer = -1;

    public GameObject questionCardPrefab;

    private static OnlineDatabase instance;
    public static OnlineDatabase Instance { get { return instance; } }

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

    internal List<CategoryPanel.Category> GetCategories(List<CategoryPanel.Category> list) {
        string response = CallOnlineDatabaseWithResponse("Categories.php", null);

        response = "{\"categoryList\" :  " + response + " }";

        Categories categories = new Categories();
        Debug.Log(response);
        JsonUtility.FromJsonOverwrite(response, categories);

        foreach (Category c in categories.categoryList) {
            CategoryPanel.Category cat = new CategoryPanel.Category();

            cat.color = new Color32((byte)c.r, (byte)c.g, (byte)c.b, (byte)c.a);
            cat.name = c.name;
            cat.id = c.id;

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

    internal void SetupNewOnlineGame(int limitPerQuestion, int limitPerPlayer, int toWin, List<InviteSearchResult> inviteUsers) {
        List<int> playerIds = new List<int>();
        inviteUsers.ForEach(i => playerIds.Add(i.GetId()));
        int currentUser = Database.Instance.GetSignedInUser().Key;
        playerIds.Add(currentUser);

        var form = new WWWForm();
        form.AddField("currentUser", currentUser);
        form.AddField("winNumber", toWin);
        form.AddField("limitPerQuestion", limitPerQuestion);
        form.AddField("limitPerPlayer", limitPerPlayer);
        form.AddField("playerIds", String.Join(",", playerIds));

        Debug.Log(CallOnlineDatabaseWithResponse("NewOnlineGame.php", form));
    }

    [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 GamePlayerInfo {
        public string username;
        public string userLockedQuestions;
    }

    [Serializable]
    public class GamePlayerInfos {
        public List<GamePlayerInfo> gamePlayerInfoList = new List<GamePlayerInfo>();
    }



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

    private void CallDatabase(string filename, WWWForm formData) {
        string postUrl = serverUrl + filename;

        UnityWebRequest www = UnityWebRequest.Post(postUrl, formData);
        www.SendWebRequest();

        if (www.isNetworkError || www.isHttpError) {
            Debug.Log(www.error);
        } else {
            while (!www.isDone) {
            }
        }
    }

    private string CallOnlineDatabaseWithResponse(string filename, WWWForm formData) {
        string postUrl = serverUrl + filename;

        UnityWebRequest www = UnityWebRequest.Post(postUrl, formData);
        www.SendWebRequest();

        if (www.isNetworkError || www.isHttpError) {
            Debug.Log(www.error);
        } else {
            while (!www.isDone) {
            }
        }

        return www.downloadHandler.text;
    }

    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;
        }
    }

    internal void SetLastPlayedDate(int gameId) {

        WWWForm form = new WWWForm();
        form.AddField("gameId", gameId);
        form.AddField("f", "SetLastPlayed");
        string response = CallOnlineDatabaseWithResponse("OnlineGames.php", form);

        if (!response.Equals("")) {
            Debug.Log(response);
        }
    }

    internal void DeclineOnlineGame(string userName, int gameId) {
        WWWForm formData = new WWWForm();
        formData.AddField("userId", -1);
        formData.AddField("f", "decline");
        formData.AddField("gameId", gameId);
        formData.AddField("userName", userName);

        CallDatabase("OnlineGames.php", formData);
    }

    internal void AcceptOnlineGame(string userName, int gameId) {
        WWWForm formData = new WWWForm();
        formData.AddField("userId", -1);
        formData.AddField("f", "accept");
        formData.AddField("gameId", gameId);
        formData.AddField("userName", userName);

        CallDatabase("OnlineGames.php", formData);
    }

    internal List<OnlineGameScript> GetOnlineGames(int userId, string userName, GameObject prefab) {

        WWWForm formData = new WWWForm();
        formData.AddField("userId", userId);
        formData.AddField("f", "list");
        formData.AddField("gameId", -1);
        formData.AddField("userName", userName);

        string response = CallOnlineDatabaseWithResponse("OnlineGames.php", formData);

        if (response.Equals("No games found for user")) {
            return null;
        }

        response = "{\"onlineGamesList\" :  " + response + " }";

        OnlineGames og = new OnlineGames();
        JsonUtility.FromJsonOverwrite(response, og);

        GameObject onlineGameObject;
        OnlineGameScript ogs = null;
        List<OnlineGameScript> games = new List<OnlineGameScript>();
        foreach (OnlineGame game in og.onlineGamesList) {
            onlineGameObject = Instantiate(prefab, new Vector2(0, 0), Quaternion.identity) as GameObject;
            ogs = onlineGameObject.GetComponent<OnlineGameScript>();
            ogs.CurrentPlayer = game.currentPlayer;
            ogs.SetGameStatus(game.status);
            Int32.TryParse(game.id, out int gameId);
            List<KeyValuePair<string, string>> playerInfos = GetGameInfo(gameId);
            if (game.status.Equals("PENDING")) {
                string extraInfo = "";
                foreach (KeyValuePair<string, string> s in playerInfos) {
                    if (s.Value.Equals("WAITING") && s.Key.Equals(userName)) {
                        ogs.SetGameStatus("INVITED");
                        extraInfo += s.Key + ",";
                    } else if (s.Value.EndsWith("WAITING")) {
                        extraInfo += s.Key + ",";
                    }
                }
                extraInfo = extraInfo.TrimEnd(',');
                ogs.SetGameStatusText(extraInfo);

            } else if (game.status.Equals("OTHERS_TURN")) { // Wont work
                ogs.SetGameStatusText(game.currentPlayer);
            } else if (game.status.Equals("ACTIVE")) {
                ogs.SetGameStatusText(game.currentPlayer);
            } else {
                ogs.SetGameStatusText();
            }

            ogs.SetId(game.id);
            ogs.SetWinNumber(game.winNumber);
            ogs.SetAnswerTimer(game.answerTimer);
            ogs.SetRoundTimeLimit(game.roundTimeLimit);
            ogs.SetRound(game.round);
            ogs.StartDate = game.startDate;

            games.Add(ogs);
            ogs.PlayerInfos = playerInfos;
        }

        return games;
    }

    internal List<KeyValuePair<string, string>> GetGameInfo(int gameId) {
        List<KeyValuePair<string, string>> returnList = new List<KeyValuePair<string, string>>();

        WWWForm form = new WWWForm();
        form.AddField("gameId", gameId);

        string response = CallOnlineDatabaseWithResponse("OnlineGameInfo.php", form);

        response = "{\"playerInfoList\" : " + response + " }";
        PlayerInfos pi = new PlayerInfos();
        JsonUtility.FromJsonOverwrite(response, pi);

        foreach (PlayerInfo p in pi.playerInfoList) {
            KeyValuePair<string, string> player = new KeyValuePair<string, string>(p.username, p.status);
            returnList.Add(player);
        }

        return returnList;
    }

    internal void SetQuestionsLost(int gameId, string playerName, int questionsLost) {
        WWWForm form = new WWWForm();
        form.AddField("f", "SetQuestionsLost");
        form.AddField("questionsLost", questionsLost);
        form.AddField("userName", playerName);
        form.AddField("gameId", gameId);

        string response = CallOnlineDatabaseWithResponse("OnlineGames.php", form);

        if (!response.Equals("")) {
            Debug.Log(response);
        }
    }

    internal void RemoveGame(int gameId) {
        WWWForm form = new WWWForm();
        form.AddField("f", "DeleteGame");
        form.AddField("gameId", gameId);

        string response = CallOnlineDatabaseWithResponse("OnlineGames.php", form);

        if (!response.Equals("")) {
            Debug.Log(response);
        }
    }

    public string GetCurrentPlayer(int gameId) {
        WWWForm form = new WWWForm();
        form.AddField("f", "CurrentPlayer");
        form.AddField("gameId", gameId);

        string response = CallOnlineDatabaseWithResponse("OnlineGames.php", form);
        if (response.Equals("")) {
            Debug.Log("Something wrong with current player for game with id: " + gameId);
        }
        return response;
    }

    public void SetCurrentPlayer(int gameId, string currentPlayer) {
        WWWForm form = new WWWForm();
        form.AddField("f", "SetCurrentPlayer");
        form.AddField("gameId", gameId);
        form.AddField("currentPlayer", currentPlayer);

        string response = CallOnlineDatabaseWithResponse("OnlineGames.php", form);

        if (!response.Equals("")) {
            Debug.Log(response);
        }
    }

    internal int GetPlayerPoints(int gameId, string playerName) {
        WWWForm form = new WWWForm();
        form.AddField("f", "GetPlayerPoints");
        form.AddField("gameId", gameId);
        form.AddField("userName", playerName);

        string response = CallOnlineDatabaseWithResponse("OnlineGames.php", form);
        if (response.Equals("")) {
            Debug.Log("Something wrong with current player for game with id: " + gameId);
        }
        Int32.TryParse(response, out int playerPoints);
        return playerPoints;
    }

    internal void SetFinishedDate(int gameId, string finishedDate) {
        WWWForm form = new WWWForm();
        form.AddField("f", "SetFinishedDate");
        form.AddField("gameId", gameId);
        form.AddField("finishedDate", finishedDate);

        string response = CallOnlineDatabaseWithResponse("OnlineGames.php", form);

        if (!response.Equals("")) {
            Debug.Log(response);
        }
    }

    internal void SetRoundValue(int gameId, int round) {
        WWWForm form = new WWWForm();
        form.AddField("f", "SetRound");
        form.AddField("gameId", gameId);
        form.AddField("round", round);

        string response = CallOnlineDatabaseWithResponse("OnlineGames.php", form);

        if (!response.Equals("")) {
            Debug.Log(response);
        }
    }

    internal int GetRoundValue(int gameId) {
        WWWForm form = new WWWForm();
        form.AddField("f", "GetRound");
        form.AddField("gameId", gameId);

        string response = CallOnlineDatabaseWithResponse("OnlineGames.php", form);
        if (response.Equals("")) {
            Debug.Log("Something wrong with getting current player for game with id: " + gameId);
        }
        Int32.TryParse(response, out int round);
        return round;
    }

    internal List<KeyValuePair<string, int>> GetPlayersForGame(int gameId) {
        WWWForm form = new WWWForm();
        form.AddField("f", "GetPlayers");
        form.AddField("gameId", gameId);

        string response = CallOnlineDatabaseWithResponse("OnlineGames.php", form);
        if (response.Equals("")) {
            Debug.Log("Something wrong with getting players from game with id: " + gameId);
        }

        GamePlayerInfos gpi = new GamePlayerInfos();
        JsonUtility.FromJsonOverwrite(response, gpi);

        foreach (GamePlayerInfo p in gpi.gamePlayerInfoList) {
            Int32.TryParse(p.userLockedQuestions, out int points);
            KeyValuePair<string, int> player = new KeyValuePair<string, int>(p.username, points);

        }
        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; }

    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 NewQuestion GetNewQuestion(List<int> userAnsweredQuestions, string userName) {
        int gameId = GameObject.Find("GameManager").GetComponent<GameManagerScript>().GameId;
        Color32 questionCategoryColor = new Color32(0, 0, 20, 20);

        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) {
        string gameMode = PlayerPrefs.GetString("GameMode");

        Int32.TryParse(questionId, out int qId);

        if (gameMode.Equals("Local")) {
            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();
        } else {
            // TODO Save question to database online;
        }
    }

    public List<QuestionCard> GetPlayerQuestions(int gameId, string playerNameValue) {
        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();
        }
        */
        // TODO Fix online call to question
        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;
        }
    }

    public List<UserName> GetUsersToInvite(string searchString) {
        string postUrl = "nordh.xyz/narKampen/dbFiles/PlayerSearch.php?";
        postUrl += "search=" + UnityWebRequest.EscapeURL(searchString);


        UserNames uNames = new UserNames();
        UnityWebRequest www = UnityWebRequest.Get(postUrl);
        www.SendWebRequest();

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

            jsonData = "{\"usernamesList\" :  " + jsonData + " }";

            JsonUtility.FromJsonOverwrite(jsonData, uNames);
        }
        // TODO handle empty
        return uNames.usernamesList;
    }

}