using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Text;
using CSFramework.DB.Common;

namespace CSFramework.DB
{
    /// <summary>
    /// DbCommand命令生成器，根据对象模型(Type)自动生成三种DbCommand,支持MsSQL,MySQL,Oracle
    /// </summary>
    public class DbCommandGenerator
    {
        private IDatabase _db;

        /// <summary>
        /// 构造器
        /// </summary>
        /// <param name="db">CSFramework.DB数据库底层通用接口</param>
        public DbCommandGenerator(IDatabase db)
        {
            _db = db;
        }

        /// <summary>
        /// 创建Insert命令 - 处理实体对象数据
        /// </summary>
        /// <param name="model">对象类型</param>
        /// <param name="ignoreFields">自增字段(或不支持更新的字段)数组</param>
        /// <returns></returns>
        public DbCommand GetInsertCommand<T>(T obj,
            params string[] ignoreFields) where T : new()
        {
            var model = obj.GetType();
            var tableName = GetTableName(model); //获取类对应的表名

            var cmd = _db.CreateCommand("", System.Data.CommandType.Text);

            if (ignoreFields == null || ignoreFields.Length == 0)
                ignoreFields =
                    GetAttributeFields(obj.GetType(), typeof(IgnoreField));

            #region 主表信息==========================

            var strSql = new StringBuilder();
            var str1 = new StringBuilder(); //数据字段
            var str2 = new StringBuilder(); //数据参数

            //利用反射获得属性的所有公共属性
            var pros =
                model.GetProperties(BindingFlags.Instance |
                                    BindingFlags.Public);

            strSql.Append("INSERT INTO " + tableName + "(");

            foreach (var pi in pros)
            {
                //跳过被忽略的字段(不生成SQL脚本)，如：自增字段。必须忽略大小写匹配！
                if (ignoreFields != null &&
                    ignoreFields.Any(e => e.ToLower() == pi.Name.ToLower()))
                    continue;

                //主键字段或IList数据类型不生成Update-SQL脚本
                //IList数据类型不生成Insert-SQL脚本
                if (!IsObjectListType(pi.PropertyType))
                {
                    //判断属性值是否为空
                    str1.Append(pi.Name + ","); //拼接字段
                    str2.Append(_db.ParamSymboName + pi.Name + ","); //声明参数

                    var p = _db.CreateParameter(pi.Name, GetValue(obj, pi));
                    cmd.Parameters.Add(p); //对参数赋值
                }
            }

            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(") VALUES (");
            strSql.Append(str2.ToString().Trim(','));
            strSql.Append("); ");

            #endregion

            cmd.CommandText = CodeSafeHelper.GetSafeSQL(strSql.ToString());
            return cmd;
        }

        /// <summary>
        /// 获取标识有特性(Attribute)的字段
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objType"></param>
        /// <param name="attributeType"></param>
        /// <returns></returns>
        public string[] GetAttributeFields(Type objType, Type attributeType)
        {
            var result = new List<string>();

            //获取所有字段属性名称
            var pros =
                objType.GetProperties(BindingFlags.Instance |
                                      BindingFlags.Public);
            foreach (var pi in pros)
            {
                var list = pi.GetCustomAttributes(attributeType);
                if (list.Count() > 0) result.Add(pi.Name);
            }

            if (result.Count > 0)
                return result.ToArray();
            else
                return null;
        }

        /// <summary>
        /// 创建Insert命令
        /// </summary>
        /// <param name="model">对象类型</param>
        /// <param name="ignoreFields">自增字段(或不支持更新的字段)数组</param>
        /// <returns></returns>
        public DbCommand GetInsertCommand(Type model,
            params string[] ignoreFields)
        {
            var tableName = GetTableName(model); //获取类对应的表名

            var cmd = _db.CreateCommand("", System.Data.CommandType.Text);

            if (ignoreFields == null || ignoreFields.Length == 0)
                ignoreFields = GetAttributeFields(model, typeof(IgnoreField));

            #region 主表信息==========================

            var strSql = new StringBuilder();
            var str1 = new StringBuilder(); //数据字段
            var str2 = new StringBuilder(); //数据参数

            //利用反射获得属性的所有公共属性
            var pros =
                model.GetProperties(BindingFlags.Instance |
                                    BindingFlags.Public);

            strSql.Append("INSERT INTO " + tableName + "(");

            foreach (var pi in pros)
            {
                //跳过被忽略的字段(不生成SQL脚本)，如：自增字段。必须忽略大小写匹配！
                if (ignoreFields != null &&
                    ignoreFields.Any(e => e.ToLower() == pi.Name.ToLower()))
                    continue;

                //主键字段或IList数据类型不生成Update-SQL脚本
                //IList数据类型不生成Insert-SQL脚本
                if (!typeof(IList).IsAssignableFrom(pi.PropertyType))
                {
                    //判断属性值是否为空
                    str1.Append(pi.Name + ","); //拼接字段
                    str2.Append(_db.ParamSymboName + pi.Name + ","); //声明参数
                    cmd.Parameters.Add(_db.CreateParameter(pi.Name,
                        DataTypeConverter.DotNetType2DbType(
                            pi.PropertyType.Name), 0, pi.Name,
                        DBNull.Value)); //对参数赋值
                }
            }

            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(") VALUES (");
            strSql.Append(str2.ToString().Trim(','));
            strSql.Append(") ");

            #endregion

            cmd.CommandText = CodeSafeHelper.GetSafeSQL(strSql.ToString());
            return cmd;
        }

        /// <summary>
        /// 创建Delete命令
        /// </summary>
        /// <param name="model">对象类型</param>
        /// <param name="PKs">主键字段名数组</param>        
        /// <returns></returns>
        public DbCommand GetDeleteCommand(Type model, params string[] PKs)
        {
            if (PKs == null || PKs.Length == 0)
                PKs = GetAttributeFields(model, typeof(KeyField));

            if (PKs == null || PKs.Length == 0)
                throw new Exception("没有指定主键字段！");

            var tableName = GetTableName(model); //获取类对应的表名

            var cmd = _db.CreateCommand("", System.Data.CommandType.Text);
            var PKTypeNames = new Dictionary<string, string>();

            #region 主表信息==========================

            var strSql = new StringBuilder();

            //利用反射获得属性的所有公共属性
            var pros =
                model.GetProperties(BindingFlags.Instance |
                                    BindingFlags.Public);

            strSql.Append("DELETE FROM " + tableName + " WHERE ");

            foreach (var pi in pros)
                if (PKs.Any(e => e.ToLower() == pi.Name.ToLower()))
                    PKTypeNames[pi.Name] = pi.PropertyType.Name;

            //组合主键 WHERE 条件
            foreach (var pk in PKs)
            {
                if (Array.IndexOf(PKs, pk) == 0)
                    strSql.Append(pk + "=" + _db.ParamSymboName + pk); //声明参数
                else
                    strSql.Append(" AND " + pk + "=" + _db.ParamSymboName +
                                  pk); //声明参数

                cmd.Parameters.Add(_db.CreateParameter(pk,
                    DataTypeConverter.DotNetType2DbType(PKTypeNames[pk]), 0, pk,
                    DBNull.Value)); //对参数赋值                
            }

            #endregion

            cmd.CommandText = CodeSafeHelper.GetSafeSQL(strSql.ToString());
            return cmd;
        }


        /// <summary>
        /// 创建Delete命令-处理实体对象数据
        /// </summary>
        /// <param name="model">对象类型</param>
        /// <param name="PKs">主键字段名数组</param>        
        /// <returns></returns>
        public DbCommand GetDeleteCommand<T>(T obj, params string[] PKs)
            where T : new()
        {
            if (PKs == null || PKs.Length == 0)
                PKs = GetAttributeFields(obj.GetType(), typeof(KeyField));

            if (PKs == null || PKs.Length == 0)
                throw new Exception("没有指定主键字段！");

            var model = obj.GetType();
            var tableName = GetTableName(model); //获取类对应的表名

            var cmd = _db.CreateCommand("", System.Data.CommandType.Text);

            var PKTypeNames = new Dictionary<string, string>();
            var PKValues = new Dictionary<string, object>();

            #region 主表信息==========================

            var strSql = new StringBuilder();

            //利用反射获得属性的所有公共属性
            var pros =
                model.GetProperties(BindingFlags.Instance |
                                    BindingFlags.Public);

            strSql.Append("DELETE FROM " + tableName + " WHERE ");

            foreach (var pi in pros)
                if (PKs.Any(e => e.ToLower() == pi.Name.ToLower()))
                {
                    PKTypeNames[pi.Name] = pi.PropertyType.Name;
                    PKValues[pi.Name] = GetValue(obj, pi);
                }

            //组合主键 WHERE 条件
            foreach (var pk in PKs)
            {
                if (Array.IndexOf(PKs, pk) == 0)
                    strSql.Append(pk + "=" + _db.ParamSymboName + pk); //声明参数
                else
                    strSql.Append(" AND " + pk + "=" + _db.ParamSymboName +
                                  pk); //声明参数

                cmd.Parameters.Add(_db.CreateParameter(pk,
                    PKValues[pk])); //对参数赋值                
            }

            #endregion

            cmd.CommandText = CodeSafeHelper.GetSafeSQL(strSql.ToString());
            return cmd;
        }

        /// <summary>
        /// 创建Update命令 - 处理实体对象数据
        /// </summary>
        /// <param name="model">对象类型</param>
        /// <param name="PKs">主键字段名数组</param>
        /// <param name="ignoreFields">自增字段(或不支持更新的字段)数组</param>
        /// <param name="updateFields">指定更新的字段</param>
        /// <returns></returns>
        public DbCommand GetUpdateCommand<T>(T obj,
            string[] PKs = null,
            string[] ignoreFields = null,
            string[] updateFields = null) where T : new()
        {
            if (PKs == null)
                PKs = GetAttributeFields(obj.GetType(), typeof(KeyField));
            if (ignoreFields == null)
                ignoreFields =
                    GetAttributeFields(obj.GetType(), typeof(IgnoreField));
            if (PKs == null || PKs.Length == 0)
                throw new Exception("没有指定主键字段！");

            var model = obj.GetType();
            var tableName = GetTableName(model); //获取类对应的表名

            var cmd = _db.CreateCommand("", System.Data.CommandType.Text);

            var PKTypeNames = new Dictionary<string, string>();
            var PKValues = new Dictionary<string, object>();

            #region 主表信息==========================

            var strSql = new StringBuilder();
            var str1 = new StringBuilder(); //数据字段
            var str2 = new StringBuilder(); //数据参数

            //利用反射获得属性的所有公共属性
            var pros =
                model.GetProperties(BindingFlags.Instance |
                                    BindingFlags.Public);

            strSql.Append("UPDATE " + tableName + " SET ");

            var isPK = false;

            foreach (var pi in pros)
            {
                isPK = PKs.Any(e => e.ToLower() == pi.Name.ToLower());

                //获取主键的数据类型
                if (isPK)
                {
                    PKTypeNames[pi.Name] = pi.PropertyType.Name;
                    PKValues[pi.Name] = GetValue(obj, pi);
                }

                //跳过被忽略的字段(不生成SQL脚本)，如：自增字段。必须忽略大小写匹配！
                if (ignoreFields != null &&
                    ignoreFields.Any(e => e.ToLower() == pi.Name.ToLower()))
                    continue;

                //指定更新的字段
                if (updateFields != null &&
                    updateFields.Any(e => e.ToLower() == pi.Name.ToLower()) ==
                    false) continue;

                //主键字段或IList数据类型不生成Update-SQL脚本
                //IList数据类型不生成Insert-SQL脚本
                if (!isPK && !typeof(IList).IsAssignableFrom(pi.PropertyType))
                {
                    //判断属性值是否为空
                    str1.Append(pi.Name + "=" + _db.ParamSymboName + pi.Name +
                                ","); //声明参数
                    cmd.Parameters.Add(
                        _db.CreateParameter(pi.Name,
                            GetValue(obj, pi))); //对参数赋值
                }
            }

            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(" WHERE ");
            foreach (var pk in PKs)
            {
                if (Array.IndexOf(PKs, pk) == 0)
                    strSql.Append(pk + "=" + _db.ParamSymboName + pk); //声明参数
                else
                    strSql.Append(" AND " + pk + "=" + _db.ParamSymboName +
                                  pk); //声明参数
                cmd.Parameters.Add(_db.CreateParameter(pk,
                    PKValues[pk])); //对参数赋值
            }

            #endregion

            cmd.CommandText = CodeSafeHelper.GetSafeSQL(strSql.ToString());
            return cmd;
        }


        /// <summary>
        /// 创建Update命令
        /// </summary>
        /// <param name="model">对象类型</param>
        /// <param name="PKs">主键字段名数组</param>
        /// <param name="ignoreFields">自增字段(或不支持更新的字段)数组</param>
        /// <param name="updateFields">指定更新的字段</param>
        /// <returns></returns>
        public DbCommand GetUpdateCommand(Type model,
            string[] PKs = null,
            string[] ignoreFields = null,
            string[] updateFields = null)
        {
            if (PKs == null) PKs = GetAttributeFields(model, typeof(KeyField));
            if (ignoreFields == null)
                ignoreFields = GetAttributeFields(model, typeof(IgnoreField));
            if (PKs == null || PKs.Length == 0)
                throw new Exception("没有指定主键字段！");

            var tableName = GetTableName(model); //获取类对应的表名

            var cmd = _db.CreateCommand("", System.Data.CommandType.Text);
            var PKTypeNames = new Dictionary<string, string>();

            #region 主表信息==========================

            var strSql = new StringBuilder();
            var str1 = new StringBuilder(); //数据字段
            var str2 = new StringBuilder(); //数据参数
            //利用反射获得属性的所有公共属性
            var pros =
                model.GetProperties(BindingFlags.Instance |
                                    BindingFlags.Public);

            strSql.Append("UPDATE " + tableName + " SET ");
            var isPK = false;

            foreach (var pi in pros)
            {
                isPK = PKs.Any(e => e.ToLower() == pi.Name.ToLower());

                //获取主键的数据类型
                if (isPK) PKTypeNames[pi.Name] = pi.PropertyType.Name;

                //跳过被忽略的字段(不生成SQL脚本)，如：自增字段。必须忽略大小写匹配！
                if (ignoreFields != null &&
                    ignoreFields.Any(e => e.ToLower() == pi.Name.ToLower()))
                    continue;

                //指定更新的字段
                if (updateFields != null &&
                    updateFields.Any(e => e.ToLower() == pi.Name.ToLower()) ==
                    false) continue;

                //主键字段或IList数据类型不生成Update-SQL脚本
                //IList数据类型不生成Insert-SQL脚本
                if (!isPK && !typeof(IList).IsAssignableFrom(pi.PropertyType))
                {
                    //判断属性值是否为空
                    str1.Append(pi.Name + "=" + _db.ParamSymboName + pi.Name +
                                ","); //声明参数
                    cmd.Parameters.Add(_db.CreateParameter(pi.Name,
                        DataTypeConverter.DotNetType2DbType(
                            pi.PropertyType.Name), 0, pi.Name,
                        DBNull.Value)); //对参数赋值
                }
            }

            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(" WHERE ");
            foreach (var pk in PKs)
            {
                if (Array.IndexOf(PKs, pk) == 0)
                    strSql.Append(pk + "=" + _db.ParamSymboName + pk); //声明参数
                else
                    strSql.Append(" AND " + pk + "=" + _db.ParamSymboName +
                                  pk); //声明参数
                cmd.Parameters.Add(_db.CreateParameter(pk,
                    DataTypeConverter.DotNetType2DbType(PKTypeNames[pk]), 0, pk,
                    DBNull.Value)); //对参数赋值
            }

            #endregion

            cmd.CommandText = CodeSafeHelper.GetSafeSQL(strSql.ToString());
            return cmd;
        }


        /// <summary>
        /// 判断属性的数据类型是对象列表(对象列表不支持生成SQL,必须转换为byte[])
        /// </summary>
        /// <param name="propertyType">属性类型</param>
        /// <returns></returns>
        private bool IsObjectListType(Type propertyType)
        {
            if (propertyType == null) return false;

            //特殊处理，数组类型byte[]允许存储数据库
            if (propertyType == typeof(byte[])) return false;

            return typeof(IList).IsAssignableFrom(propertyType);
        }

        /// <summary>
        /// 获取对象属性的值
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="pi"></param>
        /// <returns></returns>
        private object GetValue(object obj, PropertyInfo pi)
        {
            var o = pi.GetValue(obj, null);

            //转换DBNull,非常重要！！！
            if (o == null) return DBNull.Value;

            if (pi.PropertyType == typeof(DateTime))
                //处理空日期
                if (DateTime.MinValue == DateTime.Parse(o.ToString()))
                    return DBNull.Value;

            return o;
        }


        /// <summary>
        /// 获取类对应的表名
        /// </summary>
        /// <param name="model">实体模型对应的类</param>
        /// <returns></returns>
        public string GetTableName(Type model)
        {
            //从类的特性获取
            var list =
                model.GetCustomAttributes(typeof(TableInfoAttribute), false);
            if (list != null && list.Length > 0)
                return (list[0] as TableInfoAttribute).TableName;
            else
                return model.Name; //类名既表名
        }
    }
}