using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using CSFramework.DB.Common;
using CSFramework.DB.Models;
using Oracle.ManagedDataAccess.Client;

namespace CSFramework.DB
{
    public class DatabaseOracle : IDatabase
    {
        private string _ConnectionString;

        public DatabaseOracle(string connectionString)
        {
            _ConnectionString = connectionString;
        }

        /// <summary>
        /// 当前使用系统的用户编号
        /// </summary>
        public static string CurrentUserID { get; set; }

        public DatabaseType DatabaseType => DatabaseType.Oracle;

        public CommandHelper CreateSqlProc(string spName)
        {
            var sp =
                new CommandHelper(this, spName, CommandType.StoredProcedure);
            sp.AddParamOracleCursor("pkg_return");
            return sp;
        }

        public CommandHelper CreateCommand(string commandText)
        {
            return new CommandHelper(this, commandText, CommandType.Text);
        }

        public int CommandTimeout
        {
            get
            {
                var defaultTimeOut = 30;
                return DatabaseFactory.CommandTimeOut > 0
                    ? DatabaseFactory.CommandTimeOut
                    : defaultTimeOut;
            }
        }

        public int ConnectionTimeout
        {
            get
            {
                var defaultTimeOut = 15;
                return DatabaseFactory.ConnectionTimeOut > 0
                    ? DatabaseFactory.ConnectionTimeOut
                    : defaultTimeOut;
            }
        }

        public string BuildConnectionString(string server, int port,
            string dbName, string uid, string pwd, int timeout = 15)
        {
            //SERVER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=服务器地址)(PORT=端口号)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=数据库名称)));User Id=用户名;Password=密码;Persist Security Info=True;Enlist=true;Max Pool Size=300;Min Pool Size=0;Connection Lifetime=300;
            //const string DEF_SQL_CONNECTION = "SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User Id={3};Password={4};";
            const string DEF_SQL_CONNECTION =
                "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User Id={3};Password={4}";
            var connstr = string.Format(DEF_SQL_CONNECTION, server, port,
                dbName, uid, pwd);
            return connstr;
        }

        public int DefaultPort => 1521;


        /// <summary>
        /// 服务器时间
        /// </summary>
        public DateTime GetServerTime()
        {
            var o = ExecuteScalar("SELECT sysdate FROM dual;");
            return Convert.ToDateTime(o);
        }

        public Type DateTimeType =>
            typeof(Oracle.ManagedDataAccess.Types.OracleDate);

        /// <summary>
        /// 参数符号.如: WHERE Code=:Code
        /// </summary>
        public string ParamSymboName { get; set; } = ":";

        /// <summary>
        /// 创建时间戳参数
        /// </summary>
        /// <param name="parameterName">参数名</param>
        /// <param name="fieldName">字段名</param>
        /// <returns></returns>
        public DbParameter CreateTimestampParameter(string parameterName,
            string fieldName)
        {
            var p = new OracleParameter();
            p.ParameterName = parameterName;
            p.DbType = DbType.DateTime;
            p.Size = 8;
            p.SourceColumn = fieldName;
            return p;
        }

        public string ConnectionString
        {
            get => _ConnectionString;
            set => _ConnectionString = value;
        }

        public void Close(DbConnection connection)
        {
            if (connection != null)
                try
                {
                    if (connection.State != ConnectionState.Closed)
                        connection.Close();
                    connection.Dispose();
                }
                catch
                {
                }
        }

        public DbCommand CreateCommand(string commandText,
            CommandType commandType)
        {
            return new OracleCommand(commandText)
            {
                CommandText = commandText,
                CommandType = commandType,
                CommandTimeout = CommandTimeout
            };
        }

        public DbCommandBuilder CreateCommandBuilder()
        {
            return new OracleCommandBuilder();
        }

        public DbDataAdapter CreateDataAdapter()
        {
            return new OracleDataAdapter();
        }

        public DbParameter CreateParameter(string parameterName,
            object parameterValue)
        {
            parameterName = ParseParamName(parameterName);
            return new OracleParameter
            {
                ParameterName = parameterName, Value = parameterValue,
                IsNullable = true
            };
        }

        public DbParameter CreateParameter(string parameterName,
            DbType parameterType, int size, string sourceColumn,
            object parameterValue)
        {
            parameterName = ParseParamName(parameterName);
            return new OracleParameter
            {
                ParameterName = parameterName, DbType = parameterType,
                Size = size, SourceColumn = sourceColumn,
                Value = parameterValue, IsNullable = true
            };
        }

        public DbConnection CreateConnection()
        {
            return CreateConnection(_ConnectionString);
        }

        public DbConnection CreateConnection(string connectionString)
        {
            DbConnection DBConn = new OracleConnection(connectionString);
            DBConn.Open(); //打开连接
            return DBConn;
        }

        public DbTransaction TransBegin()
        {
            return CreateConnection().BeginTransaction();
        }

        public void TransCommit(DbTransaction trans,
            bool closeConnection = false)
        {
            trans.Commit();
            if (closeConnection) Close(trans.Connection);
        }

        public void TransRollback(DbTransaction trans,
            bool closeConnection = false)
        {
            trans.Rollback();
            if (closeConnection) Close(trans.Connection);
        }

        public int ExecuteCommand(DbCommand cmd)
        {
            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null && cmd.Transaction == null)
                {
                    cmd.Connection =
                        connection = CreateConnection(_ConnectionString);
                    SetContext(cmd.Connection as OracleConnection);
                }

                return cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cmd.Transaction == null) Close(connection);
            }
        }

        public DbDataReader ExecuteReader(string SQL)
        {
            DbCommand cmd = null;
            DbDataReader o = null;
            DbConnection conn = null;
            try
            {
                conn = CreateConnection(_ConnectionString);
                cmd = new OracleCommand();
                cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                SetContext(cmd.Connection as OracleConnection);
                o = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
                Close(conn);
            }

            return o;
        }


        public DbDataReader ExecuteReader(DbCommand cmd)
        {
            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null)
                {
                    cmd.Connection = connection = CreateConnection();
                    SetContext(cmd.Connection as OracleConnection);
                }

                return cmd.ExecuteReader();
            }
            finally
            {
                Close(connection);
            }
        }

        public T ExecuteReader<T>(DbCommand cmd) where T : new()
        {
            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null)
                {
                    cmd.Connection = connection = CreateConnection();
                    SetContext(cmd.Connection as OracleConnection);
                }

                var obj = default(T);
                using (var dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                        obj = DbTools.Convert2Object<T>(dataReader);
                }

                return obj;
            }
            finally
            {
                Close(connection);
            }
        }

        public T ExecuteReader<T>(string SQL) where T : new()
        {
            using (var connection = CreateConnection())
            {
                using (var command = CreateCommand(SQL, CommandType.Text))
                {
                    command.Connection = connection;
                    SetContext(command.Connection as OracleConnection);
                    var obj = default(T);
                    using (var dataReader = command.ExecuteReader())
                    {
                        if (dataReader.Read())
                            obj = DbTools.Convert2Object<T>(dataReader);
                    }

                    return obj;
                }
            }
        }

        public List<T> ExecuteReader<T>(string SQL,
            Func<DbDataReader, T> action) where T : new()
        {
            using (var connection = CreateConnection())
            {
                using (var command = CreateCommand(SQL, CommandType.Text))
                {
                    command.Connection = connection;
                    SetContext(command.Connection as OracleConnection);
                    var list = new List<T>();
                    using (var dataReader = command.ExecuteReader())
                    {
                        while (dataReader.Read())
                            list.Add(action.Invoke(dataReader));
                    }

                    return list;
                }
            }
        }

        public List<T> ExecuteReader<T>(DbCommand cmd,
            Func<DbDataReader, T> action) where T : new()
        {
            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null)
                {
                    cmd.Connection = connection = CreateConnection();
                    SetContext(cmd.Connection as OracleConnection);
                }

                var list = new List<T>();
                using (var dataReader = cmd.ExecuteReader())
                {
                    while (dataReader.Read())
                        list.Add(action.Invoke(dataReader));
                }

                return list;
            }
            finally
            {
                Close(connection);
            }
        }


        public List<T> ExecuteReaderList<T>(string SQL) where T : new()
        {
            DbConnection connection = null;
            DbCommand command = null;
            DbDataReader dataReader = null;

            try
            {
                using (connection = CreateConnection())
                {
                    using (command = CreateCommand(SQL, CommandType.Text))
                    {
                        command.Connection = connection;
                        var list = new List<T>();
                        using (dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                                list.Add(DbTools.Convert2Object<T>(dataReader));
                        }

                        return list;
                    }
                }
            }
            finally
            {
                if (connection != null) connection.Dispose();
                if (command != null) command.Dispose();
                if (dataReader != null) dataReader.Dispose();
            }
        }

        public List<string> GetStringList(string SQL)
        {
            DbConnection connection = null;
            DbCommand command = null;
            DbDataReader dataReader = null;

            try
            {
                using (connection = CreateConnection())
                {
                    using (command = CreateCommand(SQL, CommandType.Text))
                    {
                        command.Connection = connection;
                        var list = new List<string>();
                        using (dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                                list.Add(dataReader.GetValue(0)
                                    .ToString()); //取第1列
                        }

                        return list;
                    }
                }
            }
            finally
            {
                if (connection != null) connection.Dispose();
                if (command != null) command.Dispose();
                if (dataReader != null) dataReader.Dispose();
            }
        }

        public List<string> GetStringList(DbCommand cmd)
        {
            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null)
                {
                    connection = CreateConnection();
                    cmd.Connection = connection;
                }

                var list = new List<string>();
                using (var dataReader = cmd.ExecuteReader())
                {
                    while (dataReader.Read())
                        list.Add(dataReader.GetValue(0).ToString()); //取第1列
                }

                return list;
            }
            finally
            {
                Close(connection);
            }
        }

        public List<T> ExecuteReaderList<T>(DbCommand cmd) where T : new()
        {
            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null)
                    cmd.Connection = connection = CreateConnection();

                var list = new List<T>();
                using (var dataReader = cmd.ExecuteReader())
                {
                    while (dataReader.Read())
                        list.Add(DbTools.Convert2Object<T>(dataReader));
                }

                return list;
            }
            finally
            {
                Close(connection);
            }
        }

        public object ExecuteScalar(string SQL)
        {
            DbCommand cmd = new OracleCommand();
            cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
            cmd.CommandType = CommandType.Text;
            return ExecuteScalar(cmd);
        }

        public object ExecuteScalar(DbCommand cmd)
        {
            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null)
                {
                    cmd.Connection = connection = CreateConnection();
                    SetContext(cmd.Connection as OracleConnection);
                }

                var o = cmd.ExecuteScalar();
                return o;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                Close(connection);
            }
        }

        public int ExecuteSQL(string SQL)
        {
            using (var connection = CreateConnection())
            {
                return ExecuteSQL(SQL, connection);
            }
        }

        /// <summary>
        /// 在事务内提交数据
        /// </summary>
        /// <param name="trans">事务</param>
        /// <param name="SQL">SQL语句</param>
        /// <returns></returns>
        public int ExecuteTrans(DbTransaction trans, string SQL)
        {
            DbCommand cmd = null;
            var i = -1;
            try
            {
                cmd = new OracleCommand();
                cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = trans.Connection;
                cmd.Transaction = trans;

                i = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
            }

            return i;
        }


        public int ExecuteTrans(DbTransaction trans, DbCommand cmd)
        {
            var i = -1;
            try
            {
                cmd.Connection = trans.Connection;
                cmd.Transaction = trans;
                i = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
            }

            return i;
        }

        public DataSet GetDataSet(DbCommand cmd)
        {
            DbDataAdapter adapter = null;
            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null)
                {
                    connection = CreateConnection(_ConnectionString);
                    cmd.Connection = connection;
                    SetContext(cmd.Connection as OracleConnection);
                }

                var ds = new DataSet();
                adapter = new OracleDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(ds);

                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (adapter != null) adapter.Dispose();
                if (cmd != null) cmd.Dispose();
                Close(connection);
            }
        }

        public DataSet GetDataSet(string SQL)
        {
            DbConnection connection = null;
            try
            {
                connection = CreateConnection(_ConnectionString);
                SetContext(connection as OracleConnection);
                return GetDataSet(SQL, connection);
            }
            finally
            {
                Close(connection);
            }
        }

        /// <summary>
        /// 执行带参数的 Sql 语句或存储过程，并返回 DataSet 对象。
        /// </summary>
        /// <param name="SQL">要执行的 Sql 语句或存储过程名等。</param>
        /// <param name="type">CommandType 参数类型，即该命令是 sql 语句，还是存储过程名等。</param>
        /// <param name="paramlist">参数集合。</param>
        /// <returns>DataSet 对象。</returns>
        public virtual DataSet GetDataSet(string SQL, CommandType type,
            IDataParameter[] paramlist)
        {
            DbCommand cmd = null;
            DbDataAdapter adapter = null;
            DbConnection connection = null;
            try
            {
                connection = CreateConnection();

                cmd = CreateCommand(SQL, type);
                cmd.Connection = connection;
                SetContext(cmd.Connection as OracleConnection);

                if (paramlist != null)
                    cmd.Parameters.AddRange(paramlist);

                var ds = new DataSet();
                adapter = CreateDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(ds);

                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (adapter != null) adapter.Dispose();
                if (cmd != null) cmd.Dispose();
                Close(connection);
            }
        }

        public DataTable GetTable(string SQL, string tableName = "")
        {
            var dt = GetDataSet(SQL).Tables[0];
            if (tableName != "") dt.TableName = tableName;
            return dt;
        }

        public DataTable GetTop(int top, string tableName, string fields = "",
            List<DbParameter> where = null, string orderBy = "")
        {
            var cmd = CreateCommand("");

            fields = string.IsNullOrWhiteSpace(fields) ? "*" : fields;
            orderBy = string.IsNullOrEmpty(orderBy)
                ? ""
                : " ORDER BY " + orderBy;

            var whereSQL = "";
            if (where != null && where.Count > 0)
            {
                string pName;
                foreach (var p in where)
                {
                    pName = p.ParameterName;
                    if (pName.IndexOf(ParamSymboName) >= 0) //参数名称带有符号,如：@Code
                        whereSQL = whereSQL +
                                   $" AND {pName.Replace(ParamSymboName, "")}={p.ParameterName}";
                    else
                        whereSQL = whereSQL +
                                   $" AND {p.ParameterName}={ParamSymboName + p.ParameterName}";

                    cmd.Command.Parameters.Add(p);
                }
            }

            whereSQL = string.IsNullOrEmpty(whereSQL)
                ? ""
                : " WHERE ROWNUM<= " + top.ToString() + whereSQL;

            var SQL = $"SELECT {fields} FROM {tableName} {whereSQL} {orderBy}";

            cmd.Command.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
            return GetTable(cmd.Command, tableName);
        }

        public DataTable GetTable(DbCommand cmd, string tableName = "")
        {
            var dt = GetDataSet(cmd).Tables[0];
            if (tableName != "") dt.TableName = tableName;
            return dt;
        }

        public DataRow GetDataRow(string SQL)
        {
            var dt = GetDataSet(SQL).Tables[0];
            return dt.Rows.Count > 0 ? dt.Rows[0] : null;
        }

        /// <summary>
        /// ORACLE数据类型转DbType
        /// </summary>
        /// <param name="sourceType">ORACLE数据类型</param>
        /// <returns></returns>
        public DbType ToDbType(string sourceType)
        {
            return DBDataTypes.GetDbTypeByOracleType(sourceType);
        }

        public Type ToNetType(string sourceType)
        {
            return DBDataTypes.GetNetTypeByOracle(sourceType);
        }

        public DataTable meta_GetTableNames(string dbName = "")
        {
            throw new NotImplementedException("方法未实现！");
        }

        public DataTable meta_GetTableStru(string tableName)
        {
            throw new NotImplementedException("方法未实现！");
        }

        public DataTable meta_GetDatabase()
        {
            throw new NotImplementedException("方法未实现！");
        }

        public List<MetaDBNames> meta_GetDatabaseList()
        {
            throw new NotImplementedException("方法未实现！");
        }

        public List<MetaTableNames> meta_GetTableNamesList(string dbName = "")
        {
            throw new NotImplementedException("方法未实现！");
        }

        public List<MetaTableStructure> meta_GetTableStruList(string tableName)
        {
            throw new NotImplementedException("方法未实现！");
        }

        /// <summary>
        /// 返回包含参数符号的参数名称,比如：@Code, :Code, ?p_Code
        /// </summary>
        /// <param name="paramName"></param>
        /// <returns></returns>
        public string ParseParamName(string paramName)
        {
            if (string.IsNullOrEmpty(ParamSymboName) ||
                string.IsNullOrEmpty(paramName))
            {
                return paramName;
            }
            else
            {
                if (paramName.IndexOf(ParamSymboName) == 0) //首字母包含
                    return paramName;
                else
                    return ParamSymboName + paramName; //添加参数符号
            }
        }

        public List<MetaStoreProcedure> meta_GetStoreProcList(
            string dbName = "")
        {
            var sql =
                $"select object_name as name,type from all_procedures WHERE type IN ('PROCEDURE','FUNCTION');";

            CodeSafeHelper.IsCheckSQM = false;
            var result = new List<MetaStoreProcedure>();
            var sp = CreateCommand(sql);
            var dt = GetTable(sp.Command);
            foreach (DataRow R in dt.Rows)
            {
                if (R["type"].ToString().Trim().ToUpper() == "PROCEDURE")
                    result.Add(new MetaStoreProcedure
                    {
                        Type = MetaStoreProcedureType.StoreProcedure,
                        Name = R["name"].ToString()
                    });
                if (R["type"].ToString().Trim().ToUpper() == "FUNCTION")
                    result.Add(new MetaStoreProcedure
                    {
                        Type = MetaStoreProcedureType.Function,
                        Name = R["name"].ToString()
                    });
            }

            return result;
        }

        /// <summary>
        /// 获取当前表最新(最大)的自增字段值
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public int GetMaxID(string tableName)
        {
            ////mssql写法
            //string sql = $"select ident_current('{tableName}');";
            //var o = ExecuteScalar(sql);
            //return o == DBNull.Value || o == null ? 0 : Convert.ToInt32(o);
            throw new Exception("方法未实现！");
        }

        public bool UpdateDataSet(DataTable ds, string tableName, string KEY)
        {
            //先删除原表数据
            //var sql = "";
            //var ErrorMsg = "";
            //foreach (DataRow dr in ds.Rows)
            //{
            //    sql = string.Format(@"delete from {0} where {1} in ('{2}')", tableName, KEY, dr[KEY]);
            //    break;
            //}

            //var a = ExecuteSQL(sql);
            //var colMapping = new SqlBulkCopyColumnMapping[ds.Columns.Count];
            //for (var i = 0; i < ds.Columns.Count; i++)
            //{
            //    colMapping[i] = new SqlBulkCopyColumnMapping(ds.Columns[i].ColumnName, ds.Columns[i].ColumnName);
            //}

            //DataTableToSQLServer(ds, _ConnectionString, tableName, colMapping, ref ErrorMsg);
            return true;
        }


        public bool InstDataSet(DataTable ds, string tableName)
        {
            //先删除原表数据
            //var ErrorMsg = "";
            //var colMapping = new SqlBulkCopyColumnMapping[ds.Columns.Count];
            //for (var i = 0; i < ds.Columns.Count; i++)
            //{
            //    colMapping[i] = new SqlBulkCopyColumnMapping(ds.Columns[i].ColumnName, ds.Columns[i].ColumnName);
            //}

            //DataTableToSQLServer(ds, _ConnectionString, tableName, colMapping, ref ErrorMsg);
            return true;
        }

        public int ExecuteSQL(string SQL, DbConnection conn)
        {
            DbCommand cmd = null;
            var i = -1;
            try
            {
                cmd = new OracleCommand();
                cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                i = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
            }

            return i;
        }

        public DataSet GetDataSet(string SQL, DbConnection conn)
        {
            DbCommand cmd = null;
            DbDataAdapter adapter = null;
            try
            {
                cmd = new OracleCommand();
                cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;

                var ds = new DataSet();
                adapter = new OracleDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (adapter != null) adapter.Dispose();
                if (cmd != null) cmd.Dispose();
            }
        }

        /// <summary>
        /// Oracle内部权限机制控制用户数据查询
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        public int SetContext(OracleConnection connection)
        {
            try
            {
                var cmd = new OracleCommand("sp_SetContext");
                cmd.Connection = connection;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("In_Userid", CurrentUserID);
                var iValue = cmd.ExecuteNonQuery();
                return iValue;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #region

        protected AdapterRowUpdatingEvent _MyRowUpdatingEvent = null;

        public DbDataAdapter CreateDataAdapter(
            AdapterRowUpdatingEvent eventHandler)
        {
            _MyRowUpdatingEvent = eventHandler;

            var adp = new OracleDataAdapter();
            adp.RowUpdating += Adp_RowUpdating;

            return adp;
        }

        private void Adp_RowUpdating(object sender,
            OracleRowUpdatingEventArgs e)
        {
            if (_MyRowUpdatingEvent != null) _MyRowUpdatingEvent(sender, e);
        }

        #endregion
    }
}