using LSWLib;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using spl = LSWLib.CLog;

namespace syncEdosLocal
{

    class cResult
        {
        public DateTime timestamp;
        public int VisitsNew, VisitsSkipped, VisitsAddedExits, VisitsAddedEntries;
        public int ID_Visit_Min, ID_Visit_Max;
        public int Persons, Tasks;
        public string ErrorMessage;
        System.Data.SqlClient.SqlCommand cmd;
        spl spLog;
        internal int ID_Station;

        public cResult(int ? iID_station, SqlConnection co, spl Log)
        {
            ID_Station = iID_station.Value;
            ErrorMessage = string.Empty;
            VisitsNew = VisitsSkipped = VisitsAddedExits = VisitsAddedEntries = 0;
            ID_Visit_Min = int.MaxValue; ID_Visit_Max = int.MinValue;
            Persons = Tasks = 0;
            spLog = Log;
            MakeCmd(co);
        }
        public override string ToString()
        {
            return  $"Success: {VisitsNew} new , {VisitsAddedEntries} Added Entries, {VisitsAddedExits} Added Exits, {VisitsSkipped} skipped";
        }

        private void MakeCmd(SqlConnection co)
        {
            cmd = new SqlCommand("syncLoc.SyncStatusUpdate",co);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter pa = new SqlParameter("ID_Station", SqlDbType.Int, 4); cmd.Parameters.Add(pa);
            pa = new SqlParameter("TimeStamp", SqlDbType.DateTime, 4); cmd.Parameters.Add(pa);
            pa = new SqlParameter("New", SqlDbType.Int, 4); cmd.Parameters.Add(pa);
            pa = new SqlParameter("Entries", SqlDbType.Int, 4); cmd.Parameters.Add(pa);
            pa = new SqlParameter("Exits", SqlDbType.Int, 4); cmd.Parameters.Add(pa);
            pa = new SqlParameter("Skipped", SqlDbType.Int, 4); cmd.Parameters.Add(pa);
            pa = new SqlParameter("ErrorMessage", SqlDbType.VarChar, 512); cmd.Parameters.Add(pa);
            pa = new SqlParameter("ID_Visit_Min", SqlDbType.Int, 4); cmd.Parameters.Add(pa);
            pa = new SqlParameter("ID_Visit_Max", SqlDbType.Int, 4); cmd.Parameters.Add(pa);
            pa = new SqlParameter("Persons", SqlDbType.Int, 4); cmd.Parameters.Add(pa);
            pa = new SqlParameter("Tasks", SqlDbType.Int, 4); cmd.Parameters.Add(pa);

        }

        public void Write( )
        {
            try
            {
                spLog.SeverityReset();
                timestamp = DateTime.Now;
                cmd.Parameters["ID_Station"].Value = ID_Station;
                cmd.Parameters["TimeStamp"].Value = timestamp;
                cmd.Parameters["New"].Value = VisitsNew;
                cmd.Parameters["Entries"].Value = VisitsAddedEntries;
                cmd.Parameters["Exits"].Value = VisitsAddedEntries;
                cmd.Parameters["Skipped"].Value = VisitsSkipped;
                cmd.Parameters["ErrorMessage"].Value = ErrorMessage;
                cmd.Parameters["ID_Visit_Min"].Value = ID_Visit_Min;
                cmd.Parameters["ID_Visit_Max"].Value = ID_Visit_Max;
                cmd.Parameters["Persons"].Value = Persons;
                cmd.Parameters["Tasks"].Value = Tasks;
                ConnectionState cs = cmd.Connection.State;
                if (cs != ConnectionState.Open) cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                if (cs != ConnectionState.Open) cmd.Connection.Close();
                spLog.WriteSplunkLog("Result", spLog.splunkSeverity, null, this.ToString()); ;
            } catch (System.Exception ex)
            {
                spLog.setErrorException(ex);
                spLog.WriteSplunkLog("Result", spLog.splunkSeverity, null, spLog.splunkError);
            }
        }

        internal void CheckVisitID(int iD_Visit)
        {
            if (ID_Visit_Max < iD_Visit) ID_Visit_Max = iD_Visit;
            if (ID_Visit_Min > iD_Visit) ID_Visit_Min = iD_Visit;
        }
    }

    class Program
    {
        public static DataSet ds = null;
        static System.Data.SqlClient.SqlInfoMessageEventHandler iMessage = new SqlInfoMessageEventHandler(Connection_InfoMessage);
        static bool error = false;
        static bool warning = false;
        static StringBuilder messages = null;
        static string splunkMessage = string.Empty;
        static spl spLog;
        static LSWLib.Bulker Bulker;
        static cResult Result;

      
        public static bool DoorloopToDo(System.Data.SqlClient.SqlConnection fromConnection, string toConstr, System.Data.SqlClient.SqlInfoMessageEventHandler im, ref bool error, ref bool warning, ref StringBuilder messages)
        {
            ds = new DataSet();
            DataTable dtTables = new DataTable("Tables");
            ds.Tables.Add(dtTables);
            DataTable dtActions = new DataTable("Actions");
            ds.Tables.Add(dtActions);
            {
                using (var cmd = new System.Data.SqlClient.SqlCommand("syncloc.SynchroToLocalTables", fromConnection))
                {
                    spLog.KvClear();
                    cmd.CommandType = CommandType.StoredProcedure;
                    spLog.kvAddCommand(spl.skeys.sqlQuery, cmd);
                    using (var ncta = new System.Data.SqlClient.SqlDataAdapter(cmd))
                    {
                        try
                        {
                            ncta.Fill(dtTables);
                            int r = dtTables.Rows.Count;
                            spLog.KvAdd(spl.skeys.recordsAffected, r);
                            spLog.WriteSplunkLog(string.Empty, spl.iseverity.info, "LoadTables");
                        }
                        catch (System.Exception ex)
                        {
                            spLog.WriteSplunkLog(string.Empty, spl.iseverity.err, "LoadTables", ex.Message);
                        }
                    }
                }
                spLog.KvClear();
                dtTables.PrimaryKey = new DataColumn[] { dtTables.Columns["object_id"] };

                using (var cmd = new System.Data.SqlClient.SqlCommand("syncloc.SynchroToLocalActions", fromConnection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    using (var ncta = new System.Data.SqlClient.SqlDataAdapter(cmd))
                    {
                        try
                        {
                            ncta.Fill(dtActions);
                            spLog.KvAdd(spl.skeys.recordsAffected, dtActions.Rows.Count);
                            spLog.WriteSplunkLog(string.Empty, spl.iseverity.info, "LoadActions");
                        }
                        catch (System.Exception ex)
                        {
                            spLog.WriteSplunkLog(string.Empty, spl.iseverity.err, "LoadActions", ex.Message);
                        }
                    }
                }
                dtActions.PrimaryKey = new DataColumn[] { dtActions.Columns["object_id"], dtActions.Columns["Type"], dtActions.Columns["r"] };
                dtActions.ParentRelations.Add(new DataRelation("pObject", dtTables.Columns["object_id"], dtActions.Columns["object_id"]));

                StringBuilder sqlCmdsActivate = null;
                StringBuilder sqlCmdsDeActivate = null;
                string Task = string.Empty;
                string dest = string.Empty;
                string qry = string.Empty;

                using (System.Data.SqlClient.SqlConnection toConnection = new SqlConnection(toConstr))
                {
                    spLog.KvClear();
                    spLog.KvAdd(toConnection);
                    toConnection.InfoMessage += im;
                    toConnection.Open();
                    if (toConnection.State == ConnectionState.Open)
                        using (var ucmd = new System.Data.SqlClient.SqlCommand("syncloc.SynchroToLocalTablesUpdate".ToString(),fromConnection))
                        {
                            ucmd.CommandType = CommandType.StoredProcedure;
                            SqlParameter pa = new SqlParameter("ID_Station", SqlDbType.Int, 4); ucmd.Parameters.Add(pa); pa.Value = Result.ID_Station;
                            pa = new SqlParameter("OBJECT_ID", SqlDbType.Int, 4); pa.Value = Result.ID_Station; ucmd.Parameters.Add(pa); 
                            pa = new SqlParameter("lastRecords", SqlDbType.Int, 4); ucmd.Parameters.Add(pa);
                            pa = new SqlParameter("constraintsDeactivated", SqlDbType.Bit); ucmd.Parameters.Add(pa);
                            pa = new SqlParameter("constraintsActivated", SqlDbType.Bit); ucmd.Parameters.Add(pa);
                            pa = new SqlParameter("syncError", SqlDbType.Bit); ucmd.Parameters.Add(pa);

                            if (fromConnection.State == ConnectionState.Closed) fromConnection.Open();

                            foreach (DataRow rtr in dtTables.Rows)
                            {
                                ucmd.Parameters["OBJECT_ID"].Value = (int)rtr[0];
                                string src=rtr[1].ToString();
                                dest = rtr[2].ToString();
                                var x = rtr.GetChildRows(dtActions.ParentRelations[0]);
                                Task = $"Table {dest}";
                                System.Console.WriteLine($"*** Step {rtr[1].ToString()}");
                                if (x.Length > 0)
                                {
                                    sqlCmdsActivate = new StringBuilder();
                                    sqlCmdsDeActivate = new StringBuilder();
                                    foreach (DataRow aa in x)
                                    {
                                        sqlCmdsDeActivate.Append($"  {aa["ActionDeActivate"].ToString()};");
                                        sqlCmdsActivate.Append($"  {aa["ActionActivate"].ToString()};");
                                    }
                                }
                                else
                                {
                                    sqlCmdsActivate = sqlCmdsDeActivate = null;
                                }

                                int res = 0;
                                if (sqlCmdsDeActivate != null)
                                    using (var cmd = new System.Data.SqlClient.SqlCommand(sqlCmdsDeActivate.ToString(), toConnection))
                                    {
                                        cmd.CommandType = CommandType.Text;
                                        try
                                        {
                                            spLog.SeverityReset();
                                            res = cmd.ExecuteNonQuery();
                                            messages.AppendLine($"Success:Query={sqlCmdsDeActivate.ToString()}");
                                            ucmd.Parameters["constraintsDeactivated"].Value = true;
                                            spLog.WriteSplunkLog(string.Empty, spLog.splunkSeverity, "constraintsDeactivated", string.Empty);
                                        }
                                        catch (System.Exception ex)
                                        {
                                            spLog.setErrorException(ex);
                                            error = true;
                                            messages.AppendLine(spLog.splunkError);
                                            ucmd.Parameters["constraintsDeactivated"].Value = false;
                                            spLog.WriteSplunkLog(string.Empty, spLog.splunkSeverity, "constraintsDeactivated", spLog.splunkError);
                                        }
                                    }

                                int cnt = 0;
                                qry = LSWLib.SqlCommands.getColumnsQry(fromConnection, src);
                                SqlConnection[] curDests = new SqlConnection[] { toConnection };
                                cnt = Bulker.Execute(Task, fromConnection, curDests, qry, CommandType.Text, dest, 0, ref error, ref messages);
                                ucmd.Parameters["lastRecords"].Value = cnt;
                                if (dest.Contains("PersonInfo")) 
                                    Result.Persons = cnt;
                                else if (dest.Contains("TaskInfo")) Result.Tasks = cnt;


                                if (sqlCmdsActivate != null)
                                    using (var cmd = new System.Data.SqlClient.SqlCommand(sqlCmdsActivate.ToString(), toConnection))
                                    {
                                        cmd.CommandType = CommandType.Text;
                                        spLog.KvClear();
                                        spLog.kvAddCommand(spl.skeys.sqlQuery, cmd);
                                        try
                                        {
                                            spLog.SeverityReset();
                                            res = cmd.ExecuteNonQuery();
                                            messages.AppendLine($"Success:Query={sqlCmdsActivate.ToString()}");
                                            ucmd.Parameters["constraintsActivated"].Value = true;
                                            spLog.WriteSplunkLog(string.Empty, spLog.splunkSeverity, "constraintsActivated", spLog.splunkError);
                                        }
                                        catch (System.Exception ex)
                                        {
                                            spLog.setErrorException(ex);
                                            error = true;
                                            ucmd.Parameters["constraintsActivated"].Value = false;
                                            messages.AppendLine(spLog.splunkError);
                                            spLog.WriteSplunkLog(string.Empty, spLog.splunkSeverity, "constraintsActivated", spLog.splunkError);
                                        }
                                    }
                                ucmd.Parameters["syncError"].Value = error;
                                ucmd.ExecuteNonQuery();
                                if (error) break;
                            }
                        }
                }
            }
            return error;
        }
        static void Main(string[] args)
        {
            spLog = new spl("SynceDosLocal");
            Bulker = new LSWLib.Bulker(spLog, true);
            messages = Bulker.messages;
            messages.AppendLine(spLog.GetStartInfoString());


            //Sync need both
            var SourceCoStr = new SqlConnectionStringBuilder(System.Configuration.ConfigurationManager.ConnectionStrings["eDosConnectionString"].ConnectionString);
            var LocalCoStr = new SqlConnectionStringBuilder(System.Configuration.ConfigurationManager.ConnectionStrings["LocalConnectionString"].ConnectionString);

            SourceCoStr.TrustServerCertificate = LocalCoStr.TrustServerCertificate= true;
            SourceCoStr.ApplicationName = LocalCoStr.ApplicationName = "eDosSync";
            SourceCoStr.WorkstationID = LocalCoStr.WorkstationID = Environment.MachineName;

            string reDos = string.Empty;
            string reDosLocal = string.Empty;
            try
            {
                using (var key = Microsoft.Win32.Registry.LocalMachine.OpenSubKey("SOFTWARE\\WOW6432Node\\Microsoft\\MSSQLServer\\Client\\ConnectTo"))
                {
                    reDos = key.GetValue("eDos").ToString();
                    reDosLocal = key.GetValue("eDos").ToString();
                }
            } catch (System.Exception ex)
            {
                spLog.setErrorException(ex);
                spLog.WriteSplunkLog("init", spLog.splunkSeverity, null, spLog.splunkError);
                messages.AppendLine($"Error reading reg key");
            }
            messages.AppendLine($"Remote {SourceCoStr.DataSource} db {SourceCoStr.InitialCatalog} Logon= { SourceCoStr.UserID} alias:{ reDos }");
            messages.AppendLine($"Local {LocalCoStr.DataSource} db {LocalCoStr.InitialCatalog} Logon= { LocalCoStr.UserID} alias:{ reDosLocal }");

            try
            {
                using (System.Data.SqlClient.SqlConnection eDosLocalCon = new SqlConnection(LocalCoStr.ConnectionString))
                {
                    eDosLocalCon.Open();
                    int? StationID = Bulker.SqlCommands.ExecProcScalar("Get Station ID", eDosLocalCon, "GetID_LocalStation", true, ref error) as int?;
                    Result = new cResult(StationID, eDosLocalCon, spLog);
                    using (System.Data.SqlClient.SqlConnection eDosCon = new SqlConnection(SourceCoStr.ConnectionString))
                    {
                        eDosCon.InfoMessage += Connection_InfoMessage;
                        DoorloopToDo(eDosCon, LocalCoStr.ConnectionString, iMessage, ref error, ref warning, ref messages);
                        DoorloopVisits(eDosLocalCon, eDosCon, Bulker.spLog, ref error);
                    }
                    Result.Write();
                    eDosLocalCon.Close();
                }
            }
            catch (System.Exception ex)
            {
                error = true;
                spLog.WriteSplunkLog(string.Empty, spl.iseverity.err, "syncEdosLocal", ex.Message);
            }
            if (error) goto final;

            final:

            messages.AppendLine($"Process:End,tm={System.DateTime.Now.ToString("yyyy-MM-ddTHH:mm")}, Errors={error}, Warnings={warning}");
            System.Diagnostics.Debug.WriteLine(messages.ToString());
            Console.WriteLine(messages.ToString());

            //var smtpFrom = System.Configuration.ConfigurationManager.AppSettings["smtpFrom"];
            //var smtpServer = System.Configuration.ConfigurationManager.AppSettings["smtpServer"];
            //int smtpPort = 25;
            //if (int.TryParse(System.Configuration.ConfigurationManager.AppSettings["smtpPort"], out int newSMTPPort)) smtpPort = newSMTPPort; ;
            //using (System.Net.Mail.MailMessage m = new System.Net.Mail.MailMessage())
            //{
            //    m.From = new System.Net.Mail.MailAddress(smtpFrom);
            //    m.To.Add("lvandenb@sckcen.be");
            //    m.Subject = "SynceDosLocal " + (error ? "Error" : "No errors") + (warning ? " Warnings" : "");
            //    m.IsBodyHtml = true;
            //    m.Body = Bulker.htmlLog.ToString();
            //    using (var mc = new System.Net.Mail.SmtpClient(smtpServer, smtpPort)) { mc.Send(m); }
            //}
            spLog.CloseSplunkLog();
        }
        static void DoorloopVisits(SqlConnection coLocal, SqlConnection coCentral, spl spLog, ref bool error)
        {
            const string sStep = "Doorloop Visits";
            //int VisitsNew = 0, VisitsAddedEntries = 0, VisitsAddedExits = 0, VisitsSkipped = 0;
            spLog.WriteSplunkLog(sStep, spl.iseverity.info);
            DataSet1 ds = new DataSet1();
            DataSet1.CentralVisitsRow cr;
            try
            {
                using (var ta = new DataSet1TableAdapters.LocalVisitsTableAdapter())
                using (var tc = new DataSet1TableAdapters.CentralVisitsTableAdapter())
                {
                    ta.Connection = coLocal;
                    tc.Connection = coCentral;
                    tc.ClearBeforeFill = false;
                    ta.Fill(ds.LocalVisits);
                    spLog.KvAdd(spl.skeys.recordsAffected, ds.LocalVisits.Rows.Count);
                    foreach (DataSet1.LocalVisitsRow lr in ds.LocalVisits.Rows)
                    {
                        Result.CheckVisitID(lr.ID_Visit);
                        tc.Fill(ds.CentralVisits, lr.ID_Station, lr.ID_Visit);
                        cr = ds.CentralVisits.FindByID_StationID_Visit(lr.ID_Station, lr.ID_Visit);
                        if (cr == null)
                        {
                            Result.VisitsNew++;
                            
                            copyrow(lr, ds.CentralVisits);
                            if ((lr.VisitStatus & 1) > 0) { lr.syncedEntry = true; Result.VisitsAddedEntries++;}
                            if ((lr.VisitStatus & 2) > 0) { lr.syncedExit = true; Result.VisitsAddedExits++; }
                        }
                        else switch (cr.VisitStatus)
                            {
                                case 3: lr.syncedEntry = lr.syncedExit = true; Result.VisitsSkipped++; break;
                                case 1: if ((lr.VisitStatus & 2) == 2) { copyExit(lr, cr); lr.syncedEntry=lr.syncedExit = true; Result.VisitsAddedExits++; } break;
                                case 2: if ((lr.VisitStatus & 1) == 1) { copyEntry(lr, cr); lr.syncedEntry=lr.syncedExit=  true; Result.VisitsAddedEntries++; } break;
                            }
                    }
                    ta.Update(ds.LocalVisits);
                    tc.Update(ds.CentralVisits);
                }
                spLog.KvClear();
            }
            catch (System.Exception ex)
            {
                error = true;
                Result.ErrorMessage = ex.Message;
                spLog.setErrorException(ex);
                spLog.WriteSplunkLog(sStep, spLog.splunkSeverity, null, spLog.splunkError);
            }
        }
        private static void copyEntry(DataSet1.LocalVisitsRow lr, DataSet1.CentralVisitsRow cr)
        {
            cr["ID_Station"] = lr["ID_Station"];
            cr["ID_Visit"] = lr["ID_Visit"];
            cr["Inst_CODE"] = lr["Inst_CODE"];
            cr["PersID"] = lr["PersID"];
            cr["ID_Task"] = lr["ID_Task"];
            cr["EntryTime"] = lr["EntryTime"];
            cr["ID_EPD"] = lr["ID_EPD"];
            cr["isBG"] = lr["isBG"];
            cr["ENTRY_EPD_CLOCK"] = lr["ENTRY_EPD_CLOCK"];
            cr["epdType"] = lr["epdType"];
        }
        private static void copyExit(DataSet1.LocalVisitsRow lr, DataSet1.CentralVisitsRow cr)
        {
            cr["ReturnTime"] = lr["ReturnTime"];
            cr["Hp10Dose"] = lr["Hp10Dose"];
            cr["Hp07Dose"] = lr["Hp07Dose"];
            cr["Hp10Total"] = lr["Hp10Total"];
            cr["Hp07Total"] = lr["Hp07Total"];
            cr["Hp10Peak"] = lr["Hp10Peak"];
            cr["Hp07Peak"] = lr["Hp07Peak"];
            cr["Hp10Time"] = lr["Hp10Time"];
            cr["Hp07Time"] = lr["Hp07Time"];
            cr["QualityData"] = lr["QualityData"];
            cr["K1"] = lr["K1"];
            cr["K2"] = lr["K2"];
            cr["K3"] = lr["K3"];
            cr["K4"] = lr["K4"];
            cr["K5"] = lr["K5"];
            cr["K6"] = lr["K6"];
            cr["SG"] = lr["SG"];
            cr["BC"] = lr["BC"];
            cr["FB"] = lr["FB"];
            cr["HG"] = lr["HG"];
            cr["alarmStatus"] = lr["alarmStatus"];
            cr["otherAlarms"] = lr["otherAlarms"];
            cr["errorStatus"] = lr["errorStatus"];
            cr["operatingStatus"] = lr["operatingStatus"];
            cr["isAlarm"] = lr["isAlarm"];
            cr["isAlarmCleared"] = lr["isAlarmCleared"];
            cr["ENTRY_EPD_CLOCK"] = lr["ENTRY_EPD_CLOCK"];
            cr["EXIT_EPD_CLOCK"] = lr["EXIT_EPD_CLOCK"];
            cr["TIME_LOST"] = lr["TIME_LOST"];
        }
        private static DataSet1.CentralVisitsRow copyrow(DataSet1.LocalVisitsRow lr, DataSet1.CentralVisitsDataTable centralVisits)
        {
            DataSet1.CentralVisitsRow cr = centralVisits.NewCentralVisitsRow();
            foreach (DataColumn sc in lr.Table.Columns) cr[sc.ColumnName] = lr[sc.ColumnName]; 
            centralVisits.AddCentralVisitsRow(cr);
            return cr;
        }
        private static void Connection_InfoMessage(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs e)
        {
            spLog.Connection_InfoMessageLog(e, ref messages);
        }
    }
}
