justvitamin/scripts/ingest-dataforseo-amazon-reviews.ts

#!/usr/bin/env bun
import { existsSync, mkdirSync, readdirSync, readFileSync, writeFileSync } from 'fs'
import { basename, join } from 'path'

const root = process.cwd()
const dataforseoRoot = join(root, 'data', 'sources', 'dataforseo')
const rawRoot = join(dataforseoRoot, 'raw')
const amazonRoot = join(root, 'data', 'sources', 'amazon')
const outRoot = join(root, 'content_population_exports')
const asinMapPath = join(dataforseoRoot, 'asin-map.csv')

mkdirSync(rawRoot, { recursive: true })
mkdirSync(amazonRoot, { recursive: true })
mkdirSync(outRoot, { recursive: true })

function readJson(path: string, fallback: any = null) {
  if (!existsSync(path)) return fallback
  return JSON.parse(readFileSync(path, 'utf8').replace(/^\uFEFF/, ''))
}

function csv(rows: any[], headers: string[]) {
  const esc = (value: any) => {
    if (value === undefined || value === null) value = ''
    if (Array.isArray(value)) value = value.join(' | ')
    value = String(value)
    return /[",\n\r]/.test(value) ? `"${value.replace(/"/g, '""')}"` : value
  }
  return [headers.join(','), ...rows.map(row => headers.map(header => esc(row[header])).join(','))].join('\n') + '\n'
}

function parseCsv(text: string) {
  const rows: string[][] = []
  let row: string[] = []
  let cell = ''
  let quoted = false
  for (let index = 0; index < text.length; index += 1) {
    const char = text[index]
    const next = text[index + 1]
    if (quoted) {
      if (char === '"' && next === '"') { cell += '"'; index += 1 }
      else if (char === '"') quoted = false
      else cell += char
    } else if (char === '"') quoted = true
    else if (char === ',') { row.push(cell); cell = '' }
    else if (char === '\n') { row.push(cell); rows.push(row); row = []; cell = '' }
    else if (char !== '\r') cell += char
  }
  if (cell || row.length) { row.push(cell); rows.push(row) }
  const headers = rows.shift()?.map(header => header.trim()) || []
  return rows.filter(values => values.some(value => value.trim())).map(values => Object.fromEntries(headers.map((header, index) => [header, (values[index] || '').trim()])))
}

function ratingValue(value: any) {
  if (value === undefined || value === null || value === '') return null
  if (typeof value === 'object') return ratingValue(value.value)
  const number = Number(value)
  return Number.isFinite(number) ? number : value
}

function tagSku(task: any) {
  const tag = task?.data?.tag || task?.tag || ''
  const match = String(tag).match(/jv:([^:]+):/i)
  return match?.[1] || ''
}

function normaliseTaskResponse(raw: any, file: string, asinRows: any[]) {
  const outputs: any[] = []
  const issues: string[] = []
  const tasks = Array.isArray(raw?.tasks) ? raw.tasks : []
  if (!tasks.length) issues.push('missing tasks array')

  for (const task of tasks) {
    const results = Array.isArray(task?.result) ? task.result : []
    if (!results.length) {
      issues.push(`${file}: task ${task?.id || 'unknown'} has no result array`)
      continue
    }
    for (const result of results) {
      const asin = result?.asin || task?.data?.asin || ''
      const mapped = asinRows.find(row => row.ASIN && row.ASIN.toUpperCase() === String(asin).toUpperCase()) || asinRows.find(row => row.SKU === tagSku(task)) || {}
      const sku = mapped.SKU || tagSku(task) || asin || basename(file, '.json')
      const items = Array.isArray(result?.items) ? result.items : []
      if (!items.length) issues.push(`${file}: ${sku} has no review items`)
      const reviews = items.map((item: any) => ({
        review_title: item?.title || '',
        review_body: item?.review_text || item?.review_body || '',
        rating: ratingValue(item?.rating),
        date: item?.publication_date || item?.date || '',
        helpful_count: item?.helpful_votes ?? item?.helpful_count ?? 0,
        url: item?.url || '',
        verified: item?.verified ?? null,
        reviewer_name: item?.user_profile?.name || '',
        reviewer_url: item?.user_profile?.url || ''
      }))
      const missingText = reviews.filter((review: any) => !review.review_body || String(review.review_body).trim().length < 5).length
      const missingRating = reviews.filter((review: any) => review.rating === null || review.rating === '').length
      if (missingText) issues.push(`${file}: ${sku} has ${missingText} review item(s) without text`)
      if (missingRating) issues.push(`${file}: ${sku} has ${missingRating} review item(s) without rating`)
      outputs.push({
        source: 'amazon',
        provider: 'dataforseo',
        brand: mapped.Product ? 'Just Vitamins' : '',
        sku,
        asin,
        productUrl: mapped.ProductUrl || result?.check_url || result?.image?.url || '',
        capturedAt: result?.datetime || new Date().toISOString(),
        taskId: task?.id || '',
        cost: task?.cost ?? raw?.cost ?? null,
        productTitle: result?.title || mapped.Product || '',
        amazonDomain: result?.se_domain || mapped.AmazonDomain || '',
        averageRating: ratingValue(result?.rating),
        reviewsCount: result?.reviews_count ?? null,
        reviews
      })
    }
  }
  return { outputs, issues }
}

const generatedAt = new Date().toISOString()
const asinRows = existsSync(asinMapPath) ? parseCsv(readFileSync(asinMapPath, 'utf8')) : []
const rawFiles = readdirSync(rawRoot, { withFileTypes: true })
  .filter(entry => entry.isFile() && entry.name.endsWith('.json') && !entry.name.startsWith('_'))
  .map(entry => join(rawRoot, entry.name))

const example = {
  note: 'Paste DataForSEO /v3/merchant/amazon/reviews/task_get/advanced/{id} JSON responses into this raw/ folder. Files starting with _ are ignored.',
  expectedEndpoint: 'GET https://api.dataforseo.com/v3/merchant/amazon/reviews/task_get/advanced/{id}',
  expectedFields: ['tasks[].id', 'tasks[].data.asin', 'tasks[].result[].items[].review_text', 'tasks[].result[].items[].rating.value', 'tasks[].result[].items[].publication_date', 'tasks[].result[].items[].url']
}
writeFileSync(join(rawRoot, '_example-amazon-reviews-task_get-advanced.json'), JSON.stringify(example, null, 2) + '\n', 'utf8')

const rows: any[] = []
const allIssues: string[] = []
let convertedFiles = 0
let convertedReviews = 0
for (const file of rawFiles) {
  try {
    const raw = readJson(file)
    const { outputs, issues } = normaliseTaskResponse(raw, file, asinRows)
    allIssues.push(...issues)
    for (const output of outputs) {
      const safeSku = String(output.sku || output.asin).replace(/[^a-z0-9_-]/gi, '-').toUpperCase()
      const outPath = join(amazonRoot, `dataforseo-${safeSku}.json`)
      writeFileSync(outPath, JSON.stringify(output, null, 2) + '\n', 'utf8')
      convertedFiles += 1
      convertedReviews += output.reviews.length
      rows.push({ RawFile: file, SKU: output.sku, ASIN: output.asin, Reviews: output.reviews.length, OutputFile: outPath, Status: issues.length ? 'converted_with_warnings' : 'converted' })
    }
    if (!outputs.length) rows.push({ RawFile: file, SKU: '', ASIN: '', Reviews: 0, OutputFile: '', Status: 'no_outputs' })
  } catch (error: any) {
    allIssues.push(`${file}: ${error.message}`)
    rows.push({ RawFile: file, SKU: '', ASIN: '', Reviews: 0, OutputFile: '', Status: 'invalid_json' })
  }
}

if (!rawFiles.length) {
  rows.push({ RawFile: '', SKU: '', ASIN: '', Reviews: 0, OutputFile: '', Status: 'waiting_for_raw_dataforseo_results' })
}

const manifest = {
  generatedAt,
  ready: rawFiles.length > 0 && convertedFiles > 0 && allIssues.length === 0,
  rawInputDir: 'data/sources/dataforseo/raw',
  amazonOutputDir: 'data/sources/amazon',
  docs: ['https://docs.dataforseo.com/v3/merchant/amazon/reviews/task_get/advanced/'],
  counts: {
    rawFiles: rawFiles.length,
    convertedFiles,
    convertedReviews,
    issues: allIssues.length,
    asinMapRows: asinRows.length
  },
  issues: allIssues,
  outputs: rows
}

writeFileSync(join(outRoot, 'dataforseo_ingest_validation.json'), JSON.stringify(manifest, null, 2) + '\n', 'utf8')
writeFileSync(join(outRoot, 'dataforseo_ingest_validation.csv'), csv(rows, ['RawFile', 'SKU', 'ASIN', 'Reviews', 'OutputFile', 'Status']), 'utf8')
writeFileSync(join(outRoot, 'dataforseo_ingest_validation.md'), [
  '# DataForSEO Amazon reviews ingest validation',
  '',
  `Generated: ${generatedAt}`,
  '',
  `Ready: ${manifest.ready ? 'YES' : 'NO'}`,
  '',
  '## Counts',
  `- Raw files: ${manifest.counts.rawFiles}`,
  `- Converted Amazon source files: ${manifest.counts.convertedFiles}`,
  `- Converted reviews: ${manifest.counts.convertedReviews}`,
  `- Issues: ${manifest.counts.issues}`,
  '',
  '## How to use after the paid pilot',
  '1. Collect DataForSEO advanced task results from `/v3/merchant/amazon/reviews/task_get/advanced/{id}`.',
  '2. Save each raw response as JSON in `data/sources/dataforseo/raw/`.',
  '3. Run `bun run dataforseo:ingest`.',
  '4. Run `bun run validate:sources` to prove the normalized Amazon drops are usable by the existing source gate.',
  '',
  '## Issues',
  ...(allIssues.length ? allIssues.map(issue => `- ${issue}`) : ['- None']),
  ''
].join('\n'), 'utf8')

console.log(`DataForSEO ingest ready: ${manifest.ready}`)
console.log(`Raw files: ${manifest.counts.rawFiles}`)
console.log(`Converted files: ${manifest.counts.convertedFiles}`)
console.log(`Converted reviews: ${manifest.counts.convertedReviews}`)
console.log(`Issues: ${manifest.counts.issues}`)
if (rawFiles.length > 0 && !manifest.ready) process.exitCode = 1