using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using PowerEasy.WebFuture.DataMigrations.Foundation.Log;

namespace PowerEasy.WebFuture.DataMigrations.Foundation.Provider
{
    /// <summary>
    /// SQl数据库提供者。
    /// </summary>
    public class SqlServerProvider : IDatabaseProvider
    {
        /// <summary>
        /// 构造函数。
        /// </summary>
        /// <param name="connectionString">数据库链接字符串。</param>
        public SqlServerProvider(string connectionString)
        {
            this.ConnectionString = connectionString;
        }

        /// <summary>
        /// 数据库链接字符串。
        /// </summary>
        public string ConnectionString { get; set; }

        /// <summary>
        /// 检查链接是否开启。
        /// </summary>
        /// <returns>返回结果。</returns>
        public bool CheckConnection()
        {
            try
            {
                using var connection = new SqlConnection(this.ConnectionString);
                connection.Open();
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 执行非查询命令。
        /// </summary>
        /// <param name="sql">sql语句。</param>
        /// <param name="parameters">参数。</param>
        /// <returns>返回执行条数。</returns>
        public int ExecuteNonQuery(string sql, params DbParameter[] parameters)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.CommandTimeout = 3600;
                    if (parameters != null && parameters.Length > 0)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }

                    con.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 执行查询并返回单个值。
        /// </summary>
        /// <param name="sql">sql语句。</param>
        /// <param name="parameters">参数。</param>
        /// <returns>返回对象。</returns>
        public object ExecuteScalar(string sql, params DbParameter[] parameters)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    if (parameters != null && parameters.Length > 0)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }

                    con.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 获取数据库表第一条数据的sql。
        /// </summary>
        /// <param name="tableName">数据库表名字。</param>
        /// <returns>返回对应sql。</returns>
        public string GetOneDataByTableName(string tableName)
        {
            var sql = $"SELECT TOP 1 * FROM {tableName};";
            return sql;
        }

        /// <summary>
        /// 根据表名，站点id，排序字段名字获取数据库表第一条数据的sql。
        /// </summary>
        /// <param name="tableName">数据库表名字。</param>
        /// <param name="siteId">站点id。</param>
        /// <param name="orderName">排序字段名字。</param>
        /// <returns>返回对应sql。</returns>
        public string GetOneDataByTableNameAndSiteId(string tableName, int siteId, string orderName)
        {
            var sql = $"SELECT TOP 1 * FROM {tableName} WHERE SiteId={siteId} ORDER BY {orderName} DESC";
            return sql;
        }

        /// <summary>
        /// 根据表名，站点id，排序字段名字获取数据库表全部数据的sql。
        /// </summary>
        /// <param name="tableName">数据库表名字。</param>
        /// <param name="siteId">站点id。</param>
        /// <param name="orderName">排序字段名字。</param>
        /// <returns>返回对应sql。</returns>
        public string GetAllDataByTableNameAndSiteId(string tableName, int siteId, string orderName)
        {
            var sql = $"SELECT * FROM {tableName} WHERE SiteId={siteId} ORDER BY [{orderName}] DESC";
            return sql;
        }

        /// <summary>
        /// 根据表名，节点id，获取数据库表全部数据的sql。
        /// </summary>
        /// <param name="tableName">数据库表名字。</param>
        /// <param name="nodeId">节点id。</param>
        /// <returns>返回对应sql。</returns>
        public string GetAllDataByTableNameAndNodeId(string tableName, int nodeId)
        {
            var sql = $"SELECT * FROM {tableName} WHERE NodeId = {nodeId}";
            return sql;
        }

        /// <summary>
        /// 根据表名，排序字段名字，获取数据库表第一条数据的sql。
        /// </summary>
        /// <param name="tableName">数据库表名字。</param>
        /// <param name="orderName">排序字段名字。</param>
        /// <returns>返回对应sql。</returns>
        public string GetOneDataByTableNameAndOrder(string tableName, string orderName)
        {
            var sql = $"SELECT TOP 1 * FROM {tableName} ORDER BY {orderName} DESC";
            return sql;
        }

        /// <summary>
        /// 根据站点Id，节点左值，节点右值，获取节点表的一级节点和二级节点的sql。
        /// </summary>
        /// <param name="siteId">数据库表名字。</param>
        /// <param name="left">节点左值。</param>
        /// <param name="right">节点右值。</param>
        /// <returns>返回对应sql。</returns>
        public string GetRootNodeIDAndMasterNodeID(int siteId, int left, int right)
        {
            var sql = $"select NodeId from PE_ContentManage_Node where SiteId= {siteId}  and [left] < {left} and [right] > {right} order by[Left],[Right]";
            return sql;
        }

        /// <summary>
        /// 根据站点Id，获取节点表的第一条Right的sql。
        /// </summary>
        /// <param name="siteId">数据库表名字。</param>
        /// <returns>返回对应sql。</returns>
        public string GetRightBySiteId(int siteId)
        {
            var sql = "SELECT TOP 1 [Right] FROM PE_ContentManage_Node WHERE Siteid=" + siteId + " ORDER BY [Right] DESC";
            return sql;
        }

        /// <summary>
        /// 更新原节点的Left跟Right值。
        /// </summary>
        /// <param name="siteId">站点Id。</param>
        /// <param name="sourceNodeCount">源数据节点数量。</param>
        /// <param name="right">节点右值。</param>
        /// <param name="left">节点左值。</param>
        /// <param name="number">获取对应第几条sql。</param>
        /// <returns>返回对应sql。</returns>
        public string UpdateLeftAndRight(int siteId, int sourceNodeCount, int right, int left, int number = 0)
        {
            var sql = $"Update PE_ContentManage_Node Set [Right] = [Right]+{sourceNodeCount * 2},[Left] = [Left]+{sourceNodeCount * 2} where [Left]> {right} and SiteId={siteId};Update PE_ContentManage_Node Set [Right] = [Right]+{sourceNodeCount * 2} where [Left]<={left} and [right]>={right} and SiteId={siteId};Update PE_ContentManage_Node Set [Right] = [Right]+{sourceNodeCount * 2},[Left] = [Left]+{sourceNodeCount * 2} where [Left]>{left} and [right]<{right} and SiteId={siteId}; ";
            return sql;
        }

        /// <summary>
        /// 执行查询读取器。
        /// </summary>
        /// <param name="sql">sql语句。</param>
        /// <param name="parameters">参数。</param>
        /// <returns>返回DbDataReader。</returns>
        public DbDataReader ExecuteReader(string sql, params DbParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(this.ConnectionString);
            using (SqlCommand cmd = new SqlCommand(sql, connection))
            {
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                }

                try
                {
                    connection.Open();
                    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                catch (Exception)
                {
                    connection.Close();
                    connection.Dispose();
                    throw;
                }
            }
        }

        /// <summary>
        /// 执行查询并返回数据表。
        /// </summary>
        /// <param name="sql">sql语句。</param>
        /// <param name="parameters">参数。</param>
        /// <returns>返回数据表。</returns>
        public DataTable ExecuteDataTable(string sql, params DbParameter[] parameters)
        {
            DataTable dataTable = new DataTable();
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, this.ConnectionString))
            {
                if (parameters != null && parameters.Length > 0)
                {
                    adapter.SelectCommand.Parameters.AddRange(parameters);
                }

                adapter.Fill(dataTable);
            }

            return dataTable;
        }

        /// <summary>
        /// 批量插入。
        /// </summary>
        /// <param name="datatable">数据库表。</param>
        /// <returns>返回执行是否成功。</returns>
        public bool BatchInsert(DataTable datatable)
        {
            // 当将此选项设置为 true 时，SqlBulkCopy 将保留源数据中的标识列的值，并将这些值插入目标表的标识列中。这意味着如果源数据包含自增长的标识列，插入到目标表时会保持这些标识值不变。
            int batchRowCount = 1000;
            bool keepIdentity = false;
            bool flag = false;
            try
            {
                string currentTableName = datatable.TableName;
                int tableRowCount = datatable.Rows.Count;
                var sqlBulkptions = keepIdentity ? SqlBulkCopyOptions.KeepIdentity : SqlBulkCopyOptions.UseInternalTransaction;
                using (var bulkCopy = new SqlBulkCopy(this.ConnectionString, sqlBulkptions))
                {
                    bulkCopy.NotifyAfter = tableRowCount < batchRowCount ? tableRowCount : batchRowCount;
                    bulkCopy.DestinationTableName = datatable.TableName;
                    bulkCopy.BatchSize = batchRowCount;
                    bulkCopy.BulkCopyTimeout = 30000;
                    bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(this.BulkCopy_SqlRowsCopied);
                    bulkCopy.WriteToServer(datatable);
                }

                datatable.Clear();
                datatable.Dispose();
                flag = true;
            }
            catch (Exception ae)
            {
                LogHelper.TextBox.Invoke(new EventHandler(delegate
                {
                    LogHelper.TextBox.Text += "*******************/*数据迁移出现异常，请查看日志*/******************* \r\n\r\n";
                    LogHelper.TextBox.Text += "********************/*出错异常*/*********************** \r\n\r\n";
                    LogHelper.TextBox.Text += ae;

                    // 确保在文本更新后，滚动到最新的内容行。
                    LogHelper.TextBox.SelectionStart = LogHelper.TextBox.Text.Length;
                    LogHelper.TextBox.ScrollToCaret();
                }));

                MessageBox.Show(ae.Message);

                // 出错则写入日志。
                LogHelper.WriteLog(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + @"
                                        -----------------------异常信息---------------------
                                        升级表" + datatable.TableName + @"发生异常
                                        异常详细信息：\r\n" + ae.Message + @"
                                        堆栈：" + ae.StackTrace + @"
                                        -----------------------------------------------------/\r\n");

                flag = false;
            }

            return flag;
        }

        /// <summary>
        /// 单次批量处理完后触发。
        /// </summary>
        /// <param name="sender">对象。</param>
        /// <param name="e">参数。</param>
        private void BulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            ////if (backgroundWorker.CancellationPending)
            ////{
            ////    m_DoWorkEventArgs.Cancel = true;
            ////    e.Abort = true;
            ////    return;
            ////}

            ////finishNumber += tableRowCount;
            ////backgroundWorker.ReportProgress(20);
        }
    }
}
