using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace DBService
{
    public class DBService
    {
        private MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;

        public DBService()
        {
            Initialize();
        }

        private void Initialize()
        {
            server = "appychip.c27pqrom9fwo.eu-central-1.rds.amazonaws.com";
            database = "appychip";
            uid = "appychip";
            password = "appychip";
            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" +
            database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";" + "convert zero datetime=True";

            connection = new MySqlConnection(connectionString);
        }

        public int Select(string query, ref List<string>[] list)
        {
            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);
                
                MySqlDataReader dataReader = cmd.ExecuteReader();

                string a;
                //Read the data and store them in the list
                int i = 0;
                var x = "a";
                while (dataReader.Read())
                {
                    list[0].Add(dataReader["id"] + "");
                    list[1].Add(dataReader["eui"] + "");
                    list[2].Add(dataReader["temp"] + "");
                    list[3].Add(dataReader["humd"] + "");
                    list[4].Add(dataReader["pres"] + "");
                    //a = dataReader["dtime"].ToString();
                    //DateTime a = (DateTime)dataReader["dtime" + ""];
                    list[5].Add(Convert.ToDateTime(dataReader["dtime"]).ToString("dd-MM-yyyy HH:mm:ss"));
                    
                    //var v = dataReader["dtime"].GetType();
                    //var x = DateTime.ParseExact(dataReader["dtime"]., "dd-MM-yyyy hh:mm:ss", CultureInfo.InvariantCulture);
                    //DateTime b = DateTime.Parse(dataReader["dtime" + ""]);
                    //list[5].Add(dataReader["dtime"].ToString());
                }

                dataReader.Close();

                this.CloseConnection();

                return 1;
            }
            else
            {
                return -1;
            }
        }

        public int Count(string table)
        {
            string query = "SELECT Count(*) FROM ";
            query = String.Concat(query, table);
            int Count = -1;

            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                this.CloseConnection();

                return Count;
            }
            else
            {
                return Count;
            }
        }

        private bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        Console.WriteLine("Cannot connect to server.  Contact administrator");
                        break;

                    case 1045:
                        Console.WriteLine("Invalid username/password, please try again");
                        break;
                }
                return false;
            }
        }

        private bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }

        public void CreateTable()
        {
            string query = "CREATE TABLE iot_devices (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, eui VARCHAR(255), temp VARCHAR(255), humd VARCHAR(255), pres VARCHAR(255), dtime DATETIME ) ";

            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);

                cmd.ExecuteNonQuery();

                this.CloseConnection();
            }
        }

        public void Insert()
        {
            string query = "INSERT INTO iot_devices (eui, temp, humd, pres, dtime) VALUES('A3D5', '1A5D', '562C', '0000', '2020-03-20 15:11:30')";

            //open connection
            if (this.OpenConnection() == true)
            {
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //Execute command
                cmd.ExecuteNonQuery();

                //close connection
                this.CloseConnection();
            }
        }



    }


}
