using Furion;
using Furion.DatabaseAccessor;
using Furion.DependencyInjection;
using Furion.DynamicApiController;
using Furion.FriendlyException;
using Mapster;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata;
using System.Linq.Dynamic.Core;
using System.Text;
using System.Web;
using Admin.NET.Core;
using Microsoft.AspNetCore.Authorization;

namespace Admin.NET.Application
{
    /// <summary>
    /// Excel模板服务
    /// </summary>
    [Route("api")]
    [ApiDescriptionSettings(Name = "ExcelTemplate", Order = 100)]
    [AllowAnonymous]
    public class SysExcelTemplateService : ISysExcelTemplateService, IDynamicApiController, ITransient
    {
        private readonly IRepository<SysExcelTemplate, MasterDbContextLocator> _sysExcelTemplateRep;
        private readonly IRepository<SysDictType, MasterDbContextLocator> _sysDictTypeRep;
        private readonly IRepository<SysDictData, MasterDbContextLocator> _sysDictDataRep;
        private readonly static object _lock = new();

        /// <summary>
        /// Excel模板构造函数
        /// </summary>
        /// <param name="sysExcelTemplateRep"></param>
        /// <param name="sysDictTypeRep"></param>
        /// <param name="sysDictDataRep"></param>
        public SysExcelTemplateService(
            IRepository<SysExcelTemplate, MasterDbContextLocator> sysExcelTemplateRep,
            IRepository<SysDictType, MasterDbContextLocator> sysDictTypeRep,
            IRepository<SysDictData, MasterDbContextLocator> sysDictDataRep
        )
        {
            _sysExcelTemplateRep = sysExcelTemplateRep;
            _sysDictTypeRep = sysDictTypeRep;
            _sysDictDataRep = sysDictDataRep;
        }

        /// <summary>
        /// 分页查询Excel模板
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [HttpGet("sysExcelTemplate/page")]
        public async Task<PageResult<SysExcelTemplateOutput>> PageAsync([FromQuery] SysExcelTemplateSearch input)
        {
            var pName = input.Name?.Trim() ?? "";
            var pVersion = input.Version?.Trim() ?? "";
            var pAppName = input.AppName?.Trim() ?? "";
            var pClassName = input.ClassName?.Trim() ?? "";
            var pTemplateFileName = input.TemplateFileName?.Trim() ?? "";
            var pUnionUniqueFields = input.UnionUniqueFields?.Trim() ?? "";
            var pStatus = input.Status;
            var sysExcelTemplates = await _sysExcelTemplateRep.DetachedEntities
                .Where(!string.IsNullOrEmpty(pName), u => EF.Functions.Like(u.Name, $"%{pName}%"))
                .Where(!string.IsNullOrEmpty(pVersion), u => EF.Functions.Like(u.Version, $"%{pVersion}%"))
                .Where(!string.IsNullOrEmpty(pAppName), u => EF.Functions.Like(u.AppName, $"%{pAppName}%"))
                .Where(!string.IsNullOrEmpty(pClassName), u => EF.Functions.Like(u.ClassName, $"%{pClassName}%"))
                .Where(!string.IsNullOrEmpty(pTemplateFileName), u => EF.Functions.Like(u.TemplateFileName, $"%{pTemplateFileName}%"))
                .Where(!string.IsNullOrEmpty(pUnionUniqueFields), u => EF.Functions.Like(u.UnionUniqueFields, $"%{pUnionUniqueFields}%"))
                .Where(pStatus != null, u => u.Status == pStatus)
            .OrderBy(PageInputOrder.OrderBuilder(input))
            .ProjectToType<SysExcelTemplateOutput>()
            .ToADPagedListAsync(input.PageNo, input.PageSize);
            return sysExcelTemplates;
        }

        /// <summary>
        /// 不分页查询Excel模板列表
        /// </summary>
        /// <param name="input">Excel模板查询参数</param>
        /// <returns>(Excel模板)实例列表</returns>
        [HttpGet("sysExcelTemplate/listNonPage")]
        public async Task<List<SysExcelTemplateOutput>> ListNonPageAsync([FromQuery] SysExcelTemplateSearchNonPage input)
        {
            var pName = input.Name?.Trim() ?? "";
            var pVersion = input.Version?.Trim() ?? "";
            var pAppName = input.AppName?.Trim() ?? "";
            var pClassName = input.ClassName?.Trim() ?? "";
            var pTemplateFileName = input.TemplateFileName?.Trim() ?? "";
            var pUnionUniqueFields = input.UnionUniqueFields?.Trim() ?? "";
            var pStatus = input.Status;
            var sysExcelTemplates = await _sysExcelTemplateRep.DetachedEntities
                .Where(!string.IsNullOrEmpty(pName), u => EF.Functions.Like(u.Name, $"%{pName}%"))
                .Where(!string.IsNullOrEmpty(pVersion), u => EF.Functions.Like(u.Version, $"%{pVersion}%"))
                .Where(!string.IsNullOrEmpty(pAppName), u => EF.Functions.Like(u.AppName, $"%{pAppName}%"))
                .Where(!string.IsNullOrEmpty(pClassName), u => EF.Functions.Like(u.ClassName, $"%{pClassName}%"))
                .Where(!string.IsNullOrEmpty(pTemplateFileName), u => EF.Functions.Like(u.TemplateFileName, $"%{pTemplateFileName}%"))
                .Where(!string.IsNullOrEmpty(pUnionUniqueFields), u => EF.Functions.Like(u.UnionUniqueFields, $"%{pUnionUniqueFields}%"))
                .Where(pStatus != null, u => u.Status == pStatus)
            .OrderBy(PageInputOrder.OrderNonPageBuilder(input))
            .ProjectToType<SysExcelTemplateOutput>()
            .ToListAsync();
            return sysExcelTemplates;
        }

        /// <summary>
        /// 增加Excel模板
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [HttpPost("sysExcelTemplate/add")]
        public async Task AddAsync(AddSysExcelTemplateInput input)
        {
            var sysExcelTemplate = input.Adapt<SysExcelTemplate>();
            await _sysExcelTemplateRep.InsertAsync(sysExcelTemplate);
        }

        /// <summary>
        /// 删除Excel模板
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [HttpPost("sysExcelTemplate/delete")]
        public async Task DeleteAsync(DeleteSysExcelTemplateInput input)
        {
            var sysExcelTemplate = await _sysExcelTemplateRep.FirstOrDefaultAsync(u => u.Id == input.Id);
            await _sysExcelTemplateRep.DeleteAsync(sysExcelTemplate);
        }

        /// <summary>
        /// 更新Excel模板
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [HttpPost("sysExcelTemplate/edit")]
        public async Task UpdateAsync(UpdateSysExcelTemplateInput input)
        {
            var isExist = await _sysExcelTemplateRep.AnyAsync(u => u.Id == input.Id, false);
            if (!isExist) throw Oops.Oh(ErrorCode.D3000);
            var sysExcelTemplate = input.Adapt<SysExcelTemplate>();
            await _sysExcelTemplateRep.UpdateAsync(sysExcelTemplate, ignoreNullValues: false);
        }

        /// <summary>
        /// 修改Excel模板状态
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [HttpPost("sysExcelTemplate/changeStatus")]
        public async Task ChangeSysExcelTemplateStatusAsync(UpdateSysExcelTemplateStatusInput input)
        {
            var sysExcelTemplate = await _sysExcelTemplateRep.FirstOrDefaultAsync(u => u.Id == input.Id);
            if (!Enum.IsDefined(typeof(CommonStatus), input.Status))
                throw Oops.Oh(ErrorCode.D3005);
            sysExcelTemplate.Status = input.Status;
        }

        /// <summary>
        /// 获取Excel模板
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [HttpGet("sysExcelTemplate/detail")]
        public async Task<SysExcelTemplateOutput?> GetAsync([FromQuery] QuerySysExcelTemplateInput input)
        {
            return (await _sysExcelTemplateRep.DetachedEntities.FirstOrDefaultAsync(u => u.Id == input.Id))?.Adapt<SysExcelTemplateOutput>();
        }

        /// <summary>
        /// 获取Excel模板列表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [HttpGet("sysExcelTemplate/list")]
        public async Task<List<SysExcelTemplateOutput>> ListAsync([FromQuery] SysExcelTemplateInput input)
        {
            return await _sysExcelTemplateRep.DetachedEntities.ProjectToType<SysExcelTemplateOutput>().ToListAsync();
        }

        /// <summary>
        /// 根据Excel模板查询参数导出Excel
        /// </summary>
        /// <param name="input">Excel模板查询参数</param>
        /// <returns>导出的Excel文件</returns>
        [HttpGet("sysExcelTemplate/toExcel")]
        public async Task<IActionResult> ToExcelAsync([FromQuery] SysExcelTemplateSearchNonPage input)
        {
            var sysExcelTemplateList = await ListNonPageAsync(input);
            MemoryStream ms = new();
            DataConvertUtil.ToExcelData(sysExcelTemplateList, _sysDictTypeRep, _sysDictDataRep, out List<string> headers,
                out List<List<object>> data, out string sheetName);
            var excelTemplate = await GetByAppNameAndClassNameAndVersionAsync("SysExcelTemplate", "v1");
            if (excelTemplate != null)
            {
                ExcelUtil.ToExcel((App.Configuration["Excel:Template"] ?? "") + (App.Configuration["AppName"] ?? "") + @"\" + excelTemplate.TemplateFileName, headers, data, sheetName, excelTemplate.HeadStartLine, excelTemplate.DataStartLine, ms);
            }
            else
            {
                ExcelUtil.ToExcel(headers, data, sheetName, ms);
            }
            ms.Position = 0;
            var fileName = HttpUtility.UrlEncode($"{sheetName}[{DateTimeOffset.Now:yyyy-MM-dd}].xlsx", Encoding.GetEncoding("UTF-8"));
            return new FileStreamResult(ms, "application/octet-stream") { FileDownloadName = fileName };
        }

        /// <summary>
        /// 根据Excel模板查询参数导出Excel
        /// </summary>
        /// <param name="input">Excel模板查询参数</param>
        /// <param name="appName"></param>
        /// <returns>导出的Excel文件</returns>
        [HttpGet("sysExcelTemplate/toExcelByAppName")]
        public async Task<IActionResult> ToExcelAsync([FromQuery] SysExcelTemplateSearchNonPage input, [FromQuery] string appName)
        {
            var sysExcelTemplateList = await ListNonPageAsync(input);
            MemoryStream ms = new();
            DataConvertUtil.ToExcelData(sysExcelTemplateList, _sysDictTypeRep, _sysDictDataRep, out List<string> headers,
                out List<List<object>> data, out string sheetName);
            var excelTemplate = await GetByAppNameAndClassNameAndVersionAsync("SysExcelTemplate", "v1");
            if (excelTemplate != null)
            {
                ExcelUtil.ToExcel((App.Configuration["Excel:Template"] ?? "") + appName + @"\" + excelTemplate.TemplateFileName, headers, data, sheetName, excelTemplate.HeadStartLine, excelTemplate.DataStartLine, ms);
            }
            else
            {
                ExcelUtil.ToExcel(headers, data, sheetName, ms);
            }
            ms.Position = 0;
            var fileName = HttpUtility.UrlEncode($"{sheetName}[{DateTimeOffset.Now:yyyy-MM-dd}].xlsx", Encoding.GetEncoding("UTF-8"));
            return new FileStreamResult(ms, "application/octet-stream") { FileDownloadName = fileName };
        }

        /// <summary>
        /// 根据类名及版本号获取单个Excel模板
        /// </summary>
        /// <param name="className">类名</param>
        /// <param name="version">版本号</param>
        /// <returns>Excel模板实例</returns>
        [HttpGet("sysExcelTemplate/getAppNameAndByClassNameAndVersion")]
        public async Task<SysExcelTemplateOutput?> GetByAppNameAndClassNameAndVersionAsync([FromQuery] string className, [FromQuery] string version)
        {
            return await _sysExcelTemplateRep.DetachedEntities.ProjectToType<SysExcelTemplateOutput>()
                .FirstOrDefaultAsync(u => u.ClassName == className && u.Version == version);
        }

        /// <summary>
        /// 导入Excel模板文件
        /// </summary>
        /// <param name="file">Excel模板文件</param>
        /// <param name="importExcelType">Excel导入方式</param>
        /// <returns>导入的记录数</returns>
        [HttpPost("sysExcelTemplate/fromExcel")]
        public async Task<int> FromExcelAsync(IFormFile file, [FromQuery] ImportExcelType importExcelType)
        {
            int size = 200;
            var excelTemplate = await GetByAppNameAndClassNameAndVersionAsync("SysExcelTemplate", "v2");
            if (excelTemplate == null) throw Oops.Oh(ErrorCode.Excel002);
            var keys = excelTemplate.UnionUniqueFields.Split(",") ?? Array.Empty<string>();
            for (var i = 0; i < keys.Length; i++)
            {
                keys[i] = keys[i]?.Trim() ?? string.Empty;
            }
            ExcelUtil.FromExcel(file, excelTemplate.HeadStartLine, excelTemplate.DataStartLine, out List<string> headers, out List<List<object?>> data, out string sheetName);
            List<SysExcelTemplateOutput> sysExcelTemplateList = DataConvertUtil.ToObjectList(headers, data, sheetName, keys, excelTemplate?.DataStartLine ?? 2, out Dictionary<string, SysExcelTemplateOutput> dict);
            List<Dictionary<string, object>> uniqueKeyValueDictList = sysExcelTemplateList.ParseUniqueKeyValueDictList(keys.ToList(), excelTemplate?.DataStartLine ?? 2, sheetName);
            var filters = DataConvertUtil.GetExpressionListByUniqueDict<SysExcelTemplate>(keys.ToList(), uniqueKeyValueDictList, size);
            var selectKeys = keys.ToList();
            if (!selectKeys.Contains("Id")) selectKeys.Add("Id");
            var selector = DataConvertUtil.GetSelectExpressionListByUniqueDict<SysExcelTemplate, SysExcelTemplateOutput>(selectKeys);
            List<SysExcelTemplate> updates = new();
            List<SysExcelTemplate> adds = new();

            lock (_lock)
            {
                foreach (var filter in filters)
                {
                    var sysExcelTemplateExistSubList = _sysExcelTemplateRep.Where(filter).Select(selector).ToList();
                    sysExcelTemplateExistSubList.ForEach(x =>
                    {
                        var k = DataConvertUtil.GetKey(x, keys);
                        if (dict.ContainsKey(k)) dict[k].Id = x.Id;
                    });
                }
                foreach (var sysExcelTemplate in sysExcelTemplateList)
                {
                    if (sysExcelTemplate.Id > 0)
                    {
                        if (importExcelType == ImportExcelType.ADD_AND_UPDATE) updates.Add(sysExcelTemplate.Adapt<SysExcelTemplate>());
                    }
                    else
                    {
                        adds.Add(sysExcelTemplate.Adapt<SysExcelTemplate>());
                    }
                }

                if (importExcelType == ImportExcelType.ADD_AND_UPDATE) updates.ForEach(x => _sysExcelTemplateRep.Update(x));

                var maxId = _sysExcelTemplateRep.DetachedEntities.OrderByDescending(x => x.Id).Select(x => x.Id).FirstOrDefault();
                adds.ForEach(x => x.Id = ++maxId);
                Db.GetDbContext().Set<SysExcelTemplate>().AddRange(adds);
                Db.GetDbContext().SaveChanges();
            }
            await Task.CompletedTask;
            return adds.Count;
        }

        /// <summary>
        /// 根据版本下载Excel导入模板
        /// </summary>
        /// <param name="version">模板版本</param>
        /// <returns>下载的模板</returns>
        [HttpGet("sysExcelTemplate/downloadExcelTemplate")]
        public async Task<IActionResult> DownloadExcelTemplate([FromQuery] string version)
        {
            var excelTemplate = await GetByAppNameAndClassNameAndVersionAsync("SysExcelTemplate", version);
            if (excelTemplate == null) throw Oops.Oh(ErrorCode.Excel002);

            var path = Path.Combine((App.Configuration["Excel:Template"] ?? "") + (App.Configuration["AppName"] ?? "") + @"\", excelTemplate.TemplateFileName);
            Stream ms = FileUtil.Download(path, excelTemplate.TemplateFileName);
            var fileName = HttpUtility.UrlEncode($"{excelTemplate.Name}导入模板.xlsx", Encoding.GetEncoding("UTF-8"));
            return new FileStreamResult(ms, "application/octet-stream") { FileDownloadName = fileName };
        }

        /// <summary>
        /// 获取实体名称获取属性集合
        /// </summary>
        /// <param name="className">实体名称</param>
        /// <returns></returns>
        [HttpGet("sysExcelTemplate/getColumnList")]
        public async Task<List<TableColumn>> GetColumnListAsync([FromQuery] string className)
        {
            var dbContext = Db.GetDbContext();

            var entityType = dbContext.GetService<IDesignTimeModel>().Model.GetEntityTypes()
                .FirstOrDefault(u => u.ClrType.Name == className);
            if (entityType == null) return new List<TableColumn>();


            var type = entityType.ClrType;
            if (type == null) return new List<TableColumn>();

            var columnList = new List<string>()
            {
                "Id", "CreatedTime", "UpdatedTime", "CreatedUserId", "CreatedUserName", "UpdatedUserId", "UpdatedUserName", "IsDeleted"
            };

            List<TableColumn> columns = type.GetProperties()
                .Select(propertyInfo => entityType.FindProperty(propertyInfo.Name))
                .Where(p => p != null && !columnList.Contains(p.Name)).Select(p =>
                {
                    var propInfo = p?.PropertyInfo;
                    var type = propInfo?.PropertyType;
                    var typeName = type?.ToString() ?? string.Empty;
                    return new TableColumn
                    {
                        IsRequired = FieldUtil.IsRequired(p ?? null!, typeName),
                        ColumnName = p?.Name,
                        ColumnKey = ("Id".Equals(p?.Name)).ToString(),
                        DataType = typeName,
                        ColumnComment = p?.GetComment()
                    };
                }).ToList();

            columns.ForEach(x => x.Remark = x.DataType.GetColumnRemark());
            await Task.CompletedTask;
            return columns;
        }

        /// <summary>
        /// 据类名查询其查询标量类型
        /// </summary>
        /// <param name="className">类名</param>
        /// <returns></returns>
        [HttpGet("sysExcelTemplate/queryUniqueColumns")]
        public async Task<List<TableColumn>> QueryUniqueColumns(string className)
        {
            var dbContext = Db.GetDbContext();//默认数据库
            var entityType = dbContext?.GetService<IDesignTimeModel>().Model.GetEntityTypes().FirstOrDefault(u => u.ClrType.Name == className);
            await Task.CompletedTask;
            return TypeUtil.GetUniqueColumns(entityType);
        }

        /// <summary>
        /// 根据类名查询其查询表信息
        /// </summary>
        /// <param name="className">类型名</param>
        /// <returns></returns>
        [HttpGet("sysExcelTemplate/queryTable")]
        public async Task<TableInfo?> QueryTable(string className)
        {
            var dbContext = Db.GetDbContext();//默认数据库
            var entityType = dbContext?.GetService<IDesignTimeModel>().Model.GetEntityTypes().FirstOrDefault(u => u.ClrType.Name == className);
            await Task.CompletedTask;
            return TypeUtil.GetTableInfo(entityType);
        }

        /// <summary>
        /// 根据类名生成相应的种子代码
        /// </summary>
        /// <param name="className">类名</param>
        /// <param name="prefix">前缀</param>
        /// <returns></returns>
        [HttpGet("sysExcelTemplate/parseSeedData")]
        public async Task<string> ParseSeedData([FromQuery] string className, string prefix)
        {
            var mId = await ParseId(prefix);

            var tableInfo = await QueryTable(className);
            var appName = App.Configuration["AppName"];
            // var folder = App.Configuration["Desktop"];
            var folder = System.IO.Path.Combine(App.WebHostEnvironment.WebRootPath, "AutoCreateFile", "ExcelTemplateTxt");
            var unionUniqueColumns = await QueryUniqueColumns(className);
            string unionUniqueFields = "";
            unionUniqueColumns.ForEach(x => unionUniqueFields += x.ColumnName + ",");
            if (unionUniqueFields.EndsWith(",")) unionUniqueFields = unionUniqueFields[..^1];

            string fileName = DateTime.Now.Ticks + ".txt";
            string msgExcelTemplate = $"new SysExcelTemplate {{ Id={mId++}, Name=\"{tableInfo?.TableComment ?? ""}\", Version = \"v1\" , ClassName=\"{tableInfo?.TableName ?? ""}\", TemplateFileName=\"{(tableInfo?.TableName ?? "").ToUnderLine()}_v1.xlsx\", UnionUniqueFields=\"{unionUniqueFields}\", AppName = \"{appName}\", HeadStartLine = 1, DataStartLine = 2, Status = 0}},";
            FileUtil.WriteLine(folder, fileName, msgExcelTemplate);

            msgExcelTemplate = $"new SysExcelTemplate {{ Id={mId++}, Name=\"{tableInfo?.TableComment ?? ""}\", Version = \"v2\" , ClassName=\"{tableInfo?.TableName ?? ""}\", TemplateFileName=\"{(tableInfo?.TableName ?? "").ToUnderLine()}_v2.xlsx\", UnionUniqueFields=\"{unionUniqueFields}\", AppName = \"{appName}\", HeadStartLine = 11, DataStartLine = 12, Status = 0}},";
            FileUtil.WriteLine(folder, fileName, msgExcelTemplate);
            FileUtil.WriteLine(folder, fileName, "");

            return Path.Combine(folder, fileName);
        }

        /// <summary>
        /// 获取模板提示字符
        /// </summary>
        /// <param name="className">实体名称</param>
        /// <returns></returns>
        [HttpGet("sysExcelTemplate/parseTemplateHint")]
        public async Task<string> ParseTemplateHintAsync([FromQuery] string className)
        {
            List<TableColumn> tableColumns = await GetColumnListAsync(className);
            var folder = App.Configuration["Desktop"];
            string fileName = DateTime.Now.Ticks + ".txt";

            FileUtil.WriteLine(folder, fileName, "");
            FileUtil.WriteLine(folder, fileName, "1.支持Excel2007及以上版本文件。");
            FileUtil.WriteLine(folder, fileName, "2.导入新增数据时不能超过5000行。");
            FileUtil.WriteLine(folder, fileName, "3.导入更新数据时不能超过2000行。");

            FileUtil.WriteLine(folder, fileName, "");
            FileUtil.WriteLine(folder, fileName, "");

            Dictionary<string, string> typeNameDict = new()
            {
                { "System.String", "文本。如: 钟孝本"},
                { "System.Int32", "数字。如: 4377"},
                { "System.Int64", "数字。如: 4377"},
                { "System.DateTimeOffset", "日期。 如: 2023/3/1"},
                { "System.DateTime", "日期。 如: 2023/3/1"}
            };

            tableColumns.ForEach(x =>
            {
                string text = "";
                text += x.ColumnComment + "(" + (x.IsRequired ? "必填" : "非必填") + "): ";
                text += typeNameDict.ContainsKey(x.DataType) ? typeNameDict[x.DataType] : ("文本。 可选项为: " + x.Remark);
                FileUtil.WriteLine(folder, fileName, text);
            });

            return Path.Combine(folder, fileName);
        }

        /// <summary>
        /// 根据类名获取最大ID加1
        /// </summary>
        /// <param name="prefix">前缀</param>
        /// <returns></returns>
        [HttpGet("sysExcelTemplate/parseId")]
        public async Task<long> ParseId([FromQuery] string prefix)
        {
            var lastExcelTemplate = await _sysExcelTemplateRep.DetachedEntities.OrderByDescending(x => x.Id).FirstOrDefaultAsync();

            var mId = (lastExcelTemplate?.Id + 1) ?? 1;
            mId = long.Parse(prefix + mId.ToString()[1..]);
            return mId;
        }
    }
}