using System;

using System.ComponentModel.DataAnnotations;

using System.ComponentModel.DataAnnotations.Schema;

using System.Diagnostics.CodeAnalysis;

using Honeysuckle.Domain.Entities;

namespace Chagoi.Pos.Core.Entity.Goods

{

    [Table("a2")]

    [SuppressMessage("ReSharper", "InconsistentNaming")]

    public class GoodsInfo : Entity<int>

    {

        [Column("c1")]

        public string Name { get; set; }

        /// <summary>

        /// 商品条码

        /// </summary>

        [Column("c2")]

        [StringLength()]

        [Index]

        public string goods_barcode { get; set; }

        /// <summary>

        /// 简称

        /// </summary>

        [Column("c3")]

        [StringLength()]

        [Index("fdafd")]

        public string goods_short_name { get; set; }

        [Column("c4")]

        [StringLength()]

        [Required]

        public string hello { get; set; }

        [Column("c5")]

        public DateTime UpdateTime { get; set; }

    }

}

using Castle.Core.Logging;

using Chagoi.Pos.Core.Entity.Device;

using Chagoi.Pos.Core.Entity.Goods;

using Chagoi.Pos.Core.Entity.Order;

using Chagoi.Pos.Core.Entity.Pay;

using Chagoi.Pos.Core.Entity.Users;

using Honeysuckle.Dependency;

using System;

using System.Collections.Generic;

using System.ComponentModel.DataAnnotations;

using System.ComponentModel.DataAnnotations.Schema;

using System.Configuration;

using System.Data;

using System.Data.SQLite;

using System.Diagnostics;

using System.Linq;

using System.Text;

namespace Chagoi.Pos.EntityFramework.Seed

{

    public static class SeedHelper

    {

        private static readonly string DbPath;

        private static ILogger _logger;

        static SeedHelper()

        {

            DbPath = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;

        }

        public static void SeedHostDb(IIocResolver iocResolver, ILogger logger)

        {

            _logger = logger;

            SeedHostDb();

        }

        private static void SeedHostDb()

        {

            var tobeCreateTables = new List<Type>()

            {

                typeof(IotDevice),

                typeof(GoodsInfo),

                typeof(GoodsCategory),

                typeof(GoodsBrand),

                typeof(AlipayParameter),

                typeof(WxpayParameter),

                typeof(AppUser),

                typeof(Order)

            };

            _logger.Info("#region 开始执行数据库的创建或结构调整");

            var stopwatch = new Stopwatch();

            stopwatch.Start();

            using (var connection = new SQLiteConnection(DbPath))

            {

                connection.Open();

                using (var transaction = connection.BeginTransaction())

                {

                    try

                    {

                        foreach (var entity in tobeCreateTables)

                        {

                            //表名称,这里使用混淆表明,要求必须输入

                            var tableAttribute = entity.GetCustomAttributes(typeof(TableAttribute), true).FirstOrDefault();

                            if (null == tableAttribute)

                            {

                                throw new ArgumentNullException(nameof(TableAttribute));

                            }

                            var tableName = ((TableAttribute)tableAttribute).Name;

                            //填充数据库中标的原始结构

                            var tableStructureCommandText = $"PRAGMA table_info(\"{tableName}\");";

                            var sqLiteCommand = new SQLiteCommand(connection) { CommandText = tableStructureCommandText };

                            var sQLiteDataAdapter = new SQLiteDataAdapter(sqLiteCommand);

                            var tableStructure = new DataTable(tableName);

                            sQLiteDataAdapter.Fill(tableStructure);

                            var hasTable = tableStructure.Rows.Count > ;

                            if (!hasTable)

                            {

                                _logger.Info($"表{tableName}不存在,开始执行创建过程...");

                                //执行创建表的SQL语句

                                var createTableSql = GenerateCreateTableCommandText(entity, tableName);

                                sqLiteCommand.CommandText = createTableSql;

                                _logger.Debug(sqLiteCommand.CommandText);

                                sqLiteCommand.ExecuteNonQuery();

                                _logger.Info($"表{tableName}的创建语句已执行.");

                            }

                            else

                            {

                                _logger.Info($"表{tableName}已经存在,开始执行差异对比过程...");

                                var updateTableSql = GenerateUpdateTableCommandText(entity, tableStructure);

                                if (string.IsNullOrEmpty(updateTableSql))

                                {

                                    _logger.Info($"表{tableName}结构无差异,无需调整.");

                                    continue;

                                }

                                sqLiteCommand.CommandText = updateTableSql;

                                _logger.Debug(sqLiteCommand.CommandText);

                                var nonQuery = sqLiteCommand.ExecuteNonQuery();

                                _logger.Info($"表{tableName}的调整语句已执行,返回结果:{nonQuery}");

                            }

                        }

                        transaction.Commit();

                    }

                    catch (Exception ex)

                    {

                        _logger.Error("执行数据库的创建或结构调整过程发生异常",ex);

                        transaction.Rollback();

                    }

                }

                connection.Dispose();

            }

            stopwatch.Stop();

            var elapsedMilliseconds = stopwatch.ElapsedMilliseconds;

            _logger.Info($"#endregion 执行数据库的创建或结构调整过程完毕,耗时{elapsedMilliseconds}毫秒");

        }

        /// <summary>

        /// 生成创建表的sql命令,包含索引的创建

        /// </summary>

        /// <param name="entityType">实体类型</param>

        /// <param name="tableName">表的名称</param>

        /// <returns></returns>

        private static string GenerateCreateTableCommandText(Type entityType, string tableName)

        {

            var properties = entityType.GetProperties();

            //索引创建语句,根据实体的Index特性来生成

            var createTableIndexCommandTexts = new List<string>();

            var createTableCommandText = $"CREATE TABLE \"{tableName}\" (";

            foreach (var property in properties)

            {

                //列名,优先取特性名称

                var columnName = GetColumnName(property);

                //是否可空

                var notNull = IsNotNull(property);

                //字段类型

                var dbType = GetDbType(property);

                createTableCommandText += $"\"{columnName}\" {dbType} {notNull},";

                //处理索引

                var indexAttributeObj = property.GetCustomAttributes(typeof(IndexAttribute), true).FirstOrDefault();

                if (null != indexAttributeObj)

                {

                    var indexAttribute = ((IndexAttribute)indexAttributeObj);

                    //是否是唯一索引

                    var unique = indexAttribute.IsUnique ? "UNIQUE" : "";

                    //索引名称

                    var indexName = string.IsNullOrEmpty(indexAttribute.Name) ? $"Index_{columnName}" : indexAttribute.Name;

                    //创建索引的语句

                    var indexSql = $"CREATE {unique} INDEX \"{indexName}\" ON \"{tableName}\"(\"{columnName}\" ASC); ";

                    createTableIndexCommandTexts.Add(indexSql);

                }

            }

            createTableCommandText += "PRIMARY KEY (\"Id\" ASC)";

            createTableCommandText += ");";

            var allCommandText = new StringBuilder(createTableCommandText);

            foreach (var createTableIndexCommandText in createTableIndexCommandTexts)

            {

                allCommandText.Append(createTableIndexCommandText);

            }

            return allCommandText.ToString();

        }

        /// <summary>

        /// 生成更新表的sql命令,只支持新增列

        /// 不支持添加索引列,不支持删除列

        /// 新增的列必须允许为空

        /// </summary>

        /// <param name="entityType">实体类型</param>

        /// <param name="dataTable">数据中的表结构</param>

        /// <returns></returns>

        private static string GenerateUpdateTableCommandText(Type entityType, DataTable dataTable)

        {

            var properties = entityType.GetProperties();

            var alreadyCreatedColumnNames = dataTable.AsEnumerable().Select(t => t.Field<string>("name").ToString()).ToArray();

            var addColumnCommandTexts = new List<string>();

            foreach (var property in properties)

            {

                var colName = GetColumnName(property);

                var alreadyCreated = alreadyCreatedColumnNames.Any(m => m == colName);

                if (!alreadyCreated)

                {

                    var dbType = GetDbType(property);

                    var addColumnCommandText = $"ALTER  TABLE   \"{dataTable.TableName}\" ADD COLUMN  \"{colName}\" {dbType} NULL;";

                    addColumnCommandTexts.Add(addColumnCommandText);

                }

            }

            var allCommandText = new StringBuilder();

            foreach (var addColumnCommand in addColumnCommandTexts)

            {

                allCommandText.Append(addColumnCommand);

            }

            return allCommandText.ToString();

        }

        #region 基础函数

        /// <summary>

        /// 获取数据类型

        /// </summary>

        /// <param name="property"></param>

        /// <returns></returns>

        private static string GetDbType(System.Reflection.PropertyInfo property)

        {

            string dbType;

            if (property.PropertyType.FullName != null)

            {

                var propertyTypeFullName = property.PropertyType.FullName.ToUpper();

                switch (propertyTypeFullName)

                {

                    case "SYSTEM.BOOLEAN":

                        dbType = "BOOLEAN";

                        break;

                    case "SYSTEM.INT32":

                    case "SYSTEM.INT64":

                        dbType = "INTEGER";

                        break;

                    case "SYSTEM.STRING":

                        //是否标记了最大长度

                        var stringLengthAttributeObj = property.GetCustomAttributes(typeof(StringLengthAttribute), true).FirstOrDefault();

                        if (stringLengthAttributeObj != null)

                        {

                            var length = ((StringLengthAttribute)stringLengthAttributeObj).MaximumLength;

                            dbType = $"VARCHAR({length})";

                        }

                        else

                        {

                            dbType = "TEXT";

                        }

                        break;

                    case "SYSTEM.DATETIME":

                        dbType = "DATETIME";

                        break;

                    default:

                        throw new ArgumentOutOfRangeException(nameof(propertyTypeFullName));

                }

            }

            else

            {

                throw new ArgumentNullException(nameof(property.PropertyType.FullName));

            }

            return dbType;

        }

        /// <summary>

        /// 字段是否可用

        /// </summary>

        /// <param name="property"></param>

        /// <returns></returns>

        private static string IsNotNull(System.Reflection.PropertyInfo property)

        {

            var hasRequiredAttribute = property.GetCustomAttributes(typeof(RequiredAttribute), true).Length > ;

            var notNull = hasRequiredAttribute ? "NOT NULL" : "";

            if (property.Name.ToUpper() == "ID")  //ID始终不能为空

            {

                notNull = "NOT NULL";

            }

            return notNull;

        }

        /// <summary>

        /// 获取列名

        /// </summary>

        /// <param name="property"></param>

        /// <returns></returns>

        private static string GetColumnName(System.Reflection.PropertyInfo property)

        {

            var columnName = property.Name;

            var columnAttributeObj = property.GetCustomAttributes(typeof(ColumnAttribute), true).FirstOrDefault();

            if (null != columnAttributeObj)

            {

                columnName = ((ColumnAttribute)columnAttributeObj).Name;

            }

            return columnName;

        }

        #endregion

    }

}