deckhearth/scripts/bulk-import-scryfall.js

/**
 * Bulk import MTG cards from Scryfall's Oracle Cards bulk data file.
 *
 * Downloads the ~168 MB Oracle Cards JSON (one entry per unique card/oracle_id),
 * then upserts every card into the `cards` table keyed on `scryfall_id`.
 *
 * Populates all new bulk-data columns (oracle_id, illustration_id, color_identity,
 * keywords, legalities, flavor_text, artist, released_at, layout, edhrec_rank,
 * reserved, reprint, finishes) in addition to the original columns.
 *
 * Usage:
 *   POSTGRES_URL=<url> node scripts/bulk-import-scryfall.js
 *
 * Options (env vars):
 *   BULK_TYPE        — "oracle_cards" (default) | "default_cards" | "unique_artwork"
 *   BATCH_SIZE       — rows per INSERT batch (default 100)
 *   DRY_RUN          — "true" to fetch and count without writing to DB
 */

import { neon } from '@neondatabase/serverless';

if (!process.env.POSTGRES_URL) {
  console.error('POSTGRES_URL is required');
  process.exit(1);
}

const sql = neon(process.env.POSTGRES_URL, { fullResults: false });

const BULK_TYPE = process.env.BULK_TYPE || 'oracle_cards';
const BATCH_SIZE = parseInt(process.env.BATCH_SIZE || '100', 10);
const DRY_RUN = process.env.DRY_RUN === 'true';

async function fetchBulkDownloadUrl(type) {
  const response = await fetch(`https://api.scryfall.com/bulk-data/${type}`, {
    headers: { 'User-Agent': 'DeckHearth/1.0', Accept: 'application/json' },
  });
  if (!response.ok) {
    throw new Error(`Failed to fetch bulk-data metadata: ${response.status}`);
  }
  const data = await response.json();
  console.log(`[bulk-import] File: ${data.name}`);
  console.log(`[bulk-import] Updated: ${data.updated_at}`);
  console.log(`[bulk-import] Size: ${(data.size / 1024 / 1024).toFixed(1)} MB`);
  return data.download_uri;
}

function mapCard(card) {
  let currentPrice = null;
  let marketPrice = null;
  if (card.prices) {
    currentPrice = card.prices.usd ? parseFloat(card.prices.usd) : null;
    marketPrice = card.prices.usd_foil ? parseFloat(card.prices.usd_foil) : null;
  }

  return {
    scryfallId: card.id,
    oracleId: card.oracle_id || null,
    illustrationId: card.illustration_id || null,
    name: card.name,
    setName: card.set_name,
    setCode: card.set,
    cardNumber: card.collector_number,
    rarity: card.rarity,
    manaCost: card.mana_cost || null,
    cmc: card.cmc != null ? Math.round(card.cmc) : null,
    cardType: card.type_line || null,
    colors: JSON.stringify(card.colors || []),
    colorIdentity: JSON.stringify(card.color_identity || []),
    oracleText: card.oracle_text || null,
    power: card.power || null,
    toughness: card.toughness || null,
    imageUrl: card.image_uris?.normal || null,
    stockImageUrl: card.image_uris?.art_crop || null,
    currentPrice,
    marketPrice,
    keywords: JSON.stringify(card.keywords || []),
    legalities: JSON.stringify(card.legalities || {}),
    flavorText: card.flavor_text || null,
    artist: card.artist || null,
    releasedAt: card.released_at || null,
    layout: card.layout || null,
    edhrecRank: card.edhrec_rank || null,
    reserved: card.reserved || false,
    reprint: card.reprint || false,
    finishes: JSON.stringify(card.finishes || []),
  };
}

function buildUpsertQuery(batch) {
  const columns = [
    'scryfall_id', 'oracle_id', 'illustration_id', 'name', 'set_name', 'set_code',
    'card_number', 'rarity', 'game', 'mana_cost', 'cmc', 'card_type', 'colors',
    'color_identity', 'oracle_text', 'power', 'toughness', 'image_url', 'stock_image_url',
    'current_price', 'market_price', 'keywords', 'legalities', 'flavor_text', 'artist',
    'released_at', 'layout', 'edhrec_rank', 'reserved', 'reprint', 'finishes', 'verified',
  ];

  const placeholders = [];
  const values = [];
  let paramIdx = 1;

  for (const row of batch) {
    const rowPlaceholders = [];
    const rowValues = [
      row.scryfallId, row.oracleId, row.illustrationId, row.name, row.setName, row.setCode,
      row.cardNumber, row.rarity, 'MTG', row.manaCost, row.cmc, row.cardType, row.colors,
      row.colorIdentity, row.oracleText, row.power, row.toughness, row.imageUrl, row.stockImageUrl,
      row.currentPrice, row.marketPrice, row.keywords, row.legalities, row.flavorText, row.artist,
      row.releasedAt, row.layout, row.edhrecRank, row.reserved, row.reprint, row.finishes, true,
    ];

    for (let i = 0; i < rowValues.length; i++) {
      rowPlaceholders.push(`$${paramIdx}`);
      paramIdx += 1;
    }
    placeholders.push(`(${rowPlaceholders.join(', ')})`);
    values.push(...rowValues);
  }

  const updateCols = columns
    .filter((c) => c !== 'scryfall_id')
    .map((c) => `${c} = EXCLUDED.${c}`)
    .join(', ');

  const query = `
    INSERT INTO cards (${columns.join(', ')})
    VALUES ${placeholders.join(',\n')}
    ON CONFLICT (scryfall_id) DO UPDATE SET
      ${updateCols},
      updated_at = CURRENT_TIMESTAMP
  `;

  return { query, values };
}

async function processBatch(batch, stats) {
  if (DRY_RUN) {
    stats.skipped += batch.length;
    return;
  }

  const { query, values } = buildUpsertQuery(batch);
  try {
    await sql.query(query, values);
    stats.upserted += batch.length;
  } catch (error) {
    console.error(`[bulk-import] Batch failed (${batch.length} rows):`, error.message);
    for (const row of batch) {
      try {
        const { query: singleQ, values: singleV } = buildUpsertQuery([row]);
        await sql.query(singleQ, singleV);
        stats.upserted += 1;
      } catch (singleErr) {
        console.error(`[bulk-import] Failed: ${row.name} (${row.scryfallId}):`, singleErr.message);
        stats.errors += 1;
      }
    }
  }
}

async function run() {
  console.log(`[bulk-import] Starting Scryfall bulk import (type=${BULK_TYPE}, batch=${BATCH_SIZE}, dryRun=${DRY_RUN})`);

  const downloadUrl = await fetchBulkDownloadUrl(BULK_TYPE);
  console.log(`[bulk-import] Downloading...`);

  const response = await fetch(downloadUrl, {
    headers: { 'User-Agent': 'DeckHearth/1.0' },
  });

  if (!response.ok) {
    throw new Error(`Download failed: ${response.status}`);
  }

  const text = await response.text();
  console.log(`[bulk-import] Downloaded ${(text.length / 1024 / 1024).toFixed(1)} MB, parsing...`);

  const cards = JSON.parse(text);
  console.log(`[bulk-import] Total cards in bulk file: ${cards.length}`);

  const mtgCards = cards.filter((c) => c.lang === 'en' && !c.digital);
  console.log(`[bulk-import] MTG cards (English, paper): ${mtgCards.length}`);

  const stats = { upserted: 0, skipped: 0, errors: 0 };
  let batch = [];
  let processed = 0;

  for (const card of mtgCards) {
    batch.push(mapCard(card));

    if (batch.length >= BATCH_SIZE) {
      await processBatch(batch, stats);
      processed += batch.length;
      batch = [];

      if (processed % 5000 === 0) {
        console.log(`[bulk-import] Progress: ${processed}/${mtgCards.length} (${((processed / mtgCards.length) * 100).toFixed(1)}%)`);
      }
    }
  }

  if (batch.length > 0) {
    await processBatch(batch, stats);
    processed += batch.length;
  }

  console.log(`[bulk-import] Complete!`);
  console.log(`[bulk-import] Upserted: ${stats.upserted}`);
  console.log(`[bulk-import] Skipped (dry run): ${stats.skipped}`);
  console.log(`[bulk-import] Errors: ${stats.errors}`);
  console.log(`[bulk-import] Total processed: ${processed}`);
}

run().catch((err) => {
  console.error('[bulk-import] Fatal error:', err);
  process.exit(1);
});