using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;

namespace DeviceITisation
{
    public class MySqlCommand
    {
        /// <summary>
        /// 连接字符串：指定 SQL Server 实例和数据库(实际会读取dll.config中的值来更新连接字符串)
        /// </summary>
        public static string ConnectionString = "Data Source=10.126.69.225 ;Initial Catalog=EngDB;User ID=app_eng;Password=Pa55wd4ENG@202205#";

        /// <summary>
        /// 判断指定列是否存在某值
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">指定列名</param>
        /// <param name="value">指定值</param>
        /// <param name="connectionString"></param>
        /// <returns>值存在True</returns>
        public static bool IsExist(string tableName, string columnName, string value, string connectionString = "")
        {
            if (string.IsNullOrEmpty(connectionString))
                connectionString = ConnectionString;

            // SQL 查询语句，检查 ProcessName 列是否存在指定的值
            string sqlQuery = string.Format("SELECT CASE WHEN EXISTS (SELECT 1 FROM [{0}] " +
                "WHERE [{1}] = @Value) THEN 1 ELSE 0 END", tableName, columnName);

            // 创建 SQL 连接
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                // 创建 SQL 命令
                using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                {
                    // 添加参数，防止 SQL 注入
                    command.Parameters.AddWithValue("@Value", value);

                    // 打开连接
                    connection.Open();

                    // 执行查询并获取结果
                    int result = (int)command.ExecuteScalar();

                    // 如果 result == 1，表示存在该值；否则不存在
                    if (result == 1)
                        return true;
                    else
                        return false;
                }
            }
        }

        /// <summary>
        /// 判断指定表是否存在
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="connectionString"></param>
        /// <returns>值存在True</returns>
        public static bool IsExistTable(string tableName, string connectionString = "")
        {
            if (string.IsNullOrEmpty(connectionString))
                connectionString = ConnectionString;

            // SQL 查询语句，检查 ProcessName 列是否存在指定的值
            string sqlQuery = $@"
            SELECT COUNT(*)
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME = @TableName";

            // 创建 SQL 连接
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                // 创建 SQL 命令
                using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                {
                    // 添加表名参数
                    command.Parameters.AddWithValue("@TableName", tableName);

                    // 打开连接
                    connection.Open();

                    // 执行查询并获取结果
                    int result = (int)command.ExecuteScalar();

                    // 如果 result > 0，表示存在该表；否则不存在
                    if (result > 0)
                        return true;
                    else
                        return false;
                }
            }
        }

        /// <summary>
        /// 重命名表
        /// </summary>
        /// <param name="oldTableName"></param>
        /// <param name="newTableName"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static bool RenameTable(string oldTableName, string newTableName, string connectionString = "")
        {
            if (string.IsNullOrEmpty(connectionString))
                connectionString = ConnectionString;

            if (!IsExistTable(oldTableName))
                return false;

            // SQL 查询语句，检查 ProcessName 列是否存在指定的值
            string sqlQuery = $"EXEC sp_rename '{oldTableName}', '{newTableName}'"; ;

            // 创建 SQL 连接
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                // 创建 SQL 命令
                using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                {
                    // 打开连接
                    connection.Open();

                    // 执行查询并获取结果
                    int result = (int)command.ExecuteNonQuery();

                    // 如果 result == -1，表示重命名成功
                    if (result == -1)
                        return true;
                    else
                        return false;
                }
            }
        }

        /// <summary>
        /// 判断指定表是否存在
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="connectionString"></param>
        /// <returns>值存在True</returns>
        public static bool DeleteTable(string tableName, string connectionString = "")
        {
            if (string.IsNullOrEmpty(connectionString))
                connectionString = ConnectionString;

            // SQL 查询语句，检查 ProcessName 列是否存在指定的值
            string sqlQuery = $"DROP TABLE IF EXISTS [{tableName}]";

            // 创建 SQL 连接
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                // 创建 SQL 命令
                using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                {
                    // 打开连接
                    connection.Open();

                    // 执行查询并获取结果
                    int result = (int)command.ExecuteNonQuery();

                    // 如果 result = -1，表示存在该表移除成功
                    if (result == -1)
                        return true;
                    else
                        return false;
                }
            }
        }


        /// <summary>
        /// 获取所需的值通过索引指定列的指定值定位到所在行的指定列
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="indexName">指定列名</param>
        /// <param name="indexValue">指定列值</param>
        /// <param name="columnName">所需值的列名</param>
        /// <param name="connectionString"></param>
        /// <returns>所需列值</returns>
        public static string FindValueFromIndexName(string tableName, string indexName, string indexValue, string columnName, string connectionString = "")
        {
            try
            {
                if (string.IsNullOrEmpty(connectionString))
                    connectionString = ConnectionString;

                // SQL 查询语句，检查 ProcessName 列是否存在指定的值
                string sqlQuery = $"SELECT {columnName} FROM [{tableName}] WHERE {indexName} = @IndexValue";
                string resultValue = string.Empty;

                // 连接数据库并执行查询
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                    {
                        // 添加参数以防止 SQL 注入
                        command.Parameters.AddWithValue("@IndexValue", indexValue);

                        // 打开连接
                        connection.Open();

                        // 执行查询并获取结果
                        var result = command.ExecuteScalar();

                        if (result != null)
                        {
                            resultValue = result.ToString();
                        }
                    }
                }
                return resultValue;
            }
            catch { return string.Empty; }
        }

        /// <summary>
        /// 通用插入方法
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnNames">列名数组</param>
        /// <param name="columnValues">列值数组</param>
        /// <returns>受影响的行数</returns>
        public static bool AddRow(string tableName, string[] columnNames, string[] keyColumns = null, params object[] columnValues)
        {
            if (columnNames.Length != columnValues.Length)
            {
                Debug.WriteLine("列名和列值的数量必须相同");
                return false;
            }

            // 使用事务确保操作的原子性
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();

                try
                {
                    // 1. 检查记录是否存在
                    bool recordExists = CheckRecordExists(connection, transaction, tableName, keyColumns, columnNames, columnValues);

                    //// 2. 根据检查结果执行更新或插入
                    if (recordExists)
                        UpdateRecord(connection, transaction, tableName, columnNames, columnValues, keyColumns);
                    else
                        InsertRecord(connection, transaction, tableName, columnNames, columnValues);

                    transaction.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    Debug.WriteLine($"执行插入/更新操作时出错: {ex.Message}");
                    return false;
                }
            }
        }

        /// <summary>
        /// 检查记录是否存在
        /// </summary>
        private static bool CheckRecordExists(SqlConnection connection, SqlTransaction transaction,
                                            string tableName, string[] keyColumns,
                                            string[] allColumnNames, object[] allColumnValues)
        {
            string whereClause = string.Join(" AND ", keyColumns.Select(col => $"{col} = @{col}").ToArray());
            string sql = $"SELECT COUNT(1) FROM {tableName} WITH (UPDLOCK, SERIALIZABLE) WHERE {whereClause}";

            using (SqlCommand command = new SqlCommand(sql, connection, transaction))
            {
                // 只添加键列参数
                foreach (var keyCol in keyColumns)
                {
                    int index = Array.IndexOf(allColumnNames, keyCol);
                    if (index >= 0)
                        command.Parameters.AddWithValue("@" + keyCol, allColumnValues[index] ?? DBNull.Value);
                }

                int count = (int)command.ExecuteScalar();
                return count > 0;
            }
        }

        public static bool CheckRecordExists(string tableName, string[] keyColumns,
                                    string[] allColumnNames, params object[] allColumnValues)
        {
            string whereClause = string.Join(" AND ", keyColumns.Select(col => $"{col} = @{col}").ToArray());
            string sql = $"SELECT COUNT(1) FROM {tableName} WITH (UPDLOCK, SERIALIZABLE) WHERE {whereClause}";

            // 使用事务确保操作的原子性
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();

                using (SqlCommand command = new SqlCommand(sql, connection, transaction))
                {
                    // 只添加键列参数
                    foreach (var keyCol in keyColumns)
                    {
                        int index = Array.IndexOf(allColumnNames, keyCol);
                        if (index >= 0)
                            command.Parameters.AddWithValue("@" + keyCol, allColumnValues[index] ?? DBNull.Value);
                    }

                    int count = (int)command.ExecuteScalar();
                    return count > 0;
                }
            }
        }

        /// <summary>
        /// 更新记录
        /// </summary>
        private static void UpdateRecord(SqlConnection connection, SqlTransaction transaction,
                                       string tableName, string[] columnNames, object[] columnValues,
                                       string[] keyColumns)
        {
            // 只更新非键列
            var updateColumns = columnNames.Except(keyColumns).ToList();
            if (updateColumns.Count == 0)
            {
                Debug.WriteLine("没有需要更新的列");
                return;
            }

            string setClause = string.Join(", ", updateColumns.Select(col => $"{col} = @{col}").ToArray());
            string whereClause = string.Join(" AND ", keyColumns.Select(col => $"{col} = @{col}").ToArray());
            string sql = $"UPDATE {tableName} SET {setClause} WHERE {whereClause}";

            using (SqlCommand command = new SqlCommand(sql, connection, transaction))
            {
                // 添加所有参数
                for (int i = 0; i < columnNames.Length; i++)
                {
                    command.Parameters.AddWithValue("@" + columnNames[i], columnValues[i] ?? DBNull.Value);
                }

                int rowsAffected = command.ExecuteNonQuery();
                Debug.WriteLine($"更新了 {rowsAffected} 行记录");
            }
        }

        /// <summary>
        /// 插入记录
        /// </summary>
        private static void InsertRecord(SqlConnection connection, SqlTransaction transaction,
                                       string tableName, string[] columnNames, object[] columnValues)
        {
            string columns = string.Join(", ", columnNames);
            string parameters = string.Join(", ", columnNames.Select(col => "@" + col).ToArray());
            string sql = $"INSERT INTO {tableName} ({columns}) VALUES ({parameters})";

            using (SqlCommand command = new SqlCommand(sql, connection, transaction))
            {
                // 添加所有参数
                for (int i = 0; i < columnNames.Length; i++)
                {
                    command.Parameters.AddWithValue("@" + columnNames[i], columnValues[i]);
                }

                int rowsAffected = command.ExecuteNonQuery();
                Debug.WriteLine($"插入了 {rowsAffected} 行记录");
            }
        }

        /// <summary>
        /// 删除指定行通过索引指定列的指定值
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="indexName">指定列名</param>
        /// <param name="indexValue">指定列值</param>
        /// <param name="connectionString"></param>
        /// <returns>删除成功True</returns>
        public static bool DeleteRowFromIndexName(string tableName, string indexName, string indexValue, string connectionString = "")
        {
            try
            {
                if (string.IsNullOrEmpty(connectionString))
                    connectionString = ConnectionString;
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();

                    // 删除指定行
                    string deleteQuery = string.Format("DELETE FROM [{0}] WHERE {1} = @indexValue", tableName, indexName);
                    using (SqlCommand cmd = new SqlCommand(deleteQuery, conn))
                    {
                        cmd.Parameters.AddWithValue("@indexValue", indexValue);
                        cmd.ExecuteNonQuery();
                    }

                    // 更新剩余行的 step
                    string updateQuery = string.Format(@"
                WITH OrderedSteps AS (
                    SELECT {1}, ROW_NUMBER() OVER (ORDER BY {1}) AS NewValue
                    FROM [{0}]
                )
                UPDATE [{0}]
                SET {1} = NewValue
                FROM [{0}] s
                JOIN OrderedSteps o ON s.{1} = o.{1};
            ", tableName, indexName);

                    using (SqlCommand cmd = new SqlCommand(updateQuery, conn))
                    {
                        cmd.ExecuteNonQuery();
                    }
                    return true;
                }
            }
            catch { return false; }
        }

        /// <summary>
        /// 交换指定行的所有值通过索引指定列的指定值
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="swapName">列名</param>
        /// <param name="swapValue1">交换的列值</param>
        /// <param name="swapValue2">被交换的列值</param>
        /// <param name="connectionString"></param>
        /// <returns>交换成功True</returns>
        public static bool SwapRowFromIndexName(string tableName, string swapName, string swapValue1, string swapValue2, string connectionString = "")
        {

            if (string.IsNullOrEmpty(connectionString))
                connectionString = ConnectionString;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                // 开始事务
                SqlTransaction transaction = conn.BeginTransaction();
                try
                {
                    // 交换 step 为 3 和 4 的行
                    string swapQuery = string.Format(@"
                        BEGIN TRANSACTION;
                        UPDATE [{0}]
                        SET {1} = @SwapTempValue
                        WHERE {1} = @SwapValue1;

                        UPDATE [{0}]
                        SET {1} = @SwapValue1
                        WHERE {1} = @SwapValue2;

                        UPDATE [{0}]
                        SET {1} = @SwapValue2
                        WHERE {1} = @SwapTempValue;
                        COMMIT;", tableName, swapName);

                    using (SqlCommand cmd = new SqlCommand(swapQuery, conn, transaction))
                    {
                        // 添加参数
                        cmd.Parameters.AddWithValue("@SwapValue1", swapValue1);
                        cmd.Parameters.AddWithValue("@SwapValue2", swapValue2);
                        cmd.Parameters.AddWithValue("@SwapTempValue", Guid.NewGuid().ToString());

                        // 执行查询
                        cmd.ExecuteNonQuery();
                    }

                    // 提交事务
                    transaction.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    Debug.WriteLine("交换失败,原因是:" + ex.Message.ToString());
                    // 出现异常时回滚事务
                    transaction.Rollback();
                    return false;
                }
            }
        }

        /// <summary>
        /// 更新指定列的值通过索引指定列的指定值
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="oriValue"></param>
        /// <param name="newValue"></param>
        /// <param name="columnName"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static bool UpdataValueFromIndexName(string tableName, string oriValue, string newValue, string columnName, string connectionString = "")
        {
            if (string.IsNullOrEmpty(connectionString))
                connectionString = ConnectionString;

            // SQL 查询语句，检查 ProcessName 列是否存在指定的值
            string sqlQuery = string.Format("UPDATE [{0}] SET {1} = @NewValue WHERE {1} = @OriValue", tableName, columnName);
            string resultValue = string.Empty;

            // 连接数据库并执行查询
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                {
                    // 参数化查询，防止 SQL 注入
                    command.Parameters.AddWithValue("@OriValue", oriValue);  // 要替换的原始值
                    command.Parameters.AddWithValue("@NewValue", newValue);  // 替换后的新值

                    // 打开连接
                    connection.Open();

                    // 执行更新操作
                    int rowsAffected = command.ExecuteNonQuery();

                    if (rowsAffected > 0)
                    {
                        return true;
                    }
                }
            }
            return false;
        }


        /// <summary>
        /// 检查数据库是否存在
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="databaseName"></param>
        /// <returns></returns>
        public static bool DatabaseExists(string connectionString, string databaseName)
        {
            string query = "SELECT database_id FROM sys.databases WHERE name = @databaseName";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@databaseName", databaseName);
                    var result = command.ExecuteScalar();
                    return result != null;
                }
            }
        }

        /// <summary>
        /// 创建数据库(需要master权限)
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="databaseName"></param>
        /// <param name="databasePath"></param>
        /// <param name="logFilePath"></param>
        public static bool CreateDatabase(string connectionString, string databaseName)
        {
            if (string.IsNullOrEmpty(connectionString))
                connectionString = ConnectionString;

            if (!connectionString.Contains("master"))
                return false;

            try
            {
                string createQuery = $@"CREATE DATABASE {databaseName}";

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(createQuery, connection))
                    {
                        command.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch { return false; }
        }

        /// <summary>
        /// 删除数据库(需要master权限)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="connectionString"></param>
        /// <returns>值存在True</returns>
        public static bool DeleteDatabase(string databaseName, string connectionString)
        {
            if (string.IsNullOrEmpty(connectionString))
                connectionString = ConnectionString;

            if (!connectionString.Contains("master"))
                return false;

            // 设置 SQL DROP DATABASE 语句
            string dropDatabaseQuery = $"DROP DATABASE IF EXISTS {databaseName}";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    // 打开数据库连接
                    connection.Open();

                    // 1. 设置数据库为单用户模式，强制断开所有连接
                    string setSingleUserQuery = $@"ALTER DATABASE [{databaseName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;";
                    using (var cmd = new SqlCommand(setSingleUserQuery, connection))
                    {
                        cmd.ExecuteNonQuery();
                        Debug.WriteLine($"Database {databaseName} is now in single-user mode.");
                    }

                    // 创建 MySqlCommand 对象
                    using (SqlCommand cmd = new SqlCommand(dropDatabaseQuery, connection))
                    {
                        // 执行删除数据库操作
                        cmd.ExecuteNonQuery();
                        Debug.WriteLine("Database dropped successfully.");
                        return true;
                    }
                }
                catch (Exception ex)
                {
                    Debug.WriteLine("Error: " + ex.Message);
                    return false;
                }
            }
        }

        /// <summary>
        /// 复制表结构和数据(需要master权限)
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="sourceDatabase"></param>
        /// <param name="targetDatabase"></param>
        public static bool CopyDatabaseData(string connectionString, string sourceDatabase, string targetDatabase)
        {
            if (string.IsNullOrEmpty(connectionString))
                connectionString = ConnectionString;

            if (!connectionString.Contains("master"))
                return false;

            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    string useSourceDb = $"USE {sourceDatabase};";
                    string useTargetDb = $"USE {targetDatabase};";

                    // 切换到源数据库
                    using (var cmd = new SqlCommand(useSourceDb, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    // 获取源数据库中所有表的列表
                    string getTablesQuery = "SELECT name FROM sys.tables;";
                    using (var cmd = new SqlCommand(getTablesQuery, connection))
                    {
                        // 使用 CommandBehavior.CloseConnection 自动关闭连接
                        using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (reader.Read())
                            {
                                string tableName = reader.GetString(0);
                                Debug.WriteLine($"复制表: {tableName}");

                                // 复制表结构
                                string createTableQuery = $"SELECT * INTO {targetDatabase}.dbo.{tableName} FROM {sourceDatabase}.dbo.{tableName} WHERE 1 = 0;";
                                using (var cmdCreateTable = new SqlCommand(createTableQuery, connection))
                                {
                                    cmdCreateTable.ExecuteNonQuery();
                                }

                                // 复制表数据
                                string insertDataQuery = $"INSERT INTO {targetDatabase}.dbo.{tableName} SELECT * FROM {sourceDatabase}.dbo.{tableName};";
                                using (var cmdInsertData = new SqlCommand(insertDataQuery, connection))
                                {
                                    cmdInsertData.ExecuteNonQuery();
                                }
                            }
                        }
                    }
                }
                return true;
            }
            catch { return false; }
        }

        public static List<string> GetUserDatabases()
        {
            List<string> databases = new List<string>();
            string connectionString = "Server=localhost;Database=master;Integrated Security=True;";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    string query = @"
                        SELECT name 
                        FROM sys.databases 
                        WHERE database_id > 4 -- 过滤系统数据库
                            AND name NOT IN ('model','msdb','tempdb','DWConfiguration','DWDiagnostics','DWQueue') 
                            AND source_database_id IS NULL -- 排除数据库快照";

                    using (SqlCommand command = new SqlCommand(query, connection))
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            databases.Add(reader["name"]?.ToString());
                        }
                    }
                }
                catch (SqlException ex)
                {
                    Debug.WriteLine($"数据库查询失败: {ex.Message}");
                }
            }
            return databases;
        }
    }
}