using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace CSFramework.DB
{
    /// <summary>
    /// 自定义SqlCommand的基类，组合SqlCommand对象.
    /// </summary>
    public class CommandHelper
    {
        protected DbCommand _cmd = null;
        private IDatabase _db = null;

        private string _paramPrefix;

        private Dictionary<string, DbParameter> _Params =
            new Dictionary<string, DbParameter>();

        /// <summary>
        /// 参数前缀符号，如：@,?,p_,:
        /// </summary>
        public string ParamPrefix
        {
            get => _paramPrefix;
            set => _paramPrefix = value;
        }

        /// <summary>
        /// SqlCommand对象
        /// </summary>
        public DbCommand Command => _cmd;

        /// <summary>
        /// SQL命令的参数
        /// </summary>
        public DbParameterCollection Params => _cmd.Parameters;

        /// <summary>
        /// 清除参数
        /// </summary>
        public void ClearParam()
        {
            _cmd.Parameters.Clear();
        }

        /// <summary>
        /// 返回包含参数符号的参数名称，如：@Code
        /// </summary>
        /// <param name="paramName"></param>
        /// <returns></returns>
        private string ParseParamName(string paramName)
        {
            //调用具体数据库实例拼接产生
            if (_db != null)
            {
                return _db.ParseParamName(paramName);
            }
            else
            {
                if (string.IsNullOrEmpty(_paramPrefix) ||
                    string.IsNullOrEmpty(paramName))
                {
                    return paramName;
                }
                else
                {
                    if (paramName.IndexOf(_paramPrefix) == 0) //首字母包含
                        return paramName;
                    else
                        return _paramPrefix + paramName; //添加参数符号
                }
            }
        }

        /// <summary>
        /// 删除参数
        /// </summary>
        /// <param name="parameterName">参数名称</param>
        public void RemoveParam(string parameterName)
        {
            _cmd.Parameters.RemoveAt(ParseParamName(parameterName));
        }

        /// <summary>
        /// 增加参数
        /// </summary>
        /// <param name="parameterName">参数名称,不带符号</param>
        /// <param name="dbType">DbType类型</param>
        /// <param name="size">长度</param>
        /// <param name="value">值</param>
        public DbParameter AddParam(string parameterName, DbType dbType,
            int size, object value,
            ParameterDirection direction = ParameterDirection.Input)
        {
            DbParameter p;

            if (_db != null)
                p = _db.CreateParameter(parameterName, value);
            else
                p = _cmd.CreateParameter();

            //重新赋值，自动添加参数符号
            p.ParameterName = ParseParamName(parameterName);

            p.DbType = dbType;
            p.Size = size;
            p.Value = value == null ? DBNull.Value : value;
            p.Direction = direction;
            _cmd.Parameters.Add(p);
            return p;
        }

        public DbParameter AddParam(string parameterName, object value,
            ParameterDirection direction = ParameterDirection.Input)
        {
            DbParameter p;

            if (_db != null)
                p = _db.CreateParameter(parameterName, value);
            else
                p = _cmd.CreateParameter();

            //重新赋值，自动添加参数符号
            p.ParameterName = ParseParamName(parameterName);
            p.Value = value == null ? DBNull.Value : value;
            p.Direction = direction;
            _cmd.Parameters.Add(p);
            return p;
        }

        public DbParameter AddParam(string parameterName,
            ParameterDirection direction = ParameterDirection.Input)
        {
            return AddParam(parameterName, null, direction);
        }

        /// <summary>
        /// 增加参数
        /// </summary>
        /// <param name="parameterName">参数名称,带@符号</param>
        /// <param name="sqlDbType">SqlDbType类型</param>
        /// <param name="value">值</param>
        /// <param name="direction">输入/输出类型</param>
        public DbParameter AddParam(string parameterName, DbType dbType,
            object value,
            ParameterDirection direction = ParameterDirection.Input)
        {
            DbParameter p;

            if (_db != null)
                p = _db.CreateParameter(parameterName, value);
            else
                p = _cmd.CreateParameter();

            //重新赋值，自动添加参数符号
            p.ParameterName = ParseParamName(parameterName);
            p.DbType = dbType;
            p.Value = value == null ? DBNull.Value : value;
            p.Direction = direction;
            _cmd.Parameters.Add(p);
            return p;
        }

        /// <summary>
        /// 添加Oracle游标参数，用于返回DataTable/DataSet
        /// </summary>
        /// <param name="parameterName"></param>
        /// <returns></returns>
        public DbParameter AddParamOracleCursor(string parameterName = "")
        {
            var ParaName = string.IsNullOrEmpty(parameterName)
                ? "PCur"
                : parameterName;
            var refCursorType =
                Enum.Parse(
                    ((dynamic)_cmd.CreateParameter()).OracleDbType.GetType(),
                    "RefCursor");
            var param = _cmd.CreateParameter();
            param.ParameterName = ParaName;
            param.Direction = ParameterDirection.Output;
            var piInstance = param.GetType().GetProperty("OracleDbType");
            piInstance.SetValue(param, refCursorType, null);
            _cmd.Parameters.Add(param);
            return param;
        }

        /// <summary>
        ///  给SqlCommand.Parameters赋值
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="value">数值</param>
        public void SetValue(string paramName, object value)
        {
            paramName = ParseParamName(paramName);

            var p = _cmd.Parameters[paramName];
            if (p == null) throw new Exception(paramName + "参数不存在！");
            p.Value = value == null ? DBNull.Value : value;
        }

        /// <summary>
        /// 获取参数的值
        /// </summary>
        /// <param name="paramName"></param>
        /// <returns></returns>
        public object GetValue(string paramName)
        {
            paramName = ParseParamName(paramName);

            var p = _cmd.Parameters[paramName];
            if (p == null) throw new Exception(paramName + "参数不存在！");
            return p.Value;
        }

        public DbParameter GetParameter(string paramName)
        {
            paramName = ParseParamName(paramName);

            if (_cmd.Parameters.IndexOf(paramName) >= 0)
                return _cmd.Parameters[paramName];
            else
                return null;
        }

        #region 构造器

        /// <summary>
        /// 构造器
        /// </summary>
        /// <param name="db">数据库接口</param>
        /// <param name="sql">SQL命令</param>
        /// <param name="type">SQL命令类型</param>
        public CommandHelper(IDatabase db, string sql, CommandType type)
        {
            _db = db;
            _cmd = db.CreateCommand(sql, type);
            _paramPrefix = db.ParamSymboName;
        }

        /// <summary>
        /// 构造器
        /// </summary>
        /// <param name="cmd">Command对象</param>
        /// <param name="paramPrefix">参数符号，如：@,?,p_,:</param>
        public CommandHelper(DbCommand cmd, string paramPrefix)
        {
            _cmd = cmd;
            _paramPrefix = paramPrefix;

            if (cmd == null) throw new Exception("参数必须为DbCommand派生类的实例！");
        }

        #endregion
    }
}