using Dapper;
using Microsoft.Data.Sqlite;
using MySql.Data.MySqlClient;
using Npgsql;
using System.Data;
using Long.Core.Query;
using Long.Core.Settings;
using Microsoft.Extensions.Configuration;
using System.Data.SqlClient;

namespace Long.Dapper
{
    /// <summary>
    /// 静态配置文件
    /// </summary>
    public static class DatabaseConfig
    {
        public static string AppSettingsFileName = "appsettings.json";
    }

    /// <summary>
    /// 基础数据库连接类
    /// </summary>
    public class Database<TEntity> : IDatabase<TEntity>
    {
        private string _tableName = "";
        private IList<string> _fields;

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string ConnectionString { get; set; }

        /// <summary>
        /// 数据库配置关键字
        /// </summary>
        public string DbKey { get; set; }

        /// <summary>
        /// 数据库类型
        /// </summary>
        public string DbType { get; set; }

        /// <summary>
        /// 基础数据库连接类
        /// </summary>
        /// <param name="dbKey">数据库配置关键字</param>
        /// <param name="dbType">数据库类型</param>
        public Database(string dbKey = "Default", string dbType = "MySql")
        {
            SetConnectionString(dbKey);

            DbType = dbType;

            // 实例化字段
            _fields = new List<string>();

            // 获取表名
            var type = typeof(TEntity);
            _tableName = type.Name;

            // 获取字段
            var props = type.GetProperties();
            for (var i = 0; i < props.Length; i++)
            {
                _fields.Add(props[i].Name);
            }
        }

        /// <summary>
        /// 设置 appsettings.json 中的数据库连接字符串
        /// </summary>
        /// <param name="dbKey">数据库配置关键字</param>
        public void SetConnectionString(string dbKey)
        {
            IConfigurationBuilder configurationBuilder = new ConfigurationBuilder();
            configurationBuilder.AddJsonFile(DatabaseConfig.AppSettingsFileName, optional: true, reloadOnChange: true);

            IConfiguration configuration = configurationBuilder.Build();

            string res = configuration.GetSection($"ConnectionStrings:{dbKey}").Value ?? "";
            if (!String.IsNullOrWhiteSpace(res))
            {
                ConnectionString = res;
                DbKey = dbKey;
            }
        }

        /// <summary>
        /// 获取数据库连接
        /// </summary>
        /// <returns></returns>
        /// <exception cref="ApplicationException"></exception>
        public IDbConnection GetDbConnection()
        {
            if (string.IsNullOrWhiteSpace(ConnectionString))
            {
                throw new ApplicationException("数据库连接字符串异常！");
            }

            switch (DbType.ToLower())
            {
                case "npgsql":
                    return new NpgsqlConnection(ConnectionString);
                case "sqlserver":
                    return new SqlConnection(ConnectionString);
                case "sqlite":
                    return new SqliteConnection(ConnectionString);
                case "mysql":
                default:
                    return new MySqlConnection(ConnectionString);
            }
        }

        #region 查询

        /// <summary>
        /// 查询列表
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sqlString">SQL 查询字符串</param>
        /// <param name="param">SQL 参数对象</param>
        /// <returns>泛型列表</returns>
        public IEnumerable<T> Query<T>(string sqlString, object? param = null)
        {
            IDbConnection conn = GetDbConnection();
            return conn.Query<T>(sqlString, param);
        }

        /// <summary>
        /// 查询列表
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sqlString">SQL 查询字符串</param>
        /// <param name="param">SQL 参数对象</param>
        /// <returns>泛型列表</returns>
        public async Task<IEnumerable<T>> QueryAsync<T>(string sqlString, object? param = null)
        {
            IDbConnection conn = GetDbConnection();
            return await conn.QueryAsync<T>(sqlString, param);
        }

        /// <summary>
        /// 查询第一条数据
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sqlString">SQL 查询字符串</param>
        /// <param name="param">SQL 参数对象</param>
        /// <returns>泛型对象</returns>
        public T QueryFirst<T>(string sqlString, object? param = null)
        {
            IDbConnection conn = GetDbConnection();
            return conn.QueryFirstOrDefault<T>(sqlString, param);
        }

        /// <summary>
        /// 查询第一条数据
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sqlString">SQL 查询字符串</param>
        /// <param name="param">SQL 参数对象</param>
        /// <returns>泛型对象</returns>
        public async Task<T> QueryFirstAsync<T>(string sqlString, object? param = null)
        {
            IDbConnection conn = GetDbConnection();
            return await conn.QueryFirstOrDefaultAsync<T>(sqlString, param);
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <returns></returns>
        public IEnumerable<TEntity> Get()
        {
            return Query<TEntity>(GetSql(new QueryParamenter()));
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <returns></returns>
        public async Task<IEnumerable<TEntity>> GetAsync()
        {
            return await QueryAsync<TEntity>(GetSql(new QueryParamenter()));
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public IEnumerable<TEntity> Get(string sql)
        {
            return Query<TEntity>(sql);
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public async Task<IEnumerable<TEntity>> GetAsync(string sql)
        {
            return await QueryAsync<TEntity>(sql);
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public IEnumerable<TEntity> Get(string sql, object param)
        {
            return Query<TEntity>(sql, param);
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public async Task<IEnumerable<TEntity>> GetAsync(string sql, object param)
        {
            return await QueryAsync<TEntity>(sql, param);
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public async Task<IEnumerable<T>> GetAsync<T>(string sql, object param)
        {
            return await QueryAsync<T>(sql, param);
        }

        /// <summary>
        /// 通过id获取数据
        /// </summary>
        /// <param name="id">内码</param>
        /// <returns></returns>
        public IEnumerable<TEntity> Get(long id)
        {
            return Query<TEntity>(GetByIdSql(), new { Id = id });
        }

        /// <summary>
        /// 通过id获取数据
        /// </summary>
        /// <param name="id">内码</param>
        /// <returns></returns>
        public async Task<IEnumerable<TEntity>> GetAsync(long id)
        {
            return await QueryAsync<TEntity>(GetByIdSql(), new { Id = id });
        }

        /// <summary>
        /// 通过id获取数据
        /// </summary>
        /// <param name="id">内码</param>
        /// <returns></returns>
        public async Task<IEnumerable<T>> GetAsync<T>(long id)
        {
            return await QueryAsync<T>(GetByIdSql<T>(), new { Id = id });
        }

        /// <summary>
        /// 通过一堆id获取数据
        /// </summary>
        /// <param name="ids">内码列表</param>
        /// <returns></returns>
        public IEnumerable<TEntity> Get(IEnumerable<long> ids)
        {
            return Query<TEntity>(GetByIdsSql(), new { Ids = ids });
        }

        /// <summary>
        /// 通过一堆id获取数据
        /// </summary>
        /// <param name="ids">内码列表</param>
        /// <returns></returns>
        public async Task<IEnumerable<TEntity>> GetAsync(IEnumerable<long> ids)
        {
            return await QueryAsync<TEntity>(GetByIdsSql(), new { Ids = ids });
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="param">查询参数</param>
        /// <returns></returns>
        public IEnumerable<TEntity> Get(QueryParamenter param)
        {
            return Query<TEntity>(GetSql(param));
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="param">查询参数</param>
        /// <returns></returns>
        public async Task<IEnumerable<TEntity>> GetAsync(QueryParamenter param)
        {
            return await QueryAsync<TEntity>(GetSql(param));
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="param">查询参数</param>
        /// <returns></returns>
        public async Task<IEnumerable<T>> GetAsync<T>(QueryParamenter param)
        {
            return await QueryAsync<T>(GetSql(param));
        }

        /// <summary>
        /// 通过id获取一条数据
        /// </summary>
        /// <returns></returns>
        public TEntity GetFirst()
        {
            return QueryFirst<TEntity>(GetSql());
        }

        /// <summary>
        /// 通过id获取一条数据
        /// </summary>
        /// <returns></returns>
        public async Task<TEntity> GetFirstAsync()
        {
            return await QueryFirstAsync<TEntity>(GetSql());
        }

        /// <summary>
        /// 通过id获取一条数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public TEntity GetFirst(long id)
        {
            return QueryFirst<TEntity>(GetByIdSql(), new { Id = id });
        }

        /// <summary>
        /// 通过id获取一条数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<TEntity> GetFirstAsync(long id)
        {
            return await QueryFirstAsync<TEntity>(GetByIdSql(), new { Id = id });
        }

        /// <summary>
        /// 获取一条数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public TEntity GetFirst(string sql)
        {
            return QueryFirst<TEntity>(sql);
        }

        /// <summary>
        /// 获取一条数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<TEntity> GetFirstAsync(string sql)
        {
            return await QueryFirstAsync<TEntity>(sql);
        }

        /// <summary>
        /// 获取一条数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public TEntity GetFirst(string sql, object param)
        {
            return QueryFirst<TEntity>(sql, param);
        }

        /// <summary>
        /// 获取一条数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public async Task<TEntity> GetFirstAsync(string sql, object param)
        {
            return await QueryFirstAsync<TEntity>(sql, param);
        }

        /// <summary>
        /// 获取一条数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public async Task<T> GetFirstAsync<T>(string sql, object param)
        {
            return await QueryFirstAsync<T>(sql, param);
        }

        /// <summary>
        /// 获取一条数据
        /// </summary>
        /// <param name="queryParamenter"></param>
        /// <returns></returns>
        public TEntity GetFirst(QueryParamenter queryParamenter, object? param = null)
        {
            return QueryFirst<TEntity>(GetSql(queryParamenter), param);
        }

        /// <summary>
        /// 获取一条数据
        /// </summary>
        /// <param name="queryParamenter"></param>
        /// <returns></returns>
        public async Task<TEntity> GetFirstAsync(QueryParamenter queryParamenter, object? param = null)
        {
            return await QueryFirstAsync<TEntity>(GetSql(queryParamenter), param);
        }

        /// <summary>
        /// 获取一条数据
        /// </summary>
        /// <param name="queryParamenter"></param>
        /// <returns></returns>
        public TEntity GetFirst(QueryParamenter queryParamenter)
        {
            return QueryFirst<TEntity>(GetSql(queryParamenter));
        }

        /// <summary>
        /// 获取一条数据
        /// </summary>
        /// <param name="queryParamenter"></param>
        /// <returns></returns>
        public async Task<TEntity> GetFirstAsync(QueryParamenter queryParamenter)
        {
            return await QueryFirstAsync<TEntity>(GetSql(queryParamenter));
        }

        /// <summary>
        /// 获取一条数据
        /// </summary>
        /// <param name="queryParamenter"></param>
        /// <returns></returns>
        public T GetFirst<T>(QueryParamenter queryParamenter)
        {
            return QueryFirst<T>(GetSql(queryParamenter));
        }

        /// <summary>
        /// 获取一条数据
        /// </summary>
        /// <param name="queryParamenter"></param>
        /// <returns></returns>
        public async Task<T> GetFirstAsync<T>(QueryParamenter queryParamenter)
        {
            return await QueryFirstAsync<T>(GetSql(queryParamenter));
        }

        #endregion

        #region 统计

        /// <summary>
        /// 统计表记录数
        /// </summary>
        /// <returns>记录数</returns>
        public int Count()
        {
            string sql = $"SELECT COUNT(1) FROM {_tableName};";
            return QueryFirst<int>(sql);
        }

        /// <summary>
        /// 统计表记录数
        /// </summary>
        /// <returns>记录数</returns>
        public async Task<int> CountAsync()
        {
            string sql = $"SELECT COUNT(1) FROM {_tableName};";
            return await QueryFirstAsync<int>(sql);
        }

        /// <summary>
        /// 统计表记录数
        /// </summary>
        /// <returns>记录数</returns>
        public async Task<int> CountAsync(QueryParamenter queryParamenter)
        {
            return await QueryFirstAsync<int>(GetCountSql(queryParamenter));
        }

        /// <summary>
        /// 统计表记录数
        /// </summary>
        /// <param name="id">内码</param>
        /// <returns>记录数</returns>
        public int Count(long id)
        {
            string sql = $"SELECT COUNT(1) FROM {_tableName} WHERE Id=@Id;";
            return QueryFirst<int>(sql, new { Id = id });
        }

        /// <summary>
        /// 统计表记录数
        /// </summary>
        /// <param name="id">内码</param>
        /// <returns>记录数</returns>
        public async Task<int> CountAsync(long id)
        {
            string sql = $"SELECT COUNT(1) FROM {_tableName} WHERE Id=@Id;";
            return await QueryFirstAsync<int>(sql, new { Id = id });
        }

        /// <summary>
        /// 统计表记录数
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>记录数</returns>
        public int Count(string tableName)
        {
            string sql = $"SELECT COUNT(1) FROM {tableName};";
            return QueryFirst<int>(sql);
        }

        /// <summary>
        /// 统计表记录数
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>记录数</returns>
        public async Task<int> CountAsync(string tableName)
        {
            string sql = $"SELECT COUNT(1) FROM {tableName};";
            return await QueryFirstAsync<int>(sql);
        }

        /// <summary>
        /// 通过条件统计表记录数
        /// </summary>
        /// <param name="where">筛选条件，不包含 WHERE 关键字</param>
        /// <returns>记录数</returns>
        public int CountByWhere(string where)
        {
            string sql = $"SELECT COUNT(1) FROM {_tableName} WHERE {where};";
            return QueryFirst<int>(sql);
        }

        /// <summary>
        /// 通过条件统计表记录数
        /// </summary>
        /// <param name="where">筛选条件，不包含 WHERE 关键字</param>
        /// <returns>记录数</returns>
        public async Task<int> CountByWhereAsync(string where)
        {
            string sql = $"SELECT COUNT(1) FROM {_tableName} WHERE {where};";
            return await QueryFirstAsync<int>(sql);
        }

        /// <summary>
        /// 通过条件统计表记录数
        /// </summary>
        /// <param name="id">内码</param>
        /// <param name="where">筛选条件，不包含 WHERE 关键字</param>
        /// <returns>记录数</returns>
        public int CountByWhere(long id, string where)
        {
            string sql = $"SELECT COUNT(1) FROM {_tableName} WHERE Id=@Id AND {where};";
            return QueryFirst<int>(sql, new { Id = id });
        }

        /// <summary>
        /// 通过条件统计表记录数
        /// </summary>
        /// <param name="id">内码</param>
        /// <param name="where">筛选条件，不包含 WHERE 关键字</param>
        /// <returns>记录数</returns>
        public async Task<int> CountByWhereAsync(long id, string where)
        {
            string sql = $"SELECT COUNT(1) FROM {_tableName} WHERE Id=@Id AND {where};";
            return await QueryFirstAsync<int>(sql, new { Id = id });
        }

        /// <summary>
        /// 通过条件统计表记录数
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="where">筛选条件，不包含 WHERE 关键字</param>
        /// <returns>记录数</returns>
        public int CountByWhere(string tableName, string where)
        {
            string sql = $"SELECT COUNT(1) FROM {tableName} WHERE {where};";
            return QueryFirst<int>(sql);
        }

        /// <summary>
        /// 通过条件统计表记录数
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="where">筛选条件，不包含 WHERE 关键字</param>
        /// <returns>记录数</returns>
        public async Task<int> CountByWhereAsync(string tableName, string where)
        {
            string sql = $"SELECT COUNT(1) FROM {tableName} WHERE {where};";
            return await QueryFirstAsync<int>(sql);
        }

        #endregion

        #region 插入

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="insert">插入对象</param>
        /// <returns>插入记录数</returns>
        public int Insert(string sql, object? insert)
        {
            var result = Execute(sql, insert);
            if (result == 0)
            {
                throw new ApplicationException("插入记录失败！");
            }
            return result;
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="insert">插入对象</param>
        /// <returns></returns>
        public async Task<int> InsertAsync(string sql, object? insert)
        {
            var result = await ExecuteAsync(sql, insert);
            if (result == 0)
            {
                throw new ApplicationException("插入记录失败！");
            }
            return result;
        }

        /// <summary>
        /// 插入数据，自动填入Id
        /// </summary>
        /// <param name="insert">插入对象</param>
        /// <returns>记录内码</returns>
        public long Insert(TEntity insert)
        {
            var id = SetId(insert);
            var res = Insert(InsertSql(), insert);
            return res > 0 ? id : res;
        }

        /// <summary>
        /// 插入数据，自动填入Id
        /// </summary>
        /// <param name="insert">插入对象</param>
        /// <returns>记录内码</returns>
        public async Task<long> InsertAsync(TEntity insert)
        {
            var id = SetId(insert);
            var res = await InsertAsync(InsertSql(), insert);
            return res > 0 ? id : res;
        }

        /// <summary>
        /// 插入一堆数据，自动填入Id
        /// </summary>
        /// <param name="inserts">插入对象列表</param>
        /// <returns>记录内码列表</returns>
        public IEnumerable<long> Insert(IEnumerable<TEntity> inserts)
        {
            var ids = new List<long>();
            foreach (var item in inserts)
            {
                var id = SetId(item);
                ids.Add(id);
            }
            Insert(InsertSql(), inserts);
            return ids;
        }

        /// <summary>
        /// 插入一堆数据，自动填入Id
        /// </summary>
        /// <param name="inserts">插入对象列表</param>
        /// <returns>记录内码列表</returns>
        public async Task<IEnumerable<long>> InsertAsync(IEnumerable<TEntity> inserts)
        {
            var ids = new List<long>();
            foreach (var item in inserts)
            {
                var id = await SetIdAsync(item);
                ids.Add(id);
            }
            await InsertAsync(InsertSql(), inserts);
            return ids;
        }

        #endregion

        #region 更新

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="update">更新对象</param>
        /// <returns></returns>
        public int Update(string sql, object? update)
        {
            return Execute(sql, update);
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="update">更新对象</param>
        /// <returns></returns>
        public async Task<int> UpdateAsync(string sql, object? update)
        {
            return await ExecuteAsync(sql, update);
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="insert">更新对象</param>
        /// <returns></returns>
        public int Update(TEntity update)
        {
            return Update(UpdateSql(), update);
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="insert">更新对象</param>
        /// <returns></returns>
        public async Task<int> UpdateAsync(TEntity update)
        {
            return await UpdateAsync(UpdateSql(), update);
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="update">更新对象</param>
        /// <param name="fields">更新字段</param>
        /// <returns></returns>
        public int Update(TEntity update, string[] fields)
        {
            return Update(UpdateSql(fields), update);
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="update">更新对象</param>
        /// <param name="fields">更新字段</param>
        /// <returns></returns>
        public async Task<int> UpdateAsync(TEntity update, string[] fields)
        {
            return await UpdateAsync(UpdateSql(fields), update);
        }

        /// <summary>
        /// 更新一堆数据
        /// </summary>
        /// <param name="inserts">更新对象列表</param>
        /// <returns></returns>
        public int Update(IEnumerable<TEntity> updates)
        {
            return Update(UpdatesSql(), updates);
        }

        /// <summary>
        /// 更新一堆数据
        /// </summary>
        /// <param name="inserts">更新对象列表</param>
        /// <returns></returns>
        public async Task<int> UpdateAsync(IEnumerable<TEntity> updates)
        {
            return await UpdateAsync(UpdatesSql(), updates);
        }

        /// <summary>
        /// 更新一堆数据
        /// </summary>
        /// <param name="inserts">更新对象列表</param>
        /// <param name="fields">更新字段</param>
        /// <returns></returns>
        public int Update(IEnumerable<TEntity> updates, string[] fields)
        {
            return Update(UpdatesSql(fields), updates);
        }

        /// <summary>
        /// 更新一堆数据
        /// </summary>
        /// <param name="inserts">更新对象列表</param>
        /// <param name="fields">更新字段</param>
        /// <returns></returns>
        public async Task<int> UpdateAsync(IEnumerable<TEntity> updates, string[] fields)
        {
            return await UpdateAsync(UpdatesSql(fields), updates);
        }

        #endregion

        #region 删除

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id">内码</param>
        /// <returns></returns>
        public int Delete(long id)
        {
            return Execute(DeleteSql(), new { Id = id });
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id">内码</param>
        /// <returns></returns>
        public async Task<int> DeleteAsync(long id)
        {
            return await ExecuteAsync(DeleteSql(), new { Id = id });
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="ids">一堆内码</param>
        /// <returns></returns>
        public int Delete(IEnumerable<long> ids)
        {
            return Execute(DeletesSql(), new { Ids = ids });
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="ids">一堆内码</param>
        /// <returns></returns>
        public async Task<int> DeleteAsync(IEnumerable<long> ids)
        {
            return await ExecuteAsync(DeletesSql(), new { Ids = ids });
        }

        #endregion

        #region 执行

        /// <summary>
        /// 执行 SQL
        /// </summary>
        /// <param name="sqlString">sql字符串</param>
        /// <returns></returns>
        public int Execute(string sqlString)
        {
            using (IDbConnection conn = GetDbConnection())
            {
                return conn.Execute(sqlString);
            }
        }

        /// <summary>
        /// 执行 SQL
        /// </summary>
        /// <param name="sqlString">sql字符串</param>
        /// <returns></returns>
        public async Task<int> ExecuteAsync(string sqlString)
        {
            using (IDbConnection conn = GetDbConnection())
            {
                return await conn.ExecuteAsync(sqlString);
            }
        }

        /// <summary>
        /// 执行 SQL
        /// </summary>
        /// <param name="sqlString">sql字符串</param>
        /// <param name="param">传入参数</param>
        /// <returns></returns>
        public int Execute(string sqlString, object? param)
        {
            using (IDbConnection conn = GetDbConnection())
            {
                return conn.Execute(sqlString, param);
            }
        }

        /// <summary>
        /// 执行 SQL
        /// </summary>
        /// <param name="sqlString">sql字符串</param>
        /// <param name="param">传入参数</param>
        /// <returns></returns>
        public async Task<int> ExecuteAsync(string sqlString, object? param)
        {
            using (IDbConnection conn = GetDbConnection())
            {
                return await conn.ExecuteAsync(sqlString, param);
            }
        }

        /// <summary>
        /// 执行 SQL
        /// </summary>
        /// <param name="sqlString">sql字符串</param>
        /// <param name="param">传入参数</param>
        /// <param name="dbTransaction">数据库事务</param>
        /// <returns></returns>
        public int Execute(string sqlString, object? param = null, IDbTransaction? dbTransaction = null)
        {
            using (IDbConnection conn = GetDbConnection())
            {
                if (dbTransaction == null)
                {
                    return conn.Execute(sqlString, param);
                }
                return conn.Execute(sqlString, param, dbTransaction);
            }
        }

        /// <summary>
        /// 执行 SQL
        /// </summary>
        /// <param name="sqlString">sql字符串</param>
        /// <param name="param">传入参数</param>
        /// <param name="dbTransaction">数据库事务</param>
        /// <returns></returns>
        public async Task<int> ExecuteAsync(string sqlString, object? param = null, IDbTransaction? dbTransaction = null)
        {
            using (IDbConnection conn = GetDbConnection())
            {
                if (dbTransaction == null)
                {
                    return await conn.ExecuteAsync(sqlString, param);
                }
                return await conn.ExecuteAsync(sqlString, param, dbTransaction);
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="sqlString">sql字符串</param>
        /// <param name="param">传入参数</param>
        /// <returns></returns>
        public int ExecuteStoredProcedure(string storedProcedure, object? param = null)
        {
            using (IDbConnection conn = GetDbConnection())
            {
                return conn.Execute(storedProcedure, param, null, null, CommandType.StoredProcedure);
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="sqlString">sql字符串</param>
        /// <param name="param">传入参数</param>
        /// <returns></returns>
        public async Task<int> ExecuteStoredProcedureAsync(string storedProcedure, object? param = null)
        {
            using (IDbConnection conn = GetDbConnection())
            {
                return await conn.ExecuteAsync(storedProcedure, param, null, null, CommandType.StoredProcedure);
            }
        }

        #endregion

        #region 获取SQL

        /// <summary>
        /// 获取SQL
        /// </summary>
        /// <returns></returns>
        public string GetSql()
        {
            return GetSql(new QueryParamenter());
        }

        /// <summary>
        /// 获取SQL
        /// </summary>
        /// <param name="queryParamenter"></param>
        /// <returns></returns>
        public string GetSql(QueryParamenter queryParamenter)
        {
            // 分页记录起始
            int start = 0;

            // 是否分页
            bool isPage = false;

            string sql = "SELECT ";

            // 生成去重语句
            if (queryParamenter.Distinct)
            {
                sql += "DISTINCT ";
            }

            // 生成查询字段
            if (queryParamenter.Fields != null)
            {
                sql += string.Join(", ", queryParamenter.Fields);
            }
            else
            {
                sql += string.Join(", ", _fields);
            }

            sql += $" FROM {_tableName}";

            // 生成查询条件
            if (!string.IsNullOrWhiteSpace(queryParamenter.Where))
            {
                sql += " WHERE " + queryParamenter.Where;
            }

            // 判断是否分页
            if (queryParamenter.Page.HasValue && queryParamenter.PageSize.HasValue && queryParamenter.Page > 0 && queryParamenter.PageSize > 0)
            {
                isPage = true;
                start = (queryParamenter.Page.Value - 1) * queryParamenter.PageSize.Value;
            }

            // 生成分组
            if (!string.IsNullOrWhiteSpace(queryParamenter.GroupBy))
            {
                sql += " GROUP BY " + queryParamenter.GroupBy;
            }

            // 生产排序
            bool isOrderBy = false;
            if (!string.IsNullOrWhiteSpace(queryParamenter.OrderBy))
            {
                isOrderBy = true;
                sql += " ORDER BY " + queryParamenter.OrderBy;
            }

            // 分页
            if (isPage)
            {
                switch (DbType)
                {
                    case "SqlServer":
                        sql += $"{(isOrderBy ? " ORDER BY" : "")} Id OFFSET {start} ROWS FETCH NEXT {queryParamenter.PageSize ?? 20} ROWS ONLY";
                        break;
                    case "MySql":
                    case "PgSql":
                    case "Sqlite":
                        sql += $" LIMIT {queryParamenter.PageSize ?? 20} OFFSET {start}";
                        break;
                }
            }

            sql += ";";

            return sql;
        }

        /// <summary>
        /// 获取统计SQL
        /// </summary>
        /// <param name="queryParamenter"></param>
        /// <returns></returns>
        public string GetCountSql(QueryParamenter queryParamenter)
        {
            string sql = "SELECT ";

            // 生成去重语句
            if (queryParamenter.Distinct)
            {
                sql += "DISTINCT ";
            }

            // 生成查询字段
            sql += $" COUNT(1) FROM {_tableName}";

            // 生成查询条件
            if (!string.IsNullOrWhiteSpace(queryParamenter.Where))
            {
                sql += " WHERE " + queryParamenter.Where;
            }

            sql += ";";

            return sql;
        }

        /// <summary>
        /// 查询全部SQL
        /// </summary>
        /// <returns></returns>
        public string GetAllSql()
        {
            return "SELECT " + string.Join(", ", _fields) + $" FROM {_tableName};";
        }

        /// <summary>
        /// 查询全部SQL
        /// </summary>
        /// <returns></returns>
        public string GetAllSql<T>()
        {
            // 实例化字段
            var fields = new List<string>();

            // 获取表名
            var type = typeof(T);
            var tableName = type.Name;

            // 获取字段
            var props = type.GetProperties();
            for (var i = 0; i < props.Length; i++)
            {
                fields.Add(props[i].Name);
            }

            return "SELECT " + string.Join(", ", fields) + $" FROM {tableName};";
        }

        /// <summary>
        /// 通过id查询SQL
        /// </summary>
        /// <returns></returns>
        public string GetByIdSql()
        {
            string sql = GetAllSql();
            sql = sql.Substring(0, sql.IndexOf(";"));
            sql += " WHERE Id = @Id";
            return sql;
        }

        /// <summary>
        /// 通过id查询SQL
        /// </summary>
        /// <returns></returns>
        public string GetByIdSql<T>()
        {
            string sql = GetAllSql<T>();
            sql = sql.Substring(0, sql.IndexOf(";"));
            sql += " WHERE Id = @Id";
            return sql;
        }

        /// <summary>
        /// 通过一堆id查询SQL
        /// </summary>
        /// <returns></returns>
        public string GetByIdsSql()
        {
            string sql = GetAllSql();
            sql = sql.Substring(0, sql.IndexOf(";"));
            sql += " WHERE Ids IN @Ids";
            return sql;
        }

        /// <summary>
        /// 插入SQL
        /// </summary>
        /// <returns></returns>
        public string InsertSql()
        {
            return $"INSERT INTO {_tableName} (" + string.Join(", ", _fields) + ") VALUES (@" + string.Join(", @", _fields) + ");";
        }

        /// <summary>
        /// 更新SQL
        /// </summary>
        /// <returns></returns>
        public string UpdateSql()
        {
            string sql = $"UPDATE {_tableName} SET";
            var i = 0;
            foreach (var field in _fields)
            {
                if (field == "Id")
                {
                    continue;
                }
                if (i == 0)
                {
                    sql += " ";
                }
                else
                {
                    sql += ", ";
                }
                sql += field + " = @" + field;
                i++;
            }
            sql += " WHERE Id = @Id";
            return sql;
        }

        /// <summary>
        /// 更新SQL
        /// </summary>
        /// <param name="fields">要更新的字段</param>
        /// <returns></returns>
        public string UpdateSql(string[] fields)
        {
            string sql = $"UPDATE {_tableName} SET ";
            foreach (var field in fields)
            {
                sql += field + " = @" + field;
            }
            sql += " WHERE Id = @Id";
            return sql;
        }

        /// <summary>
        /// 更新一堆SQL
        /// </summary>
        /// <returns></returns>
        public string UpdatesSql()
        {
            string sql = $"UPDATE {_tableName} SET ";
            foreach (var field in _fields)
            {
                sql += field + " = @" + field;
            }
            sql += " WHERE Ids IN @Ids";
            return sql;
        }

        /// <summary>
        /// 更新一堆SQL
        /// </summary>
        /// <param name="fields">要更新的字段</param>
        /// <returns></returns>
        public string UpdatesSql(string[] fields)
        {
            string sql = $"UPDATE {_tableName} SET ";
            foreach (var field in fields)
            {
                sql += field + " = @" + field;
            }
            sql += " WHERE Ids IN @Ids";
            return sql;
        }

        /// <summary>
        /// 获取删除SQL
        /// </summary>
        /// <returns></returns>
        public string DeleteAllSql()
        {
            return $"DELETE {_tableName};";
        }

        /// <summary>
        /// 通过id获取删除SQL
        /// </summary>
        /// <returns></returns>
        public string DeleteSql()
        {
            return $"DELETE {_tableName} WHERE Id = @Id;";
        }

        /// <summary>
        /// 通过一堆id获取删除SQL
        /// </summary>
        /// <returns></returns>
        public string DeletesSql()
        {
            return $"DELETE {_tableName} WHERE Ids IN @Ids;";
        }

        #endregion

        #region 获取值和编码

        /// <summary>
        /// 设置id
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public long SetId(object? param)
        {
            long id = 0;
            Type type;
            if (param != null)
            {
                id = GetNextValue();
                type = param.GetType();
                var props = type.GetProperties();
                foreach (var prop in props)
                {
                    if (prop.Name == "Id")
                    {
                        prop.SetValue(param, id);
                    }
                }
            }
            return id;
        }

        /// <summary>
        /// 设置id
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public async Task<long> SetIdAsync(object? param)
        {
            long id = 0;
            Type type;
            if (param != null)
            {
                id = await GetNextValueAsync();
                type = param.GetType();
                var props = type.GetProperties();
                foreach (var prop in props)
                {
                    if (prop.Name == "Id")
                    {
                        prop.SetValue(param, id);
                    }
                }
            }
            return id;
        }

        /// <summary>
        /// 获取下一个值。请在数据库中建立表（NextValue）,字段有（TableName: varchar, FieldName: varchar, Value: long）
        /// </summary>
        /// <param name="defaultValue">默认值：1</param>
        /// <returns></returns>
        public long GetNextValue(long defaultValue = 1)
        {
            return GetNextValue(_tableName, "Id", defaultValue);
        }

        /// <summary>
        /// 获取下一个值。请在数据库中建立表（NextValue）,字段有（TableName: varchar, FieldName: varchar, Value: long）
        /// </summary>
        /// <param name="defaultValue">默认值：1</param>
        /// <returns></returns>
        public async Task<long> GetNextValueAsync(long defaultValue = 1)
        {
            return await GetNextValueAsync(_tableName, "Id", defaultValue);
        }

        /// <summary>
        /// 获取下一个值。请在数据库中建立表（NextValue）,字段有（TableName: varchar, FieldName: varchar, Value: long）
        /// </summary>
        /// <param name="defaultValue">默认值：1</param>
        /// <returns></returns>
        public long GetNextValue(string tableName, string fieldName, long defaultValue = 1)
        {
            string querySql = $"SELECT TableName, FieldName, Value FROM nextvalue WHERE TableName = @TableName AND FieldName = @FieldName;";
            string insertSql = $"INSERT INTO NextValue(TableName, FieldName, Value) Values(@TableName, @FieldName, @Value);";
            string updateSql = $"UPDATE NextValue SET Value = @Value WHERE TableName = @TableName AND FieldName = @FieldName;";
            var next = QueryFirst<NextValue>(querySql, new
            {
                TableName = tableName,
                FieldName = fieldName
            });
            long result = 0;

            /**
             * 没有记录，插入
             * 有记录，更新，值递增
             */
            if (next == null)
            {
                result = Execute(insertSql, new
                {
                    TableName = tableName,
                    FieldName = fieldName,
                    Value = defaultValue + 1
                });
                if (result == 0)
                {
                    throw new ApplicationException("插入下一个值失败！");
                }
                return defaultValue;
            }
            else
            {
                next.Value++;
                result = Execute(updateSql, new
                {
                    TableName = tableName,
                    FieldName = fieldName,
                    Value = next.Value
                });
                if (result == 0)
                {
                    throw new ApplicationException("更新下一个值失败！");
                }
                return next.Value;
            }
        }

        /// <summary>
        /// 获取下一个值。请在数据库中建立表（NextValue）,字段有（TableName: varchar, FieldName: varchar, Value: long）
        /// </summary>
        /// <param name="defaultValue">默认值：1</param>
        /// <returns></returns>
        public async Task<long> GetNextValueAsync(string tableName, string fieldName, long defaultValue = 1)
        {
            string querySql = $"SELECT TableName, FieldName, Value FROM nextvalue WHERE TableName = @TableName AND FieldName = @FieldName;";
            string insertSql = $"INSERT INTO NextValue (TableName, FieldName, Value) Values(@TableName, @FieldName, @Value);";
            string updateSql = $"UPDATE NextValue SET Value = @Value WHERE TableName = @TableName AND FieldName = @FieldName;";
            var next = await QueryFirstAsync<NextValue>(querySql, new
            {
                TableName = tableName,
                FieldName = fieldName
            });
            long result = 0;

            /**
             * 没有记录，插入
             * 有记录，更新，值递增
             */
            if (next == null)
            {
                result = await ExecuteAsync(insertSql, new
                {
                    TableName = tableName,
                    FieldName = fieldName,
                    Value = defaultValue + 1
                });
                if (result == 0)
                {
                    throw new ApplicationException("插入下一个值失败！");
                }
                return defaultValue;
            }
            else
            {
                result = await ExecuteAsync(updateSql, new
                {
                    TableName = tableName,
                    FieldName = fieldName,
                    Value = next.Value + 1
                });
                if (result == 0)
                {
                    throw new ApplicationException("更新下一个值失败！");
                }
                return next.Value;
            }
        }

        #endregion
    }
}