using GB5Shared.DaprCache;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Qualifier;
using GB5Shared.EntityHandler;
using GB5Shared.GB5Library.Qualifier;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.ListQuery;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Telemetry;
using Newtonsoft.Json;
using SwDAL.CustomCode.DdlScript;
using SwDAL.CustomCode.UpgradePackage;
using SwDAL.DTO.UpgradePackage;
using static GB5Shared.GB5Constant.Constant;

namespace SwBLL.UpgradePackage;

public class UpgradePackageBLL : IUpgradePackageBLL
{
    private readonly IUpgradePackageDAL                                               _UpgradePackageDAL;
    private readonly IDdlScriptDAL                                                     _DdlScriptDAL;
    private readonly AutoNumber                                                       _AutoNumber;
    private readonly IQueryExecutor                                                   _QueryExecutor;
    private readonly KeyInvalidate                                                    _KeyInvalidate;
    private readonly IListHandler<UpgradePackageListQuery, UpgradePackageListDTO>     _ListHandler;
    private readonly BaseEntityAppService<UpgradePackageDTO>                          _BaseEntityAppService;

    public UpgradePackageBLL(
        IUpgradePackageDAL upgradePackageDAL,
        IDdlScriptDAL ddlScriptDAL,
        AutoNumber autoNumber,
        IQueryExecutor queryExecutor,
        KeyInvalidate keyInvalidate,
        IListHandler<UpgradePackageListQuery, UpgradePackageListDTO> listHandler,
        BaseEntityAppService<UpgradePackageDTO> baseEntityAppService)
    {
        _UpgradePackageDAL    = upgradePackageDAL;
        _DdlScriptDAL         = ddlScriptDAL;
        _AutoNumber           = autoNumber;
        _QueryExecutor        = queryExecutor;
        _KeyInvalidate        = keyInvalidate;
        _ListHandler          = listHandler;
        _BaseEntityAppService = baseEntityAppService;
    }

    // ── Reads ─────────────────────────────────────────────────────────────

    public async Task<UpgradePackageDTO?> GetById(int packageId, LoginDTO loginDTO, CancellationToken ct)
        => await _UpgradePackageDAL.GetById(packageId, loginDTO, ct).ConfigureAwait(false);

    public async Task<IEnumerable<UpgradePackageDdlDTO>> GetDdlLines(int packageId, LoginDTO loginDTO, CancellationToken ct)
        => await _UpgradePackageDAL.GetDdlLines(packageId, loginDTO, ct).ConfigureAwait(false);

    public async Task<IEnumerable<UpgradePackageMetaDTO>> GetMetaLines(int packageId, LoginDTO loginDTO, CancellationToken ct)
        => await _UpgradePackageDAL.GetMetaLines(packageId, loginDTO, ct).ConfigureAwait(false);

    public async Task<string> GetList(
        CriteriaDTO criteria, string? searchText, int pageOffset, int pageSize,
        LoginDTO loginDTO, CancellationToken ct = default)
    {
        var merged = CriteriaRouterHelper.WithSearchAndPaging(criteria, searchText, pageOffset, pageSize);
        var c      = CriteriaBinder.Bind<UpgradePackageListCriteria>(merged);
        var result = await _ListHandler
            .HandleAsync(new UpgradePackageListQuery(c), loginDTO, ct)
            .ConfigureAwait(false);
        return JsonConvert.SerializeObject(result);
    }

    public async Task<string> GetSelectListUpgradePackage(
        int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        => await _UpgradePackageDAL.GetSelectListUpgradePackage(firstNumber, maxResult, loginDTO)
               .ConfigureAwait(false);

    public async Task<IEnumerable<PackageChainItemDTO>> GetProvisioningChain(
        int dbModelId, PackageScopeFilterDTO? scope, LoginDTO loginDTO, CancellationToken ct)
    {
        if (dbModelId <= 0) throw new ArgumentException("DbModelId is required.", nameof(dbModelId));
        var candidates = await _UpgradePackageDAL.GetProvisioningChain(dbModelId, scope, loginDTO, ct).ConfigureAwait(false);
        return candidates.Where(p => IsEligibleForRollout(p, scope?.ClientCode));
    }

    // Rollout axis (§36.5) — applied here in C#, not pushed into GET_PROVISIONING_CHAIN's SQL,
    // since a stable, cross-run-consistent hash bucketing isn't something SQL Server offers a
    // clean built-in for. Global is always eligible (the overwhelming majority of packages,
    // zero behavior change from before this axis existed). Percentage buckets deterministically
    // on the caller's own ClientCode — the same client always lands in the same bucket across
    // repeated calls, rather than a random per-call coin flip. No ClientCode to bucket on (e.g.
    // resolving a DbModel's chain with no specific client in context) → excluded, a safe default
    // rather than guessing who's "in" an unnamed rollout. Any other RolloutType (Beta/SelClients)
    // reaching this point despite Save()'s own rejection — e.g. a row created before that
    // validation existed — is excluded too, matching that same fail-safe posture.
    private static bool IsEligibleForRollout(PackageChainItemDTO package, string? clientCode)
    {
        if (package.RolloutType == (byte)SwDAL.Enums.PackageRolloutType.Global) return true;
        if (package.RolloutType != (byte)SwDAL.Enums.PackageRolloutType.Percentage) return false;
        if (string.IsNullOrWhiteSpace(clientCode)) return false;

        int bucket = (int)(System.Security.Cryptography.SHA256.HashData(
            System.Text.Encoding.UTF8.GetBytes(clientCode))[0] % 100);
        return bucket < package.RolloutPercent;
    }

    public async Task<IEnumerable<LineageNodeDTO>> GetLineage(int dbModelId, LoginDTO loginDTO, CancellationToken ct)
    {
        if (dbModelId <= 0) throw new ArgumentException("DbModelId is required.", nameof(dbModelId));
        return await _UpgradePackageDAL.GetLineage(dbModelId, loginDTO, ct).ConfigureAwait(false);
    }

    // ── Save (insert or update) ───────────────────────────────────────────

    public async Task<string> Save(UpgradePackageDTO dto, LoginDTO loginDTO, CancellationToken ct)
    {
        if (dto == null) throw new ArgumentNullException(nameof(dto));

        // Mirrors CK_MSWUPGRADEPACKAGE_SCOPEVALUE — checked here too for a clean error
        // instead of a raw constraint-violation exception from the DB.
        bool isUniversal = dto.ScopeType == (byte)SwDAL.Enums.PackageScopeType.Universal;
        if (isUniversal && !string.IsNullOrWhiteSpace(dto.ScopeValue))
            throw new ArgumentException("ScopeValue must be empty for a Universal-scope package.", nameof(dto));
        if (!isUniversal && string.IsNullOrWhiteSpace(dto.ScopeValue))
            throw new ArgumentException("ScopeValue is required for a non-Universal-scope package.", nameof(dto));

        // Mirrors CK_MSWUPGRADEPACKAGE_RELEASEVERSION_FORMAT — checked here too for a clean
        // error instead of a raw constraint-violation exception from the DB.
        if (!string.IsNullOrEmpty(dto.ReleaseVersion) && dto.ReleaseVersion.Any(c => !char.IsDigit(c) && c != '.'))
            throw new ArgumentException("ReleaseVersion must contain only digits and dots.", nameof(dto));

        // Rollout axis (§36.5) — Beta/SelClients are accepted by the CHECK constraint (reserved
        // numeric slots matching Entitlement's own FeatureFlag.RolloutType numbering) but have no
        // per-client rollout-membership table yet, so GetProvisioningChain can't actually honor
        // them. Rejecting outright here — rather than silently accepting and treating them as
        // Global — avoids a real footgun: a caller setting RolloutType=Beta expecting a
        // restriction that quietly never applies.
        if (dto.RolloutType is (byte)SwDAL.Enums.PackageRolloutType.Beta or (byte)SwDAL.Enums.PackageRolloutType.SelClients)
            throw new NotSupportedException("RolloutType Beta/SelClients is not yet implemented — no per-client rollout-membership table exists. Use Global or Percentage.");
        if (dto.RolloutPercent > 100)
            throw new ArgumentException("RolloutPercent must be between 0 and 100.", nameof(dto));
        if (dto.RolloutType == (byte)SwDAL.Enums.PackageRolloutType.Global && dto.RolloutPercent != 100)
            throw new ArgumentException("RolloutPercent must be 100 for a Global-rollout package.", nameof(dto));

        // Entitlement-aware gating (§40) — schema is ALWAYS delivered in full, never
        // feature-gated (the user's own confirmed, settled decision), so a RequiredFeatureCode
        // on a Schema-content package is meaningless and rejected outright rather than silently
        // ignored — the same "reject rather than accept-and-ignore" posture as the
        // RolloutType Beta/SelClients guard above.
        if (dto.PackageContentType == (byte)SwDAL.Enums.PackageContentType.Schema && !string.IsNullOrWhiteSpace(dto.RequiredFeatureCode))
            throw new ArgumentException("RequiredFeatureCode is only meaningful on a MetadataOrStandardData-content package — schema is always delivered in full, never feature-gated.", nameof(dto));

        bool isNew = dto.PackageId == 0;

        if (!isNew)
        {
            // Chain fields are load-bearing for provisioning-chain resolution once a package
            // is Released and possibly already applied to real client databases — changing
            // them retroactively would silently rewrite history. Same immutability posture
            // Delete/AttachDdlScript/DetachDdlScript already enforce (checked before opening
            // a transaction, same as those three), just scoped to these four fields instead
            // of the whole row.
            var existing = await _UpgradePackageDAL.GetById(dto.PackageId, loginDTO, ct).ConfigureAwait(false);
            if (existing?.PkgStatus == (byte)SwDAL.Enums.PkgStatus.Released &&
                (existing.SequenceInChain != dto.SequenceInChain ||
                 existing.SupersedesPackageId != dto.SupersedesPackageId ||
                 existing.ScopeType != dto.ScopeType ||
                 existing.ScopeValue != dto.ScopeValue ||
                 existing.RolloutType != dto.RolloutType ||
                 existing.RolloutPercent != dto.RolloutPercent ||
                 existing.PackageContentType != dto.PackageContentType ||
                 existing.RequiredFeatureCode != dto.RequiredFeatureCode))
            {
                throw new InvalidOperationException(
                    "Cannot change SequenceInChain/SupersedesPackageId/ScopeType/ScopeValue/RolloutType/RolloutPercent/PackageContentType/RequiredFeatureCode on a released upgrade package.");
            }
        }

        dto.TenantId = loginDTO.ClientId;

        var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
        try
        {
            if (isNew)
            {
                var auto      = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.SWUPGRADEPACKAGE, loginDTO, Trans);
                dto.PackageId = auto.StartNumber;
                dto.PkgStatus = 0; // Draft
            }

            await _BaseEntityAppService.ExecuteSaveAsync(
                EntityConstant.OBJECTSWUPGRADEPACKAGE,
                EventTypeConstant.SAVEUPGRADEPACKAGEEVENTTYPEID,
                dto,
                loginDTO,
                async tx =>
                {
                    _ = isNew
                        ? await _UpgradePackageDAL.SaveUpgradePackage(dto, loginDTO, tx, ct)
                        : await _UpgradePackageDAL.UpdateUpgradePackage(dto, loginDTO, tx, ct);
                    return dto.PackageId;
                },
                null, -1, -1, Trans);

            await _QueryExecutor.CommitAsync(Trans);
            var keyGen = new CacheKeyGeneration();

            var cacheKey = keyGen.KeyGeneration(
                dto.PackageId, EntityConstant.OBJECTSWUPGRADEPACKAGE,
                CacheKeyLevel.CLIENT_LEVEL, loginDTO);
            await _KeyInvalidate.AllInvalidateCache(cacheKey);

            return isNew
                ? $"{SuccessResponse.SaveSuccessMessage} {dto.PackageId}"
                : $"{SuccessResponse.UpdateSuccessMessage} {dto.PackageId}";
        }
        catch (Exception)
        {
            await _QueryExecutor.RollbackAsync(Trans);
            throw;
        }
    }

    // ── Delete ────────────────────────────────────────────────────────────

    public async Task<string> Delete(int packageId, LoginDTO loginDTO, CancellationToken ct)
    {
        var existing = await _UpgradePackageDAL.GetById(packageId, loginDTO, ct).ConfigureAwait(false);
        if (existing?.PkgStatus == (byte)SwDAL.Enums.PkgStatus.Released)
            throw new InvalidOperationException("Cannot delete a released upgrade package.");

        var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
        try
        {
            await _BaseEntityAppService.ExecuteSaveAsync(
                EntityConstant.OBJECTSWUPGRADEPACKAGE,
                EventTypeConstant.DELETEUPGRADEPACKAGEEVENTTYPEID,
                new UpgradePackageDTO { PackageId = packageId, TenantId = loginDTO.ClientId },
                loginDTO,
                async tx =>
                {
                    await _UpgradePackageDAL.DeleteUpgradePackage(packageId, loginDTO, tx, ct);
                    return packageId; 
                },
            null, -1, -1, Trans);

            await _QueryExecutor.CommitAsync(Trans);
            var keyGen = new CacheKeyGeneration();

            var cacheKey = keyGen.KeyGeneration(
                packageId, EntityConstant.OBJECTSWUPGRADEPACKAGE,
                CacheKeyLevel.CLIENT_LEVEL, loginDTO);
            await _KeyInvalidate.AllInvalidateCache(cacheKey);

            return SuccessResponse.DeleteSuccessMessage;
        }
        catch (Exception)
        {
            await _QueryExecutor.RollbackAsync(Trans);
            throw;
        }
    }

    // ── Attach/Detach a single DdlScript ───────────────────────────────────
    // ReplaceDdlLines replaces the package's whole line set — so both methods
    // load the current set, apply the one change, and replace it wholesale.
    // Same "cannot mutate a Released package" guard Delete() already enforces.

    public async Task<string> AttachDdlScript(AttachDdlScriptRequestDTO req, LoginDTO loginDTO, CancellationToken ct)
    {
        if (req == null) throw new ArgumentNullException(nameof(req));
        if (req.DdlScriptId <= 0) throw new ArgumentException("DdlScriptId is required.", nameof(req));

        var package = await _UpgradePackageDAL.GetById(req.PackageId, loginDTO, ct).ConfigureAwait(false)
            ?? throw new InvalidOperationException($"Upgrade package {req.PackageId} was not found.");
        if (package.PkgStatus == (byte)SwDAL.Enums.PkgStatus.Released)
            throw new InvalidOperationException("Cannot attach a script to a released upgrade package.");

        // Established platform convention (RefDataSourceBLL.Save's own identical guard,
        // 043_ALTER_MSWREFDATASOURCE_ScopeType.sql's header): a "Z_"-prefixed object is always
        // custom to one specific client/industry/country — never part of the universal set.
        // PackageScopeType is deliberately whole-package, not per-script (SwDAL.Enums.
        // PackageScopeType's own doc comment), so this check has to happen here, at the point
        // where a specific script and a specific package's scope first come together — not in
        // DdlScriptBLL.Save, which has no scope context of its own to check against.
        bool isUniversal = package.ScopeType == (byte)SwDAL.Enums.PackageScopeType.Universal;
        if (isUniversal)
        {
            var script = await _DdlScriptDAL.GetById(req.DdlScriptId, loginDTO, ct).ConfigureAwait(false)
                ?? throw new InvalidOperationException($"DdlScript {req.DdlScriptId} was not found.");
            if (script.ObjectName.StartsWith("Z_", StringComparison.OrdinalIgnoreCase))
                throw new InvalidOperationException(
                    $"'{script.ObjectName}' starts with the reserved custom-object prefix 'Z_' and cannot be attached " +
                    $"to Universal-scope package {req.PackageId} — attach it to a non-Universal-scope (typically Client) " +
                    "package instead, so it never ships to every client provisioned from this DbModel.");
        }

        var lines = (await _UpgradePackageDAL.GetDdlLines(req.PackageId, loginDTO, ct).ConfigureAwait(false)).ToList();
        if (lines.Any(l => l.DdlScriptId == req.DdlScriptId))
            throw new InvalidOperationException($"DdlScript {req.DdlScriptId} is already attached to package {req.PackageId}.");

        var sequence = req.Sequence > 0
            ? req.Sequence
            : (short)(lines.Count == 0 ? 1 : lines.Max(l => l.Sequence) + 1);

        lines.Add(new UpgradePackageDdlDTO
        {
            PackageId   = req.PackageId,
            DdlScriptId = req.DdlScriptId,
            Sequence    = sequence,
            TenantId    = loginDTO.ClientId,
        });

        var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
        try
        {
            GB5Trace.Step("attach-ddlscript", new { req.PackageId, req.DdlScriptId, sequence });
            await _UpgradePackageDAL.ReplaceDdlLines(req.PackageId, lines, loginDTO, Trans, ct).ConfigureAwait(false);
            await _QueryExecutor.CommitAsync(Trans);

            var keyGen = new CacheKeyGeneration();
            var cacheKey = keyGen.KeyGeneration(
                req.PackageId, EntityConstant.OBJECTSWUPGRADEPACKAGE, CacheKeyLevel.CLIENT_LEVEL, loginDTO);
            await _KeyInvalidate.AllInvalidateCache(cacheKey);

            return $"DdlScript {req.DdlScriptId} attached to package {req.PackageId} at sequence {sequence}.";
        }
        catch (Exception)
        {
            await _QueryExecutor.RollbackAsync(Trans);
            throw;
        }
    }

    public async Task<string> DetachDdlScript(DetachDdlScriptRequestDTO req, LoginDTO loginDTO, CancellationToken ct)
    {
        if (req == null) throw new ArgumentNullException(nameof(req));
        if (req.DdlScriptId <= 0) throw new ArgumentException("DdlScriptId is required.", nameof(req));

        var package = await _UpgradePackageDAL.GetById(req.PackageId, loginDTO, ct).ConfigureAwait(false)
            ?? throw new InvalidOperationException($"Upgrade package {req.PackageId} was not found.");
        if (package.PkgStatus == (byte)SwDAL.Enums.PkgStatus.Released)
            throw new InvalidOperationException("Cannot detach a script from a released upgrade package.");

        var lines = (await _UpgradePackageDAL.GetDdlLines(req.PackageId, loginDTO, ct).ConfigureAwait(false)).ToList();
        var remaining = lines.Where(l => l.DdlScriptId != req.DdlScriptId).ToList();
        if (remaining.Count == lines.Count)
            throw new InvalidOperationException($"DdlScript {req.DdlScriptId} is not attached to package {req.PackageId}.");

        var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
        try
        {
            GB5Trace.Step("detach-ddlscript", new { req.PackageId, req.DdlScriptId });
            await _UpgradePackageDAL.ReplaceDdlLines(req.PackageId, remaining, loginDTO, Trans, ct).ConfigureAwait(false);
            await _QueryExecutor.CommitAsync(Trans);

            var keyGen = new CacheKeyGeneration();
            var cacheKey = keyGen.KeyGeneration(
                req.PackageId, EntityConstant.OBJECTSWUPGRADEPACKAGE, CacheKeyLevel.CLIENT_LEVEL, loginDTO);
            await _KeyInvalidate.AllInvalidateCache(cacheKey);

            return $"DdlScript {req.DdlScriptId} detached from package {req.PackageId}.";
        }
        catch (Exception)
        {
            await _QueryExecutor.RollbackAsync(Trans);
            throw;
        }
    }
}