using Dapper;
using Estsh.Core.Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Estsh.Core.CodeGenerate
{
    public class CodeGenerator
    {
        protected DapperDbContext dapperDbContext { get; set; }
        public void Generate(string baseSpace, string tables, DapperDbContext dbContext)
        {
            string modelsSpace = baseSpace + ".Models";
            dapperDbContext = dbContext;
            List<TableInfo> listTable = GetWhereTables(tables);
            foreach (var tableInfo in listTable)
            {

                List<FieldInfo> listField = GetAllColumns(tableInfo.TableName);
                GenerateModels(modelsSpace, tableInfo, listField);
            }
        }

        /// <summary>
        /// 生成Models文件
        /// </summary>
        /// <param name="modelsNamespace">命名空间</param>
        /// <param name="tableInfo">表信息</param>
        /// <param name="listField">字段列表信息</param>
        private void GenerateModels(string modelsSpace, TableInfo tableInfo, List<FieldInfo> listField)
        {
            StringBuilder modelcontent = new StringBuilder(1024);//数据库模型字段
            foreach (FieldInfo fieldInfo in listField)
            {
                //主键
                if (!isExtColumnName(fieldInfo.FieldName))
                {
                    modelcontent.AppendLine($"        [Column(\"{fieldInfo.FieldName}\")]");
                    modelcontent.AppendLine($"        [DisplayName(\"{fieldInfo.Description}\")]");
                    modelcontent.Append($"        public {fieldInfo.DataType}{""} {dealUnderline(fieldInfo.FieldName)}");
                    modelcontent.AppendLine(" { get; set; }");
                    modelcontent.AppendLine();
                }
            }
            var path = AppDomain.CurrentDomain.BaseDirectory;
            //path = path.Substring(0, path.IndexOf("\\bin"));
            //var parentPath = path.Substring(0, path.LastIndexOf("/"));
            var parentPath = path.Substring(0, path.Length - 1);
            
            var servicesPath = parentPath + "/" + modelsSpace;
            if (!Directory.Exists(servicesPath))
            {
                servicesPath = parentPath + "/" + modelsSpace + "/Models";
                Directory.CreateDirectory(servicesPath);
            }
            var fullPath = servicesPath + "/" + dealUnderline(tableInfo.TableName) + ".cs";
            var content = ReadTemplate("ModelsTemplate.txt");
            content = content
                .Replace("{ModelNamespace}", modelsSpace)
                .Replace("{TableNameDesc}", tableInfo.Description)
                .Replace("{TableName}", tableInfo.TableName)
                .Replace("{ModelName}", dealUnderline(tableInfo.TableName))
                .Replace("{ModelContent}", modelcontent.ToString());
            WriteAndSave(fullPath, content);
        }

        //特殊列不生成
        private bool isExtColumnName(String name)
        {
            if("enabled*update_userid*update_time*create_userid*create_time*guid*factory_id*factory_code*timestamp".Contains(name.ToLower()))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        private string dealUnderline(string oldname)
        {
            string tmp = oldname.ToLower();
            string newStr = "";
            string[] tbs = tmp.Split("_");
            for (int i = 0; i < tbs.Count(); i++)
            {
                string strat = tbs[i].Substring(0, 1).ToUpper();
                string end = tbs[i].Substring(1);
                newStr += strat + end;
            }
            return newStr;
        }

        /// <summary>
        /// 从代码模板中读取内容
        /// </summary>
        /// <param name="templateName">模板名称，应包括文件扩展名称。比如：template.txt</param>
        /// <returns></returns>
        private static string ReadTemplate(string templateName)
        {
            var currentAssembly = Assembly.GetExecutingAssembly();
            var content = string.Empty;
            using (var stream = currentAssembly.GetManifestResourceStream($"{currentAssembly.GetName().Name}.CodeTemplate.{templateName}"))
            {
                if (stream != null)
                {
                    using var reader = new StreamReader(stream);
                    content = reader.ReadToEnd();
                }
            }
            return content;
        }

        /// <summary>
        /// 写文件
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="content"></param>
        private static void WriteAndSave(string fileName, string content)
        {
            //实例化一个文件流--->与写入文件相关联
            using var fs = new FileStream(fileName, FileMode.Create, FileAccess.Write);
            //实例化一个StreamWriter-->与fs相关联
            using var sw = new StreamWriter(fs);
            //开始写入
            sw.Write(content);
            //清空缓冲区
            sw.Flush();
            //关闭流
            sw.Close();
            fs.Close();
        }

        public List<TableInfo> GetWhereTables(string tables = null)
        {
            var sql = string.Format(@"SELECT tbs.name as TableName ,ds.value as Description FROM sys.tables tbs
                                        left join sys.extended_properties ds on ds.major_id=tbs.object_id and ds.minor_id=0");
            if (!string.IsNullOrEmpty(tables))
            {
                sql += string.Format(@" where tbs.name in('{0}')", tables.Replace(",", "','"));
            }
            var list = new List<TableInfo>();
            using (IDbConnection conn = dapperDbContext.GetDbConnection())
            {
                //conn.ConnectionString = "pooling=true;connection lifetime=5;min pool size = 2;max pool size=6;server=43.254.221.166;database=SAP_IF;User id=sa; password=Estsh123456;MultipleActiveResultSets=True;Enlist=false;Connect Timeout=1200;";
                conn.Open();
                list = conn.Query<TableInfo>(sql).ToList();
            }
            return list;
        }

        public List<FieldInfo> GetAllColumns(string tableName)
        {
            if (tableName == null)
                throw new ArgumentNullException(nameof(tableName));

            var sql = string.Format(@"
               SELECT a.name as FieldName,
                (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '0' end) as Increment, 
                (case when (SELECT count(*) FROM sysobjects 
                WHERE (name in (SELECT name FROM sysindexes 
                WHERE (id = a.id) AND (indid in 
                (SELECT indid FROM sysindexkeys 
                WHERE (id = a.id) AND (colid in 
                (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) 
                AND (xtype = 'PK'))>0 then '1' else '0' end) as IsIdentity,b.name as FieldType,a.length as FieldMaxLength, 
                COLUMNPROPERTY(a.id,a.name,'PRECISION') as FieldPrecision, 
                isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as FieldScale,(case when a.isnullable=1 then '1'else '0' end) IsNullable, 
                isnull(g.[value], ' ') AS Description
                FROM syscolumns a 
                left join systypes b on a.xtype=b.xusertype 
                inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 
                left join syscomments e on a.cdefault=e.id 
                left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id
                left join sys.extended_properties f on d.id=f.class and f.minor_id=0
                WHERE d.name='{0}' --如果只查询指定表,加上此条件
                order by a.id,a.colorder", tableName);
            List<FieldInfo> list = new List<FieldInfo>();

            using (IDbConnection conn = dapperDbContext.GetDbConnection())
            {
                //conn.ConnectionString = "pooling=true;connection lifetime=5;min pool size = 2;max pool size=6;server=43.254.221.166;database=SAP_IF;User id=sa; password=Estsh123456;MultipleActiveResultSets=True;Enlist=false;Connect Timeout=1200;";
                conn.Open();
                IEnumerable<dynamic> dlist = conn.Query(sql);
                foreach (var item in dlist)
                {
                    FieldInfo dbFieldInfo = new FieldInfo
                    {
                        FieldName = item.FieldName,
                        //Increment = item.Increment == "1" ? true : false,
                        IsIdentity = item.IsIdentity == "1" ? true : false,
                        FieldType = item.FieldType.ToString(),
                        DataType = item.FieldType.ToString(),
                        FieldMaxLength = item.FieldMaxLength,
                        FieldPrecision = item.FieldPrecision,
                        FieldScale = item.FieldScale,
                        IsNullable = item.IsNullable == "1" ? true : false,
                        FieldDefaultValue = item.FieldDefaultValue,
                        Description = item.Description
                    };
                    list.Add(dbFieldInfo);
                }
            }

            List<FieldInfo> reslist = new List<FieldInfo>();
            foreach (FieldInfo info in list)
            {
                info.DataType = ConvertDataType(info);
                reslist.Add(info);
            }
            return reslist;
        }

        #region 字段转换
        /// <summary>
        /// 将字段信息的类型转换为C#信息
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public string ConvertDataType(FieldInfo info)
        {
            if (info == null)
                throw new ArgumentNullException(nameof(info));
            if (string.IsNullOrEmpty(info.FieldType))
                throw new ArgumentNullException(nameof(info.FieldType));
            info.DataType = SqlType2CsharpTypeStr(info.FieldType, info.IsNullable);
            return info.DataType;
        }

        /// <summary>
        /// 将数据库类型转为系统类型。
        /// </summary>
        /// <param name="sqlType">数据库字段类型</param>
        /// <param name="isNullable">字段是否可空</param>
        /// <returns></returns>
        public static string SqlType2CsharpTypeStr(string sqlType, bool isNullable = false)
        {
            if (string.IsNullOrEmpty(sqlType))
                throw new ArgumentNullException(nameof(sqlType));
            var val = string.Empty;
            var allowNull = false;
            switch (sqlType.ToLower())
            {
                case "bit":
                    val = "bool";
                    break;
                case "int":
                    val = "int";
                    break;
                case "smallint":
                    val = "short";
                    break;
                case "bigint":
                    val = "long";
                    break;
                case "tinyint":
                    val = "bool";
                    break;

                case "binary":
                case "image":
                case "varbinary":
                    val = "byte[]";
                    allowNull = true;
                    break;

                case "decimal":
                    val = "decimal";
                    break;
                case "numeric":
                case "money":
                case "smallmoney":
                    val = "decimal";
                    break;

                case "float":
                    val = "float";
                    break;
                case "real":
                    val = "Single";
                    break;

                case "datetime":
                    val = "DateTime";
                    break;
                case "smalldatetime":
                case "timestamp":
                    val = "DateTime";
                    break;

                case "uniqueidentifier":
                    val = "Guid";
                    break;
                case "Variant":
                    val = "object";
                    allowNull = true;
                    break;

                case "text":
                    val = "string";
                    allowNull = true;
                    break;
                case "ntext":
                    val = "string";
                    allowNull = true;
                    break;
                case "char":
                    val = "string";
                    allowNull = true;
                    break;
                case "nchar":
                    val = "string";
                    allowNull = true;
                    break;
                case "varchar":
                    val = "string";
                    allowNull = true;
                    break;
                case "nvarchar":
                    val = "string";
                    allowNull = true;
                    break;
                default:
                    val = "string";
                    allowNull = true;
                    break;
            }
            if (isNullable && !allowNull)
                return val + "?";
            return val;
        }
        #endregion





    }
}