using System;
using System.Linq;

using HH.WCS.Mobox3.AnGang.AppStart;

using SqlSugar;

using Task = System.Threading.Tasks.Task;

namespace HH.WCS.Mobox3.AnGang.Helpers {
    // SqlSugar 教程：https://www.donet5.com/Home/Doc

    /// <summary>
    /// 数据库帮助类：包括单例模式和创建新连接的方法
    /// </summary>
    public static class DbHelper {
        private static SqlSugarScope _sqlSugar;

        static DbHelper() {
            Init();
        }

        public static void Init() {
            _sqlSugar = new SqlSugarScope(new ConnectionConfig() {
                ConnectionString = Settings.Config.SqlServer,
                DbType = DbType.SqlServer,
                IsAutoCloseConnection = true,
                InitKeyType = InitKeyType.Attribute // 从特性读取主键自增信息
            },
            db => {
                // 监控所有超过1秒的Sql
                db.Aop.OnLogExecuted = (sql, p) => {
                    // 执行时间超过1秒
                    if (db.Ado.SqlExecutionTime.TotalSeconds > 1) {
                        Console.WriteLine(sql + "\r\n" + db.Utilities.SerializeObject(p.ToDictionary(it => it.ParameterName, it => it.Value)));
                        // 代码CS文件名
                        var fileName = db.Ado.SqlStackTrace.FirstFileName;
                        // 代码行数
                        var fileLine = db.Ado.SqlStackTrace.FirstLine;
                        // 方法名
                        var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName;
                    }
                    // 相当于EF的 PrintToMiniProfiler
                };

                // 每次设置数值时都去除前导后导空格
                db.Aop.DataExecuted = (value, entity) => {
                    entity.EntityColumnInfos.ToList().ForEach(a => {
                        var pvalue = entity.GetValue(a.PropertyName);
                        if (pvalue != null && pvalue.GetType() == typeof(String)) {
                            entity.SetValue(a.PropertyName, pvalue.ToString().Trim());
                        }
                    });
                };

                // 配置AOP日志
                //db.Aop.OnLogExecuting = (sql, pars) =>
                //{
                //    Console.WriteLine(sql); // 输出纯SQL语句
                //};

                // 每N小时清理一次缓存
                Task.Run(async () => {
                    while (true) {
                        await Task.Delay(TimeSpan.FromHours(2));
                        db.QueryFilter.Clear();
                    }
                });
            });
        }

        /// <summary>
        /// 获取用于操控数据库的 SqlSugarClient 对象
        /// </summary>
        /// <returns></returns>
        public static SqlSugarScope GetDbClient() {
            return _sqlSugar;
        }

        /// <summary>
        /// 生成用于操控数据库的 SqlSugarClient 对象
        /// </summary>
        /// <remarks>
        /// 如果用 Oracle 数据需要包 Oracle.ManagedDataAccess/21.15.0，环境 netframework 4.62，太新了 4.8 有的服务器安装不上去
        /// </remarks>
        /// <param name="url"></param>
        /// <returns></returns>
        public static SqlSugarClient NewDbClient(string url = "") {
            // 创建数据库对象
            SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() {
                ConnectionString = string.IsNullOrEmpty(url) ? Settings.Config.SqlServer : url,
                DbType = DbType.SqlServer,
                IsAutoCloseConnection = true,
                InitKeyType = InitKeyType.Attribute // 从特性读取主键自增信息
            });

            // 监控所有超过1秒的Sql
            db.Aop.OnLogExecuted = (sql, p) => {
                // 执行时间超过1秒
                if (db.Ado.SqlExecutionTime.TotalSeconds > 1) {
                    Console.WriteLine(sql + "\r\n" + db.Utilities.SerializeObject(p.ToDictionary(it => it.ParameterName, it => it.Value)));
                    // 代码CS文件名
                    var fileName = db.Ado.SqlStackTrace.FirstFileName;
                    // 代码行数
                    var fileLine = db.Ado.SqlStackTrace.FirstLine;
                    // 方法名
                    var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName;
                }
                // 相当于EF的 PrintToMiniProfiler
            };

            // 每次设置数值时都去除前导后导空格
            db.Aop.DataExecuted = (value, entity) => {
                entity.EntityColumnInfos.ToList().ForEach(a => {
                    var pvalue = entity.GetValue(a.PropertyName);
                    if (pvalue != null && pvalue.GetType() == typeof(String)) {
                        entity.SetValue(a.PropertyName, pvalue.ToString().Trim());
                    }
                });
            };

            // 据转换 (ExecuteCommand 才会拦截，查询不行)
            //db.Aop.DataExecuting = (value, entity) => {
            //    //var val=entity.EntityColumnInfo
            //    Console.WriteLine(entity.EntityName);
            //};

            // 用于打印 SqlSugar 解码后的 SQL 语句
            db.Aop.OnLogExecuting = (sql, pars) => {
                Console.WriteLine(sql);
            };

            return db;
        }
    }
}