using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using IceImportDAL.DTO.EntityFieldLookup;
using IceImportDAL.Query.EntityFieldLookup;
using Microsoft.Extensions.Logging;

namespace IceImportDAL.CustomCode.EntityFieldLookup;

public class EntityFieldMetadataDAL : IEntityFieldMetadataDAL
{
    private readonly IQueryExecutor _qe;
    private readonly ILogger<EntityFieldMetadataDAL> _logger;

    public EntityFieldMetadataDAL(IQueryExecutor qe, ILogger<EntityFieldMetadataDAL> logger)
    {
        _qe = qe;
        _logger = logger;
    }

    public async Task<EntityFieldMetadataDTO?> ResolveAsync(
        int entityId, bool isNameLookup, LoginDTO login, CancellationToken ct)
    {
        try
        {
            var table = await _qe.QuerySingleAsync<EntityFieldMetadataDTO>(
                login, EntityFieldLookupQB.GET_TABLE_AND_ID_FIELD,
                new { EntityId = entityId }, cancellationToken: ct).ConfigureAwait(false);

            if (table is null ||
                string.IsNullOrWhiteSpace(table.TableName) ||
                string.IsNullOrWhiteSpace(table.IdFieldName))
                return null;

            var memberName = isNameLookup ? "NAME" : "CODE";
            var fieldName = await ResolveFieldNameAsync(
                entityId, EntityFieldLookupQB.GET_FIELD_BY_MEMBERNAME,
                new { EntityId = entityId, MemberName = memberName }, login, ct).ConfigureAwait(false);

            // Not every entity has a member literally named 'NAME' (e.g. MGCMTYPE's display column
            // is GCMTYPEDESCRIPTION) — fall back to the common CODE/DESCRIPTION naming convention.
            if (fieldName is null && isNameLookup)
                fieldName = await ResolveFieldNameAsync(
                    entityId, EntityFieldLookupQB.GET_FIELD_BY_MEMBERNAME_LIKE,
                    new { EntityId = entityId, MemberNamePattern = "%DESCRIPTION%" }, login, ct)
                    .ConfigureAwait(false);

            if (fieldName is null) return null;

            return new EntityFieldMetadataDTO
            {
                TableName = table.TableName,
                IdFieldName = table.IdFieldName,
                ValueFieldName = fieldName
            };
        }
        catch (OperationCanceledException)
        {
            throw;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "IceImport: EntityFieldMetadataDAL.ResolveAsync failed for EntityId {EntityId}", entityId);
            return null;
        }
    }

    private async Task<string?> ResolveFieldNameAsync(
        int entityId, string sql, object parameters, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<FieldNameRowDTO>(
            login, sql, parameters, cancellationToken: ct).ConfigureAwait(false);
        return rows.FirstOrDefault()?.FieldName;
    }

    private sealed class FieldNameRowDTO
    {
        public string? FieldName { get; set; }
    }
}