using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using DotNettyFrom.config;
using DotNettyFrom.model;
using DotNettyFrom.util;

namespace DotNettyFrom.db
{
    public class DbHelper
    {
        private static DbHelper Instance;
        private static readonly object locker = new object();

        private Dictionary<string, string> _idInfo { get; }

        private OleDbConnection _DbConn = null;


        private DbHelper(string dbPath)
        {
            _idInfo = new Dictionary<string, string>();
            if (dbPath.Equals(""))
            {
                _DbConn = new OleDbConnection(DBConfig.ConnStr + Application.StartupPath + @"\" + DBConfig.DbFileName);
            }
            else
            {
                _DbConn = new OleDbConnection(DBConfig.ConnStr + dbPath);
            }
            _DbConn.Open();
        }

        public void CloseAccessDb()
        {
            _DbConn?.Close();
        }


        public static void Init(string dbPath = "")
        {
            lock (locker)
            {
                if (Instance == null)
                {
                    Instance = new DbHelper(dbPath);
                }
            }
        }

        public static DbHelper GetInstance()
        {
            if (Instance == null)
            {
                Init();
            }
            return Instance;
        }


        //reset user value
        public int ResetUserData()
        {
            string table = DBConfig.GetTableName();
            string sql = "update " + table + " set user_value=default_value";
            using (OleDbCommand inst = new OleDbCommand(sql, _DbConn))
            {
                return inst.ExecuteNonQuery();
            }
        }

        //Insert CMD
        public int InsertCmd(WvBean wb)
        {
            if (wb == null)
            {
                return -1;
            }
            string table = DBConfig.GetTableName();
            string sql =
                "INSERT INTO " + table +
                "(my_index,MY_ID,idInfo,len,mode,desp,default_value,user_value,remark,protocol_no) VALUES(";
            sql += wb.index + ",";
            sql += "'" + wb.myId + "',";
            sql += "'" + wb.idInfo + "',";
            sql += "'" + wb.len + "',";
            sql += "'" + wb.mode + "',";
            sql += "'" + wb.desp + "',";
            sql += "'" + wb.defalutValue + "',";
            sql += "'" + wb.userValue + "',";
            sql += "'" + wb.remark + "',";
            sql += wb.protocolNo + ")";
            using (OleDbCommand inst = new OleDbCommand(sql, _DbConn))
            {
                return inst.ExecuteNonQuery();
            }
        }

        public int InsertHistory(int _type, string his_data)
        {
            string tableName = "";
            switch (_type)
            {
                case SystemConfig.IpHisType:
                    tableName = DBConfig.IpHis;
                    break;
                case SystemConfig.AddrHisType:
                    tableName = DBConfig.AddrHis;
                    break;
                case SystemConfig.CcidHisType:
                    tableName = DBConfig.CcidHis;
                    break;
                case SystemConfig.SendHisType:
                    tableName = DBConfig.SendHis;
                    break;
                default:
                    return -1;
            }

            string sql = "insert into " + tableName + "(i_date,his_data) values('" + DateTime.Now + "','" + his_data +
                         "')";
            using (OleDbCommand inst = new OleDbCommand(sql, _DbConn))
            {
                return inst.ExecuteNonQuery();
            }
        }

        //Delete
        public int DeleteCmdById(int id)
        {
            string table = DBConfig.GetTableName();
            string sql = "delete from " + table + " where ID=" + id;
            using (OleDbCommand inst = new OleDbCommand(sql, _DbConn))
            {
                return inst.ExecuteNonQuery();
            }
        }

        public int DeleteAllData()
        {
            string table = DBConfig.GetTableName();
            string sql = @"delete from " + table;
            using (OleDbCommand inst = new OleDbCommand(sql, _DbConn))
            {
                return inst.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 删除历史记录
        /// </summary>
        /// <param name="_type"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteHistoryById(int _type, int id)
        {
            string tableName;
            switch (_type)
            {
                case SystemConfig.IpHisType:
                    tableName = DBConfig.IpHis;
                    break;
                case SystemConfig.AddrHisType:
                    tableName = DBConfig.AddrHis;
                    break;
                case SystemConfig.CcidHisType:
                    tableName = DBConfig.CcidHis;
                    break;
                case SystemConfig.SendHisType:
                    tableName = DBConfig.SendHis;
                    break;
                default:
                    return -1;
            }
            string sql = @"delete from " + tableName + " where ID=" + id;
            using (OleDbCommand inst = new OleDbCommand(sql, _DbConn))
            {
                return inst.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 删除所有历史记录
        /// </summary>
        /// <param name="_type"></param>
        /// <returns></returns>
        public int DeleteAllHistory(int _type)
        {
            string tableName;
            switch (_type)
            {
                case SystemConfig.IpHisType:
                    tableName = DBConfig.IpHis;
                    break;
                case SystemConfig.AddrHisType:
                    tableName = DBConfig.AddrHis;
                    break;
                case SystemConfig.CcidHisType:
                    tableName = DBConfig.CcidHis;
                    break;
                case SystemConfig.SendHisType:
                    tableName = DBConfig.SendHis;
                    break;
                default:
                    return -1;
            }
            string sql = @"delete from " + tableName;
            using (OleDbCommand inst = new OleDbCommand(sql, _DbConn))
            {
                return inst.ExecuteNonQuery();
            }
        }

        //Update
        public int UpdateCmd(WvBean wb)
        {
            string table = DBConfig.GetTableName();

            string updateSQL = "update " + table + " set my_index=" + wb.index + ",MY_ID=\"" + wb.myId +
                               "\",idInfo=\"" +
                               wb.idInfo +
                               "\",len=\"" + wb.len +
                               "\",mode=\"" +
                               wb.mode + "\",desp=\"" + wb.desp + "\",default_value=\"" + wb.defalutValue +
                               "\",user_value=\"" +
                               wb.userValue + "\",remark=\"" + wb.remark + "\" where ID=" + wb.id;

            using (OleDbCommand inst = new OleDbCommand(updateSQL, _DbConn))
            {
                return inst.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 首次数据库加载到内存
        /// </summary>
        /// <param name="sql"></param>
        public void ReloadMoneryCache(string sql = null)
        {
            if (sql == null)
            {
                string table = DBConfig.GetTableName();
                sql = @"select * from " + table;
            }
            using (OleDbCommand sqlcmd = new OleDbCommand(sql, _DbConn))
            {
                using (OleDbDataReader reader = sqlcmd.ExecuteReader())
                {
                    while (reader != null && reader.Read()) //这个read调用很重要！不写的话运行时将提示找不到数据  
                    {
                        var myId = reader.GetString(2);
                        var userValue = CheckUtil.StringIsEmpty(reader.GetString(8));
                        if (!_idInfo.ContainsKey(myId))
                            _idInfo.Add(myId, userValue);
                    }
                }
            }
        }

        //Select
        public DataTable SelectCmds(string sql = null)
        {
            if (sql == null)
            {
                string table = DBConfig.GetTableName();
                sql = @"select * from " + table;
            }

            DataTable dataTable = new DataTable();

            using (OleDbCommand sqlcmd = new OleDbCommand(sql, _DbConn))
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter(sqlcmd);
                dataTable.Clear();
                adapter.Fill(dataTable);

                using (OleDbDataReader reader = sqlcmd.ExecuteReader())
                {
                    while (reader.Read()) //这个read调用很重要！不写的话运行时将提示找不到数据  
                    {
                        var myId = reader.GetString(2);
                        var userValue = CheckUtil.StringIsEmpty(reader.GetString(8));
                        if (!_idInfo.ContainsKey(myId))
                            _idInfo.Add(myId, userValue);
                    }
                }
            }
            return dataTable;
        }

        public WvBean SelectOneCmdByIndex(int index)
        {
            string table = DBConfig.GetTableName();
            string sql = @"select * from " + table + " where my_index=" + index;
            return SelectOneCmd(sql);
        }

        public WvBean SelectOneCmdById(int _id)
        {
            string table = DBConfig.GetTableName();
            string sql = @"select * from " + table + " where id=" + _id;
            return SelectOneCmd(sql);
        }

        private WvBean SelectOneCmd(string sql)
        {
            WvBean wb = null;
            using (OleDbCommand sqlcmd = new OleDbCommand(sql, _DbConn))
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter(sqlcmd);

                #region 读数据

                using (OleDbDataReader reader = sqlcmd.ExecuteReader())
                {
                    while (reader.Read()) //这个read调用很重要！不写的话运行时将提示找不到数据  
                    {
                        wb = new WvBean();
                        int id = 0;
                        int index = 0;
                        var myId = "";
                        var idInfo = "";
                        var len = "";
                        var mode = "";
                        var desp = "";
                        var defalutValue = "";
                        var userValue = "";
                        var remark = "";
                        var protocolNo = 0;

                        id = reader.GetInt32(0);
                        index = reader.GetInt32(1);
                        myId = reader.GetString(2);
                        idInfo = CheckUtil.StringIsEmpty(reader.GetString(3));
                        len = CheckUtil.StringIsEmpty(reader.GetString(4));
                        mode = CheckUtil.StringIsEmpty(reader.GetString(5));
                        desp = CheckUtil.StringIsEmpty(reader.GetString(6));
                        defalutValue = CheckUtil.StringIsEmpty(reader.GetString(7));
                        userValue = CheckUtil.StringIsEmpty(reader.GetString(8));
                        remark = CheckUtil.StringIsEmpty(reader.GetString(9));
                        protocolNo = reader.GetInt32(10);

                        wb.id = id;
                        wb.index = index;
                        wb.myId = myId;
                        wb.idInfo = idInfo;
                        wb.len = len;
                        wb.mode = mode;
                        wb.desp = desp;
                        wb.defalutValue = defalutValue;
                        wb.userValue = userValue;
                        wb.remark = remark;
                        wb.protocolNo = protocolNo;

                        break;
                    }
                }

                #endregion
            }
            return wb;
        }


        public DataTable SelectHistory(string sql)
        {
            DataTable dataTable = new DataTable();
            using (OleDbCommand sqlcmd = new OleDbCommand(sql, _DbConn))
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter(sqlcmd);
                dataTable.Clear();
                adapter.Fill(dataTable);
            }
            return dataTable;
        }
    }
}