justvitamin/scripts/refresh-dataforseo-live-asin-evidence.ts

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

const root = process.cwd()
const outRoot = join(root, 'content_population_exports')
const dataforseoRoot = join(root, 'data', 'sources', 'dataforseo')
const productsRawRoot = join(dataforseoRoot, 'products_raw')
const asinMapPath = join(dataforseoRoot, 'asin-map.csv')
const pilotTargetsPath = join(outRoot, 'dataforseo_pilot_targets.csv')
const payloadPath = join(outRoot, 'dataforseo_amazon_reviews_task_payload.json')
const defaultEnvPath = 'C:\\Users\\uldvs\\OneDrive\\Desktop\\work\\pi-fresh-foundation\\.env'

mkdirSync(outRoot, { recursive: true })
mkdirSync(dataforseoRoot, { recursive: true })
mkdirSync(productsRawRoot, { recursive: true })

function loadEnvFile(path: string) {
  if (!existsSync(path)) return false
  for (const line of readFileSync(path, 'utf8').split(/\r?\n/)) {
    const match = line.match(/^\s*(DATAFORSEO_[A-Za-z0-9_]+)\s*=\s*(.*)\s*$/)
    if (match) process.env[match[1]] = match[2].trim().replace(/^['"]|['"]$/g, '')
  }
  return 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 = ''
    value = Array.isArray(value) ? value.join(' | ') : 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 i = 0; i < text.length; i += 1) {
    const ch = text[i]
    const next = text[i + 1]
    if (quoted) {
      if (ch === '"' && next === '"') { cell += '"'; i += 1 }
      else if (ch === '"') quoted = false
      else cell += ch
    } else if (ch === '"') quoted = true
    else if (ch === ',') { row.push(cell); cell = '' }
    else if (ch === '\n') { row.push(cell); rows.push(row); row = []; cell = '' }
    else if (ch !== '\r') cell += ch
  }
  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 tokenise(value: string) {
  return String(value || '').toLowerCase().replace(/[^a-z0-9]+/g, ' ').split(/\s+/).filter(token => token.length >= 3 && !['with', 'and', 'the', 'for', 'high', 'made', 'strength', 'vitamins', 'vitamin', 'supplement', 'capsules', 'tablets'].includes(token))
}
function asinFromUrl(url: string) {
  const decoded = decodeURIComponent(String(url || ''))
  return decoded.match(/\/dp\/([A-Z0-9]{10})/i)?.[1]?.toUpperCase() || ''
}
function productItemsFromRaw() {
  const candidates: any[] = []
  for (const entry of readdirSync(productsRawRoot, { withFileTypes: true })) {
    if (!entry.isFile() || !entry.name.startsWith('products-task-get-') || !entry.name.endsWith('.json')) continue
    const file = join(productsRawRoot, entry.name)
    const raw = readJson(file, {})
    for (const task of raw.tasks || []) {
      const tag = String(task?.data?.tag || '')
      const sku = tag.match(/jv:([^:]+):/i)?.[1] || ''
      const keyword = task?.data?.keyword || ''
      for (const result of task.result || []) {
        for (const item of result.items || []) {
          const url = decodeURIComponent(String(item.url || ''))
          const asin = item.asin || asinFromUrl(url)
          if (!sku || !asin) continue
          candidates.push({
            SKU: sku,
            Tag: tag,
            Keyword: keyword,
            Rank: Number(item.rank_absolute || item.rank || 0),
            ASIN: asin,
            Title: item.title || '',
            Url: url,
            Rating: item.rating?.value || '',
            Votes: item.rating?.votes_count || '',
            RawFile: file
          })
        }
      }
    }
  }
  return candidates
}
function scoreCandidate(candidate: any, target: any) {
  const title = String(candidate.Title || '')
  const url = String(candidate.Url || '')
  const titleLower = title.toLowerCase()
  const brandExact = /just\s*vitamins/i.test(title) || /just-vitamins/i.test(url)
  const targetTokens = tokenise(`${target.Product || ''} ${target.SKU || ''}`)
  const matchedTokens = targetTokens.filter(token => titleLower.includes(token.toLowerCase()))
  const specialMatch =
    (target.SKU === 'JV-VISISOFT' && /visisoft|lutein|zeaxanthin|bilberry/i.test(title)) ||
    (target.SKU === 'JV-COQ10100' && /coq10|co[- ]?enzyme|q10/i.test(title)) ||
    (target.SKU === 'JV-D4000' && /4000\s?i?u|100\s?µg|100\s?mcg/i.test(title)) ||
    (target.SKU === 'JV-TURMERIC500' && /turmeric|bioperine|curcuminoids/i.test(title)) ||
    (target.SKU === 'JV-STAR1000' && /starflower|borage|gla/i.test(title)) ||
    (target.SKU === 'JV-B12' && /b12|methylcobalamin|sublingual/i.test(title))
  const score = (brandExact ? 100 : 0) + matchedTokens.length * 12 + (specialMatch ? 40 : 0) - Math.max(0, Number(candidate.Rank || 0) - 1)
  const exact = brandExact && (matchedTokens.length > 0 || specialMatch) && score >= 110
  return { score, brandExact, matchedTokens, exact }
}
function rebuildAsinMapFromEvidence() {
  const targets = existsSync(pilotTargetsPath) ? parseCsv(readFileSync(pilotTargetsPath, 'utf8')) : []
  const existing = existsSync(asinMapPath) ? parseCsv(readFileSync(asinMapPath, 'utf8')) : targets
  const candidates = productItemsFromRaw()
  const enriched = candidates.map(candidate => {
    const target = targets.find(row => row.SKU === candidate.SKU) || existing.find(row => row.SKU === candidate.SKU) || {}
    const scored = scoreCandidate(candidate, target)
    return { ...candidate, CandidateScore: scored.score, BrandExact: scored.brandExact ? 'yes' : 'no', MatchedTokens: scored.matchedTokens.join(' | '), ExactMatch: scored.exact ? 'yes' : 'no' }
  }).sort((a, b) => String(a.SKU).localeCompare(String(b.SKU)) || Number(b.CandidateScore) - Number(a.CandidateScore) || Number(a.Rank) - Number(b.Rank))

  const rows = (targets.length ? targets : existing).map(target => {
    const current = existing.find(row => row.SKU === target.SKU) || {}
    const best = enriched.find(row => row.SKU === target.SKU && row.ExactMatch === 'yes')
    const asin = best?.ASIN || current.ASIN || ''
    const productUrl = best?.ASIN ? `https://www.amazon.co.uk/dp/${best.ASIN}` : current.ProductUrl || ''
    return {
      SKU: target.SKU,
      Product: target.Product,
      Priority: target.Priority,
      Provider: 'DataForSEO',
      AmazonDomain: current.AmazonDomain || target.AmazonDomain || 'amazon.co.uk',
      ASIN: asin,
      ProductUrl: productUrl,
      Depth: current.Depth || target.Depth || '10',
      LocationName: current.LocationName || target.LocationName || 'United Kingdom',
      LanguageName: current.LanguageName || target.LanguageName || 'English (United Kingdom)',
      Status: asin ? 'ready_for_task_payload' : 'needs_asin'
    }
  })
  writeFileSync(asinMapPath, csv(rows, ['SKU', 'Product', 'Priority', 'Provider', 'AmazonDomain', 'ASIN', 'ProductUrl', 'Depth', 'LocationName', 'LanguageName', 'Status']), 'utf8')
  writeFileSync(join(productsRawRoot, 'all-asin-candidates-from-dataforseo.csv'), csv(enriched, ['SKU', 'Tag', 'Keyword', 'Rank', 'ASIN', 'CandidateScore', 'BrandExact', 'MatchedTokens', 'ExactMatch', 'Title', 'Url', 'Rating', 'Votes', 'RawFile']), 'utf8')
  return { rows, candidates: enriched }
}
function writePayload(rows: any[]) {
  const taskPayload = rows.filter(row => row.ASIN).map(row => ({
    location_name: row.LocationName,
    language_name: row.LanguageName,
    asin: row.ASIN,
    depth: Number(row.Depth) || 10,
    tag: `jv:${row.SKU}:stage1`
  }))
  writeFileSync(payloadPath, JSON.stringify(taskPayload, null, 2) + '\n', 'utf8')
  return taskPayload
}
async function accountCheck() {
  const envFile = process.env.DATAFORSEO_ENV_FILE || defaultEnvPath
  const envLoaded = loadEnvFile(envFile)
  const login = process.env.DATAFORSEO_LOGIN
  const password = process.env.DATAFORSEO_PASSWORD
  if (!login || !password) return { envLoaded, status: 'missing_credentials' }
  const auth = Buffer.from(`${login}:${password}`).toString('base64')
  const response = await fetch('https://api.dataforseo.com/v3/appendix/user_data', { headers: { Authorization: `Basic ${auth}` } })
  const body: any = await response.json()
  return { envLoaded, status: body.status_message || response.statusText, status_code: body.status_code, httpStatus: response.status }
}

const generatedAt = new Date().toISOString()
const connection = await accountCheck()
const { rows, candidates } = rebuildAsinMapFromEvidence()
const payload = writePayload(rows)
const exact = rows.filter(row => row.ASIN)
const missing = rows.filter(row => !row.ASIN)
const latestReviewPost = readdirSync(join(dataforseoRoot, 'raw'), { withFileTypes: true }).filter(entry => entry.isFile() && entry.name.includes('reviews-task-post')).map(entry => readJson(join(dataforseoRoot, 'raw', entry.name), {})).find(raw => raw?.status_code)

const manifest = {
  generatedAt,
  ready: connection.status_code === 20000 && payload.length > 0,
  credentialSource: process.env.DATAFORSEO_ENV_FILE || defaultEnvPath,
  credentialPrinted: false,
  accountConnection: connection,
  counts: {
    targets: rows.length,
    exactAsinsConfirmed: exact.length,
    missingAsins: missing.length,
    candidates: candidates.length,
    taskPayloadRows: payload.length
  },
  files: {
    asinMap: 'data/sources/dataforseo/asin-map.csv',
    productRawDir: 'data/sources/dataforseo/products_raw',
    candidateCsv: 'data/sources/dataforseo/products_raw/all-asin-candidates-from-dataforseo.csv',
    taskPayload: 'content_population_exports/dataforseo_amazon_reviews_task_payload.json',
    reportMd: 'content_population_exports/dataforseo_live_connection.md'
  },
  reviewEndpointStatus: latestReviewPost ? { status_code: latestReviewPost.status_code, status_message: latestReviewPost.status_message } : null,
  confirmedRows: rows.map(row => ({ SKU: row.SKU, Product: row.Product, ASIN: row.ASIN, ProductUrl: row.ProductUrl, Status: row.Status })),
  blocker: latestReviewPost?.status_code === 50304 ? 'DataForSEO Amazon Reviews task_post returned 50304 temporarily unavailable; no review task IDs were created.' : (payload.length ? 'Review task creation still requires local budget/spend gates before paid use.' : 'ASIN payload is empty.')
}
writeFileSync(join(outRoot, 'dataforseo_live_connection.json'), JSON.stringify(manifest, null, 2) + '\n', 'utf8')
writeFileSync(join(outRoot, 'dataforseo_live_connection.md'), [
  '# DataForSEO live connection evidence',
  '',
  `Generated: ${generatedAt}`,
  '',
  '## What was verified',
  `- Loaded DataForSEO credentials from \`${manifest.credentialSource}\` without printing secrets.`,
  `- Account check: \`${connection.status_code || 'n/a'} ${connection.status || ''}\`.`,
  '- Parsed live Amazon Products task results saved under `data/sources/dataforseo/products_raw/`.',
  '- Updated `data/sources/dataforseo/asin-map.csv` and regenerated `content_population_exports/dataforseo_amazon_reviews_task_payload.json`.',
  '',
  '## ASIN result',
  '| SKU | ASIN | Status |',
  '|---|---:|---|',
  ...rows.map(row => `| ${row.SKU} | ${row.ASIN || ''} | ${row.Status} |`),
  '',
  '## Current blocker',
  `- ${manifest.blocker}`,
  '',
  '## Counts',
  `- Exact ASINs confirmed from DataForSEO product results: ${manifest.counts.exactAsinsConfirmed}/${manifest.counts.targets}`,
  `- Review task payload rows: ${manifest.counts.taskPayloadRows}`,
  `- Missing exact ASINs: ${manifest.counts.missingAsins}`,
  ''
].join('\n'), 'utf8')

console.log(`DataForSEO live evidence ready: ${manifest.ready}`)
console.log(`Account: ${connection.status_code || 'n/a'} ${connection.status || ''}`)
console.log(`Exact ASINs: ${manifest.counts.exactAsinsConfirmed}/${manifest.counts.targets}`)
console.log(`Task payload rows: ${manifest.counts.taskPayloadRows}`)
console.log(`Missing ASINs: ${manifest.counts.missingAsins}`)