using FoodLabeling.Th.Domain.Entities;
using SqlSugar;
using Volo.Abp;
using Yi.Framework.Rbac.Domain.Entities;
using Yi.Framework.SqlSugarCore.Abstractions;
using Yi.Framework.TenantManagement.Domain;

namespace FoodLabeling.Th.Application.MultiTenancy;

/// <summary>
/// 主库（DbConnOptions.Url）直连读取 YiTenant、fl_th_tenant_admin_credential，
/// 避免后台 Init 后 UoW 缓存租户库连接导致凭据查不到，以及 FindAsync 与 TenantId 列映射不一致。
/// </summary>
internal static class ThTenantHostDataAccessor
{
    internal static async Task<TenantAggregateRoot?> LoadTenantAsync(DbConnOptions dbConnOptions, Guid tenantId)
    {
        if (string.IsNullOrWhiteSpace(dbConnOptions.Url))
        {
            throw new UserFriendlyException("未配置主库连接字符串 DbConnOptions.Url");
        }

        var dbType = dbConnOptions.DbType ?? DbType.MySql;
        using var client = CreateHostClient(dbConnOptions.Url, dbType);
        var tenant = await client.Queryable<TenantAggregateRoot>().InSingleAsync(tenantId);
        return tenant is { IsDeleted: true } ? null : tenant;
    }

    internal static async Task<ThTenantAdminCredentialEntity?> LoadCredentialAsync(
        DbConnOptions dbConnOptions,
        Guid tenantId)
    {
        if (string.IsNullOrWhiteSpace(dbConnOptions.Url))
        {
            throw new UserFriendlyException("未配置主库连接字符串 DbConnOptions.Url");
        }

        var dbType = dbConnOptions.DbType ?? DbType.MySql;
        using var client = CreateHostClient(dbConnOptions.Url, dbType);
        var credentials = await client.Queryable<ThTenantAdminCredentialEntity>()
            .Where(c => c.Id == tenantId)
            .Take(1)
            .ToListAsync();
        return credentials.FirstOrDefault();
    }

    /// <summary>
    /// 按开通邮箱反查未删除租户 Id（邮箱全局唯一时返回唯一租户；同邮箱多条取最近修改）。
    /// </summary>
    internal static async Task<Guid?> FindActiveTenantIdByLoginAccountAsync(
        DbConnOptions dbConnOptions,
        string loginAccount)
    {
        if (string.IsNullOrWhiteSpace(loginAccount) || string.IsNullOrWhiteSpace(dbConnOptions.Url))
        {
            return null;
        }

        var normalized = loginAccount.Trim().ToLowerInvariant();
        var dbType = dbConnOptions.DbType ?? DbType.MySql;
        using var client = CreateHostClient(dbConnOptions.Url, dbType);

        // YiTenant.Id 与 fl_th_tenant_admin_credential 列 collation 不一致，ORM Join 会报错
        const string sql = """
            SELECT CAST(c.`TenantId` AS CHAR(36)) AS TenantId
            FROM `fl_th_tenant_admin_credential` c
            INNER JOIN `YiTenant` t
                ON CONVERT(CAST(c.`TenantId` AS CHAR(36)) USING utf8mb4) COLLATE utf8mb4_general_ci
                 = CONVERT(CAST(t.`Id` AS CHAR(36)) USING utf8mb4) COLLATE utf8mb4_general_ci
            WHERE t.`IsDeleted` = 0
              AND c.`LoginAccount` IS NOT NULL
              AND LOWER(CONVERT(c.`LoginAccount` USING utf8mb4) COLLATE utf8mb4_general_ci) = @LoginAccount
            ORDER BY c.`LastModificationTime` DESC
            LIMIT 1
            """;

        // SqlSugar SqlQueryAsync<string> 对单列映射不稳定，使用 DTO
        var rows = await client.Ado.SqlQueryAsync<TenantIdSqlRow>(sql, new { LoginAccount = normalized });
        var raw = rows.FirstOrDefault()?.TenantId;
        if (string.IsNullOrWhiteSpace(raw) || !Guid.TryParse(raw.Trim(), out var tenantId))
        {
            return null;
        }

        return tenantId;
    }

    /// <summary>
    /// 按租户业务库 user 表 Email/UserName 反查公司租户（Team Member 等非管理员账号登录）。
    /// </summary>
    internal static async Task<Guid?> FindActiveTenantIdByBusinessUserLoginAsync(
        DbConnOptions dbConnOptions,
        string loginAccount)
    {
        if (string.IsNullOrWhiteSpace(loginAccount) || string.IsNullOrWhiteSpace(dbConnOptions.Url))
        {
            return null;
        }

        var normalized = loginAccount.Trim().ToLowerInvariant();
        var hostDbType = dbConnOptions.DbType ?? DbType.MySql;
        using var hostClient = CreateHostClient(dbConnOptions.Url, hostDbType);

        var tenants = await hostClient.Queryable<TenantAggregateRoot>()
            .Where(t => !t.IsDeleted)
            .Where(t => t.TenantConnectionString != null && t.TenantConnectionString != string.Empty)
            .Select(t => new { t.Id, t.TenantConnectionString, t.DbType })
            .ToListAsync();

        foreach (var tenant in tenants)
        {
            var connectionString = tenant.TenantConnectionString?.Trim();
            if (string.IsNullOrWhiteSpace(connectionString))
            {
                continue;
            }

            try
            {
                var tenantDbType = tenant.DbType == default ? hostDbType : tenant.DbType;
                using var tenantClient = TenantBusinessDatabaseAccessor.CreateClient(connectionString, tenantDbType);
                tenantClient.Ado.CommandTimeOut = 8;

                const string sql = """
                    SELECT CAST(`Id` AS CHAR(36)) AS UserId
                    FROM `user`
                    WHERE `IsDeleted` = 0
                      AND `State` = 1
                      AND (
                            (`Email` IS NOT NULL AND LOWER(TRIM(`Email`)) = @LoginAccount)
                         OR LOWER(TRIM(`UserName`)) = @LoginAccount
                      )
                    LIMIT 1
                    """;

                var rows = await tenantClient.Ado.SqlQueryAsync<UserIdSqlRow>(sql, new { LoginAccount = normalized });
                if (rows.Count > 0 && !string.IsNullOrWhiteSpace(rows[0].UserId))
                {
                    return tenant.Id;
                }
            }
            catch
            {
                // 单租户库不可达时跳过，继续扫描其它租户
            }
        }

        return null;
    }

    internal static async Task<List<string>> LoadMenuPermissionKeysAsync(
        DbConnOptions dbConnOptions,
        Guid tenantId)
    {
        if (string.IsNullOrWhiteSpace(dbConnOptions.Url))
        {
            throw new UserFriendlyException("未配置主库连接字符串 DbConnOptions.Url");
        }

        var dbType = dbConnOptions.DbType ?? DbType.MySql;
        using var client = CreateHostClient(dbConnOptions.Url, dbType);
        return await client.Queryable<ThTenantMenuPermissionEntity>()
            .Where(x => x.TenantId == tenantId)
            .OrderBy(x => x.CreationTime)
            .Select(x => x.PermissionKey)
            .ToListAsync();
    }

    internal static async Task<List<MenuAggregateRoot>> LoadHostMenusAsync(DbConnOptions dbConnOptions)
    {
        if (string.IsNullOrWhiteSpace(dbConnOptions.Url))
        {
            throw new UserFriendlyException("未配置主库连接字符串 DbConnOptions.Url");
        }

        var dbType = dbConnOptions.DbType ?? DbType.MySql;
        using var client = CreateHostClient(dbConnOptions.Url, dbType);
        return await client.Queryable<MenuAggregateRoot>()
            .Where(x => !x.IsDeleted)
            .OrderBy(x => x.OrderNum)
            .ToListAsync();
    }

    internal static async Task UpdateTenantAsync(DbConnOptions dbConnOptions, TenantAggregateRoot tenant)
    {
        if (string.IsNullOrWhiteSpace(dbConnOptions.Url))
        {
            throw new UserFriendlyException("未配置主库连接字符串 DbConnOptions.Url");
        }

        var dbType = dbConnOptions.DbType ?? DbType.MySql;
        using var client = CreateHostClient(dbConnOptions.Url, dbType);
        await client.Updateable(tenant).ExecuteCommandAsync();
    }

    private static SqlSugarClient CreateHostClient(string connectionString, DbType dbType)
    {
        return new SqlSugarClient(new ConnectionConfig
        {
            ConfigId = $"th-host-read-{Guid.NewGuid():N}",
            DbType = dbType,
            ConnectionString = connectionString,
            IsAutoCloseConnection = true,
            MoreSettings = new ConnMoreSettings
            {
                // 避免反查登录租户时长时间卡在不可达库
                IsAutoRemoveDataCache = true
            },
            AopEvents = new AopEvents
            {
                OnError = _ => { }
            },
            // 与 DefaultSqlSugarDbContext 一致：主库 menu 等表无 ExtraProperties 列
            ConfigureExternalServices = new ConfigureExternalServices
            {
                EntityService = (propertyInfo, columnInfo) =>
                {
                    if (propertyInfo.PropertyType == typeof(Volo.Abp.Data.ExtraPropertyDictionary))
                    {
                        columnInfo.IsIgnore = true;
                    }

                    if (propertyInfo.Name == nameof(Volo.Abp.Domain.Entities.Entity<object>.Id))
                    {
                        columnInfo.IsPrimarykey = true;
                    }
                }
            }
        },
        db =>
        {
            db.Ado.CommandTimeOut = 8;
        });
    }

    private sealed class TenantIdSqlRow
    {
        public string? TenantId { get; set; }
    }

    private sealed class UserIdSqlRow
    {
        public string? UserId { get; set; }
    }
}