using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using SwBLL.ClientDatabase;
using SwBLL.DbServer;
using SwBLL.DdlScript;
using SwBLL.UpgradePackage;
using SwBLL.UserResourceRole;
using SwDAL.DTO.Provisioning;
using SwDAL.Enums;
using SwDAL.Query.Provisioning;

namespace SwBLL.Provisioning;

/// <summary>
/// Orchestrates provisioning of a client database:
///  1. Loads the upgrade package DDL lines, ordered by SEQUENCE then DdlScriptId.
///  2. Resolves the DbServer and builds a connection string.
///  3. Executes each Approved DDL script; logs result AFTER each execution with correct status.
///  4. Sets ClientDatabase to Active if all scripts succeed, Failed if any fail.
///
/// Architecture rule (§4.1): injects BLL interfaces only — never foreign DALs.
/// </summary>
public class ProvisioningBLL : IProvisioningBLL
{
    private readonly IClientDatabaseBLL _clientDatabaseBLL;
    private readonly IDdlScriptBLL      _ddlScriptBLL;
    private readonly IUpgradePackageBLL _upgradePackageBLL;
    private readonly IDbServerBLL       _dbServerBLL;
    private readonly ITargetDbExecutor  _targetDbExecutor;
    private readonly IUserResourceRoleBLL _userResourceRoleBLL;
    private readonly IQueryExecutor     _queryExecutor;

    public ProvisioningBLL(
        IClientDatabaseBLL clientDatabaseBLL,
        IDdlScriptBLL ddlScriptBLL,
        IUpgradePackageBLL upgradePackageBLL,
        IDbServerBLL dbServerBLL,
        ITargetDbExecutor targetDbExecutor,
        IUserResourceRoleBLL userResourceRoleBLL,
        IQueryExecutor queryExecutor)
    {
        _clientDatabaseBLL = clientDatabaseBLL;
        _ddlScriptBLL      = ddlScriptBLL;
        _upgradePackageBLL = upgradePackageBLL;
        _dbServerBLL       = dbServerBLL;
        _targetDbExecutor  = targetDbExecutor;
        _userResourceRoleBLL = userResourceRoleBLL;
        _queryExecutor     = queryExecutor;
    }

    public async Task<string> ProvisionClientDatabase(
        int clientDbId, int upgradePackageId, LoginDTO loginDTO, CancellationToken ct)
    {
        // ── 1. Load and validate client database ─────────────────────────

        var clientDb = await _clientDatabaseBLL.GetById(clientDbId, loginDTO, ct)
            ?? throw new InvalidOperationException($"Client database {clientDbId} not found.");

        var server = await _dbServerBLL.GetByIdWithCredentials(clientDb.DbServerId, loginDTO, ct)
            ?? throw new InvalidOperationException($"DB server {clientDb.DbServerId} not found.");

        // Provisioning always needs the flat DbServer admin connection — contained users don't
        // exist yet, there's nothing to route a role-based connection through. Fail-closed only
        // when the caller has an EXPLICIT, insufficient grant on record (e.g. a ReadOnly-only
        // grant attempting to provision) — no grant configured at all is fail-open, consistent
        // with the other enforcement call sites in a module where every endpoint is currently
        // AllowAnonymous().
        var grant = await _userResourceRoleBLL
            .GetEffectiveRole(loginDTO.UserId, clientDb.DbServerId, clientDb.ClientDbId, loginDTO, ct)
            .ConfigureAwait(false);
        if (grant.HasValue && grant.Value != ClientDbLoginRole.Dba)
            throw new UnauthorizedAccessException(
                $"User {loginDTO.UserId} is granted {grant.Value} on this target, which is insufficient to provision a client database (requires Dba).");

        string connStr = _targetDbExecutor.BuildConnectionString(server, clientDb.DatabaseName);

        // ── 2. Load upgrade package DDL lines ────────────────────────────

        var ddlLines = (await _upgradePackageBLL.GetDdlLines(upgradePackageId, loginDTO, ct))
            .ToList();

        if (ddlLines.Count == 0)
            throw new InvalidOperationException(
                $"Upgrade package {upgradePackageId} contains no DDL scripts.");

        // ── 3. Load each DDL script and order by SEQUENCE, then DdlScriptId (§4.4) ──

        var scripts = new List<(int DdlScriptId, string SqlScript, short Sequence)>();

        foreach (var line in ddlLines)
        {
            var script = await _ddlScriptBLL.GetById(line.DdlScriptId, loginDTO, ct);
            if (script is null) continue;

            // Only execute Approved scripts
            if (script.ScriptStatus != (byte)DdlScriptStatus.Approved) continue;

            scripts.Add((script.DdlScriptId, script.SqlScript ?? string.Empty, line.Sequence));
        }

        // Order: Sequence==0 last, otherwise ascending, tie-break by DdlScriptId ASC (§4.4)
        var orderedScripts = scripts
            .OrderBy(s => s.Sequence == 0 ? short.MaxValue : s.Sequence)
            .ThenBy(s => s.DdlScriptId)
            .ToList();

        // ── 4. Execute scripts — log AFTER each, with correct status (§4.3) ──

        bool allSucceeded = true;

        foreach (var (ddlScriptId, sql, _) in orderedScripts)
        {
            try
            {
                string appliedSql = DdlScriptTemplating.SubstituteTenantId(sql, clientDb.TenantId);
                await _targetDbExecutor.ExecuteScriptAsync(connStr, appliedSql, ct).ConfigureAwait(false);

                // Log SUCCESS after successful execution
                await _ddlScriptBLL.LogExecutionAsync(
                    ddlScriptId, clientDbId,
                    (byte)DdlExecStatus.Success,
                    errorMessage: null,
                    loginDTO, ct).ConfigureAwait(false);

                // Provisioning-run-scoped log — carries PackageId/SyncGroupId context that
                // LSWDDLEXECUTION (per-script only) does not.
                await LogProvisioningAsync(
                    clientDbId, upgradePackageId, ddlScriptId,
                    (byte)DdlExecStatus.Success, message: null, errorMessage: null,
                    loginDTO, ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                allSucceeded = false;

                // Log FAILED after failed execution — do not rethrow; continue remaining scripts
                await _ddlScriptBLL.LogExecutionAsync(
                    ddlScriptId, clientDbId,
                    (byte)DdlExecStatus.Failed,
                    errorMessage: ex.Message,
                    loginDTO, ct).ConfigureAwait(false);

                await LogProvisioningAsync(
                    clientDbId, upgradePackageId, ddlScriptId,
                    (byte)DdlExecStatus.Failed, message: null, errorMessage: ex.Message,
                    loginDTO, ct).ConfigureAwait(false);
            }
        }

        // ── 5. Set final ClientDatabase status (§4.5) ────────────────────
        // Active only when ALL scripts succeed; Failed on any failure.

        byte finalStatus = allSucceeded
            ? (byte)ClientDbStatus.Active
            : (byte)ClientDbStatus.Failed;

        await _clientDatabaseBLL.UpdateStatusAsync(clientDbId, finalStatus, loginDTO, ct)
            .ConfigureAwait(false);

        return allSucceeded
            ? $"{SuccessResponse.SaveSuccessMessage} {clientDbId}"
            : $"Provisioning completed with failures. Client database {clientDbId} status set to Failed.";
    }

    // ── Current-version rollup ───────────────────────────────────────────
    // Uses only the existing injected dependencies (_clientDatabaseBLL, _upgradePackageBLL,
    // _queryExecutor) — no new constructor dependency. The provisioning-log read goes straight
    // through _queryExecutor, same as LogProvisioningAsync's write below, per this class's own
    // architecture rule (§4.1: injects BLL interfaces only — never foreign DALs) rather than
    // introducing a new IProvisioningLogDAL for one read query.
    public async Task<ClientVersionRollupDTO> GetCurrentVersion(int clientDbId, LoginDTO loginDTO, CancellationToken ct)
    {
        // ClientDbId is not sign-constrained — like most GB5 identifiers, real registered
        // SW.MSWCLIENTDATABASE rows commonly carry large negative autonumber IDs. Only the
        // unset/default value (0) is treated as "not provided"; existence is otherwise verified
        // below via _clientDatabaseBLL.GetById.
        if (clientDbId == 0) throw new ArgumentException("ClientDbId is required.", nameof(clientDbId));

        var clientDb = await _clientDatabaseBLL.GetById(clientDbId, loginDTO, ct).ConfigureAwait(false)
            ?? throw new InvalidOperationException($"Client database {clientDbId} not found.");

        var result = new ClientVersionRollupDTO { ClientDbId = clientDbId };

        var mostRecent = await _queryExecutor.QuerySingleAsync<AppliedPackageDTO>(
            loginDTO, ProvisioningLogQB.GET_MOST_RECENTLY_APPLIED_PACKAGE,
            new { ClientDbId = clientDbId, TenantId = loginDTO.ClientId }, cancellationToken: ct)
            .ConfigureAwait(false);

        if (mostRecent is null) return result; // nothing fully applied yet — unversioned

        result.MostRecentlyAppliedPackageId = mostRecent.PackageId;

        var lineage = (await _upgradePackageBLL.GetLineage(clientDb.DbModelId, loginDTO, ct).ConfigureAwait(false))
            .ToDictionary(n => n.PackageId);

        int? cursor = mostRecent.PackageId;
        var visited = new HashSet<int>();
        const int maxHops = 500; // defends against a corrupt/cyclic SupersedesPackageId graph

        for (int hop = 0; cursor is int id && hop < maxHops && visited.Add(id); hop++)
        {
            if (!lineage.TryGetValue(id, out var node)) break;
            if (!string.IsNullOrEmpty(node.ReleaseVersion))
            {
                result.CurrentReleaseVersion = node.ReleaseVersion;
                result.ResolvedFromPackageId = node.PackageId;
                result.IsVersioned           = true;
                return result;
            }
            cursor = node.SupersedesPackageId;
        }

        return result; // walked to root (or exhausted the bound) without finding a version — unversioned
    }

    // ── Log Provisioning (direct QueryExecutor — log table, not M-table) ───
    private async Task LogProvisioningAsync(
        int clientDbId, int packageId, int ddlScriptId, byte status,
        string? message, string? errorMessage, LoginDTO loginDTO, CancellationToken ct)
    {
        var logDto = new ProvisioningLogDTO
        {
            ClientDbId   = clientDbId,
            PackageId    = packageId,
            DdlScriptId  = ddlScriptId,
            LogType      = (byte)ProvisioningLogType.DdlExecution,
            LogStatus    = status,
            Message      = message,
            ErrorMessage = errorMessage,
            CreatedOn    = DateTime.UtcNow,
            CreatedById  = loginDTO.UserId,
            TenantId     = loginDTO.ClientId
        };

        await _queryExecutor.ExecuteAsync(loginDTO, ProvisioningLogQB.LOG_INSERT, logDto, cancellationToken: ct)
            .ConfigureAwait(false);
    }
}