`scrapers/import-film-pieces-seed.js`

151 lines
#!/usr/bin/env node
'use strict';
// Tick 7 — import thesetdecorator/data/film-pieces-seed.json into
// asim_spotted. 858 rows of curated SPOTTED entries (props, dressing,
// artwork) tied to films by imdb_id. Each links to a decorator + often
// the production designer + director.
//
// This is the feature Steve specifically named — "Add pages for actual
// products in movies under SPOTTED." Loading these gives us a real
// non-empty /api/spotted feed on day one.

require('dotenv').config();
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');

const SEED = path.join(process.env.HOME, 'Projects', 'thesetdecorator', 'data', 'film-pieces-seed.json');
const pool = new Pool({
  connectionString: process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp&user=stevestudio2',
  max: 4,
});

// Heuristic category labels from piece_description. Lets the SPOTTED feed
// support /api/spotted?category=art etc. — refined later by hand or LLM.
function categorize(desc) {
  const s = (desc || '').toLowerCase();
  if (/\b(wallpaper|wallcover|wall covering)\b/.test(s)) return 'wallcovering';
  if (/\b(rug|carpet|kilim)\b/.test(s)) return 'rug';
  if (/\b(art|painting|drawing|sketch|sculpture|portrait)\b/.test(s)) return 'art';
  if (/\b(sofa|chair|table|desk|bed|dresser|cabinet|nightstand|stool|bench|wardrobe)\b/.test(s)) return 'furniture';
  if (/\b(lamp|lighting|sconce|chandelier|pendant)\b/.test(s)) return 'lighting';
  if (/\b(curtain|drape|drapery|blind|shade)\b/.test(s)) return 'window-treatment';
  if (/\b(book|library|shelf)\b/.test(s)) return 'books';
  if (/\b(plant|flower|botanical|orchid)\b/.test(s)) return 'plants';
  if (/\b(prop|gun|sword|coin|jewel)\b/.test(s)) return 'prop';
  if (/\b(dish|plate|cup|tableware|glass|silver)\b/.test(s)) return 'tableware';
  if (/\b(set|stage|soundstage)\b/.test(s)) return 'set';
  return 'dressing';
}

async function findOrStubMovie(imdb_id, title, year) {
  if (!imdb_id) return null;
  const sel = await pool.query(`SELECT id FROM asim_movies WHERE imdb_id=$1 LIMIT 1`, [imdb_id]);
  if (sel.rows[0]) return sel.rows[0].id;
  const ins = await pool.query(`
    INSERT INTO asim_movies (imdb_id, title, release_year, status, poster_source)
    VALUES ($1, $2, $3, 'imdb-stub', 'made-with-ai')
    RETURNING id`, [imdb_id, title || imdb_id, year || null]);
  return ins.rows[0].id;
}

async function ensurePersonByName(name, profession, union) {
  if (!name) return null;
  // Match on full_name (case-insensitive). Could be ambiguous, but the
  // thesetdecorator seed is human-curated so first hit is usually right.
  const sel = await pool.query(
    `SELECT id FROM asim_people WHERE LOWER(full_name) = LOWER($1) ORDER BY id LIMIT 1`,
    [name]
  );
  if (sel.rows[0]) return sel.rows[0].id;
  const ins = await pool.query(`
    INSERT INTO asim_people (full_name, primary_profession, union_memberships, headshot_source, headshot_url)
    VALUES ($1, $2, $3, 'made-with-ai', NULL)
    RETURNING id`, [name, profession ? [profession] : [], union ? [union] : []]);
  return ins.rows[0].id;
}

async function ensureCredit(personId, movieId, role, department) {
  if (!personId || !movieId) return;
  await pool.query(`
    INSERT INTO asim_credits (person_id, movie_id, role, department, source)
    VALUES ($1, $2, $3, $4, 'thesetdecorator-film-pieces-seed')
    ON CONFLICT DO NOTHING`, [personId, movieId, role, department]);
}

async function main() {
  if (!fs.existsSync(SEED)) {
    console.error('[seed] not found:', SEED);
    process.exit(1);
  }
  const data = JSON.parse(fs.readFileSync(SEED, 'utf8'));
  const rows = Array.isArray(data) ? data : (data.pieces || []);
  console.log(`[seed] ${rows.length} film-pieces in JSON`);

  const runR = await pool.query(
    `INSERT INTO asim_ingest_runs (run_kind, source, notes) VALUES ('thesetdecorator-film-pieces-seed','thesetdecorator-film-pieces',$1) RETURNING id`,
    [`file=${SEED}; rows=${rows.length}`]
  );
  const runId = runR.rows[0].id;

  let nSpotted = 0;
  let nMovies = 0;
  let nCredits = 0;
  let nSkipped = 0;
  const seenMovies = new Set();

  for (const r of rows) {
    if (!r.imdb_id || !r.piece_description) { nSkipped++; continue; }
    const movieId = await findOrStubMovie(r.imdb_id, r.film_title, r.film_year);
    if (!movieId) { nSkipped++; continue; }
    if (!seenMovies.has(movieId)) { seenMovies.add(movieId); nMovies++; }

    // Build the people side first — decorator, production designer, director.
    if (r.decorator) {
      const pid = await ensurePersonByName(r.decorator, 'set_decorator', 'iatse-44');
      if (pid) { await ensureCredit(pid, movieId, 'set_decorator', 'Art'); nCredits++; }
    }
    if (r.production_designer) {
      const pid = await ensurePersonByName(r.production_designer, 'production_designer', 'iatse-800');
      if (pid) { await ensureCredit(pid, movieId, 'production_designer', 'Art'); nCredits++; }
    }
    if (r.director) {
      const pid = await ensurePersonByName(r.director, 'director', 'dga');
      if (pid) { await ensureCredit(pid, movieId, 'director', 'Directing'); nCredits++; }
    }

    // Then the SPOTTED row itself.
    const productName = (r.piece_description || '').split('—')[0].trim().slice(0, 200);
    const category = categorize(r.piece_description);
    await pool.query(`
      INSERT INTO asim_spotted (
        movie_id, product_name, product_category, scene_description,
        shop_url, thesetdecorator_id, image_source,
        submitted_by_email, submitted_by_role, verified_at, votes_up, votes_down,
        is_premium_pick
      ) VALUES (
        $1, $2, $3, $4, $5, $6, 'made-with-ai',
        NULL, 'set_decorator', NOW(), 0, 0, $7
      )`, [
        movieId,
        productName || 'Untitled scene element',
        category,
        r.piece_description,
        r.source_url || null,
        r.id || null,
        r.confidence === 'documented',
      ]).catch(() => { /* tolerant — seed has occasional duplicates */ });
    nSpotted++;
  }

  await pool.query(
    `UPDATE asim_ingest_runs SET finished_at=NOW(), status='done', records_in=$1, records_out=$2, notes=$3 WHERE id=$4`,
    [rows.length, nSpotted, `spotted=${nSpotted} new_movies=${nMovies} credits=${nCredits} skipped=${nSkipped}`, runId]
  );

  console.log(`[seed] DONE — ${nSpotted} SPOTTED rows, ${nMovies} new movie stubs, ${nCredits} credits, ${nSkipped} skipped`);
  await pool.end();
}

main().catch(e => { console.error('fatal:', e); process.exit(1); });