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

namespace CSFramework.DB
{
    /// <summary>
    /// CSFramework通用数据框架-对象实体，支持MsSQL,MySQL,Oracle三种数据库
    /// </summary>
    public class DbFramework
    {
        /// <summary>
        /// 当前事务
        /// </summary>
        protected DbTransaction _CurrentTrans = null;

        protected IDatabase _db;

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

        /// <summary>
        /// 构造器
        /// </summary>
        /// <param name="db">CSFramework.DB数据库底层通用接口</param>
        /// <param name="tran">事务</param>
        public DbFramework(IDatabase db, DbTransaction tran = null)
        {
            _db = db;
            _CurrentTrans = tran;
        }

        /// <summary>
        /// 启动事务机制
        /// </summary>
        private void BeginTransaction()
        {
            if (_CurrentTrans == null)
                _CurrentTrans = _db.TransBegin();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        private void CommitTransaction()
        {
            try
            {
                if (_CurrentTrans != null)
                {
                    _CurrentTrans.Commit();
                    _CurrentTrans.Dispose();
                    _CurrentTrans = null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        private void RollbackTransaction()
        {
            try
            {
                if (_CurrentTrans != null)
                {
                    _CurrentTrans.Rollback();
                    _CurrentTrans.Dispose();
                    _CurrentTrans = null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 查询数据，返回对象列表
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <returns></returns>
        public List<T> Select<T>(string sql) where T : new()
        {
            return _db.ExecuteReaderList<T>(sql);
        }

        /// <summary>
        /// 获取当前模型所有数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> Select<T>() where T : new()
        {
            var sql = $"SELECT * FROM {typeof(T).Name}";
            return _db.ExecuteReaderList<T>(sql);
        }

        public T Select<T>(string keyFieldName, object keyValue) where T : new()
        {
            var sql =
                $"SELECT * FROM {typeof(T).Name} WHERE {keyFieldName}={_db.ParamSymboName + keyFieldName}";
            var cmd = _db.CreateCommand(sql);
            cmd.AddParam(keyFieldName).Value = keyValue;
            return _db.ExecuteReader<T>(cmd.Command);
        }

        /// <summary>
        /// 查询数据，返回对象列表
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="cmd">命令</param>
        /// <returns></returns>
        public List<T> Select<T>(DbCommand cmd) where T : new()
        {
            return _db.ExecuteReaderList<T>(cmd);
        }

        /// <summary>
        /// 提交单表DataTable
        /// </summary>
        /// <param name="dt">DataTable对象</param>
        /// <param name="orm">DataTable对应的实体类</param>
        /// <param name="PKs">主键字段数组</param>
        /// <param name="ignoreFields">自增字段(或不能更新的字段)数组</param>
        /// <param name="updateFields">指定更新的字段</param>
        /// <returns></returns>
        public int Update(DataTable dt, Type orm,
            string[] PKs,
            string[] ignoreFields = null,
            string[] updateFields = null)
        {
            var count = 0;
            try
            {
                BeginTransaction();

                var generator = new DbCommandGenerator(_db);
                var adp = _db.CreateDataAdapter();

                adp.UpdateCommand =
                    generator.GetUpdateCommand(orm, PKs, ignoreFields,
                        updateFields);
                adp.InsertCommand =
                    generator.GetInsertCommand(orm, ignoreFields);
                adp.DeleteCommand = generator.GetDeleteCommand(orm, PKs);

                adp.UpdateCommand.Transaction = _CurrentTrans;
                adp.InsertCommand.Transaction = _CurrentTrans;
                adp.DeleteCommand.Transaction = _CurrentTrans;

                adp.UpdateCommand.Connection = _CurrentTrans.Connection;
                adp.InsertCommand.Connection = _CurrentTrans.Connection;
                adp.DeleteCommand.Connection = _CurrentTrans.Connection;

                adp.AcceptChangesDuringUpdate = false;
                count += adp.Update(dt);
                adp.Dispose();

                CommitTransaction();

                return count;
            }
            catch (DBConcurrencyException ex)
            {
                RollbackTransaction(); //回滚事务                
                throw new Exception(
                    "并发操作！其他用户已更新了源数据！Event:DbDataUpdate.Update()\r\n\r\n" +
                    ex.Message);
            }
            catch (Exception ex)
            {
                RollbackTransaction(); //回滚事务                                
                throw new Exception(
                    "更新数据发生错误！Event:DbDataUpdate。Update(),Table:" +
                    dt.TableName + "\r\n\r\n" + ex.Message);
            }
        }

        /// <summary>
        /// 提交Dataset多表数据
        /// </summary>
        /// <param name="ds">数据集</param>
        /// <param name="orms">ORM与表结构参数定义</param>
        /// <returns></returns>
        public int Update(DataSet ds, List<DbDataUpdateORM> orms)
        {
            if (ds.Tables.Count != orms.Count)
                throw new Exception("数据表与对象模型的数目不一致！");

            var generator = new DbCommandGenerator(_db);

            var mCurrentTable = "";
            DbDataUpdateORM mORM;
            var count = 0;

            try
            {
                BeginTransaction();

                foreach (DataTable dt in ds.Tables)
                {
                    mCurrentTable = dt.TableName;
                    mORM = orms[ds.Tables.IndexOf(dt)];

                    var adp = _db.CreateDataAdapter();
                    adp.UpdateCommand =
                        generator.GetUpdateCommand(mORM.ObjectType, mORM.PKs,
                            mORM.IgnoreFields);
                    adp.InsertCommand =
                        generator.GetInsertCommand(mORM.ObjectType,
                            mORM.IgnoreFields);
                    adp.DeleteCommand =
                        generator.GetDeleteCommand(mORM.ObjectType, mORM.PKs);

                    adp.UpdateCommand.Transaction = _CurrentTrans;
                    adp.InsertCommand.Transaction = _CurrentTrans;
                    adp.DeleteCommand.Transaction = _CurrentTrans;

                    adp.UpdateCommand.Connection = _CurrentTrans.Connection;
                    adp.InsertCommand.Connection = _CurrentTrans.Connection;
                    adp.DeleteCommand.Connection = _CurrentTrans.Connection;

                    adp.AcceptChangesDuringUpdate = false;
                    count += adp.Update(dt);
                    adp.Dispose();
                }

                CommitTransaction();

                return count;
            }
            catch (DBConcurrencyException ex)
            {
                RollbackTransaction(); //回滚事务                
                throw new Exception(
                    "并发操作！其他用户已更新了源数据！Event:DbDataUpdate.Update()\r\n\r\n" +
                    ex.Message);
            }
            catch (Exception ex)
            {
                RollbackTransaction(); //回滚事务                                
                throw new Exception("更新数据发生错误！Event:Update(),Table:" +
                                    mCurrentTable + "\r\n\r\n" + ex.Message);
            }
        }

        /// <summary>
        /// 新增对象
        /// </summary>
        /// <param name="obj">对象实例</param>
        /// <param name="ignoreFields">自增字段(或不支持更新的字段)数组</param>
        /// <returns></returns>
        public bool AddObject<T>(T obj, params string[] ignoreFields)
            where T : new()
        {
            var count = 0;
            using (var conn = _db.CreateConnection())
            {
                try
                {
                    var generator = new DbCommandGenerator(_db);
                    var cmd = generator.GetInsertCommand<T>(obj, ignoreFields);
                    cmd.Connection = conn;
                    cmd.Transaction = _CurrentTrans;
                    var i = _db.ExecuteCommand(cmd);
                    if (i > 0) count += i;
                    return count > 0;
                }
                catch (Exception ex)
                {
                    throw new Exception("更新数据发生错误！Event:Add()" + "\r\n\r\n" +
                                        ex.Message);
                }
            }
        }

        /// <summary>
        /// 新增对象
        /// </summary>
        /// <param name="obj">对象实例</param>
        /// <param name="ignoreFields">自增字段(或不支持更新的字段)数组</param>
        /// <param name="isid">返回的最大自增字段值</param>
        /// <returns></returns>
        public bool AddObject<T>(T obj, string[] ignoreFields, out int isid)
            where T : new()
        {
            isid = 0;
            var count = 0;
            using (var conn = _db.CreateConnection())
            {
                try
                {
                    var generator = new DbCommandGenerator(_db);
                    var cmd = generator.GetInsertCommand<T>(obj, ignoreFields);
                    var tableName =
                        generator.GetTableName(obj.GetType()); //获取类对应的表名
                    cmd.Connection = conn;
                    cmd.Transaction = _CurrentTrans;
                    var i = _db.ExecuteCommand(cmd);
                    if (i > 0)
                    {
                        count += i;
                        isid = _db.GetMaxID(tableName); //获取最大的isid
                    }

                    return count > 0;
                }
                catch (Exception ex)
                {
                    throw new Exception("更新数据发生错误！Event:Add()" + "\r\n\r\n" +
                                        ex.Message);
                }
            }
        }

        /// <summary>
        /// 新增对象列表
        /// </summary>
        /// <param name="list">对象实例列表</param>
        /// <param name="ignoreFields">自增字段(或不支持更新的字段)数组</param>
        /// <returns></returns>
        public int AddList<T>(List<T> list, params string[] ignoreFields)
            where T : new()
        {
            var generator = new DbCommandGenerator(_db);
            var count = 0;
            BeginTransaction();
            try
            {
                foreach (var obj in list)
                {
                    var cmd = generator.GetInsertCommand<T>(obj, ignoreFields);
                    cmd.Transaction = _CurrentTrans;
                    cmd.Connection = _CurrentTrans.Connection;
                    var i = _db.ExecuteCommand(cmd);
                    if (i > 0) count += i;
                }

                CommitTransaction();
                return count;
            }
            catch (Exception ex)
            {
                RollbackTransaction();
                throw new Exception("更新数据发生错误！Event:Add()" + "\r\n\r\n" +
                                    ex.Message);
            }
        }

        /// <summary>
        /// 批量更新对象列表数据
        /// </summary>
        /// <param name="list">对象实例列表</param>
        /// <param name="PKs">主键字段名数组</param>
        /// <param name="ignoreFields">自增字段(或不支持更新的字段)数组</param>
        /// <returns></returns>
        public int UpdateList<T>(List<T> list,
            string[] PKs = null,
            string[] ignoreFields = null,
            string[] updateFields = null) where T : new()
        {
            var generator = new DbCommandGenerator(_db);
            var count = 0;
            try
            {
                BeginTransaction();
                foreach (var obj in list)
                {
                    var cmd = generator.GetUpdateCommand<T>(obj, PKs,
                        ignoreFields, updateFields);
                    cmd.Transaction = _CurrentTrans;
                    cmd.Connection = _CurrentTrans.Connection;
                    var i = _db.ExecuteCommand(cmd);
                    if (i > 0) count += i;
                }

                CommitTransaction();
                return count;
            }
            catch (Exception ex)
            {
                RollbackTransaction();
                throw new Exception("更新数据发生错误！Event:Add()" + "\r\n\r\n" +
                                    ex.Message);
            }
        }


        /// <summary>
        /// 修改对象,更新单个对象数据
        /// </summary>
        /// <param name="obj">对象实例</param>
        /// <param name="PKs">主键字段名数组</param>
        /// <param name="ignoreFields">自增字段(或不支持更新的字段)数组</param>
        /// <returns></returns>
        public bool UpdateObject<T>(T obj,
            string[] PKs = null,
            string[] ignoreFields = null,
            string[] updateFields = null) where T : new()
        {
            var count = 0;
            using (var conn = _db.CreateConnection())
            {
                try
                {
                    var generator = new DbCommandGenerator(_db);
                    var cmd = generator.GetUpdateCommand<T>(obj, PKs,
                        ignoreFields, updateFields);
                    cmd.Connection = conn;
                    cmd.Transaction = _CurrentTrans;
                    var i = _db.ExecuteCommand(cmd);
                    if (i > 0) count += i;
                    return count > 0;
                }
                catch (Exception ex)
                {
                    throw new Exception("更新数据发生错误！Event:Add()" + "\r\n\r\n" +
                                        ex.Message);
                }
            }
        }

        /// <summary>
        /// 批量删除数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">对象实例列表</param>
        /// <param name="PKs">主键字段名数组</param>
        /// <returns></returns>
        public int DeleteList<T>(List<T> list, params string[] PKs)
            where T : new()
        {
            var generator = new DbCommandGenerator(_db);
            var count = 0;
            try
            {
                BeginTransaction();
                foreach (var obj in list)
                {
                    var cmd = generator.GetDeleteCommand<T>(obj, PKs);
                    cmd.Transaction = _CurrentTrans;
                    cmd.Connection = _CurrentTrans.Connection;
                    var i = _db.ExecuteCommand(cmd);
                    if (i > 0) count += i;
                }

                CommitTransaction();
                return count;
            }
            catch (Exception ex)
            {
                RollbackTransaction();
                throw new Exception("更新数据发生错误！Event:Add()" + "\r\n\r\n" +
                                    ex.Message);
            }
        }

        /// <summary>
        /// 删除对象
        /// </summary>
        /// <param name="obj">对象实例</param>
        /// <param name="PKs">主键字段名数组</param>
        /// <returns></returns>
        public bool DeleteObject<T>(T obj, params string[] PKs) where T : new()
        {
            var count = 0;
            using (var conn = _db.CreateConnection())
            {
                try
                {
                    var generator = new DbCommandGenerator(_db);
                    var cmd = generator.GetDeleteCommand<T>(obj, PKs);
                    cmd.Connection = conn;
                    cmd.Transaction = _CurrentTrans;
                    var i = _db.ExecuteCommand(cmd);
                    if (i > 0) count += i;
                    return count > 0;
                }
                catch (Exception ex)
                {
                    throw new Exception("更新数据发生错误！Event:Add()" + "\r\n\r\n" +
                                        ex.Message);
                }
            }
        }
    }

    /// <summary>
    /// 旧的名称:DbDataUpdate, 请使用DbFramework
    /// </summary>
    public class DbDataUpdate : DbFramework
    {
        /// <summary>
        /// 构造器
        /// </summary>
        /// <param name="db">CSFramework.DB数据库底层通用接口</param>
        public DbDataUpdate(IDatabase db) : base(db)
        {
        }

        /// <summary>
        /// 构造器
        /// </summary>
        /// <param name="db">CSFramework.DB数据库底层通用接口</param>
        /// <param name="tran">事务</param>
        public DbDataUpdate(IDatabase db, DbTransaction tran = null) : base(db,
            tran)
        {
        }
    }
}