using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using GB5Shared.GOP.Qualifier.DTOs;
using GB5Shared.GOP.Qualifier;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;

namespace GB5Shared.GOP.Qualifier
{
    public class QualifierDAL : IQualifierDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation    _Validation;

        public QualifierDAL(IQueryExecutor queryExecutor, IValidation validation)
        {
            _QueryExecutor = queryExecutor;
            _Validation    = validation;
        }

        // Every query in QualifierQB.cs requires @TenantId — there is no auto-population
        // by IQueryExecutor (the old comments here claiming otherwise were wrong; confirmed
        // by reading QueryExecutor.ExecuteAsync directly and by this failing live with
        // "Must declare the scalar variable @TenantId"). Save methods now set dto.TenantId
        // from loginDTO.ClientId server-side (overriding whatever a client sent) rather
        // than relying on the caller to have supplied it correctly — same defensive
        // pattern as GopTargetOperationDAL/MappingDAL.

        // ── Definition ────────────────────────────────────────────────────────────

        public async Task SaveQualifierDefinition(
            QualifierDefinitionDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                dto.TenantId     = loginDTO.ClientId;
                dto.CreatedById  = loginDTO.UserId;
                dto.ModifiedById = loginDTO.UserId;
                await _QueryExecutor.ExecuteAsync(loginDTO, QualifierQB.SAVE_QUALIFIER_DEFINITION, dto, tx, ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage));
            }
        }

        public async Task UpdateQualifierDefinition(
            QualifierDefinitionDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                dto.TenantId     = loginDTO.ClientId;
                dto.ModifiedById = loginDTO.UserId;
                await _QueryExecutor.ExecuteAsync(loginDTO, QualifierQB.UPDATE_QUALIFIER_DEFINITION, dto, tx, ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage));
            }
        }

        public async Task DeleteQualifierDefinition(
            int qualifierId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO,
                    QualifierQB.DELETE_QUALIFIER_DEFINITION,
                    new { TenantId = loginDTO.ClientId, QualifierId = qualifierId, ModifiedById = loginDTO.UserId },
                    tx, ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.DeleteErrorMessage));
            }
        }

        public async Task<QualifierDefinitionDTO?> GetQualifierById(
            int qualifierId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.QuerySingleAsync<QualifierDefinitionDTO>(
                    loginDTO, QualifierQB.GET_QUALIFIER_BY_ID,
                    new { TenantId = loginDTO.ClientId, QualifierId = qualifierId }, cancellationToken: ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        public async Task<QualifierDefinitionDTO?> GetQualifierByCode(
            string qualifierCode, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.QuerySingleAsync<QualifierDefinitionDTO>(
                    loginDTO, QualifierQB.GET_QUALIFIER_BY_CODE,
                    new { TenantId = loginDTO.ClientId, QualifierCode = qualifierCode }, cancellationToken: ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        public async Task<IReadOnlyList<QualifierDefinitionDTO>> GetQualifierList(
            LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<QualifierDefinitionDTO>(
                    loginDTO, QualifierQB.GET_QUALIFIER_LIST,
                    new { TenantId = loginDTO.ClientId }, cancellationToken: ct);
                return (result ?? []).ToList();
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        // QualifierCode/QualifierName are always plain search text here — read the raw
        // FieldValue directly rather than via CriteriaParser, which types each field off
        // CriteriaAttributeType and defaults to PICKLIST (List<int>) when the caller omits
        // it, silently turning the search text into "System.Collections.Generic.List`1[...]".
        private static string? GetRawFieldValue(CriteriaDTO criteriaDTO, string fieldName)
        {
            var attr = criteriaDTO?.SectionCriteriaList?
                .Where(s => s?.AttributesCriteriaList != null)
                .SelectMany(s => s.AttributesCriteriaList)
                .FirstOrDefault(a => string.Equals(a.FieldName, fieldName, StringComparison.OrdinalIgnoreCase));

            if (attr?.FieldValue == null) return null;

            string raw = attr.FieldValue is System.Text.Json.JsonElement je
                ? je.ToString()
                : attr.FieldValue.ToString();
            return string.IsNullOrWhiteSpace(raw) ? null : raw;
        }

        public async Task<IReadOnlyList<QualifierDefinitionDTO>> GetSelectListQualifier(
            CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                string? qualifierCode = GetRawFieldValue(criteriaDTO, "QualifierCode");
                string? qualifierName = GetRawFieldValue(criteriaDTO, "QualifierName");

                var result = await _QueryExecutor.QueryAsync<QualifierDefinitionDTO>(
                    loginDTO, QualifierQB.GET_SELECTLIST_QUALIFIER,
                    new
                    {
                        TenantId      = loginDTO.ClientId,
                        QualifierCode = string.IsNullOrWhiteSpace(qualifierCode) ? null : $"%{qualifierCode}%",
                        QualifierName = string.IsNullOrWhiteSpace(qualifierName) ? null : $"%{qualifierName}%"
                    },
                    cancellationToken: ct);
                return (result ?? []).ToList();
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        public async Task<IReadOnlyList<QualifierLibraryDTO>> GetQualifierLibrary(
            LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<QualifierLibraryDTO>(
                    loginDTO, QualifierQB.GET_QUALIFIER_LIBRARY,
                    new { TenantId = loginDTO.ClientId }, cancellationToken: ct);
                return (result ?? []).ToList();
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        // ── Version ───────────────────────────────────────────────────────────────

        public async Task SaveQualifierVersion(
            QualifierVersionDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                dto.TenantId     = loginDTO.ClientId;
                dto.CreatedById  = loginDTO.UserId;
                dto.ModifiedById = loginDTO.UserId;
                await _QueryExecutor.ExecuteAsync(loginDTO, QualifierQB.SAVE_QUALIFIER_VERSION, dto, tx, ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage));
            }
        }

        public async Task ArchiveActiveVersion(
            int qualifierId, int modifiedById, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, QualifierQB.ARCHIVE_ACTIVE_VERSION,
                    new { TenantId = loginDTO.ClientId, QualifierId = qualifierId, ModifiedById = modifiedById }, tx, ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage));
            }
        }

        public async Task ActivateVersion(
            int qualifierVersionId, string snapshotJson, int modifiedById,
            LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, QualifierQB.ACTIVATE_VERSION,
                    new { TenantId = loginDTO.ClientId, QualifierVersionId = qualifierVersionId, SnapshotJson = snapshotJson, ModifiedById = modifiedById },
                    tx, ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage));
            }
        }

        public async Task<QualifierVersionDTO?> GetActiveQualifierPlan(
            int qualifierId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                // Same ambient-transaction self-block risk as GetEntityBindings above.
                return await _QueryExecutor.QuerySingleAsync<QualifierVersionDTO>(
                    loginDTO, QualifierQB.GET_ACTIVE_QUALIFIER_PLAN,
                    new { TenantId = loginDTO.ClientId, QualifierId = qualifierId },
                    useReadUncommitted: true, cancellationToken: ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        public async Task<IReadOnlyList<QualifierVersionDTO>> GetQualifierVersionList(
            int qualifierId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<QualifierVersionDTO>(
                    loginDTO, QualifierQB.GET_QUALIFIER_VERSION_LIST,
                    new { TenantId = loginDTO.ClientId, QualifierId = qualifierId }, cancellationToken: ct);
                return (result ?? []).ToList();
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        public async Task<byte?> GetQualifierVersionStatus(
            int qualifierVersionId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.QuerySingleAsync<byte?>(
                    loginDTO, QualifierQB.GET_QUALIFIER_VERSION_STATUS,
                    new { TenantId = loginDTO.ClientId, QualifierVersionId = qualifierVersionId }, cancellationToken: ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        public async Task DeleteQualifierVersion(
            int qualifierVersionId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, QualifierQB.DELETE_QUALIFIER_VERSION,
                    new { TenantId = loginDTO.ClientId, QualifierVersionId = qualifierVersionId, ModifiedById = loginDTO.UserId },
                    tx, ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.DeleteErrorMessage));
            }
        }

        // ── Entity (bindings) ─────────────────────────────────────────────────────

        public async Task SaveQualifierEntity(
            QualifierEntityDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                dto.TenantId     = loginDTO.ClientId;
                dto.CreatedById  = loginDTO.UserId;
                dto.ModifiedById = loginDTO.UserId;
                dto.QualifierEntityId = await _QueryExecutor.ExecuteIdentityAsync(
                    loginDTO, QualifierQB.SAVE_QUALIFIER_ENTITY, dto, tx);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage));
            }
        }

        public async Task UpdateQualifierEntity(
            QualifierEntityDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                dto.TenantId     = loginDTO.ClientId;
                dto.ModifiedById = loginDTO.UserId;
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, QualifierQB.UPDATE_QUALIFIER_ENTITY, dto, tx, ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage));
            }
        }

        public async Task<IReadOnlyList<QualifierEntityDTO>> GetEntityBindings(
            string entityCode, byte stage, byte scope, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                // useReadUncommitted: true — this runs on its OWN connection (no transaction
                // is threaded through from QualifierFacadeImpl.ExecuteStageAsync), and every
                // Save pipeline stage (Validate/Enrich/PrePersist/PostPersist/EnrichForEvent)
                // calls this same lookup while BaseEntityAppService's ambient transaction is
                // still open. When the entity being saved IS a qualifier binding itself
                // (MQUALIFIERENTITY — e.g. QualifierBLL.SaveQualifierEntity), the PostPersist/
                // EnrichForEvent stage's read here targets the very row the Persist step just
                // inserted under READ COMMITTED — a plain SELECT from a second connection then
                // blocks on the still-open transaction's exclusive lock until either that
                // transaction commits (impossible — it can't commit until this pipeline, which
                // includes this blocked read, finishes) or the 300s command timeout kills it.
                // Confirmed live via sys.dm_tran_locks: an X lock on MQUALIFIERENTITY held by
                // the INSERT's session while a second session sat LCK_M_S on this exact query
                // for 180+s. Config/binding lookups tolerate a dirty read; a self-deadlock does
                // not.
                var result = await _QueryExecutor.QueryAsync<QualifierEntityDTO>(
                    loginDTO, QualifierQB.GET_ENTITY_BINDINGS,
                    new { TenantId = loginDTO.ClientId, EntityCode = entityCode, Stage = stage, Scope = scope },
                    useReadUncommitted: true, cancellationToken: ct);
                return (result ?? []).ToList();
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        public async Task<IReadOnlyList<QualifierEntityDTO>> GetEntityBindingsByQualifier(
            int qualifierId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                // Same ambient-transaction self-block risk as GetEntityBindings above.
                var result = await _QueryExecutor.QueryAsync<QualifierEntityDTO>(
                    loginDTO, QualifierQB.GET_ENTITY_BINDINGS_BY_QUALIFIER,
                    new { TenantId = loginDTO.ClientId, QualifierId = qualifierId },
                    useReadUncommitted: true, cancellationToken: ct);
                return (result ?? []).ToList();
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        // ── Method ────────────────────────────────────────────────────────────────

        public async Task SaveQualifierMethod(
            QualifierMethodDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                dto.TenantId     = loginDTO.ClientId;
                dto.CreatedById  = loginDTO.UserId;
                dto.ModifiedById = loginDTO.UserId;
                dto.QualifierMethodId = await _QueryExecutor.ExecuteIdentityAsync(
                    loginDTO, QualifierQB.SAVE_QUALIFIER_METHOD, dto, tx);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage));
            }
        }

        public async Task<IReadOnlyList<QualifierMethodDTO>> GetQualifierMethods(
            int qualifierId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<QualifierMethodDTO>(
                    loginDTO, QualifierQB.GET_QUALIFIER_METHODS,
                    new { TenantId = loginDTO.ClientId, QualifierId = qualifierId }, cancellationToken: ct);
                return (result ?? []).ToList();
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        public async Task DeleteQualifierMethod(
            int qualifierMethodId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, QualifierQB.DELETE_QUALIFIER_METHOD,
                    new { TenantId = loginDTO.ClientId, QualifierMethodId = qualifierMethodId, ModifiedById = loginDTO.UserId },
                    tx, ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.DeleteErrorMessage));
            }
        }

        // ── Rule Groups ───────────────────────────────────────────────────────────

        public async Task SaveQualifierRuleGroup(
            QualifierRuleGroupDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                dto.TenantId     = loginDTO.ClientId;
                dto.CreatedById  = loginDTO.UserId;
                dto.ModifiedById = loginDTO.UserId;
                dto.RuleGroupId = await _QueryExecutor.ExecuteIdentityAsync(
                    loginDTO, QualifierQB.SAVE_QUALIFIER_RULE_GROUP, dto, tx);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage));
            }
        }

        public async Task<IReadOnlyList<QualifierRuleGroupDTO>> GetRuleGroups(
            int qualifierId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<QualifierRuleGroupDTO>(
                    loginDTO, QualifierQB.GET_RULE_GROUPS,
                    new { TenantId = loginDTO.ClientId, QualifierId = qualifierId }, cancellationToken: ct);
                return (result ?? []).ToList();
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        public async Task DeleteQualifierRuleGroup(
            int ruleGroupId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, QualifierQB.DELETE_QUALIFIER_RULE_GROUP,
                    new { TenantId = loginDTO.ClientId, RuleGroupId = ruleGroupId, ModifiedById = loginDTO.UserId },
                    tx, ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.DeleteErrorMessage));
            }
        }

        // ── Rule Conditions ───────────────────────────────────────────────────────

        public async Task SaveQualifierRuleCondition(
            QualifierRuleConditionDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                dto.TenantId     = loginDTO.ClientId;
                dto.CreatedById  = loginDTO.UserId;
                dto.ModifiedById = loginDTO.UserId;
                dto.ConditionId = await _QueryExecutor.ExecuteIdentityAsync(
                    loginDTO, QualifierQB.SAVE_QUALIFIER_RULE_CONDITION, dto, tx);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage));
            }
        }

        public async Task<IReadOnlyList<QualifierRuleConditionDTO>> GetRuleConditions(
            int qualifierId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<QualifierRuleConditionDTO>(
                    loginDTO, QualifierQB.GET_RULE_CONDITIONS,
                    new { TenantId = loginDTO.ClientId, QualifierId = qualifierId }, cancellationToken: ct);
                return (result ?? []).ToList();
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        public async Task DeleteQualifierRuleCondition(
            int conditionId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, QualifierQB.DELETE_QUALIFIER_RULE_CONDITION,
                    new { TenantId = loginDTO.ClientId, ConditionId = conditionId, ModifiedById = loginDTO.UserId },
                    tx, ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.DeleteErrorMessage));
            }
        }

        // ── Outputs ───────────────────────────────────────────────────────────────

        public async Task SaveQualifierOutput(
            QualifierOutputDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                dto.TenantId     = loginDTO.ClientId;
                dto.CreatedById  = loginDTO.UserId;
                dto.ModifiedById = loginDTO.UserId;
                dto.OutputId = await _QueryExecutor.ExecuteIdentityAsync(
                    loginDTO, QualifierQB.SAVE_QUALIFIER_OUTPUT, dto, tx);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage));
            }
        }

        public async Task<IReadOnlyList<QualifierOutputDTO>> GetQualifierOutputs(
            int qualifierId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<QualifierOutputDTO>(
                    loginDTO, QualifierQB.GET_QUALIFIER_OUTPUTS,
                    new { TenantId = loginDTO.ClientId, QualifierId = qualifierId }, cancellationToken: ct);
                return (result ?? []).ToList();
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        public async Task DeleteQualifierOutput(
            int outputId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, QualifierQB.DELETE_QUALIFIER_OUTPUT,
                    new { TenantId = loginDTO.ClientId, OutputId = outputId, ModifiedById = loginDTO.UserId },
                    tx, ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.DeleteErrorMessage));
            }
        }

        // ── Dependencies ──────────────────────────────────────────────────────────

        public async Task SaveQualifierDependency(
            QualifierDependencyDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                dto.TenantId     = loginDTO.ClientId;
                dto.CreatedById  = loginDTO.UserId;
                dto.ModifiedById = loginDTO.UserId;
                dto.DependencyId = await _QueryExecutor.ExecuteIdentityAsync(
                    loginDTO, QualifierQB.SAVE_QUALIFIER_DEPENDENCY, dto, tx);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage));
            }
        }

        public async Task<IReadOnlyList<QualifierDependencyDTO>> GetQualifierDependencies(
            int qualifierId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<QualifierDependencyDTO>(
                    loginDTO, QualifierQB.GET_QUALIFIER_DEPENDENCIES,
                    new { TenantId = loginDTO.ClientId, QualifierId = qualifierId }, cancellationToken: ct);
                return (result ?? []).ToList();
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        public async Task<IReadOnlyList<QualifierDependencyDTO>> GetDependenciesByEntity(
            string entityCode, byte stage, byte scope, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                // Same ambient-transaction self-block risk as GetEntityBindings above.
                var result = await _QueryExecutor.QueryAsync<QualifierDependencyDTO>(
                    loginDTO, QualifierQB.GET_QUALIFIER_DEPENDENCIES_BY_ENTITY,
                    new { TenantId = loginDTO.ClientId, EntityCode = entityCode, Stage = stage, Scope = scope },
                    useReadUncommitted: true, cancellationToken: ct);
                return (result ?? []).ToList();
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        public async Task DeleteQualifierDependency(
            int dependencyId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, QualifierQB.DELETE_QUALIFIER_DEPENDENCY,
                    new { TenantId = loginDTO.ClientId, DependencyId = dependencyId, ModifiedById = loginDTO.UserId },
                    tx, ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.DeleteErrorMessage));
            }
        }

        // ── Scope ────────────────────────────────────────────────────────────────

        public async Task SaveQualifierScope(
            QualifierScopeDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                dto.TenantId     = loginDTO.ClientId;
                dto.CreatedById  = loginDTO.UserId;
                dto.ModifiedById = loginDTO.UserId;
                dto.QualifierScopeId = await _QueryExecutor.ExecuteIdentityAsync(
                    loginDTO, QualifierQB.SAVE_QUALIFIER_SCOPE, dto, tx);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage));
            }
        }

        public async Task<IReadOnlyList<QualifierScopeDTO>> GetQualifierScopes(
            int qualifierId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<QualifierScopeDTO>(
                    loginDTO, QualifierQB.GET_QUALIFIER_SCOPES,
                    new { TenantId = loginDTO.ClientId, QualifierId = qualifierId }, cancellationToken: ct);
                return (result ?? []).ToList();
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        public async Task DeleteQualifierScope(
            int qualifierScopeId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, QualifierQB.DELETE_QUALIFIER_SCOPE,
                    new { TenantId = loginDTO.ClientId, QualifierScopeId = qualifierScopeId, ModifiedById = loginDTO.UserId },
                    tx, ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.DeleteErrorMessage));
            }
        }

        // ── Entity code resolution ────────────────────────────────────────────────

        public async Task<string?> GetEntityCodeById(
            int entityId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.QuerySingleAsync<string?>(
                    loginDTO, QualifierQB.GET_ENTITY_CODE_BY_ID,
                    new { EntityId = entityId }, cancellationToken: ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }
    }
}