using FLY.OBJComponents.Server.Model;

using SQLite;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;

namespace FLY.Blowing.DbViewer.Core
{
    public class DbHelper : INotifyPropertyChanged
    {

        public event PropertyChangedEventHandler PropertyChanged;

        public string DbDirPath { get; set; } = @"D:\blowingdata";
        
        public string DbPathAirRing => System.IO.Path.Combine(DbDirPath, "airring.sqlite3");
        public string DbPathWeight => System.IO.Path.Combine(DbDirPath, "weight.sqlite3");
        public string DbPathWinder => System.IO.Path.Combine(DbDirPath, "winder.sqlite3");
        public string DbPathIbc => System.IO.Path.Combine(DbDirPath, "ibc.sqlite3");

        public bool IsAirRingExist { get; private set; }
        public bool IsWeightExist { get; private set; }
        public bool IsWinderExist { get; private set; }
        public bool IsIbcExist { get; private set; }

        public DateTime AirRingDbBeginTime { get; private set; }
        public DateTime AirRingDbEndTime { get; private set; }

        public DateTime WeightDbBeginTime { get; private set; }
        public DateTime WeightDbEndTime { get; private set; }

        public DateTime WinderDbBeginTime { get; private set; }
        public DateTime WinderDbEndTime { get; private set; }

        public DateTime IbcDbBeginTime { get; private set; }
        public DateTime IbcDbEndTime { get; private set; }




        FLY.FeedbackRenZiJia.Server.Model.DbModel dbModelAirRing = null;
        FLY.Weight.Server.Model.DbModel dbModelWeight = null;
        FLY.Winder.Server.Model.DbModel dbModelWinder = null;
        FLY.IBC.Server.Model.DbModel dbModelIbc = null;


        public DbHelper()
        {

        }

        /// <summary>
        /// 出错代码
        /// </summary>
        public string ErrorMsg { get; private set; }

        public bool IsDbErr { get; private set; }
        /// <summary>
        /// 从表获取数据
        /// </summary>
        public bool Init()
        {
            IsAirRingExist = System.IO.File.Exists(DbPathAirRing);
            IsWeightExist = System.IO.File.Exists(DbPathWeight);
            IsWinderExist = System.IO.File.Exists(DbPathWinder);
            IsIbcExist = System.IO.File.Exists(DbPathIbc);


            if (IsAirRingExist)
            {
                if (!Init_airRing())
                {
                    return false;
                }
            }

            if (IsWeightExist)
            {
                if (!Init_weight())
                {
                    return false;
                }
            }

            if (IsWinderExist)
            {
                if (!Init_winder())
                {
                    return false;
                }
            }

            if (IsIbcExist)
            {
                if (!Init_ibc())
                {
                    return false;
                }
            }

            ErrorMsg = "数据库 加载成功";
            IsDbErr = false;
            return true;
        }

        bool Init_airRing()
        {
            try
            {
                dbModelAirRing = new FeedbackRenZiJia.Server.Model.DbModel(DbPathAirRing);
                if (!dbModelAirRing.InitNoBuild())
                {
                    ErrorMsg = $"{DbPathAirRing} 数据库格式异常";
                    IsDbErr = true;
                    return false;
                }
                string cmd = $"SELECT Time FROM {dbModelAirRing.TbThickHeat.TableName} ORDER BY ID LIMIT 1";
                var ret = dbModelAirRing.sqliteHelper.ExecuteScalar(cmd);
                AirRingDbBeginTime = System.Convert.ToDateTime(ret);

                cmd = $"SELECT Time FROM {dbModelAirRing.TbThickHeat.TableName} ORDER BY ID DESC LIMIT 1";
                ret = dbModelAirRing.sqliteHelper.ExecuteScalar(cmd);
                AirRingDbEndTime = System.Convert.ToDateTime(ret);
            }
            catch (Exception e)
            {
                ErrorMsg = e.Message;
                IsDbErr = true;
                return false;
            }
            return true;
        }
        bool Init_weight() 
        {
            try
            {
                dbModelWeight = new Weight.Server.Model.DbModel(DbPathWeight);
                if (!dbModelWeight.InitNoBuild())
                {
                    ErrorMsg = $"{DbPathWeight} 数据库格式异常";
                    IsDbErr = true;
                    return false;
                }

                string cmd = $"SELECT Time FROM {dbModelWeight.TbFlow.TableName} ORDER BY ID LIMIT 1";
                var ret = dbModelWeight.sqliteHelper.ExecuteScalar(cmd);
                WeightDbBeginTime = System.Convert.ToDateTime(ret);

                cmd = $"SELECT Time FROM {dbModelWeight.TbFlow.TableName} ORDER BY ID DESC LIMIT 1";
                ret = dbModelWeight.sqliteHelper.ExecuteScalar(cmd);
                WeightDbEndTime = System.Convert.ToDateTime(ret);
            }
            catch (Exception e)
            {
                ErrorMsg = e.Message;
                IsDbErr = true;
                return false;
            }
            return true;
        }
        bool Init_winder() 
        {
            try
            {
                dbModelWinder = new FLY.Winder.Server.Model.DbModel(DbPathWinder);
                if (!dbModelWinder.InitNoBuild())
                {
                    ErrorMsg = $"{DbPathWinder} 数据库格式异常";
                    IsDbErr = true;
                    return false;
                }

                string cmd = $"SELECT Time FROM {dbModelWinder.TbWinderInfo.TableName} ORDER BY ID LIMIT 1";
                var ret = dbModelWinder.sqliteHelper.ExecuteScalar(cmd);
                WinderDbBeginTime = System.Convert.ToDateTime(ret);

                cmd = $"SELECT Time FROM {dbModelWinder.TbWinderInfo.TableName} ORDER BY ID DESC LIMIT 1";
                ret = dbModelWinder.sqliteHelper.ExecuteScalar(cmd);
                WinderDbEndTime = System.Convert.ToDateTime(ret);
            }
            catch (Exception e)
            {
                ErrorMsg = e.Message;
                IsDbErr = true;
                return false;
            }
            return true;
        }
        bool Init_ibc()
        {
            try
            {
                dbModelIbc = new FLY.IBC.Server.Model.DbModel(DbPathIbc);
                if (!dbModelIbc.InitNoBuild())
                {
                    ErrorMsg = $"{DbPathIbc} 数据库格式异常";
                    IsDbErr = true;
                    return false;
                }

                string cmd = $"SELECT Time FROM {dbModelIbc.TbWidth.TableName} ORDER BY ID LIMIT 1";
                var ret = dbModelIbc.sqliteHelper.ExecuteScalar(cmd);
                IbcDbBeginTime = System.Convert.ToDateTime(ret);

                cmd = $"SELECT Time FROM {dbModelIbc.TbWidth.TableName} ORDER BY ID DESC LIMIT 1";
                ret = dbModelIbc.sqliteHelper.ExecuteScalar(cmd);
                IbcDbEndTime = System.Convert.ToDateTime(ret);
            }
            catch (Exception e)
            {
                ErrorMsg = e.Message;
                IsDbErr = true;
                return false;
            }
            return true;
        }

        public string GetPackMsg { get; private set; }
        
        public DbProfilePack GetPack(DateTime startTime, DateTime endTime)
        {
            DbProfilePack profilePack = new DbProfilePack();
            
            profilePack = GetProfilePack_AirRing(startTime, endTime, profilePack);

            profilePack = GetProfilePack_Ibc(startTime, endTime, profilePack);

            profilePack = GetProfilePack_Winder(startTime, endTime, profilePack);

            profilePack = GetProfilePack_Weight(startTime, endTime, profilePack);
            return profilePack;
        }
        DbProfilePack GetProfilePack_AirRing(DateTime startTime, DateTime endTime, DbProfilePack profilePack)
        {
            if (dbModelAirRing == null)
                return profilePack;
            GetPackMsg = "从风环数据库加载数据";

            string startTime_str = startTime.ToStringOfSQLiteFieldType();
            string endTime_str = endTime.ToStringOfSQLiteFieldType();

            string condition =
                $"WHERE Time>={startTime_str}" +
                $" AND Time<={endTime_str}";

            var db_thickHeats = dbModelAirRing.TbThickHeat.Find(condition);
            var lc_thickHeats = FLY.FeedbackRenZiJia.Server.Model.Lc_AutoMapperProfile.Mapper.Map<
                List<FLY.FeedbackRenZiJia.Server.Model.Db_ThickHeat>, List<FLY.FeedbackRenZiJia.Server.Model.Lc_ThickHeat>>(db_thickHeats);
            profilePack.ThickHeats = lc_thickHeats;

            return profilePack;
        }
        DbProfilePack GetProfilePack_Ibc(DateTime startTime, DateTime endTime, DbProfilePack profilePack) 
        {
            if (dbModelIbc == null)
                return profilePack;
            GetPackMsg = "从IBC数据库加载数据";

            string startTime_str = startTime.ToStringOfSQLiteFieldType();
            string endTime_str = endTime.ToStringOfSQLiteFieldType();

            string condition =
                $"WHERE Time>={startTime_str}" +
                $" AND Time<={endTime_str}";

            var db_widths = dbModelIbc.TbWidth.Find(condition);
            profilePack.Widths = db_widths;

            return profilePack;
        }
        DbProfilePack GetProfilePack_Winder(DateTime startTime, DateTime endTime, DbProfilePack profilePack)
        {
            if (dbModelWinder == null)
                return profilePack;
            GetPackMsg = "从收卷数据库加载数据";

            string startTime_str = startTime.ToStringOfSQLiteFieldType();
            string endTime_str = endTime.ToStringOfSQLiteFieldType();

            string condition =
                $"WHERE Time>={startTime_str}" +
                $" AND Time<={endTime_str}";

            var db_winderInfos = dbModelWinder.TbWinderInfo.Find(condition);
            profilePack.WinderInfos = db_winderInfos;

            return profilePack;
        }
        DbProfilePack GetProfilePack_Weight(DateTime startTime, DateTime endTime, DbProfilePack profilePack)
        {
            if (dbModelWeight == null)
                return profilePack;
            GetPackMsg = "从称重数据库加载流量数据";

            string startTime_str = startTime.ToStringOfSQLiteFieldType();
            string endTime_str = endTime.ToStringOfSQLiteFieldType();

            string condition =
                $"WHERE Time>={startTime_str}" +
                $" AND Time<={endTime_str}";

            var db_flows = dbModelWeight.TbFlow.Find(condition);
            var lc_flows = FLY.Weight.Server.Model.Lc_AutoMapperProfile.Mapper.Map<
                List<FLY.Weight.Server.Model.Db_Flow>, List<FLY.Weight.Server.Model.Lc_Flow>>(db_flows);
            profilePack.WeightFlows = lc_flows;

            GetPackMsg = "从称重数据库加载混料数据";

            var db_mixs = dbModelWeight.TbMix.Find(condition);
            var lc_mixs = FLY.Weight.Server.Model.Lc_AutoMapperProfile.Mapper.Map<
                List<FLY.Weight.Server.Model.Db_Mix>, List<FLY.Weight.Server.Model.Lc_Mix>>(db_mixs);
            profilePack.WeightMixs = lc_mixs;

            return profilePack;
        }
    }


    #region 链接表的关系
    /// <summary>
    /// 包装 DB_Profile， 组织 DB_Profile， DB_ScanData....
    /// </summary>
    public class DbProfilePack
    {
        public List<FLY.FeedbackRenZiJia.Server.Model.Lc_ThickHeat> ThickHeats;
        public List<FLY.IBC.Server.Model.Db_Width> Widths;
        public List<FLY.Winder.Server.Model.Db_WinderInfo> WinderInfos;

        public List<FLY.Weight.Server.Model.Lc_Flow> WeightFlows;
        public List<FLY.Weight.Server.Model.Lc_Mix> WeightMixs;

        public int GetTotalRow() {
            int sum = 0;
            sum += ThickHeats != null ? ThickHeats.Count() : 0;
            sum += Widths != null ? Widths.Count() : 0;
            sum += WinderInfos != null ? WinderInfos.Count() : 0;
            sum += WeightFlows != null ? WeightFlows.Count() : 0;
            sum += WeightMixs != null ? WeightMixs.Count() : 0;
            return sum;
        }
        public List<string> GetMsgs() {
            List<string> msgs = new List<string>();
            if (ThickHeats != null && ThickHeats.Count() > 0)
            {
                msgs.Add($"风环记录 {ThickHeats.Count()}条");
            }

            if (WeightFlows != null && WeightFlows.Count() > 0)
            {
                msgs.Add($"称重记录 {WeightFlows.Count()}条");
            }

            if (WinderInfos != null && WinderInfos.Count() > 0)
            {
                msgs.Add($"收卷记录 {WinderInfos.Count()}条");
            }

            if (Widths != null && Widths.Count() > 0)
            {
                msgs.Add($"IBC记录 {Widths.Count()}条");
            }
            return msgs;
        }
        public void GetDateRange(out DateTime beginTime, out DateTime endTime) {

            beginTime = DateTime.MinValue;
            endTime = DateTime.MinValue;

            if (ThickHeats != null && ThickHeats.Count() > 0)
            {
                var b = ThickHeats.First().Time;
                var e = ThickHeats.Last().Time;

                if (beginTime == DateTime.MinValue)
                {
                    beginTime = b;
                    endTime = e;
                }
                else
                {
                    if (b < beginTime)
                        beginTime = b;

                    if (e > endTime)
                        endTime = e;
                }
            }

            if (WeightFlows != null && WeightFlows.Count() > 0)
            {
                var b = WeightFlows.First().Time;
                var e = WeightFlows.Last().Time;

                if (beginTime == DateTime.MinValue)
                {
                    beginTime = b;
                    endTime = e;
                }
                else
                {
                    if (b < beginTime)
                        beginTime = b;

                    if (e > endTime)
                        endTime = e;
                }
            }

            if (WinderInfos != null && WinderInfos.Count() > 0)
            {
                var b = WinderInfos.First().Time;
                var e = WinderInfos.Last().Time;

                if (beginTime == DateTime.MinValue)
                {
                    beginTime = b;
                    endTime = e;
                }
                else
                {
                    if (b < beginTime)
                        beginTime = b;

                    if (e > endTime)
                        endTime = e;
                }
            }

            if (Widths != null && Widths.Count() > 0)
            {
                var b = Widths.First().Time;
                var e = Widths.Last().Time;

                if (beginTime == DateTime.MinValue)
                {
                    beginTime = b;
                    endTime = e;
                }
                else
                {
                    if (b < beginTime)
                        beginTime = b;

                    if (e > endTime)
                        endTime = e;
                }
            }
        }
    }
    #endregion



}