using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Navigation;

namespace eDosStation
{
    public class DataInterface
    {
        public int ID_Station;
        public bool isLocal = false;
        private string currentConStr, ConCentralStr;
        public System.Data.SqlClient.SqlConnection localConnection, centralConnection;
        public System.Data.SqlClient.SqlCommand VisitInitCommand;
        public static SqlCommand VisitExitCommand;
        public static SqlCommand NextIDCommand;
        public static SqlCommand LastIDCommand;
        public static SqlCommand CheckStatus;
        public DataSet1.CommentOnEventDataTable dtCOE;
        private bool CheckUser;

        public static int? NextID()
        {
            int? nxtValue = null;
            if (NextIDCommand.Connection.State == ConnectionState.Closed) NextIDCommand.Connection.Open();
            nxtValue = NextIDCommand.ExecuteScalar() as int?;
                return nxtValue;
        }

        public static int? LastID()
        {
            int? lastValue = null;
            if (NextIDCommand.Connection.State == ConnectionState.Closed) LastIDCommand.Connection.Open();
            lastValue = LastIDCommand.ExecuteScalar() as int?;
            return lastValue;
        }
        /// <summary>
        /// Check connenction. If closed then open oand optianly leave open.
        /// </summary>
        /// <param name="leaveOpen">If opened leave it open</param>
        /// <returns>true if connection ok</returns>
        public bool checkCon(bool leaveOpen = false)
        {
            bool rv = false;
            if (localConnection != null && localConnection.State == System.Data.ConnectionState.Open) return true;
            try
            {
                if (localConnection == null || string.IsNullOrEmpty(localConnection.ConnectionString))  localConnection = new SqlConnection(currentConStr);
                
                localConnection.Open();
                rv = true;
            }
            catch (System.Exception ex)
            {
                System.Windows.MessageBox.Show($"Opening '{currentConStr}' error :{ex.Message}");
            }
            if (rv && !leaveOpen) localConnection?.Close();
            return rv;
        }
        public bool checkConCentral(bool leaveOpen = false)
        {
            bool rv = false;
            
            if (centralConnection != null && centralConnection.State == System.Data.ConnectionState.Open) return true;
            try
            {
                if (centralConnection == null || string.IsNullOrEmpty(centralConnection.ConnectionString)) centralConnection = new SqlConnection(ConCentralStr);
                centralConnection.Open();
                rv = true;
            }
            catch (System.Exception ex)
            {
                System.Windows.MessageBox.Show($"Opening '{ConCentralStr}' error :{ex.Message}");
            }
            if (rv && !leaveOpen) centralConnection?.Close();
            return rv;
        }

        internal void SetStation(int curIDStation)
        {
            ID_Station = curIDStation;
            LastIDCommand.Parameters["ID_Station"].Value = curIDStation;
            VisitInitCommand.Parameters["ID_Station"].Value = curIDStation;
            CheckStatus.Parameters["ID_Station"].Value = curIDStation;
        }

        public bool isOnline()
        {
            bool? online = false;
            if (checkCon(true))
            {
                online = CheckStatus.ExecuteScalar() as bool?;
                if (!online.HasValue) online = false;
            }
            isLocal = !online.Value;
            return online.Value;
        }

        static public void first()
        {
            var cb = new System.Data.SqlClient.SqlConnectionStringBuilder(Properties.Settings.Default.localConnectionString);
            if (string.IsNullOrEmpty(cb.Password))
            {
                var f = new AskPwd();
                f.ShowDialog();
                string pwd = f.ResponseText;
                string cfg = System.IO.Path.Combine(Environment.CurrentDirectory, System.AppDomain.CurrentDomain.FriendlyName);
                Config.ToggleConfigEncryption(cfg, pwd);
                System.Windows.Application.Current.Shutdown();
            }
        }

        private void init()
        {
            try
            {
                first();
                currentConStr = System.Configuration.ConfigurationManager.ConnectionStrings["eDosStation.Properties.Settings.localConnectionString"].ConnectionString;
                ConCentralStr = System.Configuration.ConfigurationManager.ConnectionStrings["eDosConnectionString"].ConnectionString;
                localConnection = new SqlConnection(currentConStr);
                NextIDCommand = new SqlCommand("SELECT NEXT VALUE FOR VisitSequence as VisitID", localConnection);
                isLocal = true;
                MakeCommands();
                bool rv = checkCon(false);
            } catch (Exception ex)
            {
                System.Windows.MessageBox.Show($"Init error {ex.Message} con={currentConStr}");
            }
        }

        public DataInterface()
        {
            init();
        }
       
         DataSet1TableAdapters.GetTaskInfoTableAdapter TaskInfoTa()
        {
            var ta = new DataSet1TableAdapters.GetTaskInfoTableAdapter();
            ta.Connection.ConnectionString = currentConStr;
            return ta;
        }

         DataSet1TableAdapters.GetTaskListTableAdapter TaskListTA()
        {
            var ta = new DataSet1TableAdapters.GetTaskListTableAdapter();
            ta.Connection.ConnectionString = currentConStr;
            return ta;
        }

        internal void SetCheckUser(bool pCheckUser)
        {
            this.CheckUser = pCheckUser;
        }

        DataSet1TableAdapters.CommentOnEventTableAdapter CommentTA()
        {
            var ta = new DataSet1TableAdapters.CommentOnEventTableAdapter();
            ta.Connection.ConnectionString = currentConStr;
            return ta;
        }

        public System.Data.SqlClient.SqlConnection getCon(bool LeaveOpen)
        {
           checkCon(LeaveOpen);
           return localConnection;
        }

        public System.Data.SqlClient.SqlConnection getConCentral(bool LeaveOpen)
        {
            checkConCentral(LeaveOpen);
            return centralConnection;
        }

        void MakeCommands()
        {
            LastIDCommand = new SqlCommand("VisitIDLast", localConnection);
            LastIDCommand.CommandType = CommandType.StoredProcedure;
            var pa = new System.Data.SqlClient.SqlParameter("ID_Station", SqlDbType.Int, 4); LastIDCommand.Parameters.Add(pa);

            CheckStatus = new SqlCommand("[syncLoc].[SyncStatusIsOK]",localConnection);
            CheckStatus.CommandType = CommandType.StoredProcedure;
            pa = new System.Data.SqlClient.SqlParameter("ID_Station", SqlDbType.Int, 4); CheckStatus.Parameters.Add(pa);

            VisitInitCommand = new System.Data.SqlClient.SqlCommand("VisitInit", localConnection);
            VisitInitCommand.CommandType = System.Data.CommandType.StoredProcedure;
            pa = new System.Data.SqlClient.SqlParameter("ID_Station", SqlDbType.Int, 4); VisitInitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("ID_Visit", SqlDbType.Int, 4); pa.Direction = ParameterDirection.Input; VisitInitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("PersID", SqlDbType.Int, 4); VisitInitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("ID_Task", SqlDbType.Int, 4); VisitInitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("Inst_CODE", SqlDbType.Int, 4); VisitInitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("EPD_InternalID", SqlDbType.Int, 4); VisitInitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("isBG", SqlDbType.Bit, 4); VisitInitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("ENTRY_EPD_CLOCK", SqlDbType.Decimal, 9); VisitInitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("EPDVisitID", SqlDbType.Int, 4); pa.Direction = ParameterDirection.Output; VisitInitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("epdType", SqlDbType.TinyInt, 1); VisitInitCommand.Parameters.Add(pa);

            VisitExitCommand = new SqlCommand("VisitExit", localConnection);
            VisitExitCommand.CommandType = CommandType.StoredProcedure;
            pa = new System.Data.SqlClient.SqlParameter("EPDVisitID", SqlDbType.Int, 4); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("HP07", SqlDbType.Float); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("HP10", SqlDbType.Float); VisitExitCommand.Parameters.Add(pa);

            pa = new System.Data.SqlClient.SqlParameter("Hp07Total", SqlDbType.Float); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("Hp10Total", SqlDbType.Float); VisitExitCommand.Parameters.Add(pa);

            pa = new System.Data.SqlClient.SqlParameter("Hp07Peak", SqlDbType.Float); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("Hp10Peak", SqlDbType.Float); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("HP07PeakTime", SqlDbType.DateTime); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("HP10PeakTime", SqlDbType.DateTime); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("K1", SqlDbType.Int); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("K2", SqlDbType.Int); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("K3", SqlDbType.Int); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("K4", SqlDbType.Int); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("K5", SqlDbType.Int); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("K6", SqlDbType.Int); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("SG", SqlDbType.Int); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("BC", SqlDbType.Int); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("FB", SqlDbType.Int); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("HG", SqlDbType.Int); VisitExitCommand.Parameters.Add(pa);

            pa = new System.Data.SqlClient.SqlParameter("QualityData", SqlDbType.Int); VisitExitCommand.Parameters.Add(pa);

            pa = new System.Data.SqlClient.SqlParameter("alarmStatus", SqlDbType.Int); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("otherAlarms", SqlDbType.Int); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("errorStatus", SqlDbType.Int); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("operatingStatus", SqlDbType.Int); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("ENTRY_EPD_CLOCK", SqlDbType.Decimal, 9); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("EXIT_EPD_CLOCK", SqlDbType.Decimal, 9); VisitExitCommand.Parameters.Add(pa);
            pa = new System.Data.SqlClient.SqlParameter("PersID", SqlDbType.Int, 4); VisitExitCommand.Parameters.Add(pa);
        }

         public void VisitExit(int EpdVisitID, EpdBaseClr.Epd2 ep)
        {
            VisitExitCommand.Parameters["EPDVisitID"].Value = EpdVisitID;
            VisitExitCommand.Parameters["HP07"].Value = ep.Hp07;
            VisitExitCommand.Parameters["HP10"].Value = ep.Hp10;

            VisitExitCommand.Parameters["Hp07Total"].Value = ep.Hp07Total;
            VisitExitCommand.Parameters["Hp10Total"].Value = ep.Hp10Total;

            VisitExitCommand.Parameters["Hp07Peak"].Value = ep.Hp07Peak;
            VisitExitCommand.Parameters["Hp10Peak"].Value = ep.Hp10Peak;
            if (ep.Hp07PeakTime != null)
                VisitExitCommand.Parameters["HP07PeakTime"].Value = ep.Hp07PeakTime;
            else
                VisitExitCommand.Parameters["HP07PeakTime"].Value = DBNull.Value;
            if (ep.Hp10PeakTime != null)
                VisitExitCommand.Parameters["Hp10PeakTime"].Value = ep.Hp07PeakTime;
            else
                VisitExitCommand.Parameters["Hp10PeakTime"].Value = DBNull.Value;

            VisitExitCommand.Parameters["K1"].Value = ep.K1;
            VisitExitCommand.Parameters["K2"].Value = ep.K2;
            VisitExitCommand.Parameters["K3"].Value = ep.K3;
            VisitExitCommand.Parameters["K4"].Value = ep.K4;
            VisitExitCommand.Parameters["K5"].Value = ep.K5;
            VisitExitCommand.Parameters["K6"].Value = ep.K6;
            VisitExitCommand.Parameters["SG"].Value = ep.SG;
            VisitExitCommand.Parameters["BC"].Value = ep.BC;
            VisitExitCommand.Parameters["FB"].Value = ep.FB;
            VisitExitCommand.Parameters["HG"].Value = ep.HG;

            VisitExitCommand.Parameters["QualityData"].Value = ep.QualityData;

            //VisitExitCommand.Parameters["D1"].Value = ep.D1;
            //VisitExitCommand.Parameters["D2"].Value = ep.D2;
            //VisitExitCommand.Parameters["D3"].Value = ep.D3;
            //VisitExitCommand.Parameters["D4"].Value = ep.D4;

            VisitExitCommand.Parameters["alarmStatus"].Value = ep.alarmStatus;
            VisitExitCommand.Parameters["otherAlarms"].Value = ep.otherAlarms;
            VisitExitCommand.Parameters["errorStatus"].Value = ep.errorStatus;
            VisitExitCommand.Parameters["operatingStatus"].Value = ep.operatingStatus;
            VisitExitCommand.Parameters["ENTRY_EPD_CLOCK"].Value = ep.CountsClockBase;
            VisitExitCommand.Parameters["EXIT_EPD_CLOCK"].Value = ep.CountsClock;

            if (int.TryParse(ep.WearerName, out int WearerPersID))
                VisitExitCommand.Parameters["PersID"].Value = WearerPersID;
            else
                VisitExitCommand.Parameters["PersID"].Value = DBNull.Value;

            checkCon(true);
            VisitExitCommand.Connection = localConnection;
            VisitExitCommand.ExecuteNonQuery();
            localConnection.Close();
        }

         public void FillComment()
        {
            if (dtCOE == null) dtCOE =   new DataSet1.CommentOnEventDataTable();
            using (var dt = CommentTA())
            {
                dt.Fill(dtCOE);
            }
        }

         public DataSet1.CommentOnEventRow GetCommentOnEventRow(int id)
        {
            return dtCOE.FindByID_CommnentOnEvent(id);
        }

         public void FillTaskInfo(User _User, DataSet1 dataSet1)
        {
            using (var dataSet1GetTaskInfoTableAdapter = TaskInfoTa()) // new eDosStation.DataSet1TableAdapters.GetTaskInfoTableAdapter())
            {
                //dataSet1GetTaskInfoTableAdapter.Connection.ConnectionString = currentConStr;
                dataSet1GetTaskInfoTableAdapter.Fill(dataSet1.GetTaskInfo, null, _User.Margin);
            }
        }

        public User GetUser( string CSN, string Code)
        {
            User u = new User();
            object[] Values = null;
            using (var cmd = new SqlCommand("GetPersonInfo", localConnection))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                if (int.TryParse(Code, out int PersID) ) cmd.Parameters.AddWithValue("PersID", PersID); 
                if (!string.IsNullOrEmpty(CSN)) cmd.Parameters.AddWithValue("csn", CSN);
                else cmd.Parameters.AddWithValue("csn", DBNull.Value);

                //if (!string.IsNullOrWhiteSpace(Code)) cmd.Parameters.AddWithValue("Badge", Code);
                checkCon(true);
                cmd.Connection = localConnection;
                var rs = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow);
                if (rs.HasRows && rs.Read())
                {
                    Values = new object[rs.FieldCount];
                    int rv = rs.GetValues(Values);
                    u.PersID = (int)Values[rs.GetOrdinal("PersID")];
                    u.LastName = Values[rs.GetOrdinal("LastName")].ToString();
                    u.FirstName = Values[rs.GetOrdinal("FirstName")].ToString();
                    //u.conCode = Values[rs.GetOrdinal("conCode")].ToString();
                    u.Language = Values[rs.GetOrdinal("Language")].ToString();
                    u.AccessAllow = Values[rs.GetOrdinal("AccessAllow")] as bool?;
                    decimal? m = Values[rs.GetOrdinal("Margin")] as decimal?;
                    if (m.HasValue) u.Margin = (double)m.Value; else u.Margin = null;
                    //u.Margin = (double ?)Values[rs.GetOrdinal("Margin")] ;
                    u.VisitIDLocked = Values[rs.GetOrdinal("VisitIDLocked")] as int?;
                    u.ReasonNoAccess = Values[rs.GetOrdinal("ReasonNoAccess")].ToString().Replace("\\n", "\n");
                    u.UserComment = Values[rs.GetOrdinal("Comment")].ToString();
                    u.AllOk = !CheckUser ||  (u.AccessAllow == true && u.VisitIDLocked == 0)   ;
                }
                else
                {
                    // Not found
                    u.PersID = 0;
                    u.LastName = "Not Found";
                    u.FirstName = "Not Found";
                    //u.conCode = "A1";
                    u.Language = "N";
                    u.AccessAllow = false;
                    u.Margin = 0;
                    u.VisitIDLocked = 0;
                    u.ReasonNoAccess = "Not found";
                    u.UserComment = "";
                    u.AllOk = !CheckUser || (u.AccessAllow == true && u.VisitIDLocked == 0);
                }
                rs.Close();
            }
            return u;
        }
    }
}
