viabiliza/service/csvService.js

const { consultarViabilidadeLote, discoverDataType } = require('./viabilidadeService');
const fs = require('fs');
const path = require('path');
const XLSX = require('xlsx');
const ExcelJS = require('exceljs');
const { once } = require('events');
const {
  normalizeHeader,
  isExcelFile,
  detectDelimiter,
  hasCepHeader,
  hasAddressOrNumberHeader,
  hasGeoHeaders
} = require('./fileFormat');
const {
  incrementProcessed,
  incrementErrors,
  finishJob
} = require('./jobStore.service');

const RESULT_HEADERS = [
  'Provedor (C1)', 'Nao Dedicado (C1)', 'Dedicado (C1)', 'Distancia Trajeto (C1)',
  'Provedor (C2)', 'Nao Dedicado (C2)', 'Dedicado (C2)', 'Distancia Trajeto (C2)',
  'Erro'
];

// Quantidade de itens enviados por chamada ao endpoint /lote da API.
const LOTE_CHUNK = 25;

function isXlsxFile(filePath) {
  const ext = path.extname(filePath).toLowerCase();
  if (ext === '.xlsx') return true;

  const fileStart = fs.readFileSync(filePath).subarray(0, 4);
  return fileStart[0] === 0x50 && fileStart[1] === 0x4b;
}

function splitDelimitedLine(line, delimiter) {
  const cols = [];
  let current = '';
  let inQuotes = false;

  for (let i = 0; i < line.length; i++) {
    const char = line[i];
    const next = line[i + 1];

    if (char === '"' && next === '"') {
      current += '"';
      i++;
    } else if (char === '"') {
      inQuotes = !inQuotes;
    } else if (char === delimiter && !inQuotes) {
      cols.push(current.trim());
      current = '';
    } else {
      current += char;
    }
  }

  cols.push(current.trim());
  return cols;
}

function readDelimitedRows(filePath) {
  const content = fs.readFileSync(filePath, 'utf8').replace(/^/, '');
  const lines = content.split(/\r?\n/).filter(line => line.trim());
  if (!lines.length) return [];

  const delimiter = detectDelimiter(lines[0]);
  return lines.map(line => splitDelimitedLine(line.replace(/\r$/, ''), delimiter));
}

function readExcelRows(filePath) {
  const workbook = XLSX.readFile(filePath, { cellDates: false, raw: false });
  const firstSheetName = workbook.SheetNames[0];
  if (!firstSheetName) return [];

  return XLSX.utils.sheet_to_json(workbook.Sheets[firstSheetName], {
    header: 1,
    blankrows: true,
    defval: ''
  }).map(row => row.map(cell => String(cell ?? '').trim()));
}

function readRows(filePath) {
  return isExcelFile(filePath) ? readExcelRows(filePath) : readDelimitedRows(filePath);
}

function findFirstHeaderIndex(headers, predicate) {
  return headers.map(normalizeHeader).findIndex(predicate);
}

function findHeaderRowIndex(rows) {
  const index = rows.findIndex(row => (hasCepHeader(row) && hasAddressOrNumberHeader(row)) || hasGeoHeaders(row));
  return index >= 0 ? index : 0;
}

function resolveColumnIndexes(headers) {
  const normalizedHeaders = headers.map(normalizeHeader);
  const exactIndex = aliases => {
    const normalizedAliases = aliases.map(normalizeHeader);
    return normalizedHeaders.findIndex(header => normalizedAliases.includes(header));
  };

  return {
    idxCep: findFirstHeaderIndex(headers, header => /\bcep\b/.test(header) || header === 'codigo postal'),
    idxNumero: exactIndex(['numero', 'número', 'num', 'nº', 'n°']),
    idxEndereco: findFirstHeaderIndex(headers, header => header.includes('endereco') || header.includes('logradouro')),
    idxLatitude:  findFirstHeaderIndex(headers, header => /\blat(itude)?\b/.test(header)),
    idxLongitude: findFirstHeaderIndex(headers, header => /\blo?n[g]?(itude)?\b/.test(header)),
  };
}

function extractAddressNumber(address) {
  const value = String(address || '').trim();
  if (!value) return '1';

  const withoutRoadKm = value
    .replace(/\b(BR|SP|GO|MT|KM)\s*[-]?\s*\d+[A-Z]?\b/gi, ' ')
    .replace(/\b\d+\s*[A-Z]?\b\s*(?=\))/gi, ' ');

  const labeledNumber = withoutRoadKm.match(/\b(?:n|no|num|numero|número|nº|n°)\.?\s*[:,-]?\s*(\d+[A-Z]?)\b/i);
  if (labeledNumber) return labeledNumber[1];

  const commaNumber = withoutRoadKm.match(/,\s*(\d+[A-Z]?)\b/i);
  if (commaNumber) return commaNumber[1];

  const standaloneNumbers = withoutRoadKm.match(/\b\d+[A-Z]?\b/gi) || [];
  return standaloneNumbers.length ? standaloneNumbers[standaloneNumbers.length - 1] : '1';
}

function buildCepPayload(cols, indexes) {
  const cepRaw = indexes.idxCep >= 0 ? cols[indexes.idxCep] : '';
  const cep = String(cepRaw || '').replace(/\D/g, '');
  const numeroRaw = indexes.idxNumero >= 0 ? cols[indexes.idxNumero] : '';
  const enderecoRaw = indexes.idxEndereco >= 0 ? cols[indexes.idxEndereco] : '';
  const numero = String(numeroRaw || '').trim() || extractAddressNumber(enderecoRaw);

  if (!cep) return null;
  return { cep, numero };
}

function parseCoordinate(value) {
  const str = String(value ?? '')
    .replace(/[   -​　]/g, '') // remove espaços não-quebráveis
    .trim()
    .replace(',', '.');

  // converte DMS "17 38 18.80 S" → -17.638556. Aceita direção N/S/E/W e também
  // O (Oeste=West) e L (Leste=East), usadas em pt-BR.
  const dms = str.match(/^(\d+)[°\s]+(\d+)['\s]+(\d+(?:\.\d+)?)["\s]*([NSEWOL])?$/i);
  if (dms) {
    const decimal = parseFloat(dms[1]) + parseFloat(dms[2]) / 60 + parseFloat(dms[3]) / 3600;
    const negativo = /[SWO]/i.test(dms[4] ?? ''); // Sul / West / Oeste => negativo
    return negativo ? -decimal : decimal;
  }

  // decimal, podendo ter direção no fim (ex.: "45.55 O", "8.6 S")
  const dir = str.match(/([NSEWOL])\s*$/i);
  const parsed = parseFloat(str);
  if (!Number.isFinite(parsed)) return NaN;
  if (dir) return /[SWO]/i.test(dir[1]) ? -Math.abs(parsed) : Math.abs(parsed);
  return parsed;
}

function buildGeoPayload(cols, indexes) {
  const latitude = indexes.idxLatitude >= 0 ? parseCoordinate(cols[indexes.idxLatitude]) : NaN;
  const longitude = indexes.idxLongitude >= 0 ? parseCoordinate(cols[indexes.idxLongitude]) : NaN;

  if (isNaN(latitude) || isNaN(longitude)) return null;
  return { latitude, longitude };
}

// Monta a lista de linhas com payload a consultar. Para cada linha, define o
// payload primário (geo se houver, senão cep) e o de fallback (cep, quando a
// linha tem ambos — preserva a semântica antiga "tenta geo, senão cep").
function buildLinhas(rows, headerRowIndex, indexes) {
  const linhas = [];
  for (let rowIndex = headerRowIndex + 1; rowIndex < rows.length; rowIndex++) {
    const cols = rows[rowIndex];
    const geo = buildGeoPayload(cols, indexes);
    const cep = buildCepPayload(cols, indexes);
    if (!geo && !cep) continue;
    linhas.push({ rowIndex, primary: geo || cep, fallback: (geo && cep) ? cep : null });
  }
  return linhas;
}

// Avalia todas as linhas via o endpoint /lote da API, em blocos. Retorna um
// Map(rowIndex -> resultado | { __erro }). Atualiza o progresso do job.
async function avaliarLinhas(jobId, linhas) {
  const resultadoPorRow = new Map();

  for (let start = 0; start < linhas.length; start += LOTE_CHUNK) {
    const chunk = linhas.slice(start, start + LOTE_CHUNK);

    let resultados;
    try {
      resultados = await consultarViabilidadeLote(chunk.map(l => l.primary));
    } catch (err) {
      const msg = formatApiErrorResponse(err);
      chunk.forEach(l => {
        resultadoPorRow.set(l.rowIndex, { __erro: msg });
        incrementErrors(jobId);
        incrementProcessed(jobId);
      });
      continue;
    }

    const paraFallback = [];
    chunk.forEach((linha, i) => {
      const r = resultados[i];
      if (r && r.erro && linha.fallback) {
        paraFallback.push(linha);
      } else if (r && r.erro) {
        resultadoPorRow.set(linha.rowIndex, { __erro: r.erro });
      } else if (r) {
        resultadoPorRow.set(linha.rowIndex, r);
      } else {
        resultadoPorRow.set(linha.rowIndex, { __erro: 'Sem resultado para a linha' });
      }
    });

    if (paraFallback.length) {
      let fb = null;
      try {
        fb = await consultarViabilidadeLote(paraFallback.map(l => l.fallback));
      } catch (err) {
        const msg = formatApiErrorResponse(err);
        paraFallback.forEach(l => resultadoPorRow.set(l.rowIndex, { __erro: msg }));
      }
      if (fb) {
        paraFallback.forEach((linha, k) => {
          const r = fb[k];
          resultadoPorRow.set(linha.rowIndex, (r && !r.erro) ? r : { __erro: (r && r.erro) || 'Linha sem latitude/longitude ou CEP valido' });
        });
      }
    }

    chunk.forEach(l => {
      const r = resultadoPorRow.get(l.rowIndex);
      if (r && r.__erro) incrementErrors(jobId);
      incrementProcessed(jobId);
    });
  }

  return resultadoPorRow;
}

function cleanCsvValue(value) {
  const text = String(value ?? '').replace(/[\r\n;]/g, ' ');
  return text.includes('"') ? text.replace(/"/g, "'") : text;
}

function formatApiErrorResponse(error) {
  const responseData = error && error.response && error.response.data;
  if (responseData !== undefined && responseData !== null) {
    if (typeof responseData === 'string') return responseData;
    if (responseData.error) return responseData.error;
    if (responseData.message) return responseData.message;
    return JSON.stringify(responseData);
  }

  return error && (error.message || String(error));
}

// 4 colunas por caixa: Provedor, Nao Dedicado, Dedicado, Distancia Trajeto.
// Caixa inexistente (ex.: só 1 caixa próxima) => colunas em branco.
function caixaCols(caixa) {
  if (!caixa) return ['', '', '', ''];
  const viavel = v => (v ? 'Viavel' : 'Nao Viavel');
  const distancia = (caixa.distancia != null && caixa.distancia !== '') ? caixa.distancia : '';
  return [caixa.provedor ?? '', viavel(caixa.naoDedicado), viavel(caixa.dedicado), distancia];
}

function buildSuccessResultColumns(viab) {
  const caixas = viab.caixas || [];
  const error = viab.error ? cleanCsvValue(viab.error) : '';

  let c1;
  if (caixas.length === 0) {
    // Sem cobertura: monta o status como "Caixa 1" (em vez de deixar em branco).
    const viavel = v => (v ? 'Viavel' : 'Nao Viavel');
    const distancia = (viab.distancia != null && viab.distancia !== '') ? viab.distancia : '';
    c1 = [viab.provedor ?? 'Nenhum provedor disponível', viavel(viab.naoDedicado), viavel(viab.dedicado), distancia];
  } else {
    c1 = caixaCols(caixas[0]);
  }

  // Sem 2ª caixa: replica as infos da Caixa 1 (não deixa a Caixa 2 em branco).
  const c2 = caixas[1] ? caixaCols(caixas[1]) : c1;

  return [...c1, ...c2, error];
}

function buildErrorResultColumns(err) {
  return ['', '', '', '', '', '', '', '', cleanCsvValue(formatApiErrorResponse(err))];
}

// Colunas de resultado para uma linha, a partir do Map de resultados do lote.
function resultColumnsFor(resultadoPorRow, rowIndex) {
  const resultado = resultadoPorRow.get(rowIndex) || { __erro: 'Linha não avaliada' };
  return resultado.__erro
    ? buildErrorResultColumns({ message: resultado.__erro })
    : buildSuccessResultColumns(resultado);
}

function cloneCellStyle(cell) {
  return {
    numFmt: cell.numFmt,
    font: cell.font ? { ...cell.font } : undefined,
    alignment: cell.alignment ? { ...cell.alignment } : undefined,
    border: cell.border ? { ...cell.border } : undefined,
    fill: cell.fill ? { ...cell.fill } : undefined,
    protection: cell.protection ? { ...cell.protection } : undefined
  };
}

function styleInsertedResultColumns(worksheet, headerRowNumber) {
  RESULT_HEADERS.forEach((header, index) => {
    const columnNumber = index + 1;
    const sourceColumn = worksheet.getColumn(RESULT_HEADERS.length + 1);
    const targetColumn = worksheet.getColumn(columnNumber);
    targetColumn.width = Math.max(16, sourceColumn.width || 0);

    const headerCell = worksheet.getRow(headerRowNumber).getCell(columnNumber);
    const sourceHeaderCell = worksheet.getRow(headerRowNumber).getCell(RESULT_HEADERS.length + 1);
    headerCell.value = header;
    headerCell.style = cloneCellStyle(sourceHeaderCell);
  });
}

async function processXlsxFile(inputPath, outputPath, rows, headerRowIndex, indexes, resultadoPorRow) {
  const workbook = new ExcelJS.Workbook();
  await workbook.xlsx.readFile(inputPath);

  const worksheet = workbook.worksheets[0];
  const headerRowNumber = headerRowIndex + 1;
  worksheet.spliceColumns(1, 0, ...RESULT_HEADERS.map(() => []));
  styleInsertedResultColumns(worksheet, headerRowNumber);

  for (let rowIndex = headerRowIndex + 1; rowIndex < rows.length; rowIndex++) {
    const cols = rows[rowIndex];
    const geoPayload = buildGeoPayload(cols, indexes);
    const cepPayload = buildCepPayload(cols, indexes);
    if (!geoPayload && !cepPayload) continue;

    const row = worksheet.getRow(rowIndex + 1);
    resultColumnsFor(resultadoPorRow, rowIndex).forEach((value, index) => {
      const cell = row.getCell(index + 1);
      cell.value = value;
      cell.style = cloneCellStyle(row.getCell(RESULT_HEADERS.length + 1));
    });
    row.commit();
  }

  await workbook.xlsx.writeFile(outputPath);
}

function shiftCellAddress(address, colOffset) {
  const decoded = XLSX.utils.decode_cell(address);
  decoded.c += colOffset;
  return XLSX.utils.encode_cell(decoded);
}

function shiftRange(range, colOffset) {
  const decoded = typeof range === 'string' ? XLSX.utils.decode_range(range) : range;
  return {
    s: { r: decoded.s.r, c: decoded.s.c + colOffset },
    e: { r: decoded.e.r, c: decoded.e.c + colOffset }
  };
}

function prependResultColumnsToWorksheet(worksheet, headerRowIndex, rowResults) {
  const colOffset = RESULT_HEADERS.length;
  const shiftedWorksheet = {};

  Object.keys(worksheet).forEach(key => {
    if (key[0] === '!') return;
    shiftedWorksheet[shiftCellAddress(key, colOffset)] = worksheet[key];
  });

  const originalRange = worksheet['!ref']
    ? XLSX.utils.decode_range(worksheet['!ref'])
    : { s: { r: 0, c: 0 }, e: { r: headerRowIndex, c: 0 } };

  shiftedWorksheet['!ref'] = XLSX.utils.encode_range({
    s: { r: Math.min(originalRange.s.r, headerRowIndex), c: 0 },
    e: { r: originalRange.e.r, c: originalRange.e.c + colOffset }
  });

  if (worksheet['!cols']) {
    shiftedWorksheet['!cols'] = Array(colOffset).fill({ wch: 16 }).concat(worksheet['!cols']);
  }

  if (worksheet['!merges']) {
    shiftedWorksheet['!merges'] = worksheet['!merges'].map(merge => shiftRange(merge, colOffset));
  }

  if (worksheet['!autofilter'] && worksheet['!autofilter'].ref) {
    shiftedWorksheet['!autofilter'] = {
      ...worksheet['!autofilter'],
      ref: XLSX.utils.encode_range(shiftRange(worksheet['!autofilter'].ref, colOffset))
    };
  }

  RESULT_HEADERS.forEach((value, index) => {
    const address = XLSX.utils.encode_cell({ r: headerRowIndex, c: index });
    shiftedWorksheet[address] = { t: 's', v: value };
  });

  rowResults.forEach(({ rowIndex, values }) => {
    values.forEach((value, index) => {
      const address = XLSX.utils.encode_cell({ r: rowIndex, c: index });
      shiftedWorksheet[address] = { t: 's', v: String(value ?? '') };
    });
  });

  return shiftedWorksheet;
}

async function countValidLines(inputPath) {
  await discoverDataType(inputPath);
  const rows = readRows(inputPath);
  const headerRowIndex = findHeaderRowIndex(rows);
  const headers = rows[headerRowIndex] || [];
  const indexes = resolveColumnIndexes(headers);
  let total = 0;

  for (const cols of rows.slice(headerRowIndex + 1)) {
    const geoPayload = buildGeoPayload(cols, indexes);
    const cepPayload = buildCepPayload(cols, indexes);
    if (geoPayload || cepPayload) total++;
  }

  return total;
}

async function processCsvFile(jobId, inputPath, originalName) {
  await discoverDataType(inputPath);
  const rows = readRows(inputPath);
  const headerRowIndex = findHeaderRowIndex(rows);
  const headers = rows[headerRowIndex] || [];
  const indexes = resolveColumnIndexes(headers);
  const baseName = path.parse(originalName || inputPath).name;
  const isExcel = isExcelFile(inputPath);
  const outputFilename = `processed_${Date.now()}_${baseName}${isExcel ? '.xlsx' : '.csv'}`;
  const outputPath = path.join(__dirname, '..', 'outputs', outputFilename);
  fs.mkdirSync(path.dirname(outputPath), { recursive: true });

  // Avalia todas as linhas de uma vez (em blocos) via /lote da API.
  const linhas = buildLinhas(rows, headerRowIndex, indexes);
  const resultadoPorRow = await avaliarLinhas(jobId, linhas);

  if (isXlsxFile(inputPath)) {
    await processXlsxFile(inputPath, outputPath, rows, headerRowIndex, indexes, resultadoPorRow);
    finishJob(jobId, path.basename(outputPath));

    return outputPath;
  }

  if (isExcel) {
    const workbook = XLSX.readFile(inputPath, { cellDates: false, raw: false, cellStyles: true });
    const firstSheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[firstSheetName];
    const rowResults = [];

    for (let rowIndex = headerRowIndex + 1; rowIndex < rows.length; rowIndex++) {
      const cols = rows[rowIndex];
      const geoPayload = buildGeoPayload(cols, indexes);
      const cepPayload = buildCepPayload(cols, indexes);
      if (!geoPayload && !cepPayload) continue;

      rowResults.push({ rowIndex, values: resultColumnsFor(resultadoPorRow, rowIndex) });
    }

    workbook.Sheets[firstSheetName] = prependResultColumnsToWorksheet(worksheet, headerRowIndex, rowResults);
    XLSX.writeFile(workbook, outputPath, { bookType: 'xlsx' });
    finishJob(jobId, path.basename(outputPath));

    return outputPath;
  }

  const outStream = fs.createWriteStream(outputPath, { encoding: 'utf8' });
  outStream.write('');
  outStream.write([...RESULT_HEADERS, ...headers].join(';') + '\n');

  for (let rowIndex = headerRowIndex + 1; rowIndex < rows.length; rowIndex++) {
    const cols = rows[rowIndex];
    const geoPayload = buildGeoPayload(cols, indexes);
    const cepPayload = buildCepPayload(cols, indexes);
    if (!geoPayload && !cepPayload) continue;

    const outCols = [...resultColumnsFor(resultadoPorRow, rowIndex), ...cols].map(cleanCsvValue);
    outStream.write(outCols.join(';') + '\n');
  }

  outStream.end();
  await once(outStream, 'finish');

  finishJob(jobId, path.basename(outputPath));

  return outputPath;
}

module.exports = { processCsvFile, countValidLines };