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

namespace SwTests;

/// <summary>
/// Covers ChangeRequestBLL.Execute()'s least-privilege connection selection — each
/// ChangeRequestQueryType must route through TargetDbExecutor.BuildConnectionStringForRoleAsync
/// with the matching ClientDbLoginRole, never the flat DbServer-admin BuildConnectionString
/// (except for ClientProvisioning, which bootstraps the database before contained users exist).
/// </summary>
public class ChangeRequestBLLExecutionRoleTests
{
    private static (ChangeRequestBLL Svc, Mock<IChangeRequestDAL> Dal, Mock<ITargetDbExecutor> TargetDbExecutor)
        BuildService()
    {
        var dal               = new Mock<IChangeRequestDAL>();
        var clientDatabaseBLL = new Mock<IClientDatabaseBLL>();
        var dbServerBLL       = new Mock<IDbServerBLL>();
        var targetDbExecutor  = new Mock<ITargetDbExecutor>();
        var ddlScriptBLL      = new Mock<IDdlScriptBLL>();
        var provisioner       = new Mock<IClientDatabaseProvisioner>();

        dal.Setup(d => d.UpdateStatus(It.IsAny<int>(), It.IsAny<byte>(), It.IsAny<byte>(), It.IsAny<int>(), It.IsAny<string?>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
           .Returns(Task.CompletedTask);
        dal.Setup(d => d.AppendTimeline(It.IsAny<int>(), It.IsAny<CrTimelineEventType>(), It.IsAny<string?>(), It.IsAny<int>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
           .Returns(Task.CompletedTask);

        clientDatabaseBLL.Setup(c => c.GetById(It.IsAny<int>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync(new ClientDatabaseDTO { ClientDbId = 5, ClientDbCode = "acme01", DatabaseName = "acme01", DbServerId = 1, DatabaseRole = 0 });

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

        targetDbExecutor.Setup(t => t.BuildConnectionString(It.IsAny<DbServerDTO>(), It.IsAny<string>()))
            .Returns("admin-conn-str");
        targetDbExecutor.Setup(t => t.BuildConnectionStringForRoleAsync(
                It.IsAny<DbServerDTO>(), It.IsAny<ClientDatabaseDTO>(), It.IsAny<ClientDbLoginRole>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync("role-conn-str");

        var eventLogPublish = new EventLogPublish(null!, NullLogger<EventLogPublish>.Instance);

        var svc = new ChangeRequestBLL(
            dal.Object, clientDatabaseBLL.Object, dbServerBLL.Object, targetDbExecutor.Object,
            ddlScriptBLL.Object, provisioner.Object, new Mock<IUserResourceRoleBLL>().Object,
            eventLogPublish, NullLogger<ChangeRequestBLL>.Instance);

        return (svc, dal, targetDbExecutor);
    }

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

    // Mirrors BuildService() but parameterizes ClientDatabaseDTO.TenantId — needed only by the
    // {{TENANTID}} substitution test below, kept separate rather than widening BuildService()'s
    // own return tuple and touching every existing test in this file.
    private static (ChangeRequestBLL Svc, Mock<IChangeRequestDAL> Dal, Mock<ITargetDbExecutor> TargetDbExecutor)
        BuildServiceWithTenant(int tenantId)
    {
        var dal               = new Mock<IChangeRequestDAL>();
        var clientDatabaseBLL = new Mock<IClientDatabaseBLL>();
        var dbServerBLL       = new Mock<IDbServerBLL>();
        var targetDbExecutor  = new Mock<ITargetDbExecutor>();
        var ddlScriptBLL      = new Mock<IDdlScriptBLL>();
        var provisioner       = new Mock<IClientDatabaseProvisioner>();

        dal.Setup(d => d.UpdateStatus(It.IsAny<int>(), It.IsAny<byte>(), It.IsAny<byte>(), It.IsAny<int>(), It.IsAny<string?>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
           .Returns(Task.CompletedTask);
        dal.Setup(d => d.AppendTimeline(It.IsAny<int>(), It.IsAny<CrTimelineEventType>(), It.IsAny<string?>(), It.IsAny<int>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
           .Returns(Task.CompletedTask);

        clientDatabaseBLL.Setup(c => c.GetById(It.IsAny<int>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync(new ClientDatabaseDTO { ClientDbId = 5, ClientDbCode = "acme01", DatabaseName = "acme01", DbServerId = 1, DatabaseRole = 0, TenantId = tenantId });

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

        targetDbExecutor.Setup(t => t.BuildConnectionStringForRoleAsync(
                It.IsAny<DbServerDTO>(), It.IsAny<ClientDatabaseDTO>(), It.IsAny<ClientDbLoginRole>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync("role-conn-str");

        var eventLogPublish = new EventLogPublish(null!, NullLogger<EventLogPublish>.Instance);

        var svc = new ChangeRequestBLL(
            dal.Object, clientDatabaseBLL.Object, dbServerBLL.Object, targetDbExecutor.Object,
            ddlScriptBLL.Object, provisioner.Object, new Mock<IUserResourceRoleBLL>().Object,
            eventLogPublish, NullLogger<ChangeRequestBLL>.Instance);

        return (svc, dal, targetDbExecutor);
    }

    [Fact]
    public async Task Test_Execute_DdlCr_ScriptContainsTenantIdToken_SubstitutesRealTenantIdBeforeExecuting()
    {
        var (svc, dal, targetDbExecutor) = BuildServiceWithTenant(tenantId: -1399999706);
        var cr = new ChangeRequestDTO
        {
            ChangeRequestId = 203, ClientDatabaseId = 5, CrStatus = ChangeRequestStatus.Approved,
            Category = ChangeRequestCategory.DataMigration, QueryType = ChangeRequestQueryType.DDL
        };
        dal.Setup(d => d.GetById(cr.ChangeRequestId, It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>())).ReturnsAsync(cr);
        dal.Setup(d => d.GetLinkedApprovedDdlScripts(cr.ChangeRequestId, It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
           .ReturnsAsync(new List<DdlScriptDTO> { new() { DdlScriptId = 1, SqlScript = "INSERT INTO MCURRENCY (TENANTID) VALUES ({{TENANTID}})" } });

        string? capturedSql = null;
        targetDbExecutor.Setup(t => t.ExecuteScriptAsync(It.IsAny<string>(), It.IsAny<string>(), It.IsAny<CancellationToken>()))
            .Callback<string, string, CancellationToken>((_, sql, _) => capturedSql = sql)
            .ReturnsAsync(1);

        await svc.Execute(cr.ChangeRequestId, TestLogin(), CancellationToken.None);

        Assert.Equal("INSERT INTO MCURRENCY (TENANTID) VALUES (-1399999706)", capturedSql);
    }

    [Fact]
    public async Task Test_Execute_DdlQueryType_UsesDbaRole()
    {
        var (svc, dal, targetDbExecutor) = BuildService();
        var cr = new ChangeRequestDTO
        {
            ChangeRequestId = 200, ClientDatabaseId = 5, CrStatus = ChangeRequestStatus.Approved,
            Category = ChangeRequestCategory.SchemaChange, QueryType = ChangeRequestQueryType.DDL
        };
        dal.Setup(d => d.GetById(cr.ChangeRequestId, It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>())).ReturnsAsync(cr);
        dal.Setup(d => d.GetLinkedApprovedDdlScripts(cr.ChangeRequestId, It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
           .ReturnsAsync(new List<DdlScriptDTO> { new() { DdlScriptId = 1, SqlScript = "ALTER TABLE Foo ADD Bar INT" } });

        await svc.Execute(cr.ChangeRequestId, TestLogin(), CancellationToken.None);

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

    [Theory]
    [InlineData(ChangeRequestQueryType.Insert)]
    [InlineData(ChangeRequestQueryType.Update)]
    [InlineData(ChangeRequestQueryType.Delete)]
    public async Task Test_Execute_DmlQueryTypes_UseAppRole(ChangeRequestQueryType queryType)
    {
        var (svc, dal, targetDbExecutor) = BuildService();
        var cr = new ChangeRequestDTO
        {
            ChangeRequestId = 201, ClientDatabaseId = 5, CrStatus = ChangeRequestStatus.Approved,
            Category = ChangeRequestCategory.DataMigration, QueryType = queryType, Sql = "UPDATE Foo SET X = 1"
        };
        dal.Setup(d => d.GetById(cr.ChangeRequestId, It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>())).ReturnsAsync(cr);

        await svc.Execute(cr.ChangeRequestId, TestLogin(), CancellationToken.None);

        targetDbExecutor.Verify(t => t.BuildConnectionStringForRoleAsync(
            It.IsAny<DbServerDTO>(), It.IsAny<ClientDatabaseDTO>(), ClientDbLoginRole.App, It.IsAny<CancellationToken>()), Times.Once);
    }

    [Theory]
    [InlineData(ChangeRequestQueryType.Select)]
    [InlineData(ChangeRequestQueryType.Other)]
    public async Task Test_Execute_SelectOrOtherQueryTypes_UseReadOnlyRole(ChangeRequestQueryType queryType)
    {
        var (svc, dal, targetDbExecutor) = BuildService();
        var cr = new ChangeRequestDTO
        {
            ChangeRequestId = 202, ClientDatabaseId = 5, CrStatus = ChangeRequestStatus.Approved,
            Category = ChangeRequestCategory.Performance, QueryType = queryType, Sql = "SELECT 1"
        };
        dal.Setup(d => d.GetById(cr.ChangeRequestId, It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>())).ReturnsAsync(cr);

        await svc.Execute(cr.ChangeRequestId, TestLogin(), CancellationToken.None);

        targetDbExecutor.Verify(t => t.BuildConnectionStringForRoleAsync(
            It.IsAny<DbServerDTO>(), It.IsAny<ClientDatabaseDTO>(), ClientDbLoginRole.ReadOnly, It.IsAny<CancellationToken>()), Times.Once);
    }

    // ── Per-user role-grant capping (SW.MSWUSERRESOURCEROLE) ────────────────
    // Mirrors BuildService() but exposes the IUserResourceRoleBLL mock so these tests can
    // configure a grant — kept separate rather than widening BuildService()'s own return tuple
    // and touching every existing test in this file (same rationale as BuildServiceWithTenant).

    private static (ChangeRequestBLL Svc, Mock<IChangeRequestDAL> Dal, Mock<ITargetDbExecutor> TargetDbExecutor, Mock<IUserResourceRoleBLL> UserResourceRoleBLL)
        BuildServiceWithGrant()
    {
        var dal               = new Mock<IChangeRequestDAL>();
        var clientDatabaseBLL = new Mock<IClientDatabaseBLL>();
        var dbServerBLL       = new Mock<IDbServerBLL>();
        var targetDbExecutor  = new Mock<ITargetDbExecutor>();
        var ddlScriptBLL      = new Mock<IDdlScriptBLL>();
        var provisioner       = new Mock<IClientDatabaseProvisioner>();
        var userResourceRoleBLL = new Mock<IUserResourceRoleBLL>();

        dal.Setup(d => d.UpdateStatus(It.IsAny<int>(), It.IsAny<byte>(), It.IsAny<byte>(), It.IsAny<int>(), It.IsAny<string?>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
           .Returns(Task.CompletedTask);
        dal.Setup(d => d.AppendTimeline(It.IsAny<int>(), It.IsAny<CrTimelineEventType>(), It.IsAny<string?>(), It.IsAny<int>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
           .Returns(Task.CompletedTask);

        clientDatabaseBLL.Setup(c => c.GetById(It.IsAny<int>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync(new ClientDatabaseDTO { ClientDbId = 5, ClientDbCode = "acme01", DatabaseName = "acme01", DbServerId = 1, DatabaseRole = 0 });

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

        targetDbExecutor.Setup(t => t.BuildConnectionStringForRoleAsync(
                It.IsAny<DbServerDTO>(), It.IsAny<ClientDatabaseDTO>(), It.IsAny<ClientDbLoginRole>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync("role-conn-str");

        var eventLogPublish = new EventLogPublish(null!, NullLogger<EventLogPublish>.Instance);

        var svc = new ChangeRequestBLL(
            dal.Object, clientDatabaseBLL.Object, dbServerBLL.Object, targetDbExecutor.Object,
            ddlScriptBLL.Object, provisioner.Object, userResourceRoleBLL.Object,
            eventLogPublish, NullLogger<ChangeRequestBLL>.Instance);

        return (svc, dal, targetDbExecutor, userResourceRoleBLL);
    }

    [Fact]
    public async Task Test_Execute_NoGrantConfigured_FailsOpen_UsesRequestedRoleUncapped()
    {
        var (svc, dal, targetDbExecutor, userResourceRoleBLL) = BuildServiceWithGrant();
        userResourceRoleBLL.Setup(u => u.GetEffectiveRole(It.IsAny<int>(), It.IsAny<int?>(), It.IsAny<int?>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync((ClientDbLoginRole?)null);
        var cr = new ChangeRequestDTO
        {
            ChangeRequestId = 210, ClientDatabaseId = 5, CrStatus = ChangeRequestStatus.Approved,
            Category = ChangeRequestCategory.SchemaChange, QueryType = ChangeRequestQueryType.DDL
        };
        dal.Setup(d => d.GetById(cr.ChangeRequestId, It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>())).ReturnsAsync(cr);
        dal.Setup(d => d.GetLinkedApprovedDdlScripts(cr.ChangeRequestId, It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
           .ReturnsAsync(new List<DdlScriptDTO> { new() { DdlScriptId = 1, SqlScript = "ALTER TABLE Foo ADD Bar INT" } });

        await svc.Execute(cr.ChangeRequestId, TestLogin(), CancellationToken.None);

        // No grant configured at all — brand-new authorization layer, must not lock out a user
        // with nothing configured yet — the DDL request runs uncapped, as Dba, same as before
        // this feature existed.
        targetDbExecutor.Verify(t => t.BuildConnectionStringForRoleAsync(
            It.IsAny<DbServerDTO>(), It.IsAny<ClientDatabaseDTO>(), ClientDbLoginRole.Dba, It.IsAny<CancellationToken>()), Times.Once);
    }

    [Fact]
    public async Task Test_Execute_ReadOnlyGrant_CapsDdlRequestDownToReadOnly()
    {
        var (svc, dal, targetDbExecutor, userResourceRoleBLL) = BuildServiceWithGrant();
        userResourceRoleBLL.Setup(u => u.GetEffectiveRole(It.IsAny<int>(), It.IsAny<int?>(), It.IsAny<int?>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync((ClientDbLoginRole?)ClientDbLoginRole.ReadOnly);
        var cr = new ChangeRequestDTO
        {
            ChangeRequestId = 211, ClientDatabaseId = 5, CrStatus = ChangeRequestStatus.Approved,
            Category = ChangeRequestCategory.SchemaChange, QueryType = ChangeRequestQueryType.DDL
        };
        dal.Setup(d => d.GetById(cr.ChangeRequestId, It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>())).ReturnsAsync(cr);
        dal.Setup(d => d.GetLinkedApprovedDdlScripts(cr.ChangeRequestId, It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
           .ReturnsAsync(new List<DdlScriptDTO> { new() { DdlScriptId = 1, SqlScript = "ALTER TABLE Foo ADD Bar INT" } });

        await svc.Execute(cr.ChangeRequestId, TestLogin(), CancellationToken.None);

        // DDL naturally requires Dba, but this user is only granted ReadOnly — capped down, not
        // upgraded to what the operation would otherwise need. The DDL script itself will then
        // fail against a ReadOnly connection at the DB level, which is the correct outcome for
        // an under-privileged user attempting a schema change.
        targetDbExecutor.Verify(t => t.BuildConnectionStringForRoleAsync(
            It.IsAny<DbServerDTO>(), It.IsAny<ClientDatabaseDTO>(), ClientDbLoginRole.ReadOnly, It.IsAny<CancellationToken>()), Times.Once);
    }

    [Fact]
    public async Task Test_Execute_DbaGrant_DoesNotUpgradeASelectRequest()
    {
        var (svc, dal, targetDbExecutor, userResourceRoleBLL) = BuildServiceWithGrant();
        userResourceRoleBLL.Setup(u => u.GetEffectiveRole(It.IsAny<int>(), It.IsAny<int?>(), It.IsAny<int?>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync((ClientDbLoginRole?)ClientDbLoginRole.Dba);
        var cr = new ChangeRequestDTO
        {
            ChangeRequestId = 212, ClientDatabaseId = 5, CrStatus = ChangeRequestStatus.Approved,
            Category = ChangeRequestCategory.Performance, QueryType = ChangeRequestQueryType.Select, Sql = "SELECT 1"
        };
        dal.Setup(d => d.GetById(cr.ChangeRequestId, It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>())).ReturnsAsync(cr);

        await svc.Execute(cr.ChangeRequestId, TestLogin(), CancellationToken.None);

        // A broad Dba grant elsewhere never upgrades a request that only naturally needs
        // ReadOnly — capping only ever restricts, it's not a privilege-elevation mechanism.
        targetDbExecutor.Verify(t => t.BuildConnectionStringForRoleAsync(
            It.IsAny<DbServerDTO>(), It.IsAny<ClientDatabaseDTO>(), ClientDbLoginRole.ReadOnly, It.IsAny<CancellationToken>()), Times.Once);
    }

    [Fact]
    public async Task Test_Execute_ClientProvisioningCategory_UsesAdminConnectionNotRole()
    {
        var (svc, dal, targetDbExecutor) = BuildService();
        var cr = new ChangeRequestDTO
        {
            ChangeRequestId = 203, ClientDatabaseId = 5, CrStatus = ChangeRequestStatus.Approved,
            Category = ChangeRequestCategory.ClientProvisioning, QueryType = ChangeRequestQueryType.Other,
            ProvisioningMode = ProvisioningMode.FromScripts, BaselineUpgradePackageId = 77
        };
        dal.Setup(d => d.GetById(cr.ChangeRequestId, It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>())).ReturnsAsync(cr);
        dal.Setup(d => d.GetLinkedApprovedDdlScripts(cr.ChangeRequestId, It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
           .ReturnsAsync(Enumerable.Empty<DdlScriptDTO>());

        await svc.Execute(cr.ChangeRequestId, TestLogin(), CancellationToken.None);

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