using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SQLite
{
    public abstract class SQLiteDbContext
    {
        

        public List<IDBTable> DbSet = new List<IDBTable>();
        public SQLiteHelper sqliteHelper { get; private set; }
        string ConnectionString
        {
            get
            {
                return string.Format("Data Source={0};Version=3;", DBPath);
            }
        }
        public string DBPath { get; private set; } = @"test.sqlite3";

        public SQLiteDbContext(string dbPath)
        {
            DBPath = dbPath;
            Constructor();
        }
        void Constructor()
        {
            sqliteHelper = new SQLiteHelper();
            sqliteHelper.ConnectionString = ConnectionString;

            var type = GetType();
            foreach (var p in type.GetProperties())
            {
                if (typeof(IDBTable).IsAssignableFrom(p.PropertyType))
                {
                    DbSet.Add(p.GetValue(this) as IDBTable);
                }
            }
            foreach (IDBTable dBTable in DbSet)
            {
                dBTable.Init(sqliteHelper);
            }
        }
        public void SetDBPath(string dbPath)
        {
            DBPath = dbPath;
            sqliteHelper.ConnectionString = ConnectionString;
        }

        void Build()
        {

            string directoryname = System.IO.Path.GetDirectoryName(DBPath);

            if (!string.IsNullOrEmpty(directoryname) && !System.IO.Directory.Exists(directoryname))
                System.IO.Directory.CreateDirectory(directoryname);

            SQLiteConnection.CreateFile(DBPath);

            foreach (IDBTable dBTable in DbSet)
                dBTable.Create();

        }
        void Rebuild()
        {
            if (!System.IO.File.Exists(DBPath))
            {
                Build();
            }
            else
            {
                //把文件删除，重建
                System.IO.File.Delete(DBPath);
                Build();
            }
        }

        void Load()
        {
            foreach(var table in DbSet)
            {
                table.MaxID = LoadID(table.TableName);
            }
        }
        long LoadID(string tablename)
        {
            string cmd = $"SELECT MAX(ID) FROM {tablename}";
            var reponse = sqliteHelper.ExecuteScalar(cmd);
            if (reponse is DBNull)
                return -1;
            else
                return System.Convert.ToInt64(reponse);

        }

        /// <summary>
        /// 给定分区数 检测表是否存在，合法
        /// </summary>
        /// <param name="nbolts"></param>
        /// <returns>false 表不合法，重建; true 正常！</returns>
        public bool Init()
        {
            if (!System.IO.File.Exists(DBPath))
            {
                Build();
                return false;
            }
            //TODO, 表不对删除就好。。。没必要重新创建数据库

            //任意一个表不对，或者不存在，都必须重建
            Dictionary<string, string> ddls = new Dictionary<string, string>();
            foreach (IDBTable tb in DbSet)
            {
                ddls.Add(tb.TableName, tb.DDL);
            }
            bool isVaild = sqliteHelper.IsTableValid(ddls, out Dictionary<string, SQLiteHelper.IsTableValidResult> results);

            if (!isVaild)//不合法
            {
                if (results.Any(kv => kv.Value == SQLiteHelper.IsTableValidResult.FormatErr))
                {
                    //先备份

                    //File.Copy(DBPath, DBPath + $".{DateTime.Now:yyyyMMddHHmmss}.bk");

                    //重建数据库
                    File.Move(DBPath, DBPath + $".{DateTime.Now:yyyyMMddHHmmss}.bk");
                    Build();
                    return false;
                }
                //有表 不对
                foreach (var kv in results)
                {
                    switch (kv.Value)
                    {
                        case SQLiteHelper.IsTableValidResult.NotHere:
                            {
                                //直接创建表
                                sqliteHelper.ExecuteNonQuery(ddls[kv.Key]);
                            }
                            break;
                        //case SQLiteHelper.IsTableValidResult.FormatErr:
                        //    {
                        //        //先删除表，再创建
                        //        sqliteHelper.ExecuteNonQuery($"DROP TABLE {kv.Key}");
                        //        sqliteHelper.ExecuteNonQuery(ddls[kv.Key]);
                        //    }
                        //    break;
                    }
                }
            }


            //检查INDEX
            Dictionary<string, string> indexs = new Dictionary<string, string>();
            foreach (IDBTable tb in DbSet)
            {
                if(!string.IsNullOrEmpty(tb.INDEX))
                    indexs.Add(tb.TableName, tb.INDEX);
            }
            if (indexs.Count() > 0)
            {
                isVaild = sqliteHelper.IsIndexValid(indexs, out results);

                if (!isVaild)//不合法
                {
                    foreach (var kv in results)
                    {
                        switch (kv.Value)
                        {
                            case SQLiteHelper.IsTableValidResult.NotHere:
                                {
                                    //直接创建表
                                    sqliteHelper.ExecuteNonQuery(indexs[kv.Key]);
                                }
                                break;
                            case SQLiteHelper.IsTableValidResult.FormatErr:
                                {
                                    //先删除INDEX，再创建
                                    sqliteHelper.ExecuteNonQuery($"DROP INDEX {kv.Key}_INDEX");
                                    sqliteHelper.ExecuteNonQuery(indexs[kv.Key]);
                                }
                                break;
                        }
                    }
                }
            }
            
            //最后也要加载数据
            Load();
            return isVaild;
        }
        /// <summary>
        /// 当出错，不重建表
        /// </summary>
        /// <returns></returns>
        public bool InitNoBuild()
        {
            if (!System.IO.File.Exists(DBPath))
            {
                Build();
                return false;
            }
            //TODO, 表不对删除就好。。。没必要重新创建数据库

            //任意一个表不对，或者不存在，都必须重建
            Dictionary<string, string> ddls = new Dictionary<string, string>();
            foreach (IDBTable tb in DbSet)
            {
                ddls.Add(tb.TableName, tb.DDL);
            }
            if (sqliteHelper.IsTableValid(ddls))
            {
                Load();
                return true;
            }
            else
            {
                return false;
            }
        }


        public void KeepDBSize(int month)
        {
            if (month < 1)
                month = 1;
            //最少保存上个月与这个月的数据
            DateTime del_time = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
            del_time = del_time.AddMonths(-month);


            List<string> sqls = new List<string>();

            //正常应该为
            //DELETE FROM ScanData WHERE Time < '2020-06-08'
            //
            //但 有INDEX 后 下面会更快
            //DELETE FROM ScanData WHERE ID <= (SELECT ID FROM ScanData WHERE Time < '2020-06-08' ORDER BY ID DESC LIMIT 1);


            //枚举全部TbXXX, 带有TimeAttribute 都删除
            foreach (IDBTable tb in DbSet)
            {
                sqls.Add(tb.GetDeleteSQL(del_time));
            }

            //把sqls里面为空的全部删除
            sqls.RemoveAll(sql => sql == null);
            if (sqls.Count == 0)
                return;

            sqliteHelper.QueryTran(sqls);
            
        }
    }
    
    
}