`scripts/cactus-curator-setup.js`

110 lines
#!/usr/bin/env node
'use strict';
/*
 * cactus-curator-setup.js — one-time schema + seam-score ingest for the
 * Cactus Curator (/admin/cactus-curator).
 *
 *   1. ALTER all_designs: add the 4-action date/flag columns
 *        digital_file_at  (option 2 — "Sell as Digital File : create field with date")
 *        needs_fixing_at  (option 3 — "Keep but needs fixing")
 *        web_viewer       (option 4 — "Keep, go live in a web viewer")
 *   2. CREATE wallco_cactus_rank sidecar (keeps cactus-only ranking data OUT
 *      of all_designs so the base table doesn't bloat).
 *   3. Ingest the already-computed (free) seam scores from the on-disk edges
 *      scan into wallco_cactus_rank.seam_score. Vision scores land later via
 *      scripts/cactus-vision-score.js (free local Ollama vision).
 *
 * Idempotent — safe to re-run. Uses the same psql-shell path as server.js.
 */
const fs = require('fs');
const path = require('path');
const { psqlExecLocal, psqlQuery } = require('../lib/db');

const ROOT = path.join(__dirname, '..');
const EDGES_TSV = path.join(ROOT, 'data', 'edges-scan-full-2026-05-26.tsv');

function sql(s) { return psqlExecLocal(s); }

// ── 1 + 2. schema ──────────────────────────────────────────────────────────
console.log('[setup] applying schema (columns + sidecar table)…');
sql(`
ALTER TABLE all_designs ADD COLUMN IF NOT EXISTS digital_file_at timestamptz;
ALTER TABLE all_designs ADD COLUMN IF NOT EXISTS needs_fixing_at timestamptz;
ALTER TABLE all_designs ADD COLUMN IF NOT EXISTS web_viewer boolean DEFAULT FALSE;

CREATE TABLE IF NOT EXISTS wallco_cactus_rank (
  design_id        bigint PRIMARY KEY,
  seam_score       numeric,        -- 0..100, higher = more seamless (print-ready)
  seam_edge_raw    numeric,        -- raw worst edge-lens ΔE
  seam_mid_raw     numeric,        -- raw worst mid-lens ΔE
  seam_verdict     text,           -- PASS / WARN / FAIL from edges scan
  vision_score     numeric,        -- 0..100, "looks like a real sellable wallcovering"
  vision_raw       text,           -- raw model reply (audit)
  vision_model     text,
  seam_scored_at   timestamptz,
  vision_scored_at timestamptz
);
`);
console.log('[setup] schema OK');

// ── 3. ingest seam scores from the edges TSV ────────────────────────────────
// TSV columns: id | category | level | verdict | edge_score | mid_score | note
if (!fs.existsSync(EDGES_TSV)) {
  console.warn('[setup] edges TSV not found, skipping seam ingest:', EDGES_TSV);
  process.exit(0);
}

// Which design_ids are cactus? Pull the set once so we only ingest cactus rows.
const cactusIdsRaw = psqlQuery(
  `SELECT string_agg(id::text, ',') FROM all_designs WHERE category ILIKE '%cactus%';`
);
const cactusIds = new Set((cactusIdsRaw || '').split(',').filter(Boolean));
console.log(`[setup] ${cactusIds.size} cactus design ids`);

const WARN_MAX = 12.0; // matches edges-scan.py WARN threshold
function seamQuality(edge, mid) {
  const worst = Math.max(Number(edge) || 0, Number(mid) || 0);
  // 0 ΔE -> 100 ; WARN_MAX ΔE -> 0 ; beyond -> clamp 0
  return Math.max(0, Math.min(100, Math.round((1 - worst / WARN_MAX) * 100)));
}

const lines = fs.readFileSync(EDGES_TSV, 'utf8').split('\n');
const values = [];
for (const line of lines) {
  if (!line.trim()) continue;
  const c = line.split('|');
  if (c.length < 6) continue;
  const id = c[0].trim();
  if (!cactusIds.has(id)) continue;
  const verdict = (c[3] || '').trim();
  const edge = parseFloat(c[4]);
  const mid = parseFloat(c[5]);
  if (!Number.isFinite(edge) || !Number.isFinite(mid)) continue;
  const sq = seamQuality(edge, mid);
  values.push(`(${id}, ${sq}, ${edge}, ${mid}, '${verdict.replace(/'/g, "''")}', now())`);
}
console.log(`[setup] ${values.length} cactus seam rows to ingest`);

// chunked upsert
const CHUNK = 500;
let done = 0;
for (let i = 0; i < values.length; i += CHUNK) {
  const batch = values.slice(i, i + CHUNK);
  sql(`
    INSERT INTO wallco_cactus_rank (design_id, seam_score, seam_edge_raw, seam_mid_raw, seam_verdict, seam_scored_at)
    VALUES ${batch.join(',')}
    ON CONFLICT (design_id) DO UPDATE SET
      seam_score    = EXCLUDED.seam_score,
      seam_edge_raw = EXCLUDED.seam_edge_raw,
      seam_mid_raw  = EXCLUDED.seam_mid_raw,
      seam_verdict  = EXCLUDED.seam_verdict,
      seam_scored_at= EXCLUDED.seam_scored_at;
  `);
  done += batch.length;
  process.stdout.write(`\r[setup] ingested ${done}/${values.length}`);
}
console.log('\n[setup] seam ingest complete');

const cov = psqlQuery(`SELECT count(*) FROM wallco_cactus_rank WHERE seam_score IS NOT NULL;`);
console.log(`[setup] wallco_cactus_rank rows with seam_score: ${cov}`);