namespace Scripting
{
    public class AccountDatabaseMySQL : IAccountDatabase
    {
        class ActorInstance
        {
            public string Name;
            public string AccountName;
            public bool Banned;
            public bool GM;
            public int ID;
        }

        // MySql Settigns, second connection required since MySql does not support multiple DataReaders opened in a single connection.
        private MySqlConnection sql_con = null;
        private MySqlConnection sql_con2 = null;
        private string connString = "server=192.168.78.101;user=mike;database=rcprodb;port=3306;password=mike;";


        // Use this to speed up searches!
        static LinkedList<ActorInstance> SearchDB = new LinkedList<ActorInstance>();

        /// <summary>
        /// Initialize account database.
        /// </summary>
        public void Initialize(AccountAddEventHandler addDelegate)
        {
            using (sql_con = new MySqlConnection(connString))
            {
                sql_con.Open();
                MySqlCommand AcctCmd = new MySqlCommand("SELECT * FROM Accounts", sql_con);

                using (sql_con2 = new MySqlConnection(connString))
                {
                    sql_con2.Open();

                    using (MySqlDataReader dr = AcctCmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            string Username = dr.GetValue(0).ToString();
                            string Password = dr.GetValue(1).ToString();
                            string Email = dr.GetValue(2).ToString();
                            bool GM = (bool)dr.GetValue(3);
                            bool Banned = (bool)dr.GetValue(4);

                            MySqlCommand CharCmd = new MySqlCommand("SELECT * FROM Characters WHERE Username = @Username ORDER BY ID", sql_con2);
                            CharCmd.Parameters.AddWithValue("@Username", Username);

                            using (MySqlDataReader CharDR = CharCmd.ExecuteReader())
                            {
                                while (CharDR.Read())
                                {
                                    ActorInstance AI = new ActorInstance();
                                    AI.AccountName = Username;
                                    AI.Banned = Banned;
                                    AI.GM = GM;
                                    object CharacterID = CharDR.GetValue(1);
                                    AI.ID = int.Parse(CharacterID.ToString());
                                    AI.Name = (string)CharDR.GetValue(2);
                                    SearchDB.AddLast(AI);
                                }
                            }
                            addDelegate.Invoke(new AccountBase(Username, Password, Email, GM, Banned), true);
                        }
                    }
                }
            }
            sql_con2.Close();
            sql_con.Close();
        }


        /// <summary>
        /// Add an account to the account database
        /// </summary>
        public void Add(AccountBase account, AccountAddEventHandler completionDelegate)
        {
            // Check
            if (account == null)
            {
                completionDelegate.Invoke(account, false);
                return;
            }

            using (sql_con = new MySqlConnection(connString))
            {
                sql_con.Open();
                MySqlCommand NewAcct = new MySqlCommand("INSERT INTO Accounts (Username, Password, EMail, GM, Banned) VALUES (@Username,@Password,@EMail,@GM,@Banned)", sql_con);
                NewAcct.Parameters.AddWithValue("@Username", account.Username);
                NewAcct.Parameters.AddWithValue("@Password", account.Password);
                NewAcct.Parameters.AddWithValue("@EMail", account.Email);
                NewAcct.Parameters.AddWithValue("@GM", account.IsGM);
                NewAcct.Parameters.AddWithValue("@Banned", account.IsBanned);

                int numEffected = NewAcct.ExecuteNonQuery();
            }
            completionDelegate.Invoke(account, true);
            sql_con.Close();
        }

        /// <summary>
        /// Load an accounts actors.
        /// </summary>
        public void Load(AccountBase account, AccountLoadEventHandler completionDelegate)
        {
            // Check
            if (account == null)
            {
                completionDelegate.Invoke(account, null);
                return;
            }

            using (sql_con = new MySqlConnection(connString))
            {
                sql_con.Open();
                LoadCompleteEventArgs Args = new LoadCompleteEventArgs(account);
                MySqlCommand CharCmd = new MySqlCommand("SELECT * FROM Characters WHERE Username = @Username ORDER BY ID", sql_con);
                CharCmd.Parameters.AddWithValue("@Username", account.Username);

                using (MySqlDataReader CharDR = CharCmd.ExecuteReader())
                {
                    while (CharDR.Read())
                    {
                        byte[] SerializedAI = (byte[])CharDR.GetValue(3);
                        byte[] SerializedScripts = (byte[])CharDR.GetValue(4);
                        Args.ActorInstances.Add(new ActorInstanceData(SerializedAI, SerializedScripts));
                    }
                    CharDR.Close();
                }
                completionDelegate.Invoke(account, Args);
            }
            sql_con.Close();
        }

        /// <summary>
        /// Save an accounts actors.
        /// </summary>
        public void Save(AccountBase account, int index, ActorInstanceData data)
        {
            // Check
            if (account == null)
                return;

            // Setup output
            List<ActorInstanceData> ActorInstances = new List<ActorInstanceData>();

            byte Length = data.SerializedAI[4];

            String Name = ASCIIEncoding.ASCII.GetString(data.SerializedAI, 5, Length);

            if (data == null)
            {
                MySqlCommand CharCmd = new MySqlCommand("DELETE from Characters WHERE Name = @Username AND WHERE Index = @index", sql_con);
                CharCmd.Parameters.AddWithValue("@Username", account.Username);
                CharCmd.Parameters.AddWithValue("@index", index);


                int numEffected = CharCmd.ExecuteNonQuery();

                LinkedListNode<ActorInstance> AINode = SearchDB.First;
                while (AINode != null)
                {
                    ActorInstance AI = AINode.Value;

                    if (AI.ID == index && AI.AccountName == account.Username)
                    {
                        SearchDB.Remove(AINode);
                    }
                }

                return;
            }

            using (sql_con = new MySqlConnection(connString))
            {
                sql_con.Open();
                bool written = false;
                MySqlCommand CharCmd = new MySqlCommand("SELECT * FROM Characters WHERE ActorName = @Name", sql_con);
                CharCmd.Parameters.AddWithValue("@Name", Name);

                using (sql_con2 = new MySqlConnection(connString))
                {
                    sql_con2.Open();
                    using (MySqlDataReader CharDR = CharCmd.ExecuteReader())
                    {
                        while (CharDR.Read())
                        {
                            CharCmd = new MySqlCommand("UPDATE Characters SET Username = @Username, ID = @Index, ActorName = @Name, SerializedAI = @serializedAI, SerializedScripts = @serializedScripts WHERE ActorName = @Name", sql_con2);
                            CharCmd.Parameters.AddWithValue("@UserName", account.Username);
                            CharCmd.Parameters.AddWithValue("@Index", index);
                            CharCmd.Parameters.AddWithValue("@Name", Name);
                            CharCmd.Parameters.AddWithValue("@serializedAI", data.SerializedAI);
                            CharCmd.Parameters.AddWithValue("@serializedScripts", data.SerializedScripts);

                            CharCmd.ExecuteNonQuery();
                            written = true;
                        }
                    }

                    if (!written)
                    {
                        CharCmd = new MySqlCommand("Insert INTO Characters (Username, ID, ActorName, SerializedAI, SerializedScripts) VALUES(@UserName,@Index,@Name,@serializedAI,@serializedScripts)", sql_con2);
                        CharCmd.Parameters.AddWithValue("@UserName", account.Username);
                        CharCmd.Parameters.AddWithValue("@Index", index);
                        CharCmd.Parameters.AddWithValue("@Name", Name);
                        CharCmd.Parameters.AddWithValue("@serializedAI", data.SerializedAI);
                        CharCmd.Parameters.AddWithValue("@serializedScripts", data.SerializedScripts);

                        int numEffected = CharCmd.ExecuteNonQuery();
                    }
                }
            }
            sql_con2.Close();
            sql_con.Close();
        }

        /// <summary>
        ///
        /// </summary>
        public void Update()
        {

        }

        /// <summary>
        /// Perform a search for the given actor name to see if it exists.
        /// </summary>
        /// <param name="request">Handle of request object.</param>
        /// <param name="completionDelegate">Callback for completion of search.</param>
        public void OnActorInfoRequest(ActorInfoRequest request, AccountActorInfoRequestHandler completionDelegate)
        {
            foreach (ActorInstance AI in SearchDB)
            {
                if (AI.Name.Equals(request.ActorName, StringComparison.CurrentCultureIgnoreCase))
                {
                    completionDelegate.Invoke(request, AI.Name, AI.AccountName, AI.Banned, AI.GM);
                    return;
                }
            }

            // Failed!
            completionDelegate.Invoke(request, null, null, false, false);
        }

    }
}