Normalize contact rows and quarantine bad mappings

{
  "label": "Supplied agency data"
}

{
  "label": "Normalize contact rows and quarantine bad mappings",
  "code": "\nfunction obj(value, keys, required = keys) {\n  if (!value || typeof value !== 'object' || Array.isArray(value)) throw new Error('Expected object');\n  if (Object.keys(value).some(key => !keys.includes(key))) throw new Error('Unknown input field');\n  if (required.some(key => !Object.hasOwn(value, key))) throw new Error('Missing required input field');\n  return value;\n}\nfunction text(value, name, max = 200, allowEmpty = false) {\n  if (typeof value !== 'string' || value.length > max || (!allowEmpty && !value.trim())) throw new Error('Invalid ' + name);\n  return value.trim();\n}\nfunction list(value, name, max = 500) {\n  if (!Array.isArray(value) || value.length > max) throw new Error('Invalid ' + name);\n  return value;\n}\nfunction int(value, name, min = 0, max = Number.MAX_SAFE_INTEGER) {\n  if (!Number.isSafeInteger(value) || value < min || value > max) throw new Error('Invalid ' + name);\n  return value;\n}\nfunction amount(value, name) {\n  if (!Number.isFinite(value) || value < 0 || value > 1000000000) throw new Error('Invalid ' + name);\n  return value;\n}\nfunction email(value) { return /^[^\\s@]+@[^\\s@]+\\.[^\\s@]+$/.test(value); }\nfunction day(value) {\n  if (typeof value !== 'string' || !/^\\d{4}-\\d{2}-\\d{2}$/.test(value)) throw new Error('Invalid calendar date');\n  const ms = Date.parse(value + 'T00:00:00Z');\n  if (!Number.isFinite(ms) || new Date(ms).toISOString().slice(0, 10) !== value) throw new Error('Invalid calendar date');\n  return ms;\n}\nfunction instant(value) {\n  if (typeof value !== 'string' || !/^\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}(\\.\\d{3})?Z$/.test(value)) throw new Error('Invalid UTC timestamp');\n  const ms = Date.parse(value);\n  const normalized = value.includes('.') ? value : value.replace('Z', '.000Z');\n  if (!Number.isFinite(ms) || new Date(ms).toISOString() !== normalized) throw new Error('Invalid UTC timestamp');\n  return ms;\n}\nfunction uniqueIds(items) {\n  const ids = items.map(item => text(item.id, 'item ID', 120));\n  if (new Set(ids).size !== ids.length) throw new Error('Duplicate item ID');\n  return ids;\n}\nfunction ratioBps(numerator, denominator) {\n  const n = BigInt(numerator) * 10000n;\n  const d = BigInt(denominator);\n  const quotient = n / d;\n  const floor = n < 0n && n % d !== 0n ? quotient - 1n : quotient;\n  const result = Number(floor);\n  if (!Number.isSafeInteger(result)) throw new Error('Unsafe calculated ratio');\n  return result;\n}\n\n\nconst x = obj(context.input, ['importId','mapping','rows']);\nconst importId = text(x.importId, 'import ID', 120), mapping = obj(x.mapping, ['externalId','name','email']);\nconst keys = ['externalId','name','email'];\nconst columns = keys.map(key => text(mapping[key], 'mapping column', 120));\nif (new Set(columns).size !== columns.length) throw new Error('Overlapping mapping keys');\nconst rows = list(x.rows, 'rows');\nconst parsed = rows.map((row,index) => {\n  obj(row, ['cells']);\n  const cells = list(row.cells, 'cells', 50), values = new Map(), reasons = [];\n  cells.forEach(cell => {\n    obj(cell, ['column','value']);\n    const column = text(cell.column, 'column', 120), value = text(cell.value, 'cell value', 4000, true);\n    if (!columns.includes(column)) return;\n    if (values.has(column)) { if (!reasons.includes('ambiguous_mapped_column')) reasons.push('ambiguous_mapped_column'); }\n    else values.set(column, value);\n  });\n  const externalId = values.get(columns[0]) || '', name = values.get(columns[1]) || '', address = (values.get(columns[2]) || '').toLowerCase();\n  if (!externalId || externalId.length > 120) reasons.push('invalid_external_id');\n  if (!name || name.length > 200) reasons.push('invalid_name');\n  if (!email(address) || address.length > 320) reasons.push('invalid_email');\n  return {row:index + 1, externalId, name, address, reasons};\n});\nconst counts = new Map();\nparsed.forEach(row => { if (row.externalId) counts.set(row.externalId, (counts.get(row.externalId) || 0) + 1); });\nconst drafts = [], quarantine = [];\nparsed.forEach(row => {\n  if (counts.get(row.externalId) > 1) row.reasons.push('duplicate_external_id');\n  if (row.reasons.length) quarantine.push({row:row.row, reasons:row.reasons});\n  else drafts.push({externalId:row.externalId, name:row.name, email:row.address});\n});\nreturn {importId, drafts, quarantine};\n",
  "outputVar": "result",
  "timeout": 1000,
  "memoryLimit": 16
}