using System.Security.Cryptography;
using System.Text.RegularExpressions;
using Dapper;
using SwBLL.ServerConfig;
using GB5Shared.DTO.Framework.Login;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using SwBLL.ClientDatabase;
using SwDAL.DTO.ClientDatabase;
using SwDAL.DTO.DbServer;
using SwDAL.Enums;
using VaultSharp;

namespace SwBLL.Provisioning;

/// <summary>
/// SQL Server implementation of IClientDatabaseProvisioner. Postgres is deliberately out of
/// scope for this release (DbType.PostgreSQL throws NotSupportedException) — matches this
/// module's own "harden before extending" sequencing rather than expanding scope on two axes
/// (multi-dialect DB creation + per-database login isolation) in the same pass.
///
/// Every operation here runs against SQL Server's admin ("master") connection or the freshly
/// created target database directly via Microsoft.Data.SqlClient/Dapper — deliberately NOT
/// through ITargetDbExecutor's ExecuteScriptAsync/ExecuteQueryAsync (those are scoped to
/// already-existing target application databases and enforce a SELECT-only mode that RESTORE/
/// CREATE/ALTER DATABASE statements don't fit), except for the contained-user CREATE USER
/// script, which genuinely is just DDL against the (now-existing) target database.
/// </summary>
public class ClientDatabaseProvisioner : IClientDatabaseProvisioner
{
    private static readonly Regex ValidIdentifier = new(@"^[A-Za-z_][A-Za-z0-9_]*$", RegexOptions.Compiled);
    private static readonly Regex ValidBackupFileName = new(@"^[A-Za-z0-9_\-\.]+\.bak$", RegexOptions.Compiled | RegexOptions.IgnoreCase);

    private readonly ITargetDbExecutor              _targetDbExecutor;
    private readonly IProvisioningBLL                _provisioningBLL;
    private readonly IClientDatabaseBLL              _clientDatabaseBLL;
    private readonly IServerConfigBLL                _serverConfigBLL;
    private readonly IVaultClient                    _vaultClient;
    private readonly SqlServerProvisioningOptions    _options;
    private readonly ILogger<ClientDatabaseProvisioner> _logger;

    public ClientDatabaseProvisioner(
        ITargetDbExecutor targetDbExecutor,
        IProvisioningBLL provisioningBLL,
        IClientDatabaseBLL clientDatabaseBLL,
        IServerConfigBLL serverConfigBLL,
        IVaultClient vaultClient,
        IOptions<SqlServerProvisioningOptions> options,
        ILogger<ClientDatabaseProvisioner> logger)
    {
        _targetDbExecutor  = targetDbExecutor;
        _provisioningBLL   = provisioningBLL;
        _clientDatabaseBLL = clientDatabaseBLL;
        _serverConfigBLL   = serverConfigBLL;
        _vaultClient       = vaultClient;
        _options           = options.Value;
        _logger            = logger;
    }

    public async Task CreateFromSnapshotAsync(
        ClientDatabaseDTO clientDb, DbServerDTO server, string templateBackupRef,
        LoginDTO login, CancellationToken ct)
    {
        EnsureSqlServer(server);
        var databaseName = EnsureValidIdentifier(clientDb.DatabaseName, nameof(clientDb.DatabaseName));
        var clientDbCode = EnsureValidIdentifier(clientDb.ClientDbCode, nameof(clientDb.ClientDbCode));

        if (!ValidBackupFileName.IsMatch(templateBackupRef))
            throw new ArgumentException(
                "TemplateBackupRef must be a plain .bak filename (no path segments).", nameof(templateBackupRef));

        _logger.LogInformation(
            "Creating client database {DatabaseName} from snapshot {TemplateBackupRef} on server {DbServerId}",
            databaseName, templateBackupRef, server.DbServerId);

        var adminConnStr = _targetDbExecutor.BuildAdminConnectionString(server);

        await EnsureDatabaseDoesNotExistAsync(adminConnStr, databaseName, ct).ConfigureAwait(false);

        var backupPath   = CombinePath(_options.BackupSourcePath, templateBackupRef);

        var fileList = await QueryBackupFileListAsync(adminConnStr, backupPath, ct).ConfigureAwait(false);
        var dataFile = fileList.FirstOrDefault(f => f.Type.Equals("D", StringComparison.OrdinalIgnoreCase))
            ?? throw new InvalidOperationException($"Backup '{templateBackupRef}' has no data file entry.");
        var logFile = fileList.FirstOrDefault(f => f.Type.Equals("L", StringComparison.OrdinalIgnoreCase))
            ?? throw new InvalidOperationException($"Backup '{templateBackupRef}' has no log file entry.");

        var dataDest = CombinePath(_options.DataFileDestinationPath, $"{databaseName}.mdf");
        var logDest  = CombinePath(_options.LogFileDestinationPath, $"{databaseName}_log.ldf");

        var restoreSql = $@"
RESTORE DATABASE [{databaseName}]
FROM DISK = N'{EscapeLiteral(backupPath)}'
WITH MOVE N'{EscapeLiteral(dataFile.LogicalName)}' TO N'{EscapeLiteral(dataDest)}',
     MOVE N'{EscapeLiteral(logFile.LogicalName)}' TO N'{EscapeLiteral(logDest)}',
     REPLACE, STATS = 10;";

        await ExecuteAdminStatementAsync(adminConnStr, restoreSql, ct).ConfigureAwait(false);

        await AssertDatabaseDefaultsAsync(
            adminConnStr, clientDb, databaseName, dataFile.LogicalName, logFile.LogicalName, ct)
            .ConfigureAwait(false);

        var appCredential = await CreateContainedUsersAndStoreCredentialsAsync(
            clientDb, server, databaseName, clientDbCode, login, ct)
            .ConfigureAwait(false);

        await RegisterAndLinkServerConfigAsync(clientDb, server, databaseName, appCredential, login, ct)
            .ConfigureAwait(false);
    }

    public async Task CreateFromScriptsAsync(
        ClientDatabaseDTO clientDb, DbServerDTO server, int baselineUpgradePackageId,
        LoginDTO login, CancellationToken ct)
    {
        EnsureSqlServer(server);
        var databaseName = EnsureValidIdentifier(clientDb.DatabaseName, nameof(clientDb.DatabaseName));
        var clientDbCode = EnsureValidIdentifier(clientDb.ClientDbCode, nameof(clientDb.ClientDbCode));

        _logger.LogInformation(
            "Creating empty client database {DatabaseName} for baseline UpgradePackage {PackageId} on server {DbServerId}",
            databaseName, baselineUpgradePackageId, server.DbServerId);

        var adminConnStr = _targetDbExecutor.BuildAdminConnectionString(server);

        await EnsureDatabaseDoesNotExistAsync(adminConnStr, databaseName, ct).ConfigureAwait(false);

        await ExecuteAdminStatementAsync(adminConnStr, $"CREATE DATABASE [{databaseName}];", ct).ConfigureAwait(false);

        // Plain CREATE DATABASE with no explicit ON PRIMARY/LOG ON clause always names the
        // primary data file after the database itself and the log file "{name}_log" — SQL
        // Server's own default, not something this code chooses.
        var dataLogicalName = databaseName;
        var logLogicalName  = $"{databaseName}_log";

        await AssertDatabaseDefaultsAsync(
            adminConnStr, clientDb, databaseName, dataLogicalName, logLogicalName, ct)
            .ConfigureAwait(false);

        var appCredential = await CreateContainedUsersAndStoreCredentialsAsync(
            clientDb, server, databaseName, clientDbCode, login, ct)
            .ConfigureAwait(false);

        await RegisterAndLinkServerConfigAsync(clientDb, server, databaseName, appCredential, login, ct)
            .ConfigureAwait(false);

        // Reuse the existing, unmodified DDL-deployment flow for the baseline schema — no new
        // script-execution logic needed here at all.
        await _provisioningBLL.ProvisionClientDatabase(clientDb.ClientDbId, baselineUpgradePackageId, login, ct)
            .ConfigureAwait(false);
    }

    // ── DB-level defaults (recovery model, PAGE_VERIFY, autogrowth, containment) ────────────

    private async Task AssertDatabaseDefaultsAsync(
        string adminConnStr, ClientDatabaseDTO clientDb, string databaseName,
        string dataLogicalName, string logLogicalName, CancellationToken ct)
    {
        // FULL recovery for the main OLTP database (point-in-time recovery matters for real
        // transactional client data); SIMPLE for Report/Archive — less critical, avoids
        // unnecessary log growth/backup overhead on databases that are themselves derived.
        var recoveryModel = (DatabaseRole)clientDb.DatabaseRole == DatabaseRole.Main ? "FULL" : "SIMPLE";

        var sql = $@"
ALTER DATABASE [{databaseName}] SET RECOVERY {recoveryModel};
ALTER DATABASE [{databaseName}] SET PAGE_VERIFY CHECKSUM;
ALTER DATABASE [{databaseName}] SET AUTO_SHRINK OFF;
ALTER DATABASE [{databaseName}] SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE [{databaseName}] SET CONTAINMENT = PARTIAL;
ALTER DATABASE [{databaseName}] MODIFY FILE (NAME = N'{EscapeLiteral(dataLogicalName)}', FILEGROWTH = {_options.DataFileAutogrowthMB}MB);
ALTER DATABASE [{databaseName}] MODIFY FILE (NAME = N'{EscapeLiteral(logLogicalName)}', FILEGROWTH = {_options.LogFileAutogrowthMB}MB);";

        // READ_COMMITTED_SNAPSHOT ON is deliberately NOT asserted here — it changes transaction
        // semantics platform-wide and is left as an explicit decision for whoever operates this
        // server, not something creation silently defaults.

        await ExecuteAdminStatementAsync(adminConnStr, sql, ct).ConfigureAwait(false);
    }

    // ── Registering the new database with the legacy MSERVER/MSERVERCONFIG registry ────────

    /// <summary>
    /// Makes a freshly created database visible to the rest of GB5 at runtime: registers it in
    /// MSERVER/MSERVERCONFIG (the table ReportConnectionResolver/ServerConfigCache actually read),
    /// storing the {clientdb}_app contained-user credential as the routable connection, then for
    /// non-Main roles links the new ServerConfigId back onto the sibling Main row's
    /// ReportServerConfigId/ArchiveServerConfigId so ReportConnectionResolver picks it up with no
    /// changes to that resolver itself (§11.5/§11.6 of the Thread 6 plan).
    /// </summary>
    private async Task RegisterAndLinkServerConfigAsync(
        ClientDatabaseDTO clientDb, DbServerDTO server, string databaseName,
        (string Username, string Password) appCredential, LoginDTO login, CancellationToken ct)
    {
        var role = (DatabaseRole)clientDb.DatabaseRole;

        // New convention, additive to TCMS's existing 1=TEST value — see MServerConfigWriteDTO's
        // doc comment. 0=Production/OLTP, 2=Report, 3=Archive.
        byte dbInstanceType = role switch
        {
            DatabaseRole.Main    => (byte)0,
            DatabaseRole.Report  => (byte)2,
            DatabaseRole.Archive => (byte)3,
            _ => throw new NotSupportedException($"Unhandled DatabaseRole {role}.")
        };

        var request = new RegisterServerConfigRequest
        {
            ConnectionName    = $"{clientDb.ClientDbCode}-{role}".ToUpperInvariant(),
            ServerMachineName = server.HostName ?? throw new InvalidOperationException(
                $"DbServerId {server.DbServerId} has no HostName — cannot register MSERVER."),
            DatabaseType      = server.DbType,
            DatabaseName      = databaseName,
            DatabaseUserName  = appCredential.Username,
            DatabasePassword  = appCredential.Password,
            DatabasePort      = server.Port.ToString(),
            DbInstanceType    = dbInstanceType,
            ClientId          = clientDb.TenantId
        };

        var serverConfigId = await _serverConfigBLL.RegisterDatabaseAsync(request, login, ct)
            .ConfigureAwait(false);

        await _clientDatabaseBLL.UpdateServerConfigIdAsync(clientDb.ClientDbId, serverConfigId, login, ct)
            .ConfigureAwait(false);

        if (role != DatabaseRole.Main)
        {
            var mainServerConfigId = await _clientDatabaseBLL
                .GetMainServerConfigIdForModelAsync(clientDb.DbModelId, login, ct)
                .ConfigureAwait(false);

            if (mainServerConfigId is null)
            {
                _logger.LogWarning(
                    "Client database {ClientDbId} (role {Role}) registered as ServerConfigId {ServerConfigId}, " +
                    "but no Main-role sibling has a ServerConfigId yet for DbModelId {DbModelId} — " +
                    "ReportConnectionResolver will not route to it until the Main database is registered too.",
                    clientDb.ClientDbId, role, serverConfigId, clientDb.DbModelId);
                return;
            }

            await _serverConfigBLL.LinkReportOrArchiveAsync(
                mainServerConfigId.Value, serverConfigId, isArchive: role == DatabaseRole.Archive, ct)
                .ConfigureAwait(false);
        }
    }

    // ── Per-database contained users (dba / app / readonly) ─────────────────────────────────

    private async Task<(string Username, string Password)> CreateContainedUsersAndStoreCredentialsAsync(
        ClientDatabaseDTO clientDb, DbServerDTO server, string databaseName, string clientDbCode,
        LoginDTO login, CancellationToken ct)
    {
        // Contained database users (CONTAINMENT = PARTIAL, asserted in AssertDatabaseDefaultsAsync)
        // authenticate entirely inside this one database — there is no server-level login for a
        // credential here to even attempt using against a different client's database. This is
        // the structural fix for "a user with one DB's password should not be able to access
        // other DBs": it's not a permission to remember to deny, it's architecturally impossible.
        var dbaLogin      = $"{clientDbCode}_dba";
        var appLogin      = $"{clientDbCode}_app";
        var readOnlyLogin = $"{clientDbCode}_readonly";

        var dbaPassword      = GeneratePassword();
        var appPassword      = GeneratePassword();
        var readOnlyPassword = GeneratePassword();

        var createUsersSql = $@"
CREATE USER [{dbaLogin}] WITH PASSWORD = N'{EscapeLiteral(dbaPassword)}';
ALTER ROLE db_owner ADD MEMBER [{dbaLogin}];

CREATE USER [{appLogin}] WITH PASSWORD = N'{EscapeLiteral(appPassword)}';
ALTER ROLE db_datareader ADD MEMBER [{appLogin}];
ALTER ROLE db_datawriter ADD MEMBER [{appLogin}];

CREATE USER [{readOnlyLogin}] WITH PASSWORD = N'{EscapeLiteral(readOnlyPassword)}';
ALTER ROLE db_datareader ADD MEMBER [{readOnlyLogin}];";
        // Note: db_datareader alone never restricts a login's ability to create *local* #temp
        // tables in tempdb — that's a tempdb-level default granted to every login, not something
        // requiring a grant here. Nothing above touches it.

        var targetConnStr = _targetDbExecutor.BuildConnectionString(server, databaseName);
        await _targetDbExecutor.ExecuteScriptAsync(targetConnStr, createUsersSql, ct).ConfigureAwait(false);

        var dbaVaultPath      = $"sqlworkbench/clientdb/{clientDb.ClientDbId}/dba-password";
        var appVaultPath      = $"sqlworkbench/clientdb/{clientDb.ClientDbId}/app-password";
        var readOnlyVaultPath = $"sqlworkbench/clientdb/{clientDb.ClientDbId}/readonly-password";

        await WriteSecretAsync(dbaVaultPath, dbaPassword, ct).ConfigureAwait(false);
        await WriteSecretAsync(appVaultPath, appPassword, ct).ConfigureAwait(false);
        await WriteSecretAsync(readOnlyVaultPath, readOnlyPassword, ct).ConfigureAwait(false);

        await _clientDatabaseBLL.UpdateCredentialVaultPathsAsync(
            clientDb.ClientDbId, dbaVaultPath, appVaultPath, readOnlyVaultPath, login, ct)
            .ConfigureAwait(false);

        _logger.LogInformation(
            "Created contained users {DbaLogin}/{AppLogin}/{ReadOnlyLogin} for client database {ClientDbId}",
            dbaLogin, appLogin, readOnlyLogin, clientDb.ClientDbId);

        return (appLogin, appPassword);
    }

    // ── Helpers ──────────────────────────────────────────────────────────────────────────────

    /// <summary>Guards against RESTORE ... WITH REPLACE silently overwriting an existing database,
    /// or CREATE DATABASE racing/colliding with one — both would otherwise proceed against a name
    /// collision with no warning. A collision here means an upstream naming/uniqueness problem
    /// (e.g. a stale MSWCLIENTDATABASE row, or two clients assigned the same DatabaseName) that
    /// must be resolved explicitly, never silently overwritten.</summary>
    private async Task EnsureDatabaseDoesNotExistAsync(string adminConnStr, string databaseName, CancellationToken ct)
    {
        if (await _targetDbExecutor.DatabaseExistsAsync(adminConnStr, databaseName, ct).ConfigureAwait(false))
            throw new InvalidOperationException(
                $"A database named '{databaseName}' already exists on this server — refusing to proceed. " +
                "Resolve the naming collision (choose a different DatabaseName, or explicitly handle the " +
                "existing database out-of-band) before retrying.");
    }

    private static void EnsureSqlServer(DbServerDTO server)
    {
        if (server.DbType != (byte)SwDAL.Enums.DbType.SqlServer)
            throw new NotSupportedException(
                $"Client database creation only supports SQL Server in this release (DbServerId {server.DbServerId} is DbType {server.DbType}).");
    }

    private static string EnsureValidIdentifier(string value, string paramName)
    {
        if (string.IsNullOrWhiteSpace(value) || !ValidIdentifier.IsMatch(value))
            throw new ArgumentException(
                $"'{value}' is not a valid identifier (letters/digits/underscore only, must not start with a digit).",
                paramName);
        return value;
    }

    private static string EscapeLiteral(string value) => value.Replace("'", "''");

    private static string CombinePath(string directory, string fileName)
        => $"{directory.TrimEnd('/', '\\')}\\{fileName}";

    private static string GeneratePassword()
    {
        // 24 random bytes -> Base64 gives upper/lower/digit/symbol variety, comfortably clearing
        // SQL Server's default CHECK_POLICY complexity requirement (3 of 4 categories).
        var bytes = RandomNumberGenerator.GetBytes(24);
        return Convert.ToBase64String(bytes);
    }

    private async Task WriteSecretAsync(string vaultPath, string value, CancellationToken ct)
        => await _vaultClient.V1.Secrets.KeyValue.V2.WriteSecretAsync(
            path: vaultPath, data: new Dictionary<string, object> { ["value"] = value }, mountPoint: "secret")
            .ConfigureAwait(false);

    private async Task<List<BackupFileListEntry>> QueryBackupFileListAsync(
        string adminConnStr, string backupPath, CancellationToken ct)
    {
        await using var conn = new SqlConnection(adminConnStr);
        await conn.OpenAsync(ct).ConfigureAwait(false);

        var sql = $"RESTORE FILELISTONLY FROM DISK = N'{EscapeLiteral(backupPath)}';";
        var rows = await conn.QueryAsync<BackupFileListEntry>(
            new CommandDefinition(sql, cancellationToken: ct, commandTimeout: _options.ProvisioningCommandTimeoutSeconds))
            .ConfigureAwait(false);
        return rows.ToList();
    }

    private async Task ExecuteAdminStatementAsync(string adminConnStr, string sql, CancellationToken ct)
    {
        await using var conn = new SqlConnection(adminConnStr);
        await conn.OpenAsync(ct).ConfigureAwait(false);
        await conn.ExecuteAsync(
            new CommandDefinition(sql, cancellationToken: ct, commandTimeout: _options.ProvisioningCommandTimeoutSeconds))
            .ConfigureAwait(false);
    }

    /// <summary>Subset of RESTORE FILELISTONLY's real result columns — Dapper maps by name and
    /// ignores the many other columns that statement returns.</summary>
    private sealed class BackupFileListEntry
    {
        public string LogicalName  { get; set; } = string.Empty;
        public string PhysicalName { get; set; } = string.Empty;
        public string Type         { get; set; } = string.Empty; // "D" = data, "L" = log
    }
}