using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LB_SmartVision.SQL
{
    public class LB_SqlCommand
    {
        /// <summary>
        /// 连接字符串：指定 MySQL 实例和数据库(实际会读取dll.config中的值来更新连接字符串)
        /// </summary>
        public static string ConnectionString = "Server=localhost;Database=product_mes;Uid=root;Pwd=root;";

        /// <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;

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

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

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

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

                    // 如果 result == 1，表示存在该值；否则不存在
                    return result == 1;
                }
            }
        }

        /// <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;

            // MySQL 查询语句，检查表是否存在
            string sqlQuery = $@"
                                SELECT COUNT(*)
                                FROM INFORMATION_SCHEMA.TABLES
                                WHERE TABLE_SCHEMA = DATABASE() 
                                  AND TABLE_NAME = @TableName";

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

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

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

                    // 如果 result > 0，表示存在该表；否则不存在
                    return result > 0;
                }
            }
        }

        /// <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;

            // MySQL 重命名表语法
            string sqlQuery = $"ALTER TABLE `{oldTableName}` RENAME TO `{newTableName}`";

            // 创建 MySQL 连接
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                // 创建 MySQL 命令
                using (MySqlCommand command = new MySqlCommand(sqlQuery, connection))
                {
                    // 打开连接
                    connection.Open();

                    try
                    {
                        // 执行查询并获取结果
                        command.ExecuteNonQuery();
                        return true;
                    }
                    catch
                    {
                        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;

            // MySQL 删除表语句
            string sqlQuery = $"DROP TABLE IF EXISTS `{tableName}`";

            // 创建 MySQL 连接
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                // 创建 MySQL 命令
                using (MySqlCommand command = new MySqlCommand(sqlQuery, connection))
                {
                    // 打开连接
                    connection.Open();

                    try
                    {
                        // 执行删除操作
                        command.ExecuteNonQuery();
                        return true;
                    }
                    catch
                    {
                        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;

                // MySQL 查询语句
                string sqlQuery = $"SELECT `{columnName}` FROM `{tableName}` WHERE `{indexName}` = @IndexValue";
                string resultValue = string.Empty;

                // 连接数据库并执行查询
                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {
                    using (MySqlCommand command = new MySqlCommand(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="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 (MySqlConnection conn = new MySqlConnection(connectionString))
                {
                    conn.Open();

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

                    // MySQL 中更新行号的写法（假设indexName是自增列）
                    string updateQuery = string.Format(@"
                        SET @row_number = 0;
                        UPDATE `{0}` 
                        SET `{1}` = @row_number := @row_number + 1 
                        ORDER BY `{1}`;", tableName, indexName);

                    using (MySqlCommand cmd = new MySqlCommand(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 (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();

                // 开始事务
                MySqlTransaction transaction = conn.BeginTransaction();
                try
                {
                    // MySQL 交换行值的写法
                    string swapQuery = string.Format(@"
                        UPDATE `{0}` 
                        SET `{1}` = CASE 
                            WHEN `{1}` = @SwapValue1 THEN @SwapTempValue
                            WHEN `{1}` = @SwapValue2 THEN @SwapValue1
                            WHEN `{1}` = @SwapTempValue THEN @SwapValue2
                        END
                        WHERE `{1}` IN (@SwapValue1, @SwapValue2, @SwapTempValue);", tableName, swapName);

                    using (MySqlCommand cmd = new MySqlCommand(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 UpdateValueFromIndexName(string tableName, string oriValue, string newValue, string columnName, string connectionString = "")
        {
            // 修正原方法名拼写错误 Updata -> Update
            if (string.IsNullOrEmpty(connectionString))
                connectionString = ConnectionString;

            // MySQL 更新语句
            string sqlQuery = string.Format("UPDATE `{0}` SET `{1}` = @NewValue WHERE `{1}` = @OriValue", tableName, columnName);

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

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

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

                    return rowsAffected > 0;
                }
            }
        }

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

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

        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="databaseName"></param>
        /// <returns></returns>
        public static bool CreateDatabase(string connectionString, string databaseName)
        {
            if (string.IsNullOrEmpty(connectionString))
                connectionString = ConnectionString;

            try
            {
                string createQuery = $"CREATE DATABASE IF NOT EXISTS `{databaseName}` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci";

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

        /// <summary>
        /// 删除数据库
        /// </summary>
        /// <param name="databaseName"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static bool DeleteDatabase(string databaseName, string connectionString)
        {
            if (string.IsNullOrEmpty(connectionString))
                connectionString = ConnectionString;

            // MySQL 删除数据库语句
            string dropDatabaseQuery = $"DROP DATABASE IF EXISTS `{databaseName}`";

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

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

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

            try
            {
                // 先创建目标数据库（如果不存在）
                CreateDatabase(connectionString, targetDatabase);

                using (var connection = new MySqlConnection(connectionString))
                {
                    connection.Open();

                    // 获取源数据库中所有表的列表
                    string getTablesQuery = $"SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = @sourceDb";
                    using (var cmd = new MySqlCommand(getTablesQuery, connection))
                    {
                        cmd.Parameters.AddWithValue("@sourceDb", sourceDatabase);
                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                string tableName = reader.GetString(0);
                                Debug.WriteLine($"复制表: {tableName}");

                                // 关闭reader才能执行后续操作
                                reader.Close();

                                // 复制表结构和数据（MySQL 简化写法）
                                string copyTableQuery = $"CREATE TABLE `{targetDatabase}`.`{tableName}` LIKE `{sourceDatabase}`.`{tableName}`; " +
                                                       $"INSERT INTO `{targetDatabase}`.`{tableName}` SELECT * FROM `{sourceDatabase}`.`{tableName}`;";

                                using (var cmdCopy = new MySqlCommand(copyTableQuery, connection))
                                {
                                    cmdCopy.ExecuteNonQuery();
                                }

                                // 重新打开reader继续读取下一个表
                                reader.NextResult();
                            }
                        }
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                Debug.WriteLine($"复制失败: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 获取用户数据库列表
        /// </summary>
        /// <returns></returns>
        public static List<string> GetUserDatabases()
        {
            List<string> databases = new List<string>();
            string connectionString = "Server=localhost;Database=user_pt;Uid=root;Pwd=root;";

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    string query = @"
                        SELECT SCHEMA_NAME 
                        FROM INFORMATION_SCHEMA.SCHEMATA 
                        WHERE SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')";

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