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

using SystemX.Net.DB.LogProcess.DBType;
using SystemX.Common;
using SystemX.Common.Serialization;

using static SystemX.Net.Platform.Common.Util.LogMessage;

using log4net.Config;

namespace SystemX.Net.DB.LogProcess
{
    public class XLogDBConnManager
    {
        public enum eDbType
        {
            Unknown,
            MS_SQL
        }

        public enum eConnCategory
        {
            Main,
            ShortTerm,
            LongTerm
        }

        private Stopwatch stMainDBAccessTime;

        private Stopwatch stShortTermDBAccessTime;

        private Stopwatch stLongTermDBAccessTime;

        private bool MAIN_CONNECT_STATE { set; get; }

        private bool SHORTTERM_CONNECT_STATE { set; get; }

        private bool LONGTERM_CONNECT_STATE { set; get; }

        private bool SET_CONN_STATE { set; get; }

        private string strCurrentLongTermSchemaName { set; get; }
        private string strCurrentShortTermSummaryTableName { set; get; }
        private string strCurrentShortTermLogTableName { set; get; }

        public bool CONNECT_STATE
        {
            get { return MAIN_CONNECT_STATE && SHORTTERM_CONNECT_STATE && LONGTERM_CONNECT_STATE && SET_CONN_STATE; }
        }

        protected void SET_CONNECT_STATE(eConnCategory eConn, bool bValue)
        {
            switch (eConn)
            {
                case eConnCategory.Main:
                    MAIN_CONNECT_STATE = bValue;
                    break;
                case eConnCategory.ShortTerm:
                    SHORTTERM_CONNECT_STATE = bValue;
                    break;
                case eConnCategory.LongTerm:
                    LONGTERM_CONNECT_STATE = bValue;
                    break; 
            }
        }

        protected bool GET_CONNECT_STATE(eConnCategory eConn)
        {
            switch (eConn)
            {
                case eConnCategory.Main:
                    return MAIN_CONNECT_STATE;
                case eConnCategory.ShortTerm:
                    return SHORTTERM_CONNECT_STATE;
                case eConnCategory.LongTerm:
                    return LONGTERM_CONNECT_STATE;
            }

            return false;
        }

        private string ConfigPath { get; set; }

        public bool SetConfigFilePath(string strConfigFilePath, bool bReadConfig = false)
        {
            ConfigPath = strConfigFilePath;

            if (bReadConfig)
                return InfoConnection.ReadConfig(ConfigPath);

            return true;
        }

        public string GetConfigFilePath()
        {
            return ConfigPath;
        }

        private XDBConnInfo InfoConnection;
        
        public XDBConnInfo GetDBConnectInfo()
        {
            return InfoConnection;
        }

        private IDBLogControl MgrMainConnection { set; get; }

        private IDBLogControl MgrShortTermConnection { set; get; }

        private IDBLogControl MgrLongTermConnection { set; get; }

        public XLogDBConnManager()
        {
            stMainDBAccessTime = new Stopwatch();
            stMainDBAccessTime.Start();

            stShortTermDBAccessTime = new Stopwatch();
            stShortTermDBAccessTime.Start();

            stLongTermDBAccessTime = new Stopwatch();
            stLongTermDBAccessTime.Start();

            MAIN_CONNECT_STATE = false;
            SHORTTERM_CONNECT_STATE = false;
            LONGTERM_CONNECT_STATE = false;

            InfoConnection = new XDBConnInfo();

            strCurrentLongTermSchemaName = string.Empty;
            strCurrentShortTermSummaryTableName = string.Empty;
            strCurrentShortTermLogTableName = string.Empty;

            SET_CONN_STATE = true;
        }

        public long GetMainDBAccessTime()
        {
            if (stMainDBAccessTime != null)
                return stMainDBAccessTime.ElapsedMilliseconds;
            else
                return long.MinValue;
        }

        public void SetMainDBAccessTime()
        {
            if (stMainDBAccessTime != null)
                stMainDBAccessTime.Restart();
        }

        public long GetShortTermDBAccessTime()
        {
            if (stShortTermDBAccessTime != null)
                return stShortTermDBAccessTime.ElapsedMilliseconds;
            else
                return long.MinValue;
        }

        public void SetShortTermDBAccessTime()
        {
            if (stShortTermDBAccessTime != null)
                stShortTermDBAccessTime.Restart();
        }

        public long GetLongTermDBAccessTime()
        {
            if (stLongTermDBAccessTime != null)
                return stLongTermDBAccessTime.ElapsedMilliseconds;
            else
                return long.MinValue;
        }

        public void SetLongTermDBAccessTime()
        {
            if (stLongTermDBAccessTime != null)
                stLongTermDBAccessTime.Restart();
        }

        public IDBLogControl CurrentConnection(eConnCategory eConn)
        {
            if (GET_CONNECT_STATE(eConn))
            {
                switch (eConn)
                {
                    case eConnCategory.Main:
                        {
                            if (stMainDBAccessTime != null)
                                stMainDBAccessTime.Restart();
                        }
                        return MgrMainConnection;
                    case eConnCategory.ShortTerm:
                        {
                            if (stShortTermDBAccessTime != null)
                                stShortTermDBAccessTime.Restart();
                        }
                        return MgrShortTermConnection;
                    case eConnCategory.LongTerm:
                        {
                            if (stLongTermDBAccessTime != null)
                                stLongTermDBAccessTime.Restart();
                        }
                        return MgrLongTermConnection;          
                }

                return null;
            }
            else
                return null;
        }

        private bool ReadyForConnection(eConnCategory eConn)
        {
            bool bResult = true;

            try
            {
                string strSetType = string.Empty;

                eDbType eType = eDbType.Unknown;

                switch (eConn)
                {
                    case eConnCategory.Main:
                        strSetType = InfoConnection.ConnMain.Type;
                        break;
                    case eConnCategory.ShortTerm:
                        strSetType = InfoConnection.ConnShortTerm.Type;
                        break;
                    case eConnCategory.LongTerm:
                        strSetType = InfoConnection.ConnLongTerm.Type;
                        break;
                }

                if (!Enum.TryParse(strSetType, out eType))
                    throw new Exception("Failed Database type check.");

                switch (eConn)
                {
                    case eConnCategory.Main:
                        MgrMainConnection = CreateDBConnMgr(eType);
                        MgrMainConnection.ConnectionText = InfoConnection.GetConnectionStatement(eType, eConn);
                        break;
                    case eConnCategory.ShortTerm:
                        MgrShortTermConnection = CreateDBConnMgr(eType);
                        MgrShortTermConnection.ConnectionText = InfoConnection.GetConnectionStatement(eType, eConn);
                        break;
                    case eConnCategory.LongTerm:
                        MgrLongTermConnection = CreateDBConnMgr(eType);
                        MgrLongTermConnection.ConnectionText = InfoConnection.GetConnectionStatement(eType, eConn);
                        break;
                }
            }
            catch (Exception e)
            {
                bResult = false;

                MessageOutput.ConsoleWrite(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss>>") + @"Failed make connection.[SystemX.Net.DB : XDBConnManager.MakeConnection]\r\n" + e.Message, ConsoleColor.Yellow, LogMessageLevel.DEBUG);
            }

            return bResult;
        }

        private bool MakeConnection(eConnCategory eConn)
        {
            bool bResult = true;

            try
            {
                bResult = ReadyForConnection(eConn);

                if(bResult)
                {
                    switch (eConn)
                    {
                        case eConnCategory.Main:
                            if (!MgrMainConnection.OpenConnection())
                                throw new Exception("Failed [Main] IDBControl.OpenConnection()");
                            break;
                        case eConnCategory.ShortTerm:
                            if (!MgrShortTermConnection.OpenConnection())
                                throw new Exception("Failed [ShortTerm] IDBControl.OpenConnection()");
                            break;
                        case eConnCategory.LongTerm:
                            if (!MgrLongTermConnection.OpenConnection())
                                throw new Exception("Failed [LongTerm] IDBControl.OpenConnection()");
                            break;
                    }
                }
            }
            catch (Exception e)
            {
                bResult = false;

                MessageOutput.ConsoleWrite(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss>>") + @"Failed make connection.[SystemX.Net.DB : XDBConnManager.MakeConnection]\r\n" + e.Message, ConsoleColor.Yellow, LogMessageLevel.DEBUG);
            }

            return bResult;
        }


        private bool MakeConnection(eConnCategory eConn, bool bUseHostInfo, string strHostTableName, string strUserTableName)
        {
            bool bResult = true;

            try
            {
                bResult = ReadyForConnection(eConn);

                if (bResult)
                {
                    switch (eConn)
                    {
                        case eConnCategory.Main:
                            if (!MgrMainConnection.OpenConnection(bUseHostInfo, strHostTableName, strUserTableName))
                                throw new Exception("Failed [Main] IDBControl.OpenConnection()");
                            break;
                        case eConnCategory.ShortTerm:
                            if (!MgrShortTermConnection.OpenConnection(bUseHostInfo, strHostTableName, strUserTableName))
                                throw new Exception("Failed [ShortTerm] IDBControl.OpenConnection()");
                            break;
                        case eConnCategory.LongTerm:
                            if (!MgrLongTermConnection.OpenConnection(bUseHostInfo, strHostTableName, strUserTableName))
                                throw new Exception("Failed [LongTerm] IDBControl.OpenConnection()");
                            break;
                    }
                }
            }
            catch (Exception e)
            {
                bResult = false;

                MessageOutput.ConsoleWrite(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss>>") + @"Failed make connection.[SystemX.Net.DB : XDBConnManager.MakeConnection]\r\n" + e.Message, ConsoleColor.Yellow, LogMessageLevel.DEBUG);
            }

            return bResult;
        }

        public string GetDataDatabaseSchemaName(eConnCategory SetCategory)
        {
            if (SetCategory == eConnCategory.LongTerm) return InfoConnection.ConnLongTerm.SCHEMA;
            else throw new ArgumentException("Get of connection schema is supported only in LongTerm.");
        }
        public bool SetDataDatabaseSchemaName(eConnCategory SetCategory, string strSetName)
        {
            if (SetCategory == eConnCategory.LongTerm)
            {
                InfoConnection.ConnLongTerm.SCHEMA = strSetName;

                return true;
            }
            else
                throw new ArgumentException("Change of connection schema is supported only in LongTerm.");
        }
        public string GetDataDatabaseSummaryTableName(eConnCategory SetCategory)
        {
            if (SetCategory == eConnCategory.ShortTerm) return InfoConnection.ConnShortTerm.SUMMARY_TABLE;
            else throw new ArgumentException("Get of summary table is supported only in ShortTerm.");
        }
        public bool SetDataDatabaseSummaryTableName(eConnCategory SetCategory, string strSetName)
        {
            if (SetCategory == eConnCategory.ShortTerm)
            {
                InfoConnection.ConnShortTerm.SUMMARY_TABLE = strSetName;

                return true;
            }
            else
                throw new ArgumentException("Change of summary table is supported only in ShortTerm.");
        }
        public string GetDataDatabaseLogTableName(eConnCategory SetCategory)
        {
            if (SetCategory == eConnCategory.ShortTerm) return InfoConnection.ConnShortTerm.LOG_TABLE;
            else throw new ArgumentException("Get of log table is supported only in ShortTerm.");
        }
        public bool SetDataDatabaseLogTableName(eConnCategory SetCategory, string strSetName)
        {
            if (SetCategory == eConnCategory.ShortTerm)
            {
                InfoConnection.ConnShortTerm.LOG_TABLE = strSetName;

                return true;
            }
            else
                throw new ArgumentException("Change of log table is supported only in ShortTerm.");
        }

        public bool CheckDatabaseConnection(bool bReadCfgFile = true, bool bAutoAttachNameDataDb = true)
        {
            SET_CONN_STATE = true;

            return OpenConnection(bReadCfgFile, bAutoAttachNameDataDb);
        }

        public bool OpenConnection(bool bReadCfgFile = true, bool bAutoAttachNameDataDb = true)
        {
            SET_CONNECT_STATE(eConnCategory.Main, true);
            SET_CONNECT_STATE(eConnCategory.ShortTerm, true);
            SET_CONNECT_STATE(eConnCategory.LongTerm, true);

            try
            {
                if (bReadCfgFile)
                {
                    if (InfoConnection.ReadConfig(ConfigPath) == false)
                    {
                        MessageOutput.ConsoleWrite(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss>>") + @"Fail read DB info file.[SystemX.Net.DB : XDBConnManager.OpenConnection]", ConsoleColor.Yellow, LogMessageLevel.DEBUG);

                        throw new Exception("Failed ReadConfig()");
                    }
                }

                bool bNeedTryLongTermConn = false;
                bool bNeedTryShortTermConn = false;

                if (bAutoAttachNameDataDb)
                {
                    string strYYYY = DateTime.Now.ToString("yyyy");
                    string strMM = DateTime.Now.ToString("MM");
                    string strdd = DateTime.Now.ToString("dd");

                    string strGetSchemaName = GetDataDatabaseSchemaName(eConnCategory.LongTerm);
                    string strGetSummaryTableName = GetDataDatabaseSummaryTableName(eConnCategory.ShortTerm);
                    string strGetLogTableName = GetDataDatabaseLogTableName(eConnCategory.ShortTerm);

                    //LongTerm Schema Name
                    string[] GetNames = strGetSchemaName.Split('_');

                    if(GetNames.Count() >= 1)
                    {
                        strGetSchemaName = GetNames[0] + ("_" + strYYYY);

                        SetDataDatabaseSchemaName(eConnCategory.LongTerm, strGetSchemaName);

                        if (strCurrentLongTermSchemaName.Length <= 0)
                        {
                            strCurrentLongTermSchemaName = GetDataDatabaseSchemaName(eConnCategory.LongTerm);

                            bNeedTryLongTermConn = true;
                        }
                        else
                        {
                            if(strCurrentLongTermSchemaName.CompareTo(GetDataDatabaseSchemaName(eConnCategory.LongTerm)) != 0)
                            {
                                strCurrentLongTermSchemaName = GetDataDatabaseSchemaName(eConnCategory.LongTerm);

                                bNeedTryLongTermConn = true;
                            }
                        }
                    }

                    //ShortTerm Summary Table Name
                    GetNames = strGetSummaryTableName.Split('_');

                    if (GetNames.Count() >= 2)
                    {
                        strGetSummaryTableName = GetNames[0] + "_" + GetNames[1] + ("_" + strYYYY);

                        SetDataDatabaseSummaryTableName(eConnCategory.ShortTerm, strGetSummaryTableName);

                        if (strCurrentShortTermSummaryTableName.Length <= 0)
                        {
                            strCurrentShortTermSummaryTableName = GetDataDatabaseSummaryTableName(eConnCategory.ShortTerm);

                            bNeedTryShortTermConn = true;
                        }
                        else
                        {
                            if (strCurrentShortTermSummaryTableName.CompareTo(GetDataDatabaseSummaryTableName(eConnCategory.ShortTerm)) != 0)
                            {
                                strCurrentShortTermSummaryTableName = GetDataDatabaseSummaryTableName(eConnCategory.ShortTerm);

                                bNeedTryShortTermConn = true;
                            }
                        }
                    }

                    //ShortTerm Log Table Name
                    GetNames = strGetLogTableName.Split('_');

                    if (GetNames.Count() >= 2)
                    {
                        strGetLogTableName = GetNames[0] + "_" + GetNames[1] + ("_" + strYYYY);

                        SetDataDatabaseLogTableName(eConnCategory.ShortTerm, strGetLogTableName);

                        if (strCurrentShortTermLogTableName.Length <= 0)
                        {
                            strCurrentShortTermLogTableName = GetDataDatabaseLogTableName(eConnCategory.ShortTerm);

                            bNeedTryShortTermConn = true;
                        }
                        else
                        {
                            if (strCurrentShortTermLogTableName.CompareTo(GetDataDatabaseLogTableName(eConnCategory.ShortTerm)) != 0)
                            {
                                strCurrentShortTermLogTableName = GetDataDatabaseLogTableName(eConnCategory.ShortTerm);

                                bNeedTryShortTermConn = true;
                            }
                        }
                    }
                }

                if(bNeedTryShortTermConn)
                    SET_CONNECT_STATE(eConnCategory.ShortTerm, MakeConnection(eConnCategory.ShortTerm));
                if(bNeedTryLongTermConn)
                    SET_CONNECT_STATE(eConnCategory.LongTerm, MakeConnection(eConnCategory.LongTerm));

                if (bNeedTryShortTermConn || bNeedTryLongTermConn)
                    SET_CONNECT_STATE(eConnCategory.Main, MakeConnection(eConnCategory.Main));

                if (GET_CONNECT_STATE(eConnCategory.Main) == false ||
                    GET_CONNECT_STATE(eConnCategory.ShortTerm) == false ||
                    GET_CONNECT_STATE(eConnCategory.LongTerm) == false)
                    throw new Exception();
            }
            catch (Exception e)
            {
                MessageOutput.ConsoleWrite(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss>>") + @"Fail db connect.[SystemX.Net.DB : XDBConnManager.OpenConnection - Parameter Type]\r\n" + e.Message, ConsoleColor.Yellow, LogMessageLevel.DEBUG);

                SET_CONN_STATE = false;
            }

            return CONNECT_STATE;
        }

        public bool OpenConnection(bool bUseHostInfo, string strHostTableName, string strUserTableName, bool bReadCfgFile = true)
        {
            SET_CONNECT_STATE(eConnCategory.Main, true);
            SET_CONNECT_STATE(eConnCategory.ShortTerm, true);
            SET_CONNECT_STATE(eConnCategory.LongTerm, true);

            try
            {
                if (bReadCfgFile)
                {
                    if (InfoConnection.ReadConfig(ConfigPath) == false)
                    {
                        MessageOutput.ConsoleWrite(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss>>") + @"Fail read DB info file.[SystemX.Net.DB : XDBConnManager.OpenConnection]", ConsoleColor.Yellow, LogMessageLevel.DEBUG);

                        throw new Exception("Failed ReadConfig()");
                    }
                }

                SET_CONNECT_STATE(eConnCategory.Main, MakeConnection(eConnCategory.Main, bUseHostInfo, strHostTableName, strUserTableName));
                SET_CONNECT_STATE(eConnCategory.ShortTerm, MakeConnection(eConnCategory.ShortTerm));
                SET_CONNECT_STATE(eConnCategory.LongTerm, MakeConnection(eConnCategory.LongTerm));

                if (GET_CONNECT_STATE(eConnCategory.Main) == false ||
                    GET_CONNECT_STATE(eConnCategory.ShortTerm) == false ||
                    GET_CONNECT_STATE(eConnCategory.LongTerm) == false)
                    throw new Exception();
            }
            catch (Exception e)
            {
                MessageOutput.ConsoleWrite(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss>>") + @"Fail db connect.[SystemX.Net.DB : XDBConnManager.OpenConnection - Parameter Type]\r\n" + e.Message, ConsoleColor.Yellow, LogMessageLevel.DEBUG);

                SET_CONN_STATE = false;
            }

            return CONNECT_STATE;
        }

        IDBLogControl CreateDBConnMgr(eDbType eType)
        {
            switch (eType)
            {
                case eDbType.MS_SQL:
                    return new XDBLogTMSSQL();
                default:
                    return null;
            }
        }
        public SqlDataReader QueryDatabase(eConnCategory eConn, string strStmt)
        {
            IDBLogControl ctrlDB = null;

            switch (eConn)
            {
                case eConnCategory.Main:
                    ctrlDB = MgrMainConnection; 
                    break;
                case eConnCategory.ShortTerm:
                    ctrlDB = MgrShortTermConnection;
                    break;
                case eConnCategory.LongTerm:
                    ctrlDB = MgrLongTermConnection;
                    break;
            }

            SqlDataReader drResult = ctrlDB.QueryDatabase(strStmt);

            return drResult;
        }
        public DataTable QueryDatabaseInTable(eConnCategory eConn, string strStmt)
        {
            IDBLogControl ctrlDB = null;

            switch (eConn)
            {
                case eConnCategory.Main:
                    ctrlDB = MgrMainConnection;
                    break;
                case eConnCategory.ShortTerm:
                    ctrlDB = MgrShortTermConnection;
                    break;
                case eConnCategory.LongTerm:
                    ctrlDB = MgrLongTermConnection;
                    break;
            }

            DataTable dtResult = ctrlDB.QueryDataTable(strStmt);

            return dtResult;
        }
        public DataSet QueryDatabaseInSet(eConnCategory eConn, string strStmt)
        {
            IDBLogControl ctrlDB = null;

            switch (eConn)
            {
                case eConnCategory.Main:
                    ctrlDB = MgrMainConnection;
                    break;
                case eConnCategory.ShortTerm:
                    ctrlDB = MgrShortTermConnection;
                    break;
                case eConnCategory.LongTerm:
                    ctrlDB = MgrLongTermConnection;
                    break;
            }

            DataSet dsResult = ctrlDB.QueryDataSet(strStmt);

            return dsResult;
        }
    }
}