using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using System.Data;

namespace SebBot_3
{
    static class SQL
    {
        // *************************************************************************************
        // MAIN REF
        public static Frm_MAIN MAIN;

        // *************************************************************************************
        // public

        static public string ServerIp
        {
            get
            {
                return m_ServerIp;
            }
            set
            {
                m_ServerIp = value;
            }
        }

        static public bool DB_Available
        {
            get
            {
                return m_DB_Available;
            }
            set
            {
                m_DB_Available = value;
            }
        }

        // *************************************************************************************
        // private
        static private MySqlConnection _myConnection;
        static private string m_ServerIp;
        static private bool m_DB_Available;

        // *************************************************************************************
        // enum
        public enum eEventType
        {
            JOIN = 0,
            PART = 1,
            QUIT = 2,
            KICK = 3,
            BAN = 4,
            PRIVMSG = 10,
            ACTION = 11,
            NOTICE = 12,
            CTCP = 13,
            NICK = 20,
            MODE = 30
        };



        // *************************************************************************************
        // constructor
        static SQL()
        {

        }

        // *************************************************************************************
        // Connect
        static public void Connect()
        {
            try
            {
                _myConnection = new MySqlConnection("datasource=" + "192.168.1.250" + ";username=" + "root" + ";password=" + "sl1107" + ";database=" + "db_BOT");
                _myConnection.Open();

                if (_myConnection.State == System.Data.ConnectionState.Open)
                {
                    DB_Available = true;
                }
            }
            catch (Exception ex)
            {
                DB_Available = false;
                MessageBox.Show(ex.Message);
            }
        }

        static public void Disconnect()
        {
            try
            {
                _myConnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        // *************************************************************************************
        // Add Event
        static public void AddEvent(eEventType evt,string nick, string ident, string host, string message, string info)
        {
            string listField = string.Empty;
            string listValue = string.Empty;
            try
            {
                if (_myConnection.State != System.Data.ConnectionState.Open && DB_Available) return;

                if (!string.IsNullOrEmpty(message) && message.Contains("'"))
                {
                    message = message.Replace("'", " ");
                }

                listField = "dt_event";
                listField += ",s_event_type";
                if (!string.IsNullOrEmpty(nick)) listField += ",s_nick";
                if (!string.IsNullOrEmpty(ident)) listField += ",s_ident";
                if (!string.IsNullOrEmpty(host)) listField += ",s_host";
                if (!string.IsNullOrEmpty(message)) listField += ",s_message";
                if (!string.IsNullOrEmpty(info)) listField += ",s_infos";

                listValue = "'" + GetDateTimeNow() + "'";
                listValue += ",'" + evt.ToString() + "'";
                if (!string.IsNullOrEmpty(nick)) listValue += ",'" + nick + "'";
                if (!string.IsNullOrEmpty(ident)) listValue += ",'" + ident + "'";
                if (!string.IsNullOrEmpty(host)) listValue += ",'" + host + "'";
                if (!string.IsNullOrEmpty(message))  listValue += ",'" + message + "'";
                if (!string.IsNullOrEmpty(info)) listValue += ",'" + info + "'";

                string Query_Sql = "INSERT INTO " + "tb_events" + " (" + listField + ") VALUES (" + listValue + ")";
                MySqlCommand myCmd = new MySqlCommand(Query_Sql);

                myCmd.Connection = _myConnection;

                myCmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                //MessageBox.Show(myex.Message);
                //DB_Available = false;
            }
        }


        // *************************************************************************************
        // DB Info
        static public string GetDB_Size()
        {
            string s = string.Empty;
            int nb = 0;
            ulong tbl = 0;
            ulong tblmx = 0;
            float tbsz = 0.0f;
            float tbprc = 0.0f;

            try
            {
                if (_myConnection.State != System.Data.ConnectionState.Open) return "Base de donnée non-connectée.";

                MySqlCommand myCmd = new MySqlCommand("SELECT COUNT(*) FROM tb_events", _myConnection);
                MySqlDataReader myReader = myCmd.ExecuteReader();
                while (myReader.Read() != false)
                {
                    nb = myReader.GetInt32(0);
                }
                myReader.Close();


                myCmd = new MySqlCommand("SELECT data_length, Max_data_length FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'tb_events'", _myConnection);
                myReader = myCmd.ExecuteReader();
                while (myReader.Read() != false)
                {
                    //tbl = myReader.GetInt32(0);
                    if (myReader.FieldCount == 2)
                    {
                        tbl = myReader.GetUInt64("data_length");
                        tblmx = myReader.GetUInt64("Max_data_length");
                    }
                }

                myReader.Close();

                tbsz =((float)tbl / 1000000.0f);

                tbprc = (tbsz / 50000.0f) * 100.0f;

                s = IRCCodes.K(8,1) + " " + nb.ToString() + IRCCodes.K(0,1) + " enregistrements dans la base de donnée, sa taille est de"+IRCCodes.K(8,1) + " " + tbsz.ToString("0.00") + " Mb"+ IRCCodes.K(0,1)+  " ("+ IRCCodes.K(8,1) + " " +tbprc.ToString("0.000") + " %" + IRCCodes.K(0,1) + " )" ;

                return s;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                DB_Available = false;
                return "data n/a";
            }
        }

        static public void GetDB_Seen(string for_nick, string arg)
        {
            try
            {
                if (_myConnection.State != System.Data.ConnectionState.Open)
                {
                    IRC_Connections.MASTER_Notice_To_Nick(for_nick, "Base de donnée non-connectée.");
                    return;
                }

                string sarg;
                string query;
                if (arg.Contains("."))
                {
                    query = "Select * from tb_events WHERE s_host = '" + arg + "' ORDER BY dt_event DESC";
                }
                else
                {
                    query = "Select * from tb_events WHERE s_nick = '" + arg + "'";
                }

                string ret_seen = "je ne sais pas.";

                MySqlDataAdapter adapter = new MySqlDataAdapter(query, _myConnection);
                DataSet ds = new DataSet("RES");
                adapter.Fill(ds);


                string s_host;
                string s_nick;
                string s_dt_evt;
                string s_type_evt;

                int nb = ds.Tables[0].Rows.Count;
                if (nb == 0)
                {
                    IRC_Connections.MASTER_Notice_To_Nick(for_nick, "0 Infos sur " + arg);
                    return;
                }
                if (nb > 3)
                {
                    nb = 3;
                }
                for (int i = 0; i < nb; i++)
                {
                    s_host = ds.Tables[0].Rows[i]["s_host"].ToString();
                    s_nick = ds.Tables[0].Rows[i]["s_nick"].ToString();
                    s_dt_evt = ds.Tables[0].Rows[i]["dt_event"].ToString();
                    s_type_evt = ds.Tables[0].Rows[i]["s_event_type"].ToString();

                    ret_seen = IRCCodes.K(0, 1) + "(" + IRCCodes.K(8, 1) + s_nick;
                    ret_seen += IRCCodes.K(0, 1) + ") a été vue le " + IRCCodes.K(8, 1) + " " + s_dt_evt;
                    ret_seen += IRCCodes.K(0, 1) + " avec le host " + IRCCodes.K(8, 1) + " " + s_host;
                    ret_seen += IRCCodes.K(0, 1) + " et cetait un (" + IRCCodes.K(11, 1) + " " + s_type_evt + IRCCodes.K(0, 1) + " )";
                    IRC_Connections.MASTER_Notice_To_Nick(for_nick, ret_seen);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                IRC_Connections.MASTER_Notice_To_Nick(for_nick, "Infos non-disponible");
            }
        }

        // *************************************************************************************
        // Get Date Time ( timestamp )
        static public string GetDateTimeNow()
        {
            return DateTime.Now.Year.ToString("0000") + "-" + DateTime.Now.Month.ToString("00") + "-" + DateTime.Now.Day.ToString("00") + " " + DateTime.Now.Hour.ToString("00") + ":" + DateTime.Now.Minute.ToString("00") + ":" + DateTime.Now.Second.ToString("00");
        }

    }
}