`tk11041-innovations-reconcile/reconcile.mjs`

161 lines
#!/usr/bin/env node
// TK-11041 — feed-first / local SKU reconciliation for the missing Innovations cohort.
//
// READ-ONLY. No Shopify writes. No dw_unified writes. No catalog activation.
// Produces a DETERMINISTIC, dry-run recovery map for the 51 ACTIVE, sample-only
// Phillipe-Romano-private-label Innovations products, proving whether any LOCAL
// (feed-first) recovery path exists before falling back to the gated vendor line-sheet.
//
// Cohort definition (deterministic):
//   vendor='Phillipe Romano' AND status='ACTIVE'
//   AND NOT has_product_variant  (unbuyable: only the $4.25 memo Sample)
//   AND supplier_name ~* 'innov'
//
// Recovery-source join keys tested (per the pilot README's proven join model):
//   sp.sku    = innovations_catalog.dw_sku    (numeric DW-SKU form)
//   sp.dw_sku = innovations_catalog.dw_sku
//   sp.mfr_sku= innovations_catalog.mfr_sku
//   + feed-first pattern-token word match against innovations_catalog.pattern_name
//
// Every candidate source is additionally run through lib/provenance.classifyCatalogSource
// so a "match" made of fabricated (no-URL / truncated) catalog data is never counted
// as a real recovery.

import fs from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
import { queryRows } from '../lib/db.mjs';
import { classifyCatalogSource } from './lib/provenance.mjs';

const HERE = path.dirname(fileURLToPath(import.meta.url));
const DATA = path.join(HERE, 'data');
fs.mkdirSync(DATA, { recursive: true });

// ---------------------------------------------------------------------------
// 1) The 51 cohort, with the identity fields + a derived pattern token.
// ---------------------------------------------------------------------------
const cohort = queryRows(`
  select
    sp.sku,
    coalesce(sp.dw_sku,'')  as dw_sku,
    coalesce(sp.mfr_sku,'') as mfr_sku,
    split_part(coalesce(sp.mfr_sku,''),'-',1) as collection,
    trim(regexp_replace(
      regexp_replace(split_part(sp.title,' | ',1),'( Type ?2| Natural| Vinyl| Memo Sample).*$','','i'),
      '^La ','')) as base,
    sp.title
  from shopify_products sp
  where sp.vendor='Phillipe Romano' and sp.status='ACTIVE'
    and not coalesce(sp.has_product_variant,false)
    and sp.supplier_name ~* 'innov'
  order by sp.mfr_sku
`);

const patToken = base => (base || '').split(' ').filter(Boolean)[0] || '';

// ---------------------------------------------------------------------------
// 2) Per-item local-recovery test (all read-only lookups against the catalog).
// ---------------------------------------------------------------------------
const map = cohort.map(r => {
  const tok = patToken(r.base);

  const bySku = r.sku ? queryRows(
    `select mfr_sku, product_url, pattern_name, price_trade from innovations_catalog
       where upper(dw_sku)=upper($$${r.sku}$$)`) : [];
  const byDwSku = r.dw_sku ? queryRows(
    `select mfr_sku, product_url, pattern_name, price_trade from innovations_catalog
       where upper(dw_sku)=upper($$${r.dw_sku}$$)`) : [];
  const byMfr = r.mfr_sku ? queryRows(
    `select mfr_sku, product_url, pattern_name, price_trade from innovations_catalog
       where upper(mfr_sku)=upper($$${r.mfr_sku}$$)`) : [];
  const byPattern = tok.length >= 4 ? queryRows(
    `select mfr_sku, product_url, pattern_name, price_trade from innovations_catalog
       where pattern_name ~* ('\\m'||$$${tok}$$||'\\M')`) : [];

  const candidates = [...bySku, ...byDwSku, ...byMfr, ...byPattern];
  // A candidate only counts as a REAL recovery if it is a trustworthy source
  // (has provenance + a non-fabricated code) AND carries a usable net price.
  const trustworthy = candidates.filter(c =>
    classifyCatalogSource(c).trustworthy && Number(c.price_trade) > 0);

  const recoverable = trustworthy.length > 0;
  return {
    sku: r.sku,
    internal_code: r.mfr_sku,
    collection: r.collection,
    pattern_token: tok,
    title: r.title,
    join_sku_hits: bySku.length,
    join_dwsku_hits: byDwSku.length,
    join_mfr_hits: byMfr.length,
    pattern_match_hits: byPattern.length,
    trustworthy_priced_sources: trustworthy.length,
    status: recoverable ? 'RECOVERABLE_LOCAL' : 'BLOCKED_ON_VENDOR_LINESHEET',
    recovery_action: recoverable
      ? 'stage add-yard variant (net_trade/0.65/0.85 per YARD) via proven stage-innov-reprice — GATED (Shopify write)'
      : 'no trustworthy local source; match returned Innovations line-sheet on pattern+colorway, then stage add-yard — email send GATED to Steve',
  };
});

// ---------------------------------------------------------------------------
// 3) Catalog provenance snapshot (grounds the "no local source" conclusion).
// ---------------------------------------------------------------------------
const catalog = queryRows(`select mfr_sku, product_url, pattern_name from innovations_catalog`);
let noUrl = 0, corrupted = 0, trustworthy = 0;
for (const row of catalog) {
  const c = classifyCatalogSource(row);
  if (c.reasons.includes('no_provenance_url')) noUrl++;
  if (c.reasons.includes('corrupted_lowercase_tail') || c.reasons.includes('code_is_pattern_tail')) corrupted++;
  if (c.trustworthy) trustworthy++;
}

// links: which vendor does innovations_catalog actually describe?
const linkage = queryRows(`
  select sp.vendor, count(*) n from innovations_catalog ic
  join shopify_products sp on sp.shopify_id like '%'||ic.shopify_product_id
  where coalesce(ic.shopify_product_id,'')<>'' group by 1 order by 2 desc`);

const summary = {
  task: 'TK-11041',
  generated_at: new Date().toISOString(),
  cohort_size: map.length,
  recoverable_local: map.filter(m => m.status === 'RECOVERABLE_LOCAL').length,
  blocked_on_vendor: map.filter(m => m.status === 'BLOCKED_ON_VENDOR_LINESHEET').length,
  collections: [...new Set(map.map(m => m.collection))].sort(),
  join_coverage: {
    by_sku: map.reduce((a, m) => a + m.join_sku_hits, 0),
    by_dwsku: map.reduce((a, m) => a + m.join_dwsku_hits, 0),
    by_mfr: map.reduce((a, m) => a + m.join_mfr_hits, 0),
    by_pattern_token: map.reduce((a, m) => a + m.pattern_match_hits, 0),
  },
  catalog_provenance: {
    total_rows: catalog.length,
    no_provenance_url: noUrl,
    fabricated_code_rows: corrupted,
    trustworthy_source_rows: trustworthy,
    describes_vendor: linkage,
  },
  conclusion: 'BLOCKED_ON_VENDOR_LINESHEET — 0 trustworthy local recovery sources for the cohort; '
    + 'innovations_catalog describes the Innovations USA (DWIN-) line, not the PR private-label. '
    + 'Local backfill would fabricate identity/price. Recovery requires the gated vendor line-sheet.',
};

// ---------------------------------------------------------------------------
// 4) Emit artifacts.
// ---------------------------------------------------------------------------
fs.writeFileSync(path.join(DATA, 'innovations-recovery-map.json'),
  JSON.stringify({ summary, map }, null, 2));
fs.writeFileSync(path.join(DATA, 'reconcile-summary.json'),
  JSON.stringify(summary, null, 2));

const csvCols = ['sku', 'internal_code', 'collection', 'pattern_token',
  'join_sku_hits', 'join_dwsku_hits', 'join_mfr_hits', 'pattern_match_hits',
  'trustworthy_priced_sources', 'status'];
const csv = [csvCols.join(',')]
  .concat(map.map(m => csvCols.map(k => JSON.stringify(m[k] ?? '')).join(',')))
  .join('\n');
fs.writeFileSync(path.join(DATA, 'innovations-recovery-map.csv'), csv + '\n');

console.log(JSON.stringify(summary, null, 2));
console.log(`\nWrote:\n  data/innovations-recovery-map.json (${map.length} items)\n  data/innovations-recovery-map.csv\n  data/reconcile-summary.json`);