using System;
using System.Collections;
using System.Data;
using MySql.Data.MySqlClient;

namespace ReleaseMule
{
    public class Mysql
    {


        #region Constants

        public static string host = "HOST";
        public const string user = "USER";
        public const string pass = "PASS";
        public const string DupeDataBase = "JUPE";
        public const string dupetable = "releases";
        public const string nuketable = "nukes";
        public static string connectstring = "Data Source="
            + host + ";UserName="
            + user + ";Password="
            + pass + ";Persist Security Info=yes";
        public static string Dupeconstring = "DataSource="
                    + host + ";UserName="
                    + user + ";Password="
                    + pass + ";Database=" + DupeDataBase + ";Persist Security Info=yes";

        public const string showDupeQuery = "SELECT * FROM releases WHERE name LIKE '%";
        public const string showReleasesQuery = "SELECT name from releases";
        public const string showReleasesCountQuery = "SELECT COUNT(name) from releases";
        public const string showReleasesQueryAll = "SELECT name,section,time,date from releases";
        public const string showNukesCountQuery = "SELECT COUNT(name) from nukes";
        public const int releasesfieldscount = 4;

        public const string releasesSchema = "(name varchar(512) NOT NULL PRIMARY KEY,date varchar(512),time varchar(255),section varchar(512))";
        public const string nukesSchema = "(name varchar(512) NOT NULL PRIMARY KEY,date varchar(512),time varchar(255),section varchar(512),reason varchar(512))";

        public string ExecuteQueryReturnString(string Q, string connString)
        {
            string queryresults = "";
            try
            {
                MySqlConnection myConn = new MySqlConnection(connString);
                MySqlCommand myCommand = new MySqlCommand(Q, myConn);
                myCommand.Connection.Open();
                MySqlDataReader myReader =
                    myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                while (myReader.Read())
                {
                    for (int o = 0; o < myReader.FieldCount; o++)
                        if (myReader.GetString(o) != null)
                            queryresults = (myReader.GetString(o));
                        else
                            queryresults = "NULL";
                }
                myReader.Close();
                myConn.Close();

                return queryresults;
            }
            catch (Exception)
            {
                queryresults = "Not Found";

                return queryresults;

            }
        }



        public ArrayList ExecuteQuery(string Q, string connString,bool encrypt)
        {
            ArrayList queryresults = new ArrayList();
            try
            {
                MySqlConnection myConn = new MySqlConnection(connString);
                MySqlCommand myCommand = new MySqlCommand(Q, myConn);
                myCommand.Connection.Open();
                MySqlDataReader myReader =
                    myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                while (myReader.Read())
                {
                    for (int o = 0; o < myReader.FieldCount; o++)
                        if (myReader.GetString(o) != null)
                        {
                            if (encrypt)
                                queryresults.Add(Encryption.decrypttxt(myReader.GetString(o)));
                            else
                                queryresults.Add(myReader.GetString(o));
                        }
                        else
                            queryresults.Add("NULL");
                }
                myReader.Close();
                myConn.Close();
                return queryresults;
            }
            catch (Exception ex)
            {
                Console.WriteLine("eek , Mysql Error Bollox get some mofo to fix");
                queryresults.Add("EXCEPTION Nothing Found :" + ex);
                return queryresults;
            }
        }



        public ArrayList QueryDupeCheck(string todupe, string constring)
        {
            ArrayList queryresults = new ArrayList();
            try
            {
                string query = showDupeQuery + todupe + "%';";
                MySqlConnection myConn = new MySqlConnection(constring);
                MySqlCommand myCommand = new MySqlCommand(query, myConn);
                myCommand.Connection.Open();
                MySqlDataReader myReader =
                    myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                while (myReader.Read())
                {
                    for (int o = 0; o < myReader.FieldCount; o++)
                        queryresults.Add(Encryption.decrypttxt(myReader.GetString(o)));
                }
                myReader.Close();
                myConn.Close();
                return queryresults;
            }
            catch (Exception ex)
            {
                queryresults.Add("Nothing Found :" + ex);
                return queryresults;
            }
        }
        public bool dupecheck(string release,bool show)
        {

            try
            {
                release = Encryption.encrypttxt(release);
                string query = "SELECT name FROM releases WHERE name LIKE '%" + release + "%';";
                MySqlConnection myConn = new MySqlConnection(Dupeconstring);
                MySqlCommand myCommand = new MySqlCommand(query, myConn);
                myCommand.Connection.Open();
                MySqlDataReader myReader =
                    myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                while (myReader.Read())
                {
                    for (int o = 0; o < myReader.FieldCount; o++)
                    {
                        if (myReader.GetString(o) == release)
                        {
                            if (show)
                            {
                                Console.WriteLine(myReader.GetString(o));
                            }
                            return true;
                        }
                        else
                        {
                            if (show)
                            {
                                if (myReader.GetString(o).Contains(release))
                                {
                                    Console.WriteLine(release + " duped with " + myReader.GetString(o));
                                }
                            }
                            else
                            {
                                Console.WriteLine("DUPPPPPPPE");
                                return false;
                            }
                        }
                    }
                }
                // fuckup
                Console.WriteLine("Ran Through");
                return false;

            }
            catch (Exception)
            {
                return false;
            }

            //  return false;
        }

        // public const string showDupeQuery = "SELECT * FROM releases WHERE name LIKE '%";
        public string CreateTable(string tablename, string fields, string constring)
        {

            MySqlConnection conDatabase = new MySqlConnection(constring);
            MySqlCommand cmdDatabase = new MySqlCommand("CREATE TABLE " + tablename + " " + fields, conDatabase);
            conDatabase.Open();
            try
            {
                cmdDatabase.ExecuteNonQuery();
            }
            catch (Exception f)
            {
                string errormsg = "Exception Caused because :" + f;

                conDatabase.Close();
                return (tablename + " Table Not Created: already exists");
            }
            conDatabase.Close();
            return (tablename + " Table Created");
        }

        public string DeleteTable(string tablename, string constring)
        {
            MySqlConnection conDatabase = new MySqlConnection(constring);
            MySqlCommand cmdDatabase = new MySqlCommand("DROP TABLE " + tablename, conDatabase);
            conDatabase.Open();
            try
            {
                cmdDatabase.ExecuteNonQuery();
            }
            catch (Exception f)
            {
                string errormsg = "Exception Caused because :" + f;

                conDatabase.Close();
                return ("Table " + tablename + " Not Deleted: it does not exist");
            }
            conDatabase.Close();
            return ("Table " + tablename + "Deleted");
        }

        public string CreateDb(string database)
        {
            MySqlConnection conDatabase = new MySqlConnection(connectstring);
            MySqlCommand cmdDatabase = new MySqlCommand("CREATE DATABASE " + database, conDatabase);
            conDatabase.Open();
            try
            {
                cmdDatabase.ExecuteNonQuery();
            }
            catch (Exception f)
            {
                string errormsg = "Exception Caused because :" + f;

                conDatabase.Close();
                return ("DataBase : " + database + " Not Created: Already Exists");
            }
            conDatabase.Close();
            return ("DataBase :" + database + " Created");
        }
       public string DeleteDb(string database)
        {
            MySqlConnection conDatabase = new MySqlConnection(connectstring);
            MySqlCommand cmdDatabase = new MySqlCommand("DROP DATABASE " + database, conDatabase);
            conDatabase.Open();
            try
            {
                cmdDatabase.ExecuteNonQuery();
            }
            catch (Exception f)
            {
                string errormsg = "Exception Caused because :" + f;

                conDatabase.Close();
                return ("DataBase : " + database + " not deleted :" + errormsg);
            }
            conDatabase.Close();
            return ("DataBase : " + database + " Deleted");
        }


        public string AddRelease(string release, string section, string time, string date)
        {
            string encrypted_release = Encryption.encrypttxt(release);
            string encrypted_section = Encryption.encrypttxt(section);
            string encrypted_time = Encryption.encrypttxt(time);
            string encrypted_date = Encryption.encrypttxt(date);
            release = encrypted_release;
            date = encrypted_date;
            section = encrypted_section;
            time = encrypted_time;
            MySqlConnection conDatabase = new MySqlConnection(Dupeconstring);


            MySqlCommand cmdDatabase =
                new MySqlCommand(
                    "INSERT INTO " + "releases " + "(name,date,time,section) values (\"" + release + "\",\"" + date +
                    "\",\"" + time + "\",\" " + section + "\");", conDatabase);

            conDatabase.Open();
            try
            {
                cmdDatabase.ExecuteNonQuery();
            }
            catch (Exception f)
            {
                string errormsg = "Exception Caused because :" + f;

                conDatabase.Close();
                return ("Entry Not Created: " + errormsg);
            }
            conDatabase.Close();
            return ("Entry Created");
        }

        //"(name varchar(255) NOT NULL PRIMARY KEY,affilsites varchar(255),sections varchar(255))";

        public void InitDb()
        {
            // CreateDb
           Console.WriteLine(CreateDb(DupeDataBase));
            // CreateTables
            Console.WriteLine(CreateTable("releases", releasesSchema, Dupeconstring));
            Console.WriteLine(CreateTable("nukes", nukesSchema, Dupeconstring));

        }
        public void DestoryDb()
        {
            Console.WriteLine(DeleteTable("releases", Dupeconstring));
            Console.WriteLine(DeleteTable("nukes", Dupeconstring));
            Console.WriteLine(DeleteDb(DupeDataBase));


        }








        #endregion

    }
}