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

namespace CodeGenerator.Core
{
    public class SqliteDatabase : 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);

            try
            {
                using (IDbConnection conn = new SQLiteConnection(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);
            }

            return dbList;
        }

        public IList<TableInfo> GetTableList(ServerInfo server, string dbName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@" SELECT name FROM sqlite_master WHERE type='table' ORDER BY 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();

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

        public List<ColumnInfo> GetColumnInfoList(ServerInfo server, string dbName, string tableName)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.AppendFormat("SELECT * from {0}", tableName);

                DataTable result = null;


                using (IDataReader dr = ExecuteReader(strSql.ToString(), Config.GetConnectionString(server)))
                {
                    result = dr.GetSchemaTable();

                    List<ColumnInfo> list = new List<ColumnInfo>();

                    foreach (DataRow dataRow in result.Rows)
                    {
                        ColumnInfo columnInfo = new ColumnInfo();
                        columnInfo.Id = (Convert.ToInt32(dataRow["ColumnOrdinal"]) + 1).ToString();
                        columnInfo.Name = dataRow["ColumnName"].ToString();
                        columnInfo.DataType = dataRow["DataTypeName"].ToString();
                        columnInfo.AutoIncrement = Convert.ToBoolean(dataRow["IsAutoIncrement"]) ? "是" : "否";
                        columnInfo.MaxLength = Convert.ToInt32(dataRow["ColumnSize"]);
                        columnInfo.DecimalNum = Convert.ToInt32(string.IsNullOrEmpty(dataRow["NumericScale"].ToString()) ? "0" : dataRow["NumericScale"]);
                        columnInfo.DefaultValue = dataRow["DefaultValue"].ToString();
                        columnInfo.AllowDBNull = Convert.ToBoolean(dataRow["AllowDBNull"]) ? "是" : "否";
                        columnInfo.IsPrimaryKey = Convert.ToBoolean(dataRow["IsKey"]) ? "是" : "否";
                        columnInfo.Comments = "";

                        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)
        {
            return "";
        }

        #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 "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 "bool":
                    name.Alias = isNull == "是" ? "bool?" : "bool";
                    name.Name = isNull == "是" ? "System.Boolean?" : "System.Boolean";
                    name.JdbcType = SqlDbType.Bit.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 "decimal":
                    name.Alias = isNull == "是" ? "decimal?" : "decimal";
                    name.Name = isNull == "是" ? "System.Decimal?" : "System.Decimal";
                    name.JdbcType = SqlDbType.Decimal.ToString();
                    break;
                case "float":
                    name.Alias = isNull == "是" ? "double?" : "double";
                    name.Name = isNull == "是" ? "System.Double?" : "System.Double";
                    name.JdbcType = SqlDbType.Float.ToString();
                    break;
                case "double":
                    name.Alias = isNull == "是" ? "double?" : "double";
                    name.Name = isNull == "是" ? "System.Double?" : "System.Double";
                    name.JdbcType = SqlDbType.Float.ToString();
                    break;
                case "guid":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.VarChar.ToString();
                    break;
                case "identity":
                    name.Alias = isNull == "是" ? "int?" : "int";
                    name.Name = isNull == "是" ? "System.Int64?" : "System.Int64";
                    name.JdbcType = SqlDbType.Int.ToString();
                    break;
                case "int":
                    name.Alias = isNull == "是" ? "int?" : "int";
                    name.Name = isNull == "是" ? "System.Int32?" : "System.Int32";
                    name.JdbcType = SqlDbType.Int.ToString();
                    break;
                case "int8":
                    name.Alias = isNull == "是" ? "byte?" : "byte";
                    name.Name = isNull == "是" ? "System.Byte?" : "System.Byte";
                    name.JdbcType = SqlDbType.TinyInt.ToString();
                    break;
                case "int16":
                    name.Alias = isNull == "是" ? "short?" : "short";
                    name.Name = isNull == "是" ? "System.Int16?" : "System.Int16";
                    name.JdbcType = SqlDbType.SmallInt.ToString();
                    break;
                case "smallint":
                    name.Alias = isNull == "是" ? "short?" : "short";
                    name.Name = isNull == "是" ? "System.Int16?" : "System.Int16";
                    name.JdbcType = SqlDbType.SmallInt.ToString();
                    break;
                case "int32":
                    name.Alias = isNull == "是" ? "int?" : "int";
                    name.Name = isNull == "是" ? "System.Int32?" : "System.Int32";
                    name.JdbcType = SqlDbType.Int.ToString();
                    break;
                case "int64":
                    name.Alias = isNull == "是" ? "int?" : "int";
                    name.Name = isNull == "是" ? "System.Int64?" : "System.Int64";
                    name.JdbcType = SqlDbType.BigInt.ToString();
                    break;
                case "integer":
                    name.Alias = isNull == "是" ? "int?" : "int";
                    name.Name = isNull == "是" ? "System.Int64?" : "System.Int64";
                    name.JdbcType = SqlDbType.BigInt.ToString();
                    break;
                case "long":
                    name.Alias = isNull == "是" ? "int?" : "int";
                    name.Name = isNull == "是" ? "System.Int64?" : "System.Int64";
                    name.JdbcType = SqlDbType.BigInt.ToString();
                    break;
                case "longchar":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.VarChar.ToString();
                    break;
                case "longtext":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.VarChar.ToString();
                    break;
                case "longvarchar":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.VarChar.ToString();
                    break;
                case "memo":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.VarChar.ToString();
                    break;
                case "money":
                    name.Alias = isNull == "是" ? "decimal?" : "decimal";
                    name.Name = isNull == "是" ? "System.Decimal?" : "System.Decimal";
                    name.JdbcType = SqlDbType.Money.ToString();
                    break;
                case "nchar":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.NChar.ToString();
                    break;
                case "ntext":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.NVarChar.ToString();
                    break;
                case "number":
                    name.Alias = isNull == "是" ? "decimal?" : "decimal";
                    name.Name = isNull == "是" ? "System.Decimal?" : "System.Decimal";
                    name.JdbcType = SqlDbType.Decimal.ToString();
                    break;
                case "numeric":
                    name.Alias = isNull == "是" ? "decimal?" : "decimal";
                    name.Name = isNull == "是" ? "System.Decimal?" : "System.Decimal";
                    name.JdbcType = SqlDbType.Decimal.ToString();
                    break;
                case "nvarchar":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.NVarChar.ToString();
                    break;
                case "real":
                    name.Alias = isNull == "是" ? "double?" : "double";
                    name.Name = isNull == "是" ? "System.Double?" : "System.Double";
                    name.JdbcType = SqlDbType.Real.ToString();
                    break;
                case "single":
                    name.Alias = isNull == "是" ? "float?" : "float";
                    name.Name = isNull == "是" ? "System.Single?" : "System.Single";
                    name.JdbcType = SqlDbType.Float.ToString();
                    break;
                case "smalldatetime":
                    name.Alias = isNull == "是" ? "DateTime?" : "DateTime";
                    name.Name = isNull == "是" ? "System.DateTime?" : "System.DateTime";
                    name.JdbcType = SqlDbType.SmallDateTime.ToString();
                    break;
                case "text":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.Text.ToString();
                    break;
                case "time":
                    name.Alias = isNull == "是" ? "DateTime?" : "DateTime";
                    name.Name = isNull == "是" ? "System.DateTime?" : "System.DateTime";
                    name.JdbcType = SqlDbType.DateTime2.ToString();
                    break;
                case "timestamp":
                    name.Alias = isNull == "是" ? "DateTime?" : "DateTime";
                    name.Name = isNull == "是" ? "System.DateTime?" : "System.DateTime";
                    name.JdbcType = SqlDbType.DateTime2.ToString();
                    break;
                case "varchar":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.VarChar.ToString();
                    break;
                case "varchar2":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.VarChar.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 "bit":
                    name.Alias = "Boolean";
                    name.Name = "java.lang.Boolean";
                    name.JdbcType = "BOOLEAN";
                    break;
                case "bigint":
                    name.Alias = "Boolean";
                    name.Name = "java.lang.Boolean";
                    name.JdbcType = "BOOLEAN";
                    break;
                case "bool":
                    name.Alias = "Boolean";
                    name.Name = "java.lang.Boolean";
                    name.JdbcType = "BOOLEAN";
                    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 "decimal":
                    name.Alias = "BigDecimal";
                    name.Name = "java.math.BigDecimal";
                    name.JdbcType = "DECIMAL";
                    break;
                case "float":
                    name.Alias = "Float";
                    name.Name = "java.lang.Float";
                    name.JdbcType = "Float";
                    break;
                case "double":
                    name.Alias = "Double";
                    name.Name = "java.lang.Double";
                    name.JdbcType = "Double";
                    break;
                case "guid":
                    name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "VARCHAR";
                    break;
                case "identity":
                     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 "int8":
                    name.Alias = "Boolean";
                    name.Name = "System.Boolean";
                    name.JdbcType = "BIT";
                    break;
                case "int16":
                   name.Alias = "Integer";
                    name.Name = "java.lang.Integer";
                    name.JdbcType = "INTEGER";
                    break;
                case "smallint":
                    name.Alias = "Integer";
                    name.Name = "java.lang.Integer";
                    name.JdbcType = "INTEGER";
                    break;
                case "int32":
                   name.Alias = "Integer";
                    name.Name = "java.lang.Integer";
                    name.JdbcType = "INTEGER";
                    break;
                case "int64":
                    name.Alias = "BigInteger";
                    name.Name = "ava.math.BigInteger";
                    name.JdbcType = "BIGINT";
                    break;
                case "integer":
                    name.Alias = "BigInteger";
                    name.Name = "java.math.BigInteger";
                    name.JdbcType = "BIGINT";
                    break;
                case "long":
                    name.Alias = "Long";
                    name.Name = "java.lang.Long";
                    name.JdbcType = "LONG";
                    break;
                case "longchar":
                    name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "CHAR";
                    break;
                case "longtext":
                    name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "LONGVARCHAR";
                    break;
                case "longvarchar":
                   name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "LONGVARCHAR";
                    break;
                case "memo":
                    name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "LONGVARCHAR";
                    break;
                case "money":
                    name.Alias = "BigDecimal";
                    name.Name = "java.math.BigDecimal";
                    name.JdbcType = "DECIMAL";
                    break;
                case "nchar":
                    name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "CHAR";
                    break;
                case "ntext":
                    name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "LONGVARCHAR";
                    break;
                case "number":
                    name.Alias = "BigDecimal";
                    name.Name = "java.math.BigDecimal";
                    name.JdbcType = "DECIMAL";
                    break;
                case "numeric":
                    name.Alias = "BigDecimal";
                    name.Name = "java.math.BigDecimal";
                    name.JdbcType = "DECIMAL";
                    break;
                case "nvarchar":
                    name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "LONGVARCHAR";
                    break;
                case "real":
                    name.Alias = "Double";
                    name.Name = "java.lang.Double";
                    name.JdbcType = "Double";
                    break;
                case "single":
                    name.Alias = "Float";
                    name.Name = "java.lang.Float";
                    name.JdbcType = "Float";
                    break;
                case "smalldatetime":
                    name.Alias = "Date";
                    name.Name = "java.util.Date";
                    name.JdbcType = "TIMESTAMP";
                    break;
                case "text":
                    name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "VARCHAR";
                    break;
                case "time":
                    name.Alias = "Date";
                    name.Name = "java.util.Date";
                    name.JdbcType = "TIMESTAMP";
                    break;
                case "timestamp":
                    name.Alias = "Date";
                    name.Name = "java.util.Date";
                    name.JdbcType = "TIMESTAMP";
                    break;
                case "varchar":
                    name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "VARCHAR";
                    break;
                case "varchar2":
                     name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "VARCHAR";
                    break;
            }
            return name;
        }
        #endregion

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