using Cowain.Base.DBContext;
using Microsoft.EntityFrameworkCore;
using MySqlConnector;
using System.Data;
using System.Linq.Expressions;
using Dapper;
using Cowain.Base.IServices;

namespace Cowain.Base.Services;

public class BaseService : IBaseService
{
    // 替换为 IDbContextFactory<SqlDbContext>
    protected readonly IDbContextFactory<SqlDbContext> _dbContextFactory;

    public BaseService(IDbContextFactory<SqlDbContext> dbContextFactory)
    {
        _dbContextFactory = dbContextFactory;
    }

    /// <summary>
    /// 根据ID删除实体
    /// </summary>
    public int Delete<T>(int Id) where T : class
    {
        // 每次操作创建独立上下文，using 自动释放
        using var dbContext = _dbContextFactory.CreateDbContext();
        T? t = dbContext.Set<T>().Find(Id);
        if (t == null) throw new KeyNotFoundException($"未找到类型 {typeof(T).Name}，Id={Id}");
        dbContext.Set<T>().Remove(t);
        return dbContext.SaveChanges();
    }

    /// <summary>
    /// 根据ID异步删除实体（支持取消）
    /// </summary>
    public async Task<int> DeleteAsync<T>(int Id, CancellationToken cancellationToken = default) where T : class
    {
        await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
        var t = await dbContext.Set<T>().FindAsync(Id, cancellationToken).ConfigureAwait(false);
        if (t == null) throw new KeyNotFoundException($"未找到类型 {typeof(T).Name}，Id={Id}");
        dbContext.Set<T>().Remove(t);
        return await dbContext.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
    }

    /// <summary>
    /// 删除实体
    /// </summary>
    public int Delete<T>(T t) where T : class
    {
        ArgumentNullException.ThrowIfNull(t);
        using var dbContext = _dbContextFactory.CreateDbContext();
        var entry = dbContext.Entry(t);
        if (entry.State == EntityState.Detached)
        {
            dbContext.Set<T>().Attach(t);
        }
        dbContext.Set<T>().Remove(t);
        return dbContext.SaveChanges();
    }

    /// <summary>
    /// 删除实体（异步，支持取消）
    /// </summary>
    public async Task<int> DeleteAsync<T>(T t, CancellationToken cancellationToken = default) where T : class
    {
        ArgumentNullException.ThrowIfNull(t);
        await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
        var entry = dbContext.Entry(t);
        if (entry.State == EntityState.Detached)
        {
            dbContext.Set<T>().Attach(t);
        }
        dbContext.Set<T>().Remove(t);
        return await dbContext.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
    }

    /// <summary>
    /// 批量读取实体
    /// </summary>
    public List<T> Find<T>() where T : class
    {
        using var dbContext = _dbContextFactory.CreateDbContext();
        return dbContext.Set<T>().ToList();
    }

    /// <summary>
    /// 异步、安全的批量读取（支持取消）
    /// </summary>
    public async Task<List<T>> FindAsync<T>(CancellationToken cancellationToken = default) where T : class
    {
        await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
        return await dbContext.Set<T>().ToListAsync(cancellationToken).ConfigureAwait(false);
    }

    /// <summary>
    /// 根据ID查询实体
    /// </summary>
    public T? Find<T>(int id) where T : class
    {
        using var dbContext = _dbContextFactory.CreateDbContext();
        return dbContext.Set<T>().Find(id);
    }

    /// <summary>
    /// 根据ID异步查询实体（支持取消）
    /// </summary>
    public async Task<T?> FindAsync<T>(int id, CancellationToken cancellationToken = default) where T : class
    {
        await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
        return await dbContext.Set<T>().FindAsync(new object[] { id }, cancellationToken).ConfigureAwait(false);
    }

    /// <summary>
    /// 带表达式的异步查询（支持取消）
    /// </summary>
    public async Task<T?> FirstOrDefaultAsync<T>(Expression<Func<T, bool>> funcWhere, CancellationToken cancellationToken = default) where T : class
    {
        using var dbContext = _dbContextFactory.CreateDbContext();
        return await dbContext.Set<T>().Where(funcWhere).FirstOrDefaultAsync(cancellationToken);
    }

    /// <summary>
    /// 插入实体
    /// </summary>
    public int Insert<T>(T t) where T : class
    {
        ArgumentNullException.ThrowIfNull(t);
        using var dbContext = _dbContextFactory.CreateDbContext();
        dbContext.Set<T>().Add(t);
        return dbContext.SaveChanges();
    }

    /// <summary>
    /// 异步插入实体
    /// </summary>
    public async Task<int> InsertAsync<T>(T t, CancellationToken cancellationToken = default) where T : class
    {
        ArgumentNullException.ThrowIfNull(t);
        await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
        dbContext.Set<T>().Add(t);
        return await dbContext.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
    }

    /// <summary>
    /// 批量插入实体
    /// </summary>
    public int Insert<T>(IEnumerable<T> tList) where T : class
    {
        using var dbContext = _dbContextFactory.CreateDbContext();
        dbContext.Set<T>().AddRange(tList);
        return dbContext.SaveChanges();
    }

    /// <summary>
    /// 批量异步插入实体
    /// </summary>
    public async Task<int> InsertAsync<T>(IEnumerable<T> tList, CancellationToken cancellationToken = default) where T : class
    {
        await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
        dbContext.Set<T>().AddRange(tList);
        return await dbContext.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
    }

    /// <summary>
    /// 带表达式的查询
    /// </summary>
    public List<T> Query<T>(Expression<Func<T, bool>>? funcWhere = null) where T : class
    {
        // 注意：Query 返回 IQueryable 时，上下文生命周期需由调用方管理，或改为立即执行（ToList/First 等）
        var dbContext = _dbContextFactory.CreateDbContext();
        IQueryable<T> query = dbContext.Set<T>();
        if (funcWhere is not null)
        {
            query = query.Where(funcWhere);
        }
        return query.ToList();
    }


    /// <summary>
    /// 带表达式的异步查询（支持取消、空条件查全部）
    /// </summary>
    public async Task<List<T>> QueryAsync<T>(
        Expression<Func<T, bool>>? funcWhere = null, // 设置默认值，调用更便捷
        CancellationToken cancellationToken = default)
        where T : class
    {
        using var dbContext = _dbContextFactory.CreateDbContext();
        IQueryable<T> query = dbContext.Set<T>();

        if (funcWhere is not null)
        {
            query = query.Where(funcWhere);
        }

        return await query.ToListAsync(cancellationToken);
    }

    /// <summary>
    /// 基础版：返回实体列表+总条数（无投影）
    /// </summary>
    public async Task<(List<T> Data, int TotalCount)> QueryAsync<T, TKey>(
        Expression<Func<T, bool>>? funcWhere = null,
        Expression<Func<T, TKey>>? orderBy = null,
        bool isAscending = true,
        int pageIndex = 1,
        int pageSize = 20,
        CancellationToken cancellationToken = default)
        where T : class
    {
        pageIndex = pageIndex < 1 ? 1 : pageIndex;
        pageSize = pageSize < 0 ? 0 : pageSize;

        using var dbContext = _dbContextFactory.CreateDbContext();
        IQueryable<T> query = dbContext.Set<T>();

        if (funcWhere is not null) query = query.Where(funcWhere);
        if (orderBy is not null) query = isAscending ? query.OrderBy(orderBy) : query.OrderByDescending(orderBy);

        if (pageSize > 0)
        {
            long totalCountLong = await query.LongCountAsync(cancellationToken);
            int totalCount = (int)Math.Min(totalCountLong, int.MaxValue);
            var data = await query.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync(cancellationToken);
            return (data, totalCount);
        }
        else
        {
            var data = await query.ToListAsync(cancellationToken);
            return (data, data.Count);
        }
    }

    /// <summary>
    /// 增强版：支持投影（Select映射），直接返回ViewModel列表+总条数
    /// </summary>
    /// <typeparam name="T">原实体类型</typeparam>
    /// <typeparam name="TKey">排序字段类型</typeparam>
    /// <typeparam name="TViewModel">目标ViewModel类型</typeparam>
    /// <param name="selectExpression">投影表达式（EF会解析为SQL的SELECT字段）</param>
    public async Task<(List<TViewModel> Data, int TotalCount)> QueryAsync<T, TKey, TViewModel>(
        Expression<Func<T, TViewModel>> selectExpression, // 新增：投影映射表达式
        Expression<Func<T, bool>>? funcWhere = null,
        Expression<Func<T, TKey>>? orderBy = null,
        bool isAscending = true,
        int pageIndex = 1,
        int pageSize = 20,
        CancellationToken cancellationToken = default)
        where T : class
    {
        pageIndex = pageIndex < 1 ? 1 : pageIndex;
        pageSize = pageSize < 0 ? 0 : pageSize;

        using var dbContext = _dbContextFactory.CreateDbContext();
        IQueryable<T> query = dbContext.Set<T>();

        // 1. 过滤
        if (funcWhere is not null) query = query.Where(funcWhere);
        // 2. 排序
        if (orderBy is not null) query = isAscending ? query.OrderBy(orderBy) : query.OrderByDescending(orderBy);

        // 3. 先查总条数（注意：总条数要在投影前查，避免投影影响计数）
        long totalCountLong = await query.LongCountAsync(cancellationToken);
        int totalCount = (int)Math.Min(totalCountLong, int.MaxValue);

        // 4. 分页+投影（EF会转换为SQL：SELECT Id, Name, LayOutX, LayOutY FROM ... LIMIT ...）
        IQueryable<TViewModel> viewModelQuery = query
            .Skip((pageIndex - 1) * pageSize)
            .Take(pageSize)
            .Select(selectExpression); // 内置投影，替代手动Select

        // 5. 转为List
        var data = await viewModelQuery.ToListAsync(cancellationToken);

        return (data, totalCount);
    }

    /// <summary>
    /// 更新实体
    /// </summary>
    public int Update<T>(T t) where T : class
    {
        ArgumentNullException.ThrowIfNull(t);
        using var dbContext = _dbContextFactory.CreateDbContext();
        var entry = dbContext.Entry(t);
        if (entry.State == EntityState.Detached)
        {
            dbContext.Set<T>().Attach(t);
        }
        dbContext.Entry(t).State = EntityState.Modified;
        return dbContext.SaveChanges();
    }

    /// <summary>
    /// 异步更新实体（支持取消）
    /// </summary>
    public async Task<int> UpdateAsync<T>(T t, CancellationToken cancellationToken = default) where T : class
    {
        ArgumentNullException.ThrowIfNull(t);
        await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
        var entry = dbContext.Entry(t);
        if (entry.State == EntityState.Detached)
        {
            dbContext.Set<T>().Attach(t);
        }
        dbContext.Entry(t).State = EntityState.Modified;
        return await dbContext.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
    }

    /// <summary>
    /// 批量更新实体
    /// </summary>
    public int Update<T>(IEnumerable<T> tList) where T : class
    {
        ArgumentNullException.ThrowIfNull(tList);
        var list = tList as IList<T> ?? tList.ToList();
        if (list.Count == 0) return 0;

        using var dbContext = _dbContextFactory.CreateDbContext();
        dbContext.Set<T>().UpdateRange(list);
        return dbContext.SaveChanges();
    }

    /// <summary>
    /// 异步批量更新实体（支持取消）
    /// </summary>
    public async Task<int> UpdateAsync<T>(IEnumerable<T> tList, CancellationToken cancellationToken = default) where T : class
    {
        ArgumentNullException.ThrowIfNull(tList);
        var list = tList as IList<T> ?? tList.ToList();
        if (list.Count == 0) return 0;

        await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
        dbContext.Set<T>().UpdateRange(list);
        return await dbContext.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
    }

    /// <summary>
    /// 使用sql语句获取DataTable
    /// </summary>
    public DataTable GetDataTable(string sql, IEnumerable<MySqlParameter>? parameters = null)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));

        // 从工厂创建上下文获取连接字符串
        using var dbContext = _dbContextFactory.CreateDbContext();
        using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
        using var cmd = new MySqlCommand(sql, conn)
        {
            CommandType = CommandType.Text
        };

        if (parameters != null)
        {
            foreach (var p in parameters) cmd.Parameters.Add(p);
        }
        using var adapter = new MySqlDataAdapter(cmd);
        var table = new DataTable();
        adapter.Fill(table);
        return table;
    }

    /// <summary>
    /// 异步使用sql语句获取DataTable（支持取消）
    /// </summary>
    public async Task<DataTable> GetDataTableAsync(string sql, IEnumerable<MySqlParameter>? parameters = null, CancellationToken cancellationToken = default)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));

        await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
        await using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
        await conn.OpenAsync(cancellationToken).ConfigureAwait(false);

        using var cmd = new MySqlCommand(sql, conn) { CommandType = CommandType.Text };
        if (parameters != null) cmd.Parameters.AddRange(parameters.ToArray());

        using var reader = await cmd.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false);
        var table = new DataTable();
        table.Load(reader);
        return table;
    }

    // ================= Dapper 辅助方法 =================

    /// <summary>
    /// 使用 Dapper 查询并映射到 POCO（同步）
    /// </summary>
    public IEnumerable<T> QueryByDapper<T>(string sql, object? param = null)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
        using var dbContext = _dbContextFactory.CreateDbContext();
        using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
        conn.Open();
        return conn.Query<T>(sql, param);
    }

    /// <summary>
    /// 使用 Dapper 查询并映射到 POCO（异步，支持 CancellationToken）
    /// </summary>
    public async Task<IEnumerable<T>> QueryByDapperAsync<T>(string sql, object? param = null, CancellationToken cancellationToken = default)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
        await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
        await using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
        await conn.OpenAsync(cancellationToken).ConfigureAwait(false);
        var command = new CommandDefinition(sql, param, cancellationToken: cancellationToken);
        var result = await conn.QueryAsync<T>(command).ConfigureAwait(false);
        return result;
    }

    /// <summary>
    /// 使用 Dapper 查询单条记录（同步）
    /// </summary>
    public T? QueryFirstOrDefaultByDapper<T>(string sql, object? param = null)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
        using var dbContext = _dbContextFactory.CreateDbContext();
        using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
        conn.Open();
        return conn.QueryFirstOrDefault<T>(sql, param);
    }

    /// <summary>
    /// 使用 Dapper 查询单条记录（异步）
    /// </summary>
    public async Task<T?> QueryFirstOrDefaultByDapperAsync<T>(string sql, object? param = null, CancellationToken cancellationToken = default)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
        await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
        await using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
        await conn.OpenAsync(cancellationToken).ConfigureAwait(false);
        var command = new CommandDefinition(sql, param, cancellationToken: cancellationToken);
        return await conn.QueryFirstOrDefaultAsync<T>(command).ConfigureAwait(false);
    }

    /// <summary>
    /// 使用 Dapper 执行非查询 SQL（同步），返回受影响行数
    /// </summary>
    public int ExecuteByDapper(string sql, object? param = null)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
        using var dbContext = _dbContextFactory.CreateDbContext();
        using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
        conn.Open();
        return conn.Execute(sql, param);
    }

    /// <summary>
    /// 使用 Dapper 执行非查询 SQL（异步），返回受影响行数
    /// </summary>
    public async Task<int> ExecuteByDapperAsync(string sql, object? param = null, CancellationToken cancellationToken = default)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
        await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
        await using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
        await conn.OpenAsync(cancellationToken).ConfigureAwait(false);
        var command = new CommandDefinition(sql, param, cancellationToken: cancellationToken);
        return await conn.ExecuteAsync(command).ConfigureAwait(false);
    }

    /// <summary>
    /// 使用 Dapper 执行 SQL 并返回 DataTable（异步，支持 CancellationToken）
    /// </summary>
    public async Task<DataTable> QueryToDataTableByDapperAsync(string sql, object? param = null, CancellationToken cancellationToken = default)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
        await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
        await using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
        await conn.OpenAsync(cancellationToken).ConfigureAwait(false);
        var command = new CommandDefinition(sql, param, cancellationToken: cancellationToken, flags: CommandFlags.Buffered);
        await using var reader = await conn.ExecuteReaderAsync(command).ConfigureAwait(false);
        var table = new DataTable();
        table.Load(reader);
        return table;
    }

    /// <summary>
    /// 使用 Dapper 执行 SQL 并返回 DataTable（同步）
    /// </summary>
    public DataTable QueryToDataTableByDapper(string sql, object? param = null)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
        using var dbContext = _dbContextFactory.CreateDbContext();
        using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
        conn.Open();
        using var reader = conn.ExecuteReader(sql, param);
        var table = new DataTable();
        table.Load(reader);
        return table;
    }
}