using UnityEngine;
using System.Collections;
using System.Collections.Generic;
using System.Globalization;

public class DatabaseController : MonoBehaviour {
    // Make sure your data base contains:
    // A table named "participants"
    // A variable named participantID which is INT and AUTO_INCREMENT and PRIMARY_KEY

    /*
    Here is how to setup the database

    Local Test with xampp server:
    1. Set the DatabaseURL = localhost/experiments/emp
    2. Change the cariables in the connect.php script
    3. Move the php files (connect.php etc) to the folder C://xampp/htaccess/experiments/emp

    Online:
    1. Set the DatabaseURL = www.janajarecki.de/experiments/
    2. Change the cariables in the connect.php script
    3. Upload the php files (connect.php etc) to the location www.janajarecki.de/experiments

    */

    public struct Payload {
        public string tablename;
        public string columnname;
        public string data;
        public string datatype;
        public string id;
        public string hash {
            get {
                return Md5Sum(tablename + columnname + data + datatype + Globals.ParticipantID);
            }
        }
        public string json() {
            get {
                return "";
            }
        }

    }

    public bool DebugMode = false;
    public bool DontSave = false;
    public bool TestParticipant = false;
    public string DatabaseURL; // this is the folder/url in the on the server where PHP scripts are
    public List<string> TableNames;
    private bool _paymentTable = true;
    private Queue<Payload> queries = new Queue<>();

    // Make the tables in the data base and initialize the participants db
    void Awake()
    {
        if(!DontSave)
        {
            StartCoroutine(CreateTablesPhp(TableNames));
            StartCoroutine(InitializeParticipantPhp());
        }
    }

    // This creates tables (if they do not exist already)
    // It always creates a table named `participants` with variables participantID, deviceID
    private IEnumerator CreateTablesPhp(List<string> tablenames)
    {
        // by default we will add a participants table as the first table
        tablenames.Insert(0, "participants");
        // if we pay participants we will a payment table
        if (_paymentTable) tablenames.Add("payment");

        // loop throught table names and create them one by one
        foreach(string tablename in tablenames)
        {
            // create a new www form to post things into the DB
            WWWForm form = new WWWForm();
            form.AddField("tablename", tablename);
            var url = DatabaseURL +"CreateTables.php?";
            WWW webRequest = new WWW(url, form);
            yield return webRequest;

            if (DebugMode)
            {
                Debug.Log(webRequest.text +"(in CreateTablesPhp)");
            }

            // check for errors
            if (webRequest.error != null)
            {
                Debug.Log("WWW Error when trying to create tables: " +webRequest.error);
            }
        }
    }

    // This initializes the participant
    public IEnumerator InitializeParticipantPhp()
    {

        if (TestParticipant)
        {
            WWW testWebRequest = new WWW(DatabaseURL +"DeleteTestParticipant.php?");
            yield return testWebRequest;
            if (DebugMode)
            {
                Debug.Log("Deleted Test Participant: " +testWebRequest.text);
            }

            if (testWebRequest.error != null)
            {
                Debug.Log("Error (Database.Controller, testWebRequest): "+ testWebRequest.error);
            }
        }

        // store the real input to prevent user generated fake input
        string hash = Md5Sum(SystemInfo.deviceUniqueIdentifier);

        // create a new www form to post things into the DB
        WWWForm form = new WWWForm();
        form.AddField("deviceId", SystemInfo.deviceUniqueIdentifier);
        form.AddField("hash", hash);

        // locate the php file that saves our device ID and retrieves the unique ID
        var url = DatabaseURL +"InitializeParticipant.php?";
        WWW webRequest = new WWW(url, form);
        yield return webRequest;

        // check for errors
        if (webRequest.error != null)
        {
            Debug.Log("WWW Error when trying to save the device and get the ID in the data base: "+ webRequest.error);
        }
        else
        {
            Globals.ParticipantID = webRequest.text;

            if (DebugMode)
            {
                Debug.Log("Initialized participant: " +webRequest.text);
            }
        }
    }


    // Save data to the data base
    public void Save(object data, string tablename, string columnname, string datatype, bool multipleTrials)
    {
        if (!DontSave)
        {
            StartCoroutine(SavePhp(data, tablename, columnname, datatype, multipleTrials));
        }

        if(DebugMode)
        {
            Debug.Log("Data: " +data);
        }
    }

    // This function stores new values into the existing columns of our mysql data base
    private IEnumerator SavePhp(object data, string tablename, string columnname, string datatype, bool multipleTrials)
    {
        // check if participant id was assigned
        if (Globals.ParticipantID == null)
        {
            Debug.Log("ERROR: Globals.ParticipantId is not defined yet. You must move a script using .Save() to the next game object. (in SavePhp)");
        }

        // store the real input to prevent user generated fake input
        string hash = Md5Sum(tablename + columnname + data + datatype + Globals.ParticipantID);

        // create a new www form to post things into the DB
        WWWForm form = new WWWForm();
        form.AddField("tablename", tablename);
        form.AddField("columnname", columnname);
        form.AddField("data", data.ToString());
        form.AddField("datatype", datatype);
        form.AddField("id", Globals.ParticipantID);
        form.AddField("hash", hash);

        // locate the php file that saves our data into the table named tablename
        //var url = DatabaseURL +"Save_" +tablename.ToLower() +".php?";
        var url = DatabaseURL;
        if (!multipleTrials) // no new row (add data in the row where participantID == participantID)
        {
            url = url +"UpdateData.php?";
        }
        if (multipleTrials) // make a new row based on trial number
        {
            form.AddField("trial", Globals.trial.ToString());
            url = url +"InsertData.php?";
        }
        WWW webRequest = new WWW(url, form);
        yield return webRequest;

        if (DebugMode)
        {
            Debug.Log("SavePHP: " +webRequest.text);
        }

        // check for errors
        if (webRequest.error != null)
        {
            Debug.Log("WWW Error when trying to save the data: "+ webRequest.error +" -- Concerns data" +columnname +" " +data);
        }
    }

    // Compute MD5 sums for checking for user inserted input
    public  string Md5Sum(string strToEncrypt)
    {
        System.Text.UTF8Encoding ue = new System.Text.UTF8Encoding();
        byte[] bytes = ue.GetBytes(strToEncrypt);

        // encrypt bytes
        System.Security.Cryptography.MD5CryptoServiceProvider md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
        byte[] hashBytes = md5.ComputeHash(bytes);

        // Convert the encrypted bytes back to a string (base 16)
        string hashString = "";

        for (int i = 0; i < hashBytes.Length; i++)
        {
            hashString += System.Convert.ToString(hashBytes[i], 16).PadLeft(2, '0');
        }

        return hashString.PadLeft(32, '0');
    }

}