using System.Reflection;
using System.Text.RegularExpressions;
using Microsoft.AspNetCore.Http.Timeouts;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Options;
using SqlSugar;
using Volo.Abp;
using Volo.Abp.Application.Dtos;
using Volo.Abp.Data;
using Volo.Abp.Modularity;
using Volo.Abp.MultiTenancy;
using Volo.Abp.Uow;
using Yi.Framework.Ddd.Application;
using Yi.Framework.SqlSugarCore.Abstractions;
using Yi.Framework.TenantManagement.Application.Contracts;
using Yi.Framework.TenantManagement.Application.Contracts.Dtos;
using Yi.Framework.TenantManagement.Domain;

namespace Yi.Framework.TenantManagement.Application
{
    /// <summary>
    /// 租户管理
    /// </summary>
    public class TenantService :
        YiCrudAppService<TenantAggregateRoot, TenantGetOutputDto, TenantGetListOutputDto, Guid, TenantGetListInput,
            TenantCreateInput, TenantUpdateInput>, ITenantService
    {
        private readonly ISqlSugarRepository<TenantAggregateRoot, Guid> _repository;
        private readonly IDataSeeder _dataSeeder;
        private readonly DbConnOptions _dbConnOptions;

        public TenantService(
            ISqlSugarRepository<TenantAggregateRoot, Guid> repository,
            IDataSeeder dataSeeder,
            IOptions<DbConnOptions> dbConnOptions) :
            base(repository)
        {
            _repository = repository;
            _dataSeeder = dataSeeder;
            _dbConnOptions = dbConnOptions.Value;
        }

        /// <summary>
        /// YiTenant 标记了 <see cref="DefaultTenantTableAttribute"/>，读写必须走主库（CurrentTenant=null → DbConnOptions.Url），
        /// 不能随请求租户上下文落到 Default 租户的业务库。
        /// </summary>
        private IDisposable UseHostTenantScope() => CurrentTenant.Change(null);

        /// <summary>
        /// 用主库连接串直连读取 YiTenant，避免 UoW 事务 + IsAutoCloseConnection 在后台 Init 时 NRE。
        /// </summary>
        private async Task<TenantAggregateRoot?> LoadTenantFromHostAsync(Guid id)
        {
            if (string.IsNullOrWhiteSpace(_dbConnOptions.Url))
            {
                throw new UserFriendlyException("未配置主库连接字符串 DbConnOptions.Url");
            }

            var dbType = _dbConnOptions.DbType ?? DbType.MySql;
            using var client = new SqlSugarClient(new ConnectionConfig
            {
                ConfigId = $"host-yitenant-read-{Guid.NewGuid():N}",
                DbType = dbType,
                ConnectionString = _dbConnOptions.Url,
                IsAutoCloseConnection = true
            });

            var tenant = await client.Queryable<TenantAggregateRoot>().InSingleAsync(id);
            return tenant is { IsDeleted: true } ? null : tenant;
        }

        /// <summary>
        /// 租户单查
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public override async Task<TenantGetOutputDto> GetAsync(Guid id)
        {
            using (UseHostTenantScope())
            {
                return await base.GetAsync(id);
            }
        }

        /// <summary>
        /// 租户多查
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public override async Task<PagedResultDto<TenantGetListOutputDto>> GetListAsync(TenantGetListInput input)
        {
            using (UseHostTenantScope())
            {
                RefAsync<int> total = 0;

                var entities = await _repository._DbQueryable
                    .WhereIF(!string.IsNullOrEmpty(input.Name), x => x.Name.Contains(input.Name!))
                    .WhereIF(input.StartTime is not null && input.EndTime is not null,
                        x => x.CreationTime >= input.StartTime && x.CreationTime <= input.EndTime)
                    .ToPageListAsync(input.SkipCount, input.MaxResultCount, total);
                return new PagedResultDto<TenantGetListOutputDto>(total, await MapToGetListOutputDtosAsync(entities));
            }
        }

        /// <summary>
        /// 租户选项
        /// </summary>
        /// <returns></returns>
        public async Task<List<TenantSelectOutputDto>> GetSelectAsync()
        {
            using (UseHostTenantScope())
            {
                var entites = await _repository._DbQueryable.ToListAsync();
                return entites.Select(x => new TenantSelectOutputDto { Id = x.Id, Name = x.Name }).ToList();
            }
        }


        /// <summary>
        /// 创建租户
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public override async Task<TenantGetOutputDto> CreateAsync(TenantCreateInput input)
        {
            using (UseHostTenantScope())
            {
                if (await _repository.IsAnyAsync(x => x.Name == input.Name))
                {
                    throw new UserFriendlyException("创建失败，当前租户已存在");
                }

                return await base.CreateAsync(input);
            }
        }

        /// <summary>
        /// 更新租户
        /// </summary>
        /// <param name="id"></param>
        /// <param name="input"></param>
        /// <returns></returns>
        public override async Task<TenantGetOutputDto> UpdateAsync(Guid id, TenantUpdateInput input)
        {
            using (UseHostTenantScope())
            {
                if (await _repository.IsAnyAsync(x => x.Name == input.Name && x.Id != id))
                {
                    throw new UserFriendlyException("更新后租户名已经存在");
                }

                return await base.UpdateAsync(id, input);
            }
        }


        /// <summary>
        /// 租户删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public override async Task DeleteAsync(IEnumerable<Guid> id)
        {
            using (UseHostTenantScope())
            {
                await base.DeleteAsync(id);
            }
        }


        /// <summary>
        /// 初始化租户
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpPut("tenant/init/{id}")]
        [RequestTimeout("ThTenantDatabaseInit")]
        [UnitOfWork(isTransactional: false)]
        public async Task InitAsync([FromRoute] Guid id)
        {
            TenantAggregateRoot tenant;
            using (UseHostTenantScope())
            {
                tenant = await LoadTenantFromHostAsync(id);
                if (tenant == null)
                {
                    throw new UserFriendlyException("未找到租户信息");
                }
            }

            // 用主库读到的连接串直接建库/表，不依赖 CurrentTenant.Change(id) 解析仓储连接
            await CodeFirstForTenantAsync(tenant, LazyServiceProvider);

            using (CurrentTenant.Change(id))
            {
                await _dataSeeder.SeedAsync(id);
            }
        }

        /// <summary>
        /// 在租户业务库执行 CodeFirst（连接串来自主库 YiTenant 记录）。
        /// 步骤：无库名连接建库 → 新连接连目标库建表（建库后必须重连，同会话 USE 在部分 MySQL/RDS 会 Access denied）。
        /// </summary>
        private async Task CodeFirstForTenantAsync(TenantAggregateRoot tenant, IServiceProvider service)
        {
            var moduleContainer = service.GetRequiredService<IModuleContainer>();

            List<Type> types = new List<Type>();
            foreach (var module in moduleContainer.Modules)
            {
                types.AddRange(module.Assembly.GetTypes()
                    .Where(x => x.GetCustomAttribute<IgnoreCodeFirstAttribute>() == null)
                    .Where(x => x.GetCustomAttribute<SugarTable>() != null)
                    .Where(x => x.GetCustomAttribute<DefaultTenantTableAttribute>() is null)
                    .Where(x => x.GetCustomAttribute<SplitTableAttribute>() is null));
            }

            var databaseName = ExtractDatabaseName(tenant.TenantConnectionString);
            if (string.IsNullOrWhiteSpace(databaseName) || !SafeDatabaseNameRegex.IsMatch(databaseName))
            {
                throw new UserFriendlyException("租户连接串中的数据库名无效，无法建库");
            }

            // 建库连接候选（按优先级，互不相同才尝试下一条）：
            // 1) AdminConnectionString 高权限（可选）
            // 2) 主库 Url（带 database=host，RDS 上多数账号必须先进入已授权库才能执行 CREATE）
            // 3) 无 database= 的实例级连接（与部分 Navicat 用法一致）
            var createCandidates = new List<(string Label, string ConnectionString)>();
            void AddCandidate(string label, string? conn)
            {
                if (string.IsNullOrWhiteSpace(conn))
                {
                    return;
                }

                if (createCandidates.Any(x =>
                        string.Equals(x.ConnectionString, conn, StringComparison.OrdinalIgnoreCase)))
                {
                    return;
                }

                createCandidates.Add((label, conn));
            }

            AddCandidate("AdminConnectionString", _dbConnOptions.AdminConnectionString);
            AddCandidate("DbConnOptions.Url(主库)", _dbConnOptions.Url);
            AddCandidate(
                "实例级(无database)",
                BuildServerLevelConnectionString(
                    !string.IsNullOrWhiteSpace(_dbConnOptions.Url)
                        ? _dbConnOptions.Url!
                        : tenant.TenantConnectionString));

            if (createCandidates.Count == 0)
            {
                throw new UserFriendlyException("未配置可用的数据库连接串，无法创建租户库");
            }

            var externalServices = new ConfigureExternalServices
            {
                EntityNameService = (type, entity) =>
                {
                    if (_dbConnOptions.EnableUnderLine && !entity.DbTableName.Contains('_'))
                    {
                        entity.DbTableName = UtilMethods.ToUnderLine(entity.DbTableName);
                    }
                },
                EntityService = (propertyInfo, columnInfo) =>
                {
                    // 与 SqlSugarDbContextFactory 一致：可空引用类型映射为可空列，避免 Seed 插入 null 失败
                    if (new NullabilityInfoContext().Create(propertyInfo).WriteState
                        is NullabilityState.Nullable)
                    {
                        columnInfo.IsNullable = true;
                    }

                    if (_dbConnOptions.EnableUnderLine && !columnInfo.IsIgnore
                        && !columnInfo.DbColumnName.Contains('_'))
                    {
                        columnInfo.DbColumnName = UtilMethods.ToUnderLine(columnInfo.DbColumnName);
                    }
                }
            };

            // 1) CREATE DATABASE：依次尝试候选连接
            var createErrors = new List<string>();
            string? privilegedConnectionString = null;
            foreach (var candidate in createCandidates)
            {
                try
                {
                    ExecuteCreateDatabase(tenant.DbType, candidate.ConnectionString, databaseName);
                    privilegedConnectionString = candidate.ConnectionString;
                    break;
                }
                catch (Exception ex)
                {
                    createErrors.Add($"{candidate.Label}: {GetRootMessage(ex)}");
                }
            }

            if (privilegedConnectionString == null)
            {
                var userId = ExtractConnectionValue(tenant.TenantConnectionString, "uid") ?? "netteam";
                throw new UserFriendlyException(
                    $"创建租户库失败（库={databaseName}）。尝试结果：{string.Join(" | ", createErrors)}。" +
                    $"说明：MySQL 报 Access denied to database 新建库名时，通常是账号没有 CREATE 权限（与能否连上主库无关）。" +
                    $"请在 appsettings 的 DbConnOptions.AdminConnectionString 配置高权限账号连接串后重试；或手动执行：" +
                    $"CREATE DATABASE IF NOT EXISTS `{databaseName}` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; " +
                    $"GRANT ALL PRIVILEGES ON `{databaseName}`.* TO '{userId}'@'%'; " +
                    $"再 PUT /api/app/tenant/init/{tenant.Id}");
            }

            // 2) 把业务账号（TenantConnectionString 的 uid，如 netteam）授权到新库
            //    建库常用高权限账号（Admin / javateam），否则业务账号无 CREATE TABLE 权限
            var grantWarnings = TryGrantTenantDatabaseAccess(
                tenant.DbType,
                privilegedConnectionString,
                databaseName,
                tenant.TenantConnectionString);

            // 3) 建表：优先业务连接串；失败则用建库成功的高权限连接串切到目标库再建表
            try
            {
                InitTenantTables(
                    tenant.Id,
                    tenant.DbType,
                    tenant.TenantConnectionString,
                    types,
                    externalServices);
            }
            catch (Exception tenantConnEx)
            {
                var adminTenantConn = ReplaceConnectionDatabase(privilegedConnectionString, databaseName);
                try
                {
                    InitTenantTables(
                        tenant.Id,
                        tenant.DbType,
                        adminTenantConn,
                        types,
                        externalServices);
                }
                catch (Exception adminConnEx)
                {
                    var userId = ExtractConnectionValue(tenant.TenantConnectionString, "uid") ?? "netteam";
                    throw new UserFriendlyException(
                        $"租户库 `{databaseName}` 已存在，但建表失败。" +
                        $"业务账号建表：{GetRootMessage(tenantConnEx)}；" +
                        $"高权限建表：{GetRootMessage(adminConnEx)}。" +
                        (string.IsNullOrEmpty(grantWarnings) ? "" : $"授权提示：{grantWarnings}。") +
                        $"请手动执行 GRANT ALL PRIVILEGES ON `{databaseName}`.* TO '{userId}'@'%'; 后 " +
                        $"PUT /api/app/tenant/init/{tenant.Id}");
                }
            }

            await Task.CompletedTask;
        }

        private static readonly Regex SafeDatabaseNameRegex =
            new(@"^[a-zA-Z0-9][a-zA-Z0-9_\-]{0,63}$", RegexOptions.Compiled);

        private static string GetRootMessage(Exception ex)
        {
            var current = ex;
            while (current.InnerException != null)
            {
                current = current.InnerException;
            }

            return current.Message;
        }

        /// <summary>
        /// 去掉 database=，使连接为「实例级」，便于执行 CREATE DATABASE（与 Navicat 无默认库一致）。
        /// </summary>
        private static string BuildServerLevelConnectionString(string connectionString)
        {
            var segments = connectionString
                .Split(';', StringSplitOptions.RemoveEmptyEntries)
                .Select(x => x.Trim())
                .Where(x => !x.StartsWith("database=", StringComparison.OrdinalIgnoreCase)
                            && !x.StartsWith("initial catalog=", StringComparison.OrdinalIgnoreCase))
                .ToList();
            return string.Join(';', segments) + ";";
        }

        private static void ExecuteCreateDatabase(DbType dbType, string connectionString, string databaseName)
        {
            using var createDb = new SqlSugarClient(new ConnectionConfig
            {
                ConfigId = $"tenant-create-db-{databaseName}-{Guid.NewGuid():N}",
                DbType = dbType,
                ConnectionString = connectionString,
                IsAutoCloseConnection = true
            });

            createDb.Ado.ExecuteCommand(
                $"CREATE DATABASE IF NOT EXISTS `{databaseName}` " +
                "DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        }

        /// <summary>
        /// 授权业务账号访问新租户库；失败不抛（返回警告，后续可用高权限建表）。
        /// </summary>
        private static string TryGrantTenantDatabaseAccess(
            DbType dbType,
            string privilegedConnectionString,
            string databaseName,
            string tenantConnectionString)
        {
            var appUserId = ExtractConnectionValue(tenantConnectionString, "uid");
            var privilegedUserId = ExtractConnectionValue(privilegedConnectionString, "uid");
            if (string.IsNullOrWhiteSpace(appUserId))
            {
                return "租户连接串缺少 uid，跳过 GRANT";
            }

            // 建库账号与业务账号相同，通常无需再 GRANT
            if (string.Equals(appUserId, privilegedUserId, StringComparison.OrdinalIgnoreCase))
            {
                return string.Empty;
            }

            try
            {
                using var grantDb = new SqlSugarClient(new ConnectionConfig
                {
                    ConfigId = $"tenant-grant-{databaseName}-{Guid.NewGuid():N}",
                    DbType = dbType,
                    ConnectionString = privilegedConnectionString,
                    IsAutoCloseConnection = true
                });

                // 阿里云 RDS：对业务账号授权新库全部权限
                grantDb.Ado.ExecuteCommand(
                    $"GRANT ALL PRIVILEGES ON `{databaseName}`.* TO '{EscapeSqlLiteral(appUserId)}'@'%'");
                return string.Empty;
            }
            catch (Exception ex)
            {
                return GetRootMessage(ex);
            }
        }

        private static void InitTenantTables(
            Guid tenantId,
            DbType dbType,
            string connectionString,
            List<Type> types,
            ConfigureExternalServices externalServices)
        {
            using var db = new SqlSugarClient(new ConnectionConfig
            {
                ConfigId = $"tenant-init-tables-{tenantId}-{Guid.NewGuid():N}",
                DbType = dbType,
                ConnectionString = connectionString,
                IsAutoCloseConnection = true,
                ConfigureExternalServices = externalServices
            });

            db.Ado.ExecuteCommand("SELECT 1");

            // 历史误用精简 DbModel（如 RoleDbEntity）建出的残缺表，InitTables 不会补列，需先删再重建
            DropIncompleteRbacTablesIfNeeded(db);

            if (types.Count > 0)
            {
                db.CodeFirst.InitTables(types.ToArray());
            }
        }

        /// <summary>
        /// 若 Role 等表缺少 CreationTime，说明曾被精简映射建坏，删除后由完整实体重建。
        /// </summary>
        private static void DropIncompleteRbacTablesIfNeeded(ISqlSugarClient db)
        {
            foreach (var tableName in new[] { "Role", "role", "RoleMenu", "rolemenu", "Menu", "menu" })
            {
                if (!db.DbMaintenance.IsAnyTable(tableName, false))
                {
                    continue;
                }

                var columns = db.DbMaintenance.GetColumnInfosByTableName(tableName, false);
                var hasCreationTime = columns.Any(c =>
                    string.Equals(c.DbColumnName, "CreationTime", StringComparison.OrdinalIgnoreCase));

                // Role 精简模型只有 Id/IsDeleted/RoleCode/RoleName，缺 CreationTime
                if (string.Equals(tableName, "Role", StringComparison.OrdinalIgnoreCase) && !hasCreationTime)
                {
                    db.DbMaintenance.DropTable(tableName);
                    continue;
                }

                // RoleMenu 精简模型无业务外键类型问题较少，但若列过少也重建
                if (string.Equals(tableName, "RoleMenu", StringComparison.OrdinalIgnoreCase)
                    && columns.Count < 3)
                {
                    db.DbMaintenance.DropTable(tableName);
                }
            }
        }

        private static string EscapeSqlLiteral(string value)
            => value.Replace("'", "''", StringComparison.Ordinal);

        private static string? ExtractDatabaseName(string connectionString)
            => ExtractConnectionValue(connectionString, "database");

        private static string? ExtractConnectionValue(string connectionString, string key)
        {
            if (string.IsNullOrWhiteSpace(connectionString))
            {
                return null;
            }

            var prefix = key + "=";
            foreach (var segment in connectionString.Split(';', StringSplitOptions.RemoveEmptyEntries))
            {
                var part = segment.Trim();
                if (part.StartsWith(prefix, StringComparison.OrdinalIgnoreCase))
                {
                    return part[prefix.Length..].Trim();
                }
            }

            return null;
        }

        private static string ReplaceConnectionDatabase(string connectionString, string databaseName)
        {
            var segments = connectionString
                .Split(';', StringSplitOptions.RemoveEmptyEntries)
                .Select(x => x.Trim())
                .Where(x => !x.StartsWith("database=", StringComparison.OrdinalIgnoreCase)
                            && !x.StartsWith("initial catalog=", StringComparison.OrdinalIgnoreCase))
                .ToList();
            segments.Add($"database={databaseName}");
            return string.Join(';', segments) + ";";
        }
    }
}