using System.Runtime.Remoting.Messaging;
using MySql.Data.MySqlClient;
using System.Security.Cryptography;
using System.Text;
using UnityEngine;
using UnityEngine.SocialPlatforms.Impl;

public class ScoreBdd
{
    public static float[] myScores;
    public static float[] allScores;

    public static void loadMyScores()
    {
        myScores = new float[25];
        for (int i = 0; i < myScores.Length; i++)
            myScores[i] = PlayerPrefs.GetFloat("ScoreLvl" + (i + 1), -1);
    }

    public static void loadAllScores(ref int loadedScore)
    {
        allScores = new float[25];
        for (int i = 0; i < allScores.Length; i++)
        {
            allScores[i] = getAllBest(i + 1);
            loadedScore++;
        }
    }


    public static bool AddScore(int id, int lvl, float score)
    {
        MySqlConnection con = new MySqlConnection("host=51.38.179.180;user=kiuser;password=EioKnSzZq7Tc0jIf;database=kiugame;");
        string level = "lvl" + lvl;

        if (!ScoreExist(id))
        {
            string sql3 = "INSERT INTO kiugame.levelscore(id, " + level + ") VALUES('" + id + "', '" + score + "');";
            MySqlCommand cmd3 = new MySqlCommand(sql3, con);
            con.Open();
            cmd3.ExecuteNonQuery();
            con.Close();


            ActualiseWorldScoreOnline(id);
            return true;
        }


        string sql = "SELECT id, " + level + " FROM levelscore";
        MySqlCommand cmd = new MySqlCommand(sql, con);

        con.Open();
        MySqlDataReader reader = cmd.ExecuteReader();

        bool continuer = true;
        float tempScore = -1;

        while (continuer && reader.Read())
        {
            int tempId = (int) reader["id"];

            if (id == tempId)
            {
                if(reader[level].GetType().Equals(typeof(float)))
                    tempScore  = (float) reader[level];

                continuer = false;
                con.Close();
            }
        }

        if (tempScore < score)
        {
            string sql2 = "UPDATE kiugame.levelscore SET " + level + " = " + score + " WHERE id=" + id + ";";
            MySqlCommand cmd2 = new MySqlCommand(sql2, con);
            con.Open();
            cmd2.ExecuteNonQuery();
            con.Close();

            ActualiseWorldScoreOnline(id);
            return true;
        }

        con.Close();
        return false;
    }

    public static void ActualiseWorldScoreOnline(int id)
    {
        MySqlConnection con = new MySqlConnection("host=51.38.179.180;user=kiuser;password=EioKnSzZq7Tc0jIf;database=kiugame;");

        float totalScore = 0;

        for (int i = 1; i <= 5; i++)
        {
            float score = 0;

            for (int j = (i - 1) * 5 ; j < i * 5; j++)
                if (myScores[j] > 0)
                    score += myScores[j];

            totalScore += score;

            string sql = "UPDATE kiugame.levelscore SET world" + i + " = " + score + " WHERE id=" + id + ";";

            MySqlCommand cmd = new MySqlCommand(sql, con);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        string sql2 = "UPDATE kiugame.levelscore SET totalscore = " + totalScore + " WHERE id=" + id + ";";

        MySqlCommand cmd2 = new MySqlCommand(sql2, con);
        con.Open();
        cmd2.ExecuteNonQuery();
        con.Close();
    }

    public static bool ScoreExist(int id)
    {
        string sql = "SELECT id FROM levelscore";
        MySqlConnection con = new MySqlConnection("host=51.38.179.180;user=kiuser;password=EioKnSzZq7Tc0jIf;database=kiugame;");
        MySqlCommand cmd = new MySqlCommand(sql, con);
        con.Open();

        MySqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            int tempId = (int) reader["id"];

            if (id == tempId)
            {
                con.Close();
                return true;
            }
        }

        con.Close();
        return false;
    }


    public static float getAllBest(int lvl)
    {
        string level = "lvl" + lvl;
        string sql = "SELECT " + level + " FROM levelscore";
        MySqlConnection con = new MySqlConnection("host=51.38.179.180;user=kiuser;password=EioKnSzZq7Tc0jIf;database=kiugame;");
        MySqlCommand cmd = new MySqlCommand(sql, con);
        con.Open();

        MySqlDataReader reader = cmd.ExecuteReader();

        float score = -1;

        while (reader.Read())
        {
            float tempScore = -1;
            if (reader[level].GetType().Equals(typeof(float)))
                tempScore = (float) reader[level];

            if (tempScore > score)
                score = tempScore;
        }

        con.Close();

        if (score > 0)
            return score;

        return 0;
    }
}