namespace IStation.DAL.SQLite
{
    /// <summary>
    /// 
    /// </summary>
    public partial class AnalysisParameter : Yw.DAL.SQLite.BaseDAL<Entity.AnalysisParameter>, IAnalysisParameter
    {
        /// <summary>
        /// 
        /// </summary>
        public override ConnectionConfig ConnectionConfig
        {
            get { return ConfigHelper.AnalysisConnectionConfig; }

        }

        /// <summary>
        /// 
        /// </summary>
        private string TableNamePrefix
        {
            get { return "AnalysisParameter_"; }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="runFlag"></param>
        /// <returns></returns>
        public string GetTableName(string runFlag)
        {
            return $"{this.TableNamePrefix}{runFlag}";
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private string GetCreateTableSql(string tableName)
        {
            var sql = $"CREATE TABLE  {tableName} (ID   BIGINT NOT NULL\r\n   PRIMARY KEY,\r\n    PumpID  BIGINT,\r\n    Hz   REAL,\r\n    Head   REAL,\r\n    PressureDiff REAL,\r\n    Flow  REAL,\r\n    Power   REAL,\r\n    WP    REAL,\r\n    UWP REAL\r\n);";
            return sql;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private string GetExistTableSql(string tableName)
        {
            return ConfigHelper.GetExistTableSql(tableName);
        }


        /// <summary>
        /// 获取全部表名
        /// </summary>
        public List<string> GetAllTableName()
        {
            using (SqlSugarClient db = new SqlSugarClient(ConnectionConfig))
            {
                var list = new List<string>();
                var sql = $"select name from sqlite_master where type='table' and name like '%{this.TableNamePrefix}%';";
                using (var reader = db.Ado.GetDataReader(sql))
                {
                    while (reader.Read())
                    {
                        var tableName = reader["name"].ToString();
                        list.Add(tableName);
                    }
                }
                return list;
            }
        }

        /// <summary>
        /// 获取全部表
        /// </summary> 
        public Dictionary<string, List<Entity.AnalysisParameter>> GetAllTable()
        {
            //var tableNames = new List<string>();
            //using (SqlSugarClient db = new SqlSugarClient(ConnectionConfig))
            //{
            //    var sql_sel_table = $"select name from sqlite_master where type='table' and name like '%{this.TableNamePrefix}%';";
            //    var reader = db.Ado.GetDataReader(sql_sel_table);
            //    while (reader.Read())
            //    {
            //        var tableName = reader["name"].ToString();
            //        tableNames.Add(tableName);
            //    }
            //}

            var tableNames = GetAllTableName();
            if (tableNames == null || !tableNames.Any())
                return default;

            var dict = new Dictionary<string, List<Entity.AnalysisParameter>>();
            using (SqlSugarClient db = new SqlSugarClient(ConnectionConfig))
            {
                foreach (var tableName in tableNames)
                {
                    var sql = $"select * from {tableName}";
                    var list = db.Queryable<Entity.AnalysisParameter>().AS(tableName).ToList();
                    if (list == null || !list.Any())
                        continue;
                    dict.Add(tableName, list);
                }
                return dict;
            }
        }


        /// <summary>
        /// 大批量插入(表不存在就新建)
        /// </summary>
        public bool BulkInsertsEx(string runFlag, List<Entity.AnalysisParameter> list)
        {
            if (list == null || list.Count < 1)
                return default;
            var tableName = GetTableName(runFlag);
            using (SqlSugarClient db = new SqlSugarClient(ConnectionConfig))
            {
                var exist_sql = GetExistTableSql(tableName);
                if (db.Ado.GetInt(exist_sql) < 1)
                {
                    var sql_create_table = GetCreateTableSql(tableName);
                    db.Ado.ExecuteCommand(sql_create_table);
                }
                //大数据写入
                return db.Fastest<Entity.AnalysisParameter>().AS(tableName).BulkCopy(list) > 0;
            }
        }

        /// <summary>
        /// 判断数据库是否存在表
        /// </summary> 
        public bool ExistTable(string runFlag)
        {
            using (SqlSugarClient db = new SqlSugarClient(ConnectionConfig))
            {
                var tableName = GetTableName(runFlag);
                var sql = GetExistTableSql(tableName);
                var bol = db.Ado.ExecuteCommand(sql) > 0;
                return bol;
            }
        }

        /// <summary>
        /// 删除所有表
        /// </summary>
        public bool DeleteAllTable()
        {
            var tables = GetAllTableName();
            if (tables == null || !tables.Any())
                return false;
            using (SqlSugarClient db = new SqlSugarClient(ConnectionConfig))
            {
                db.BeginTran();
                foreach (var table in tables)
                {
                    var sql = $"drop table {table};";
                    db.Ado.ExecuteCommand(sql);
                }
                db.CommitTran();
            }
            return true;
        }


        /// <summary>
        /// 查询
        /// </summary>
        public List<Entity.AnalysisParameter> GetList(string runFlag, double targetHead)
        {
            var tableName = GetTableName(runFlag);
            using (SqlSugarClient db = new SqlSugarClient(ConnectionConfig))
            {
                var sql = GetExistTableSql(tableName);
                if (db.Ado.GetInt(sql) < 1)
                {
                    return default;
                }
                var list = db.Queryable<Entity.AnalysisParameter>().AS(tableName)
                      .Where(x => x.Head == targetHead)
                      .OrderBy(x => x.Power)
                      .ToList();
                return list;
            }
        }
    }
}