using System.Text.RegularExpressions;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using IceImportDAL.DTO.EntityFieldLookup;

namespace IceImportDAL.CustomCode.EntityFieldLookup;

public class GenericEntityCodeLookupDAL : IGenericEntityCodeLookupDAL
{
    private const int ChunkSize = 2000; // matches GcmCodeLookupDAL's IN-clause batch size

    private static readonly Regex ValidIdentifier =
        new(@"^[A-Za-z_][A-Za-z0-9_]*$", RegexOptions.Compiled);

    private readonly IQueryExecutor _qe;

    public GenericEntityCodeLookupDAL(IQueryExecutor qe) => _qe = qe;

    public async Task<Dictionary<string, int>> ResolveValuesToIdsAsync(
        EntityFieldMetadataDTO metadata, IEnumerable<string> values, LoginDTO login, CancellationToken ct)
    {
        if (!ValidIdentifier.IsMatch(metadata.TableName) ||
            !ValidIdentifier.IsMatch(metadata.IdFieldName) ||
            !ValidIdentifier.IsMatch(metadata.ValueFieldName))
            throw new InvalidOperationException(
                $"EntityFieldMetadata resolved an unsafe identifier: Table={metadata.TableName}, " +
                $"IdField={metadata.IdFieldName}, ValueField={metadata.ValueFieldName}");

        var distinct = (values ?? Enumerable.Empty<string>())
            .Where(v => !string.IsNullOrWhiteSpace(v))
            .Select(v => v.Trim())
            .Distinct(StringComparer.OrdinalIgnoreCase)
            .ToList();

        var result = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
        if (distinct.Count == 0) return result;

        var sql = $@"
            SELECT {metadata.ValueFieldName} AS Value, {metadata.IdFieldName} AS Id
            FROM   {metadata.TableName}
            WHERE  {metadata.ValueFieldName} IN @Values";

        for (int offset = 0; offset < distinct.Count; offset += ChunkSize)
        {
            var chunk = distinct.Skip(offset).Take(ChunkSize).ToList();
            var rows = await _qe.QueryAsync<GenericLookupRowDTO>(
                login, sql, new { Values = chunk }, cancellationToken: ct).ConfigureAwait(false);

            foreach (var row in rows)
                if (row.Value is not null)
                    result[row.Value] = row.Id;
        }

        return result;
    }

    private sealed class GenericLookupRowDTO
    {
        public string? Value { get; set; }
        public int Id { get; set; }
    }
}