using Hh.Mes.Common.log;
using Hh.Mes.Common.Request;
using Hh.Mes.POJO.Entity;
using Hh.Mes.POJO.Response;
using Hh.Mes.POJO.ViewModel;
using Hh.Mes.POJO.WebEntity.tool;
using Hh.Mes.Service.Repository;
using Hh.Mes.Service.WebService.Base;
using Hh.Mes.T4;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using DbType = SqlSugar.DbType;

namespace Hh.Mes.Service.WebService.Tool
{
    public class CodeGeneratorService : RepositorySqlSugar<base_Entity>
    {
        private readonly DbType dbType;
        private readonly SysModuleService _moduleService;

        public CodeGeneratorService(SysModuleService moduleService)
        {
            dbType = Context.CurrentConnectionConfig.DbType;
            _moduleService = moduleService;
        }
        public dynamic Load(PageReq pageReq, TableInfo entity)
        {
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var data = GetAllTableDescription(entity.TableCode, entity.TableName);
                var result = new Response<List<TableInfo>>()
                {
                    Result = data.Skip(pageReq.limit * (pageReq.page - 1)).Take(pageReq.limit).ToList(),
                    Count = data.Count()
                };
                return result;
            }, catchRetrunValue: "list");
        }


        /// <summary>
        /// 获取代码
        /// </summary>
        /// <param name="entityName">表名称</param>
        /// <param name="areaName">区域名称</param>
        /// <returns></returns>
        public CodePreviewVM GetCode(string entityName, string areaName, IEnumerable<string> areaItems)
        {
            var jsText = string.Empty;
            var modelText = string.Empty;
            var schemaTables = GetSchemaTable(entityName);
            if (schemaTables.Any())
            {
                var jsTemplate = new JavaScriptTemplate(areaName, entityName, schemaTables);
                jsText = jsTemplate.TransformText();
                var modelTemplate = new ModelTemplate(entityName, schemaTables);
                modelText = modelTemplate.TransformText();
            }
            var controllerTemplate = new ControllerTemplate(areaName, entityName);
            //过滤部分列，用于在Service和Cshtml渲染
            var filterColumns = new List<string> { "Id", "keys", "createBy", "createTime", "updateTime", "updateBy" };
            var newColumns = schemaTables.Where(x => !filterColumns.Any(f => x.ColumnName.Equals(f, StringComparison.OrdinalIgnoreCase))).ToList();
            var serviceTemplate = new ServiceTemplate(areaName, entityName, newColumns);
            var tableDescription = GetTableDescription(entityName);
            var cshtmlTemplate = new CshtmlTemplate(areaName, entityName, newColumns, tableDescription?.TableName);
            var vm = new CodePreviewVM()
            {
                JavaScriptTemp = jsText,
                ModelTemp = modelText,
                ControllerTemp = controllerTemplate.TransformText(),
                ServiceTemp = serviceTemplate.TransformText(),
                CshtmlTemp = cshtmlTemplate.TransformText(),
                AreaItems = areaItems,
                AreaName = areaName,
                EntityName = entityName,
            };
            return vm;
        }

        /// <summary>
        /// 获取所有表名称和表注释
        /// </summary>
        /// <param name="tableCode">表名称，用于模糊查询</param>
        /// <param name="tableName">表说明/备注，用于模糊查询</param>
        /// <returns></returns>
        public List<TableInfo> GetAllTableDescription(string tableCode = "", string tableName = "")
        {
            var result = new List<TableInfo>();
            string sql = string.Empty;
            switch (dbType)
            {
                case DbType.MySql:
                    var tableSchemaName = Context.CurrentConnectionConfig.ConnectionString.Split(";")
                        .FirstOrDefault(x => x.Equals("database", StringComparison.OrdinalIgnoreCase))?.Split("=").LastOrDefault();
                    if (!string.IsNullOrWhiteSpace(tableSchemaName))
                    {
                        sql = $"select table_name as TableCode,table_comment AS TableName from information_schema.tables where table_schema='{tableSchemaName}' ";
                        if (!string.IsNullOrWhiteSpace(tableCode))
                        {
                            sql += $"And table_name Like '%{tableCode}%' ";
                        }
                        if (!string.IsNullOrWhiteSpace(tableName))
                        {
                            sql += $"And table_comment LIKE '%{tableName}%' ";
                        }
                        sql += "ORDER BY TableCode";
                        result = Context.Ado.SqlQuery<TableInfo>(sql).ToList();
                    }
                    break;
                case DbType.SqlServer:
                    sql = "SELECT * FROM vw_TableDes WHERE 1=1 ";
                    if (!string.IsNullOrWhiteSpace(tableCode))
                    {
                        sql += $"And TableCode Like '%{tableCode}%' ";
                    }
                    if (!string.IsNullOrWhiteSpace(tableName))
                    {
                        sql += $"And Cast(TableName as varchar) LIKE '%{tableName}%' ";
                    }
                    sql += "ORDER BY TableCode";
                    result = Context.Ado.SqlQuery<TableInfo>(sql).ToList();
                    break;
                case DbType.Sqlite:
                    sql = $"SELECT Name AS TableCode FROM sqlite_master WHERE type='table' ";
                    if (!string.IsNullOrWhiteSpace(tableCode))
                    {
                        sql += $"And name Like '%{tableCode}%' ";
                    }
                    sql += "ORDER BY name";
                    result = Context.Ado.SqlQuery<TableInfo>(sql).ToList();
                    break;
                case DbType.Oracle:
                    break;
                case DbType.PostgreSQL:
                    break;
                case DbType.Dm:
                    break;
                case DbType.Kdbndp:
                    break;
                default:
                    break;
            }
            return result;
        }

        /// <summary>
        /// 获取单张的表名称和表注释
        /// </summary>
        /// <param name="tableCode">表名称</param>
        /// <returns></returns>
        public TableInfo GetTableDescription(string tableCode)
        {
            var table = new TableInfo()
            {
                TableCode = tableCode,
                TableName = string.Empty
            };
            string sql;
            switch (dbType)
            {
                case DbType.MySql:
                    var tableSchemaName = Context.CurrentConnectionConfig.ConnectionString.Split(";")
                        .FirstOrDefault(x => x.Equals("database", StringComparison.OrdinalIgnoreCase))?.Split("=").LastOrDefault();
                    if (!string.IsNullOrWhiteSpace(tableSchemaName))
                    {
                        sql = $"select table_name as TableCode,table_comment AS TableName from information_schema.tables where table_schema='{tableSchemaName}'";
                        table = Context.Ado.SqlQuerySingle<TableInfo>(sql);
                    }
                    break;
                case DbType.SqlServer:
                    sql = $"SELECT * FROM vw_TableDes WHERE TableCode = '{tableCode}' ORDER BY TableCode";
                    table = Context.Ado.SqlQuerySingle<TableInfo>(sql);
                    break;
                case DbType.Sqlite:
                    break;
                case DbType.Oracle:
                    break;
                case DbType.PostgreSQL:
                    break;
                case DbType.Dm:
                    break;
                case DbType.Kdbndp:
                    break;
                default:
                    break;
            }
            return table;
        }

        /// <summary>
        /// 获取表字段信息，包含字段名称、字段类型、字段长度以及字段注释
        /// </summary>
        /// <param name="tableCode">表名</param>
        public List<TableColumnInfo> GetSchemaTable(string tableCode)
        {
            var data = new List<TableColumnInfo>();
            string sql;
            switch (dbType)
            {
                case DbType.MySql:
                    var tableSchemaName = Context.CurrentConnectionConfig.ConnectionString.Split(";")
                        .FirstOrDefault(x => x.Equals("database", StringComparison.OrdinalIgnoreCase))?.Split("=").LastOrDefault();
                    sql = $@"SELECT DISTINCT
	column_name AS ColumnName,
	data_type AS TypeName,
	character_Maximum_length AS TypeLenght,
	column_comment AS ColumnDescription,
CASE
		is_nullable 
		WHEN 'NO' THEN
		0 ELSE 1 
	END AS NullAble 
FROM
	information_schema.COLUMNS 
WHERE
	table_schema = '{tableSchemaName}' 
	AND table_name = '{tableCode}'";
                    if (!string.IsNullOrWhiteSpace(tableCode))
                    {
                        data = Context.SqlQueryable<TableColumnInfo>(sql).ToList();
                    }
                    break;
                case DbType.SqlServer:
                    var objname = new SugarParameter("@objname", tableCode);
                    var result = Context.Ado.UseStoredProcedure().GetDataSetAll($"up_help", objname).Tables[1];
                    foreach (DataRow dr in result.Rows)
                    {
                        int.TryParse(dr["length"].ToString(), out int length);
                        var item = new TableColumnInfo
                        {
                            ColumnName = dr["Column_name"].ToString(),
                            TypeName = dr["Type"].ToString(),
                            ColumnDescription = dr["Column_Desc"].ToString(),
                            TypeLenght = length,
                            NullAble = dr["NullAble"].ToString().Equals("yes", StringComparison.OrdinalIgnoreCase)
                        };
                        data.Add(item);
                    }
                    break;
                case DbType.Sqlite:
                    sql = $"PRAGMA table_info('{tableCode}')";
                    var sqliteResult = Context.Ado.SqlQuery<dynamic>(sql).ToList();
                    foreach (var item in sqliteResult)
                    {
                        var typeString = $"{item.type}";
                        int.TryParse(Regex.Match(typeString, "[0-9]+").ToString(), out int lenght);
                        data.Add(new TableColumnInfo
                        {
                            ColumnName = item.name,
                            ColumnDescription = string.Empty,
                            TypeLenght = lenght,
                            NullAble = !Convert.ToBoolean(item.notnull),
                            TypeName = Regex.Match(typeString, "[A-Z,a-z]+").ToString(),
                        });
                    }
                    break;
                case DbType.Oracle:
                    break;
                case DbType.PostgreSQL:
                    break;
                case DbType.Dm:
                    break;
                case DbType.Kdbndp:
                    break;
                default:
                    break;
            }
            //数据格式化
            data.ForEach(x =>
            {
                x.ColumnName = FirstLowerCase(x.ColumnName);
                x.TypeName = SqlToCsharp(x.TypeName);
                x.ColumnDescription = Regex.Replace(x.ColumnDescription, @"[\r\n]", "");
            });
            return data;
        }

        /// <summary>
        /// 数据库类型转C#数据类型，目前仅支持Sql Server、MySql、Sqlite
        /// </summary>
        /// <param name="sqlFieldType">数据表字段类型</param>
        /// <returns>相对应的C#数据类型</returns>
        private string SqlToCsharp(string sqlFieldType)
        {
            string cSharpType;
            switch (sqlFieldType.ToLower())
            {
                case "image":
                case "varbinary":
                case "timestamp":
                case "binary":
                    cSharpType = "byte[]";
                    break;
                case "text":
                case "char":
                case "nvarchar":
                case "nchar":
                case "ntext":
                case "varchar":
                    cSharpType = "string";
                    break;
                case "uniqueidentifier":
                    cSharpType = "Guid";
                    break;
                case "tinyint":
                    cSharpType = "byte";
                    break;
                case "smallint":
                    cSharpType = "Int16";
                    break;
                case "int":
                case "integer":
                    cSharpType = "int";
                    break;
                case "smalldatetime":
                case "datetime":
                case "datetime2":
                    cSharpType = "DateTime";
                    break;
                case "decimal":
                case "smallmoney":
                case "money":
                    cSharpType = "decimal";
                    break;
                case "real":
                    cSharpType = "Single";
                    break;
                case "float":
                    cSharpType = "double";
                    break;
                case "bit":
                    cSharpType = "bool";
                    break;
                case "bigint":
                    cSharpType = "Int64";
                    break;
                default:
                    cSharpType = string.Empty;
                    break;
            }
            return cSharpType;
        }

        /// <summary>
        /// 字符串首字母转小写
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        private string FirstLowerCase(string str)
        {
            return str.Substring(0, 1).ToLower() + str.Substring(1);
        }

        /// <summary>
        /// 保存文件
        /// </summary>
        /// <param name="path"></param>
        /// <param name="text"></param>
        public Response SaveFile(string path, string text)
        {
            var result = new Response()
            {
                Status = true
            };
            var fileName = Path.GetFileName(path);
            var basePath = Path.GetDirectoryName(path);
            try
            {
                if (!File.Exists(path))
                {
                    if (!Directory.Exists(basePath))
                    {
                        Directory.CreateDirectory(basePath);
                    }
                    File.WriteAllText(path, text);
                    result.Message = $"“{fileName}”保存成功";
                }
                else
                {
                    result.Status = false;
                    result.Message = $"保存“{fileName}”失败，文件已存在！";
                }
            }
            catch (Exception ex)
            {
                result.Status = false;
                result.Message = $"保存“{fileName}”失败，{ex.Message}";
            }
            return result;
        }

        public string ConvertToControllerName(string tableName)
        {
            var str = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(tableName);
            return string.Join("", str.Split("_"));
        }

        /// <summary>
        /// 添加菜单栏和按钮
        /// </summary>
        /// <param name="module"></param>
        /// <param name="areaName">区域名称</param>
        /// <returns></returns>
        public Response InsertModelAndButton(SysModule module, string areaName)
        {
            var result = new Response()
            {
                Status = true
            };
            try
            {
                var oldmodule = Context.Queryable<SysModule>().Any(x => x.Url.Equals(module.Url));
                if (oldmodule)
                {
                    result.Status = false;
                    result.Message = $"路径为“{module.Url}”菜单项已存在！";
                }
                else
                {
                    var peer = Context.Queryable<SysModule>().Where(x => x.Url.StartsWith($"/{areaName}/")).First();
                    if (peer != null)
                    {
                        module.ParentName = peer.ParentName;
                        module.ParentId = peer.ParentId;
                    }
                    Response<SysModule> sysModule = _moduleService.Ins(module,base.sysWebUser.Account);
                    List<SysModuleElement> btns = _moduleService.DefaultBtns.Select(x => new SysModuleElement
                    {
                        AreaMenus = "1",
                        Name = x.Text,
                        Icon = x.Icon,
                        Class = x.BtnClass,
                        DomId = x.Value,
                        ModuleId = sysModule.Result.Id,
                        Remark = x.Text,
                        Sort = x.Sort,
                    }).ToList();
                    _moduleService.AddMenu(btns, base.sysWebUser.Account);
                    result = sysModule;
                }
            }
            catch (Exception ex)
            {
                result.Status = false;
                result.Message = ex.Message;
            }
            return result;
        }
    }
}