using SwBLL.ServerConfig;
using GB5Shared.DTO.Framework.Login;
using Microsoft.Extensions.Logging.Abstractions;
using Microsoft.Extensions.Options;
using Moq;
using SwBLL.ClientDatabase;
using SwBLL.Provisioning;
using SwDAL.DTO.ClientDatabase;
using SwDAL.DTO.DbServer;
using VaultSharp;
using Xunit;

namespace SwTests;

/// <summary>
/// Covers ClientDatabaseProvisioner's guard clauses — the identifier/DbType/backup-filename
/// validation that must reject bad input BEFORE any RESTORE/CREATE DATABASE statement is ever
/// built, since ClientDbCode/DatabaseName are interpolated directly into DDL text (database/file
/// names can't be SQL-parameterized). None of these tests need a real SQL Server or Vault —
/// every case here is expected to throw before ITargetDbExecutor or IVaultClient is ever touched.
/// </summary>
public class ClientDatabaseProvisionerTests
{
    private static (ClientDatabaseProvisioner Svc, Mock<ITargetDbExecutor> TargetDbExecutor)
        BuildService()
    {
        var targetDbExecutor  = new Mock<ITargetDbExecutor>();
        var provisioningBLL   = new Mock<SwBLL.Provisioning.IProvisioningBLL>();
        var clientDatabaseBLL = new Mock<IClientDatabaseBLL>();
        var serverConfigBLL   = new Mock<IServerConfigBLL>(); // never touched by the guard tests below
        var vaultClient       = new Mock<IVaultClient>(); // never touched by the guard tests below
        var options           = Options.Create(new SqlServerProvisioningOptions
        {
            BackupSourcePath        = "D:\\Backups",
            DataFileDestinationPath = "D:\\Data",
            LogFileDestinationPath  = "D:\\Logs"
        });

        var svc = new ClientDatabaseProvisioner(
            targetDbExecutor.Object, provisioningBLL.Object, clientDatabaseBLL.Object,
            serverConfigBLL.Object, vaultClient.Object, options, NullLogger<ClientDatabaseProvisioner>.Instance);

        return (svc, targetDbExecutor);
    }

    private static ClientDatabaseDTO ValidClientDb(string dbCode = "acme01", string dbName = "acme01") => new()
    {
        ClientDbId   = 1,
        ClientDbCode = dbCode,
        ClientDbName = "Acme Corp",
        DatabaseName = dbName,
        DbServerId   = 1,
        DbModelId    = 1,
        DatabaseRole = 0
    };

    private static DbServerDTO SqlServerServer() => new() { DbServerId = 1, DbType = 0, HostName = "sql01" };
    private static DbServerDTO PostgresServer()  => new() { DbServerId = 2, DbType = 1, HostName = "pg01" };

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

    [Theory]
    [InlineData("acme; DROP TABLE Users--")]
    [InlineData("1acme")]
    [InlineData("acme db")]
    [InlineData("")]
    public async Task Test_CreateFromScriptsAsync_InvalidDatabaseName_ThrowsBeforeAnyDbCall(string badName)
    {
        var (svc, targetDbExecutor) = BuildService();
        var clientDb = ValidClientDb(dbName: badName);

        await Assert.ThrowsAsync<ArgumentException>(() =>
            svc.CreateFromScriptsAsync(clientDb, SqlServerServer(), baselineUpgradePackageId: 1, TestLogin(), CancellationToken.None));

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

    [Theory]
    [InlineData("acme; DROP TABLE Users--")]
    [InlineData("1acme")]
    public async Task Test_CreateFromScriptsAsync_InvalidClientDbCode_ThrowsBeforeAnyDbCall(string badCode)
    {
        var (svc, targetDbExecutor) = BuildService();
        var clientDb = ValidClientDb(dbCode: badCode);

        await Assert.ThrowsAsync<ArgumentException>(() =>
            svc.CreateFromScriptsAsync(clientDb, SqlServerServer(), baselineUpgradePackageId: 1, TestLogin(), CancellationToken.None));

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

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

        await Assert.ThrowsAsync<NotSupportedException>(() =>
            svc.CreateFromScriptsAsync(ValidClientDb(), PostgresServer(), baselineUpgradePackageId: 1, TestLogin(), CancellationToken.None));

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

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

        await Assert.ThrowsAsync<NotSupportedException>(() =>
            svc.CreateFromSnapshotAsync(ValidClientDb(), PostgresServer(), "template.bak", TestLogin(), CancellationToken.None));

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

    [Theory]
    [InlineData("../../etc/passwd")]
    [InlineData("template.bak; DROP TABLE Users--")]
    [InlineData("..\\..\\windows\\system32\\template.bak")]
    [InlineData("template.txt")]
    [InlineData("")]
    public async Task Test_CreateFromSnapshotAsync_InvalidTemplateBackupRef_ThrowsBeforeAnyDbCall(string badRef)
    {
        var (svc, targetDbExecutor) = BuildService();

        await Assert.ThrowsAsync<ArgumentException>(() =>
            svc.CreateFromSnapshotAsync(ValidClientDb(), SqlServerServer(), badRef, TestLogin(), CancellationToken.None));

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

    [Fact]
    public async Task Test_CreateFromSnapshotAsync_ValidPlainFilename_PassesGuardsAndReachesTargetDbExecutor()
    {
        var (svc, targetDbExecutor) = BuildService();
        targetDbExecutor.Setup(t => t.BuildAdminConnectionString(It.IsAny<DbServerDTO>())).Returns("admin-conn-str");
        targetDbExecutor.Setup(t => t.DatabaseExistsAsync(It.IsAny<string>(), It.IsAny<string>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync(false);

        // Guards all pass — it should proceed far enough to ask TargetDbExecutor for the admin
        // connection string (and then fail later trying to actually open it, which is expected
        // and fine here — this test only proves the guard clauses don't false-positive on valid input).
        await Assert.ThrowsAnyAsync<Exception>(() =>
            svc.CreateFromSnapshotAsync(ValidClientDb(), SqlServerServer(), "acme-template.bak", TestLogin(), CancellationToken.None));

        targetDbExecutor.Verify(t => t.BuildAdminConnectionString(It.IsAny<DbServerDTO>()), Times.Once);
    }

    // ── Existence check — RESTORE ... WITH REPLACE / CREATE DATABASE must never silently
    // overwrite or race a name collision with an existing database ──────────────────────────

    [Fact]
    public async Task Test_CreateFromSnapshotAsync_DatabaseAlreadyExists_ThrowsBeforeRestoring()
    {
        var (svc, targetDbExecutor) = BuildService();
        targetDbExecutor.Setup(t => t.BuildAdminConnectionString(It.IsAny<DbServerDTO>())).Returns("admin-conn-str");
        targetDbExecutor.Setup(t => t.DatabaseExistsAsync("admin-conn-str", "acme01", It.IsAny<CancellationToken>()))
            .ReturnsAsync(true);

        await Assert.ThrowsAsync<InvalidOperationException>(() =>
            svc.CreateFromSnapshotAsync(ValidClientDb(), SqlServerServer(), "acme-template.bak", TestLogin(), CancellationToken.None));

        targetDbExecutor.Verify(t => t.DatabaseExistsAsync("admin-conn-str", "acme01", It.IsAny<CancellationToken>()), Times.Once);
    }

    [Fact]
    public async Task Test_CreateFromScriptsAsync_DatabaseAlreadyExists_ThrowsBeforeCreating()
    {
        var (svc, targetDbExecutor) = BuildService();
        targetDbExecutor.Setup(t => t.BuildAdminConnectionString(It.IsAny<DbServerDTO>())).Returns("admin-conn-str");
        targetDbExecutor.Setup(t => t.DatabaseExistsAsync("admin-conn-str", "acme01", It.IsAny<CancellationToken>()))
            .ReturnsAsync(true);

        await Assert.ThrowsAsync<InvalidOperationException>(() =>
            svc.CreateFromScriptsAsync(ValidClientDb(), SqlServerServer(), baselineUpgradePackageId: 1, TestLogin(), CancellationToken.None));

        targetDbExecutor.Verify(t => t.DatabaseExistsAsync("admin-conn-str", "acme01", It.IsAny<CancellationToken>()), Times.Once);
        targetDbExecutor.Verify(t => t.ExecuteScriptAsync(It.IsAny<string>(), It.IsAny<string>(), It.IsAny<CancellationToken>()), Times.Never);
    }
}