using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Timers;
using System.Configuration;
using System.Threading;

using SrvCommon;
using MySql.Data.MySqlClient;

namespace SrvTracDataCopier
{
    public static class ServiceCfg
    {
        public static string TableName = "trackerlog1";
        public static string LogConnectionString = "";
        public static string SrvConnectionString = "";
        public static int Interval = 10;
        public static int MaxBulkRow = 100;        
        public static long Initial_ID = 49298987;

        static ServiceCfg()
        {
            try { TableName = ConfigurationManager.AppSettings["table_name"].ToString(); }
            catch { }

            try { Interval = int.Parse(ConfigurationManager.AppSettings["interval"]) * 1000; }
            catch { }

            try { Initial_ID = long.Parse(ConfigurationManager.AppSettings["initial_id"]); }
            catch { }

            try { LogConnectionString = ConfigurationManager.ConnectionStrings["db_tracker"].ToString(); }
            catch { }

            try { SrvConnectionString = ConfigurationManager.ConnectionStrings["db_Srvtrac"].ToString(); }
            catch { }
        }
    }

    public partial class Service1 : ServiceBase
    {
        private System.Timers.Timer timer1 = null;
        private string table_name = "";

        private MySqlConnection db_trk = null;
        private MySqlConnection db_Srv = null;

        public Service1()
        {
            InitializeComponent();
        }

        protected override void OnStart(string[] args)
        {
            // on restart, lets copy most recent record from TRACKERLOG so that it doesnt need to copy all old data,
            // since copying all lost data takes a VERY LONG time. We don't need all old data in staging anyways.
            if (Copy_LastRoutine())
            {
                timer1 = new System.Timers.Timer();
                this.timer1.Interval = ServiceCfg.Interval;
                this.timer1.Elapsed += new System.Timers.ElapsedEventHandler(this.timer1_Tick);
                this.timer1.Enabled = true;
                this.table_name = ServiceCfg.TableName;

                SrvLogger.LogInfoLine("SrvTracDataCopier Started");
            }
        }

        protected override void OnStop()
        {
            this.timer1.Enabled = false;
            SrvLogger.LogInfoLine("SrvTracDataCopier Stopped");
        }

        /// <summary>
        /// Create and open database connection.
        /// </summary>
        private MySqlConnection CreateConnection(string conn_key)
        {
            try
            {
                MySqlConnection dbConn = new MySqlConnection(conn_key);
                dbConn.Open();
                return dbConn;
            }
            catch (Exception ex)
            {
                SrvLogger.LogErrorLine("Exception: CannotOpenDbConn - {0}", ex.Message);  
            }
            return null;
        }

        public void timer1_Tick(object sender, ElapsedEventArgs e)
        {
            try
            {
                this.timer1.Enabled = false;
            }
            catch
            {
              // TRY/CATCH to circumvent NUnit
            }

            try
            {
                this.db_trk = CreateConnection(ServiceCfg.LogConnectionString);
                this.db_Srv = CreateConnection(ServiceCfg.SrvConnectionString);

                if (this.db_trk != null && this.db_Srv != null)
                {
                    int nRet = Copy_MainRoutine();
                    SrvLogger.LogInfoLine("Inserted {0} Rows.", nRet);
                }
            }
            catch (MySqlException ex)
            {
                SrvLogger.LogErrorLine("MySqlException: {0}", ex.Message);
            }
            catch (Exception ex)
            {
                SrvLogger.LogErrorLine("Exception: {0}", ex.Message);
            }
            finally
            {
                if (this.db_trk != null && this.db_trk.State == ConnectionState.Open)
                    this.db_trk.Close();

                if (this.db_Srv != null && this.db_Srv.State == ConnectionState.Open)
                    this.db_Srv.Close();
            }

            try
            {
                this.timer1.Enabled = true;
            }
            catch
            {
                // TRY/CATCH to circumvent NUnit
            }
        }

        protected long GetMaxIdFromDB(MySqlConnection dbConn)
        {
            using (MySqlCommand dbCmd = dbConn.CreateCommand())
            {
                dbCmd.CommandText = string.Format("SELECT MAX(ID) FROM {0};", ServiceCfg.TableName);
                dbCmd.CommandType = CommandType.Text;

                using (MySqlDataReader dbReader = dbCmd.ExecuteReader(CommandBehavior.SingleRow))
                {
                    if (dbReader.Read())
                    {
                        return dbReader.GetInt64(0);
                    }
                }
            }
            return 0;
        }

        protected bool Copy_LastRoutine()
        {
            int nRet = 0;

            try
            {
                this.db_trk = CreateConnection(ServiceCfg.LogConnectionString);
                this.db_Srv = CreateConnection(ServiceCfg.SrvConnectionString);

                long nMaxSource = GetMaxIdFromDB(this.db_trk);

                DataTable dt = new DataTable();

                string szQuery = string.Format("SELECT * FROM `{0}` WHERE `ID` = '{1}';",
                    ServiceCfg.TableName, nMaxSource);

                using (MySqlCommand dbCmd = this.db_trk.CreateCommand())
                {
                    dbCmd.CommandText = szQuery;
                    dbCmd.CommandType = CommandType.Text;

                    using (MySqlDataAdapter dbAdp = new MySqlDataAdapter(dbCmd))
                    {
                        dbAdp.Fill(dt);
                    }
                }

                if (dt.Rows.Count == 1)
                {
                    string hex = "0x" + SrvUtil.ByteArrayToHexString((byte[])dt.Rows[0]["Datagram"]);
                    DateTime tm = (DateTime)dt.Rows[0]["RecvTime"];

                    szQuery = string.Format("INSERT INTO {0} (`ID`,`DevModel`,Datagram,`RecvTime`) VALUES ('{1}', '{2}', {3}, '{4}');", 
                        ServiceCfg.TableName, dt.Rows[0]["ID"], dt.Rows[0]["DevModel"], hex, tm.ToString("yyyy-MM-dd HH:mm:ss.fff"));

                    using (MySqlCommand dbCmd = this.db_Srv.CreateCommand())
                    {
                        dbCmd.CommandText = szQuery;
                        dbCmd.CommandType = CommandType.Text;
                        nRet = dbCmd.ExecuteNonQuery();
                    }
                }
            }
            finally
            {
                this.db_trk.Close();
                this.db_Srv.Close();
            }
            return (nRet > 0 ? true : false);
        }

        protected int Copy_MainRoutine()
        {
            int nRet = 0;

            long nMaxSource = GetMaxIdFromDB(this.db_trk);
            long nMaxTarget = GetMaxIdFromDB(this.db_Srv);

            if (nMaxTarget == 0)
                nMaxTarget = ServiceCfg.Initial_ID;

            for (long nIndex = nMaxTarget; nIndex < nMaxSource; nIndex += ServiceCfg.MaxBulkRow)
            {
                DataTable dt = new DataTable();

                string szQuery = string.Format("SELECT `ID`,`DevModel`,`Datagram`,`RecvTime` FROM `{0}` WHERE `ID` > '{1}' AND `ID` <= '{2}';",
                    ServiceCfg.TableName, nIndex, nIndex + ServiceCfg.MaxBulkRow);

                using (MySqlCommand dbCmd = this.db_trk.CreateCommand())
                {
                    dbCmd.CommandText = szQuery;
                    dbCmd.CommandType = CommandType.Text;

                    using (MySqlDataAdapter dbAdp = new MySqlDataAdapter(dbCmd))
                    {
                        dbAdp.Fill(dt);
                    }
                }

                szQuery = string.Format("INSERT INTO {0} (`ID`,`DevModel`,Datagram,`RecvTime`) VALUES ", ServiceCfg.TableName);

                int nCnt = 1;
                int totalrows = dt.Rows.Count;

                foreach (DataRow dr in dt.Rows)
                {
                    string hex = "0x" + SrvUtil.ByteArrayToHexString((byte[]) dr["Datagram"]);
                    DateTime tm = (DateTime)dr["RecvTime"];
                    string model = dr["DevModel"].ToString();

                    // don't copy V07/AT05 stuff, there is just too many
                    if (nCnt != totalrows && (model == "V07" || model == "AT05"))
                    {
                        nCnt++;
                        continue;
                    }
                    // ...

                    string buf = string.Format("('{0}', '{1}', {2}, '{3}')", dr["ID"], model, hex, tm.ToString("yyyy-MM-dd HH:mm:ss.fff"));

                    if (nCnt == totalrows)
                    {
                        szQuery += string.Format("{0};", buf);
                    }
                    else
                    {
                        szQuery += string.Format("{0},", buf);
                    }
                    nCnt++;
                }

                if (totalrows > 0)
                {
                    //SrvLogger.ConsoleWriteLine(szQuery);
                    using (MySqlCommand dbCmd = this.db_Srv.CreateCommand())
                    {
                        dbCmd.CommandText = szQuery;
                        dbCmd.CommandType = CommandType.Text;
                        nRet += dbCmd.ExecuteNonQuery();
                    }
                }

                Thread.Sleep(100);
            }
            return nRet;
        }
    }
}