Excel Import Guide

// Client-side: Generate hash from plaintext before encryption
const importHash = crypto.createHash('sha256')
  .update(`${tx.date}|${tx.description}|${tx.amount}`)
  .digest('hex')

// Server-side: Check existing hashes
if (existingHashes.has(importHash)) {
  skipped++
  continue
}

// Backend: excel-parser.ts
function detectFileType(workbook: XLSX.WorkBook): FileType {
  // Check for Control de Gastos (has month sheets)
  const monthSheets = ['Enero', 'Febrero', 'Marzo', ...]
  if (workbook.SheetNames.some(name => monthSheets.includes(name.trim()))) {
    return 'control_gastos'
  }

  const firstSheet = workbook.Sheets[workbook.SheetNames[0]]
  const data = XLSX.utils.sheet_to_json(firstSheet, { header: 1 })

  // Check for Movimientos CC (has F. VALOR column)
  for (let i = 0; i < Math.min(10, data.length); i++) {
    const row = data[i]
    if (row?.some(cell => 
      typeof cell === 'string' && cell.toUpperCase().includes('F. VALOR')
    )) {
      return 'movimientos_cc'
    }
  }

  // Check for generic Excel with date + amount columns
  const dateKeywords = ['fecha', 'date', 'valor fecha', 'f.valor']
  const amountKeywords = ['importe', 'amount', 'cantidad', 'monto']
  // ... detection logic

  return 'generic_excel'
}

function detectDelimiter(content: string): ',' | ';' | '\t' {
  const firstLine = content.split(/\r?\n/)[0]
  const delimiters = [',', ';', '\t'] as const
  
  const counts = delimiters.map(d => ({
    delimiter: d,
    count: (firstLine.match(new RegExp(d === '\t' ? '\t' : `\\${d}`, 'g')) || []).length
  }))

  const best = counts.sort((a, b) => b.count - a.count)[0]
  return best.delimiter
}

// Frontend: User-friendly error messages
function getFriendlyErrorMessage(error: string): string {
  const errorMap: Record<string, string> = {
    'Solo se permiten archivos Excel (.xls, .xlsx) o CSV (.csv)':
      'El formato del archivo no es soportado. Usa archivos Excel (.xls, .xlsx) o CSV (.csv)',
    'El archivo supera el tamaño máximo permitido (5MB)':
      'El archivo es demasiado grande. El tamaño máximo es 5MB',
    'Error al procesar el archivo': 
      'No se pudo leer el archivo. Asegúrate de que no está dañado',
    // ... more mappings
  }

  for (const [key, value] of Object.entries(errorMap)) {
    if (error.includes(key)) return value
  }
  return error || 'Ha ocurrido un error inesperado'
}

// Backend: utils/sanitize.js
export function sanitizeCSVValue(value: string): string {
  if (!value) return ''
  
  // Remove dangerous CSV injection prefixes
  let sanitized = value.toString().trim()
  const dangerousStarts = ['=', '+', '-', '@', '\t', '\r']
  
  while (dangerousStarts.some(prefix => sanitized.startsWith(prefix))) {
    sanitized = sanitized.substring(1).trim()
  }
  
  // Remove control characters and XSS patterns
  sanitized = sanitized.replace(/[\x00-\x1F\x7F]/g, '')
  
  return sanitized
}

const isEncrypted = transactions.length > 0 && !!(transactions[0] as any).description_encrypted

if (isEncrypted) {
  // Store encrypted fields
  placeholders.push('(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)')
  values.push(
    id, account_id, subcategoryId, tx.date,
    null, null,  // Plaintext fields are null
    txAny.description_encrypted,
    txAny.amount_encrypted,
    txAny.amount_sign,  // Sign stored separately for filtering
    txAny.bank_category_encrypted,
    txAny.bank_subcategory_encrypted,
    tx.bank_category,  // Hash for deduplication
    importHash
  )
}

// Backend: Save mappings after successful import
if (category_mappings && category_mappings.length > 0) {
  const validMappings = category_mappings.filter(m => m.subcategory_id)
  
  if (validMappings.length > 0) {
    const mappingPlaceholders: string[] = []
    const mappingValues: any[] = []

    for (const mapping of validMappings) {
      mappingPlaceholders.push('(UUID(), ?, ?, ?, ?)')
      mappingValues.push(
        account_id,
        mapping.bank_category,
        mapping.bank_subcategory,
        mapping.subcategory_id
      )
    }

    await db.query(
      `INSERT INTO category_mappings 
        (id, account_id, bank_category, bank_subcategory, subcategory_id)
       VALUES ${mappingPlaceholders.join(', ')}
       ON DUPLICATE KEY UPDATE 
         subcategory_id = VALUES(subcategory_id), 
         updated_at = CURRENT_TIMESTAMP`,
      mappingValues
    )
  }
}