using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;//MySql Connector
using System.Data;
using System.IO;

namespace Items_SpellID_Checker
{
    class Program
    {
        const int advance = 100;
        const string DBCselect = "SELECT Id FROM dbc_spell WHERE Id = '";
        static string username = "";
        static string password = "";
        static string world_db = "";
        static string spell_db = "";
        static string LOG_FILE = "bugged items.txt";
        static string[] names = { "spellid_1", "spellid_2", "spellid_3", "spellid_4", "spellid_5" };
        static void Main(string[] args)
        {

            MySqlConnection ItConn = new MySqlConnection("Server=localhost;UserId="+username+";Password="+password+";Database="+world_db);
            ItConn.Open();
            MySqlCommand ItCommand = ItConn.CreateCommand();
            MySqlDataAdapter ItDAdap = new MySqlDataAdapter();
            DataSet ItDSet = new DataSet();
            ItDAdap.SelectCommand = ItCommand;
            MySqlConnection DBCConn = new MySqlConnection("Server=localhost;UserId=" + username + ";Password=" + password + ";Database=" + spell_db);
            DBCConn.Open();
            MySqlCommand DBCCommand = DBCConn.CreateCommand();
            MySqlDataAdapter DBCDAdap = new MySqlDataAdapter();
            DBCDAdap.SelectCommand = DBCCommand;
            DataSet DBCDSet = new DataSet();
            for (int i = 0; i < names.Length; i++)
            {
                int counter = 0;
                Log("Checking " + names[i]);
                for (; ; )
                {
                    ItCommand.CommandText = "SELECT entry, " + names[i] + " FROM items " +
                        "WHERE " + names[i] + " != '0' " +
                        " ORDER BY entry " +
                        "LIMIT " + counter + "," + advance + ";";

                    ItDSet.Clear();
                    ItDAdap.Fill(ItDSet);
                    if (ItDSet.Tables[0].Rows.Count == 0)
                        break;

                    foreach (DataRow item in ItDSet.Tables[0].Rows)
                    {
                        DBCCommand.CommandText = DBCselect + item[i+1] + "';";
                        DBCDSet.Clear();
                        DBCDAdap.Fill(DBCDSet);
                        if (DBCDSet.Tables[0].Rows.Count == 0)
                        {
                            Log("ItemID = " + item[0] + " has an inexistent " + names[i] + " = " + item[i+1]);
                        }
                    }
                    counter += advance;
                }
                Log("");
            }
            Console.WriteLine("Finished");
            ItConn.Close();
            DBCConn.Close();
            Console.Read();
        }
        public static void Log(String s)
        {
            StreamWriter log;
            try
            {
                if (!File.Exists(LOG_FILE))
                    log = File.CreateText(LOG_FILE);
                else
                {
                    log = File.AppendText(LOG_FILE);
                    log.Write("\n");
                }
                log.Write(s);
                log.Close();
                Console.WriteLine(s);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }
    }
}