Art-gallery/scripts/sync-image-paths.js

/**
 * Align painting image_path / thumbnail_path with files on disk and import missing rows.
 * Run: npm run dev:sync-image-paths
 * Flags: --dry-run (report only, no DB writes)
 */
require('dotenv').config();
const fs = require('fs');
const path = require('path');
const pool = require('../server/db');

const DRY_RUN = process.argv.includes('--dry-run');
const IMAGE_DIR = path.resolve(process.env.IMAGE_DIR || path.join(__dirname, '../data/images'));
const PAINTINGS_DIR = path.join(IMAGE_DIR, 'paintings');
const THUMBS_DIR = path.join(IMAGE_DIR, 'paintings', 'thumbs');

function safeArtistBase(name) {
  return name.replace(/[^a-zA-Z0-9_-]/g, '_');
}

function safePaintingBase(artistName, title) {
  return `${artistName}_${title}`.replace(/[^a-zA-Z0-9_-]/g, '_');
}

function normalizeTitle(s) {
  return s.toLowerCase().replace(/[^a-z0-9]+/g, ' ').trim();
}

function slugToTitle(slug) {
  return slug.replace(/_/g, ' ').replace(/\s+/g, ' ').trim();
}

function findPathsForSafeBase(safeBase) {
  let imagePath = null;
  let thumbPath = null;

  for (const ext of ['.jpg', '.jpeg', '.png', '.webp', '.JPG']) {
    const full = path.join(PAINTINGS_DIR, safeBase + ext);
    if (!imagePath && fs.existsSync(full)) {
      imagePath = `paintings/${safeBase}${ext}`.replace(/\\/g, '/');
    }
    const thumb = path.join(THUMBS_DIR, safeBase + '_thumb' + ext);
    if (!thumbPath && fs.existsSync(thumb)) {
      thumbPath = `paintings/thumbs/${safeBase}_thumb${ext}`.replace(/\\/g, '/');
    }
  }
  if (!thumbPath && imagePath) thumbPath = imagePath;
  return { imagePath, thumbPath };
}

function matchArtistFromFilename(filename, artistsByPrefix) {
  for (const { prefix, artist } of artistsByPrefix) {
    if (filename === prefix || filename.startsWith(prefix + '_')) {
      return { artist, prefix };
    }
  }
  return null;
}

async function main() {
  if (!fs.existsSync(PAINTINGS_DIR)) {
    console.error(`Paintings directory not found: ${PAINTINGS_DIR}`);
    process.exit(1);
  }

  const artistsRes = await pool.query('SELECT id, name FROM artists ORDER BY name');
  const artistsByPrefix = artistsRes.rows
    .map((a) => ({ prefix: safeArtistBase(a.name), artist: a }))
    .sort((a, b) => b.prefix.length - a.prefix.length);

  const paintingsRes = await pool.query(`
    SELECT p.id, p.title, p.image_path, p.thumbnail_path, p.artist_id, a.name AS artist_name
    FROM paintings p
    JOIN artists a ON a.id = p.artist_id
  `);

  const bySafeBase = new Map();
  const titlesByArtist = new Map();
  for (const row of paintingsRes.rows) {
    const base = safePaintingBase(row.artist_name, row.title);
    bySafeBase.set(base, row);
    if (!titlesByArtist.has(row.artist_id)) titlesByArtist.set(row.artist_id, new Set());
    titlesByArtist.get(row.artist_id).add(normalizeTitle(row.title));
  }

  const diskFiles = fs
    .readdirSync(PAINTINGS_DIR)
    .filter((f) => /\.(jpg|jpeg|png|webp)$/i.test(f) && !f.includes('_thumb'));

  let linked = 0;
  let imported = 0;
  let skipped = 0;
  let unmatched = 0;

  for (const file of diskFiles) {
    const ext = path.extname(file);
    const safeBase = file.slice(0, -ext.length);
    const paths = findPathsForSafeBase(safeBase);
    if (!paths.imagePath && !paths.thumbPath) continue;

    const existing = bySafeBase.get(safeBase);
    if (existing) {
      const needsUpdate =
        (paths.imagePath && existing.image_path !== paths.imagePath) ||
        (paths.thumbPath && existing.thumbnail_path !== paths.thumbPath);
      if (needsUpdate) {
        if (!DRY_RUN) {
          await pool.query(
            `UPDATE paintings SET image_path = COALESCE($1, image_path), thumbnail_path = COALESCE($2, thumbnail_path) WHERE id = $3`,
            [paths.imagePath, paths.thumbPath, existing.id]
          );
        }
        linked += 1;
      }
      continue;
    }

    const matched = matchArtistFromFilename(safeBase, artistsByPrefix);
    if (!matched) {
      unmatched += 1;
      continue;
    }

    const titleSlug = safeBase.slice(matched.prefix.length + 1);
    if (!titleSlug) {
      skipped += 1;
      continue;
    }

    const title = slugToTitle(titleSlug);
    const norm = normalizeTitle(title);
    const artistTitles = titlesByArtist.get(matched.artist.id) || new Set();
    if (artistTitles.has(norm)) {
      skipped += 1;
      continue;
    }

    const sortRes = await pool.query(
      'SELECT COALESCE(MAX(sort_order), 0) + 1 AS next FROM paintings WHERE artist_id = $1',
      [matched.artist.id]
    );
    const sortOrder = sortRes.rows[0].next;

    if (!DRY_RUN) {
      const insert = await pool.query(
        `INSERT INTO paintings (artist_id, title, wikipedia_title, image_path, thumbnail_path, sort_order)
         VALUES ($1, $2, $3, $4, $5, $6)
         RETURNING id`,
        [matched.artist.id, title, title, paths.imagePath, paths.thumbPath, sortOrder]
      );
      bySafeBase.set(safeBase, {
        id: insert.rows[0].id,
        artist_name: matched.artist.name,
        title,
        image_path: paths.imagePath,
        thumbnail_path: paths.thumbPath,
      });
    }
    artistTitles.add(norm);
    titlesByArtist.set(matched.artist.id, artistTitles);
    imported += 1;
  }

  // Link paths for existing rows whose files use the canonical safe base name
  for (const row of paintingsRes.rows) {
    if (row.image_path && row.thumbnail_path) continue;
    const synced = findPathsForSafeBase(safePaintingBase(row.artist_name, row.title));
    if (!synced.imagePath && !synced.thumbPath) continue;
    if (!DRY_RUN) {
      await pool.query(
        `UPDATE paintings SET image_path = COALESCE($1, image_path), thumbnail_path = COALESCE($2, thumbnail_path) WHERE id = $3`,
        [synced.imagePath, synced.thumbPath, row.id]
      );
    }
    linked += 1;
  }

  const summary = await pool.query(`
    SELECT COUNT(*)::int AS total_paintings,
           COUNT(DISTINCT artist_id)::int AS artists_with_works,
           ROUND(AVG(cnt), 1) AS avg_per_artist,
           MAX(cnt)::int AS max_per_artist
    FROM (
      SELECT p.artist_id, COUNT(p.id)::int AS cnt
      FROM paintings p
      GROUP BY p.artist_id
    ) t
  `);

  console.log(
    `${DRY_RUN ? '[dry-run] ' : ''}Linked ${linked}, imported ${imported}, skipped ${skipped}, unmatched files ${unmatched}`
  );
  console.log('Catalog:', summary.rows[0]);

  await pool.end();
}

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