`lib/shopify.js`

175 lines
// Shopify Admin API writes for the discontinued-agent.
//
// When a vendor reply is a HIGH-CONFIDENCE explicit discontinuation and its
// mfr# maps cleanly to Shopify product(s), we:
//   WRITE 1 — set product status -> ARCHIVED (pulls it from the live store)
//   WRITE 2 — stamp metafield custom.date_discontinued (date) = today
//
// SAFETY: every write is dry-run unless SHOPIFY_AUTOCOMMIT=1. The mfr#->product
// bridge is the local dw_unified mirror (shopify_products.mfr_sku, indexed) with
// a secondary match on the manufacturer_sku metafield (mirror.mfr_sku is blank
// on ~22% of rows even when the real mfr# lives in a metafield). Live status is
// re-read before any archive so a stale mirror never drives a bad write.
//
// Creds are read from the secrets-manager master .env (never hardcoded). This
// is the LIVE production store (designer-laboratory-sandbox is a legacy name).
//
// Cost: Shopify Admin API + local Postgres mirror = $0 per call.

import { readFileSync } from 'node:fs';
import { spawnSync } from 'node:child_process';

const API_VERSION = process.env.SHOPIFY_API_VERSION || '2024-10';
const MF_NAMESPACE = 'custom';
const MF_KEY = 'date_discontinued';
const PSQL = process.env.PSQL_BIN || '/opt/homebrew/opt/postgresql@14/bin/psql';
const MIRROR_DSN = process.env.DW_UNIFIED_DSN || 'host=/tmp dbname=dw_unified';
// Sanity cap: a single mfr# resolving to more than this many ACTIVE products is
// suspicious (broad/garbage match) -> queue instead of auto-archiving.
export const MATCH_CAP = Number(process.env.SHOPIFY_MATCH_CAP || 25);

const SECRETS_ENV = process.env.SECRETS_ENV || '/Users/macstudio3/Projects/secrets-manager/.env';

function fromSecrets(key) {
  if (process.env[key]) return process.env[key];
  try {
    const raw = readFileSync(SECRETS_ENV, 'utf8');
    for (const line of raw.split('\n')) {
      const m = line.match(/^([A-Za-z_][A-Za-z0-9_]*)=(.*)$/);
      if (m && m[1] === key) {
        let v = m[2].trim();
        if ((v.startsWith('"') && v.endsWith('"')) || (v.startsWith("'") && v.endsWith("'"))) v = v.slice(1, -1);
        return v;
      }
    }
  } catch { /* ignore */ }
  return null;
}

function creds() {
  const domain =
    fromSecrets('SHOPIFY_STORE_DOMAIN') || fromSecrets('SHOPIFY_STORE') || 'designer-laboratory-sandbox.myshopify.com';
  const token = fromSecrets('SHOPIFY_ADMIN_TOKEN');
  if (!token) throw new Error('SHOPIFY_ADMIN_TOKEN not found in secrets-manager .env');
  return { domain, token };
}

export function today() {
  // Shopify `date` metafields want ISO YYYY-MM-DD.
  return new Date().toISOString().slice(0, 10);
}

async function gql(query, variables = {}) {
  const { domain, token } = creds();
  const res = await fetch(`https://${domain}/admin/api/${API_VERSION}/graphql.json`, {
    method: 'POST',
    headers: { 'X-Shopify-Access-Token': token, 'Content-Type': 'application/json' },
    body: JSON.stringify({ query, variables }),
  });
  const data = await res.json();
  if (data.errors) throw new Error('Shopify GraphQL: ' + JSON.stringify(data.errors));
  return data.data;
}

// Idempotently ensure the custom.date_discontinued metafield DEFINITION exists
// (so the stamped value renders as a typed Date in admin). Safe to call every run.
export async function ensureDefinition() {
  const d = await gql(
    `mutation($def: MetafieldDefinitionInput!){
       metafieldDefinitionCreate(definition:$def){
         createdDefinition{ id } userErrors{ code message }
       }
     }`,
    {
      def: {
        name: 'Date Discontinued',
        namespace: MF_NAMESPACE,
        key: MF_KEY,
        type: 'date',
        ownerType: 'PRODUCT',
        description: 'Date the vendor confirmed this product discontinued (set by discontinued-agent).',
      },
    }
  );
  const errs = d.metafieldDefinitionCreate.userErrors || [];
  const taken = errs.some((e) => e.code === 'TAKEN');
  return { created: !!d.metafieldDefinitionCreate.createdDefinition, alreadyExisted: taken, errors: errs.filter((e) => e.code !== 'TAKEN') };
}

// Mirror lookup: mfr# -> ACTIVE Shopify products. Uses spawnSync (no shell) with
// psql :'mfr' safe-quoting; matches the mfr_sku column OR the manufacturer_sku
// metafield. Returns [{ gid, dwSku, viaColumn }].
export function findActiveProductsByMfr(mfr) {
  const sql =
    "SELECT shopify_id, coalesce(dw_sku,''), (upper(mfr_sku)=upper(:'mfr'))::int " +
    'FROM shopify_products ' +
    "WHERE status='ACTIVE' AND (" +
    "upper(mfr_sku)=upper(:'mfr') OR " +
    "upper(coalesce(metafields->'manufacturer_sku'->>'value','')) = upper(:'mfr'))";
  const r = spawnSync(PSQL, [MIRROR_DSN, '-v', `mfr=${mfr}`, '-Atc', sql, '-F', '\t'], { encoding: 'utf8' });
  if (r.status !== 0) throw new Error('mirror lookup failed: ' + (r.stderr || '').trim());
  return (r.stdout || '')
    .split('\n')
    .filter(Boolean)
    .map((line) => {
      const [gid, dwSku, viaCol] = line.split('\t');
      return { gid, dwSku, viaColumn: viaCol === '1' };
    });
}

// Live-read a product's status + current discontinued-date metafield.
export async function getProduct(gid) {
  const d = await gql(
    `query($id:ID!){ product(id:$id){ id status title
        metafield(namespace:"${MF_NAMESPACE}", key:"${MF_KEY}"){ value } } }`,
    { id: gid }
  );
  return d.product;
}

// Archive + stamp ONE product. Dry-run unless commit=true. Returns a diff record.
export async function archiveAndStamp(gid, { commit = false } = {}) {
  const before = await getProduct(gid);
  if (!before) return { gid, error: 'product not found live (stale mirror)' };

  const stamp = today();
  const needArchive = before.status !== 'ARCHIVED';
  const needStamp = !before.metafield || !before.metafield.value;
  const plan = {
    gid,
    title: before.title,
    statusBefore: before.status,
    statusAfter: needArchive ? 'ARCHIVED' : before.status,
    dateBefore: before.metafield?.value || null,
    dateAfter: needStamp ? stamp : before.metafield?.value || stamp,
    willArchive: needArchive,
    willStamp: needStamp,
    committed: false,
  };
  if (!commit || (!needArchive && !needStamp)) return plan;

  if (needArchive) {
    const d = await gql(
      `mutation($input:ProductInput!){ productUpdate(input:$input){ product{ id status } userErrors{ field message } } }`,
      { input: { id: gid, status: 'ARCHIVED' } }
    );
    const e = d.productUpdate.userErrors;
    if (e && e.length) return { ...plan, error: 'archive: ' + JSON.stringify(e) };
  }
  if (needStamp) {
    const d = await gql(
      `mutation($m:[MetafieldsSetInput!]!){ metafieldsSet(metafields:$m){ metafields{ id value } userErrors{ field message } } }`,
      { m: [{ ownerId: gid, namespace: MF_NAMESPACE, key: MF_KEY, type: 'date', value: stamp }] }
    );
    const e = d.metafieldsSet.userErrors;
    if (e && e.length) return { ...plan, error: 'stamp: ' + JSON.stringify(e) };
  }
  return { ...plan, committed: true };
}

// Health/ping — resolves creds + a cheap shop query.
export async function ping() {
  const d = await gql(`{ shop { name myshopifyDomain } }`);
  return { ok: true, shop: d.shop.name, domain: d.shop.myshopifyDomain };
}