using System.Data;
using System.Data.Common;
using System.Text;
using Dm;
using PowerEasy.WebFuture.DataMigrations.Foundation.Log;

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

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

        /// <summary>
        /// 检查链接是否开启。
        /// </summary>
        /// <returns>返回结果。</returns>
        public bool CheckConnection()
        {
            try
            {
                using var connection = new DmConnection(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 (var connection = new DmConnection(this.ConnectionString))
            {
                using (var cmd = new DmCommand(sql, connection))
                {
                    if (parameters != null && parameters.Length > 0)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }

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

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

                adapter.Fill(dt);
            }

            return dt;
        }

        /// <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)
        {
            DmConnection connection = new DmConnection(this.ConnectionString);
            using (DmCommand cmd = new DmCommand(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 object ExecuteScalar(string sql, params DbParameter[] parameters)
        {
            using (DbDataReader reader = this.ExecuteReader(sql, parameters))
            {
                if (reader.Read())
                {
                    // 读取第一个结果并返回
                    return reader.GetValue(0);
                }
                else
                {
                    return null;
                }
            }
        }

        /// <summary>
        /// 批量插入。
        /// </summary>
        /// <param name="datatable">数据库表。</param>
        /// <returns>返回执行是否成功。</returns>
        public bool BatchInsert(DataTable datatable)
        {
            bool flag = false;
            int batchRowCount = 10;
            List<string> columns = new List<string>();
            StringBuilder columnSqlBuilder = new StringBuilder();
            StringBuilder valueSqlBuilder = new StringBuilder();
            DmConnection connection = new DmConnection(this.ConnectionString);
            string sql = string.Empty;
            try
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }

                using (DmTransaction tran = (DmTransaction)connection.BeginTransaction())
                {
                    foreach (DataColumn dc in datatable.Columns)
                    {
                        if (!string.IsNullOrEmpty(dc.ColumnName))
                        {
                            columns.Add("\"" + dc.ColumnName + "\"");
                        }
                    }

                    columnSqlBuilder.AppendFormat("INSERT INTO {0} ({1}) VALUES ", datatable.TableName, string.Join(",", columns));

                    int batchCount = 0;
                    for (int i = 0; i < datatable.Rows.Count; i++)
                    {
                        DataRow dr = datatable.Rows[i];
                        StringBuilder valueBuilder = new StringBuilder();

                        foreach (DataColumn dc in datatable.Columns)
                        {
                            if (!string.IsNullOrEmpty(dc.ColumnName))
                            {
                                if (dr.IsNull(dc.ColumnName))
                                {
                                    valueBuilder.Append("NULL,");
                                }
                                else if (dc.DataType == typeof(int))
                                {
                                    valueBuilder.Append(dr[dc.ColumnName] + ",");
                                }
                                else if (dc.DataType == typeof(bool))
                                {
                                    int isBool = dr[dc.ColumnName].ToString() == "False" ? 0 : 1;
                                    valueBuilder.Append(isBool + ",");
                                }
                                else if (dc.DataType == typeof(DateTime))
                                {
                                    valueBuilder.Append($"'{((DateTime)dr[dc.ColumnName]).ToString("yyyy-MM-dd HH:mm:ss")}',");
                                }
                                else
                                {
                                    valueBuilder.Append($"'{dr[dc.ColumnName].ToString().Replace("'", @"\'")}',");
                                }
                            }
                        }

                        valueSqlBuilder.AppendFormat("({0}),", valueBuilder.ToString().TrimEnd(','));

                        batchCount++;

                        if (batchCount >= batchRowCount || i == datatable.Rows.Count - 1)
                        {
                            string cmdSql = columnSqlBuilder.ToString() + valueSqlBuilder.ToString().TrimEnd(',');
                            sql = cmdSql;
                            using (DmCommand cmd = new DmCommand(cmdSql, connection, tran))
                            {
                                cmd.ExecuteNonQuery();
                            }

                            valueSqlBuilder.Clear();
                            batchCount = 0;
                        }
                    }

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

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

                MessageBox.Show(ex.Message);

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

                flag = false;
            }
            finally
            {
                // 确保连接被关闭
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }

            return flag;
        }
    }
}
