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

using System.Data;

namespace ConsoleApplication1
{
    class IrcChannel{
        List<StalkWord> StalkWords{get;set;};
        string ChannelName{get;set;};

        public override string ToString(){
            string returnString = "";
            returnString+=ChannelName;
            for(int i = 0; i < StalkWords.Count; i++){
               if(i==0)returnString+="{";
               returnString+=StalkWords[i];
               if(i==0)returnString+="}";
            }
        }
    }

    class StalkWord{
        IrcChannel ircChannel;
        string Word{get;set;};
    }

    class Program
    {
        public static string SQLuser;
        public static string SQLpassword;
        public static List<IrcChannel> IrcChannels = new List<IrcChannel>();

        static void Main(string[] args)
        {
            readconfigfile();
            string connectionString = "Server=sql;" +
                "Database=u_chzz_stalkbot;" +
                "User ID=" + SQLuser + ";" +
                "Password=" + SQLpassword + ";" +
                "Persist Security Info=True;";

            MySqlConnection conn = new MySqlConnection(connectionString);
            conn.Open();

            string SQLStatement = "SELECT * FROM channel";
            MySqlDataAdapter ChanAdapter = new MySqlDataAdapter(SQLStatement, conn);
            DataTable dtResult = new DataTable();
            DataTable dtstalk = new DataTable();

            // Fill the DataTable with the result of the SQL statement
            ChanAdapter.Fill(dtResult);

            // Loop through all entries

            foreach (DataRow drRow in dtResult.Rows)
            {
                IrcChannel myChannel = new IrcChannel();
                myChannel.ChannelName = drRow["Name"].ToString();
                Console.WriteLine(myChannel.ChannelName);
                //now, for each channel, populate array of stalks
                SQLStatement = "SELECT text FROM chanstalk WHERE channel = '"+myChannel.ChannelName+"'";
                Console.WriteLine(SQLStatement);
                MySqlDataAdapter stalkAdapter = new MySqlDataAdapter(SQLStatement, conn);
                DataTable stResult = new DataTable();
                stalkAdapter.Fill(stResult);
                dtstalk.Rows.Add(stResult);
                foreach (DataRow stRow in stResult.Rows)
                {
                    StalkWord myStalkWord = new StalkWord();
                    myStalkWord.Word = stRow["text"].ToString();
                    Console.WriteLine("****"+myStalkWord.Word );
                    myChannel.StalkWords.Add(myStalkWord);
                }
                stalkAdapter.Dispose();
                IrcChannels.Add(myIrcChannel);
            }
            // We don't need the data adapter any more
            ChanAdapter.Dispose();
            conn.Close();
            conn.Dispose();
            Console.WriteLine("XXXXXXX");
         foreach (IrcChannel myChan in IrcChannels)
         {
             Console.WriteLine(myChan);
         }


        }
                static void readconfigfile()
        {
            Console.WriteLine("reading .my.cnf");
            StreamReader settingsreader = new StreamReader("/home/chzz/.my.cnf");
            try {
               settingsreader.ReadLine();
               SQLuser = settingsreader.ReadLine();
               SQLuser = SQLuser.Remove(0,SQLuser.IndexOf("=")+2);
               SQLpassword = settingsreader.ReadLine();
               SQLpassword = SQLpassword.Remove(0, SQLpassword.IndexOf("=") + 2);
              // SQLpassword = SQLpassword.Replace("\"","");
            }
            catch (Exception ex) {
                Console.WriteLine("Could not read .my.cnf for SQL user/password");
                Console.WriteLine(ex.Message);
            }

        }

    }
}