using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using MySqlConnector;
using TeamAAS.Database.Attributes;
using TeamAAS.Database.Interfaces;
using TeamAAS.Database.Models;

namespace TeamAAS.Database.Providers
{
    /// <summary>
    /// MySQL 数据库提供者实现
    /// </summary>
    [DatabaseProvider("mysql", DisplayName = "MySQL", RequiresServer = true,
        Description = "MySQL / MariaDB 数据库，基于 MySqlConnector")]
    public class MySqlDatabase : IDatabase
    {
        private MySqlConnection _connection;
        private DatabaseConfig _config;
        private readonly object _sync = new object();

        public Guid Id { get; private set; }
        public string Name { get; set; }
        public string ProviderType => "mysql";
        public bool IsConnected => _connection != null && _connection.State == ConnectionState.Open;

        public string ConnectionString
        {
            get
            {
                if (_config == null) return string.Empty;
                if (!string.IsNullOrWhiteSpace(_config.ConnectionString))
                    return MaskPassword(_config.ConnectionString);

                var sb = new StringBuilder();
                sb.Append($"Server={_config.Server};");
                if (_config.Port > 0) sb.Append($"Port={_config.Port};");
                sb.Append($"Database={_config.DatabaseName};");
                sb.Append($"Uid={_config.UserId};");
                sb.Append("Pwd=***;");
                if (_config.ConnectionTimeout > 0)
                    sb.Append($"Connection Timeout={_config.ConnectionTimeout};");
                return sb.ToString();
            }
        }

        private string GetRawConnectionString()
        {
            if (!string.IsNullOrWhiteSpace(_config?.ConnectionString))
                return _config.ConnectionString;

            if (_config == null)
                throw new InvalidOperationException("数据库未配置");

            var sb = new MySqlConnectionStringBuilder
            {
                Server = _config.Server,
                Port = (uint)(_config.Port > 0 ? _config.Port : 3306),
                Database = _config.DatabaseName,
                UserID = _config.UserId,
                Password = _config.Password,
                ConnectionTimeout = (uint)(_config.ConnectionTimeout > 0 ? _config.ConnectionTimeout : 30)
            };

            if (_config.ExtraParams != null)
            {
                foreach (var kv in _config.ExtraParams)
                {
                    try { sb[kv.Key] = kv.Value; } catch { /* 忽略无效参数 */ }
                }
            }

            return sb.ConnectionString;
        }

        public void Configure(DatabaseConfig config)
        {
            _config = config ?? throw new ArgumentNullException(nameof(config));
            Id = config.Id == Guid.Empty ? Guid.NewGuid() : config.Id;
            Name = config.Name ?? $"MySQL_{_config.Server}";
        }

        public bool Open()
        {
            lock (_sync)
            {
                try
                {
                    if (_connection == null)
                        _connection = new MySqlConnection(GetRawConnectionString());

                    if (_connection.State == ConnectionState.Open)
                        return true;

                    _connection.Open();
                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }

        public Task<bool> OpenAsync()
        {
            return Task.Run(() => Open());
        }

        public void Close()
        {
            lock (_sync)
            {
                if (_connection != null)
                {
                    try { _connection.Close(); } catch { }
                }
            }
        }

        public Task CloseAsync()
        {
            return Task.Run(() => Close());
        }

        public (bool Success, string Message) TestConnection()
        {
            try
            {
                using (var conn = new MySqlConnection(GetRawConnectionString()))
                {
                    conn.Open();
                    return (true, "连接成功");
                }
            }
            catch (Exception ex)
            {
                return (false, ex.Message);
            }
        }

        public Task<(bool Success, string Message)> TestConnectionAsync()
        {
            return Task.Run(() => TestConnection());
        }

        public int ExecuteNonQuery(string sql, IDictionary<string, object> parameters = null)
        {
            using (var cmd = CreateCommand(sql, parameters))
            {
                EnsureOpen();
                return cmd.ExecuteNonQuery();
            }
        }

        public Task<int> ExecuteNonQueryAsync(string sql, IDictionary<string, object> parameters = null)
        {
            return Task.Run(() => ExecuteNonQuery(sql, parameters));
        }

        public DataTable ExecuteQuery(string sql, IDictionary<string, object> parameters = null)
        {
            using (var cmd = CreateCommand(sql, parameters))
            {
                EnsureOpen();
                var dt = new DataTable();
                using (var reader = cmd.ExecuteReader())
                {
                    dt.Load(reader);
                }
                return dt;
            }
        }

        public Task<DataTable> ExecuteQueryAsync(string sql, IDictionary<string, object> parameters = null)
        {
            return Task.Run(() => ExecuteQuery(sql, parameters));
        }

        public object ExecuteScalar(string sql, IDictionary<string, object> parameters = null)
        {
            using (var cmd = CreateCommand(sql, parameters))
            {
                EnsureOpen();
                return cmd.ExecuteScalar();
            }
        }

        public Task<object> ExecuteScalarAsync(string sql, IDictionary<string, object> parameters = null)
        {
            return Task.Run(() => ExecuteScalar(sql, parameters));
        }

        public bool ExecuteTransaction(IEnumerable<string> sqlCommands)
        {
            if (sqlCommands == null) return true;

            EnsureOpen();
            using (var transaction = _connection.BeginTransaction())
            {
                try
                {
                    foreach (var sql in sqlCommands)
                    {
                        if (string.IsNullOrWhiteSpace(sql)) continue;
                        using (var cmd = _connection.CreateCommand())
                        {
                            cmd.Transaction = transaction;
                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    transaction.Commit();
                    return true;
                }
                catch
                {
                    try { transaction.Rollback(); } catch { }
                    return false;
                }
            }
        }

        public Task<bool> ExecuteTransactionAsync(IEnumerable<string> sqlCommands)
        {
            return Task.Run(() => ExecuteTransaction(sqlCommands));
        }

        public List<string> GetTableNames()
        {
            var tables = new List<string>();
            var sql = "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = @dbName ORDER BY TABLE_NAME";
            var parameters = new Dictionary<string, object> { { "@dbName", _config.DatabaseName } };

            using (var cmd = CreateCommand(sql, parameters))
            {
                EnsureOpen();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        tables.Add(reader.GetString(0));
                    }
                }
            }
            return tables;
        }

        public Task<List<string>> GetTableNamesAsync()
        {
            return Task.Run(() => GetTableNames());
        }

        public List<ColumnInfo> GetTableSchema(string tableName)
        {
            var columns = new List<ColumnInfo>();
            var sql = @"
                SELECT 
                    COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY,
                    CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT, COLUMN_COMMENT
                FROM information_schema.COLUMNS 
                WHERE TABLE_SCHEMA = @dbName AND TABLE_NAME = @tableName
                ORDER BY ORDINAL_POSITION";

            var parameters = new Dictionary<string, object>
            {
                { "@dbName", _config.DatabaseName },
                { "@tableName", tableName }
            };

            using (var cmd = CreateCommand(sql, parameters))
            {
                EnsureOpen();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        columns.Add(new ColumnInfo
                        {
                            ColumnName = reader.GetString(0),
                            DataType = reader.GetString(1),
                            IsNullable = reader.GetString(2) == "YES",
                            IsPrimaryKey = reader.GetString(3) == "PRI",
                            MaxLength = reader.IsDBNull(4) ? 0 : Convert.ToInt32(reader[4]),
                            DefaultValue = reader.IsDBNull(5) ? null : reader.GetString(5),
                            Description = reader.IsDBNull(6) ? null : reader.GetString(6)
                        });
                    }
                }
            }
            return columns;
        }

        public Task<List<ColumnInfo>> GetTableSchemaAsync(string tableName)
        {
            return Task.Run(() => GetTableSchema(tableName));
        }

        #region 私有方法

        private MySqlCommand CreateCommand(string sql, IDictionary<string, object> parameters)
        {
            var cmd = _connection.CreateCommand();
            cmd.CommandText = sql;

            if (parameters != null)
            {
                foreach (var kv in parameters)
                {
                    var paramName = kv.Key.StartsWith("@") ? kv.Key : "@" + kv.Key;
                    cmd.Parameters.AddWithValue(paramName, kv.Value ?? DBNull.Value);
                }
            }

            return cmd;
        }

        private void EnsureOpen()
        {
            if (_connection == null || _connection.State != ConnectionState.Open)
            {
                if (!Open())
                    throw new InvalidOperationException("数据库连接失败");
            }
        }

        private static string MaskPassword(string connStr)
        {
            try
            {
                var builder = new MySqlConnectionStringBuilder(connStr);
                if (!string.IsNullOrWhiteSpace(builder.Password))
                    builder.Password = "***";
                return builder.ConnectionString;
            }
            catch
            {
                return connStr;
            }
        }

        #endregion

        #region IDisposable

        private bool _disposed;

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (_disposed) return;

            if (disposing)
            {
                Close();
                _connection?.Dispose();
                _connection = null;
            }

            _disposed = true;
        }

        ~MySqlDatabase()
        {
            Dispose(false);
        }

        #endregion
    }
}