using SqlSugar;
using System.Collections.Generic;

namespace IStation.DAL.SQLite
{
    /// <summary>
    /// 
    /// </summary>
    public class MonitorRecord : IMonitorRecord
    {
        /// <summary>
        /// 
        /// </summary>
        public ConnectionConfig ConnectionConfig
        {
            get { return ConfigHelper.MonitorConnectionConfig; }
        }

        #region Query

        /// <summary>
        /// 查询时间区间内的数据
        /// </summary>
        public List<Entity.MonitorRecord> GetByTimeRange(DateTime StartTime, DateTime EndTime)
        {
            if (StartTime > EndTime)
                return default;
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Queryable<Entity.MonitorRecord>()
                    .Where(x => x.DataTime > StartTime.AddSeconds(-1) && x.DataTime < EndTime.AddSeconds(1))
                    .OrderBy(x => x.DataTime, OrderByType.Asc)
                    .ToList();
            }
        }
         

        /// <summary>
        /// 查询接收时间区间内的数据
        /// </summary>
        public List<Entity.MonitorRecord> GetByReceiptTimeRange(DateTime StartTime, DateTime EndTime)
        {
            if (StartTime > EndTime)
                return default;
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Queryable<Entity.MonitorRecord>()
                    .Where(x => x.ReceiptTime > StartTime.AddSeconds(-1) && x.ReceiptTime < EndTime.AddSeconds(1))
                    .OrderBy(x => x.ReceiptTime, OrderByType.Asc)
                    .ToList();
            }
        }

        #region 时间区间

        /// <summary>
        /// 通过 DataCode 查询时间区间内的数据
        /// </summary>
        public List<Entity.MonitorRecord> GetByDataCodeOfTimeRange(string DataCode, DateTime StartTime, DateTime EndTime)
        {
            if (StartTime > EndTime)
                return default;
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Queryable<Entity.MonitorRecord>()
                    .Where(x => x.DataTime > StartTime.AddSeconds(-1) && x.DataTime < EndTime.AddSeconds(1))
                    .Where(x => x.DataCode == DataCode)
                    .OrderBy(x => x.DataTime, OrderByType.Asc)
                    .ToList();
            }
        }

        /// <summary>
        /// 通过 DataCode 查询时间区间内的第一条数据
        /// </summary>
        public Entity.MonitorRecord GetFirstByDataCodeOfTimeRange(string DataCode, DateTime StartTime, DateTime EndTime)
        {
            if (StartTime > EndTime)
                return default;
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Queryable<Entity.MonitorRecord>()
                    .Where(x => x.DataTime > StartTime.AddSeconds(-1) && x.DataTime < EndTime.AddSeconds(1))
                    .Where(x => x.DataCode == DataCode)
                    .OrderBy(x => x.DataTime, OrderByType.Asc)
                    .First();
            }
        }

        /// <summary>
        /// 通过 DataCode 查询时间区间内的最后一条数据
        /// </summary>
        public Entity.MonitorRecord GetLastByDataCodeOfTimeRange(string DataCode, DateTime StartTime, DateTime EndTime)
        {
            if (StartTime > EndTime)
                return default;
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Queryable<Entity.MonitorRecord>()
                    .Where(x => x.DataTime > StartTime.AddSeconds(-1) && x.DataTime < EndTime.AddSeconds(1))
                    .Where(x => x.DataCode == DataCode)
                    .OrderBy(x => x.DataTime, OrderByType.Desc)
                    .First();
            }
        }

        /// <summary>
        /// 通过 DataCode 查询时间区间内受限的数据
        /// </summary>
        public List<Entity.MonitorRecord> GetLimitByDataCodeOfTimeRange(string DataCode, DateTime StartTime, DateTime EndTime, int Limit)
        {
            if (StartTime > EndTime)
                return default;
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                var list = db.Queryable<Entity.MonitorRecord>()
                    .Where(x => x.DataTime > StartTime.AddSeconds(-1) && x.DataTime < EndTime.AddSeconds(1))
                    .Where(x => x.DataCode == DataCode)
                    .OrderBy(x => x.DataTime, OrderByType.Asc)
                    .ToList();
                return list?.ToLimitList(Limit);
            }
        }

        #endregion

        #region 日期区间

        /// <summary>
        /// 通过 DataCode 查询日期区间内的数据
        /// </summary>
        public List<Entity.MonitorRecord> GetByDataCodeOfDayRange(string DataCode, DateTime StartDay, DateTime EndDay)
        {
            if (StartDay.Date > EndDay.Date)
                return default;
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Queryable<Entity.MonitorRecord>()
                    .Where(x => x.DataTime >= StartDay.Date && x.DataTime < EndDay.AddDays(1).Date)
                    .Where(x => x.DataCode == DataCode)
                    .OrderBy(x => x.DataTime, OrderByType.Asc)
                    .ToList();
            }
        }

        /// <summary>
        /// 通过 DataCode 查询日期区间内的第一条数据
        /// </summary>
        public Entity.MonitorRecord GetFirstByDataCodeOfDayRange(string DataCode, DateTime StartDay, DateTime EndDay)
        {
            if (StartDay.Date > EndDay.Date)
                return default;
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Queryable<Entity.MonitorRecord>()
                    .Where(x => x.DataTime >= StartDay.Date && x.DataTime < EndDay.AddDays(1).Date)
                    .Where(x => x.DataCode == DataCode)
                    .OrderBy(x => x.DataTime, OrderByType.Asc)
                    .First();
            }
        }

        /// <summary>
        /// 通过 DataCode 查询日期区间内的最后一条数据
        /// </summary>
        public Entity.MonitorRecord GetLastByDataCodeOfDayRange(string DataCode, DateTime StartDay, DateTime EndDay)
        {
            if (StartDay.Date > EndDay.Date)
                return default;
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Queryable<Entity.MonitorRecord>()
                    .Where(x => x.DataTime >= StartDay.Date && x.DataTime < EndDay.AddDays(1).Date)
                    .Where(x => x.DataCode == DataCode)
                    .OrderBy(x => x.DataTime, OrderByType.Desc)
                    .First();
            }
        }

        /// <summary>
        /// 通过 DataCode 查询日期区间内受限的数据
        /// </summary>
        public List<Entity.MonitorRecord> GetLimitByDataCodeOfDayRange(string DataCode, DateTime StartDay, DateTime EndDay, int Limit)
        {
            if (StartDay.Date > EndDay.Date)
                return default;
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                var list = db.Queryable<Entity.MonitorRecord>()
                    .Where(x => x.DataTime >= StartDay.Date && x.DataTime < EndDay.AddDays(1).Date)
                    .Where(x => x.DataCode == DataCode)
                    .OrderBy(x => x.DataTime, OrderByType.Asc)
                    .ToList();
                return list?.ToLimitList(Limit);
            }
        }

        #endregion

        #region 日期

        /// <summary>
        /// 通过 DataCode 获取某日的数据
        /// </summary>
        public List<Entity.MonitorRecord> GetByDataCodeOfDay(string DataCode, DateTime Day)
        {
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Queryable<Entity.MonitorRecord>()
                    .Where(x => x.DataTime >= Day.Date && x.DataTime < Day.AddDays(1).Date)
                    .Where(x => x.DataCode == DataCode)
                    .OrderBy(x => x.DataTime, OrderByType.Asc)
                    .ToList();
            }
        }

        /// <summary>
        /// 通过 DataCode 获取某日的第一条数据
        /// </summary>
        public Entity.MonitorRecord GetFirstByDataCodeOfDay(string DataCode, DateTime Day)
        {
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Queryable<Entity.MonitorRecord>()
                    .Where(x => x.DataTime >= Day.Date && x.DataTime < Day.AddDays(1).Date)
                    .Where(x => x.DataCode == DataCode)
                    .OrderBy(x => x.DataTime, OrderByType.Asc)
                    .First();
            }
        }

        /// <summary>
        /// 通过 DataCode 获取某日的最后一条数据
        /// </summary>
        public Entity.MonitorRecord GetLastByDataCodeOfDay(string DataCode, DateTime Day)
        {
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Queryable<Entity.MonitorRecord>()
                    .Where(x => x.DataTime >= Day.Date && x.DataTime < Day.AddDays(1).Date)
                    .Where(x => x.DataCode == DataCode)
                    .OrderBy(x => x.DataTime, OrderByType.Desc)
                    .First();
            }
        }

        /// <summary>
        /// 通过 DataCode 获取某日受限的数据
        /// </summary>
        public List<Entity.MonitorRecord> GetLimitByDataCodeOfDay(string DataCode, DateTime Day, int Limit)
        {
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                var list = db.Queryable<Entity.MonitorRecord>()
                    .Where(x => x.DataTime >= Day.Date && x.DataTime < Day.AddDays(1).Date)
                    .Where(x => x.DataCode == DataCode)
                    .OrderBy(x => x.DataTime, OrderByType.Asc)
                    .ToList();
                return list?.ToLimitList(Limit);
            }
        }

        #endregion

        #region 时间

        /// <summary>
        /// 通过时间获取
        /// </summary>
        public Entity.MonitorRecord GetByDataCodeOfTime(string DataCode, DateTime Time)
        {
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Queryable<Entity.MonitorRecord>()
                    .Where(x => x.DataTime > Time.AddSeconds(-1) && x.DataTime < Time.AddSeconds(1))
                    .Where(x => x.DataCode == DataCode)
                    .OrderBy(x => x.DataTime, OrderByType.Asc)
                    .First();
            }
        }

        #endregion

        #endregion

        #region InsertOrUpdate

        /// <summary>
        /// 插入
        /// </summary>
        public bool InsertOrUpdate(Entity.MonitorRecord entity)
        {
            if (entity == null)
                return false;

            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Insertable(entity).ExecuteCommand() > 0;

                //var sql = CreateSql();
                //var paras = CreateParameters(entity);
                //return db.Ado.ExecuteCommand(sql, paras) > 0;
            }
        }

        /// <summary>
        /// 批量插入
        /// </summary>
        public bool InsertOrUpdate(List<Entity.MonitorRecord> list)
        {
            if (list == null || list.Count < 1)
                return false;
           
            using (var db = new SqlSugarClient(ConnectionConfig))
            { 
                return db.Insertable(list).ExecuteCommand() > 0;

                //try
                //{
                //    db.Ado.BeginTran();
                //    var sql = CreateSql();
                //    foreach (var item in list)
                //    {
                //        var paras = CreateParameters(item);
                //        var bol = db.Ado.ExecuteCommand(sql, paras) > 0;
                //        if (!bol)
                //        {
                //            db.Ado.RollbackTran();
                //            return false;
                //        }
                //    }
                //    db.Ado.CommitTran();
                //    return true;
                //}
                //catch
                //{
                //    db.Ado.RollbackTran();
                //    throw;
                //}
            }
        }


        //创建sql
        private string CreateSql()
        {
            var sb = new StringBuilder();
            sb.AppendLine("INSERT INTO MonitorRecord");
            sb.AppendLine($" VALUES(@DataCode,@DataTime,@DataValue,@ReceiptTime)");
            sb.AppendLine("ON CONFLICT(DataCode, DataTime, ReceiptTime) DO UPDATE");
            sb.AppendLine("SET DataValue = excluded.DataValue;");
            return sb.ToString();
        }

        //创建参数
        private List<SugarParameter> CreateParameters(Entity.MonitorRecord entity)
        {
            var list = new List<SugarParameter>();
            list.Add(new SugarParameter("@DataCode", entity.DataCode));
            list.Add(new SugarParameter("@DataTime", entity.DataTime));
            list.Add(new SugarParameter("@DataValue", entity.DataValue));
            list.Add(new SugarParameter("@ReceiptTime", entity.ReceiptTime));
            return list;
        }

        #endregion


    }
}