Art-gallery/server/influence-import-service.js

/**
 * Parse CSV/JSON/XLSX influence files, map columns, resolve entities, expand
 * artist-level rows to all paintings, preview and commit into painting_influence_sources.
 */
const crypto = require('crypto');
const XLSX = require('xlsx');
const { parse: parseCsv } = require('csv-parse/sync');
const pool = require('./db');
const { normalizeArtist, loadMovements } = require('../scripts/influence-resolver');

function hashBuffer(buffer) {
  return crypto.createHash('sha256').update(buffer).digest('hex');
}

/** Stable fingerprint of row content (ignores filename / column order noise). */
function hashImportPayload(rows, mapping) {
  const normalized = (rows || []).map((row) => {
    const mapped = applyMapping(row, mapping || {});
    return [
      mapped.subject_artist,
      mapped.subject_painting,
      mapped.influenced_by,
      mapped.influenced,
      mapped.notes,
      mapped.reference,
      mapped.source_url,
    ].map((v) => String(v || '').trim().toLowerCase().replace(/\s+/g, ' '));
  });
  const canonical = JSON.stringify(normalized);
  return crypto.createHash('sha256').update(canonical).digest('hex');
}

async function findPriorImport({ contentHash, payloadHash }) {
  if (!contentHash && !payloadHash) return null;
  const { rows } = await pool.query(
    `SELECT l.id, l.created_at, l.details, u.username
     FROM curator_audit_log l
     LEFT JOIN users u ON u.id = l.user_id
     WHERE l.action = 'influence.import'
       AND (
         ($1::text IS NOT NULL AND l.details->>'contentHash' = $1)
         OR ($2::text IS NOT NULL AND l.details->>'payloadHash' = $2)
       )
     ORDER BY l.created_at DESC
     LIMIT 1`,
    [contentHash || null, payloadHash || null],
  );
  if (!rows[0]) return null;
  const d = rows[0].details || {};
  return {
    importedAt: rows[0].created_at,
    username: rows[0].username || null,
    fileName: d.fileName || null,
    inserted: d.inserted ?? null,
    contentHash: d.contentHash || null,
    payloadHash: d.payloadHash || null,
    match:
      contentHash && d.contentHash === contentHash
        ? 'file'
        : payloadHash && d.payloadHash === payloadHash
          ? 'data'
          : 'unknown',
  };
}

async function insertLegacy(poolClient, workId, sourcePaintingId, edge) {
  await poolClient.query(
    `INSERT INTO painting_influences
       (painting_id, influenced_by_painting_id, notes, source, aspects, quote, source_author, source_url)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
     ON CONFLICT (painting_id, influenced_by_painting_id) DO NOTHING`,
    [
      workId,
      sourcePaintingId,
      edge.notes || null,
      edge.source || null,
      edge.aspects || null,
      edge.quote || null,
      edge.source_author || null,
      edge.source_url || null,
    ],
  );
}

async function insertSource(poolClient, workId, sourceType, sourceIds, edge, workYear) {
  const result = await poolClient.query(
    `INSERT INTO painting_influence_sources (
       painting_id, source_type, source_painting_id, source_artist_id, source_movement_id,
       period_note, period_start_year, period_end_year,
       notes, source, aspects, quote, source_author, source_url, discovered_via, confidence
     ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16)
     ON CONFLICT DO NOTHING
     RETURNING id`,
    [
      workId,
      sourceType,
      sourceIds.source_painting_id ?? null,
      sourceIds.source_artist_id ?? null,
      sourceIds.source_movement_id ?? null,
      null,
      null,
      null,
      edge.notes || null,
      edge.source || null,
      edge.aspects || null,
      edge.quote || null,
      edge.source_author || null,
      edge.source_url || null,
      edge.discovered_via || 'import-wizard',
      edge.confidence || 'curated',
    ],
  );
  return result.rowCount > 0 ? result.rows[0].id : null;
}

const COLUMN_ROLES = [
  'subject_artist',
  'subject_painting',
  'influenced_by',
  'influenced',
  'notes',
  'reference',
  'source_url',
  'ignore',
];

const PRESETS = {
  web_sources: {
    id: 'web_sources',
    label: 'Web sources (Artist / Painting / Influenced by / Influenced / Reference)',
    mapping: {
      Artist: 'subject_artist',
      Painting: 'subject_painting',
      'Influenced by': 'influenced_by',
      Influenced: 'influenced',
      'Reference (source + link)': 'reference',
    },
  },
  story_of_art: {
    id: 'story_of_art',
    label: 'Story of Art / Title Case influences',
    mapping: {
      Artist: 'subject_artist',
      Painting: 'subject_painting',
      'Influenced by': 'influenced_by',
      Influenced: 'influenced',
      'Reference (chapter + context in text)': 'reference',
      Reference: 'reference',
    },
  },
  art_influences: {
    id: 'art_influences',
    label: 'Art influences (snake_case)',
    mapping: {
      artist: 'subject_artist',
      painting: 'subject_painting',
      influenced_by: 'influenced_by',
      influenced: 'influenced',
      reference: 'reference',
    },
  },
  custom: {
    id: 'custom',
    label: 'Custom mapping',
    mapping: {},
  },
};

function normalizeHeader(h) {
  return String(h || '')
    .replace(/^\uFEFF/, '')
    .trim();
}

function suggestPreset(columns) {
  const set = new Set(columns.map((c) => c.toLowerCase()));
  if (set.has('influenced_by') && set.has('artist')) return 'art_influences';
  if (set.has('influenced by') && set.has('artist')) {
    if ([...columns].some((c) => /chapter/i.test(c))) return 'story_of_art';
    return 'web_sources';
  }
  return 'custom';
}

function autoMapColumns(columns) {
  const mapping = {};
  for (const col of columns) {
    const lower = col.toLowerCase();
    if (lower === 'artist' || lower === 'subject_artist') mapping[col] = 'subject_artist';
    else if (lower === 'painting' || lower === 'subject_painting' || lower === 'work') mapping[col] = 'subject_painting';
    else if (lower === 'influenced by' || lower === 'influenced_by' || lower === 'influencedby') mapping[col] = 'influenced_by';
    else if (lower === 'influenced' || lower === 'influenced_on' || lower === 'influencedon') mapping[col] = 'influenced';
    else if (lower === 'notes') mapping[col] = 'notes';
    else if (lower.startsWith('reference') || lower === 'source') mapping[col] = 'reference';
    else if (lower === 'source_url' || lower === 'url' || lower === 'link') mapping[col] = 'source_url';
    else mapping[col] = 'ignore';
  }
  return mapping;
}

function extractUrls(text) {
  if (!text) return [];
  const matches = String(text).match(/https?:\/\/[^\s;]+/gi) || [];
  return matches.map((u) => u.replace(/[),.;]+$/, ''));
}

function splitTokens(cell) {
  if (cell == null || cell === '') return [];
  const text = String(cell).trim();
  if (!text || text.toLowerCase() === 'null' || text === 'None') return [];
  // Prefer semicolon splits; also split on commas when not inside parentheses
  const parts = text
    .split(/;/)
    .flatMap((chunk) => {
      const c = chunk.trim();
      if (!c) return [];
      // If many commas and no long phrases, split; else keep as one token and also try comma split for name lists
      if (c.includes(',') && !/\([^)]*,/.test(c)) {
        return c.split(',').map((x) => x.trim()).filter(Boolean);
      }
      return [c];
    })
    .map((t) => t.replace(/\s+/g, ' ').trim())
    .filter((t) => t.length > 1);
  return [...new Set(parts)];
}

function parseBuffer(buffer, filename, options = {}) {
  const name = (filename || '').toLowerCase();
  const ext = name.includes('.') ? name.slice(name.lastIndexOf('.')) : '';

  if (ext === '.json' || (buffer[0] === 0x7b || buffer[0] === 0x5b)) {
    const text = buffer.toString('utf8');
    const data = JSON.parse(text);
    let rows;
    let sheets = null;
    if (Array.isArray(data)) {
      rows = data;
    } else if (data && Array.isArray(data.rows)) {
      rows = data.rows;
    } else if (data && typeof data === 'object') {
      sheets = Object.keys(data).filter((k) => Array.isArray(data[k]));
      const key = options.sheet || sheets[0];
      rows = data[key] || [];
    } else {
      throw new Error('JSON must be an array of objects or { rows: [...] }');
    }
    const columns = rows.length ? Object.keys(rows[0]).map(normalizeHeader) : [];
    return {
      format: 'json',
      sheets,
      sheet: options.sheet || (sheets && sheets[0]) || null,
      columns,
      rows: rows.map(normalizeRowKeys),
      sampleRows: rows.slice(0, 5).map(normalizeRowKeys),
      rowCount: rows.length,
    };
  }

  if (ext === '.xlsx' || ext === '.xls' || buffer[0] === 0x50) {
    const workbook = XLSX.read(buffer, { type: 'buffer', cellDates: false });
    const sheets = workbook.SheetNames;
    const sheetName = options.sheet && sheets.includes(options.sheet) ? options.sheet : sheets[0];
    const sheet = workbook.Sheets[sheetName];
    const rows = XLSX.utils.sheet_to_json(sheet, { defval: '', raw: false });
    const columns = rows.length ? Object.keys(rows[0]).map(normalizeHeader) : [];
    const normalized = rows.map(normalizeRowKeys);
    return {
      format: 'xlsx',
      sheets,
      sheet: sheetName,
      columns,
      rows: normalized,
      sampleRows: normalized.slice(0, 5),
      rowCount: normalized.length,
    };
  }

  // CSV default
  const text = buffer.toString('utf8');
  const records = parseCsv(text, {
    columns: true,
    skip_empty_lines: true,
    relax_column_count: true,
    bom: true,
    trim: true,
  });
  const columns = records.length ? Object.keys(records[0]).map(normalizeHeader) : [];
  const normalized = records.map(normalizeRowKeys);
  return {
    format: 'csv',
    sheets: null,
    sheet: null,
    columns,
    rows: normalized,
    sampleRows: normalized.slice(0, 5),
    rowCount: normalized.length,
  };
}

function normalizeRowKeys(row) {
  const out = {};
  for (const [k, v] of Object.entries(row)) {
    out[normalizeHeader(k)] = v == null ? '' : String(v);
  }
  return out;
}

function applyMapping(row, mapping) {
  const mapped = {
    subject_artist: '',
    subject_painting: '',
    influenced_by: '',
    influenced: '',
    notes: '',
    reference: '',
    source_url: '',
  };
  for (const [col, role] of Object.entries(mapping || {})) {
    if (!role || role === 'ignore') continue;
    if (!(role in mapped)) continue;
    const val = row[col];
    if (val == null || val === '') continue;
    mapped[role] = mapped[role] ? `${mapped[role]}; ${val}` : String(val);
  }
  return mapped;
}

async function buildLookupCaches() {
  const movementsByName = await loadMovements(pool);
  const { rows: artists } = await pool.query('SELECT id, name FROM artists ORDER BY name');
  const { rows: paintings } = await pool.query(
    `SELECT p.id, p.title, p.year, p.artist_id, a.name AS artist_name
     FROM paintings p JOIN artists a ON a.id = p.artist_id`,
  );
  const { rows: movements } = await pool.query('SELECT id, name FROM art_movements ORDER BY name');

  const artistsByLower = new Map();
  for (const a of artists) {
    artistsByLower.set(a.name.toLowerCase(), a);
    const canon = normalizeArtist(a.name);
    if (canon.toLowerCase() !== a.name.toLowerCase()) {
      artistsByLower.set(canon.toLowerCase(), a);
    }
  }

  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);
  }

  return { movementsByName, artists, artistsByLower, paintings, paintingsByArtist, movements };
}

function resolveToken(token, caches, subjectArtistId) {
  const raw = token.trim();
  if (!raw) return { status: 'empty', token: raw };

  const artistHit = caches.artistsByLower.get(raw.toLowerCase())
    || caches.artistsByLower.get(normalizeArtist(raw).toLowerCase());
  if (artistHit) {
    return {
      status: 'resolved',
      token: raw,
      sourceType: 'artist',
      sourceArtistId: artistHit.id,
      label: artistHit.name,
    };
  }

  // Also try stripping parenthetical notes: "Parrhasius (their contest)"
  const bare = raw.replace(/\s*\([^)]*\)\s*/g, ' ').replace(/\s+/g, ' ').trim();
  if (bare && bare.toLowerCase() !== raw.toLowerCase()) {
    const bareArtist = caches.artistsByLower.get(bare.toLowerCase())
      || caches.artistsByLower.get(normalizeArtist(bare).toLowerCase());
    if (bareArtist) {
      return {
        status: 'resolved',
        token: raw,
        sourceType: 'artist',
        sourceArtistId: bareArtist.id,
        label: bareArtist.name,
      };
    }
    const bareMovementId = caches.movementsByName.get(bare.toLowerCase());
    if (bareMovementId) {
      const mov = caches.movements.find((m) => m.id === bareMovementId);
      return {
        status: 'resolved',
        token: raw,
        sourceType: 'movement',
        sourceMovementId: bareMovementId,
        label: mov?.name || bare,
      };
    }
  }

  const movementId = caches.movementsByName.get(raw.toLowerCase());
  if (movementId) {
    const mov = caches.movements.find((m) => m.id === movementId);
    return {
      status: 'resolved',
      token: raw,
      sourceType: 'movement',
      sourceMovementId: movementId,
      label: mov?.name || raw,
    };
  }

  // Painting matches only for short title-like tokens (avoid free-text prose false hits)
  const wordCount = raw.split(/\s+/).length;
  if (wordCount <= 8) {
    if (subjectArtistId) {
      const list = caches.paintingsByArtist.get(subjectArtistId) || [];
      let best = null;
      let bestScore = 0;
      for (const p of list) {
        const score = scoreTitle(p.title, raw);
        if (score > bestScore) {
          bestScore = score;
          best = p;
        }
      }
      if (best && bestScore >= 2) {
        return {
          status: 'resolved',
          token: raw,
          sourceType: 'painting',
          sourcePaintingId: best.id,
          label: `${best.artist_name} — ${best.title}`,
        };
      }
      // Exact-ish normalized equality
      const normRaw = normalizeLoose(raw);
      const exact = list.find((p) => normalizeLoose(p.title) === normRaw);
      if (exact) {
        return {
          status: 'resolved',
          token: raw,
          sourceType: 'painting',
          sourcePaintingId: exact.id,
          label: `${exact.artist_name} — ${exact.title}`,
        };
      }
    }

    const normRaw = normalizeLoose(raw);
    const titleHits = caches.paintings.filter((p) => normalizeLoose(p.title) === normRaw);
    if (titleHits.length === 1) {
      const p = titleHits[0];
      return {
        status: 'resolved',
        token: raw,
        sourceType: 'painting',
        sourcePaintingId: p.id,
        label: `${p.artist_name} — ${p.title}`,
      };
    }
    if (titleHits.length > 1) {
      return {
        status: 'ambiguous',
        token: raw,
        candidates: titleHits.slice(0, 8).map((p) => ({
          sourceType: 'painting',
          sourcePaintingId: p.id,
          label: `${p.artist_name} — ${p.title}`,
        })),
      };
    }
  }

  return { status: 'unresolved', token: raw };
}

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

function scoreTitle(dbTitle, hint) {
  const normDb = normalizeLoose(dbTitle);
  const keywords = normalizeLoose(hint).split(' ').filter((w) => w.length > 2);
  if (!keywords.length) return 0;
  const hits = keywords.filter((k) => normDb.includes(k)).length;
  const required = keywords.length === 1 ? 1 : Math.min(2, keywords.length);
  return hits >= required ? hits : 0;
}

function edgeKey(paintingId, sourceType, sourcePaintingId, sourceArtistId, sourceMovementId) {
  return [
    paintingId,
    sourceType,
    sourcePaintingId || 0,
    sourceArtistId || 0,
    sourceMovementId || 0,
  ].join(':');
}

/**
 * Build preview proposals from mapped file rows.
 * Artist-level: expand to all paintings of subject (influenced_by) or target (influenced).
 */
async function buildPreview(rows, mapping, options = {}) {
  const caches = await buildLookupCaches();
  const { rows: existing } = await pool.query(
    `SELECT painting_id, source_type, source_painting_id, source_artist_id, source_movement_id
     FROM painting_influence_sources`,
  );
  const existingKeys = new Set(
    existing.map((e) =>
      edgeKey(e.painting_id, e.source_type, e.source_painting_id, e.source_artist_id, e.source_movement_id),
    ),
  );

  const proposals = [];
  const warnings = [];
  let errors = 0;

  rows.forEach((row, rowIndex) => {
    const mapped = applyMapping(row, mapping);
    const subjectName = mapped.subject_artist.trim();
    if (!subjectName) {
      warnings.push({ rowIndex, message: 'Missing subject artist' });
      errors += 1;
      return;
    }

    const subjectArtist = caches.artistsByLower.get(subjectName.toLowerCase())
      || caches.artistsByLower.get(normalizeArtist(subjectName).toLowerCase());
    if (!subjectArtist) {
      warnings.push({ rowIndex, message: `Unresolved subject artist: ${subjectName}`, token: subjectName });
      errors += 1;
      return;
    }

    let subjectPaintings = caches.paintingsByArtist.get(subjectArtist.id) || [];
    if (mapped.subject_painting.trim()) {
      const hint = mapped.subject_painting.trim();
      let best = null;
      let bestScore = 0;
      for (const p of subjectPaintings) {
        const s = scoreTitle(p.title, hint);
        if (s > bestScore) {
          bestScore = s;
          best = p;
        }
      }
      // Plan decision 1A: still expand to ALL paintings; painting column is contextual only.
      // Keep note of matched work in metadata.
      if (!best) {
        warnings.push({
          rowIndex,
          message: `Painting hint not matched (still expanding to all works): ${hint}`,
          token: hint,
        });
      }
    }

    if (!subjectPaintings.length) {
      warnings.push({ rowIndex, message: `Artist has no paintings: ${subjectArtist.name}` });
      errors += 1;
      return;
    }

    const urls = extractUrls(mapped.source_url || mapped.reference);
    const notesParts = [mapped.notes, mapped.reference].filter(Boolean);
    const edgeMeta = {
      notes: notesParts.join(' | ').slice(0, 4000) || null,
      source: options.sourceLabel || 'import-wizard',
      source_url: urls[0] || null,
      confidence: 'curated',
      discovered_via: 'import-wizard',
    };

    const byTokens = splitTokens(mapped.influenced_by);
    for (const token of byTokens) {
      const resolved = resolveToken(token, caches, subjectArtist.id);
      if (resolved.status !== 'resolved') {
        warnings.push({
          rowIndex,
          message: `${resolved.status}: ${token}`,
          token,
          direction: 'influenced_by',
          candidates: resolved.candidates,
        });
        if (resolved.status === 'unresolved' || resolved.status === 'ambiguous') {
          /* counted in warnings */
        }
        continue;
      }
      for (const p of subjectPaintings) {
        const prop = makeProposal({
          rowIndex,
          direction: 'influenced_by',
          paintingId: p.id,
          paintingTitle: p.title,
          artistId: subjectArtist.id,
          artistName: subjectArtist.name,
          resolved,
          edgeMeta,
          existingKeys,
        });
        proposals.push(prop);
      }
    }

    const onTokens = splitTokens(mapped.influenced);
    for (const token of onTokens) {
      const resolved = resolveToken(token, caches, subjectArtist.id);
      if (resolved.status !== 'resolved') {
        warnings.push({
          rowIndex,
          message: `${resolved.status}: ${token}`,
          token,
          direction: 'influenced',
          candidates: resolved.candidates,
        });
        continue;
      }

      if (resolved.sourceType === 'artist') {
        // Reverse: subject artist is the source on the target artist's paintings
        const targetPaintings = caches.paintingsByArtist.get(resolved.sourceArtistId) || [];
        if (!targetPaintings.length) {
          warnings.push({
            rowIndex,
            message: `Influenced artist has no paintings: ${resolved.label}`,
            token,
          });
          continue;
        }
        const reverseResolved = {
          status: 'resolved',
          token: subjectArtist.name,
          sourceType: 'artist',
          sourceArtistId: subjectArtist.id,
          label: subjectArtist.name,
        };
        const reverseMeta = {
          ...edgeMeta,
          notes: [
            edgeMeta.notes,
            `Reverse link: ${subjectArtist.name} listed as influence on ${resolved.label}.`,
          ]
            .filter(Boolean)
            .join(' '),
        };
        for (const p of targetPaintings) {
          const prop = makeProposal({
            rowIndex,
            direction: 'influenced',
            paintingId: p.id,
            paintingTitle: p.title,
            artistId: resolved.sourceArtistId,
            artistName: resolved.label,
            resolved: reverseResolved,
            edgeMeta: reverseMeta,
            existingKeys,
          });
          proposals.push(prop);
        }
      } else {
        // Non-artist "influenced" targets: attach as sources on subject's paintings (weaker semantics)
        for (const p of subjectPaintings) {
          const prop = makeProposal({
            rowIndex,
            direction: 'influenced',
            paintingId: p.id,
            paintingTitle: p.title,
            artistId: subjectArtist.id,
            artistName: subjectArtist.name,
            resolved,
            edgeMeta: {
              ...edgeMeta,
              notes: [edgeMeta.notes, `Listed under Influenced (non-artist target).`].filter(Boolean).join(' '),
            },
            existingKeys,
          });
          proposals.push(prop);
        }
      }
    }
  });

  // Deduplicate proposals by edge key (keep first)
  const seen = new Set();
  const deduped = [];
  for (const p of proposals) {
    if (seen.has(p.edgeKey)) {
      p.action = 'skip';
      p.reason = p.reason || 'duplicate in file';
      continue;
    }
    seen.add(p.edgeKey);
    deduped.push(p);
  }

  return {
    proposals: deduped,
    warnings,
    counts: {
      rows: rows.length,
      proposals: deduped.length,
      willCreate: deduped.filter((p) => p.action === 'create').length,
      willSkip: deduped.filter((p) => p.action === 'skip').length,
      errors,
      unresolvedTokens: warnings.filter((w) => w.token).length,
    },
  };
}

function makeProposal({
  rowIndex,
  direction,
  paintingId,
  paintingTitle,
  artistId,
  artistName,
  resolved,
  edgeMeta,
  existingKeys,
}) {
  const sourceType = resolved.sourceType;
  const sourcePaintingId = resolved.sourcePaintingId || null;
  const sourceArtistId = resolved.sourceArtistId || null;
  const sourceMovementId = resolved.sourceMovementId || null;

  if (sourceType === 'painting' && sourcePaintingId === paintingId) {
    return {
      rowIndex,
      direction,
      paintingId,
      paintingTitle,
      artistId,
      artistName,
      sourceType,
      sourcePaintingId,
      sourceArtistId,
      sourceMovementId,
      sourceLabel: resolved.label,
      token: resolved.token,
      notes: edgeMeta.notes,
      source: edgeMeta.source,
      sourceUrl: edgeMeta.source_url,
      confidence: edgeMeta.confidence,
      discoveredVia: edgeMeta.discovered_via,
      edgeKey: edgeKey(paintingId, sourceType, sourcePaintingId, sourceArtistId, sourceMovementId),
      action: 'skip',
      reason: 'self painting',
    };
  }
  if (sourceType === 'artist' && sourceArtistId === artistId) {
    return {
      rowIndex,
      direction,
      paintingId,
      paintingTitle,
      artistId,
      artistName,
      sourceType,
      sourcePaintingId,
      sourceArtistId,
      sourceMovementId,
      sourceLabel: resolved.label,
      token: resolved.token,
      notes: edgeMeta.notes,
      source: edgeMeta.source,
      sourceUrl: edgeMeta.source_url,
      confidence: edgeMeta.confidence,
      discoveredVia: edgeMeta.discovered_via,
      edgeKey: edgeKey(paintingId, sourceType, sourcePaintingId, sourceArtistId, sourceMovementId),
      action: 'skip',
      reason: 'self artist',
    };
  }

  const key = edgeKey(paintingId, sourceType, sourcePaintingId, sourceArtistId, sourceMovementId);
  const exists = existingKeys.has(key);
  return {
    rowIndex,
    direction,
    paintingId,
    paintingTitle,
    artistId,
    artistName,
    sourceType,
    sourcePaintingId,
    sourceArtistId,
    sourceMovementId,
    sourceLabel: resolved.label,
    token: resolved.token,
    notes: edgeMeta.notes,
    source: edgeMeta.source,
    sourceUrl: edgeMeta.source_url,
    confidence: edgeMeta.confidence,
    discoveredVia: edgeMeta.discovered_via,
    edgeKey: key,
    action: exists ? 'skip' : 'create',
    reason: exists ? 'already in database' : null,
  };
}

async function commitProposals(proposals, { userId, req, fileName } = {}) {
  const toCreate = (proposals || []).filter((p) => p.action === 'create');
  let inserted = 0;
  let skipped = 0;
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    for (const p of toCreate) {
      const edge = {
        notes: p.notes,
        source: p.source,
        source_url: p.sourceUrl,
        confidence: p.confidence || 'curated',
        discovered_via: p.discoveredVia || 'import-wizard',
      };
      const sourceIds = {
        source_painting_id: p.sourcePaintingId,
        source_artist_id: p.sourceArtistId,
        source_movement_id: p.sourceMovementId,
      };
      if (p.sourceType === 'painting' && p.sourcePaintingId) {
        await insertLegacy(client, p.paintingId, p.sourcePaintingId, edge);
      }
      const id = await insertSource(client, p.paintingId, p.sourceType, sourceIds, edge, null);
      if (id) inserted += 1;
      else skipped += 1;
    }
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }

  return { inserted, skipped, attempted: toCreate.length, fileName: fileName || null };
}

module.exports = {
  COLUMN_ROLES,
  PRESETS,
  parseBuffer,
  suggestPreset,
  autoMapColumns,
  applyMapping,
  buildPreview,
  commitProposals,
  splitTokens,
  normalizeHeader,
  hashBuffer,
  hashImportPayload,
  findPriorImport,
};