Art-gallery/scripts/import-painter-palette.js

/**
 * Integrate Inputs/PainterPalette.csv into existing gallery artists:
 * - Enrich artists.palette_metadata (+ fill missing birth/death years)
 * - Insert artist/movement influence sources from Influencedby, Teachers, Influencedon, Pupils
 *
 * Usage:
 *   npm run dev:migrate:artist-palette
 *   npm run dev:import-painter-palette              # metadata + influences
 *   npm run dev:import-painter-palette -- --dry-run
 *   npm run dev:import-painter-palette -- --metadata-only
 *   npm run dev:import-painter-palette -- --influences-only
 */
require('dotenv').config();
const pool = require('../server/db');
const {
  loadPainterPalette,
  findPaletteRow,
  buildArtistResolver,
  collectInfluenceSources,
} = require('./painter-palette-lib');
const { loadMovements, resolveAndInsertSource } = require('./influence-resolver');

const DRY_RUN = process.argv.includes('--dry-run');
const METADATA_ONLY = process.argv.includes('--metadata-only');
const INFLUENCES_ONLY = process.argv.includes('--influences-only');

const PALETTE_EDGE = {
  notes: 'Artist relationship from PainterPalette dataset (WikiArt / Art500k / Wikidata enrichment).',
  source: 'PainterPalette.csv',
  source_author: 'PainterPalette',
  confidence: 'discovered',
  discovered_via: 'painter-palette',
};

function roleNote(role) {
  if (role === 'teacher') return 'Listed as teacher in PainterPalette.';
  if (role === 'pupil') return 'Listed as pupil in PainterPalette.';
  if (role === 'influenced_on') return 'Listed as influenced by this artist in PainterPalette.';
  return null;
}

async function main() {
  const tableCheck = await pool.query(`
    SELECT EXISTS (
      SELECT FROM information_schema.columns
      WHERE table_name = 'artists' AND column_name = 'palette_metadata'
    ) AS ok
  `);
  if (!tableCheck.rows[0]?.ok) {
    console.error('Run npm run dev:migrate:artist-palette first.');
    process.exit(1);
  }

  const palette = loadPainterPalette();
  console.log(`Loaded PainterPalette: ${palette.rowCount} painters`);

  const { rows: dbArtists } = await pool.query(`
    SELECT a.id, a.name, a.birth_year, a.death_year
    FROM artists a
    ORDER BY a.name
  `);

  const { rows: paintings } = await pool.query(`
    SELECT p.id, p.year, p.artist_id, a.name AS artist_name
    FROM paintings p
    JOIN artists a ON a.id = p.artist_id
    ORDER BY a.name, p.year NULLS LAST, p.id
  `);

  const paintingsByArtist = new Map();
  for (const p of paintings) {
    if (!paintingsByArtist.has(p.artist_id)) paintingsByArtist.set(p.artist_id, []);
    paintingsByArtist.get(p.artist_id).push(p);
  }

  const resolver = buildArtistResolver(dbArtists);
  const movementsByName = await loadMovements(pool);

  const stats = {
    matched: 0,
    metadataUpdated: 0,
    yearsFilled: 0,
    influencesAdded: 0,
    influencesSkipped: 0,
    influencesFailed: 0,
    unresolved: new Set(),
  };

  const matchedPairs = [];
  for (const artist of dbArtists) {
    const row = findPaletteRow(artist.name, palette);
    if (row) {
      stats.matched += 1;
      matchedPairs.push({ artist, row });
    }
  }
  console.log(`Matched ${stats.matched} / ${dbArtists.length} gallery artists to PainterPalette`);

  if (!INFLUENCES_ONLY) {
    for (const { artist, row } of matchedPairs) {
      const meta = row.metadata;
      const updates = [];
      const params = [];
      let n = 1;

      params.push(JSON.stringify(meta));
      updates.push(`palette_metadata = $${n++}`);

      if (!artist.birth_year && meta.birth_year) {
        updates.push(`birth_year = $${n++}`);
        params.push(Math.round(meta.birth_year));
        stats.yearsFilled += 1;
      }
      if (!artist.death_year && meta.death_year) {
        updates.push(`death_year = $${n++}`);
        params.push(Math.round(meta.death_year));
        stats.yearsFilled += 1;
      }

      params.push(artist.id);
      const sql = `UPDATE artists SET ${updates.join(', ')} WHERE id = $${n}`;
      if (DRY_RUN) {
        console.log(`[dry-run] metadata ${artist.name} ← ${row.artist}`);
      } else {
        await pool.query(sql, params);
        stats.metadataUpdated += 1;
      }
    }
  }

  if (!METADATA_ONLY) {
    async function applySourceToPaintings(paintingList, sourceRef, edge, subjectArtistId) {
      if (!paintingList?.length) return;
      for (const p of paintingList) {
        if (DRY_RUN) {
          stats.influencesAdded += 1;
          continue;
        }
        const result = await resolveAndInsertSource(
          pool,
          { id: p.id, year: p.year, artist_id: subjectArtistId },
          sourceRef,
          edge,
          movementsByName,
          false,
          null,
          null
        );
        if (result.inserted) stats.influencesAdded += 1;
        else if (result.ok) stats.influencesSkipped += 1;
        else if (result.reason !== 'self' && result.reason !== 'self artist') {
          stats.influencesFailed += 1;
        } else stats.influencesSkipped += 1;
      }
    }

    for (const { artist, row } of matchedPairs) {
      const subjectPaintings = paintingsByArtist.get(artist.id) || [];
      if (!subjectPaintings.length) continue;

      const sources = collectInfluenceSources(row);
      for (const { raw, role } of sources) {
        const resolved = resolver.resolve(raw);
        if (!resolved) {
          if (!isNoise(raw)) stats.unresolved.add(raw);
          continue;
        }

        const extraNote = roleNote(role);
        const edge = { ...PALETTE_EDGE, notes: extraNote ? `${PALETTE_EDGE.notes} ${extraNote}` : PALETTE_EDGE.notes };

        if (role === 'influenced_by' || role === 'teacher') {
          if (resolved.type === 'artist') {
            await applySourceToPaintings(
              subjectPaintings,
              { type: 'artist', artist: resolved.artist },
              edge,
              artist.id
            );
          } else if (resolved.type === 'movement') {
            await applySourceToPaintings(
              subjectPaintings,
              { type: 'movement', movement: resolved.movement },
              edge,
              artist.id
            );
          }
        } else if (role === 'influenced_on' || role === 'pupil') {
          if (resolved.type !== 'artist') continue;
          const targetArtist = dbArtists.find((a) => a.id === resolved.id);
          if (!targetArtist) continue;
          const targetPaintings = paintingsByArtist.get(resolved.id) || [];
          const reverseEdge = {
            ...edge,
            notes: `${PALETTE_EDGE.notes} Reverse link: ${artist.name} listed as influence on ${resolved.artist}.`,
          };
          await applySourceToPaintings(
            targetPaintings,
            { type: 'artist', artist: artist.name },
            reverseEdge,
            resolved.id
          );
        }
      }
    }
  }

  const { rows: totalSources } = await pool.query(
    'SELECT COUNT(*)::int AS n FROM painting_influence_sources WHERE discovered_via = $1',
    ['painter-palette']
  );

  console.log('\n--- Summary ---');
  if (!INFLUENCES_ONLY) {
    console.log(`Metadata updated: ${stats.metadataUpdated}${DRY_RUN ? ' (dry-run)' : ''}`);
    console.log(`Birth/death years filled: ${stats.yearsFilled}`);
  }
  if (!METADATA_ONLY) {
    console.log(
      `Influence rows attempted: ${stats.influencesAdded} inserted, ${stats.influencesSkipped} skipped, ${stats.influencesFailed} failed${DRY_RUN ? ' (dry-run counts inserts as attempts)' : ''}`
    );
    console.log(`Unresolved influence names (sample): ${[...stats.unresolved].slice(0, 15).join('; ')}`);
  }
  console.log(`Total painter-palette influence sources in DB: ${totalSources[0].n}`);

  await pool.end();
}

function isNoise(name) {
  const { isNoiseInfluenceName } = require('./painter-palette-lib');
  return isNoiseInfluenceName(name);
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});