using System;
using System.Data;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Collections;
using System.Linq;
using System.Text;

/*
            command =connection.CreateCommand();
            command.CommandText = "INSERT INTO log(userid,message,date) VALUES(1,'Hello there',NOW());";
            MySqlDataReader reader;
            reader = command.ExecuteReader();
            Console.WriteLine("Executed Query: " + reader);
 */
namespace Lightchat_server
{
    public class SQL
    {
        private MySqlConnection connection = new MySqlConnection();
        private MySqlDataAdapter data = new MySqlDataAdapter();
        private MySqlCommand command = new MySqlCommand();
        MySqlDataReader reader;


        public void connect(Boolean flag)
        {
            if (flag) { Console.WriteLine("Connecting to MySQL server @ localhost"); }
            //Set connection string
            connection.ConnectionString =
                "server=localhost;"
                + "database=lightmunnas;"
                + "uid=root;"
                + "password=redrun11;";
            //Connect
            connection.Open();
            if (flag) { Console.WriteLine("    >Connected"); }
        }

        public void disconnect()
        {
            try
            {
                connection.Dispose();
                connection.Close();
            }
            catch
            {

            }
        }

        public void userLogin(String userName) // on client connection
        {
            command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM users WHERE (username = '" + userName + "');";
            reader = command.ExecuteReader();

            if (reader.Read() != false)//Is the client name already in the database?
            {
                reader.Close(); //close reader

                command.CommandText = "UPDATE users SET lastlogin=NOW() WHERE username ='" + userName + "';";
                command.ExecuteNonQuery();
            }
            else //add username to the database
            {
                reader.Close(); //close reader

                Console.WriteLine("    >Clients first time connecting, adding to database");
                command.CommandText = "INSERT INTO users(username,lastlogin,sent) VALUES('" + userName + "',NOW(),0);";
                command.ExecuteNonQuery();
            }
        }

        public void createChannel(String name, String password)
        {
            command = connection.CreateCommand();
            command.CommandText = "INSERT INTO channels(channelname,channelpassword) VALUES('" + name + "','" + password + "');";
            command.ExecuteNonQuery();
        }

        public void newMessage(String userName, String msg)
        {
            command = connection.CreateCommand();
            command.CommandText = "SELECT id FROM users WHERE username='" + userName + "';";
            reader = command.ExecuteReader();
            reader.Read();
            String id = reader.GetValue(0).ToString(); // get id
            reader.Close();

            command.CommandText = "INSERT INTO log(userid,message,date) VALUES(" + id + ",'" + msg + "',NOW());";
            command.ExecuteNonQuery();
            command.CommandText = "UPDATE users SET sent = sent + 1 WHERE id=" + id + ";";
            command.ExecuteNonQuery();
        }

        public string getLog(String input)
        {
            try
            {
                String[] arguments = input.Split('-');

                String users = null;
                bool usersb = false;
                String date = null;
                bool dateb = false;

                foreach (string argument in arguments)
                {
                    if (argument.Substring(0, 1) == "u")
                    {
                        String[] s;
                        String noArg = argument.Substring(argument.IndexOf('u') + 1);
                        if (argument.Contains(','))
                        {

                            s = noArg.Split(',');
                            users = "users.username='" + s[0].Trim() + "'";

                            for (int i = 1; i < s.Length; i++)
                            {
                                users += " OR users.username='" + s[i].Trim() + "'";
                            }

                        }
                        else
                        {
                            users = "users.username='" + noArg.Trim() + "'";
                        }
                        usersb = true;

                    }
                    else if (argument.Substring(0, 1) == "d")
                    {
                        String noArg = argument.Substring(argument.IndexOf('d') + 1).Trim();
                        dateb = true;
                        if (argument.Contains(','))
                        {
                            String[] d;
                            int argCount = 0;
                            d = noArg.Split(',');
                            foreach (String dt in d)
                            {
                                if (dt.Contains('*'))
                                {
                                    String[] dtsplit = dt.Split('*');
                                    foreach (String dts in dtsplit)
                                    {
                                        dts.Trim();
                                    }
                                    if (argCount == 0)
                                    {
                                        date += "log.date BETWEEN CAST '" + dtsplit[0] + "00' AS DATETIME AND CAST'" + dtsplit[1] + "00' DATETIME";
                                        argCount++;
                                    }
                                    else
                                    {
                                        date += "OR log.date BETWEEN CAST '" + dtsplit[0] + "00' DATETIME AND CAST '" + dtsplit[1] + "00' DATETIME";
                                    }

                                }
                                else
                                {
                                    if (argCount == 0)
                                    {
                                        date += "log.date BETWEEN CAST '" + dt + "00' DATETIME AND DATE_ADD(CAST('" + d + "00' AS datetime), INTERVAL +5 MINUTE)";
                                        argCount++;
                                    }
                                    else
                                    {
                                        date += " OR log.date BETWEEN '" + dt + "00' AND DATE_ADD(CAST('" + d + "00' AS datetime), INTERVAL +5 MINUTE)";
                                    }
                                }
                            }
                        }
                        else
                        {
                            int argCount = 0;
                            String d = noArg;
                            if (d.Contains('*'))
                            {
                                String[] dtsplit = d.Split('*');
                                foreach (String dts in dtsplit)
                                {
                                    dts.Trim();
                                }
                                if (argCount == 0)
                                {
                                    date += "log.date BETWEEN '" + dtsplit[0] + "00' AND '20" + dtsplit[1] + "'";
                                    argCount++;
                                }
                                else
                                {
                                    date += "OR log.date BETWEEN '" + dtsplit[0] + "00' AND '20" + dtsplit[1] + "'";
                                }

                            }
                            else
                            {
                                if (argCount == 0)
                                {
                                    date += "log.date BETWEEN '" + d + "00' AND DATE_ADD(CAST('" + d + "00' AS datetime), INTERVAL +5 MINUTE)";
                                    argCount++;
                                }
                                else
                                {
                                    date += " OR log.date BETWEEN '" + d + "'00 AND DATE_ADD(CAST('" + d + "00' AS datetime), INTERVAL +5 MINUTE)";
                                }
                            }
                        }
                    }

                }
                String args = "SELECT users.username, log.message, log.date FROM log INNER JOIN users ON users.id=log.userid WHERE ";
                if (!usersb && dateb)
                {
                    args += date;
                }
                else if (usersb && !dateb)
                {
                    args += users;
                }
                else
                {
                    args += users + " AND " + date;
                }
                args += " ORDER BY log.date ASC;";



                String data = null;
                Console.WriteLine(args);
                Console.WriteLine();

                command = connection.CreateCommand();
                command.CommandText = args;
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    data += "(" + reader["date"].ToString() + ") <" + reader["username"].ToString() + "> " + reader["message"].ToString() + Environment.NewLine;
                }

                reader.Close();

                return (data);

            }
            catch (Exception ex)
            {
                Console.WriteLine("    >/log ERROR: " + ex.Message);
                return (null);

            }
        }

        public string getFileLog(String input)
        {
            try
            {
                String[] arguments = input.Split('-');

                String users = null;
                bool usersb = false;
                String date = null;
                bool dateb = false;

                foreach (string argument in arguments)
                {
                    if (argument.Substring(0, 1) == "u")
                    {
                        String[] s;
                        String noArg = argument.Substring(argument.IndexOf('u') + 1);
                        if (argument.Contains(','))
                        {

                            s = noArg.Split(',');
                            users = "users.username='" + s[0].Trim() + "'";

                            for (int i = 1; i < s.Length; i++)
                            {
                                users += " OR users.username='" + s[i].Trim() + "'";
                            }

                        }
                        else
                        {
                            users = "users.username='" + noArg.Trim() + "'";
                        }
                        usersb = true;

                    }
                    else if (argument.Substring(0, 1) == "d")
                    {
                        String noArg = argument.Substring(argument.IndexOf('d') + 1).Trim();
                        dateb = true;
                        if (argument.Contains(','))
                        {
                            String[] d;
                            int argCount = 0;
                            d = noArg.Split(',');
                            foreach (String dt in d)
                            {
                                if (dt.Contains('*'))
                                {
                                    String[] dtsplit = dt.Split('*');
                                    foreach (String dts in dtsplit)
                                    {
                                        dts.Trim();
                                    }
                                    if (argCount == 0)
                                    {
                                        date += "filelog.date BETWEEN CAST '" + dtsplit[0] + "00' AS DATETIME AND CAST'" + dtsplit[1] + "00' DATETIME";
                                        argCount++;
                                    }
                                    else
                                    {
                                        date += "OR filelog.date BETWEEN CAST '" + dtsplit[0] + "00' DATETIME AND CAST '" + dtsplit[1] + "00' DATETIME";
                                    }

                                }
                                else
                                {
                                    if (argCount == 0)
                                    {
                                        date += "filelog.date BETWEEN CAST '" + dt + "00' DATETIME AND DATE_ADD(CAST('" + d + "00' AS datetime), INTERVAL +5 MINUTE)";
                                        argCount++;
                                    }
                                    else
                                    {
                                        date += " OR filelog.date BETWEEN '" + dt + "00' AND DATE_ADD(CAST('" + d + "00' AS datetime), INTERVAL +5 MINUTE)";
                                    }
                                }
                            }
                        }
                        else
                        {
                            int argCount = 0;
                            String d = noArg;
                            if (d.Contains('*'))
                            {
                                String[] dtsplit = d.Split('*');
                                foreach (String dts in dtsplit)
                                {
                                    dts.Trim();
                                }
                                if (argCount == 0)
                                {
                                    date += "filelog.date BETWEEN '" + dtsplit[0] + "00' AND '20" + dtsplit[1] + "'";
                                    argCount++;
                                }
                                else
                                {
                                    date += "OR filelog.date BETWEEN '" + dtsplit[0] + "00' AND '20" + dtsplit[1] + "'";
                                }

                            }
                            else
                            {
                                if (argCount == 0)
                                {
                                    date += "filelog.date BETWEEN '" + d + "00' AND DATE_ADD(CAST('" + d + "00' AS datetime), INTERVAL +5 MINUTE)";
                                    argCount++;
                                }
                                else
                                {
                                    date += " OR filelog.date BETWEEN '" + d + "'00 AND DATE_ADD(CAST('" + d + "00' AS datetime), INTERVAL +5 MINUTE)";
                                }
                            }
                        }
                    }

                }
                String args = "SELECT users.username, filelog.filename, filelog.size, filelog.date FROM filelog INNER JOIN users ON users.id=filelog.userid WHERE ";
                if (!usersb && dateb)
                {
                    args += date;
                }
                else if (usersb && !dateb)
                {
                    args += users;
                }
                else
                {
                    args += users + " AND " + date;
                }
                args += " ORDER BY log.date ASC;";



                String data = null;
                Console.WriteLine(args);
                Console.WriteLine();

                command = connection.CreateCommand();
                command.CommandText = args;
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    data += "(" + reader["date"].ToString() + ") " + reader["username"].ToString() + " sent " + reader["filename"].ToString() + " which is " + reader["size"].ToString() + " bytes."  + Environment.NewLine;
                }

                reader.Close();

                return (data);

            }
            catch (Exception ex)
            {
                Console.WriteLine("    >/log ERROR: " + ex.Message);
                return (null);

            }
        }

        public void fileTransfer(String userName, String fileName, int fileSize)
        {
            command = connection.CreateCommand();
            command.CommandText = "SELECT id FROM users WHERE username='" + userName + "';";
            reader = command.ExecuteReader();
            reader.Read();
            String id = reader.GetValue(0).ToString(); // get id
            reader.Close();

            command = connection.CreateCommand();
            command.CommandText = "INSERT INTO filetransfers(userid,size,filename,time) VALUES('" + id + "','" + fileSize + "','" + fileName + "',NOW());";
            command.ExecuteNonQuery();
        }
    }
}