`coordonne-collab-audit/cotswolds-create-drafts.js`

133 lines
#!/usr/bin/env node
// Cotswolds (Ybarra & Serret for Coordonné) — create the 37 settlement-OK SKUs as DRAFT.
// Steve-approved 2026-07-22: DRAFT only (admin-only, storefront-safe). Activation is separate.
// Pattern lifted from create-coco-mural.js. Deltas:
//  - status FORCED to DRAFT for all (not image-conditional).
//  - Per-row price from coordonne_catalog.price_retail ($169 wallcovering / $113.62 mural).
//  - product_type = Wallcovering | Mural (word "Wallpaper" BANNED); mural sellable option = "Panel", else "Roll".
//  - Feed-first ($0) backfill of description + real mfr SKU + material from the Woo Store API.
//  - Only the 37 settlement-OK dw_skus (read from cotswolds-settlement-result.json).
//  - Idempotency: skip any row that already has a shopify_product_id in PG.
//  - PG-first (stamp description) -> Shopify create (authoritative) -> write back shopify_product_id.
const fs = require('fs'), path = require('path');
const { execSync } = require('child_process');
const ENV = fs.readFileSync(path.join(__dirname, '..', '.env'), 'utf8');
const TOKEN = (ENV.match(/^SHOPIFY_ADMIN_TOKEN=(.+)$/m) || [])[1];
const DOMAIN = 'designer-laboratory-sandbox.myshopify.com', VER = '2024-10';
if (!TOKEN) { console.error('no token'); process.exit(1); }
const DRY = process.env.DRY === '1';
const LIMIT = parseInt(process.env.LIMIT || '0', 10);

const ent = { '&#8217;': '’', '&#8216;': '‘', '&#8220;': '“', '&#8221;': '”', '&#8211;': '–', '&#8212;': '—', '&#8230;': '…', '&amp;': '&', '&nbsp;': ' ', '&quot;': '"', '&#039;': "'", '&#39;': "'" };
const strip = h => (h || '').replace(/<[^>]+>/g, ' ').replace(/&#?\w+;/g, m => ent[m] || m).replace(/\s+/g, ' ').trim();
function esc(s) { return String(s == null ? '' : s).replace(/'/g, "''"); }
function pg(sql) { return execSync(`psql "host=/tmp dbname=dw_unified" -tA -F'|' -c ${JSON.stringify(sql)}`, { encoding: 'utf8' }).trim(); }
async function gql(query, variables) {
  const r = await fetch(`https://${DOMAIN}/admin/api/${VER}/graphql.json`, {
    method: 'POST', headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' },
    body: JSON.stringify({ query, variables }),
  });
  return r.json();
}
const M_CREATE = `mutation($input: ProductInput!, $media: [CreateMediaInput!]) {
  productCreate(input: $input, media: $media) {
    product { id handle status title variants(first:5){ nodes{ id title sku } } }
    userErrors { field message } } }`;
const M_VSET = `mutation($productId: ID!, $variants: [ProductVariantsBulkInput!]!) {
  productVariantsBulkUpdate(productId: $productId, variants: $variants) { userErrors { field message } } }`;
const M_VCREATE = `mutation($productId: ID!, $variants: [ProductVariantsBulkInput!]!) {
  productVariantsBulkCreate(productId: $productId, variants: $variants) { userErrors { field message } } }`;

(async () => {
  // 37 settlement-OK dw_skus
  const gate = JSON.parse(fs.readFileSync(path.join(__dirname, 'cotswolds-settlement-result.json'), 'utf8'));
  const okSkus = gate.results.filter(o => o.verdict === 'OK').map(o => o.dw_sku);
  const inList = okSkus.map(s => `'${esc(s)}'`).join(',');
  // Pull authoritative staged rows from PG (source of truth for price/width/image/type)
  const raw = pg(`SELECT dw_sku, mfr_sku, pattern_name, color_name, product_type, width, price_retail, material, image_url, product_url, shopify_product_id FROM coordonne_catalog WHERE dw_sku IN (${inList}) ORDER BY dw_sku`);
  let rows = raw.split('\n').filter(Boolean).map(l => {
    const [dw_sku, mfr_sku, pattern_name, color_name, product_type, width, price_retail, material, image_url, product_url, shopify_product_id] = l.split('|');
    return { dw_sku, mfr_sku, pattern_name, color_name, product_type, width, price_retail, material, image_url, product_url, shopify_product_id };
  });
  const already = rows.filter(r => r.shopify_product_id && r.shopify_product_id !== '');
  rows = rows.filter(r => !r.shopify_product_id || r.shopify_product_id === '');   // idempotency
  if (LIMIT) rows = rows.slice(0, LIMIT);

  const results = []; let created = 0, err = 0;
  for (const row of rows) {
    const dwSku = row.dw_sku;
    const isMural = /mural/i.test(row.product_type);
    const productType = isMural ? 'Mural' : 'Wallcovering';
    const sellOption = isMural ? 'Panel' : 'Roll';
    const price = (parseFloat(row.price_retail) || (isMural ? 113.62 : 169)).toFixed(2);
    const width = (row.width || '').trim() || null;
    const img = (row.image_url || '').trim() || null;
    const title = `${row.pattern_name} | Coordonné`;

    // feed-first ($0) backfill: description + real mfr SKU + material from Woo Store API
    let desc = '', realSku = null, material = row.material || null;
    const wpid = (row.mfr_sku || '').replace(/^COORD-/, '');
    try {
      const wr = await fetch(`https://coordonne.com/wp-json/wc/store/v1/products/${wpid}`);
      const wj = await wr.json();
      desc = strip(wj.description) || strip(wj.short_description) || '';
      realSku = wj.sku || null;
      const qa = (wj.attributes || []).find(a => /quality|material/i.test(a.name));
      if (qa && qa.terms) material = qa.terms.map(t => t.name).join(' / ') || material;
    } catch (e) { /* leave desc empty; draft still created */ }
    if (!desc) desc = `${row.pattern_name} — from the Cotswolds collection by Ybarra & Serret for Coordonné.`;

    const tags = ['Coordonné', 'Cotswolds', 'Ybarra & Serret', productType, row.pattern_name, 'settlement-ok'];
    if (!img) tags.push('Needs-Image');
    if (!width) tags.push('Needs-Width');

    if (DRY) { results.push({ dwSku, title, productType, price, realSku, status: 'DRAFT', tags, hasImg: !!img }); process.stderr.write(`[DRY] ${dwSku} ${title} ${productType} $${price} img=${!!img}\n`); continue; }

    // 1) PG-FIRST: stamp cleaned description (keep mfr_sku = internal COORD- code)
    pg(`UPDATE coordonne_catalog SET about_vendor='${esc(desc)}', material=COALESCE(NULLIF('${esc(material)}',''),material), updated_at=now() WHERE dw_sku='${esc(dwSku)}'`);

    // 2) Shopify create — DRAFT
    const input = {
      title, descriptionHtml: `<p>${esc(desc)}</p>`, vendor: 'Coordonné', productType, status: 'DRAFT', tags,
      metafields: [
        realSku ? { namespace: 'custom', key: 'manufacturer_sku', type: 'single_line_text_field', value: realSku } : null,
        { namespace: 'custom', key: 'pattern_name', type: 'single_line_text_field', value: row.pattern_name },
        { namespace: 'custom', key: 'brand', type: 'single_line_text_field', value: 'Coordonné' },
        material ? { namespace: 'custom', key: 'material', type: 'multi_line_text_field', value: material } : null,
        width ? { namespace: 'custom', key: 'width', type: 'single_line_text_field', value: width } : null,
      ].filter(Boolean),
      productOptions: [{ name: 'Size', values: [{ name: 'Sample' }] }],
    };
    const media = img ? [{ originalSource: img, mediaContentType: 'IMAGE', alt: title }] : null;
    const cr = await gql(M_CREATE, { input, media });
    const ue = cr?.data?.productCreate?.userErrors;
    if (ue && ue.length) { err++; results.push({ dwSku, status: 'error', errors: ue }); process.stderr.write(`ERR ${dwSku}: ${JSON.stringify(ue)}\n`); continue; }
    const prod = cr?.data?.productCreate?.product;
    if (!prod) { err++; results.push({ dwSku, status: 'gql-error', errors: cr.errors }); process.stderr.write(`GQLERR ${dwSku}: ${JSON.stringify(cr.errors)}\n`); continue; }
    const pid = prod.id;

    // 3a) sample variant -> $4.25, sku {DW}-Sample, untracked
    const sampleV = prod.variants.nodes[0];
    const vr1 = await gql(M_VSET, { productId: pid, variants: [{ id: sampleV.id, price: '4.25', inventoryItem: { sku: `${dwSku}-Sample`, tracked: false } }] });
    const vue1 = vr1?.data?.productVariantsBulkUpdate?.userErrors || [];
    if (vue1.length) process.stderr.write(`VARERR-sample ${dwSku}: ${JSON.stringify(vue1)}\n`);

    // 3b) sellable variant @ price, sku {DW}, untracked
    const vr2 = await gql(M_VCREATE, { productId: pid, variants: [{ optionValues: [{ optionName: 'Size', name: sellOption }], price, inventoryItem: { sku: dwSku, tracked: false } }] });
    const vue2 = vr2?.data?.productVariantsBulkCreate?.userErrors || [];
    if (vue2.length) process.stderr.write(`VARERR-sell ${dwSku}: ${JSON.stringify(vue2)}\n`);

    // 4) PG write-back shopify_product_id
    const numId = pid.split('/').pop();
    pg(`UPDATE coordonne_catalog SET shopify_product_id='${numId}', updated_at=now() WHERE dw_sku='${esc(dwSku)}'`);

    created++;
    results.push({ dwSku, shopify_id: pid, handle: prod.handle, status: prod.status, title, productType, price, realSku, variantErrors: [...vue1, ...vue2] });
    process.stderr.write(`OK ${dwSku} -> ${prod.handle} [${prod.status}] ${productType} $${price}\n`);
    await new Promise(r => setTimeout(r, 700));
  }
  const summary = { created, err, attempted: rows.length, skipped_already_created: already.length, results };
  fs.writeFileSync(path.join(__dirname, process.env.OUTFILE || 'cotswolds-create-result.json'), JSON.stringify(summary, null, 2));
  console.log(JSON.stringify({ created, err, attempted: rows.length, skipped_already_created: already.length }, null, 2));
})();