using ClosedXML.Excel;
using line_gestao_api.Data;
using line_gestao_api.Dtos;
using line_gestao_api.Models;
using line_gestao_api.Services;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Security.Claims;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;

namespace line_gestao_api.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    [Authorize]
    public class LinesController : ControllerBase
    {
        private readonly AppDbContext _db;
        private readonly ITenantProvider _tenantProvider;
        private readonly IVigenciaNotificationSyncService _vigenciaNotificationSyncService;
        private readonly ParcelamentosImportService _parcelamentosImportService;
        private readonly SpreadsheetImportAuditService _spreadsheetImportAuditService;
        private static readonly List<AccountCompanyDto> AccountCompanies = new()
        {
            new AccountCompanyDto
            {
                Empresa = "CLARO LINE MÓVEL",
                Contas = new List<string> { "172593311", "172593840" }
            },
            new AccountCompanyDto
            {
                Empresa = "VIVO MACROPHONY",
                Contas = new List<string> { "0430237019", "0437488125", "0449508564", "0454371844" }
            },
            new AccountCompanyDto
            {
                Empresa = "VIVO LINE MÓVEL",
                Contas = new List<string> { "0435288088" }
            },
            new AccountCompanyDto
            {
                Empresa = "TIM LINE MÓVEL",
                Contas = new List<string> { "0072046192" }
            }
        };

        public LinesController(
            AppDbContext db,
            ITenantProvider tenantProvider,
            IVigenciaNotificationSyncService vigenciaNotificationSyncService,
            ParcelamentosImportService parcelamentosImportService,
            SpreadsheetImportAuditService spreadsheetImportAuditService)
        {
            _db = db;
            _tenantProvider = tenantProvider;
            _vigenciaNotificationSyncService = vigenciaNotificationSyncService;
            _parcelamentosImportService = parcelamentosImportService;
            _spreadsheetImportAuditService = spreadsheetImportAuditService;
        }

        public class ImportExcelForm
        {
            public IFormFile File { get; set; } = default!;
        }

        // ==========================================================
        // ✅ 1. ENDPOINT: AGRUPAR POR CLIENTE
        // ==========================================================
        [HttpGet("groups")]
        public async Task<ActionResult<PagedResult<ClientGroupDto>>> GetClientGroups(
            [FromQuery] string? skil,
            [FromQuery] string? search,
            [FromQuery] string? additionalMode,
            [FromQuery] string? additionalServices,
            [FromQuery] int page = 1,
            [FromQuery] int pageSize = 10)
        {
            page = page < 1 ? 1 : page;
            pageSize = pageSize < 1 ? 10 : pageSize;

            var query = _db.MobileLines.AsNoTracking();
            var reservaFilter = false;

            // Filtro SKIL
            if (!string.IsNullOrWhiteSpace(skil))
            {
                var sSkil = skil.Trim();
                if (sSkil.Equals("RESERVA", StringComparison.OrdinalIgnoreCase))
                {
                    reservaFilter = true;
                    query = query.Where(x =>
                        EF.Functions.ILike((x.Usuario ?? "").Trim(), "RESERVA") ||
                        (EF.Functions.ILike((x.Cliente ?? "").Trim(), "RESERVA") &&
                         EF.Functions.ILike((x.Skil ?? "").Trim(), "RESERVA")));
                }
                else
                    query = query.Where(x => EF.Functions.ILike(x.Skil ?? "", $"%{sSkil}%"));
            }

            query = ApplyAdditionalFilters(query, additionalMode, additionalServices);

            IQueryable<ClientGroupDto> groupedQuery;

            if (reservaFilter)
            {
                var userDataClientByLine = BuildUserDataClientByLineQuery();
                var userDataClientByItem = BuildUserDataClientByItemQuery();
                var reservaRows =
                    from line in query
                    join udLine in userDataClientByLine on line.Linha equals udLine.Linha into udLineJoin
                    from udLine in udLineJoin.DefaultIfEmpty()
                    join udItem in userDataClientByItem on line.Item equals udItem.Item into udItemJoin
                    from udItem in udItemJoin.DefaultIfEmpty()
                    let clienteOriginal = (line.Cliente ?? "").Trim()
                    let skilOriginal = (line.Skil ?? "").Trim()
                    let clientePorLinha = (udLine.Cliente ?? "").Trim()
                    let clientePorItem = (udItem.Cliente ?? "").Trim()
                    let reservaEstrita = EF.Functions.ILike(clienteOriginal, "RESERVA") &&
                                         EF.Functions.ILike(skilOriginal, "RESERVA")
                    let clienteEfetivo = reservaEstrita
                        ? "RESERVA"
                        : (!string.IsNullOrEmpty(clienteOriginal) &&
                           !EF.Functions.ILike(clienteOriginal, "RESERVA"))
                        ? clienteOriginal
                        : (!string.IsNullOrEmpty(clientePorLinha) &&
                           !EF.Functions.ILike(clientePorLinha, "RESERVA"))
                            ? clientePorLinha
                            : (!string.IsNullOrEmpty(clientePorItem) &&
                               !EF.Functions.ILike(clientePorItem, "RESERVA"))
                                ? clientePorItem
                                : ""
                    select new
                    {
                        Cliente = string.IsNullOrEmpty(clienteEfetivo) ? "RESERVA" : clienteEfetivo,
                        line.Status
                    };

                if (!string.IsNullOrWhiteSpace(search))
                {
                    var s = search.Trim();
                    reservaRows = reservaRows.Where(x => EF.Functions.ILike(x.Cliente ?? "", $"%{s}%"));
                }

                groupedQuery = reservaRows
                    .GroupBy(x => x.Cliente)
                    .Select(g => new ClientGroupDto
                    {
                        Cliente = g.Key!,
                        TotalLinhas = g.Count(),
                        Ativos = g.Count(x => EF.Functions.ILike(x.Status ?? "", "%ativo%")),
                        Bloqueados = g.Count(x => EF.Functions.ILike(x.Status ?? "", "%bloque%") ||
                                                  EF.Functions.ILike(x.Status ?? "", "%perda%") ||
                                                  EF.Functions.ILike(x.Status ?? "", "%roubo%"))
                    });
            }
            else
            {
                query = query.Where(x => !string.IsNullOrEmpty(x.Cliente));

                if (!string.IsNullOrWhiteSpace(search))
                {
                    var s = search.Trim();
                    query = query.Where(x => EF.Functions.ILike(x.Cliente ?? "", $"%{s}%"));
                }

                groupedQuery = query.GroupBy(x => x.Cliente)
                    .Select(g => new ClientGroupDto
                    {
                        Cliente = g.Key!,
                        TotalLinhas = g.Count(),
                        Ativos = g.Count(x => EF.Functions.ILike(x.Status ?? "", "%ativo%")),
                        Bloqueados = g.Count(x => EF.Functions.ILike(x.Status ?? "", "%bloque%") ||
                                                  EF.Functions.ILike(x.Status ?? "", "%perda%") ||
                                                  EF.Functions.ILike(x.Status ?? "", "%roubo%"))
                    });
            }

            var totalGroups = await groupedQuery.CountAsync();

            var orderedGroupedQuery = reservaFilter
                ? groupedQuery
                    .OrderByDescending(x => EF.Functions.ILike((x.Cliente ?? "").Trim(), "RESERVA"))
                    .ThenBy(x => x.Cliente)
                : groupedQuery.OrderBy(x => x.Cliente);

            var items = await orderedGroupedQuery
                .Skip((page - 1) * pageSize)
                .Take(pageSize)
                .ToListAsync();

            return Ok(new PagedResult<ClientGroupDto>
            {
                Page = page,
                PageSize = pageSize,
                Total = totalGroups,
                Items = items
            });
        }

        // ==========================================================
        // ✅ 2. ENDPOINT: LISTAR NOMES DE CLIENTES (ACEITA SKIL)
        // ==========================================================
        [HttpGet("clients")]
        public async Task<ActionResult<List<string>>> GetClients(
            [FromQuery] string? skil,
            [FromQuery] string? additionalMode,
            [FromQuery] string? additionalServices)
        {
            var query = _db.MobileLines.AsNoTracking();
            var reservaFilter = false;

            if (!string.IsNullOrWhiteSpace(skil))
            {
                var sSkil = skil.Trim();
                if (sSkil.Equals("RESERVA", StringComparison.OrdinalIgnoreCase))
                {
                    reservaFilter = true;
                    query = query.Where(x =>
                        EF.Functions.ILike((x.Usuario ?? "").Trim(), "RESERVA") ||
                        (EF.Functions.ILike((x.Cliente ?? "").Trim(), "RESERVA") &&
                         EF.Functions.ILike((x.Skil ?? "").Trim(), "RESERVA")));
                }
                else
                    query = query.Where(x => EF.Functions.ILike(x.Skil ?? "", $"%{sSkil}%"));
            }

            query = ApplyAdditionalFilters(query, additionalMode, additionalServices);

            List<string> clients;
            if (reservaFilter)
            {
                var userDataClientByLine = BuildUserDataClientByLineQuery();
                var userDataClientByItem = BuildUserDataClientByItemQuery();
                clients = await (
                    from line in query
                    join udLine in userDataClientByLine on line.Linha equals udLine.Linha into udLineJoin
                    from udLine in udLineJoin.DefaultIfEmpty()
                    join udItem in userDataClientByItem on line.Item equals udItem.Item into udItemJoin
                    from udItem in udItemJoin.DefaultIfEmpty()
                    let clienteOriginal = (line.Cliente ?? "").Trim()
                    let skilOriginal = (line.Skil ?? "").Trim()
                    let clientePorLinha = (udLine.Cliente ?? "").Trim()
                    let clientePorItem = (udItem.Cliente ?? "").Trim()
                    let reservaEstrita = EF.Functions.ILike(clienteOriginal, "RESERVA") &&
                                         EF.Functions.ILike(skilOriginal, "RESERVA")
                    let clienteEfetivo = reservaEstrita
                        ? "RESERVA"
                        : (!string.IsNullOrEmpty(clienteOriginal) &&
                           !EF.Functions.ILike(clienteOriginal, "RESERVA"))
                        ? clienteOriginal
                        : (!string.IsNullOrEmpty(clientePorLinha) &&
                           !EF.Functions.ILike(clientePorLinha, "RESERVA"))
                            ? clientePorLinha
                            : (!string.IsNullOrEmpty(clientePorItem) &&
                               !EF.Functions.ILike(clientePorItem, "RESERVA"))
                                ? clientePorItem
                                : ""
                    let clienteExibicao = string.IsNullOrEmpty(clienteEfetivo) ? "RESERVA" : clienteEfetivo
                    select clienteExibicao
                )
                .Distinct()
                .OrderByDescending(x => EF.Functions.ILike((x ?? "").Trim(), "RESERVA"))
                .ThenBy(x => x)
                .ToListAsync();
            }
            else
            {
                clients = await query
                    .Where(x => !string.IsNullOrEmpty(x.Cliente))
                    .Select(x => x.Cliente!)
                    .Distinct()
                    .OrderBy(x => x)
                    .ToListAsync();
            }

            return Ok(clients);
        }

        // ==========================================================
        // ✅ ENDPOINTS DO FATURAMENTO (PF/PJ)
        // ==========================================================
        [HttpGet("billing")]
        public async Task<ActionResult<PagedResult<BillingClient>>> GetBilling(
            [FromQuery] string? tipo,          // "PF", "PJ" ou null (todos)
            [FromQuery] string? search,        // busca por cliente
            [FromQuery] int page = 1,
            [FromQuery] int pageSize = 20,
            [FromQuery] string? sortBy = "cliente",
            [FromQuery] string? sortDir = "asc")
        {
            page = page < 1 ? 1 : page;
            pageSize = pageSize < 1 ? 20 : pageSize;

            var q = _db.BillingClients.AsNoTracking();

            if (!string.IsNullOrWhiteSpace(tipo))
            {
                var t = tipo.Trim().ToUpperInvariant();
                if (t == "PF" || t == "PJ") q = q.Where(x => x.Tipo == t);
            }

            if (!string.IsNullOrWhiteSpace(search))
            {
                var s = search.Trim();
                q = q.Where(x => EF.Functions.ILike(x.Cliente ?? "", $"%{s}%"));
            }

            var total = await q.CountAsync();

            var sb = (sortBy ?? "cliente").Trim().ToLowerInvariant();
            var desc = string.Equals((sortDir ?? "asc").Trim(), "desc", StringComparison.OrdinalIgnoreCase);

            q = sb switch
            {
                "item" => desc ? q.OrderByDescending(x => x.Item) : q.OrderBy(x => x.Item),
                "tipo" => desc ? q.OrderByDescending(x => x.Tipo).ThenBy(x => x.Cliente) : q.OrderBy(x => x.Tipo).ThenBy(x => x.Cliente),
                "qtdlinhas" => desc ? q.OrderByDescending(x => x.QtdLinhas ?? 0).ThenBy(x => x.Cliente) : q.OrderBy(x => x.QtdLinhas ?? 0).ThenBy(x => x.Cliente),
                "franquiavivo" => desc ? q.OrderByDescending(x => x.FranquiaVivo ?? 0).ThenBy(x => x.Cliente) : q.OrderBy(x => x.FranquiaVivo ?? 0).ThenBy(x => x.Cliente),
                "valorcontratovivo" => desc ? q.OrderByDescending(x => x.ValorContratoVivo ?? 0).ThenBy(x => x.Cliente) : q.OrderBy(x => x.ValorContratoVivo ?? 0).ThenBy(x => x.Cliente),
                "franquialine" => desc ? q.OrderByDescending(x => x.FranquiaLine ?? 0).ThenBy(x => x.Cliente) : q.OrderBy(x => x.FranquiaLine ?? 0).ThenBy(x => x.Cliente),
                "valorcontratoline" => desc ? q.OrderByDescending(x => x.ValorContratoLine ?? 0).ThenBy(x => x.Cliente) : q.OrderBy(x => x.ValorContratoLine ?? 0).ThenBy(x => x.Cliente),
                "lucro" => desc ? q.OrderByDescending(x => x.Lucro ?? 0).ThenBy(x => x.Cliente) : q.OrderBy(x => x.Lucro ?? 0).ThenBy(x => x.Cliente),
                "aparelho" => desc ? q.OrderByDescending(x => x.Aparelho ?? "").ThenBy(x => x.Cliente) : q.OrderBy(x => x.Aparelho ?? "").ThenBy(x => x.Cliente),
                "formapagamento" => desc ? q.OrderByDescending(x => x.FormaPagamento ?? "").ThenBy(x => x.Cliente) : q.OrderBy(x => x.FormaPagamento ?? "").ThenBy(x => x.Cliente),
                _ => desc ? q.OrderByDescending(x => x.Cliente).ThenBy(x => x.Item) : q.OrderBy(x => x.Cliente).ThenBy(x => x.Item),
            };

            var items = await q
                .Skip((page - 1) * pageSize)
                .Take(pageSize)
                .ToListAsync();

            return Ok(new PagedResult<BillingClient>
            {
                Page = page,
                PageSize = pageSize,
                Total = total,
                Items = items
            });
        }

        [HttpGet("billing/clients")]
        public async Task<ActionResult<List<string>>> GetBillingClients([FromQuery] string? tipo)
        {
            var q = _db.BillingClients.AsNoTracking();

            if (!string.IsNullOrWhiteSpace(tipo))
            {
                var t = tipo.Trim().ToUpperInvariant();
                if (t == "PF" || t == "PJ") q = q.Where(x => x.Tipo == t);
            }

            var clients = await q
                .Where(x => !string.IsNullOrEmpty(x.Cliente))
                .Select(x => x.Cliente!)
                .Distinct()
                .OrderBy(x => x)
                .ToListAsync();

            return Ok(clients);
        }

        [HttpGet("account-companies")]
        public ActionResult<List<AccountCompanyDto>> GetAccountCompanies()
        {
            var items = AccountCompanies
                .Select(x => new AccountCompanyDto
                {
                    Empresa = x.Empresa,
                    Contas = x.Contas.ToList()
                })
                .ToList();

            return Ok(items);
        }

        [HttpGet("accounts")]
        public ActionResult<List<string>> GetAccounts([FromQuery] string? empresa)
        {
            if (string.IsNullOrWhiteSpace(empresa))
                return Ok(new List<string>());

            var target = empresa.Trim();

            var contas = AccountCompanies
                .FirstOrDefault(x => string.Equals(x.Empresa, target, StringComparison.OrdinalIgnoreCase))
                ?.Contas
                ?.ToList()
                ?? new List<string>();

            return Ok(contas);
        }

        // ==========================================================
        // ✅ 2.1 ENDPOINT: LINHAS POR CLIENTE (para SELECT do MUREG)
        // GET: /api/lines/by-client?cliente=...
        // ==========================================================
        [HttpGet("by-client")]
        public async Task<ActionResult<List<LineOptionDto>>> GetLinesByClient([FromQuery] string cliente)
        {
            if (string.IsNullOrWhiteSpace(cliente))
                return Ok(new List<LineOptionDto>());

            var c = cliente.Trim();

            // ⚠️ use ILike para não depender de maiúscula/minúscula
            var items = await _db.MobileLines
                .AsNoTracking()
                .Where(x => x.Cliente != null && EF.Functions.ILike(x.Cliente, c))
                .Where(x => x.Linha != null && x.Linha != "")
                .OrderBy(x => x.Item)
                .Select(x => new LineOptionDto
                {
                    Id = x.Id,
                    Item = x.Item,
                    Linha = x.Linha,
                    Chip = x.Chip,
                    Cliente = x.Cliente,
                    Usuario = x.Usuario,
                    Skil = x.Skil
                })
                .ToListAsync();

            return Ok(items);
        }

        // ==========================================================
        // ✅ 3. GET ALL (GERAL)
        // ==========================================================
        [HttpGet]
        public async Task<ActionResult<PagedResult<MobileLineListDto>>> GetAll(
            [FromQuery] string? search,
            [FromQuery] string? skil,
            [FromQuery] string? client,
            [FromQuery] string? additionalMode,
            [FromQuery] string? additionalServices,
            [FromQuery] int page = 1,
            [FromQuery] int pageSize = 20,
            [FromQuery] string? sortBy = "item",
            [FromQuery] string? sortDir = "asc")
        {
            page = page < 1 ? 1 : page;
            pageSize = pageSize < 1 ? 20 : pageSize;

            var q = _db.MobileLines.AsNoTracking();
            var reservaFilter = false;

            if (!string.IsNullOrWhiteSpace(skil))
            {
                var sSkil = skil.Trim();
                if (sSkil.Equals("RESERVA", StringComparison.OrdinalIgnoreCase))
                {
                    reservaFilter = true;
                    q = q.Where(x =>
                        EF.Functions.ILike((x.Usuario ?? "").Trim(), "RESERVA") ||
                        (EF.Functions.ILike((x.Cliente ?? "").Trim(), "RESERVA") &&
                         EF.Functions.ILike((x.Skil ?? "").Trim(), "RESERVA")));
                }
                else
                    q = q.Where(x => EF.Functions.ILike(x.Skil ?? "", $"%{sSkil}%"));
            }

            q = ApplyAdditionalFilters(q, additionalMode, additionalServices);

            var sb = (sortBy ?? "item").Trim().ToLowerInvariant();
            var desc = string.Equals((sortDir ?? "asc").Trim(), "desc", StringComparison.OrdinalIgnoreCase);

            if (sb == "plano") sb = "planocontrato";
            if (sb == "contrato") sb = "vencconta";

            if (reservaFilter)
            {
                var userDataClientByLine = BuildUserDataClientByLineQuery();
                var userDataClientByItem = BuildUserDataClientByItemQuery();
                var rq =
                    from line in q
                    join udLine in userDataClientByLine on line.Linha equals udLine.Linha into udLineJoin
                    from udLine in udLineJoin.DefaultIfEmpty()
                    join udItem in userDataClientByItem on line.Item equals udItem.Item into udItemJoin
                    from udItem in udItemJoin.DefaultIfEmpty()
                    let clienteOriginal = (line.Cliente ?? "").Trim()
                    let skilOriginal = (line.Skil ?? "").Trim()
                    let clientePorLinha = (udLine.Cliente ?? "").Trim()
                    let clientePorItem = (udItem.Cliente ?? "").Trim()
                    let reservaEstrita = EF.Functions.ILike(clienteOriginal, "RESERVA") &&
                                         EF.Functions.ILike(skilOriginal, "RESERVA")
                    let clienteEfetivo = reservaEstrita
                        ? "RESERVA"
                        : (!string.IsNullOrEmpty(clienteOriginal) &&
                           !EF.Functions.ILike(clienteOriginal, "RESERVA"))
                        ? clienteOriginal
                        : (!string.IsNullOrEmpty(clientePorLinha) &&
                           !EF.Functions.ILike(clientePorLinha, "RESERVA"))
                            ? clientePorLinha
                            : (!string.IsNullOrEmpty(clientePorItem) &&
                               !EF.Functions.ILike(clientePorItem, "RESERVA"))
                                ? clientePorItem
                                : ""
                    let clienteExibicao = string.IsNullOrEmpty(clienteEfetivo) ? "RESERVA" : clienteEfetivo
                    select new
                    {
                        line.Id,
                        line.Item,
                        line.Conta,
                        line.Linha,
                        line.Chip,
                        Cliente = clienteExibicao,
                        line.Usuario,
                        line.PlanoContrato,
                        line.Status,
                        line.Skil,
                        line.Modalidade,
                        line.VencConta,
                        line.GestaoVozDados,
                        line.Skeelo,
                        line.VivoNewsPlus,
                        line.VivoTravelMundo,
                        line.VivoSync,
                        line.VivoGestaoDispositivo,
                        line.TipoDeChip
                    };

                if (!string.IsNullOrWhiteSpace(client))
                    rq = rq.Where(x => EF.Functions.ILike(x.Cliente ?? "", client.Trim()));

                if (!string.IsNullOrWhiteSpace(search))
                {
                    var s = search.Trim();
                    rq = rq.Where(x =>
                        EF.Functions.ILike(x.Linha ?? "", $"%{s}%") ||
                        EF.Functions.ILike(x.Chip ?? "", $"%{s}%") ||
                        EF.Functions.ILike(x.Cliente ?? "", $"%{s}%") ||
                        EF.Functions.ILike(x.Usuario ?? "", $"%{s}%") ||
                        EF.Functions.ILike(x.Conta ?? "", $"%{s}%") ||
                        EF.Functions.ILike(x.Status ?? "", $"%{s}%"));
                }

                var totalReserva = await rq.CountAsync();

                rq = sb switch
                {
                    "conta" => desc ? rq.OrderByDescending(x => x.Conta ?? "").ThenBy(x => x.Item) : rq.OrderBy(x => x.Conta ?? "").ThenBy(x => x.Item),
                    "linha" => desc ? rq.OrderByDescending(x => x.Linha ?? "").ThenBy(x => x.Item) : rq.OrderBy(x => x.Linha ?? "").ThenBy(x => x.Item),
                    "chip" => desc ? rq.OrderByDescending(x => x.Chip ?? "").ThenBy(x => x.Item) : rq.OrderBy(x => x.Chip ?? "").ThenBy(x => x.Item),
                    "cliente" => desc
                        ? rq.OrderByDescending(x => EF.Functions.ILike((x.Cliente ?? "").Trim(), "RESERVA"))
                            .ThenByDescending(x => x.Cliente ?? "")
                            .ThenBy(x => x.Item)
                        : rq.OrderByDescending(x => EF.Functions.ILike((x.Cliente ?? "").Trim(), "RESERVA"))
                            .ThenBy(x => x.Cliente ?? "")
                            .ThenBy(x => x.Item),
                    "usuario" => desc ? rq.OrderByDescending(x => x.Usuario ?? "").ThenBy(x => x.Item) : rq.OrderBy(x => x.Usuario ?? "").ThenBy(x => x.Item),
                    "planocontrato" => desc ? rq.OrderByDescending(x => x.PlanoContrato ?? "").ThenBy(x => x.Item) : rq.OrderBy(x => x.PlanoContrato ?? "").ThenBy(x => x.Item),
                    "vencconta" => desc ? rq.OrderByDescending(x => x.VencConta ?? "").ThenBy(x => x.Item) : rq.OrderBy(x => x.VencConta ?? "").ThenBy(x => x.Item),
                    "status" => desc ? rq.OrderByDescending(x => x.Status ?? "").ThenBy(x => x.Item) : rq.OrderBy(x => x.Status ?? "").ThenBy(x => x.Item),
                    "skil" => desc ? rq.OrderByDescending(x => x.Skil ?? "").ThenBy(x => x.Item) : rq.OrderBy(x => x.Skil ?? "").ThenBy(x => x.Item),
                    "modalidade" => desc ? rq.OrderByDescending(x => x.Modalidade ?? "").ThenBy(x => x.Item) : rq.OrderBy(x => x.Modalidade ?? "").ThenBy(x => x.Item),
                    _ => desc ? rq.OrderByDescending(x => x.Item) : rq.OrderBy(x => x.Item)
                };

                var itemsReserva = await rq
                    .Skip((page - 1) * pageSize)
                    .Take(pageSize)
                    .Select(x => new MobileLineListDto
                    {
                        Id = x.Id,
                        Item = x.Item,
                        Conta = x.Conta,
                        Linha = x.Linha,
                        Chip = x.Chip,
                        Cliente = x.Cliente,
                        Usuario = x.Usuario,
                        PlanoContrato = x.PlanoContrato,
                        Status = x.Status,
                        Skil = x.Skil,
                        Modalidade = x.Modalidade,
                        VencConta = x.VencConta,
                        GestaoVozDados = x.GestaoVozDados,
                        Skeelo = x.Skeelo,
                        VivoNewsPlus = x.VivoNewsPlus,
                        VivoTravelMundo = x.VivoTravelMundo,
                        VivoSync = x.VivoSync,
                        VivoGestaoDispositivo = x.VivoGestaoDispositivo,
                        TipoDeChip = x.TipoDeChip
                    })
                    .ToListAsync();

                return Ok(new PagedResult<MobileLineListDto>
                {
                    Page = page,
                    PageSize = pageSize,
                    Total = totalReserva,
                    Items = itemsReserva
                });
            }

            if (!string.IsNullOrWhiteSpace(client))
                q = q.Where(x => EF.Functions.ILike(x.Cliente ?? "", client.Trim()));

            if (!string.IsNullOrWhiteSpace(search))
            {
                var s = search.Trim();
                q = q.Where(x =>
                    EF.Functions.ILike(x.Linha ?? "", $"%{s}%") ||
                    EF.Functions.ILike(x.Chip ?? "", $"%{s}%") ||
                    EF.Functions.ILike(x.Cliente ?? "", $"%{s}%") ||
                    EF.Functions.ILike(x.Usuario ?? "", $"%{s}%") ||
                    EF.Functions.ILike(x.Conta ?? "", $"%{s}%") ||
                    EF.Functions.ILike(x.Status ?? "", $"%{s}%"));
            }

            var total = await q.CountAsync();

            q = sb switch
            {
                "conta" => desc ? q.OrderByDescending(x => x.Conta ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Conta ?? "").ThenBy(x => x.Item),
                "linha" => desc ? q.OrderByDescending(x => x.Linha ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Linha ?? "").ThenBy(x => x.Item),
                "chip" => desc ? q.OrderByDescending(x => x.Chip ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Chip ?? "").ThenBy(x => x.Item),
                "cliente" => desc ? q.OrderByDescending(x => x.Cliente ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Cliente ?? "").ThenBy(x => x.Item),
                "usuario" => desc ? q.OrderByDescending(x => x.Usuario ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Usuario ?? "").ThenBy(x => x.Item),
                "planocontrato" => desc ? q.OrderByDescending(x => x.PlanoContrato ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.PlanoContrato ?? "").ThenBy(x => x.Item),
                "vencconta" => desc ? q.OrderByDescending(x => x.VencConta ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.VencConta ?? "").ThenBy(x => x.Item),
                "status" => desc ? q.OrderByDescending(x => x.Status ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Status ?? "").ThenBy(x => x.Item),
                "skil" => desc ? q.OrderByDescending(x => x.Skil ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Skil ?? "").ThenBy(x => x.Item),
                "modalidade" => desc ? q.OrderByDescending(x => x.Modalidade ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Modalidade ?? "").ThenBy(x => x.Item),
                _ => desc ? q.OrderByDescending(x => x.Item) : q.OrderBy(x => x.Item)
            };

            var items = await q
                .Skip((page - 1) * pageSize)
                .Take(pageSize)
                .Select(x => new MobileLineListDto
                {
                    Id = x.Id,
                    Item = x.Item,
                    Conta = x.Conta,
                    Linha = x.Linha,
                    Chip = x.Chip,
                    Cliente = x.Cliente,
                    Usuario = x.Usuario,
                    PlanoContrato = x.PlanoContrato,
                    Status = x.Status,
                    Skil = x.Skil,
                    Modalidade = x.Modalidade,
                    VencConta = x.VencConta,
                    GestaoVozDados = x.GestaoVozDados,
                    Skeelo = x.Skeelo,
                    VivoNewsPlus = x.VivoNewsPlus,
                    VivoTravelMundo = x.VivoTravelMundo,
                    VivoSync = x.VivoSync,
                    VivoGestaoDispositivo = x.VivoGestaoDispositivo,
                    TipoDeChip = x.TipoDeChip
                })
                .ToListAsync();

            return Ok(new PagedResult<MobileLineListDto>
            {
                Page = page,
                PageSize = pageSize,
                Total = total,
                Items = items
            });
        }

        // ==========================================================
        // ✅ 4. GET BY ID
        // ==========================================================
        [HttpGet("{id:guid}")]
        public async Task<ActionResult<MobileLineDetailDto>> GetById(Guid id)
        {
            var x = await _db.MobileLines.AsNoTracking().FirstOrDefaultAsync(a => a.Id == id);
            if (x == null) return NotFound();
            var vigencia = await FindVigenciaByMobileLineAsync(x, null, asNoTracking: true);
            return Ok(ToDetailDto(x, vigencia));
        }

        // ==========================================================
        // ✅ 5. CREATE
        // ==========================================================
        [HttpPost]
        [Authorize(Roles = "admin,gestor")]
        public async Task<ActionResult<MobileLineDetailDto>> Create([FromBody] CreateMobileLineDto req)
        {
            if (string.IsNullOrWhiteSpace(req.Cliente))
                return BadRequest(new { message = "O nome do Cliente é obrigatório." });

            if (string.IsNullOrWhiteSpace(req.Linha))
                return BadRequest(new { message = "O número da Linha é obrigatório." });

            if (!req.DtEfetivacaoServico.HasValue)
                return BadRequest(new { message = "A Dt. Efetivação Serviço é obrigatória." });

            if (!req.DtTerminoFidelizacao.HasValue)
                return BadRequest(new { message = "A Dt. Término Fidelização é obrigatória." });

            var linhaLimpa = OnlyDigits(req.Linha);
            var chipLimpo = OnlyDigits(req.Chip);

            if (string.IsNullOrWhiteSpace(linhaLimpa))
                return BadRequest(new { message = "Número de linha inválido." });

            var exists = await _db.MobileLines.AsNoTracking().AnyAsync(x => x.Linha == linhaLimpa);
            if (exists)
                return Conflict(new { message = $"A linha {req.Linha} já está cadastrada no sistema." });

            var maxItem = await _db.MobileLines.MaxAsync(x => (int?)x.Item) ?? 0;
            var nextItem = maxItem + 1;

            var now = DateTime.UtcNow;

            var planSuggestion = await AutoFillRules.ResolvePlanSuggestionAsync(_db, req.PlanoContrato);
            var franquiaVivo = req.FranquiaVivo ?? planSuggestion?.FranquiaGb;
            var valorPlanoVivo = req.ValorPlanoVivo ?? planSuggestion?.ValorPlano;

            var newLine = new MobileLine
            {
                Id = Guid.NewGuid(),
                Item = nextItem,
                Cliente = req.Cliente.Trim().ToUpper(),
                Linha = linhaLimpa,
                Chip = string.IsNullOrWhiteSpace(chipLimpo) ? null : chipLimpo,
                Usuario = req.Usuario?.Trim(),
                Status = req.Status?.Trim(),
                Skil = req.Skil?.Trim(),
                Modalidade = req.Modalidade?.Trim(),
                PlanoContrato = req.PlanoContrato?.Trim(),
                Conta = req.Conta?.Trim(),
                VencConta = req.VencConta?.Trim(),

                DataBloqueio = ToUtc(req.DataBloqueio),
                DataEntregaOpera = ToUtc(req.DataEntregaOpera),
                DataEntregaCliente = ToUtc(req.DataEntregaCliente),

                Cedente = req.Cedente?.Trim(),
                Solicitante = req.Solicitante?.Trim(),

                FranquiaVivo = franquiaVivo,
                ValorPlanoVivo = valorPlanoVivo,
                GestaoVozDados = req.GestaoVozDados,
                Skeelo = req.Skeelo,
                VivoNewsPlus = req.VivoNewsPlus,
                VivoTravelMundo = req.VivoTravelMundo,
                VivoSync = req.VivoSync,
                VivoGestaoDispositivo = req.VivoGestaoDispositivo,
                ValorContratoVivo = req.ValorContratoVivo,
                FranquiaLine = req.FranquiaLine,
                FranquiaGestao = req.FranquiaGestao,
                LocacaoAp = req.LocacaoAp,
                ValorContratoLine = req.ValorContratoLine,
                Desconto = req.Desconto,
                Lucro = req.Lucro,
                TipoDeChip = req.TipoDeChip?.Trim(),

                CreatedAt = now,
                UpdatedAt = now
            };

            ApplyReservaRule(newLine);

            _db.MobileLines.Add(newLine);
            var vigencia = await UpsertVigenciaFromMobileLineAsync(
                newLine,
                req.DtEfetivacaoServico,
                req.DtTerminoFidelizacao,
                overrideDates: false);

            try
            {
                await _db.SaveChangesAsync();
                await _vigenciaNotificationSyncService.SyncCurrentTenantAsync();
            }
            catch (DbUpdateException)
            {
                return StatusCode(500, new { message = "Erro ao salvar no banco de dados." });
            }

            return CreatedAtAction(nameof(GetById), new { id = newLine.Id }, ToDetailDto(newLine, vigencia));
        }

        // ==========================================================
        // ✅ 6. UPDATE
        // ==========================================================
        [HttpPut("{id:guid}")]
        [Authorize(Roles = "admin,gestor")]
        public async Task<IActionResult> Update(Guid id, [FromBody] UpdateMobileLineRequest req)
        {
            var x = await _db.MobileLines.FirstOrDefaultAsync(a => a.Id == id);
            if (x == null) return NotFound();
            var previousLinha = x.Linha;

            var newLinha = OnlyDigits(req.Linha);
            if (!string.IsNullOrWhiteSpace(newLinha) && !string.Equals((x.Linha ?? ""), newLinha, StringComparison.Ordinal))
            {
                var exists = await _db.MobileLines.AsNoTracking().AnyAsync(m => m.Linha == newLinha && m.Id != id);
                if (exists) return Conflict(new { message = "Já existe registro com essa LINHA.", linha = newLinha });
            }

            x.Conta = req.Conta?.Trim();
            x.Linha = string.IsNullOrWhiteSpace(newLinha) ? null : newLinha;

            var newChip = OnlyDigits(req.Chip);
            x.Chip = string.IsNullOrWhiteSpace(newChip) ? null : newChip;

            x.Cliente = req.Cliente?.Trim();
            x.Usuario = req.Usuario?.Trim();
            x.PlanoContrato = req.PlanoContrato?.Trim();
            x.FranquiaVivo = req.FranquiaVivo;
            x.ValorPlanoVivo = req.ValorPlanoVivo;
            x.GestaoVozDados = req.GestaoVozDados;
            x.Skeelo = req.Skeelo;
            x.VivoNewsPlus = req.VivoNewsPlus;
            x.VivoTravelMundo = req.VivoTravelMundo;
            x.VivoSync = req.VivoSync;
            x.VivoGestaoDispositivo = req.VivoGestaoDispositivo;
            x.ValorContratoVivo = req.ValorContratoVivo;
            x.FranquiaLine = req.FranquiaLine;
            x.FranquiaGestao = req.FranquiaGestao;
            x.LocacaoAp = req.LocacaoAp;
            x.ValorContratoLine = req.ValorContratoLine;
            x.Desconto = req.Desconto;
            x.Lucro = req.Lucro;
            x.Status = req.Status?.Trim();
            x.DataBloqueio = ToUtc(req.DataBloqueio);
            x.Skil = req.Skil?.Trim();
            x.Modalidade = req.Modalidade?.Trim();
            x.Cedente = req.Cedente?.Trim();
            x.Solicitante = req.Solicitante?.Trim();
            x.DataEntregaOpera = ToUtc(req.DataEntregaOpera);
            x.DataEntregaCliente = ToUtc(req.DataEntregaCliente);
            x.VencConta = req.VencConta?.Trim();
            x.TipoDeChip = req.TipoDeChip?.Trim();

            ApplyReservaRule(x);

            await UpsertVigenciaFromMobileLineAsync(
                x,
                req.DtEfetivacaoServico,
                req.DtTerminoFidelizacao,
                overrideDates: false,
                previousLinha: previousLinha);
            x.UpdatedAt = DateTime.UtcNow;

            try
            {
                await _db.SaveChangesAsync();
                await _vigenciaNotificationSyncService.SyncCurrentTenantAsync();
            }
            catch (DbUpdateException) { return Conflict(new { message = "Conflito ao salvar." }); }

            return NoContent();
        }

        // ==========================================================
        // ✅ 7. DELETE
        // ==========================================================
        [HttpDelete("{id:guid}")]
        [Authorize(Roles = "admin")]
        public async Task<IActionResult> Delete(Guid id)
        {
            var x = await _db.MobileLines.FirstOrDefaultAsync(a => a.Id == id);
            if (x == null) return NotFound();
            _db.MobileLines.Remove(x);
            await _db.SaveChangesAsync();
            await _vigenciaNotificationSyncService.SyncCurrentTenantAsync();
            return NoContent();
        }

        // ==========================================================
        // ✅ 8. IMPORT EXCEL
        // ==========================================================
        [HttpPost("import-excel")]
        [Authorize(Roles = "admin")]
        [Consumes("multipart/form-data")]
        [RequestSizeLimit(50_000_000)]
        public async Task<ActionResult<ImportResultDto>> ImportExcel([FromForm] ImportExcelForm form)
        {
            var tenantId = _tenantProvider.TenantId;
            if (!tenantId.HasValue || tenantId.Value == Guid.Empty)
                return Unauthorized("Tenant inválido.");

            var file = form.File;
            if (file == null || file.Length == 0) return BadRequest("Arquivo inválido.");

            await using var tx = await _db.Database.BeginTransactionAsync();
            SpreadsheetImportAuditSession? auditSession = null;

            try
            {
                using var stream = file.OpenReadStream();
                using var wb = new XLWorkbook(stream);

                // =========================
                // ✅ IMPORTA GERAL
                // =========================
                var ws = wb.Worksheets.FirstOrDefault(w => w.Name.Trim().Equals("GERAL", StringComparison.OrdinalIgnoreCase));
                if (ws == null) return BadRequest("Aba 'GERAL' não encontrada.");

                var headerRow = ws.RowsUsed().FirstOrDefault(r => r.CellsUsed().Any(c => NormalizeHeader(c.GetString()) == "ITEM"));
                if (headerRow == null) return BadRequest("Cabeçalho 'ITEM' não encontrado na aba GERAL.");

                var map = BuildHeaderMap(headerRow);

                int colItem = GetCol(map, "ITEM");
                if (colItem == 0) return BadRequest("Coluna 'ITEM' não encontrada na aba GERAL.");

                var startRow = headerRow.RowNumber() + 1;

                // limpa tudo antes (idempotente)
                // ⚠️ limpa dependências primeiro (evita FK Restrict da MUREG)
                await _db.MuregLines.ExecuteDeleteAsync();
                await _db.MobileLines.ExecuteDeleteAsync();

                var buffer = new List<MobileLine>(600);
                var imported = 0;
                var maxItemFromGeral = 0;

                var lastRow = ws.LastRowUsed()?.RowNumber() ?? startRow;

                for (int r = startRow; r <= lastRow; r++)
                {
                    var itemStr = GetCellString(ws, r, colItem);
                    if (string.IsNullOrWhiteSpace(itemStr)) break;
                    var item = TryInt(itemStr);

                    var linhaDigits = OnlyDigits(GetCellByHeader(ws, r, map, "LINHA"));
                    var chipDigits = OnlyDigits(GetCellByHeader(ws, r, map, "CHIP"));

                    // ✅ se vier vazio, vira null (evita duplicidade de "")
                    var linhaVal = string.IsNullOrWhiteSpace(linhaDigits) ? null : linhaDigits;
                    var chipVal = string.IsNullOrWhiteSpace(chipDigits) ? null : chipDigits;

                    var now = DateTime.UtcNow;

                    var e = new MobileLine
                    {
                        Id = Guid.NewGuid(),
                        Item = item,
                        Conta = GetCellByHeader(ws, r, map, "CONTA"),
                        Linha = linhaVal,
                        Chip = chipVal,
                        Cliente = GetCellByHeader(ws, r, map, "CLIENTE"),
                        Usuario = GetCellByHeader(ws, r, map, "USUARIO"),
                        PlanoContrato = GetCellByHeader(ws, r, map, "PLANO CONTRATO"),

                        FranquiaVivo = TryDecimal(GetCellByHeaderAny(ws, r, map, "FRAQUIA", "FRANQUIA", "FRANQUIA VIVO", "FRAQUIA VIVO")),
                        ValorPlanoVivo = TryDecimal(GetCellByHeaderAny(ws, r, map, "VALOR DO PLANO R$", "VALOR DO PLANO", "VALORPLANO")),
                        GestaoVozDados = TryDecimal(GetCellByHeaderAny(ws, r, map, "GESTAO VOZ E DADOS R$", "GESTAO VOZ E DADOS", "GESTAOVOZEDADOS")),
                        Skeelo = TryDecimal(GetCellByHeaderAny(ws, r, map, "SKEELO")),
                        VivoNewsPlus = TryDecimal(GetCellByHeaderAny(ws, r, map, "VIVO NEWS PLUS")),
                        VivoTravelMundo = TryDecimal(GetCellByHeaderAny(ws, r, map, "VIVO TRAVEL MUNDO")),
                        VivoSync = TryDecimal(GetCellByHeaderAny(ws, r, map, "VIVO SYNC")),
                        VivoGestaoDispositivo = TryDecimal(GetCellByHeaderAny(ws, r, map, "VIVO GESTAO DISPOSITIVO")),
                        ValorContratoVivo = TryDecimal(GetCellByHeaderAny(ws, r, map, "VALOR CONTRATO VIVO", "VALOR DO CONTRATO VIVO")),
                        FranquiaLine = TryDecimal(GetCellByHeaderAny(ws, r, map, "FRANQUIA LINE", "FRAQUIA LINE")),
                        FranquiaGestao = TryDecimal(GetCellByHeaderAny(ws, r, map, "FRANQUIA GESTAO", "FRAQUIA GESTAO")),
                        LocacaoAp = TryDecimal(GetCellByHeaderAny(ws, r, map, "LOCACAO AP.", "LOCACAO AP", "LOCACAOAP")),
                        ValorContratoLine = TryDecimal(GetCellByHeaderAny(ws, r, map, "VALOR CONTRATO LINE", "VALOR DO CONTRATO LINE")),
                        Desconto = TryDecimal(GetCellByHeaderAny(ws, r, map, "DESCONTO")),
                        Lucro = TryDecimal(GetCellByHeaderAny(ws, r, map, "LUCRO")),
                        Status = GetCellByHeader(ws, r, map, "STATUS"),
                        DataBloqueio = TryDate(ws, r, map, "DATA DO BLOQUEIO"),
                        Skil = GetCellByHeader(ws, r, map, "SKIL"),
                        Modalidade = GetCellByHeader(ws, r, map, "MODALIDADE"),
                        Cedente = GetCellByHeader(ws, r, map, "CEDENTE"),
                        Solicitante = GetCellByHeader(ws, r, map, "SOLICITANTE"),
                        DataEntregaOpera = TryDate(ws, r, map, "DATA DA ENTREGA OPERA."),
                        DataEntregaCliente = TryDate(ws, r, map, "DATA DA ENTREGA CLIENTE"),
                        VencConta = GetCellByHeader(ws, r, map, "VENC. DA CONTA"),
                        TipoDeChip = GetCellByHeaderAny(ws, r, map, "TIPO DE CHIP", "TIPO CHIP"),
                        CreatedAt = now,
                        UpdatedAt = now
                    };

                    ApplyReservaRule(e);

                    buffer.Add(e);
                    imported++;
                    if (item > maxItemFromGeral) maxItemFromGeral = item;

                    if (buffer.Count >= 500)
                    {
                        await _db.MobileLines.AddRangeAsync(buffer);
                        await _db.SaveChangesAsync();
                        buffer.Clear();
                    }
                }

                if (buffer.Count > 0)
                {
                    await _db.MobileLines.AddRangeAsync(buffer);
                    await _db.SaveChangesAsync();
                }

                auditSession = _spreadsheetImportAuditService.StartRun(
                    file.FileName,
                    maxItemFromGeral,
                    imported);

                // =========================
                // ✅ IMPORTA MUREG (ALTERADO: NÃO ESTOURA ERRO SE LINHANOVA JÁ EXISTIR)
                // =========================
                await ImportMuregFromWorkbook(wb);

                // =========================
                // ✅ IMPORTA FATURAMENTO PF/PJ
                // =========================
                await ImportBillingFromWorkbook(wb);

                // =========================
                // ✅ IMPORTA DADOS DOS USUÁRIOS (UserDatas)
                // =========================
                var userDataImported = await ImportUserDatasFromWorkbook(wb);
                if (userDataImported)
                {
                    await RepairReservaClientAssignmentsAsync();
                }

                // =========================
                // ✅ IMPORTA VIGÊNCIA
                // =========================
                await ImportVigenciaFromWorkbook(wb);

                // =========================
                // ✅ IMPORTA TROCA DE NÚMERO
                // =========================
                await ImportTrocaNumeroFromWorkbook(wb);

                // =========================
                // ✅ IMPORTA CHIPS VIRGENS
                // =========================
                await ImportChipsVirgensFromWorkbook(wb);

                // =========================
                // ✅ IMPORTA CONTROLE DE RECEBIDOS
                // =========================
                await ImportControleRecebidosFromWorkbook(wb);

                // =========================
                // ✅ IMPORTA RESUMO
                // =========================
                if (auditSession == null)
                {
                    throw new InvalidOperationException("Sessão de auditoria não iniciada.");
                }

                await ImportResumoFromWorkbook(wb, auditSession);

                // =========================
                // ✅ IMPORTA PARCELAMENTOS
                // =========================
                var parcelamentosSummary = await _parcelamentosImportService.ImportFromWorkbookAsync(wb, replaceAll: true);
                if (auditSession != null)
                {
                    await _spreadsheetImportAuditService.SaveRunAsync(auditSession);
                }

                await AddSpreadsheetImportHistoryAsync(file.FileName, imported, parcelamentosSummary);
                await tx.CommitAsync();
                return Ok(new ImportResultDto { Imported = imported, Parcelamentos = parcelamentosSummary });
            }
            catch (Exception ex)
            {
                await tx.RollbackAsync();
                return StatusCode(500, new { message = "Erro ao importar Excel.", detail = ex.Message });
            }
        }

        private async Task AddSpreadsheetImportHistoryAsync(
            string? fileName,
            int imported,
            ParcelamentosImportSummaryDto? parcelamentosSummary)
        {
            var tenantId = _tenantProvider.TenantId;
            if (!tenantId.HasValue)
            {
                return;
            }

            var claimNameId = User.FindFirst(ClaimTypes.NameIdentifier)?.Value;
            var userId = Guid.TryParse(claimNameId, out var parsedUserId) ? parsedUserId : (Guid?)null;

            var userName = User.FindFirst("name")?.Value
                ?? User.FindFirst(ClaimTypes.Name)?.Value
                ?? User.Identity?.Name;

            var userEmail = User.FindFirst(ClaimTypes.Email)?.Value
                ?? User.FindFirst("email")?.Value;

            var changes = new List<AuditFieldChangeDto>
            {
                new() { Field = "Arquivo", ChangeType = "imported", NewValue = string.IsNullOrWhiteSpace(fileName) ? "-" : fileName },
                new() { Field = "LinhasImportadasGeral", ChangeType = "imported", NewValue = imported.ToString(CultureInfo.InvariantCulture) },
                new() { Field = "ParcelamentosLidos", ChangeType = "imported", NewValue = (parcelamentosSummary?.Lidos ?? 0).ToString(CultureInfo.InvariantCulture) },
                new() { Field = "ParcelamentosInseridos", ChangeType = "imported", NewValue = (parcelamentosSummary?.Inseridos ?? 0).ToString(CultureInfo.InvariantCulture) },
                new() { Field = "ParcelamentosAtualizados", ChangeType = "imported", NewValue = (parcelamentosSummary?.Atualizados ?? 0).ToString(CultureInfo.InvariantCulture) }
            };

            _db.AuditLogs.Add(new AuditLog
            {
                TenantId = tenantId.Value,
                OccurredAtUtc = DateTime.UtcNow,
                UserId = userId,
                UserName = string.IsNullOrWhiteSpace(userName) ? "USUARIO" : userName,
                UserEmail = userEmail,
                Action = "IMPORT",
                Page = AuditLogBuilder.SpreadsheetImportPageName,
                EntityName = "SpreadsheetImport",
                EntityId = null,
                EntityLabel = "Importação Excel",
                ChangesJson = JsonSerializer.Serialize(changes),
                RequestPath = HttpContext.Request.Path.Value,
                RequestMethod = HttpContext.Request.Method,
                IpAddress = HttpContext.Connection.RemoteIpAddress?.ToString()
            });

            await _db.SaveChangesAsync();
        }

        // ==========================================================
        // ✅ IMPORTAÇÃO DA ABA MUREG
        // ✅ NOVA REGRA:
        // - Se LinhaNova já existir em OUTRA linha da GERAL => NÃO atualiza a GERAL, NÃO dá erro
        // - Mesmo assim salva o registro na MUREG normalmente
        // - Evita duplicidade na coluna Linha da GERAL
        // ==========================================================
        private async Task ImportMuregFromWorkbook(XLWorkbook wb)
        {
            var wsM = wb.Worksheets.FirstOrDefault(w => w.Name.Trim().Equals("MUREG", StringComparison.OrdinalIgnoreCase))
                      ?? wb.Worksheets.FirstOrDefault(w => w.Name.Trim().ToUpperInvariant().Contains("MUREG"));

            if (wsM == null) return;

            var headerRow = wsM.RowsUsed().FirstOrDefault(r => r.CellsUsed().Any(c => NormalizeHeader(c.GetString()) == "ITEM"));
            if (headerRow == null) return;

            var map = BuildHeaderMap(headerRow);

            int colItem = GetCol(map, "ITEM");
            if (colItem == 0) return;

            var startRow = headerRow.RowNumber() + 1;

            // limpa MUREG antes (idempotente)
            await _db.MuregLines.ExecuteDeleteAsync();

            // ✅ dicionários para resolver MobileLineId por Linha/Chip
            var mobilePairs = await _db.MobileLines
                .AsNoTracking()
                .Select(x => new { x.Id, x.Linha, x.Chip })
                .ToListAsync();

            var mobileByLinha = new Dictionary<string, Guid>(StringComparer.Ordinal);
            var mobileByChip = new Dictionary<string, Guid>(StringComparer.Ordinal);

            foreach (var m in mobilePairs)
            {
                if (!string.IsNullOrWhiteSpace(m.Linha))
                {
                    var k = OnlyDigits(m.Linha);
                    if (!string.IsNullOrWhiteSpace(k) && !mobileByLinha.ContainsKey(k))
                        mobileByLinha[k] = m.Id;
                }

                if (!string.IsNullOrWhiteSpace(m.Chip))
                {
                    var k = OnlyDigits(m.Chip);
                    if (!string.IsNullOrWhiteSpace(k) && !mobileByChip.ContainsKey(k))
                        mobileByChip[k] = m.Id;
                }
            }

            // ✅ cache de entidades tracked para atualizar a GERAL sem consultar toda hora
            var mobileCache = new Dictionary<Guid, MobileLine>();

            var buffer = new List<MuregLine>(600);
            var lastRow = wsM.LastRowUsed()?.RowNumber() ?? startRow;

            for (int r = startRow; r <= lastRow; r++)
            {
                var itemStr = GetCellString(wsM, r, colItem);
                if (string.IsNullOrWhiteSpace(itemStr)) break;

                var linhaAntiga = NullIfEmptyDigits(GetCellByHeader(wsM, r, map, "LINHA ANTIGA"));
                var linhaNova = NullIfEmptyDigits(GetCellByHeader(wsM, r, map, "LINHA NOVA"));
                var iccid = NullIfEmptyDigits(GetCellByHeader(wsM, r, map, "ICCID"));
                var dataMureg = TryDate(wsM, r, map, "DATA DA MUREG");

                // ✅ resolve MobileLineId (prioridade: LinhaAntiga, depois ICCID)
                Guid mobileLineId = Guid.Empty;

                if (!string.IsNullOrWhiteSpace(linhaAntiga) && mobileByLinha.TryGetValue(linhaAntiga, out var idPorLinha))
                    mobileLineId = idPorLinha;
                else if (!string.IsNullOrWhiteSpace(iccid) && mobileByChip.TryGetValue(iccid, out var idPorChip))
                    mobileLineId = idPorChip;

                // Se não encontrou correspondência na GERAL, não dá pra salvar (MobileLineId é obrigatório)
                if (mobileLineId == Guid.Empty)
                    continue;

                // ✅ snapshot da linha antiga: se vier vazia na planilha, pega a linha atual da GERAL
                string? linhaAntigaSnapshot = linhaAntiga;
                if (string.IsNullOrWhiteSpace(linhaAntigaSnapshot))
                {
                    if (!mobileCache.TryGetValue(mobileLineId, out var mobTmp))
                    {
                        mobTmp = await _db.MobileLines.FirstOrDefaultAsync(x => x.Id == mobileLineId);
                        if (mobTmp != null) mobileCache[mobileLineId] = mobTmp;
                    }

                    linhaAntigaSnapshot = mobTmp?.Linha;
                }

                var now = DateTime.UtcNow;

                // ✅ salva MUREG sempre
                var e = new MuregLine
                {
                    Id = Guid.NewGuid(),
                    Item = TryInt(itemStr),
                    MobileLineId = mobileLineId,
                    LinhaAntiga = linhaAntigaSnapshot,
                    LinhaNova = linhaNova,
                    ICCID = iccid,
                    DataDaMureg = dataMureg,
                    CreatedAt = now,
                    UpdatedAt = now
                };

                buffer.Add(e);

                // ✅ REFLETE NA GERAL (somente se NÃO houver conflito)
                if (!string.IsNullOrWhiteSpace(linhaNova))
                {
                    // Se LinhaNova já existe na GERAL em OUTRA MobileLine => ignora update (não duplica)
                    if (mobileByLinha.TryGetValue(linhaNova, out var idJaExiste) && idJaExiste != mobileLineId)
                    {
                        // ignora update da GERAL
                    }
                    else
                    {
                        // carrega entity tracked (cache) e atualiza
                        if (!mobileCache.TryGetValue(mobileLineId, out var mobile))
                        {
                            mobile = await _db.MobileLines.FirstOrDefaultAsync(x => x.Id == mobileLineId);
                            if (mobile != null) mobileCache[mobileLineId] = mobile;
                        }

                        if (mobile != null)
                        {
                            // valida conflito de ICCID também (evita duplicidade de CHIP)
                            var iccidConflita = false;
                            if (!string.IsNullOrWhiteSpace(iccid) &&
                                mobileByChip.TryGetValue(iccid, out var chipJaExiste) &&
                                chipJaExiste != mobileLineId)
                            {
                                iccidConflita = true;
                            }

                            // atualiza Linha
                            mobile.Linha = linhaNova;

                            // atualiza Chip se ICCID vier e NÃO conflitar
                            if (!string.IsNullOrWhiteSpace(iccid) && !iccidConflita)
                                mobile.Chip = iccid;

                            mobile.UpdatedAt = DateTime.UtcNow;

                            // atualiza os dicionários para próximas linhas do MUREG
                            mobileByLinha[linhaNova] = mobileLineId;

                            if (!string.IsNullOrWhiteSpace(iccid) && !iccidConflita)
                                mobileByChip[iccid] = mobileLineId;
                        }
                    }
                }

                if (buffer.Count >= 500)
                {
                    await _db.MuregLines.AddRangeAsync(buffer);
                    await _db.SaveChangesAsync();
                    buffer.Clear();
                }
            }

            if (buffer.Count > 0)
            {
                await _db.MuregLines.AddRangeAsync(buffer);
                await _db.SaveChangesAsync();
            }
        }

        // ==========================================================
        // ✅ IMPORTAÇÃO DO FATURAMENTO (PF/PJ)
        // ==========================================================
        private async Task ImportBillingFromWorkbook(XLWorkbook wb)
        {
            await _db.BillingClients.ExecuteDeleteAsync();

            // PF
            var wsPf = wb.Worksheets.FirstOrDefault(w => w.Name.Trim().Equals("FATURAMENTO PF", StringComparison.OrdinalIgnoreCase))
                    ?? wb.Worksheets.FirstOrDefault(w => w.Name.Trim().ToUpperInvariant().Contains("FATURAMENTO") && w.Name.Trim().ToUpperInvariant().Contains("PF"));
            if (wsPf != null)
                await ImportBillingSheet(wsPf, "PF");

            // PJ
            var wsPj = wb.Worksheets.FirstOrDefault(w => w.Name.Trim().Equals("FATURAMENTO PJ", StringComparison.OrdinalIgnoreCase))
                    ?? wb.Worksheets.FirstOrDefault(w => w.Name.Trim().ToUpperInvariant().Contains("FATURAMENTO") && w.Name.Trim().ToUpperInvariant().Contains("PJ"));
            if (wsPj != null)
                await ImportBillingSheet(wsPj, "PJ");
        }

        private async Task ImportBillingSheet(IXLWorksheet ws, string tipo)
        {
            var headerRow =
                ws.RowsUsed().FirstOrDefault(r =>
                    r.CellsUsed().Any(c => NormalizeHeader(c.GetString()) == "CLIENTE"));

            if (headerRow == null) return;

            var headerRowIndex = headerRow.RowNumber();
            var map = BuildHeaderMap(headerRow);

            // linha acima (grupos VIVO / LINE)
            var groupRowIndex = Math.Max(1, headerRowIndex - 1);
            var groupRow = ws.Row(groupRowIndex);

            var lastCol = GetLastUsedColumn(ws, headerRowIndex);

            var colItem = FindColByAny(headerRow, lastCol, "ITEM");
            var colCliente = FindColByAny(headerRow, lastCol, "CLIENTE");
            if (colCliente == 0) return;

            var colQtd = FindColByAny(headerRow, lastCol, "QTD DE LINHAS", "QTD LINHAS", "QTDDLINHAS");
            var colLucro = FindColByAny(headerRow, lastCol, "LUCRO");
            var colAparelho = FindColByAny(headerRow, lastCol, "APARELHO");
            var colForma = FindColByAny(headerRow, lastCol, "FORMA DE PAGAMENTO", "FORMA PAGAMENTO", "FORMAPAGAMENTO");

            var hasAnyGroup = RowHasAnyText(groupRow);

            int colFranquiaVivo = 0;
            int colValorVivo = 0;
            int colFranquiaLine = 0;
            int colValorLine = 0;

            if (hasAnyGroup)
            {
                colFranquiaVivo = FindColInGroup(groupRow, headerRow, lastCol, "VIVO",
                    "FRANQUIA", "FRAQUIA", "FRANQUIAVIVO", "FRAQUIAVIVO");

                colValorVivo = FindColInGroup(groupRow, headerRow, lastCol, "VIVO",
                    "VALOR CONTRATO VIVO", "VALOR DO CONTRATO VIVO", "VALOR VIVO", "VALOR",
                    "R$", "RS", "");

                colFranquiaLine = FindColInGroup(groupRow, headerRow, lastCol, "LINE",
                    "FRANQUIA LINE", "FRAQUIA LINE", "FRANQUIA", "FRAQUIA", "FRANQUIALINE", "FRAQUIALINE");

                colValorLine = FindColInGroup(groupRow, headerRow, lastCol, "LINE",
                    "VALOR CONTRATO LINE", "VALOR DO CONTRATO LINE", "VALOR LINE", "VALOR",
                    "R$", "RS", "");

                if (colValorVivo == 0 && colFranquiaVivo > 0)
                {
                    var cand = colFranquiaVivo + 1;
                    if (cand <= lastCol)
                    {
                        var g = GetMergedGroupKeyAt(groupRow, cand);
                        if (!string.IsNullOrWhiteSpace(g) && g.Contains(NormalizeHeader("VIVO")))
                            colValorVivo = cand;
                    }
                }

                if (colValorLine == 0 && colFranquiaLine > 0)
                {
                    var cand = colFranquiaLine + 1;
                    if (cand <= lastCol)
                    {
                        var g = GetMergedGroupKeyAt(groupRow, cand);
                        if (!string.IsNullOrWhiteSpace(g) && g.Contains(NormalizeHeader("LINE")))
                            colValorLine = cand;
                    }
                }
            }

            if (colFranquiaVivo == 0 || colValorVivo == 0 || colFranquiaLine == 0 || colValorLine == 0)
            {
                if (colFranquiaLine == 0)
                    colFranquiaLine = GetColAny(map, "FRAQUIA LINE", "FRANQUIA LINE", "FRANQUIALINE", "FRAQUIALINE");

                if (colFranquiaVivo == 0)
                {
                    colFranquiaVivo = GetColAny(map, "FRAQUIA VIVO", "FRANQUIA VIVO", "FRANQUIAVIVO", "FRAQUIAVIVO");
                    if (colFranquiaVivo == 0)
                    {
                        var colFranquia = GetColAny(map, "FRAQUIA", "FRANQUIA");
                        if (colFranquia != 0 && colFranquia != colFranquiaLine) colFranquiaVivo = colFranquia;
                    }
                }

                if (colValorVivo == 0)
                    colValorVivo = GetColAny(map,
                        "VALOR CONTRATO VIVO",
                        "VALOR DO CONTRATO VIVO",
                        "VALOR CONTRATO VIVO R$",
                        "VALOR VIVO");

                if (colValorLine == 0)
                    colValorLine = GetColAny(map,
                        "VALOR CONTRATO LINE",
                        "VALOR DO CONTRATO LINE",
                        "VALOR CONTRATO LINE R$",
                        "VALOR LINE");
            }

            var colRazao = GetColAny(map, "RAZAO SOCIAL", "RAZÃO SOCIAL", "RAZAOSOCIAL");
            var colNome = GetColAny(map, "NOME", "NOME COMPLETO");

            var startRow = headerRowIndex + 1;
            var lastRow = ws.LastRowUsed()?.RowNumber() ?? startRow;

            var buffer = new List<BillingClient>(400);
            var seqItem = 0;

            for (int r = startRow; r <= lastRow; r++)
            {
                var cliente = colCliente > 0 ? GetCellString(ws, r, colCliente) : "";
                var nome = colNome > 0 ? GetCellString(ws, r, colNome) : "";
                var razao = colRazao > 0 ? GetCellString(ws, r, colRazao) : "";

                if (string.IsNullOrWhiteSpace(cliente))
                {
                    if (!string.IsNullOrWhiteSpace(razao)) cliente = razao;
                    else if (!string.IsNullOrWhiteSpace(nome)) cliente = nome;
                }

                if (string.IsNullOrWhiteSpace(cliente)) break;

                seqItem++;

                var itemStr = colItem > 0 ? GetCellString(ws, r, colItem) : "";
                var item = !string.IsNullOrWhiteSpace(itemStr) ? TryInt(itemStr) : seqItem;

                int? qtd = null;
                if (colQtd > 0)
                {
                    var qtdStr = GetCellString(ws, r, colQtd);
                    qtd = TryNullableInt(qtdStr);
                }

                var franquiaVivoStr = colFranquiaVivo > 0 ? GetCellString(ws, r, colFranquiaVivo) : "";
                var franquiaLineStr = colFranquiaLine > 0 ? GetCellString(ws, r, colFranquiaLine) : "";

                var valorContratoVivoStr = colValorVivo > 0 ? GetCellString(ws, r, colValorVivo) : "";
                var valorContratoLineStr = colValorLine > 0 ? GetCellString(ws, r, colValorLine) : "";

                var lucroStr = colLucro > 0 ? GetCellString(ws, r, colLucro) : "";
                var aparelho = colAparelho > 0 ? GetCellString(ws, r, colAparelho) : "";
                var formaPagto = colForma > 0 ? GetCellString(ws, r, colForma) : "";

                var now = DateTime.UtcNow;

                var e = new BillingClient
                {
                    Id = Guid.NewGuid(),
                    Tipo = tipo,
                    Item = item,
                    Cliente = cliente.Trim(),
                    QtdLinhas = qtd,

                    FranquiaVivo = TryDecimal(franquiaVivoStr),
                    ValorContratoVivo = TryDecimal(valorContratoVivoStr),

                    FranquiaLine = TryDecimal(franquiaLineStr),
                    ValorContratoLine = TryDecimal(valorContratoLineStr),

                    Lucro = TryDecimal(lucroStr),

                    Aparelho = string.IsNullOrWhiteSpace(aparelho) ? null : aparelho.Trim(),
                    FormaPagamento = string.IsNullOrWhiteSpace(formaPagto) ? null : formaPagto.Trim(),

                    CreatedAt = now,
                    UpdatedAt = now
                };

                buffer.Add(e);

                if (buffer.Count >= 300)
                {
                    await _db.BillingClients.AddRangeAsync(buffer);
                    await _db.SaveChangesAsync();
                    buffer.Clear();
                }
            }

            if (buffer.Count > 0)
            {
                await _db.BillingClients.AddRangeAsync(buffer);
                await _db.SaveChangesAsync();
            }
        }

        // ==========================================================
        // ✅ IMPORTAÇÃO: DADOS DOS USUÁRIOS (UserDatas)
        // ==========================================================
        private async Task<bool> ImportUserDatasFromWorkbook(XLWorkbook wb)
        {
            var ws = wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("DADOS DOS USUÁRIOS"))
                  ?? wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("DADOS DOS USUARIOS"))
                  ?? wb.Worksheets.FirstOrDefault(w =>
                        NormalizeHeader(w.Name).Contains("DADOS") &&
                        NormalizeHeader(w.Name).Contains("USUAR"));

            if (ws == null) return false;

            var headerRow = ws.RowsUsed().FirstOrDefault(r =>
                r.CellsUsed().Any(c =>
                    NormalizeHeader(c.GetString()) == "ITEM" ||
                    NormalizeHeader(c.GetString()) == "CLIENTE" ||
                    NormalizeHeader(c.GetString()) == "RAZAO SOCIAL" ||
                    NormalizeHeader(c.GetString()) == "NOME"));

            if (headerRow == null) return false;

            var map = BuildHeaderMap(headerRow);

            var colItem = GetCol(map, "ITEM");
            var colCliente = GetCol(map, "CLIENTE");
            var colRazao = GetColAny(map, "RAZAO SOCIAL", "RAZÃO SOCIAL", "RAZAOSOCIAL");
            var colNome = GetColAny(map, "NOME", "NOME COMPLETO");
            var colLinha = GetCol(map, "LINHA");

            if (colCliente == 0 && colRazao == 0 && colNome == 0) return false;

            await _db.UserDatas.ExecuteDeleteAsync();

            var startRow = headerRow.RowNumber() + 1;
            var lastRow = ws.LastRowUsed()?.RowNumber() ?? startRow;

            var buffer = new List<UserData>(500);
            var seq = 0;

            var colCpf = GetColAny(map, "CPF");
            var colCnpj = GetColAny(map, "CNPJ");
            var colRg = GetColAny(map, "RG");
            var colEmail = GetColAny(map, "EMAIL", "E-MAIL");
            var colEndereco = GetColAny(map, "ENDERECO", "ENDEREÇO");
            var colCelular = GetColAny(map, "CELULAR", "CEL");
            var colFixo = GetColAny(map, "TELEFONE FIXO", "TELEFONEFIXO", "FIXO", "TELEFONE");

            var colDataNasc = GetColAny(map,
                "DATA DE NASCIMENTO",
                "DATADENASCIMENTO",
                "DATA NASCIMENTO",
                "DATANASCIMENTO",
                "NASCIMENTO",
                "DTNASC");

            for (int r = startRow; r <= lastRow; r++)
            {
                var cliente = colCliente > 0 ? GetCellString(ws, r, colCliente) : "";
                var razao = colRazao > 0 ? GetCellString(ws, r, colRazao) : "";
                var nome = colNome > 0 ? GetCellString(ws, r, colNome) : "";

                if (string.IsNullOrWhiteSpace(cliente) && string.IsNullOrWhiteSpace(razao) && string.IsNullOrWhiteSpace(nome)) break;

                if (string.IsNullOrWhiteSpace(cliente))
                {
                    cliente = !string.IsNullOrWhiteSpace(razao) ? razao : nome;
                }

                seq++;

                int item;
                if (colItem > 0)
                {
                    var itemStr = GetCellString(ws, r, colItem);
                    item = !string.IsNullOrWhiteSpace(itemStr) ? TryInt(itemStr) : seq;
                }
                else item = seq;

                var linha = colLinha > 0 ? NullIfEmptyDigits(GetCellString(ws, r, colLinha)) : null;

                var cpf = colCpf > 0 ? NullIfEmptyDigits(GetCellString(ws, r, colCpf)) : null;
                var cnpj = colCnpj > 0 ? NullIfEmptyDigits(GetCellString(ws, r, colCnpj)) : null;
                var rg = colRg > 0 ? NullIfEmptyDigits(GetCellString(ws, r, colRg)) : null;

                DateTime? dataNascimento = null;
                if (colDataNasc > 0)
                    dataNascimento = TryDateCell(ws, r, colDataNasc);

                var email = colEmail > 0 ? GetCellString(ws, r, colEmail) : "";
                var endereco = colEndereco > 0 ? GetCellString(ws, r, colEndereco) : "";

                var celular = colCelular > 0 ? NullIfEmptyDigits(GetCellString(ws, r, colCelular)) : null;
                var fixo = colFixo > 0 ? NullIfEmptyDigits(GetCellString(ws, r, colFixo)) : null;

                var now = DateTime.UtcNow;

                var tipoPessoa = !string.IsNullOrWhiteSpace(cnpj) || !string.IsNullOrWhiteSpace(razao)
                    ? "PJ"
                    : "PF";

                var nomeFinal = string.IsNullOrWhiteSpace(nome) ? cliente : nome.Trim();
                var razaoFinal = string.IsNullOrWhiteSpace(razao) ? cliente : razao.Trim();

                var e = new UserData
                {
                    Id = Guid.NewGuid(),
                    Item = item,
                    Linha = linha,
                    Cliente = cliente.Trim(),

                    TipoPessoa = tipoPessoa,
                    Nome = tipoPessoa == "PF" ? nomeFinal : null,
                    RazaoSocial = tipoPessoa == "PJ" ? razaoFinal : null,
                    Cnpj = tipoPessoa == "PJ" ? cnpj : null,
                    Cpf = cpf,
                    Rg = rg,
                    DataNascimento = ToUtc(dataNascimento),

                    Email = string.IsNullOrWhiteSpace(email) ? null : email.Trim(),
                    Endereco = string.IsNullOrWhiteSpace(endereco) ? null : endereco.Trim(),

                    Celular = celular,
                    TelefoneFixo = fixo,

                    CreatedAt = now,
                    UpdatedAt = now
                };

                buffer.Add(e);

                if (buffer.Count >= 400)
                {
                    await _db.UserDatas.AddRangeAsync(buffer);
                    await _db.SaveChangesAsync();
                    buffer.Clear();
                }
            }

            if (buffer.Count > 0)
            {
                await _db.UserDatas.AddRangeAsync(buffer);
                await _db.SaveChangesAsync();
            }

            return true;
        }

        private async Task<int> RepairReservaClientAssignmentsAsync()
        {
            var reservaLines = await _db.MobileLines
                .Where(x => EF.Functions.ILike((x.Cliente ?? "").Trim(), "RESERVA"))
                .Where(x => EF.Functions.ILike((x.Usuario ?? "").Trim(), "RESERVA"))
                .Where(x => !EF.Functions.ILike((x.Skil ?? "").Trim(), "RESERVA"))
                .ToListAsync();

            if (reservaLines.Count == 0) return 0;

            var userDataByLine = await BuildUserDataClientByLineQuery()
                .ToDictionaryAsync(x => x.Linha, x => x.Cliente);
            var userDataByItem = await BuildUserDataClientByItemQuery()
                .ToDictionaryAsync(x => x.Item, x => x.Cliente);

            var updated = 0;
            var now = DateTime.UtcNow;

            foreach (var line in reservaLines)
            {
                var resolvedClient = "";

                if (!string.IsNullOrWhiteSpace(line.Linha) &&
                    userDataByLine.TryGetValue(line.Linha, out var clientByLine) &&
                    !string.IsNullOrWhiteSpace(clientByLine))
                {
                    resolvedClient = clientByLine.Trim();
                }

                if (string.IsNullOrWhiteSpace(resolvedClient) &&
                    userDataByItem.TryGetValue(line.Item, out var clientByItem) &&
                    !string.IsNullOrWhiteSpace(clientByItem))
                {
                    resolvedClient = clientByItem.Trim();
                }

                if (string.IsNullOrWhiteSpace(resolvedClient) ||
                    string.Equals(resolvedClient, "RESERVA", StringComparison.OrdinalIgnoreCase))
                {
                    continue;
                }

                if (string.Equals((line.Cliente ?? "").Trim(), resolvedClient, StringComparison.OrdinalIgnoreCase))
                {
                    continue;
                }

                line.Cliente = resolvedClient;
                line.UpdatedAt = now;
                updated++;
            }

            if (updated > 0)
            {
                await _db.SaveChangesAsync();
            }

            return updated;
        }

        // ==========================================================
        // ✅ IMPORTAÇÃO: VIGÊNCIA
        // ==========================================================
        private async Task ImportVigenciaFromWorkbook(XLWorkbook wb)
        {
            var ws = wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("VIGÊNCIA"))
                  ?? wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("VIGENCIA"))
                  ?? wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name).Contains("VIGEN"));

            if (ws == null) return;

            var headerRow = ws.RowsUsed().FirstOrDefault(r =>
                r.CellsUsed().Any(c =>
                {
                    var k = NormalizeHeader(c.GetString());
                    return k == "ITEM" || k == "ITEM(ID)" || k == "ITEMID";
                }));

            if (headerRow == null) return;

            var map = BuildHeaderMap(headerRow);

            var colItem = GetColAny(map, "ITEM", "ITEM(ID)", "ITEMID", "ITEM (ID)", "ITÉM (ID)");
            if (colItem == 0) return;

            var startRow = headerRow.RowNumber() + 1;
            var lastRow = ws.LastRowUsed()?.RowNumber() ?? startRow;

            var tenantId = GetTenantIdFromClaims();
            var notificationsQuery = _db.Notifications
                .IgnoreQueryFilters()
                .Where(n => n.VigenciaLineId != null);
            if (tenantId.HasValue)
            {
                notificationsQuery = notificationsQuery.Where(n => n.TenantId == tenantId.Value);
            }
            await notificationsQuery.ExecuteUpdateAsync(setters =>
                setters.SetProperty(n => n.VigenciaLineId, n => null));

            var vigenciaQuery = _db.VigenciaLines.IgnoreQueryFilters();
            if (tenantId.HasValue)
            {
                vigenciaQuery = vigenciaQuery.Where(v => v.TenantId == tenantId.Value);
            }
            await vigenciaQuery.ExecuteDeleteAsync();

            var buffer = new List<VigenciaLine>(600);

            for (int r = startRow; r <= lastRow; r++)
            {
                var itemStr = GetCellString(ws, r, colItem);
                if (string.IsNullOrWhiteSpace(itemStr)) break;

                var conta = GetCellByHeader(ws, r, map, "CONTA");
                var linha = NullIfEmptyDigits(GetCellByHeader(ws, r, map, "LINHA"));
                var cliente = GetCellByHeader(ws, r, map, "CLIENTE");
                var usuario = GetCellByHeader(ws, r, map, "USUÁRIO");
                if (string.IsNullOrWhiteSpace(usuario))
                    usuario = GetCellByHeader(ws, r, map, "USUARIO");

                var plano = GetCellByHeader(ws, r, map, "PLANO CONTRATO");

                var dtEfet = TryDateNoUtc(ws, r, map, "DT. DE EFETIVAÇÃO DO SERVIÇO");
                if (dtEfet == null) dtEfet = TryDateNoUtc(ws, r, map, "DT. DE EFETIVACAO DO SERVICO");

                var dtFim = TryDateNoUtc(ws, r, map, "DT. DE TÉRMINO DA FIDELIZAÇÃO");
                if (dtFim == null) dtFim = TryDateNoUtc(ws, r, map, "DT. DE TERMINO DA FIDELIZACAO");

                var totalStr = GetCellByHeader(ws, r, map, "TOTAL");
                var now = DateTime.UtcNow;

                var e = new VigenciaLine
                {
                    Id = Guid.NewGuid(),
                    Item = TryInt(itemStr),
                    Conta = string.IsNullOrWhiteSpace(conta) ? null : conta.Trim(),
                    Linha = linha,
                    Cliente = string.IsNullOrWhiteSpace(cliente) ? null : cliente.Trim(),
                    Usuario = string.IsNullOrWhiteSpace(usuario) ? null : usuario.Trim(),
                    PlanoContrato = string.IsNullOrWhiteSpace(plano) ? null : plano.Trim(),
                    DtEfetivacaoServico = dtEfet,
                    DtTerminoFidelizacao = dtFim,
                    Total = TryDecimal(totalStr),
                    CreatedAt = now,
                    UpdatedAt = now
                };

                buffer.Add(e);

                if (buffer.Count >= 500)
                {
                    await _db.VigenciaLines.AddRangeAsync(buffer);
                    await _db.SaveChangesAsync();
                    buffer.Clear();
                }
            }

            if (buffer.Count > 0)
            {
                await _db.VigenciaLines.AddRangeAsync(buffer);
                await _db.SaveChangesAsync();
            }
        }

        // ==========================================================
        // ✅ IMPORTAÇÃO: TROCA DE NÚMERO
        // ==========================================================
        private async Task ImportTrocaNumeroFromWorkbook(XLWorkbook wb)
        {
            var ws = wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("TROCA DE NÚMERO"))
                  ?? wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("TROCA DE NUMERO"))
                  ?? wb.Worksheets.FirstOrDefault(w =>
                        NormalizeHeader(w.Name).Contains("TROCA") &&
                        NormalizeHeader(w.Name).Contains("NUMER"));

            if (ws == null) return;

            var headerRow = ws.RowsUsed().FirstOrDefault(r =>
                r.CellsUsed().Any(c => NormalizeHeader(c.GetString()) == "ITEM"));

            if (headerRow == null) return;

            var map = BuildHeaderMap(headerRow);

            var colItem = GetCol(map, "ITEM");
            if (colItem == 0) return;

            var startRow = headerRow.RowNumber() + 1;
            var lastRow = ws.LastRowUsed()?.RowNumber() ?? startRow;

            await _db.TrocaNumeroLines.ExecuteDeleteAsync();

            var buffer = new List<TrocaNumeroLine>(600);

            for (int r = startRow; r <= lastRow; r++)
            {
                var itemStr = GetCellString(ws, r, colItem);
                if (string.IsNullOrWhiteSpace(itemStr)) break;

                var linhaAntiga = NullIfEmptyDigits(GetCellByHeader(ws, r, map, "LINHA ANTIGA"));
                var linhaNova = NullIfEmptyDigits(GetCellByHeader(ws, r, map, "LINHA NOVA"));
                var iccid = NullIfEmptyDigits(GetCellByHeader(ws, r, map, "ICCID"));

                var dataTroca = TryDate(ws, r, map, "DATA TROCA");
                if (dataTroca == null) dataTroca = TryDate(ws, r, map, "DATA DA TROCA");

                var motivo = GetCellByHeader(ws, r, map, "MOTIVO");
                var obs = GetCellByHeader(ws, r, map, "OBSERVAÇÃO");
                if (string.IsNullOrWhiteSpace(obs)) obs = GetCellByHeader(ws, r, map, "OBSERVACAO");

                var now = DateTime.UtcNow;

                var e = new TrocaNumeroLine
                {
                    Id = Guid.NewGuid(),
                    Item = TryInt(itemStr),
                    LinhaAntiga = linhaAntiga,
                    LinhaNova = linhaNova,
                    ICCID = iccid,
                    DataTroca = dataTroca,
                    Motivo = string.IsNullOrWhiteSpace(motivo) ? null : motivo.Trim(),
                    Observacao = string.IsNullOrWhiteSpace(obs) ? null : obs.Trim(),
                    CreatedAt = now,
                    UpdatedAt = now
                };

                buffer.Add(e);

                if (buffer.Count >= 500)
                {
                    await _db.TrocaNumeroLines.AddRangeAsync(buffer);
                    await _db.SaveChangesAsync();
                    buffer.Clear();
                }
            }

            if (buffer.Count > 0)
            {
                await _db.TrocaNumeroLines.AddRangeAsync(buffer);
                await _db.SaveChangesAsync();
            }
        }

        // ==========================================================
        // ✅ IMPORTAÇÃO CHIPS VIRGENS
        // ==========================================================
        private async Task ImportChipsVirgensFromWorkbook(XLWorkbook wb)
        {
            var ws = wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("CHIP VIRGENS E CONTROLE DE RECEBIDOS"))
                ?? wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("CHIPS VIRGENS"))
                ?? wb.Worksheets.FirstOrDefault(w =>
                {
                    var name = NormalizeHeader(w.Name);
                    return name.Contains("CHIP") && name.Contains("VIRGEN");
                });

            if (ws == null) return;

            var headers = new List<IXLRow>();
            foreach (var rowIndex in new[] { 7, 8 })
            {
                var row = ws.Row(rowIndex);
                if (IsChipsVirgensHeader(row))
                {
                    headers.Add(row);
                }
            }

            if (headers.Count == 0)
            {
                headers = ws.RowsUsed()
                    .Where(IsChipsVirgensHeader)
                    .OrderBy(r => r.RowNumber())
                    .ToList();
            }
            if (headers.Count == 0) return;

            await _db.ChipVirgemLines.ExecuteDeleteAsync();

            var buffer = new List<ChipVirgemLine>(500);
            var lastRow = ws.LastRowUsed()?.RowNumber() ?? 1;

            for (int i = 0; i < headers.Count; i++)
            {
                var headerRow = headers[i];
                var lastCol = headerRow.LastCellUsed()?.Address.ColumnNumber
                              ?? ws.LastColumnUsed()?.ColumnNumber()
                              ?? headerRow.LastCellUsed()?.Address.ColumnNumber
                              ?? 1;
                var itemColumns = headerRow.CellsUsed()
                    .Where(c => NormalizeHeader(c.GetString()) == "ITEM")
                    .Select(c => c.Address.ColumnNumber)
                    .OrderBy(c => c)
                    .ToList();
                if (itemColumns.Count == 0) continue;

                var startRow = headerRow.RowNumber() + 1;
                var endRow = lastRow;

                for (int tableIndex = 0; tableIndex < itemColumns.Count; tableIndex++)
                {
                    var startCol = itemColumns[tableIndex];
                    var endCol = tableIndex + 1 < itemColumns.Count
                        ? itemColumns[tableIndex + 1] - 1
                        : lastCol;

                    int colItem = startCol;
                    int colChip = FindHeaderColumn(headerRow, startCol, endCol, "CHIP");
                    int colObs = FindHeaderColumn(headerRow, startCol, endCol, "OBS");
                    if (colItem == 0 || colChip == 0 || colObs == 0) continue;

                    for (int r = startRow; r <= endRow; r++)
                    {
                        var itemStr = GetCellString(ws, r, colItem);
                        if (string.IsNullOrWhiteSpace(itemStr)) break;

                        var chipRaw = GetCellString(ws, r, colChip);
                        var numeroChip = NullIfEmptyDigits(chipRaw);
                        if (string.IsNullOrWhiteSpace(numeroChip) && !string.IsNullOrWhiteSpace(chipRaw))
                        {
                            numeroChip = chipRaw.Trim();
                        }
                        var observacoes = GetCellString(ws, r, colObs);
                        if (string.IsNullOrWhiteSpace(numeroChip) && string.IsNullOrWhiteSpace(observacoes))
                        {
                            continue;
                        }

                        var now = DateTime.UtcNow;

                        var e = new ChipVirgemLine
                        {
                            Id = Guid.NewGuid(),
                            Item = TryInt(itemStr),
                            NumeroDoChip = numeroChip,
                            Observacoes = string.IsNullOrWhiteSpace(observacoes) ? null : observacoes.Trim(),
                            CreatedAt = now,
                            UpdatedAt = now
                        };

                        buffer.Add(e);

                        if (buffer.Count >= 500)
                        {
                            await _db.ChipVirgemLines.AddRangeAsync(buffer);
                            await _db.SaveChangesAsync();
                            buffer.Clear();
                        }
                    }
                }
            }

            if (buffer.Count > 0)
            {
                await _db.ChipVirgemLines.AddRangeAsync(buffer);
                await _db.SaveChangesAsync();
            }
        }

        // ==========================================================
        // ✅ IMPORTAÇÃO CONTROLE DE RECEBIDOS (2022-2025)
        // ==========================================================
        private async Task ImportControleRecebidosFromWorkbook(XLWorkbook wb)
        {
            await _db.ControleRecebidoLines.ExecuteDeleteAsync();

            var years = new[] { 2022, 2023, 2024, 2025 };
            var importedYears = new HashSet<int>();

            foreach (var info in GetControleRecebidosWorksheets(wb))
            {
                await ImportControleRecebidosSheet(info.Sheet, info.Year);
                importedYears.Add(info.Year);
            }

            foreach (var year in years)
            {
                if (importedYears.Contains(year)) continue;

                var ws = FindControleRecebidosWorksheet(wb, year);
                if (ws == null) continue;

                await ImportControleRecebidosSheet(ws, year);
            }
        }

        // ==========================================================
        // ✅ IMPORTAÇÃO DA ABA RESUMO
        // ==========================================================
        private async Task ImportResumoFromWorkbook(XLWorkbook wb, SpreadsheetImportAuditSession auditSession)
        {
            var ws = wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("RESUMO"));
            if (ws == null) return;

            await _db.ResumoMacrophonyPlans.ExecuteDeleteAsync();
            await _db.ResumoMacrophonyTotals.ExecuteDeleteAsync();
            await _db.ResumoVivoLineResumos.ExecuteDeleteAsync();
            await _db.ResumoVivoLineTotals.ExecuteDeleteAsync();
            await _db.ResumoClienteEspeciais.ExecuteDeleteAsync();
            await _db.ResumoPlanoContratoResumos.ExecuteDeleteAsync();
            await _db.ResumoPlanoContratoTotals.ExecuteDeleteAsync();
            await _db.ResumoLineTotais.ExecuteDeleteAsync();
            await _db.ResumoGbDistribuicoes.ExecuteDeleteAsync();
            await _db.ResumoGbDistribuicaoTotais.ExecuteDeleteAsync();
            await _db.ResumoReservaLines.ExecuteDeleteAsync();
            await _db.ResumoReservaTotals.ExecuteDeleteAsync();

            var now = DateTime.UtcNow;

            await ImportResumoTabela1(ws, now, auditSession);
            await ImportResumoTabela2(ws, now, auditSession);
            await ImportResumoTabela3(ws, now);
            await ImportResumoTabela4(ws, now);
            await ImportResumoTabela7(ws, now);
            await ImportResumoTabela5(ws, now);
            await ImportResumoTabela6(ws, now);
        }

        private async Task ImportResumoTabela1(IXLWorksheet ws, DateTime now, SpreadsheetImportAuditSession auditSession)
        {
            var lastRowUsed = ws.LastRowUsed()?.RowNumber() ?? 1;
            var headerRow = FindHeaderRowForMacrophonyPlans(ws, 1, lastRowUsed);
            if (headerRow == 0) return;

            var map = BuildHeaderMap(ws.Row(headerRow));
            var colPlano = GetCol(map, "PLANO CONTRATO");
            var colGb = GetCol(map, "GB");
            var colValorIndividual = GetColAny(map, "VALOR INDIVIDUAL C/ SVAs", "VALOR INDIVIDUAL C/ SVAS", "VALOR INDIVIDUAL");
            var colFranquiaGb = GetColAny(map, "FRANQUIA GB", "FRAQUIA GB");
            var colTotalLinhas = GetColAny(map, "TOTAL DE LINHAS", "TOTAL LINHAS");
            var colValorTotal = GetCol(map, "VALOR TOTAL");

            var buffer = new List<ResumoMacrophonyPlan>(200);
            string? lastPlanoContrato = null;
            decimal? lastGb = null;
            var dataStarted = false;
            var emptyDataStreak = 0;
            int? totalRowIndex = null;
            var missingPlanoCount = 0;
            var missingGbCount = 0;

            for (int r = headerRow + 1; r <= lastRowUsed; r++)
            {
                var plano = GetCellString(ws, r, colPlano);
                var gb = GetCellString(ws, r, colGb);
                var valorInd = GetCellString(ws, r, colValorIndividual);
                var franquia = GetCellString(ws, r, colFranquiaGb);
                var totalLinhas = GetCellString(ws, r, colTotalLinhas);
                var valorTotal = GetCellString(ws, r, colValorTotal);

                var isPlanoTotal = !string.IsNullOrWhiteSpace(plano)
                    && NormalizeHeader(plano) == NormalizeHeader("TOTAL");

                if (isPlanoTotal)
                {
                    totalRowIndex = r;
                    break;
                }

                var hasAnyValue = !(string.IsNullOrWhiteSpace(plano)
                    && string.IsNullOrWhiteSpace(gb)
                    && string.IsNullOrWhiteSpace(valorInd)
                    && string.IsNullOrWhiteSpace(franquia)
                    && string.IsNullOrWhiteSpace(totalLinhas)
                    && string.IsNullOrWhiteSpace(valorTotal));

                if (!hasAnyValue)
                {
                    if (dataStarted)
                    {
                        emptyDataStreak++;
                        if (emptyDataStreak >= 2) break;
                    }
                    continue;
                }

                emptyDataStreak = 0;

                var franquiaValue = TryDecimal(franquia);
                var totalLinhasValue = TryNullableInt(totalLinhas);
                var isDataRow = franquiaValue.HasValue || totalLinhasValue.HasValue;
                if (isDataRow) dataStarted = true;
                if (!isDataRow && dataStarted)
                {
                    break;
                }
                if (!isDataRow)
                {
                    continue;
                }

                var planoNormalized = NormalizeHeader(plano);
                if (!string.IsNullOrWhiteSpace(plano)
                    && planoNormalized != NormalizeHeader("PLANO CONTRATO")
                    && planoNormalized != NormalizeHeader("TOTAL"))
                {
                    lastPlanoContrato = plano.Trim();
                }

                var gbValue = TryDecimal(gb);
                if (gbValue.HasValue)
                {
                    lastGb = gbValue;
                }

                var resolvedPlano = isDataRow
                    ? (string.IsNullOrWhiteSpace(plano) ? lastPlanoContrato : plano.Trim())
                    : (string.IsNullOrWhiteSpace(plano) ? null : plano.Trim());

                var resolvedGb = isDataRow
                    ? (gbValue ?? lastGb)
                    : gbValue;

                if (isDataRow && string.IsNullOrWhiteSpace(resolvedPlano))
                {
                    missingPlanoCount++;
                }

                if (isDataRow && !resolvedGb.HasValue)
                {
                    missingGbCount++;
                }

                var vivoTravelCell = ws.Cell(r, 8).GetString();
                var vivoTravel = !string.IsNullOrWhiteSpace(vivoTravelCell)
                                 && vivoTravelCell.Contains("VIVO TRAVEL", StringComparison.OrdinalIgnoreCase);

                buffer.Add(new ResumoMacrophonyPlan
                {
                    PlanoContrato = string.IsNullOrWhiteSpace(resolvedPlano) ? null : resolvedPlano,
                    Gb = resolvedGb,
                    ValorIndividualComSvas = TryDecimal(valorInd),
                    FranquiaGb = franquiaValue,
                    TotalLinhas = totalLinhasValue,
                    ValorTotal = TryDecimal(valorTotal),
                    VivoTravel = vivoTravel,
                    CreatedAt = now,
                    UpdatedAt = now
                });
            }

            if (buffer.Count > 0)
            {
                await _db.ResumoMacrophonyPlans.AddRangeAsync(buffer);
                await _db.SaveChangesAsync();
            }

            if (missingPlanoCount > 0 || missingGbCount > 0)
            {
                throw new InvalidOperationException($"Import RESUMO/MACROPHONY: {missingPlanoCount} linhas sem PLANO CONTRATO e {missingGbCount} linhas sem GB.");
            }

            if (totalRowIndex == null)
            {
                return;
            }

            var totalLinhasSource = TryNullableInt(GetCellString(ws, totalRowIndex.Value, colTotalLinhas));
            var total = new ResumoMacrophonyTotal
            {
                FranquiaGbTotal = TryDecimal(GetCellString(ws, totalRowIndex.Value, colFranquiaGb)),
                TotalLinhasTotal = _spreadsheetImportAuditService.CanonicalizeTotalLinhas(
                    auditSession,
                    "RESUMO.MACROPHONY_TOTAL",
                    "TotalLinhasTotal",
                    totalLinhasSource,
                    "HIGH"),
                ValorTotal = TryDecimal(GetCellString(ws, totalRowIndex.Value, colValorTotal)),
                CreatedAt = now,
                UpdatedAt = now
            };

            await _db.ResumoMacrophonyTotals.AddAsync(total);
            await _db.SaveChangesAsync();
        }

        private static int FindHeaderRowForMacrophonyPlans(IXLWorksheet ws, int startRow, int lastRow)
        {
            for (int r = startRow; r <= lastRow; r++)
            {
                var row = ws.Row(r);
                if (!row.CellsUsed().Any()) continue;

                var map = BuildHeaderMap(row);
                var hasPlano = GetCol(map, "PLANO CONTRATO") > 0;
                var hasGb = GetCol(map, "GB") > 0;
                var hasTotalLinhas = GetColAny(map, "TOTAL DE LINHAS", "TOTAL LINHAS") > 0;

                if (hasPlano && hasGb && hasTotalLinhas)
                {
                    return r;
                }
            }

            return 0;
        }

        private async Task ImportResumoTabela2(IXLWorksheet ws, DateTime now, SpreadsheetImportAuditSession auditSession)
        {
            const int headerRow = 5;
            const int totalRow = 219;
            var lastRow = Math.Min(totalRow - 1, ws.LastRowUsed()?.RowNumber() ?? totalRow - 1);

            var map = BuildHeaderMap(ws.Row(headerRow));
            var colSkil = GetCol(map, "SKIL");
            var colCliente = GetCol(map, "CLIENTE");
            var colQtdLinhas = GetColAny(map, "QTD DE LINHAS", "QTD. DE LINHAS", "QTD LINHAS");
            var colFranquiaTotal = GetColAny(map, "FRANQUIA TOTAL", "FRAQUIA TOTAL");
            var colValorContratoVivo = GetColAny(map, "VALOR CONTRATO VIVO", "VALOR DO CONTRATO VIVO");
            var colFranquiaLine = GetColAny(map, "FRANQUIA LINE", "FRAQUIA LINE");
            var colValorContratoLine = GetColAny(map, "VALOR CONTRATO LINE", "VALOR DO CONTRATO LINE");
            var colLucro = GetCol(map, "LUCRO");

            var buffer = new List<ResumoVivoLineResumo>(400);

            for (int r = headerRow + 1; r <= lastRow; r++)
            {
                var skil = GetCellString(ws, r, colSkil);
                var cliente = GetCellString(ws, r, colCliente);
                var qtdLinhas = GetCellString(ws, r, colQtdLinhas);
                var franquiaTotal = GetCellString(ws, r, colFranquiaTotal);
                var valorContratoVivo = GetCellString(ws, r, colValorContratoVivo);
                var franquiaLine = GetCellString(ws, r, colFranquiaLine);
                var valorContratoLine = GetCellString(ws, r, colValorContratoLine);
                var lucro = GetCellString(ws, r, colLucro);

                if (string.IsNullOrWhiteSpace(skil)
                    && string.IsNullOrWhiteSpace(cliente)
                    && string.IsNullOrWhiteSpace(qtdLinhas)
                    && string.IsNullOrWhiteSpace(franquiaTotal)
                    && string.IsNullOrWhiteSpace(valorContratoVivo)
                    && string.IsNullOrWhiteSpace(franquiaLine)
                    && string.IsNullOrWhiteSpace(valorContratoLine)
                    && string.IsNullOrWhiteSpace(lucro))
                {
                    continue;
                }

                buffer.Add(new ResumoVivoLineResumo
                {
                    Skil = string.IsNullOrWhiteSpace(skil) ? null : skil.Trim(),
                    Cliente = string.IsNullOrWhiteSpace(cliente) ? null : cliente.Trim(),
                    QtdLinhas = TryNullableInt(qtdLinhas),
                    FranquiaTotal = TryDecimal(franquiaTotal),
                    ValorContratoVivo = TryDecimal(valorContratoVivo),
                    FranquiaLine = TryDecimal(franquiaLine),
                    ValorContratoLine = TryDecimal(valorContratoLine),
                    Lucro = TryDecimal(lucro),
                    CreatedAt = now,
                    UpdatedAt = now
                });
            }

            if (buffer.Count > 0)
            {
                await _db.ResumoVivoLineResumos.AddRangeAsync(buffer);
                await _db.SaveChangesAsync();
            }

            var qtdLinhasTotalSource = TryNullableInt(GetCellString(ws, totalRow, colQtdLinhas));
            var total = new ResumoVivoLineTotal
            {
                QtdLinhasTotal = _spreadsheetImportAuditService.CanonicalizeTotalLinhas(
                    auditSession,
                    "RESUMO.VIVO_LINE_TOTAL",
                    "QtdLinhasTotal",
                    qtdLinhasTotalSource,
                    "HIGH"),
                FranquiaTotal = TryDecimal(GetCellString(ws, totalRow, colFranquiaTotal)),
                ValorContratoVivo = TryDecimal(GetCellString(ws, totalRow, colValorContratoVivo)),
                FranquiaLine = TryDecimal(GetCellString(ws, totalRow, colFranquiaLine)),
                ValorContratoLine = TryDecimal(GetCellString(ws, totalRow, colValorContratoLine)),
                Lucro = TryDecimal(GetCellString(ws, totalRow, colLucro)),
                CreatedAt = now,
                UpdatedAt = now
            };

            await _db.ResumoVivoLineTotals.AddAsync(total);
            await _db.SaveChangesAsync();
        }

        private async Task ImportResumoTabela3(IXLWorksheet ws, DateTime now)
        {
            const int headerStartRow = 223;
            const int headerEndRow = 225;
            const int valuesRow = 227;

            var headerColumns = new Dictionary<int, string>();
            for (int row = headerStartRow; row <= headerEndRow; row++)
            {
                var rowData = ws.Row(row);
                var lastCol = rowData.LastCellUsed()?.Address.ColumnNumber ?? 1;
                for (int col = 1; col <= lastCol; col++)
                {
                    var name = rowData.Cell(col).GetString();
                    if (string.IsNullOrWhiteSpace(name)) continue;
                    if (!headerColumns.ContainsKey(col))
                    {
                        headerColumns[col] = name.Trim();
                    }
                }
            }

            if (headerColumns.Count == 0)
            {
                return;
            }

            var buffer = new List<ResumoClienteEspecial>(headerColumns.Count);
            foreach (var entry in headerColumns)
            {
                var valueStr = ws.Cell(valuesRow, entry.Key).GetString();
                buffer.Add(new ResumoClienteEspecial
                {
                    Nome = entry.Value,
                    Valor = TryDecimal(valueStr),
                    CreatedAt = now,
                    UpdatedAt = now
                });
            }

            await _db.ResumoClienteEspeciais.AddRangeAsync(buffer);
            await _db.SaveChangesAsync();
        }

        private async Task ImportResumoTabela4(IXLWorksheet ws, DateTime now)
        {
            var lastRowUsed = ws.LastRowUsed()?.RowNumber() ?? 1;
            var headerRow = FindHeaderRowForPlanoContratoResumo(ws, 1, lastRowUsed);
            if (headerRow == 0) return;

            var map = BuildHeaderMap(ws.Row(headerRow));
            var colPlano = GetCol(map, "PLANO CONTRATO");
            var colGb = GetCol(map, "GB");
            var colValorIndividual = GetColAny(map, "VALOR INDIVIDUAL C/ SVAs", "VALOR INDIVIDUAL C/ SVAS", "VALOR INDIVIDUAL");
            var colFranquiaGb = GetColAny(map, "FRANQUIA GB", "FRAQUIA GB");
            var colTotalLinhas = GetColAny(map, "TOTAL DE LINHAS", "TOTAL LINHAS");
            var colValorTotal = GetCol(map, "VALOR TOTAL");
            var colCliente = GetCol(map, "CLIENTE");
            var colQtdLinhas = GetColAny(map, "QTD DE LINHAS", "QTD. DE LINHAS", "QTD LINHAS");

            var buffer = new List<ResumoPlanoContratoResumo>(200);
            string? lastPlanoContrato = null;
            var dataStarted = false;
            var emptyDataStreak = 0;
            int? totalRowIndex = null;
            var missingPlanoCount = 0;

            for (int r = headerRow + 1; r <= lastRowUsed; r++)
            {
                var plano = GetCellString(ws, r, colPlano);
                var gb = GetCellString(ws, r, colGb);
                var valorInd = GetCellString(ws, r, colValorIndividual);
                var franquia = GetCellString(ws, r, colFranquiaGb);
                var totalLinhas = GetCellString(ws, r, colTotalLinhas);
                var valorTotal = GetCellString(ws, r, colValorTotal);
                var cliente = GetCellString(ws, r, colCliente);
                var qtdLinhas = GetCellString(ws, r, colQtdLinhas);

                var isPlanoTotal = !string.IsNullOrWhiteSpace(plano)
                    && NormalizeHeader(plano) == NormalizeHeader("TOTAL");

                if (isPlanoTotal)
                {
                    totalRowIndex = r;
                    break;
                }

                var hasAnyValue = !(string.IsNullOrWhiteSpace(plano)
                    && string.IsNullOrWhiteSpace(gb)
                    && string.IsNullOrWhiteSpace(valorInd)
                    && string.IsNullOrWhiteSpace(franquia)
                    && string.IsNullOrWhiteSpace(totalLinhas)
                    && string.IsNullOrWhiteSpace(valorTotal)
                    && string.IsNullOrWhiteSpace(cliente)
                    && string.IsNullOrWhiteSpace(qtdLinhas));

                if (!hasAnyValue)
                {
                    if (dataStarted)
                    {
                        emptyDataStreak++;
                        if (emptyDataStreak >= 2) break;
                    }
                    continue;
                }

                emptyDataStreak = 0;

                var isDataRow = !string.IsNullOrWhiteSpace(cliente) || TryNullableInt(qtdLinhas).HasValue;

                var planoNormalized = NormalizeHeader(plano);
                if (!string.IsNullOrWhiteSpace(plano)
                    && planoNormalized != NormalizeHeader("PLANO CONTRATO")
                    && planoNormalized != NormalizeHeader("TOTAL"))
                {
                    lastPlanoContrato = plano.Trim();
                }

                if (!isDataRow && dataStarted)
                {
                    break;
                }

                if (isDataRow) dataStarted = true;

                var resolvedPlano = isDataRow
                    ? (string.IsNullOrWhiteSpace(plano) ? lastPlanoContrato : plano.Trim())
                    : (string.IsNullOrWhiteSpace(plano) ? null : plano.Trim());

                if (isDataRow && string.IsNullOrWhiteSpace(resolvedPlano))
                {
                    missingPlanoCount++;
                }

                buffer.Add(new ResumoPlanoContratoResumo
                {
                    PlanoContrato = string.IsNullOrWhiteSpace(resolvedPlano) ? null : resolvedPlano,
                    Gb = TryDecimal(gb),
                    ValorIndividualComSvas = TryDecimal(valorInd),
                    FranquiaGb = TryDecimal(franquia),
                    TotalLinhas = TryNullableInt(totalLinhas),
                    ValorTotal = TryDecimal(valorTotal),
                    CreatedAt = now,
                    UpdatedAt = now
                });
            }

            if (buffer.Count > 0)
            {
                await _db.ResumoPlanoContratoResumos.AddRangeAsync(buffer);
                await _db.SaveChangesAsync();
            }

            if (missingPlanoCount > 0)
            {
                throw new InvalidOperationException($"Import RESUMO/PLANO CONTRATO: {missingPlanoCount} linhas de dados ficaram sem PLANO CONTRATO.");
            }

            if (totalRowIndex == null)
            {
                return;
            }

            var total = new ResumoPlanoContratoTotal
            {
                ValorTotal = TryDecimal(GetCellString(ws, totalRowIndex.Value, colValorTotal)),
                CreatedAt = now,
                UpdatedAt = now
            };

            await _db.ResumoPlanoContratoTotals.AddAsync(total);
            await _db.SaveChangesAsync();
        }

        private static int FindHeaderRowForPlanoContratoResumo(IXLWorksheet ws, int startRow, int lastRow)
        {
            for (int r = startRow; r <= lastRow; r++)
            {
                var row = ws.Row(r);
                if (!row.CellsUsed().Any()) continue;

                var map = BuildHeaderMap(row);
                var hasPlano = GetCol(map, "PLANO CONTRATO") > 0;
                var hasCliente = GetCol(map, "CLIENTE") > 0;
                var hasQtd = GetColAny(map, "QTD DE LINHAS", "QTD. DE LINHAS", "QTD LINHAS") > 0;

                if (hasPlano && hasCliente && hasQtd)
                {
                    return r;
                }
            }

            return 0;
        }

        private async Task ImportResumoTabela7(IXLWorksheet ws, DateTime now)
        {
            var lastRowUsed = ws.LastRowUsed()?.RowNumber() ?? 1;
            var headerRow = FindHeaderRowForGbDistribuicao(ws, 1, lastRowUsed);
            if (headerRow == 0)
            {
                Console.WriteLine("[WARN] RESUMO/GB_QTD_SOMA: cabeçalho GB/QTD/SOMA não encontrado.");
                return;
            }

            var map = BuildHeaderMap(ws.Row(headerRow));
            var colGb = GetCol(map, "GB");
            var colQtd = GetColAny(map, "QTD", "QUANTIDADE");
            var colSoma = GetCol(map, "SOMA");

            if (colGb == 0 || colQtd == 0 || colSoma == 0)
            {
                Console.WriteLine("[WARN] RESUMO/GB_QTD_SOMA: colunas obrigatórias incompletas no cabeçalho.");
                return;
            }

            var buffer = new List<ResumoGbDistribuicao>(120);
            var dataStarted = false;
            var emptyRowStreak = 0;
            int? totalRowIndex = null;

            for (int r = headerRow + 1; r <= lastRowUsed; r++)
            {
                var gbText = GetCellString(ws, r, colGb);
                var qtdText = GetCellString(ws, r, colQtd);
                var somaText = GetCellString(ws, r, colSoma);

                var hasAnyValue = !(string.IsNullOrWhiteSpace(gbText)
                    && string.IsNullOrWhiteSpace(qtdText)
                    && string.IsNullOrWhiteSpace(somaText));

                if (!hasAnyValue)
                {
                    if (dataStarted)
                    {
                        emptyRowStreak++;
                        if (emptyRowStreak >= 2) break;
                    }
                    continue;
                }

                emptyRowStreak = 0;

                var gbKey = NormalizeHeader(gbText);
                var isTotalRow = gbKey == NormalizeHeader("TOTAL")
                    || gbKey == NormalizeHeader("TOTAL GERAL");
                if (isTotalRow)
                {
                    totalRowIndex = r;
                    break;
                }

                var gbValue = TryDecimal(gbText);
                var qtdValue = TryNullableInt(qtdText);
                var somaValue = TryDecimal(somaText);
                var isDataRow = gbValue.HasValue || qtdValue.HasValue || somaValue.HasValue;

                if (!isDataRow)
                {
                    if (dataStarted) break;
                    continue;
                }

                dataStarted = true;

                buffer.Add(new ResumoGbDistribuicao
                {
                    Gb = gbValue,
                    Qtd = qtdValue,
                    Soma = somaValue,
                    CreatedAt = now,
                    UpdatedAt = now
                });
            }

            if (buffer.Count > 0)
            {
                await _db.ResumoGbDistribuicoes.AddRangeAsync(buffer);
                await _db.SaveChangesAsync();
            }

            int? totalLinhas = null;
            decimal? somaTotal = null;
            if (totalRowIndex.HasValue)
            {
                totalLinhas = TryNullableInt(GetCellString(ws, totalRowIndex.Value, colQtd));
                somaTotal = TryDecimal(GetCellString(ws, totalRowIndex.Value, colSoma));
            }

            totalLinhas ??= buffer.Sum(x => x.Qtd ?? 0);
            somaTotal ??= buffer.Sum(x => x.Soma ?? 0m);

            if (totalLinhas.HasValue || somaTotal.HasValue)
            {
                await _db.ResumoGbDistribuicaoTotais.AddAsync(new ResumoGbDistribuicaoTotal
                {
                    TotalLinhas = totalLinhas,
                    SomaTotal = somaTotal,
                    CreatedAt = now,
                    UpdatedAt = now
                });
                await _db.SaveChangesAsync();
            }
        }

        private static int FindHeaderRowForGbDistribuicao(IXLWorksheet ws, int startRow, int lastRow)
        {
            for (int r = startRow; r <= lastRow; r++)
            {
                var row = ws.Row(r);
                if (!row.CellsUsed().Any()) continue;

                var map = BuildHeaderMap(row);
                var hasGb = GetCol(map, "GB") > 0;
                var hasQtd = GetColAny(map, "QTD", "QUANTIDADE") > 0;
                var hasSoma = GetCol(map, "SOMA") > 0;

                if (hasGb && hasQtd && hasSoma)
                {
                    return r;
                }
            }

            return 0;
        }

        private async Task ImportResumoTabela5(IXLWorksheet ws, DateTime now)
        {
            var lastRowUsed = ws.LastRowUsed()?.RowNumber() ?? 1;
            var headerRow = FindHeaderRowForLineTotais(ws, 1, lastRowUsed);
            if (headerRow == 0)
            {
                Console.WriteLine("[WARN] RESUMO/TOTAIS_LINE: cabeçalho VALOR TOTAL LINE/LUCRO TOTAL LINE/QTD. LINHAS não encontrado.");
                return;
            }

            var map = BuildHeaderMap(ws.Row(headerRow));
            var colValorTotalLine = GetColAny(map, "VALOR TOTAL LINE", "VALOR TOTAL LINE R$");
            var colLucroTotalLine = GetColAny(map, "LUCRO TOTAL LINE", "LUCRO TOTAL LINE R$");
            var colQtdLinhas = GetColAny(map, "QTD. LINHAS", "QTD LINHAS", "QTD. DE LINHAS", "QTDLINHAS");
            var firstMetricCol = new[] { colValorTotalLine, colLucroTotalLine, colQtdLinhas }
                .Where(c => c > 0)
                .DefaultIfEmpty(2)
                .Min();
            var lastLabelCol = Math.Max(1, firstMetricCol - 1);

            var buffer = new List<ResumoLineTotais>(8);
            var dataStarted = false;
            var emptyRowStreak = 0;

            for (int r = headerRow + 1; r <= lastRowUsed; r++)
            {
                var tipo = GetFirstNonEmptyCellInRange(ws, r, 1, lastLabelCol);
                var valorRaw = GetCellString(ws, r, colValorTotalLine);
                var lucroRaw = GetCellString(ws, r, colLucroTotalLine);
                var qtdRaw = GetCellString(ws, r, colQtdLinhas);

                var hasAnyValue = !(string.IsNullOrWhiteSpace(tipo)
                    && string.IsNullOrWhiteSpace(valorRaw)
                    && string.IsNullOrWhiteSpace(lucroRaw)
                    && string.IsNullOrWhiteSpace(qtdRaw));

                if (!hasAnyValue)
                {
                    if (dataStarted)
                    {
                        emptyRowStreak++;
                        if (emptyRowStreak >= 2) break;
                    }
                    continue;
                }

                emptyRowStreak = 0;

                var valor = TryDecimal(valorRaw);
                var lucro = TryDecimal(lucroRaw);
                var qtd = TryNullableInt(qtdRaw);
                var isDataRow = valor.HasValue || lucro.HasValue || qtd.HasValue;

                if (!isDataRow)
                {
                    if (dataStarted) break;
                    continue;
                }

                dataStarted = true;

                buffer.Add(new ResumoLineTotais
                {
                    Tipo = string.IsNullOrWhiteSpace(tipo) ? null : tipo.Trim(),
                    ValorTotalLine = valor,
                    LucroTotalLine = lucro,
                    QtdLinhas = qtd,
                    CreatedAt = now,
                    UpdatedAt = now
                });
            }

            if (buffer.Count == 0)
            {
                Console.WriteLine("[WARN] RESUMO/TOTAIS_LINE: cabeçalho encontrado, mas nenhuma linha de PF/PJ/DIFERENÇA foi importada.");
                return;
            }

            await _db.ResumoLineTotais.AddRangeAsync(buffer);
            await _db.SaveChangesAsync();
        }

        private static int FindHeaderRowForLineTotais(IXLWorksheet ws, int startRow, int lastRow)
        {
            for (int r = startRow; r <= lastRow; r++)
            {
                var row = ws.Row(r);
                if (!row.CellsUsed().Any()) continue;

                var map = BuildHeaderMap(row);
                var hasValor = GetColAny(map, "VALOR TOTAL LINE", "VALOR TOTAL LINE R$") > 0;
                var hasLucro = GetColAny(map, "LUCRO TOTAL LINE", "LUCRO TOTAL LINE R$") > 0;
                var hasQtd = GetColAny(map, "QTD. LINHAS", "QTD LINHAS", "QTD. DE LINHAS", "QTDLINHAS") > 0;

                if (hasValor && hasLucro && hasQtd)
                {
                    return r;
                }
            }

            return 0;
        }

        private async Task ImportResumoTabela6(IXLWorksheet ws, DateTime now)
        {
            var lastRowUsed = ws.LastRowUsed()?.RowNumber() ?? 1;
            var sectionRow = FindSectionRow(ws, "LINHAS NA RESERVA");
            if (sectionRow == 0) return;

            var headerRow = FindHeaderRowForReserva(ws, sectionRow + 1, lastRowUsed);
            if (headerRow == 0) return;

            var map = BuildHeaderMap(ws.Row(headerRow));
            var colDdd = GetCol(map, "DDD");
            var colFranquiaGb = GetColAny(map, "FRANQUIA GB", "FRAQUIA GB");
            var colQtdLinhas = GetColAny(map, "QTD. DE LINHAS", "QTD DE LINHAS", "QTD. LINHAS", "QTDLINHAS");
            var colTotal = GetCol(map, "TOTAL");

            var buffer = new List<ResumoReservaLine>(200);
            string? lastDddValid = null;
            decimal? lastTotalForDdd = null;
            var dataStarted = false;
            var emptyRowStreak = 0;
            int? totalRowIndex = null;
            var totalsFromSheetByDdd = new Dictionary<string, decimal?>();
            var sumQtdByDdd = new Dictionary<string, int>();

            for (int r = headerRow + 1; r <= lastRowUsed; r++)
            {
                var ddd = GetCellString(ws, r, colDdd);
                var franquia = GetCellString(ws, r, colFranquiaGb);
                var qtdLinhas = GetCellString(ws, r, colQtdLinhas);
                var total = GetCellString(ws, r, colTotal);

                var hasAnyValue = !(string.IsNullOrWhiteSpace(ddd)
                    && string.IsNullOrWhiteSpace(franquia)
                    && string.IsNullOrWhiteSpace(qtdLinhas)
                    && string.IsNullOrWhiteSpace(total));

                if (!hasAnyValue)
                {
                    if (dataStarted)
                    {
                        emptyRowStreak++;
                        if (emptyRowStreak >= 2) break;
                    }
                    continue;
                }

                emptyRowStreak = 0;

                var dddCandidate = NullIfEmptyDigits(ddd);
                var hasFranquiaText = !string.IsNullOrWhiteSpace(franquia);
                var hasQtdText = !string.IsNullOrWhiteSpace(qtdLinhas);
                var hasTotalText = !string.IsNullOrWhiteSpace(total);

                // ✅ Rodapé "TOTAL GERAL" (DDD vazio + franquia vazia + qtd + total preenchidos)
                var isTotalGeralRow = string.IsNullOrWhiteSpace(dddCandidate)
                    && !hasFranquiaText
                    && hasQtdText
                    && hasTotalText;

                if (isTotalGeralRow)
                {
                    totalRowIndex = r;
                    break;
                }

                var franquiaValue = TryDecimal(franquia);
                var qtdValue = TryNullableInt(qtdLinhas);
                var isDataRow = franquiaValue.HasValue || qtdValue.HasValue;

                if (!string.IsNullOrWhiteSpace(dddCandidate))
                {
                    if (!string.Equals(lastDddValid, dddCandidate, StringComparison.OrdinalIgnoreCase))
                    {
                        lastTotalForDdd = null;
                    }
                    lastDddValid = dddCandidate;
                }

                if (!isDataRow && dataStarted)
                {
                    break;
                }

                if (isDataRow) dataStarted = true;

                var resolvedDdd = isDataRow
                    ? (dddCandidate ?? lastDddValid)
                    : dddCandidate;

                var totalValue = TryDecimal(total);
                if (!string.IsNullOrWhiteSpace(resolvedDdd) && totalValue.HasValue)
                {
                    lastTotalForDdd = totalValue;
                    totalsFromSheetByDdd[resolvedDdd] = totalValue;
                }

                if (!string.IsNullOrWhiteSpace(resolvedDdd) && qtdValue.HasValue)
                {
                    if (sumQtdByDdd.TryGetValue(resolvedDdd, out var acc))
                        sumQtdByDdd[resolvedDdd] = acc + qtdValue.Value;
                    else
                        sumQtdByDdd[resolvedDdd] = qtdValue.Value;
                }

                buffer.Add(new ResumoReservaLine
                {
                    Ddd = string.IsNullOrWhiteSpace(resolvedDdd) ? null : resolvedDdd,
                    FranquiaGb = franquiaValue,
                    QtdLinhas = qtdValue,
                    Total = totalValue ?? lastTotalForDdd,
                    CreatedAt = now,
                    UpdatedAt = now
                });
            }

            var missingDddCount = buffer.Count(x => x.Ddd == null && (x.FranquiaGb.HasValue || x.QtdLinhas.HasValue));
            if (missingDddCount > 0)
            {
                throw new InvalidOperationException($"Import RESUMO/RESERVA: {missingDddCount} linhas de dados ficaram sem DDD.");
            }

            if (buffer.Count > 0)
            {
                await _db.ResumoReservaLines.AddRangeAsync(buffer);
                await _db.SaveChangesAsync();
            }

            if (totalsFromSheetByDdd.Count > 0)
            {
                foreach (var kv in totalsFromSheetByDdd)
                {
                    if (!kv.Value.HasValue) continue;
                    if (!sumQtdByDdd.TryGetValue(kv.Key, out var sum)) continue;
                    var totalInt = (int)Math.Round(kv.Value.Value);
                    if (totalInt != sum)
                    {
                        Console.WriteLine($"[WARN] RESUMO/RESERVA DDD {kv.Key}: TOTAL(planilha)={totalInt} vs SOMA(QTD)={sum}");
                    }
                }
            }

            var totalGeralLinhasReserva = totalRowIndex.HasValue
                ? TryNullableInt(GetCellString(ws, totalRowIndex.Value, colQtdLinhas))
                : null;

            if (totalGeralLinhasReserva.HasValue)
            {
                var somaPorDdd = sumQtdByDdd.Values.Sum();
                if (somaPorDdd != totalGeralLinhasReserva.Value)
                {
                    Console.WriteLine($"[WARN] RESUMO/RESERVA TOTAL GERAL: planilha={totalGeralLinhasReserva.Value} vs SOMA(DDD)={somaPorDdd}");
                }
            }

            if (totalRowIndex == null)
            {
                return;
            }

            var totalEntity = new ResumoReservaTotal
            {
                TotalGeralLinhasReserva = totalGeralLinhasReserva,
                QtdLinhasTotal = TryNullableInt(GetCellString(ws, totalRowIndex.Value, colQtdLinhas)),
                Total = TryDecimal(GetCellString(ws, totalRowIndex.Value, colTotal)),
                CreatedAt = now,
                UpdatedAt = now
            };

            await _db.ResumoReservaTotals.AddAsync(totalEntity);
            await _db.SaveChangesAsync();
        }

        private static int FindSectionRow(IXLWorksheet ws, string sectionName)
        {
            var normalizedTarget = NormalizeHeader(sectionName);
            foreach (var row in ws.RowsUsed())
            {
                foreach (var cell in row.CellsUsed())
                {
                    var key = NormalizeHeader(cell.GetString());
                    if (string.IsNullOrWhiteSpace(key)) continue;
                    if (key.Contains(normalizedTarget)) return row.RowNumber();
                }
            }

            return 0;
        }

        private static int FindHeaderRowForReserva(IXLWorksheet ws, int startRow, int lastRow)
        {
            for (int r = startRow; r <= lastRow; r++)
            {
                var row = ws.Row(r);
                if (!row.CellsUsed().Any()) continue;

                var map = BuildHeaderMap(row);
                var hasDdd = GetCol(map, "DDD") > 0;
                var hasFranquia = GetColAny(map, "FRANQUIA GB", "FRAQUIA GB") > 0;
                var hasQtd = GetColAny(map, "QTD. DE LINHAS", "QTD DE LINHAS", "QTD. LINHAS", "QTDLINHAS") > 0;

                if (hasDdd && hasFranquia && hasQtd)
                {
                    return r;
                }
            }

            return 0;
        }

        private async Task ImportControleRecebidosSheet(IXLWorksheet ws, int year)
        {
            var buffer = new List<ControleRecebidoLine>(500);

            var firstRow = ws.FirstRowUsed()?.RowNumber() ?? 1;
            var lastRow = ws.LastRowUsed()?.RowNumber() ?? firstRow;
            var rowIndex = firstRow;
            decimal? lastValorDaNf = null;
            decimal? lastValorUnit = null;

            while (rowIndex <= lastRow)
            {
                var row = ws.Row(rowIndex);
                if (!IsControleRecebidosHeader(row))
                {
                    rowIndex++;
                    continue;
                }

                var map = BuildHeaderMap(row);
                int colItem = GetCol(map, "ITEM");
                if (colItem == 0)
                {
                    rowIndex++;
                    continue;
                }

                var isResumo = GetColAny(map, "QTD.", "QTD", "QUANTIDADE") > 0
                               && GetColAny(map, "CHIP") == 0
                               && GetColAny(map, "SERIAL") == 0;

                lastValorDaNf = null;
                lastValorUnit = null;
                rowIndex++;

                for (; rowIndex <= lastRow; rowIndex++)
                {
                    var currentRow = ws.Row(rowIndex);
                    if (IsControleRecebidosHeader(currentRow))
                    {
                        rowIndex--;
                        break;
                    }

                    var itemStr = GetCellString(ws, rowIndex, colItem);
                    if (string.IsNullOrWhiteSpace(itemStr)) break;

                    var notaFiscal = GetCellByHeaderAny(ws, rowIndex, map, "NOTA FISCAL", "NOTA", "NF");
                    var chip = NullIfEmptyDigits(GetCellByHeaderAny(ws, rowIndex, map, "CHIP"));
                    var serial = GetCellByHeaderAny(ws, rowIndex, map, "SERIAL");
                    var conteudo = GetCellByHeaderAny(ws, rowIndex, map, "CONTEÚDO DA NF", "CONTEUDO DA NF");
                    var numeroLinha = NullIfEmptyDigits(GetCellByHeaderAny(ws, rowIndex, map, "NÚMERO DA LINHA", "NUMERO DA LINHA"));
                    var valorUnit = TryDecimal(GetCellByHeaderAny(ws, rowIndex, map, "VALOR UNIT.", "VALOR UNIT", "VALOR UNITÁRIO", "VALOR UNITARIO"));
                    if (valorUnit.HasValue)
                    {
                        lastValorUnit = valorUnit;
                    }
                    else
                    {
                        valorUnit = lastValorUnit;
                    }
                    var valorDaNf = TryDecimal(GetCellByHeaderAny(ws, rowIndex, map, "VALOR DA NF", "VALOR DA N F"));
                    if (valorDaNf.HasValue)
                    {
                        lastValorDaNf = valorDaNf;
                    }
                    else
                    {
                        valorDaNf = lastValorDaNf;
                    }
                    var dataDaNf = TryDateNoUtc(ws, rowIndex, map, "DATA DA NF");
                    var dataReceb = TryDateNoUtc(ws, rowIndex, map, "DATA DO RECEBIMENTO");
                    var qtd = TryNullableInt(GetCellByHeaderAny(ws, rowIndex, map, "QTD.", "QTD", "QUANTIDADE"));

                    var now = DateTime.UtcNow;

                    var e = new ControleRecebidoLine
                    {
                        Id = Guid.NewGuid(),
                        Ano = year,
                        Item = TryInt(itemStr),
                        NotaFiscal = string.IsNullOrWhiteSpace(notaFiscal) ? null : notaFiscal.Trim(),
                        Chip = chip,
                        Serial = string.IsNullOrWhiteSpace(serial) ? null : serial.Trim(),
                        ConteudoDaNf = string.IsNullOrWhiteSpace(conteudo) ? null : conteudo.Trim(),
                        NumeroDaLinha = numeroLinha,
                        ValorUnit = valorUnit,
                        ValorDaNf = valorDaNf,
                        DataDaNf = dataDaNf,
                        DataDoRecebimento = dataReceb,
                        Quantidade = qtd,
                        IsResumo = isResumo,
                        CreatedAt = now,
                        UpdatedAt = now
                    };

                    buffer.Add(e);

                    if (buffer.Count >= 500)
                    {
                        await _db.ControleRecebidoLines.AddRangeAsync(buffer);
                        await _db.SaveChangesAsync();
                        buffer.Clear();
                    }
                }

                rowIndex++;
            }

            if (buffer.Count > 0)
            {
                await _db.ControleRecebidoLines.AddRangeAsync(buffer);
                await _db.SaveChangesAsync();
            }
        }

        private sealed class ControleRecebidosWorksheetInfo
        {
            public ControleRecebidosWorksheetInfo(IXLWorksheet sheet, int year)
            {
                Sheet = sheet;
                Year = year;
            }

            public IXLWorksheet Sheet { get; }
            public int Year { get; }
        }

        private static IEnumerable<ControleRecebidosWorksheetInfo> GetControleRecebidosWorksheets(XLWorkbook wb)
        {
            var years = new[] { 2022, 2023, 2024, 2025 };

            foreach (var ws in wb.Worksheets)
            {
                var name = NormalizeHeader(ws.Name);
                var isControleRecebidos = name.Contains("CONTROLE") && name.Contains("RECEBIDOS");
                var isRomaneio = name.Contains("ROMANEIO");

                if (!isControleRecebidos && !isRomaneio) continue;

                var year = years.FirstOrDefault(y => name.Contains(y.ToString()));
                if (year == 0) continue;

                yield return new ControleRecebidosWorksheetInfo(ws, year);
            }
        }

        private static IXLWorksheet? FindControleRecebidosWorksheet(XLWorkbook wb, int year)
        {
            var normalizedName = NormalizeHeader($"CONTROLE DE RECEBIDOS {year}");

            var ws = wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == normalizedName);
            if (ws != null) return ws;

            ws = wb.Worksheets.FirstOrDefault(w =>
            {
                var name = NormalizeHeader(w.Name);
                return name.Contains("CONTROLE") && name.Contains("RECEBIDOS") && name.Contains(year.ToString());
            });
            if (ws != null) return ws;

            if (year == 2024)
            {
                ws = wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("CONTROLE DE RECEBIDOS"));
            }

            return ws;
        }

        private static bool IsControleRecebidosHeader(IXLRow row)
        {
            var hasItem = false;
            var hasNota = false;

            foreach (var cell in row.CellsUsed())
            {
                var k = NormalizeHeader(cell.GetString());
                if (k == "ITEM") hasItem = true;
                if (k == "NOTAFISCAL") hasNota = true;
                if (hasItem && hasNota) return true;
            }

            return false;
        }

        private static bool IsChipsVirgensHeader(IXLRow row)
        {
            var hasItem = false;
            var hasNumeroChip = false;
            var hasObs = false;

            foreach (var cell in row.CellsUsed())
            {
                var k = NormalizeHeader(cell.GetString());
                if (k == "ITEM") hasItem = true;
                if (k.Contains("CHIP")) hasNumeroChip = true;
                if (k.Contains("OBS")) hasObs = true;
            }

            return hasItem && hasNumeroChip && hasObs;
        }

        // ==========================================================
        // HELPERS (SEUS)
        // ==========================================================
        private static Dictionary<string, int> BuildHeaderMap(IXLRow headerRow)
        {
            var map = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
            foreach (var cell in headerRow.CellsUsed())
            {
                var k = NormalizeHeader(cell.GetString());
                if (!string.IsNullOrWhiteSpace(k) && !map.ContainsKey(k))
                    map[k] = cell.Address.ColumnNumber;
            }
            return map;
        }

        private static Dictionary<string, int> BuildHeaderMapRange(IXLRow headerRow, int startCol, int endCol)
        {
            var map = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
            foreach (var cell in headerRow.CellsUsed())
            {
                var col = cell.Address.ColumnNumber;
                if (col < startCol || col > endCol) continue;

                var k = NormalizeHeader(cell.GetString());
                if (!string.IsNullOrWhiteSpace(k) && !map.ContainsKey(k))
                    map[k] = col;
            }
            return map;
        }

        private static int FindHeaderColumn(IXLRow headerRow, int startCol, int endCol, params string[] headerKeys)
        {
            var normalizedKeys = headerKeys.Select(NormalizeHeader).Where(k => !string.IsNullOrWhiteSpace(k)).ToArray();
            if (normalizedKeys.Length == 0) return 0;

            for (int col = startCol; col <= endCol; col++)
            {
                var key = NormalizeHeader(headerRow.Cell(col).GetString());
                if (string.IsNullOrWhiteSpace(key)) continue;

                foreach (var wanted in normalizedKeys)
                {
                    if (key == wanted || key.Contains(wanted))
                    {
                        return col;
                    }
                }
            }

            return 0;
        }

        private static DateTime? ToUtc(DateTime? dt)
        {
            if (dt == null) return null;
            var v = dt.Value;
            return v.Kind == DateTimeKind.Utc ? v :
                   (v.Kind == DateTimeKind.Local ? v.ToUniversalTime() : DateTime.SpecifyKind(v, DateTimeKind.Utc));
        }

        private async Task<VigenciaLine?> FindVigenciaByMobileLineAsync(MobileLine mobileLine, string? previousLinha, bool asNoTracking)
        {
            var currentLinha = NullIfEmptyDigits(mobileLine.Linha);
            var oldLinha = NullIfEmptyDigits(previousLinha);
            if (!string.IsNullOrWhiteSpace(currentLinha) &&
                string.Equals(currentLinha, oldLinha, StringComparison.Ordinal))
            {
                oldLinha = null;
            }

            IQueryable<VigenciaLine> q = _db.VigenciaLines;
            if (asNoTracking)
            {
                q = q.AsNoTracking();
            }

            if (!string.IsNullOrWhiteSpace(currentLinha) && !string.IsNullOrWhiteSpace(oldLinha))
            {
                var byEitherLinha = await q.FirstOrDefaultAsync(v => v.Linha == currentLinha || v.Linha == oldLinha);
                if (byEitherLinha != null) return byEitherLinha;
            }

            if (!string.IsNullOrWhiteSpace(currentLinha))
            {
                var byLinha = await q.FirstOrDefaultAsync(v => v.Linha == currentLinha);
                if (byLinha != null) return byLinha;
            }

            if (!string.IsNullOrWhiteSpace(oldLinha))
            {
                var byPreviousLinha = await q.FirstOrDefaultAsync(v => v.Linha == oldLinha);
                if (byPreviousLinha != null) return byPreviousLinha;
            }

            if (mobileLine.Item > 0 && string.IsNullOrWhiteSpace(currentLinha) && string.IsNullOrWhiteSpace(oldLinha))
            {
                return await q.FirstOrDefaultAsync(v => v.Item == mobileLine.Item);
            }

            return null;
        }

        private async Task<VigenciaLine> UpsertVigenciaFromMobileLineAsync(
            MobileLine mobileLine,
            DateTime? dtEfetivacaoServico,
            DateTime? dtTerminoFidelizacao,
            bool overrideDates,
            string? previousLinha = null)
        {
            var now = DateTime.UtcNow;
            var vigencia = await FindVigenciaByMobileLineAsync(mobileLine, previousLinha, asNoTracking: false);

            if (vigencia == null)
            {
                vigencia = new VigenciaLine
                {
                    Id = Guid.NewGuid(),
                    CreatedAt = now
                };
                _db.VigenciaLines.Add(vigencia);
            }

            vigencia.Item = mobileLine.Item;
            vigencia.Conta = string.IsNullOrWhiteSpace(mobileLine.Conta) ? null : mobileLine.Conta.Trim();
            vigencia.Cliente = string.IsNullOrWhiteSpace(mobileLine.Cliente) ? null : mobileLine.Cliente.Trim();
            vigencia.Usuario = string.IsNullOrWhiteSpace(mobileLine.Usuario) ? null : mobileLine.Usuario.Trim();
            vigencia.PlanoContrato = string.IsNullOrWhiteSpace(mobileLine.PlanoContrato) ? null : mobileLine.PlanoContrato.Trim();

            var linhaDigits = NullIfEmptyDigits(mobileLine.Linha) ?? NullIfEmptyDigits(previousLinha);
            if (!string.IsNullOrWhiteSpace(linhaDigits))
            {
                vigencia.Linha = linhaDigits;
            }

            var totalFromLine = mobileLine.ValorPlanoVivo ?? mobileLine.ValorContratoVivo;
            if (totalFromLine.HasValue)
            {
                vigencia.Total = totalFromLine.Value;
            }

            if (overrideDates || dtEfetivacaoServico.HasValue)
            {
                vigencia.DtEfetivacaoServico = ToUtc(dtEfetivacaoServico);
            }
            if (overrideDates || dtTerminoFidelizacao.HasValue)
            {
                vigencia.DtTerminoFidelizacao = ToUtc(dtTerminoFidelizacao);
            }

            vigencia.UpdatedAt = now;
            return vigencia;
        }

        private static MobileLineDetailDto ToDetailDto(MobileLine x, VigenciaLine? vigencia = null) => new()
        {
            Id = x.Id,
            Item = x.Item,
            Conta = x.Conta,
            Linha = x.Linha,
            Chip = x.Chip,
            Cliente = x.Cliente,
            Usuario = x.Usuario,
            PlanoContrato = x.PlanoContrato,
            FranquiaVivo = x.FranquiaVivo,
            ValorPlanoVivo = x.ValorPlanoVivo,
            GestaoVozDados = x.GestaoVozDados,
            Skeelo = x.Skeelo,
            VivoNewsPlus = x.VivoNewsPlus,
            VivoTravelMundo = x.VivoTravelMundo,
            VivoSync = x.VivoSync,
            VivoGestaoDispositivo = x.VivoGestaoDispositivo,
            ValorContratoVivo = x.ValorContratoVivo,
            FranquiaLine = x.FranquiaLine,
            FranquiaGestao = x.FranquiaGestao,
            LocacaoAp = x.LocacaoAp,
            ValorContratoLine = x.ValorContratoLine,
            Desconto = x.Desconto,
            Lucro = x.Lucro,
            Status = x.Status,
            DataBloqueio = x.DataBloqueio,
            Skil = x.Skil,
            Modalidade = x.Modalidade,
            Cedente = x.Cedente,
            Solicitante = x.Solicitante,
            DataEntregaOpera = x.DataEntregaOpera,
            DataEntregaCliente = x.DataEntregaCliente,
            DtEfetivacaoServico = vigencia?.DtEfetivacaoServico,
            DtTerminoFidelizacao = vigencia?.DtTerminoFidelizacao,
            VencConta = x.VencConta,
            TipoDeChip = x.TipoDeChip
        };

        private static void ApplyReservaRule(MobileLine x)
        {
            if (IsReservaValue(x.Cliente)) x.Cliente = "RESERVA";
            if (IsReservaValue(x.Usuario)) x.Usuario = "RESERVA";
            if (IsReservaValue(x.Skil)) x.Skil = "RESERVA";
        }

        private static bool IsReservaValue(string? value)
            => string.Equals(value?.Trim(), "RESERVA", StringComparison.OrdinalIgnoreCase);

        private static int GetCol(Dictionary<string, int> map, string name)
            => map.TryGetValue(NormalizeHeader(name), out var c) ? c : 0;

        private Guid? GetTenantIdFromClaims()
        {
            var claim = User?.FindFirst("tenantId")?.Value
                ?? User?.FindFirst("tenant")?.Value;
            return Guid.TryParse(claim, out var tenantId) ? tenantId : null;
        }

        private static int GetColAny(Dictionary<string, int> map, params string[] headers)
        {
            foreach (var h in headers)
            {
                var k = NormalizeHeader(h);
                if (map.TryGetValue(k, out var c)) return c;
            }
            return 0;
        }

        private static string GetCellByHeader(IXLWorksheet ws, int row, Dictionary<string, int> map, string header)
        {
            var k = NormalizeHeader(header);
            return map.TryGetValue(k, out var c) ? GetCellString(ws, row, c) : "";
        }

        private static string GetCellByHeaderAny(IXLWorksheet ws, int row, Dictionary<string, int> map, params string[] headers)
        {
            foreach (var h in headers)
            {
                var k = NormalizeHeader(h);
                if (map.TryGetValue(k, out var c))
                    return GetCellString(ws, row, c);
            }
            return "";
        }

        private static string GetCellString(IXLWorksheet ws, int row, int col)
        {
            if (col <= 0) return "";
            return (ws.Cell(row, col).GetValue<string>() ?? "").Trim();
        }

        private static string GetFirstNonEmptyCellInRange(IXLWorksheet ws, int row, int startCol, int endCol)
        {
            if (endCol < startCol) return "";
            for (int col = startCol; col <= endCol; col++)
            {
                var value = GetCellString(ws, row, col);
                if (!string.IsNullOrWhiteSpace(value)) return value;
            }

            return "";
        }

        private sealed class UserDataClientByLine
        {
            public string Linha { get; set; } = "";
            public string Cliente { get; set; } = "";
        }

        private sealed class UserDataClientByItem
        {
            public int Item { get; set; }
            public string Cliente { get; set; } = "";
        }

        private IQueryable<UserDataClientByLine> BuildUserDataClientByLineQuery()
        {
            return _db.UserDatas
                .AsNoTracking()
                .Where(x => x.Linha != null && x.Linha != "")
                .Where(x => x.Cliente != null && x.Cliente != "")
                .Where(x => !EF.Functions.ILike((x.Cliente ?? "").Trim(), "RESERVA"))
                .GroupBy(x => x.Linha!)
                .Select(g => new UserDataClientByLine
                {
                    Linha = g.Key,
                    Cliente = g.Max(x => x.Cliente!)!
                });
        }

        private IQueryable<UserDataClientByItem> BuildUserDataClientByItemQuery()
        {
            return _db.UserDatas
                .AsNoTracking()
                .Where(x => x.Item > 0)
                .Where(x => x.Cliente != null && x.Cliente != "")
                .Where(x => !EF.Functions.ILike((x.Cliente ?? "").Trim(), "RESERVA"))
                .GroupBy(x => x.Item)
                .Select(g => new UserDataClientByItem
                {
                    Item = g.Key,
                    Cliente = g.Max(x => x.Cliente!)!
                });
        }

        private static IQueryable<MobileLine> ApplyAdditionalFilters(
            IQueryable<MobileLine> query,
            string? additionalMode,
            string? additionalServices)
        {
            var mode = (additionalMode ?? "").Trim().ToLowerInvariant();
            var withOnly = mode is "with" or "com";
            var withoutOnly = mode is "without" or "sem";

            var selected = ParseAdditionalServices(additionalServices);
            var hasServiceFilter = selected.Count > 0;

            var includeGvd = selected.Contains("gvd");
            var includeSkeelo = selected.Contains("skeelo");
            var includeNews = selected.Contains("news");
            var includeTravel = selected.Contains("travel");
            var includeSync = selected.Contains("sync");
            var includeDispositivo = selected.Contains("dispositivo");

            if (hasServiceFilter)
            {
                if (withoutOnly)
                {
                    return query.Where(x =>
                        (!includeGvd || (x.GestaoVozDados ?? 0m) <= 0m) &&
                        (!includeSkeelo || (x.Skeelo ?? 0m) <= 0m) &&
                        (!includeNews || (x.VivoNewsPlus ?? 0m) <= 0m) &&
                        (!includeTravel || (x.VivoTravelMundo ?? 0m) <= 0m) &&
                        (!includeSync || (x.VivoSync ?? 0m) <= 0m) &&
                        (!includeDispositivo || (x.VivoGestaoDispositivo ?? 0m) <= 0m));
                }

                // "with" e também "all" com serviços selecionados:
                // filtra linhas que tenham qualquer um dos adicionais selecionados com valor > 0.
                return query.Where(x =>
                    (includeGvd && (x.GestaoVozDados ?? 0m) > 0m) ||
                    (includeSkeelo && (x.Skeelo ?? 0m) > 0m) ||
                    (includeNews && (x.VivoNewsPlus ?? 0m) > 0m) ||
                    (includeTravel && (x.VivoTravelMundo ?? 0m) > 0m) ||
                    (includeSync && (x.VivoSync ?? 0m) > 0m) ||
                    (includeDispositivo && (x.VivoGestaoDispositivo ?? 0m) > 0m));
            }

            if (withOnly)
            {
                return query.Where(x =>
                    (x.GestaoVozDados ?? 0m) > 0m ||
                    (x.Skeelo ?? 0m) > 0m ||
                    (x.VivoNewsPlus ?? 0m) > 0m ||
                    (x.VivoTravelMundo ?? 0m) > 0m ||
                    (x.VivoSync ?? 0m) > 0m ||
                    (x.VivoGestaoDispositivo ?? 0m) > 0m);
            }

            if (withoutOnly)
            {
                return query.Where(x =>
                    (x.GestaoVozDados ?? 0m) <= 0m &&
                    (x.Skeelo ?? 0m) <= 0m &&
                    (x.VivoNewsPlus ?? 0m) <= 0m &&
                    (x.VivoTravelMundo ?? 0m) <= 0m &&
                    (x.VivoSync ?? 0m) <= 0m &&
                    (x.VivoGestaoDispositivo ?? 0m) <= 0m);
            }

            return query;
        }

        private static HashSet<string> ParseAdditionalServices(string? raw)
        {
            var set = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
            if (string.IsNullOrWhiteSpace(raw)) return set;

            var chunks = raw.Split(new[] { ',', ';', '|', ' ' }, StringSplitOptions.RemoveEmptyEntries);
            foreach (var chunk in chunks)
            {
                var normalized = NormalizeAdditionalServiceKey(chunk);
                if (!string.IsNullOrWhiteSpace(normalized))
                    set.Add(normalized);
            }

            return set;
        }

        private static string NormalizeAdditionalServiceKey(string? raw)
        {
            var key = (raw ?? "").Trim().ToLowerInvariant()
                .Replace("-", "")
                .Replace("_", "")
                .Replace(" ", "");

            return key switch
            {
                "gvd" => "gvd",
                "gestaovozedados" => "gvd",
                "gestaovozdados" => "gvd",

                "skeelo" => "skeelo",

                "news" => "news",
                "vivonewsplus" => "news",

                "travel" => "travel",
                "vivotravelmundo" => "travel",

                "sync" => "sync",
                "vivosync" => "sync",

                "dispositivo" => "dispositivo",
                "gestaodispositivo" => "dispositivo",
                "vivogestaodispositivo" => "dispositivo",

                _ => string.Empty
            };
        }

        private static DateTime? TryDate(IXLWorksheet ws, int row, Dictionary<string, int> map, string header)
        {
            var k = NormalizeHeader(header);
            if (!map.TryGetValue(k, out var c)) return null;
            return TryDateCell(ws, row, c);
        }

        private static DateTime? TryDateNoUtc(IXLWorksheet ws, int row, Dictionary<string, int> map, string header)
        {
            var k = NormalizeHeader(header);
            if (!map.TryGetValue(k, out var c)) return null;
            return TryDateCellNoUtc(ws, row, c);
        }

        private static DateTime? TryDateCell(IXLWorksheet ws, int row, int col)
        {
            if (col <= 0) return null;

            var cell = ws.Cell(row, col);

            if (cell.DataType == XLDataType.DateTime)
                return ToUtc(cell.GetDateTime());

            if (cell.TryGetValue<DateTime>(out var dt))
                return ToUtc(dt);

            var s = cell.GetValue<string>()?.Trim();
            if (string.IsNullOrWhiteSpace(s)) return null;

            if (DateTime.TryParse(s, new CultureInfo("pt-BR"), DateTimeStyles.None, out var d))
                return ToUtc(d);

            return null;
        }

        private static DateTime? TryDateCellNoUtc(IXLWorksheet ws, int row, int col)
        {
            if (col <= 0) return null;

            var cell = ws.Cell(row, col);

            if (cell.DataType == XLDataType.DateTime)
                return ToUtcDateOnly(cell.GetDateTime());

            if (cell.TryGetValue<DateTime>(out var dt))
                return ToUtcDateOnly(dt);

            var s = cell.GetValue<string>()?.Trim();
            if (string.IsNullOrWhiteSpace(s)) return null;

            if (DateTime.TryParse(s, new CultureInfo("pt-BR"), DateTimeStyles.None, out var d))
                return ToUtcDateOnly(d);

            return null;
        }

        private static DateTime ToUtcDateOnly(DateTime dt)
        {
            return new DateTime(dt.Year, dt.Month, dt.Day, 12, 0, 0, DateTimeKind.Utc);
        }

        private static decimal? TryDecimal(string? input)
        {
            if (string.IsNullOrWhiteSpace(input)) return null;

            var normalized = NormalizeDecimalInput(input);
            if (string.IsNullOrWhiteSpace(normalized)) return null;

            return decimal.TryParse(normalized, NumberStyles.Float, CultureInfo.InvariantCulture, out var value)
                ? value
                : null;
        }

        private static string NormalizeDecimalInput(string raw)
        {
            var s = raw
                .Replace("R$", "", StringComparison.OrdinalIgnoreCase)
                .Replace("%", "", StringComparison.OrdinalIgnoreCase)
                .Replace(" ", "")
                .Replace("\u00A0", "")
                .Trim();

            if (string.IsNullOrWhiteSpace(s)) return string.Empty;

            var negativeByParentheses = s.StartsWith("(") && s.EndsWith(")");
            if (negativeByParentheses && s.Length >= 2)
            {
                s = s[1..^1];
            }

            var allowed = new StringBuilder(s.Length);
            foreach (var ch in s)
            {
                if (char.IsDigit(ch) || ch == '.' || ch == ',' || ch == '-' || ch == '+' || ch == 'e' || ch == 'E')
                {
                    allowed.Append(ch);
                }
            }

            s = allowed.ToString();
            if (string.IsNullOrWhiteSpace(s)) return string.Empty;

            string normalized;
            var commaCount = s.Count(c => c == ',');
            var dotCount = s.Count(c => c == '.');

            if (s.IndexOf('e') >= 0 || s.IndexOf('E') >= 0)
            {
                normalized = s.Replace(",", ".");
            }
            else if (commaCount > 0 && dotCount > 0)
            {
                var lastComma = s.LastIndexOf(',');
                var lastDot = s.LastIndexOf('.');

                if (lastComma > lastDot)
                {
                    // Ex.: 1.234,56 -> 1234.56
                    normalized = s.Replace(".", "").Replace(",", ".");
                }
                else
                {
                    // Ex.: 1,234.56 -> 1234.56
                    normalized = s.Replace(",", "");
                }
            }
            else if (commaCount > 0)
            {
                normalized = NormalizeSingleSeparatorNumber(s, ',');
            }
            else if (dotCount > 0)
            {
                normalized = NormalizeSingleSeparatorNumber(s, '.');
            }
            else
            {
                normalized = s;
            }

            if (negativeByParentheses && !normalized.StartsWith("-"))
            {
                normalized = "-" + normalized;
            }

            return normalized;
        }

        private static string NormalizeSingleSeparatorNumber(string value, char separator)
        {
            var separatorCount = value.Count(c => c == separator);
            if (separatorCount <= 0) return value;

            if (separatorCount == 1)
            {
                var idx = value.IndexOf(separator);
                var leftDigits = value[..idx].Count(char.IsDigit);
                var rightDigits = value[(idx + 1)..].Count(char.IsDigit);

                // "1.234"/"1,234" normalmente representa milhar.
                var looksLikeThousandsSeparator = rightDigits == 3 && leftDigits > 0 && leftDigits <= 3;
                if (looksLikeThousandsSeparator)
                {
                    return value.Replace(separator.ToString(), "");
                }

                return separator == ',' ? value.Replace(",", ".") : value;
            }

            var parts = value.Split(separator);
            var allGroupsAreThousands = parts.Skip(1).All(p => p.Length == 3 && p.All(char.IsDigit));
            if (allGroupsAreThousands)
            {
                return value.Replace(separator.ToString(), "");
            }

            var last = value.LastIndexOf(separator);
            var whole = value[..last].Replace(separator.ToString(), "");
            var fraction = value[(last + 1)..];
            return $"{whole}.{fraction}";
        }

        private static int TryInt(string s)
            => int.TryParse(OnlyDigits(s), out var n) ? n : 0;

        private static int? TryNullableInt(string? s)
        {
            if (string.IsNullOrWhiteSpace(s)) return null;
            var d = OnlyDigits(s);
            if (string.IsNullOrWhiteSpace(d)) return null;
            return int.TryParse(d, out var n) ? n : null;
        }

        private static string OnlyDigits(string? s)
        {
            if (string.IsNullOrWhiteSpace(s)) return "";
            var sb = new StringBuilder();
            foreach (var c in s) if (char.IsDigit(c)) sb.Append(c);
            return sb.ToString();
        }

        private static string? NullIfEmptyDigits(string? s)
        {
            var d = OnlyDigits(s);
            return string.IsNullOrWhiteSpace(d) ? null : d;
        }

        private static string NormalizeHeader(string? s)
        {
            if (string.IsNullOrWhiteSpace(s)) return "";
            s = s.Trim().ToUpperInvariant().Normalize(NormalizationForm.FormD);

            var sb = new StringBuilder();
            foreach (var c in s)
                if (CharUnicodeInfo.GetUnicodeCategory(c) != UnicodeCategory.NonSpacingMark)
                    sb.Append(c);

            return sb.ToString()
                     .Normalize(NormalizationForm.FormC)
                     .Replace(" ", "")
                     .Replace("\t", "")
                     .Replace("\n", "")
                     .Replace("\r", "");
        }

        // ==========================================================
        // ✅ BILLING HELPERS
        // ==========================================================
        private static int GetLastUsedColumn(IXLWorksheet ws, int headerRowIndex)
        {
            var row = ws.Row(headerRowIndex);
            var last = row.LastCellUsed()?.Address.ColumnNumber ?? 1;
            var last2 = ws.LastColumnUsed()?.ColumnNumber() ?? last;
            return Math.Max(last, last2);
        }

        private static bool RowHasAnyText(IXLRow row)
        {
            foreach (var c in row.CellsUsed())
            {
                if (!string.IsNullOrWhiteSpace(c.GetValue<string>()))
                    return true;
            }
            return false;
        }

        private static int FindColByAny(IXLRow headerRow, int lastCol, params string[] headers)
        {
            var wanted = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
            foreach (var h in headers) wanted.Add(NormalizeHeader(h));

            for (int col = 1; col <= lastCol; col++)
            {
                var key = NormalizeHeader(headerRow.Cell(col).GetString());
                if (string.IsNullOrWhiteSpace(key)) continue;

                if (wanted.Contains(key)) return col;

                foreach (var w in wanted)
                {
                    if (!string.IsNullOrWhiteSpace(w) && key.Contains(w))
                        return col;
                }
            }
            return 0;
        }

        private static string GetMergedGroupKeyAt(IXLRow groupRow, int col)
        {
            for (int c = col; c >= 1; c--)
            {
                var g = NormalizeHeader(groupRow.Cell(c).GetString());
                if (!string.IsNullOrWhiteSpace(g))
                    return g;
            }
            return "";
        }

        private static int FindColInGroup(IXLRow groupRow, IXLRow headerRow, int lastCol, string groupKey, params string[] headerKeys)
        {
            var gk = NormalizeHeader(groupKey);
            var wanted = headerKeys.Select(NormalizeHeader).ToArray();

            for (int col = 1; col <= lastCol; col++)
            {
                var groupAtCol = GetMergedGroupKeyAt(groupRow, col);
                if (string.IsNullOrWhiteSpace(groupAtCol)) continue;

                if (!groupAtCol.Contains(gk)) continue;

                var h = NormalizeHeader(headerRow.Cell(col).GetString());

                if (string.IsNullOrWhiteSpace(h) && wanted.Any(w => w == ""))
                    return col;

                foreach (var w in wanted)
                {
                    if (string.IsNullOrWhiteSpace(w)) continue;

                    if (h == w) return col;
                    if (!string.IsNullOrWhiteSpace(h) && h.Contains(w)) return col;
                }
            }

            return 0;
        }
    }
}