// ==========================================================================
// Author:  Yee Hsu
// Date:    8/5/2008
// File:    Database.cpp
//
// Desc:    Demonstrates use of databases and SQL queries in C# MySQL API
// ==========================================================================

using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using CommonLib;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace Controller
{
    public class Database
    {
        private MySQL mysql = null;

        public bool Connect(DBInformation dbinfo)
        {
            this.mysql = new MySQL();

            return this.mysql.Connect(dbinfo);
        }

        public void Disconnect()
        {
            this.mysql.Disconnect();
        }

        public Dictionary<int, Dictionary<int, UpdateBlock>> GetMapUpdates()
        {
            MySqlDataReader myResultSet = null;

            try
            {
                if (this.mysql != null)
                {
                    string sSQL = "SELECT updateid FROM uidtable GROUP BY updateid;";
                    myResultSet = this.mysql.ReturnExecutedQuery(sSQL);

                    List<int> lUpdateId = new List<int>();
                    Dictionary<int, Dictionary<int, UpdateBlock>> mapUpdates = new Dictionary<int, Dictionary<int, UpdateBlock>>();

                    // add updates to process in list
                    while (myResultSet.Read())
                    {
                        int nUpdateId = int.Parse(myResultSet.GetValue(0).ToString());
                        lUpdateId.Add(nUpdateId);
                    }
                    myResultSet.Close();

                    // now lets process list of update ids
                    foreach (int nId in lUpdateId)
                    {
                        Dictionary<int, UpdateBlock> upBlock = this.GetUpdateHistIdBlock(nId);

                        if (upBlock != null)
                            mapUpdates.Add(nId, upBlock);
                    }
                    return mapUpdates;
                }
            }
            catch (Exception e)
            {
                Common.SendEmail("Exception!", e.ToString());
                myResultSet.Close();
            }
            return null;
        }

        public Dictionary<int, UpdateBlock> GetUpdateHistIdBlock(int nUpdateId)
        {
            MySqlDataReader myResultSet = null;

            try
            {
                if (this.mysql != null)
                {
                    string sSQL = String.Format("SELECT updatehistid, idcount FROM uidtable WHERE updateid = '{0}' ORDER BY idcount DESC LIMIT {1};", 
                        nUpdateId.ToString(), Configuration.nNumHistories);
                    myResultSet = this.mysql.ReturnExecutedQuery(sSQL);

                    List<string> lUpdateHistId = new List<string>();
                    Dictionary<int, UpdateBlock> mUpdateBlock = new Dictionary<int, UpdateBlock>();

                    // add updatehistid to process in list
                    while (myResultSet.Read())
                    {
                        string sUpdateHistId = myResultSet.GetValue(0).ToString();
                        lUpdateHistId.Add(sUpdateHistId);
                    }
                    myResultSet.Close();

                    // now lets process list of updatehist ids
                    int i = 1;

                    foreach (string sUpHistId in lUpdateHistId)
                    {
                        UpdateBlock ub = this.GetUpdateBlock(sUpHistId);

                        if (ub != null)
                            mUpdateBlock.Add(i++, ub);
                    }
                    return mUpdateBlock;
                }
            }
            catch (Exception e)
            {
                Common.SendEmail("Exception!", e.ToString());
                myResultSet.Close();
            }
            return null;
        }

        public UpdateBlock GetUpdateBlock(string sUpdateHistId)
        {
            MySqlDataReader myResultSet = null;

            try
            {
                if (this.mysql != null)
                {
                    string sSQL = String.Format("SELECT UNIX_TIMESTAMP(timestamp), defdate, defversion, defsignature, engineversion FROM deftable WHERE updatehistid = '{0}';", sUpdateHistId);
                    myResultSet = this.mysql.ReturnExecutedQuery(sSQL);

                    UpdateBlock upBlock = new UpdateBlock();
                    upBlock.diDefinitionInfo = null;
                    upBlock.fiFixInfo = null;
                    bool bHasInfo = false;

                    // add updatehistid to process in list
                    if (myResultSet.Read())
                    {
                        upBlock.diDefinitionInfo = new DefInformation();

                        upBlock.diDefinitionInfo.nTimeStamp = int.Parse(myResultSet.GetValue(0).ToString());
                        upBlock.diDefinitionInfo.sDefVersion = myResultSet.GetValue(2).ToString();
                        upBlock.diDefinitionInfo.sDefSignature = myResultSet.GetValue(3).ToString();
                        upBlock.diDefinitionInfo.sEngineVersion = myResultSet.GetValue(4).ToString();
                        upBlock.diDefinitionInfo.sUpdateHistId = sUpdateHistId;

                        // for some reason, defdate value is kinda different, lets get the correct one
                        string[] sDefDate = myResultSet.GetValue(1).ToString().Split(' ');
                        upBlock.diDefinitionInfo.sDefDate = sDefDate[0];

                        if (upBlock.diDefinitionInfo.sDefDate.Length > 0)
                            bHasInfo = true;
                    }
                    myResultSet.Close();

                    if (bHasInfo)
                    {
                        // now get patch links
                        sSQL = String.Format("SELECT * FROM urllinktable WHERE updatehistid = '{0}' LIMIT 1", sUpdateHistId);
                        myResultSet = this.mysql.ReturnExecutedQuery(sSQL);
                        upBlock.plPatchLinkInfo = null;

                        if (myResultSet.Read())
                        {
                            upBlock.plPatchLinkInfo = new UrlInformation();
                            upBlock.plPatchLinkInfo.lUrlInfos = null;

                            upBlock.plPatchLinkInfo.sPatchLink = myResultSet.GetValue(0).ToString();
                            upBlock.plPatchLinkInfo.sType = myResultSet.GetValue(1).ToString();
                            upBlock.plPatchLinkInfo.sPatchId = myResultSet.GetValue(2).ToString();
                            upBlock.plPatchLinkInfo.sUpdateHistId = sUpdateHistId;
                        }
                        myResultSet.Close();

                        // now get patch link instr url
                        if (upBlock.plPatchLinkInfo != null)
                        {
                            upBlock.plPatchLinkInfo.lUrlInfos = new List<string>();

                            sSQL = String.Format("SELECT * FROM urlinstrtable WHERE patchid = '{0}' GROUP BY urlinstr;", upBlock.plPatchLinkInfo.sPatchId);
                            myResultSet = this.mysql.ReturnExecutedQuery(sSQL);

                            while (myResultSet.Read())
                            {
                                string sInstrUrl = myResultSet.GetValue(0).ToString();
                                upBlock.plPatchLinkInfo.lUrlInfos.Add(sInstrUrl);
                            }
                            myResultSet.Close();
                        }

                        // now get virusnames/vulner
                        upBlock.fiFixInfo = new List<FixInformation>();

                        sSQL = String.Format("SELECT fixhistid FROM fixidtable WHERE updatehistid = '{0}' LIMIT 1", sUpdateHistId);
                        sSQL = String.Format("SELECT * FROM fixnametable WHERE fixhistid = ({0});", sSQL);
                        myResultSet = this.mysql.ReturnExecutedQuery(sSQL);

                        while (myResultSet.Read())
                        {
                            FixInformation fixinfo = new FixInformation();

                            fixinfo.sFixHistId = myResultSet.GetValue(0).ToString();
                            fixinfo.sFixName = myResultSet.GetValue(1).ToString();
                            fixinfo.nType = int.Parse(myResultSet.GetValue(2).ToString());
                            fixinfo.nSeverity = int.Parse(myResultSet.GetValue(3).ToString());
                            fixinfo.sUrl = myResultSet.GetValue(4).ToString();
                            upBlock.fiFixInfo.Add(fixinfo);
                        }
                        myResultSet.Close();
                        return upBlock;
                    }
                }
            }
            catch (Exception e)
            {
                Common.SendEmail("Exception!", e.ToString());
                myResultSet.Close();
            }
            return null;
        }

        public string GetVendorNameByUpdateId(int nUpdateId)
        {
            MySqlDataReader myResultSet = null;

            try
            {
                if (this.mysql != null)
                {
                    string sSQL = String.Format("SELECT vendorname FROM vendortable WHERE updateid = '{0}' GROUP BY vendorname;", nUpdateId);
                    myResultSet = this.mysql.ReturnExecutedQuery(sSQL);
                    myResultSet.Read();
                    string sVendorName = myResultSet.GetValue(0).ToString();
                    myResultSet.Close();

                    return sVendorName;
                }
            }
            catch (Exception e)
            {
                Common.SendEmail("Exception!", e.ToString());
                myResultSet.Close();
            }
            return null;
        }

        public Dictionary<int, string> GetMapVendors()
        {
            MySqlDataReader myResultSet = null;

            try
            {
                if (this.mysql != null)
                {
                    string sSQL = String.Format("SELECT * FROM vendortable;");
                    myResultSet = this.mysql.ReturnExecutedQuery(sSQL);

                    Dictionary<int, string> mapVendors = new Dictionary<int, string>();

                    while (myResultSet.Read())
                    {
                        int nUpdateId = int.Parse(myResultSet.GetValue(0).ToString());
                        string sVendorName = myResultSet.GetValue(1).ToString();

                        mapVendors.Add(nUpdateId, sVendorName);
                    }
                    myResultSet.Close();

                    return mapVendors;
                }
            }
            catch (Exception e)
            {
                Common.SendEmail("Exception!", e.ToString());
                myResultSet.Close();
            }
            return null;
        }

        public Dictionary<int, List<ProdInformation>> GetKeyMaps()
        {
            try
            {
                Dictionary<int, string> mapVendors = this.GetMapVendors();

                Dictionary<int, List<ProdInformation>> mapKeyMaps = new Dictionary<int, List<ProdInformation>>();
                List<ProdInformation> lProd = null;

                foreach (KeyValuePair<int, string> kv in mapVendors)
                {
                    lProd = this.GetProductList(kv);
                    mapKeyMaps.Add(kv.Key, lProd);
                }
                return mapKeyMaps;
            }
            catch (Exception e)
            {
                Common.SendEmail("Exception!", e.ToString());
                return null;
            }
        }

        public List<ProdInformation> GetProductList(KeyValuePair<int, string> kv)
        {
            MySqlDataReader myResultSet = null;

            try
            {
                if (this.mysql != null)
                {
                    string sSQL = String.Format("SELECT * FROM producttable WHERE updateid = {0};", kv.Key);
                    myResultSet = this.mysql.ReturnExecutedQuery(sSQL);

                    List<ProdInformation> lProd = new List<ProdInformation>();

                    while (myResultSet.Read())
                    {
                        ProdInformation newprod = new ProdInformation();
                        newprod.nUpdateId = kv.Key;
                        newprod.sVendorName = kv.Value;
                        newprod.sProductName = myResultSet.GetValue(1).ToString();
                        newprod.sProductVers = myResultSet.GetValue(2).ToString();
                        newprod.nOS = int.Parse(myResultSet.GetValue(3).ToString());

                        lProd.Add(newprod);
                    }
                    myResultSet.Close();

                    return lProd;
                }
            }
            catch (Exception e)
            {
                Common.SendEmail("Exception!", e.ToString());
                myResultSet.Close();
            }
            return null;
        }
    }
}