using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Mapster;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using NCC.Common.Core.Manager;
using NCC.Common.Enum;
using NCC.Common.Extension;
using NCC.Common.Filter;
using NCC.Common.Helper;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.Extend.Entitys.Dto.LqAttendanceSummary;
using NCC.Extend.Entitys.Enum;
using NCC.Extend.Entitys.lq_attendance_summary;
using NCC.Extend.Interfaces.LqAttendanceSummary;
using NCC.FriendlyException;
using NCC.System.Entitys.Permission;
using SqlSugar;
using Yitter.IdGenerator;

namespace NCC.Extend
{
    /// <summary>
    /// 考勤汇总服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "绿纤考勤汇总服务", Name = "LqAttendanceSummary", Order = 200)]
    [Route("api/Extend/[controller]")]
    public class LqAttendanceSummaryService : ILqAttendanceSummaryService, IDynamicApiController, ITransient
    {
        private readonly ISqlSugarClient _db;
        private readonly IUserManager _userManager;
        private readonly ILogger<LqAttendanceSummaryService> _logger;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="db">数据库客户端</param>
        /// <param name="userManager">用户管理器</param>
        /// <param name="logger">日志记录器</param>
        public LqAttendanceSummaryService(ISqlSugarClient db, IUserManager userManager, ILogger<LqAttendanceSummaryService> logger)
        {
            _db = db;
            _userManager = userManager;
            _logger = logger;
        }

        #region 上传Excel文件导入考勤汇总数据
        /// <summary>
        /// 上传Excel文件导入考勤汇总数据
        /// </summary>
        /// <remarks>
        /// 上传Excel文件批量导入考勤汇总数据
        /// 
        /// Excel文件格式要求：
        /// - 第一行必须是标题行
        /// - 列顺序：员工姓名、员工电话、年份、月份、出勤天数、请假天数、休息天数、备注
        /// - 支持.xlsx和.xls格式
        /// 
        /// 示例请求：
        /// POST /api/Extend/LqAttendanceSummary/ImportAttendanceDataFromExcel
        /// Content-Type: multipart/form-data
        /// 
        /// 参数说明：
        /// - file: Excel文件（必填）
        /// </remarks>
        /// <param name="file">Excel文件</param>
        /// <returns>导入结果</returns>
        /// <response code="200">导入成功</response>
        /// <response code="400">文件格式错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("ImportAttendanceDataFromExcel")]
        public async Task<dynamic> ImportAttendanceDataFromExcel(IFormFile file)
        {
            try
            {
                if (file == null || file.Length == 0)
                {
                    throw NCCException.Oh("请选择要上传的Excel文件");
                }

                // 检查文件格式
                var allowedExtensions = new[] { ".xlsx", ".xls" };
                var fileExtension = Path.GetExtension(file.FileName).ToLowerInvariant();
                if (!allowedExtensions.Contains(fileExtension))
                {
                    throw NCCException.Oh("只支持.xlsx和.xls格式的Excel文件");
                }

                var importData = new List<LqAttendanceSummaryImportInput>();

                // 保存临时文件
                var tempFilePath = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString() + Path.GetExtension(file.FileName));
                try
                {
                    using (var stream = new FileStream(tempFilePath, FileMode.Create))
                    {
                        await file.CopyToAsync(stream);
                    }

                    // 使用ExcelImportHelper读取Excel文件
                    var dataTable = ExcelImportHelper.ToDataTable(tempFilePath, 0, 0);

                    if (dataTable.Rows.Count == 0)
                    {
                        throw NCCException.Oh("Excel文件中没有数据行");
                    }

                    // 根据列名查找列索引（支持多种可能的列名）
                    int GetColumnIndex(string[] possibleNames)
                    {
                        foreach (var name in possibleNames)
                        {
                            for (int col = 0; col < dataTable.Columns.Count; col++)
                            {
                                var columnName = dataTable.Columns[col].ColumnName?.Trim();
                                if (columnName == name || columnName?.Contains(name) == true)
                                {
                                    return col;
                                }
                            }
                        }
                        return -1;
                    }

                    var nameColIndex = GetColumnIndex(new[] { "员工姓名", "姓名", "员工", "姓名" });
                    var phoneColIndex = GetColumnIndex(new[] { "员工电话", "电话", "手机", "手机号", "联系电话" });
                    var yearColIndex = GetColumnIndex(new[] { "年份", "年" });
                    var monthColIndex = GetColumnIndex(new[] { "月份", "月" });
                    var workDaysColIndex = GetColumnIndex(new[] { "出勤天数", "出勤", "工作天数", "上班天数" });
                    var leaveDaysColIndex = GetColumnIndex(new[] { "请假天数", "请假", "事假天数" });
                    var restDaysColIndex = GetColumnIndex(new[] { "休息天数", "休息", "休假天数" });
                    var remarkColIndex = GetColumnIndex(new[] { "备注", "说明", "备注信息" });

                    // 验证必需的列是否存在
                    if (nameColIndex == -1) throw NCCException.Oh("Excel文件中未找到'员工姓名'列");
                    if (phoneColIndex == -1) throw NCCException.Oh("Excel文件中未找到'员工电话'列");
                    if (yearColIndex == -1) throw NCCException.Oh("Excel文件中未找到'年份'列");
                    if (monthColIndex == -1) throw NCCException.Oh("Excel文件中未找到'月份'列");

                    // 从第1行开始读取数据（跳过标题行）
                    for (int i = 1; i < dataTable.Rows.Count; i++)
                    {
                        try
                        {
                            var row = dataTable.Rows[i];
                            var employeeName = row[nameColIndex]?.ToString()?.Trim();
                            var employeePhone = row[phoneColIndex]?.ToString()?.Trim();
                            
                            // 处理年份：可能是数字、日期或字符串
                            string yearText = null;
                            if (yearColIndex >= 0 && row[yearColIndex] != null && row[yearColIndex] != DBNull.Value)
                            {
                                if (row[yearColIndex] is DateTime dt)
                                {
                                    yearText = dt.Year.ToString();
                                }
                                else if (row[yearColIndex] is double d)
                                {
                                    yearText = ((int)d).ToString();
                                }
                                else
                                {
                                    yearText = row[yearColIndex].ToString()?.Trim();
                                }
                            }
                            
                            // 处理月份：可能是数字、日期或字符串
                            string monthText = null;
                            if (monthColIndex >= 0 && row[monthColIndex] != null && row[monthColIndex] != DBNull.Value)
                            {
                                if (row[monthColIndex] is DateTime dt)
                                {
                                    monthText = dt.Month.ToString();
                                }
                                else if (row[monthColIndex] is double d)
                                {
                                    monthText = ((int)d).ToString();
                                }
                                else
                                {
                                    monthText = row[monthColIndex].ToString()?.Trim();
                                }
                            }
                            
                            var workDaysText = workDaysColIndex >= 0 ? row[workDaysColIndex]?.ToString()?.Trim() : null;
                            var leaveDaysText = leaveDaysColIndex >= 0 ? row[leaveDaysColIndex]?.ToString()?.Trim() : null;
                            var restDaysText = restDaysColIndex >= 0 ? row[restDaysColIndex]?.ToString()?.Trim() : null;
                            var remark = remarkColIndex >= 0 ? row[remarkColIndex]?.ToString()?.Trim() : null;

                            // 跳过空行
                            if (string.IsNullOrEmpty(employeeName) && string.IsNullOrEmpty(employeePhone))
                            {
                                continue;
                            }

                            // 验证必填字段
                            if (string.IsNullOrEmpty(employeeName))
                            {
                                throw new Exception($"第{i + 1}行：员工姓名不能为空");
                            }
                            if (string.IsNullOrEmpty(employeePhone))
                            {
                                throw new Exception($"第{i + 1}行：员工电话不能为空");
                            }

                            // 解析数值字段
                            int year = 0;
                            int month = 0;
                            
                            // 解析年份：支持纯数字、日期格式、中文格式
                            if (string.IsNullOrEmpty(yearText))
                            {
                                throw new Exception($"第{i + 1}行：年份不能为空");
                            }
                            
                            // 尝试直接解析为整数
                            if (int.TryParse(yearText, out year))
                            {
                                // 成功解析
                            }
                            // 尝试解析日期格式（如：2025-11-01 或 2025/11/01）
                            else if (DateTime.TryParse(yearText, out DateTime yearDate))
                            {
                                year = yearDate.Year;
                            }
                            // 尝试解析中文格式（如：2025年）
                            else if (yearText.Contains("年"))
                            {
                                var yearMatch = Regex.Match(yearText, @"(\d{4})年");
                                if (yearMatch.Success && int.TryParse(yearMatch.Groups[1].Value, out year))
                                {
                                    // 成功解析
                                }
                                else
                                {
                                    throw new Exception($"第{i + 1}行：年份格式错误，无法解析：{yearText}");
                                }
                            }
                            else
                            {
                                throw new Exception($"第{i + 1}行：年份格式错误，无法解析。实际值：\"{yearText}\"（类型：{yearText?.GetType().Name}）");
                            }
                            
                            // 验证年份范围
                            if (year < 2020 || year > 2030)
                            {
                                throw new Exception($"第{i + 1}行：年份必须在2020-2030之间");
                            }
                            
                            // 解析月份：支持纯数字、日期格式、中文格式
                            if (string.IsNullOrEmpty(monthText))
                            {
                                throw new Exception($"第{i + 1}行：月份不能为空");
                            }
                            
                            // 尝试直接解析为整数
                            if (int.TryParse(monthText, out month))
                            {
                                // 成功解析
                            }
                            // 尝试解析日期格式（如：2025-11-01 或 2025/11/01）
                            else if (DateTime.TryParse(monthText, out DateTime monthDate))
                            {
                                month = monthDate.Month;
                            }
                            // 尝试解析中文格式（如：11月）
                            else if (monthText.Contains("月"))
                            {
                                var monthMatch = Regex.Match(monthText, @"(\d{1,2})月");
                                if (monthMatch.Success && int.TryParse(monthMatch.Groups[1].Value, out month))
                                {
                                    // 成功解析
                                }
                                else
                                {
                                    throw new Exception($"第{i + 1}行：月份格式错误，无法解析：{monthText}");
                                }
                            }
                            // 尝试解析"年-月"格式（如：2025-11）
                            else if (monthText.Contains("-") || monthText.Contains("/"))
                            {
                                var parts = monthText.Split(new[] { "-", "/" }, StringSplitOptions.RemoveEmptyEntries);
                                if (parts.Length >= 2 && int.TryParse(parts[1], out month))
                                {
                                    // 成功解析
                                }
                                else
                                {
                                    throw new Exception($"第{i + 1}行：月份格式错误，无法解析：{monthText}");
                                }
                            }
                            else
                            {
                                throw new Exception($"第{i + 1}行：月份格式错误，无法解析。实际值：\"{monthText}\"（类型：{monthText?.GetType().Name}）");
                            }
                            
                            // 验证月份范围
                            if (month < 1 || month > 12)
                            {
                                throw new Exception($"第{i + 1}行：月份必须在1-12之间");
                            }

                            decimal.TryParse(workDaysText, out decimal workDays);
                            decimal.TryParse(leaveDaysText, out decimal leaveDays);
                            decimal.TryParse(restDaysText, out decimal restDays);

                            var item = new LqAttendanceSummaryImportInput
                            {
                                EmployeeName = employeeName,
                                EmployeePhone = employeePhone,
                                Year = year,
                                Month = month,
                                WorkDays = workDays,
                                LeaveDays = leaveDays,
                                RestDays = restDays,
                                Remark = remark
                            };

                            importData.Add(item);
                        }
                        catch (Exception ex)
                        {
                            throw new Exception($"第{i + 1}行数据解析失败: {ex.Message}");
                        }
                    }
                }
                finally
                {
                    // 清理临时文件
                    if (File.Exists(tempFilePath))
                    {
                        File.Delete(tempFilePath);
                    }
                }

                if (!importData.Any())
                {
                    throw NCCException.Oh("Excel文件中没有有效的数据行");
                }

                // 处理导入数据
                return await ProcessImportData(importData);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "上传Excel文件导入考勤汇总数据失败");
                throw NCCException.Oh($"上传Excel文件导入考勤汇总数据失败: {ex.Message}");
            }
        }
        #endregion

        #region 处理导入数据
        /// <summary>
        /// 处理导入数据
        /// </summary>
        /// <param name="importData">导入数据列表</param>
        /// <returns>导入结果</returns>
        private async Task<dynamic> ProcessImportData(List<LqAttendanceSummaryImportInput> importData)
        {
            var successCount = 0;
            var failCount = 0;
            var errorMessages = new List<string>();
            var entitiesToInsert = new List<LqAttendanceSummaryEntity>();
            var entitiesToUpdate = new List<LqAttendanceSummaryEntity>();

            foreach (var item in importData)
            {
                try
                {
                    // 1. 根据员工姓名和电话查找用户ID
                    var user = await _db.Queryable<UserEntity>()
                        .Where(u => u.RealName == item.EmployeeName && u.MobilePhone == item.EmployeePhone)
                        .FirstAsync();

                    if (user == null)
                    {
                        errorMessages.Add($"员工 {item.EmployeeName}({item.EmployeePhone}) 不存在");
                        failCount++;
                        continue;
                    }

                    // 2. 检查是否已存在相同记录
                    var existingRecord = await _db.Queryable<LqAttendanceSummaryEntity>()
                        .Where(a => a.UserId == user.Id && a.Year == item.Year && a.Month == item.Month)
                        .FirstAsync();

                    if (existingRecord != null)
                    {
                        // 更新现有记录
                        existingRecord.EmployeeStatus = 1; // 默认在职
                        existingRecord.WorkDays = item.WorkDays;
                        existingRecord.LeaveDays = item.LeaveDays;
                        existingRecord.RestDays = item.RestDays;
                        existingRecord.Remark = item.Remark;
                        existingRecord.UpdateUser = _userManager.UserId;
                        existingRecord.UpdateTime = DateTime.Now;
                        entitiesToUpdate.Add(existingRecord);
                    }
                    else
                    {
                        // 创建新记录
                        var entity = new LqAttendanceSummaryEntity
                        {
                            Id = YitIdHelper.NextId().ToString(),
                            UserId = user.Id,
                            Year = item.Year,
                            Month = item.Month,
                            EmployeeStatus = 1, // 默认在职
                            WorkDays = item.WorkDays,
                            LeaveDays = item.LeaveDays,
                            RestDays = item.RestDays,
                            Remark = item.Remark,
                            CreateUser = _userManager.UserId,
                            CreateTime = DateTime.Now,
                            UpdateUser = _userManager.UserId,
                            UpdateTime = DateTime.Now,
                            IsEffective = 1
                        };
                        entitiesToInsert.Add(entity);
                    }

                    successCount++;
                }
                catch (Exception ex)
                {
                    errorMessages.Add($"处理员工 {item.EmployeeName} 数据失败: {ex.Message}");
                    failCount++;
                    _logger.LogError(ex, $"导入考勤数据失败 - 员工: {item.EmployeeName}");
                }
            }

            // 批量插入和更新
            if (entitiesToInsert.Any())
            {
                await _db.Insertable(entitiesToInsert).ExecuteCommandAsync();
            }

            if (entitiesToUpdate.Any())
            {
                await _db.Updateable(entitiesToUpdate).ExecuteCommandAsync();
            }

            return new
            {
                success = true,
                message = "导入完成",
                data = new
                {
                    totalCount = importData.Count,
                    successCount = successCount,
                    failCount = failCount,
                    insertCount = entitiesToInsert.Count,
                    updateCount = entitiesToUpdate.Count,
                    errorMessages = errorMessages
                }
            };
        }
        #endregion

        #region 获取考勤汇总列表
        /// <summary>
        /// 获取考勤汇总列表
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("")]
        public async Task<dynamic> GetList([FromQuery] LqAttendanceSummaryListQueryInput input)
        {
            var sidx = input.sidx == null ? "id" : input.sidx;
            var sord = input.sort == null ? "desc" : input.sort;
            var data = await _db.Queryable<LqAttendanceSummaryEntity>()
                .Where(a => a.IsEffective == StatusEnum.有效.GetHashCode())
                .WhereIF(!string.IsNullOrEmpty(input.UserId), a => a.UserId == input.UserId)
                .WhereIF(input.Year.HasValue, a => a.Year == input.Year)
                .WhereIF(input.Month.HasValue, a => a.Month == input.Month)
                .WhereIF(input.EmployeeStatus.HasValue, a => a.EmployeeStatus == input.EmployeeStatus)

                .Select(a => new LqAttendanceSummaryListOutput
                {
                    id = a.Id,
                    userId = a.UserId,
                    userName = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == a.UserId).Select(u => u.RealName),
                    year = a.Year,
                    month = a.Month,
                    employeeStatus = a.EmployeeStatus,
                    workDays = a.WorkDays,
                    leaveDays = a.LeaveDays,
                    restDays = a.RestDays,
                    remark = a.Remark,
                    createUser = a.CreateUser,
                    createUserName = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == a.CreateUser).Select(u => u.RealName),
                    createTime = a.CreateTime,
                    updateUser = a.UpdateUser,
                    updateUserName = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == a.UpdateUser).Select(u => u.RealName),
                    updateTime = a.UpdateTime,
                    isEffective = a.IsEffective,
                })
                .MergeTable()
                .OrderBy(sidx + " " + input.sort)
                .ToPagedListAsync(input.currentPage, input.pageSize);
            return PageResult<LqAttendanceSummaryListOutput>.SqlSugarPageResult(data);
        }
        #endregion

        #region 清空某一个月的数据
        /// <summary>
        ///  清空某一个月的数据
        /// </summary>
        /// <param name="year">年份</param>
        /// <param name="month">月份</param>
        /// <returns></returns>
        [HttpDelete("DeleteByMonth/{year}/{month}")]
        public async Task<dynamic> DeleteByMonth(string year, string month)
        {
            int yearInt = int.Parse(year);
            int monthInt = int.Parse(month);
            await _db.Deleteable<LqAttendanceSummaryEntity>().Where(p => p.Year == yearInt && p.Month == monthInt).ExecuteCommandAsync();
            return new
            {
                success = true,
                message = "清空成功"
            };
        }
        #endregion

        #region 生成健康师考勤模拟数据
        /// <summary>
        /// 生成健康师考勤模拟数据
        /// </summary>
        /// <remarks>
        /// 为所有健康师生成指定年月的考勤模拟数据
        /// 
        /// 规则：
        /// - 80%以上的健康师出勤天数 >= 21天
        /// - 其他健康师出勤天数随机生成（0-20天）
        /// - 员工状态默认为1（在职）
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "year": 2025,
        ///   "month": 11
        /// }
        /// ```
        /// </remarks>
        /// <param name="year">年份</param>
        /// <param name="month">月份</param>
        /// <returns>生成结果</returns>
        /// <response code="200">成功生成考勤模拟数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">生成失败</response>
        [HttpPost("GenerateMockData/{year}/{month}")]
        public async Task<dynamic> GenerateMockData(int year, int month)
        {
            try
            {
                // 验证参数
                if (year < 2000 || year > 3000)
                {
                    throw NCCException.Oh("年份参数无效");
                }
                if (month < 1 || month > 12)
                {
                    throw NCCException.Oh("月份参数无效，应在1-12之间");
                }

                // 查询所有健康师
                var healthCoaches = await _db.Queryable<UserEntity>()
                    .Where(u => u.Gw == "健康师" && u.EnabledMark == 1 && (u.DeleteMark == null || u.DeleteMark == 0))
                    .Select(u => new { u.Id, u.RealName })
                    .ToListAsync();

                if (!healthCoaches.Any())
                {
                    throw NCCException.Oh("未找到健康师用户");
                }

                // 检查是否已存在该年月的考勤数据
                var existingRecords = await _db.Queryable<LqAttendanceSummaryEntity>()
                    .Where(a => a.Year == year && a.Month == month)
                    .CountAsync();

                if (existingRecords > 0)
                {
                    throw NCCException.Oh($"已存在{year}年{month}月的考勤数据，请先清空后再生成");
                }

                var totalCount = healthCoaches.Count;
                var highAttendanceCount = (int)Math.Ceiling(totalCount * 0.8); // 80%以上，向上取整
                var lowAttendanceCount = totalCount - highAttendanceCount;

                var random = new Random();
                var entitiesToInsert = new List<LqAttendanceSummaryEntity>();
                var now = DateTime.Now;

                // 生成考勤数据
                for (int i = 0; i < totalCount; i++)
                {
                    var healthCoach = healthCoaches[i];
                    decimal workDays;
                    decimal leaveDays;
                    decimal restDays;

                    if (i < highAttendanceCount)
                    {
                        // 80%以上的健康师：出勤天数 >= 21天
                        workDays = random.Next(21, 31); // 21-30天
                        var remainingDays = 30 - workDays;
                        // 剩余天数分配给请假和休息
                        if (remainingDays > 0)
                        {
                            leaveDays = random.Next(0, (int)remainingDays + 1);
                            restDays = remainingDays - leaveDays;
                        }
                        else
                        {
                            leaveDays = 0;
                            restDays = 0;
                        }
                    }
                    else
                    {
                        // 其他20%的健康师：出勤天数 < 21天
                        workDays = random.Next(0, 21); // 0-20天
                        var remainingDays = 30 - workDays;
                        // 剩余天数分配给请假和休息
                        if (remainingDays > 0)
                        {
                            leaveDays = random.Next(0, (int)remainingDays + 1);
                            restDays = remainingDays - leaveDays;
                        }
                        else
                        {
                            leaveDays = 0;
                            restDays = 0;
                        }
                    }

                    var entity = new LqAttendanceSummaryEntity
                    {
                        Id = YitIdHelper.NextId().ToString(),
                        UserId = healthCoach.Id,
                        Year = year,
                        Month = month,
                        EmployeeStatus = 1, // 在职
                        WorkDays = workDays,
                        LeaveDays = leaveDays,
                        RestDays = restDays,
                        Remark = "模拟数据",
                        CreateUser = _userManager.UserId ?? "system",
                        CreateTime = now,
                        UpdateUser = _userManager.UserId ?? "system",
                        UpdateTime = now,
                        IsEffective = 1
                    };
                    entitiesToInsert.Add(entity);
                }

                // 批量插入
                await _db.Insertable(entitiesToInsert).ExecuteCommandAsync();

                // 统计结果
                var highAttendanceActual = entitiesToInsert.Count(e => e.WorkDays >= 21);
                var highAttendancePercentage = (decimal)highAttendanceActual / totalCount * 100;

                return new
                {
                    success = true,
                    message = "生成考勤模拟数据成功",
                    data = new
                    {
                        year = year,
                        month = month,
                        totalCount = totalCount,
                        highAttendanceCount = highAttendanceActual,
                        highAttendancePercentage = Math.Round(highAttendancePercentage, 2),
                        generatedCount = entitiesToInsert.Count
                    }
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"生成健康师考勤模拟数据失败 - 年份: {year}, 月份: {month}");
                throw NCCException.Oh($"生成健康师考勤模拟数据失败：{ex.Message}");
            }
        }
        #endregion


    }
}