using System;
using System.Threading;
using System.Data.SqlClient;

namespace FilterTimers.Timers
{
    internal class DBCheckerTimer
    {
        #region Timer Variables

        private Int32 Interval;

        #endregion

        private Boolean IsDone = true;

        public DBCheckerTimer(Int32 I)
        {
            Interval = I;

            Thread NewThread = new Thread(delegate ()
            {
                while (true)
                {
                    Thread.Sleep(Interval * 1000);

                    if (IsDone) { Run(); }
                }
            });

            NewThread.Start();
        }

        private void Run()
        {
            IsDone = false;

            try
            {
                using (SqlCommand Command = Program.SQL.Connection.CreateCommand())
                {
                    using (SqlCommand Command2 = Program.SQL.Connection.CreateCommand())
                    {
                        Command.CommandText = String.Format("SELECT nItemKey FROM {0}..tItemMobList", Program.Auth.ConfigValues["CharacterDB"]);

                        using (SqlDataReader Reader = Command.ExecuteReader())
                        {
                            Int32 DeletedtItemMobLists = 0;

                            while (Reader.Read())
                            {
                                Command2.CommandText = String.Format("SELECT COUNT(*) FROM {0}..tItem WHERE nItemKey = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nItemKey"]);

                                Byte nItemKeyCount = Convert.ToByte(Command2.ExecuteScalar());

                                if (nItemKeyCount == 0)
                                {
                                    Command2.CommandText = String.Format("DELETE FROM {0}..tItemMobList WHERE nItemKey = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nItemKey"]);

                                    DeletedtItemMobLists += Command2.ExecuteNonQuery();
                                }
                            }

                            Program.SendConsoleText(ConsoleColor.Green, "Cleaned up {0} tItemMobLists.", DeletedtItemMobLists);
                        }

                        Command.CommandText = String.Format("SELECT DISTINCT nItemKey FROM {0}..tItemOptions", Program.Auth.ConfigValues["CharacterDB"]);

                        using (SqlDataReader Reader = Command.ExecuteReader())
                        {
                            Int32 DeletedtItemOptions = 0;

                            while (Reader.Read())
                            {
                                Command2.CommandText = String.Format("SELECT COUNT(*) FROM {0}..tItem WHERE nItemKey = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nItemKey"]);

                                Byte nItemKeyCount = Convert.ToByte(Command2.ExecuteScalar());

                                if (nItemKeyCount == 0)
                                {
                                    Command2.CommandText = String.Format("DELETE FROM {0}..tItemOptions WHERE nItemKey = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nItemKey"]);

                                    DeletedtItemOptions += Command2.ExecuteNonQuery();
                                }
                            }

                            Program.SendConsoleText(ConsoleColor.Green, "Cleaned up {0} tItemOptions.", DeletedtItemOptions);
                        }

                        Command.CommandText = String.Format("SELECT nNo FROM {0}..tGuild", Program.Auth.ConfigValues["CharacterDB"]);

                        using (SqlDataReader Reader = Command.ExecuteReader())
                        {
                            Int32 DeletedGuilds = 0;

                            while (Reader.Read())
                            {
                                Command2.CommandText = String.Format("SELECT COUNT(*) FROM {0}..tGuildMember WHERE nNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nNo"]);

                                Int32 nCharNoCount = Convert.ToInt32(Command2.ExecuteScalar());

                                if (nCharNoCount == 0)
                                {
                                    Command2.CommandText = String.Format("DELETE FROM {0}..tGuild WHERE nNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nNo"]);
                                    Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tGuildAcademy WHERE nNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nNo"]);
                                    Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tGuildAcademyMember WHERE nNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nNo"]);
                                    Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tGuildAcademyRanking WHERE nGuildNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nNo"]);
                                    Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tGuildMember WHERE nNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nNo"]);
                                    Command2.ExecuteNonQuery();

                                    DeletedGuilds += 1;
                                }
                            }

                            Program.SendConsoleText(ConsoleColor.Green, "Cleaned up {0} Guilds.", DeletedGuilds);
                        }

                        Command.CommandText = String.Format("SELECT nCharNo, sID, dCreateDate, dDeletedDate FROM {0}..tCharacter", Program.Auth.ConfigValues["CharacterDB"]);

                        using (SqlDataReader Reader = Command.ExecuteReader())
                        {
                            Int32 DeletedtCharacterChargedBuffers = 0;
                            Int32 DeletedtCharacterOptions = 0;
                            Int32 DeletedtCharacterShapes = 0;
                            Int32 DeletedtCharacterSingleOptions = 0;
                            Int32 DeletedtCharacterStates = 0;
                            Int32 DeletedtCharacterTitles = 0;
                            Int32 DeletedtChatBlocks = 0;
                            Int32 DeletedtFriends = 0;
                            Int32 DeletedtGuildMembers = 0;
                            Int32 DeletedtHolyPromises = 0;
                            Int32 DeletedtHolyPromiseRewards = 0;
                            Int32 DeletedtItems = 0;
                            Int32 DeletedtMarriages = 0;
                            Int32 DeletedtQuests = 0;
                            Int32 DeletedtSkills = 0;
                            Int32 DeletedtCharacters = 0;

                            while (Reader.Read())
                            {
                                if (Convert.ToDateTime(Reader["dCreateDate"]) != Convert.ToDateTime(Reader["dDeletedDate"]) && Convert.ToDateTime(Reader["dDeletedDate"]) < DateTime.Now.AddDays(-7) && Convert.ToString(Reader["sID"]).Contains("~"))
                                {
                                    Command2.CommandText = String.Format("DELETE FROM {0}..tCharacterChargedBuffer WHERE nCharNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nCharNo"]);

                                    DeletedtCharacterChargedBuffers += Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tCharacterOptions WHERE nCharNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nCharNo"]);

                                    DeletedtCharacterOptions += Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tCharacterShape WHERE nCharNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nCharNo"]);

                                    DeletedtCharacterShapes += Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tCharacterSingleOption WHERE nCharNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nCharNo"]);

                                    DeletedtCharacterSingleOptions += Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tCharacterState WHERE nCharNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nCharNo"]);

                                    DeletedtCharacterStates += Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tCharacterTitle WHERE nCharNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nCharNo"]);

                                    DeletedtCharacterTitles += Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tChatBlock WHERE nCharNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nCharNo"]);

                                    DeletedtChatBlocks += Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tFriend WHERE nCharNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nCharNo"]);

                                    DeletedtFriends += Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tFriend WHERE nFriendCharNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nCharNo"]);

                                    DeletedtFriends += Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tGuildMember WHERE nCharNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nCharNo"]);

                                    DeletedtGuildMembers += Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tHolyPromise WHERE nCharNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nCharNo"]);

                                    DeletedtHolyPromises += Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tHolyPromiseReward WHERE nCharNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nCharNo"]);

                                    DeletedtHolyPromiseRewards += Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tItem WHERE nStorageType != '0' AND nStorageType != '6' AND nOwner = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nCharNo"]);

                                    DeletedtItems += Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tMarriage WHERE nCharNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nCharNo"]);

                                    DeletedtMarriages += Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tMarriage WHERE nPartnerCharNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nCharNo"]);

                                    DeletedtMarriages += Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tQuest WHERE nCharNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nCharNo"]);

                                    DeletedtQuests += Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tSkill WHERE nCharNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nCharNo"]);

                                    DeletedtSkills += Command2.ExecuteNonQuery();

                                    Command2.CommandText = String.Format("DELETE FROM {0}..tCharacter WHERE nCharNo = '{1}'", Program.Auth.ConfigValues["CharacterDB"], Reader["nCharNo"]);

                                    DeletedtCharacters += Command2.ExecuteNonQuery();
                                }
                            }

                            Program.SendConsoleText(ConsoleColor.Green, "Cleaned up {0} tCharacterChargedBuffers.", DeletedtCharacterChargedBuffers);
                            Program.SendConsoleText(ConsoleColor.Green, "Cleaned up {0} tCharacterOptions.", DeletedtCharacterOptions);
                            Program.SendConsoleText(ConsoleColor.Green, "Cleaned up {0} tCharacterShapes.", DeletedtCharacterShapes);
                            Program.SendConsoleText(ConsoleColor.Green, "Cleaned up {0} tCharacterSingleOptions.", DeletedtCharacterSingleOptions);
                            Program.SendConsoleText(ConsoleColor.Green, "Cleaned up {0} tCharacterStates.", DeletedtCharacterStates);
                            Program.SendConsoleText(ConsoleColor.Green, "Cleaned up {0} tCharacterTitles.", DeletedtCharacterTitles);
                            Program.SendConsoleText(ConsoleColor.Green, "Cleaned up {0} tChatBlocks.", DeletedtChatBlocks);
                            Program.SendConsoleText(ConsoleColor.Green, "Cleaned up {0} tFriends.", DeletedtFriends);
                            Program.SendConsoleText(ConsoleColor.Green, "Cleaned up {0} tGuildMembrs.", DeletedtGuildMembers);
                            Program.SendConsoleText(ConsoleColor.Green, "Cleaned up {0} tHolyPromises.", DeletedtHolyPromises);
                            Program.SendConsoleText(ConsoleColor.Green, "Cleaned up {0} tHolyPromiseRewards.", DeletedtHolyPromiseRewards);
                            Program.SendConsoleText(ConsoleColor.Green, "Cleaned up {0} tItems.", DeletedtItems);
                            Program.SendConsoleText(ConsoleColor.Green, "Cleaned up {0} tMarriages.", DeletedtMarriages);
                            Program.SendConsoleText(ConsoleColor.Green, "Cleaned up {0} tQuests.", DeletedtQuests);
                            Program.SendConsoleText(ConsoleColor.Green, "Cleaned up {0} tSKills.", DeletedtSkills);
                            Program.SendConsoleText(ConsoleColor.Green, "Cleaned up {0} tCharacters.", DeletedtCharacters);
                        }
                    }
                }
            }
            catch (Exception Error) { Program.SendConsoleText(ConsoleColor.Red, "Timers error - Database checker\n{0}", Error.ToString()); }

            IsDone = true;
        }
    }
}