using GB5Shared.DTO.Framework.Login;
using Microsoft.Extensions.Logging.Abstractions;
using Moq;
using SwBLL.ClientDatabase;
using SwBLL.DbServer;
using SwBLL.Provisioning;
using SwBLL.Query;
using SwBLL.UserResourceRole;
using SwDAL.CustomCode.QueryHistory;
using SwDAL.DTO.ClientDatabase;
using SwDAL.DTO.DbServer;
using SwDAL.DTO.QueryHistory;
using SwDAL.Enums;
using Xunit;

namespace SwTests;

/// <summary>
/// Covers SwQueryBLL.ExecuteAdHocQuery — it must always execute against the selected
/// DbServer/database via ITargetDbExecutor, never against the caller's own tenant DB via
/// IQueryExecutor/LoginDTO. Ad-hoc queries are inherently read-only, so a registered
/// ClientDatabase routes through the least-privilege ReadOnly contained user. The unregistered-
/// database fallback (raw DbServer admin credential, no contained-login role to route through)
/// requires a per-user role grant (SW.MSWUSERRESOURCEROLE) to exist at all — the one enforcement
/// call site in this module that is fail-closed by default, not fail-open.
/// </summary>
public class SwQueryBLLTests
{
    private static (SwQueryBLL Svc, Mock<IDbServerBLL> DbServerBLL, Mock<IClientDatabaseBLL> ClientDatabaseBLL,
        Mock<ITargetDbExecutor> TargetDbExecutor, Mock<IUserResourceRoleBLL> UserResourceRoleBLL) BuildService()
    {
        var dbServerBLL       = new Mock<IDbServerBLL>();
        var clientDatabaseBLL = new Mock<IClientDatabaseBLL>();
        var targetDbExecutor  = new Mock<ITargetDbExecutor>();
        var userResourceRoleBLL = new Mock<IUserResourceRoleBLL>();
        var historyDal        = new Mock<IQueryHistoryDAL>();

        dbServerBLL.Setup(d => d.GetById(It.IsAny<int>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync(new DbServerDTO { DbServerId = 1, HostName = "sql01" });

        historyDal.Setup(h => h.Insert(It.IsAny<QueryHistoryInsertDTO>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync(1);

        var svc = new SwQueryBLL(
            dbServerBLL.Object, clientDatabaseBLL.Object, targetDbExecutor.Object, userResourceRoleBLL.Object,
            historyDal.Object, NullLogger<SwQueryBLL>.Instance);

        return (svc, dbServerBLL, clientDatabaseBLL, targetDbExecutor, userResourceRoleBLL);
    }

    private static LoginDTO TestLogin() => new() { ClientId = 42, UserId = -1 };

    [Fact]
    public async Task Test_ExecuteAdHocQuery_RegisteredClientDatabase_UsesReadOnlyRole()
    {
        var (svc, _, clientDatabaseBLL, targetDbExecutor, _) = BuildService();

        clientDatabaseBLL.Setup(c => c.GetByServerAndDatabaseName(1, "acme01", It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync(new ClientDatabaseDTO { ClientDbId = 5, ClientDbCode = "acme01", DatabaseName = "acme01", DbServerId = 1 });
        targetDbExecutor.Setup(t => t.BuildConnectionStringForRoleAsync(
                It.IsAny<DbServerDTO>(), It.IsAny<ClientDatabaseDTO>(), ClientDbLoginRole.ReadOnly, It.IsAny<CancellationToken>()))
            .ReturnsAsync("role-conn-str");
        targetDbExecutor.Setup(t => t.ExecuteQueryAsync("role-conn-str", "SELECT 1", It.IsAny<Dictionary<string, object?>>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync(new List<Dictionary<string, object?>> { new() { ["Col"] = 1 } });

        var result = await svc.ExecuteAdHocQuery("SELECT 1", new Dictionary<string, object?>(), 1, "acme01", TestLogin(), CancellationToken.None);

        Assert.Single(result);
        targetDbExecutor.Verify(t => t.BuildConnectionStringForRoleAsync(
            It.IsAny<DbServerDTO>(), It.IsAny<ClientDatabaseDTO>(), ClientDbLoginRole.ReadOnly, It.IsAny<CancellationToken>()), Times.Once);
        targetDbExecutor.Verify(t => t.BuildConnectionString(It.IsAny<DbServerDTO>(), It.IsAny<string>(), null, null), Times.Never);
    }

    [Fact]
    public async Task Test_ExecuteAdHocQuery_ClientDbIdProvided_TakesPrecedenceOverDbServerIdAndDatabaseName()
    {
        var (svc, dbServerBLL, clientDatabaseBLL, targetDbExecutor, _) = BuildService();

        // ClientDbId resolves to a DIFFERENT DbServerId than the one passed in — proving
        // clientDbId is authoritative and dbServerId/databaseName are ignored once it's given
        // (exactly the real-world case: FE always knows ClientDbId once fixed, and any stale
        // DbServerId it also sends must not silently override the real target).
        clientDatabaseBLL.Setup(c => c.GetById(5, It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync(new ClientDatabaseDTO { ClientDbId = 5, ClientDbCode = "acme01", DatabaseName = "acme01", DbServerId = 99 });
        dbServerBLL.Setup(d => d.GetById(99, It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync(new DbServerDTO { DbServerId = 99, HostName = "sql-real" });
        targetDbExecutor.Setup(t => t.BuildConnectionStringForRoleAsync(
                It.Is<DbServerDTO>(s => s.DbServerId == 99), It.IsAny<ClientDatabaseDTO>(), ClientDbLoginRole.ReadOnly, It.IsAny<CancellationToken>()))
            .ReturnsAsync("role-conn-str");
        targetDbExecutor.Setup(t => t.ExecuteQueryAsync("role-conn-str", "SELECT 1", It.IsAny<Dictionary<string, object?>>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync(new List<Dictionary<string, object?>>());

        await svc.ExecuteAdHocQuery(
            "SELECT 1", new Dictionary<string, object?>(), dbServerId: 1, databaseName: null,
            TestLogin(), CancellationToken.None, clientDbId: 5);

        clientDatabaseBLL.Verify(c => c.GetByServerAndDatabaseName(
            It.IsAny<int>(), It.IsAny<string>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()), Times.Never);
        targetDbExecutor.Verify(t => t.BuildConnectionStringForRoleAsync(
            It.Is<DbServerDTO>(s => s.DbServerId == 99), It.IsAny<ClientDatabaseDTO>(), ClientDbLoginRole.ReadOnly, It.IsAny<CancellationToken>()), Times.Once);
    }

    [Fact]
    public async Task Test_ExecuteAdHocQuery_UnregisteredDatabase_WithGrant_FallsBackToPlainConnectionString()
    {
        var (svc, _, clientDatabaseBLL, targetDbExecutor, userResourceRoleBLL) = BuildService();

        clientDatabaseBLL.Setup(c => c.GetByServerAndDatabaseName(1, "master", It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync((ClientDatabaseDTO?)null);
        userResourceRoleBLL.Setup(u => u.GetEffectiveRole(It.IsAny<int>(), 1, null, It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync((ClientDbLoginRole?)ClientDbLoginRole.ReadOnly);
        targetDbExecutor.Setup(t => t.BuildConnectionString(It.IsAny<DbServerDTO>(), "master", null, null))
            .Returns("admin-conn-str");
        targetDbExecutor.Setup(t => t.ExecuteQueryAsync("admin-conn-str", "SELECT 1", It.IsAny<Dictionary<string, object?>>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync(new List<Dictionary<string, object?>>());

        await svc.ExecuteAdHocQuery("SELECT 1", new Dictionary<string, object?>(), 1, "master", TestLogin(), CancellationToken.None);

        targetDbExecutor.Verify(t => t.BuildConnectionString(It.IsAny<DbServerDTO>(), "master", null, null), Times.Once);
        targetDbExecutor.Verify(t => t.BuildConnectionStringForRoleAsync(
            It.IsAny<DbServerDTO>(), It.IsAny<ClientDatabaseDTO>(), It.IsAny<ClientDbLoginRole>(), It.IsAny<CancellationToken>()), Times.Never);
    }

    [Fact]
    public async Task Test_ExecuteAdHocQuery_UnregisteredDatabase_NoGrant_ThrowsUnauthorized()
    {
        // Unlike ChangeRequestBLL/ProvisioningBLL (fail-open when no grant is configured), this
        // one call site is fail-closed by default — it's the single most dangerous path in this
        // BLL (a flat DbServer admin credential, no contained-login role to route through at
        // all), so "nothing configured yet" must not silently allow it.
        var (svc, _, clientDatabaseBLL, targetDbExecutor, userResourceRoleBLL) = BuildService();

        clientDatabaseBLL.Setup(c => c.GetByServerAndDatabaseName(1, "master", It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync((ClientDatabaseDTO?)null);
        userResourceRoleBLL.Setup(u => u.GetEffectiveRole(It.IsAny<int>(), It.IsAny<int?>(), It.IsAny<int?>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync((ClientDbLoginRole?)null);

        await Assert.ThrowsAsync<UnauthorizedAccessException>(() =>
            svc.ExecuteAdHocQuery("SELECT 1", new Dictionary<string, object?>(), 1, "master", TestLogin(), CancellationToken.None));

        targetDbExecutor.Verify(t => t.BuildConnectionString(It.IsAny<DbServerDTO>(), It.IsAny<string>(), null, null), Times.Never);
    }
}