using CodeGenerator.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Dapper;

namespace CodeGenerator.Core
{
    public class SqlServerDatabase : IDatabase
    {
        /// <summary>
        /// 测试数据库链接
        /// </summary>
        /// <param name="server"></param>
        /// <param name="errMessage"></param>
        /// <returns></returns>
        public bool TestConnnect(ServerInfo server, out string errMessage)
        {
            string connectionString = Config.GetConnectionString(server, "master");

            try
            {
                using (IDbConnection conn = new SqlConnection(connectionString))
                {
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    errMessage = string.Empty;
                    return true;
                }
            }
            catch (SqlException err)
            {
                errMessage = err.Message;
                return false;
            }
        }

        public IList<string> GetDatabaseList(ServerInfo server)
        {
            IList<string> dbList = new List<string>();
            if (!string.IsNullOrEmpty(server.DatabaseName))
            {
                dbList.Add(server.DatabaseName);
            }
            else
            {
                string strSql = "select name from sysdatabases";

                using (IDataReader dr = ExecuteReader(strSql.ToString(), Config.GetConnectionString(server, "master")))
                {
                    while (dr.Read())
                    {
                        dbList.Add(dr["name"].ToString());
                    }
                }
            }
            return dbList;
        }

        public IList<TableInfo> GetTableList(ServerInfo server, string dbName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@" SELECT a.name,b.comments FROM sysobjects a 
                         LEFT JOIN(
                         SELECT isnull(g.[value],'') AS comments,a.name FROM sys.tables a  
                         LEFT JOIN sys.extended_properties g ON (a.object_id = g.major_id AND g.minor_id = 0) ) b 
                         ON a.name=b.name
                         WHERE a.xtype='u'
                         ORDER BY a.name");

            using (IDataReader dr = ExecuteReader(strSql.ToString(), Config.GetConnectionString(server, dbName)))
            {
                IList<TableInfo> tbList = new List<TableInfo>();

                while (dr.Read())
                {
                    TableInfo table = new TableInfo();
                    table.Name = dr["name"].ToString();
                    table.Comments = dr["comments"].ToString(); ;

                    tbList.Add(table);
                }
                return tbList;
            }
        }

        public List<ColumnInfo> GetColumnInfoList(ServerInfo server, string dbName, string tableName)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(@"SELECT 
                                 Id              = a.colorder,
                                 Name     = a.name,
                                 AutoIncrement       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '是'else '' end,
                                 IsPrimaryKey       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                                                  SELECT name FROM sysindexes WHERE indid in(
                                                     SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '是' else '' end,
                                 DataType       = b.name,
                                 MaxLength       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
                                 DecimalNum   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
                                 AllowDBNull     = case when a.isnullable=1 then '是'else '' end,
                                 DefaultValue     = isnull(e.text,''),
                                 Comments   = isnull(g.[value],'')
                             FROM syscolumns a
                             left join systypes b on a.xusertype=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  
                             where d.name='{0}'  
                             order by a.id,a.colorder");

                strSql = strSql.Replace("{0}", tableName);

                using (IDataReader dr = ExecuteReader(strSql.ToString(), Config.GetConnectionString(server, dbName)))
                {
                    List<ColumnInfo> list = new List<ColumnInfo>();

                    while (dr.Read())
                    {
                        ColumnInfo columnInfo = new ColumnInfo();
                        columnInfo.Id = dr["Id"].ToString();
                        columnInfo.Name = dr["Name"].ToString();
                        columnInfo.DataType = dr["DataType"].ToString();
                        columnInfo.AutoIncrement = dr["AutoIncrement"].ToString();
                        columnInfo.MaxLength = Convert.ToInt32(dr["MaxLength"]);
                        columnInfo.DecimalNum = Convert.ToInt32(dr["DecimalNum"]);
                        columnInfo.DefaultValue = dr["DefaultValue"].ToString();
                        columnInfo.AllowDBNull = dr["AllowDBNull"].ToString();
                        columnInfo.IsPrimaryKey = dr["IsPrimaryKey"].ToString();
                        columnInfo.Comments = dr["Comments"].ToString();

                        columnInfo.NetType = this.GetNetType(columnInfo.DataType, columnInfo.AllowDBNull);
                        columnInfo.JavaType = this.GetJavaType(columnInfo.DataType);

                        columnInfo.IsNumeric = DataTypeHelper.IsNumeric(columnInfo.NetType.Alias);
                        columnInfo.IsDateTime = DataTypeHelper.IsDateTime(columnInfo.NetType.Alias);
                        columnInfo.IsBoolean = DataTypeHelper.IsBoolean(columnInfo.NetType.Alias);

                        columnInfo.UIControlType = DataTypeHelper.GetUIControlType(columnInfo);

                        list.Add(columnInfo);
                    }
                    return list;
                }

            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("获取表结构信息：", ex);
                return null;
            }
        }


        /// <summary>
        /// 获取表说明
        /// </summary>
        /// <param name="tableName">表明</param>
        /// <returns></returns>
        public string GetTableComments(ServerInfo server, string dbName, string tableName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select isnull(g.[value],'') AS comments ");
            strSql.Append(" from ");
            strSql.Append(" sys.tables a  ");
            strSql.AppendFormat(" left join sys.extended_properties g ");
            strSql.Append(" on (a.object_id = g.major_id AND g.minor_id = 0) ");
            strSql.Append(" where ");
            strSql.AppendFormat(" a.name='{0}'", tableName);

            string result = "";
            using (IDataReader dr = ExecuteReader(strSql.ToString(), Config.GetConnectionString(server)))
            {
                while (dr.Read())
                {
                    if (dr["comments"] != null)
                        result = dr["comments"].ToString();
                }
                return result;
            }
        }

        #region 类型转换


        /// <summary>
        /// Sql类型转C#类型
        /// </summary>
        /// <param name="typeName"></param>
        /// <param name="isNull"></param>
        /// <returns></returns>
        public NameElement GetNetType(string typeName, string isNull)
        {
            NameElement name = new NameElement();
            switch (typeName.Trim().ToLower())
            {
                case "varchar":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.VarChar.ToString();
                    break;
                case "nvarchar":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.NVarChar.ToString();
                    break;
                case "text":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.Text.ToString();
                    break;
                case "ntext":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.NText.ToString();
                    break;
                case "char":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.Char.ToString();
                    break;
                case "nchar":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.NChar.ToString();
                    break;
                case "xml":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.Xml.ToString();
                    break;
                case "bit":
                    name.Alias = isNull == "是" ? "bool?" : "bool";
                    name.Name = isNull == "是" ? "System.Boolean?" : "System.Boolean";
                    name.JdbcType = SqlDbType.Bit.ToString();
                    break;
                case "bigint":
                    name.Alias = isNull == "是" ? "long?" : "long";
                    name.Name = isNull == "是" ? "System.Int64?" : "System.Int64";
                    name.JdbcType = SqlDbType.BigInt.ToString();
                    break;
                case "int":
                    name.Alias = isNull == "是" ? "int?" : "int";
                    name.Name = isNull == "是" ? "System.Int32?" : "System.Int32";
                    name.JdbcType = SqlDbType.Int.ToString();
                    break;
                case "tinyint":
                    name.Alias = isNull == "是" ? "byte?" : "byte";
                    name.Name = isNull == "是" ? "System.Byte?" : "System.Byte";
                    name.JdbcType = SqlDbType.TinyInt.ToString();
                    break;
                case "smallint":
                    name.Alias = isNull == "是" ? "short?" : "short";
                    name.Name = isNull == "是" ? "System.Int16?" : "System.Int16";
                    name.JdbcType = SqlDbType.SmallInt.ToString();
                    break;
                case "smallmoney":
                    name.Alias = isNull == "是" ? "decimal?" : "decimal";
                    name.Name = isNull == "是" ? "System.Decimal?" : "System.Decimal";
                    name.JdbcType = SqlDbType.SmallMoney.ToString();
                    break;
                case "decimal":
                    name.Alias = isNull == "是" ? "decimal?" : "decimal";
                    name.Name = isNull == "是" ? "System.Decimal?" : "System.Decimal";
                    name.JdbcType = SqlDbType.Decimal.ToString();
                    break;
                case "money":
                    name.Alias = isNull == "是" ? "decimal?" : "decimal";
                    name.Name = isNull == "是" ? "System.Decimal?" : "System.Decimal";
                    name.JdbcType = SqlDbType.Money.ToString();
                    break;
                case "real":
                    name.Alias = isNull == "是" ? "float?" : "float";
                    name.Name = isNull == "是" ? "System.Single?" : "System.Single";
                    name.JdbcType = SqlDbType.Real.ToString();
                    break;
                case "float":
                    name.Alias = isNull == "是" ? "double?" : "double";
                    name.Name = isNull == "是" ? "System.Double?" : "System.Double";
                    name.JdbcType = SqlDbType.Float.ToString();
                    break;
                case "date":
                    name.Alias = isNull == "是" ? "DateTime?" : "DateTime";
                    name.Name = isNull == "是" ? "System.DateTime?" : "System.DateTime";
                    name.JdbcType = SqlDbType.Date.ToString();
                    break;
                case "datetime":
                    name.Alias = isNull == "是" ? "DateTime?" : "DateTime";
                    name.Name = isNull == "是" ? "System.DateTime?" : "System.DateTime";
                    name.JdbcType = SqlDbType.DateTime.ToString();
                    break;
                case "datetime2":
                    name.Alias = isNull == "是" ? "DateTime?" : "DateTime";
                    name.Name = isNull == "是" ? "System.DateTime?" : "System.DateTime";
                    name.JdbcType = SqlDbType.DateTime2.ToString();
                    break;
                case "smalldatetime":
                    name.Alias = isNull == "是" ? "DateTime?" : "DateTime";
                    name.Name = isNull == "是" ? "System.DateTime?" : "System.DateTime";
                    name.JdbcType = SqlDbType.SmallDateTime.ToString();
                    break;
            }
            return name;
        }

        /// <summary>
        /// Sql类型转Java类型
        /// </summary>
        /// <param name="typeName"></param>
        /// <param name="isNull"></param>
        /// <returns></returns>
        public NameElement GetJavaType(string typeName)
        {
            NameElement name = new NameElement();
            switch (typeName.Trim().ToLower())
            {
                case "varchar":
                    name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "VARCHAR";
                    break;
                case "nvarchar":
                    name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "NVARCHAR";
                    break;
                case "text":
                    name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "LONGVARCHAR";
                    break;
                case "ntext":
                    name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "LONGVARCHAR";
                    break;
                case "char":
                    name.Alias = "java.lang.String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "CHAR";
                    break;
                case "nchar":
                    name.Alias = "java.lang.String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "NCHAR";
                    break;
                case "xml":
                    name.Alias = "java.lang.String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "LONGVARCHAR";
                    break;
                case "bit":
                    name.Alias = "Boolean";
                    name.Name = "java.lang.Boolean";
                    name.JdbcType = "BOOLEAN";
                    break;
                case "bigint":
                    name.Alias = "Long";
                    name.Name = "java.lang.Long";
                    name.JdbcType = "BIGINT";
                    break;
                case "int":
                    name.Alias = "Integer";
                    name.Name = "java.lang.Integer";
                    name.JdbcType = "INTEGER";
                    break;
                case "tinyint":
                    name.Alias = "Integer";
                    name.Name = "java.lang.Integer";
                    name.JdbcType = "TINYINT";
                    break;
                case "smallint":
                    name.Alias = "Integer";
                    name.Name = "java.lang.Integer";
                    name.JdbcType = "SMALLINT";
                    break;
                case "smallmoney":
                    name.Alias = "BigDecimal";
                    name.Name = "java.math.BigDecimal";
                    name.JdbcType = "DECIMAL";
                    break;
                case "decimal":
                    name.Alias = "BigDecimal";
                    name.Name = "java.math.BigDecimal";
                    name.JdbcType = "DECIMAL";
                    break;
                case "money":
                    name.Alias = "BigDecimal";
                    name.Name = "java.math.BigDecimal";
                    name.JdbcType = "DECIMAL";
                    break;
                case "real":
                    name.Alias = "Float";
                    name.Name = "java.lang.Float";
                    name.JdbcType = "REAL";
                    break;
                case "float":
                    name.Alias = "Double";
                    name.Name = "java.lang.Double";
                    name.JdbcType = "DOUBLE";
                    break;
                case "date":
                    name.Alias = "Date";
                    name.Name = "java.util.Date";
                    name.JdbcType = "DATE";
                    break;
                case "datetime":
                    name.Alias = "Date";
                    name.Name = "java.util.Date";
                    name.JdbcType = "TIMESTAMP";
                    break;
                case "datetime2":
                    name.Alias = "Date";
                    name.Name = "java.util.Date";
                    name.JdbcType = "TIMESTAMP";
                    break;
                case "smalldatetime":
                    name.Alias = "Date";
                    name.Name = "java.util.Date";
                    name.JdbcType = "TIMESTAMP";
                    break;
            }
            return name;
        }
        #endregion

        #region 私有方法
        /// <summary>
        /// 专供获取数据库用
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        private IDataReader ExecuteReader(string strSQL, string connectionString)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            IDbCommand cmd = new SqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
        }
        #endregion
    }
}