Art-gallery/scripts/audit-influence-duplicates.js

require('dotenv').config();
const pool = require('../server/db');

async function main() {
  const overlap = await pool.query(`
    WITH legacy AS (
      SELECT influenced_by_painting_id AS src, painting_id AS dst
      FROM painting_influences
    ),
    sources AS (
      SELECT source_painting_id AS src, painting_id AS dst
      FROM painting_influence_sources
      WHERE source_type = 'painting' AND source_painting_id IS NOT NULL
    )
    SELECT COUNT(*)::int AS doubled_edges
    FROM legacy l
    INNER JOIN sources s ON l.src = s.src AND l.dst = s.dst
  `);
  console.log('Doubled painting edges (legacy + sources):', overlap.rows[0].doubled_edges);

  const dupSources = await pool.query(`
    SELECT painting_id, source_type,
           COALESCE(source_painting_id, 0) AS sp,
           COALESCE(source_artist_id, 0) AS sa,
           COALESCE(source_movement_id, 0) AS sm,
           COUNT(*)::int AS cnt
    FROM painting_influence_sources
    GROUP BY painting_id, source_type, sp, sa, sm
    HAVING COUNT(*) > 1
    ORDER BY cnt DESC
    LIMIT 20
  `);
  console.log('Duplicate rows in painting_influence_sources:', dupSources.rows.length);
  if (dupSources.rows.length) console.table(dupSources.rows);

  const dupLegacy = await pool.query(`
    SELECT painting_id, influenced_by_painting_id, COUNT(*)::int AS cnt
    FROM painting_influences
    GROUP BY painting_id, influenced_by_painting_id
    HAVING COUNT(*) > 1
  `);
  console.log('Duplicate rows in painting_influences:', dupLegacy.rows.length);

  const sample = await pool.query(`
    SELECT pi.influenced_by_painting_id AS src_id, pi.painting_id AS dst_id,
           p_src.title AS src_title, a_src.name AS src_artist,
           p_dst.title AS dst_title, a_dst.name AS dst_artist
    FROM painting_influences pi
    JOIN paintings p_dst ON pi.painting_id = p_dst.id
    JOIN artists a_dst ON p_dst.artist_id = a_dst.id
    JOIN paintings p_src ON pi.influenced_by_painting_id = p_src.id
    JOIN artists a_src ON p_src.artist_id = a_src.id
    WHERE EXISTS (
      SELECT 1 FROM painting_influence_sources pis
      WHERE pis.painting_id = pi.painting_id
        AND pis.source_painting_id = pi.influenced_by_painting_id
        AND pis.source_type = 'painting'
    )
    ORDER BY a_dst.name, p_dst.title
    LIMIT 15
  `);
  console.log('\nSample doubled links (shown twice in API "influenced" query):');
  for (const r of sample.rows) {
    console.log(`  ${r.src_artist} — ${r.src_title} → ${r.dst_artist} — ${r.dst_title}`);
  }

  const apiDupCount = await pool.query(`
    WITH influenced AS (
      SELECT pi.influenced_by_painting_id AS painting_id, pi.painting_id AS linked_id
      FROM painting_influences pi
      UNION ALL
      SELECT pis.source_painting_id, pis.painting_id
      FROM painting_influence_sources pis
      WHERE pis.source_painting_id IS NOT NULL AND pis.source_type = 'painting'
    )
    SELECT painting_id, linked_id, COUNT(*)::int AS cnt
    FROM influenced
    GROUP BY painting_id, linked_id
    HAVING COUNT(*) > 1
    ORDER BY cnt DESC
    LIMIT 20
  `);
  console.log('\nAPI "influenced" duplicates (UNION ALL):', apiDupCount.rows.length);
  if (apiDupCount.rows.length) console.table(apiDupCount.rows);

  const legacyOnly = await pool.query(`
    SELECT COUNT(*)::int AS n
    FROM painting_influences pi
    WHERE NOT EXISTS (
      SELECT 1 FROM painting_influence_sources pis
      WHERE pis.painting_id = pi.painting_id
        AND pis.source_painting_id = pi.influenced_by_painting_id
        AND pis.source_type = 'painting'
    )
  `);
  console.log('\nLegacy-only edges (not in sources):', legacyOnly.rows[0].n);

  const sourcesOnly = await pool.query(`
    SELECT COUNT(*)::int AS n
    FROM painting_influence_sources pis
    WHERE pis.source_type = 'painting' AND pis.source_painting_id IS NOT NULL
      AND NOT EXISTS (
        SELECT 1 FROM painting_influences pi
        WHERE pi.painting_id = pis.painting_id
          AND pi.influenced_by_painting_id = pis.source_painting_id
      )
  `);
  console.log('Sources-only painting edges (not in legacy):', sourcesOnly.rows[0].n);

  const fixedApiDup = await pool.query(`
    SELECT source_painting_id AS painting_id, painting_id AS linked_id, COUNT(*)::int AS cnt
    FROM painting_influence_sources
    WHERE source_type = 'painting' AND source_painting_id IS NOT NULL
    GROUP BY source_painting_id, painting_id
    HAVING COUNT(*) > 1
  `);
  console.log('\nDuplicate "influenced" after fix (should be 0):', fixedApiDup.rows.length);

  await pool.end();
}

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