← back to Sku Check Skill
server.js
1397 lines
try { require('dotenv').config({ path: '/root/.env' }); } catch (_) {}
const fs = require('fs');
const os = require('os');
const path = require('path');
// Canonical secrets store — holds YORKWALL_* (parsed safely, never bash-sourced).
try { require('dotenv').config({ path: path.join(os.homedir(), 'Projects/secrets-manager/.env') }); } catch (_) {}
const express = require('express');
const helmet = require('helmet');
const { Pool } = require('pg');
const fetch = require('node-fetch');
const { execFile } = require('child_process');
const app = express();
// Security headers via helmet (added 2026-05-04 overnight YOLO loop)
app.use(helmet({ contentSecurityPolicy: false }));
const PORT = process.env.PORT || 9894;
const AGENT_COLOR = '#84CC16'; // Lime
const pool = new Pool({
connectionString: (process.env.DATABASE_URL || 'postgresql://dw_admin@127.0.0.1:5432/dw_unified')
});
const SHOPIFY_TOKEN = process.env.SHOPIFY_ADMIN_TOKEN;
const SHOPIFY_SHOP = 'designer-laboratory-sandbox.myshopify.com';
const SHOPIFY_API = `https://${SHOPIFY_SHOP}/admin/api/2024-01`;
const SHOPIFY_HEADERS = {
'X-Shopify-Access-Token': SHOPIFY_TOKEN,
'Content-Type': 'application/json'
};
const SLACK_WEBHOOK = 'https://hooks.slack.com/services/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';
const GEMINI_API_KEY = process.env.GEMINI_API_KEY || '';
const GEMINI_ENDPOINT = 'https://generativelanguage.googleapis.com/v1beta/models/gemini-2.0-flash:generateContent';
// Brewster / York trade portal — the ONLY source of live trade cost + stock for
// the whole Brewster/York/Jeffrey-Stevens family. Credentials come from the
// secrets store (YORKWALL_USER / YORKWALL_PASS); never hardcode them here (the
// portal locks the account for 30 min on repeated bad logins). The live pull is
// delegated to the yw-costcheck.js scraper, which owns the abort-if-locked guard.
const YORKWALL_LOGIN_URL = 'https://www.yorkwall.com/Login/';
const YORKWALL_USERNAME = process.env.YORKWALL_USER || '6003784';
const YW_SCRIPT = path.join(os.homedir(), 'Projects/Designer-Wallcoverings/DW-Programming/ImportNewSkufromURL/yw-costcheck.js');
// Vendor catalog table mapping (vendor_code -> PostgreSQL table info)
const VENDOR_CATALOG = {
'BRE': { table: 'brewster_catalog', skuCol: 'mfr_sku', name: 'Brewster', website: 'brewsterwallcovering.com', tradePortal: 'yorkwall.com' },
'Thib': { table: 'thibaut_catalog', skuCol: 'mfr_sku', name: 'Thibaut', website: 'thibautdesign.com' },
'yor': { table: 'york_catalog', skuCol: 'mfr_sku', name: 'York', website: 'yorkwallcoverings.com', tradePortal: 'yorkwall.com' },
'YOR': { table: 'york_catalog', skuCol: 'mfr_sku', name: 'York', website: 'yorkwallcoverings.com', tradePortal: 'yorkwall.com' },
'WQ': { table: 'wallquest_catalog', skuCol: 'mfr_sku', name: 'WallQuest', website: 'wallquest.com' },
'SCH': { table: 'schumacher_catalog', skuCol: 'mfr_sku', name: 'Schumacher', website: 'fschumacher.com' },
'Sch': { table: 'schumacher_catalog', skuCol: 'mfr_sku', name: 'Schumacher', website: 'fschumacher.com' },
'PJ': { table: 'pj_catalog', skuCol: 'mfr_sku', name: 'Phillip Jeffries', website: 'phillipjeffries.com' },
'kra': { table: 'kravet_catalog', skuCol: 'mfr_sku', name: 'Kravet', website: 'kravet.com' },
'SCA': { table: 'scalamandre_catalog', skuCol: 'mfr_sku', name: 'Scalamandre', website: 'scalamandre.com' },
'jf': { table: 'jf_catalog', skuCol: 'mfr_sku', name: 'JF Fabrics', website: 'jffabrics.com' },
'Mom': { table: 'momeni_catalog', skuCol: 'mfr_sku', name: 'Momeni', website: 'momeni.com' },
'OSB': { table: 'osborne_catalog', skuCol: 'mfr_sku', name: 'Osborne & Little', website: 'osborneandlittle.com' },
'Arte': { table: 'arte_catalog', skuCol: 'arte_sku', name: 'Arte International', website: 'arte-international.com' },
'KOR': { table: 'koroseal_catalog', skuCol: 'mfr_sku', name: 'Koroseal', website: 'koroseal.com' },
'MR': { table: 'maya_catalog', skuCol: 'mfr_sku', name: 'Maya Romanoff', website: 'mayaromanoff.com' },
'RL': { table: 'rlf_catalog', skuCol: 'mfr_sku', name: 'Ralph Lauren', website: 'ralphlaurenhome.com' },
'INN': { table: 'innovations_catalog', skuCol: 'mfr_sku', name: 'Innovations', website: 'innovationsusa.com' },
'CT': { table: 'cowtan_catalog', skuCol: 'mfr_sku', name: 'Cowtan & Tout', website: 'cowtan.com' },
'CS': { table: 'coleson_catalog', skuCol: 'mfr_sku', name: 'Cole & Son', website: 'cole-and-son.com' },
'EL': { table: 'elitis_catalog', skuCol: 'mfr_sku', name: 'Elitis', website: 'elitis.fr' },
};
// Also map by vendor name for fallback
const VENDOR_NAME_MAP = {
'Brewster': 'BRE', 'Thibaut': 'Thib', 'York': 'yor', 'WallQuest': 'WQ',
'Schumacher': 'SCH', 'Schumacher Wallcoverings': 'SCH', 'Phillip Jeffries': 'PJ',
'Kravet': 'kra', 'Scalamandre': 'SCA', 'JF Fabrics': 'jf', 'Momeni': 'Mom',
'Osborne & Little': 'OSB', 'Arte': 'Arte', 'Arte International': 'Arte',
'Koroseal': 'KOR', 'Koroseal Wallcoverings': 'KOR', 'Maya Romanoff': 'MR',
'Ralph Lauren': 'RL', 'Innovations': 'INN', 'Innovationsusa': 'INN',
'Cowtan & Tout': 'CT', 'Cole & Son': 'CS', 'Elitis': 'EL',
};
function findVendorCatalog(vendorCode, vendorName) {
if (VENDOR_CATALOG[vendorCode]) return VENDOR_CATALOG[vendorCode];
if (vendorName && VENDOR_NAME_MAP[vendorName]) return VENDOR_CATALOG[VENDOR_NAME_MAP[vendorName]];
// Fuzzy match
const lv = (vendorName || '').toLowerCase();
for (const [code, config] of Object.entries(VENDOR_CATALOG)) {
if (config.name.toLowerCase().includes(lv) || lv.includes(config.name.toLowerCase())) return config;
}
return null;
}
function toTitleCase(str) {
if (!str) return '';
const small = new Set(['a','an','the','and','but','or','for','nor','in','on','at','to','by','of','up']);
return str.replace(/\w\S*/g, (txt, i) => {
if (i > 0 && small.has(txt.toLowerCase())) return txt.toLowerCase();
return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();
});
}
function cleanWallpaper(str) {
if (!str) return str;
return str.replace(/\bWallpaper\b/gi, 'Wallcovering').replace(/\bWallpapers\b/gi, 'Wallcoverings');
}
// Fetch specs from PostgreSQL vendor catalog
async function fetchSpecsFromCatalog(mfrSku, vendorCode, vendorName) {
const vc = findVendorCatalog(vendorCode, vendorName);
if (!vc) return { specs: null, vendor: null, message: `No catalog mapping for ${vendorCode}/${vendorName}` };
try {
const result = await pool.query(`SELECT * FROM ${vc.table} WHERE ${vc.skuCol} = $1 LIMIT 1`, [mfrSku.trim()]);
if (result.rows.length === 0) return { specs: null, vendor: vc, message: `${mfrSku} not found in ${vc.table}` };
const c = result.rows[0];
return {
specs: {
patternName: c.pattern_name || c.name || c.design_name || '',
colorName: c.color_name || c.color || c.colorway || '',
collection: c.collection || c.collection_name || c.book_name || '',
width: c.width || c.roll_width || '',
length: c.roll_length || c.length || '',
repeat: c.pattern_repeat || c.repeat || '',
composition: c.composition || c.content || c.material || '',
backing: c.backing || '',
fireRating: c.fire_rating || c.fire_rating_us || '',
matchType: c.match_type || '',
country: c.country_of_origin || c.country || '',
productUrl: c.product_url || c.url || '',
imageUrl: c.image_url || c.packshot_url || '',
roomSettingUrl: c.room_setting_url || c.room_image_url || '',
retailPrice: c.retail_price || c.price || null,
tradePrice: c.trade_price || c.wholesale_price || null,
costPrice: c.cost_price || null,
},
rawCatalog: c,
vendor: vc,
message: `Found in ${vc.table}`
};
} catch (e) {
return { specs: null, vendor: vc, message: `Error querying ${vc.table}: ${e.message}` };
}
}
// Fetch images from vendor catalog
async function fetchImagesFromCatalog(mfrSku, vendorCode, vendorName) {
const vc = findVendorCatalog(vendorCode, vendorName);
if (!vc) return { images: [], message: `No catalog for ${vendorCode}` };
try {
const result = await pool.query(`SELECT * FROM ${vc.table} WHERE ${vc.skuCol} = $1 LIMIT 1`, [mfrSku.trim()]);
if (result.rows.length === 0) return { images: [], message: 'Not found' };
const c = result.rows[0];
const images = [c.image_url, c.packshot_url, c.color_image_url, c.room_setting_url, c.room_image_url].filter(Boolean);
return { images, productUrl: c.product_url || c.url || '', message: `${images.length} images found` };
} catch (e) {
return { images: [], message: e.message };
}
}
// Run Gemini AI analysis on product image
async function analyzeWithGemini(imageUrl, productTitle) {
if (!imageUrl) return { analysis: null, message: 'No image URL' };
try {
// Fetch image and convert to base64
const imgResp = await fetch(imageUrl, {
headers: { 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36' },
timeout: 15000
});
if (!imgResp.ok) return { analysis: null, message: `Image fetch failed: HTTP ${imgResp.status}` };
const imgBuf = await imgResp.buffer();
const base64 = imgBuf.toString('base64');
const mimeType = imgResp.headers.get('content-type') || 'image/jpeg';
const geminiResp = await fetch(`${GEMINI_ENDPOINT}?key=${GEMINI_API_KEY}`, {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({
contents: [{
parts: [
{ inlineData: { mimeType, data: base64 } },
{ text: `As an expert interior designer, analyze this wallcovering "${productTitle || ''}".
Return ONLY valid JSON:
{
"backgroundColor": "<single dominant background color>",
"colors": ["<max 3 visible colors>"],
"styles": ["<1-2 from: Traditional, Contemporary, Coastal, Tropical, Art Deco, Mid-Century, Bohemian, Transitional, Victorian, Chinoiserie>"],
"patterns": ["<1-2 from: Botanical, Floral, Geometric, Damask, Palm, Trellis, Stripe, Abstract, Toile, Scenic, Solid, Texture>"],
"material": "<Paper, Vinyl, Grasscloth, or Non-woven>",
"description": "<2-3 sentence professional interior designer description for commercial applications>",
"tags": ["<3-5 interior design tags>"]
}` }
]
}],
generationConfig: { temperature: 0.1, maxOutputTokens: 800 }
})
});
const geminiData = await geminiResp.json();
try {
const { logGemini } = require(require('path').join(require('os').homedir(), '.claude/skills/cost-tracker/scripts/log-gemini.js'));
logGemini(geminiData, { app: 'sku-check-skill', model: 'gemini-2.0-flash', note: 'sku-image-analysis' });
} catch {}
const text = geminiData?.candidates?.[0]?.content?.parts?.[0]?.text || '';
const jsonMatch = text.match(/\{[\s\S]*\}/);
if (!jsonMatch) return { analysis: null, message: 'Gemini returned no JSON' };
const analysis = JSON.parse(jsonMatch[0]);
return { analysis, message: 'AI analysis complete' };
} catch (e) {
return { analysis: null, message: `Gemini error: ${e.message}` };
}
}
// Get full Shopify product with variants
async function getShopifyProductFull(productId) {
try {
const r = await fetch(`${SHOPIFY_API}/products/${productId}.json`, {
headers: { 'X-Shopify-Access-Token': SHOPIFY_TOKEN }
});
const data = await r.json();
return data.product || null;
} catch (e) { return null; }
}
// Basic auth
const AUTH_USER = 'admin';
const AUTH_PASS = 'DWSecure2024!';
function basicAuth(req, res, next) {
if (req.path === '/health') return next();
const auth = req.headers.authorization;
if (!auth || !auth.startsWith('Basic ')) {
res.setHeader('WWW-Authenticate', 'Basic realm="SKU Check Skill"');
return res.status(401).send('Authentication required');
}
const decoded = Buffer.from(auth.split(' ')[1], 'base64').toString();
const [user, pass] = decoded.split(':');
if (user === AUTH_USER && pass === AUTH_PASS) return next();
res.setHeader('WWW-Authenticate', 'Basic realm="SKU Check Skill"');
return res.status(401).send('Invalid credentials');
}
app.use(basicAuth);
app.use(express.json());
// 404-guard: block backup / swap / scrub-snapshot paths before static mount
app.use((req, res, next) => {
if (/\.(bak|orig|rej|swp|swo)(\.|$)|\.pre-|~$/i.test(req.path)) {
return res.status(404).send('Not found');
}
next();
});
app.use(express.static(path.join(__dirname, 'public')));
// Vendor URL mapping
const VENDOR_URLS = {
'BRE': { name: 'Brewster', url: 'https://www.brewsterwallcovering.com/search?q={MFR_SKU}', noResultsPattern: /no results|no products|0 results|nothing found/i },
'Thib': { name: 'Thibaut', url: 'https://www.thibautdesign.com/search?q={MFR_SKU}', noResultsPattern: /no results|no products|0 results|nothing found/i },
'yor': { name: 'York', url: 'https://www.yorkwallcoverings.com/search?q={MFR_SKU}', noResultsPattern: /no results|no products|0 results|nothing found/i },
'WQ': { name: 'WallQuest', url: 'https://www.wallquest.com/search?q={MFR_SKU}', noResultsPattern: /no results|no products|0 results|nothing found/i },
'SCH': { name: 'Schumacher', url: 'https://www.fschumacher.com/search?q={MFR_SKU}', noResultsPattern: /no results|no products|0 results|nothing found/i },
'Sch': { name: 'Schumacher', url: 'https://www.fschumacher.com/search?q={MFR_SKU}', noResultsPattern: /no results|no products|0 results|nothing found/i },
'PJ': { name: 'Phillip Jeffries', url: 'https://www.phillipjeffries.com/search?q={MFR_SKU}', noResultsPattern: /no results|no products|0 results|nothing found/i },
'kra': { name: 'Kravet', url: 'https://www.kravet.com/search?q={MFR_SKU}', noResultsPattern: /no results|no products|0 results|nothing found/i },
'SCA': { name: 'Scalamandre', url: 'https://www.scalamandre.com/search?q={MFR_SKU}', noResultsPattern: /no results|no products|0 results|nothing found/i },
'jf': { name: 'JF Fabrics', url: 'https://www.jffabrics.com/search?q={MFR_SKU}', noResultsPattern: /no results|no products|0 results|nothing found/i },
'Mom': { name: 'Momeni', url: 'https://www.momeni.com/search?q={MFR_SKU}', noResultsPattern: /no results|no products|0 results|nothing found/i },
'Fol': { name: 'Followers', url: 'https://www.google.com/search?q={MFR_SKU}+site:{VENDOR}', noResultsPattern: /did not match/i },
'RAD': { name: 'Radici', url: 'https://www.google.com/search?q={MFR_SKU}+Radici', noResultsPattern: /did not match/i },
'OSB': { name: 'Osborne & Little', url: 'https://www.osborneandlittle.com/search?q={MFR_SKU}', noResultsPattern: /no results|no products|0 results|nothing found/i }
};
// Active batch job tracking
let activeJob = null;
// Health endpoint
app.get('/health', (req, res) => res.json({ status: 'ok', port: PORT }));
// Lookup SKU in crossref
async function lookupSku(dwSku) {
const clean = dwSku.replace(/-/g, '');
const dash = dwSku.includes('-') ? dwSku : null;
const result = await pool.query(
`SELECT dw_sku, dw_sku_dash, mfr_sku, vendor_code, vendor_name
FROM dw_mfr_crossref
WHERE dw_sku = $1 OR dw_sku_dash = $1 OR dw_sku = $2 OR dw_sku_dash = $2
LIMIT 1`,
[dwSku, clean]
);
return result.rows[0] || null;
}
// Find Shopify product by handle/SKU
async function findShopifyProduct(dwSkuDash) {
if (!dwSkuDash) return null;
const handle = dwSkuDash.toLowerCase();
// Try exact handle match first
try {
const r = await fetch(`${SHOPIFY_API}/products.json?handle=${handle}&fields=id,handle,tags`, {
headers: { 'X-Shopify-Access-Token': SHOPIFY_TOKEN }
});
const data = await r.json();
if (data.products && data.products.length > 0) return data.products[0];
} catch (e) { /* ignore */ }
// Try GraphQL search to find product with SKU in handle or title
try {
const query = `{
products(first: 3, query: "${handle}") {
edges { node { id legacyResourceId handle tags } }
}
}`;
const r = await fetch(`https://${SHOPIFY_SHOP}/admin/api/2024-01/graphql.json`, {
method: 'POST',
headers: SHOPIFY_HEADERS,
body: JSON.stringify({ query })
});
const data = await r.json();
const edges = data?.data?.products?.edges;
if (edges && edges.length > 0) {
const node = edges[0].node;
return { id: node.legacyResourceId, handle: node.handle, tags: node.tags.join(', ') };
}
} catch (e) { /* ignore */ }
return null;
}
// Check vendor website for MFR SKU
async function checkVendorWebsite(mfrSku, vendorCode) {
const vendor = VENDOR_URLS[vendorCode];
if (!vendor) {
return { status: 'error', httpStatus: 0, url: '', message: `Unknown vendor code: ${vendorCode}` };
}
const url = vendor.url.replace('{MFR_SKU}', encodeURIComponent(mfrSku));
try {
const r = await fetch(url, {
timeout: 15000,
headers: {
'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36'
},
redirect: 'follow'
});
const httpStatus = r.status;
if (httpStatus === 404) {
return { status: 'not_found', httpStatus, url };
}
if (httpStatus !== 200) {
return { status: 'error', httpStatus, url, message: `HTTP ${httpStatus}` };
}
const body = await r.text();
// Check if page contains the MFR SKU (product exists)
if (body.includes(mfrSku) || body.includes(mfrSku.replace(/-/g, ''))) {
return { status: 'found', httpStatus, url };
}
// Check for "no results" patterns
if (vendor.noResultsPattern && vendor.noResultsPattern.test(body)) {
return { status: 'not_found', httpStatus, url };
}
// If we got a 200 but can't confirm the SKU is there, mark as found (conservative)
// The search page returned results but we couldn't verify the exact SKU
return { status: 'found', httpStatus, url, message: 'Search returned results but exact SKU not confirmed in body' };
} catch (e) {
return { status: 'error', httpStatus: 0, url, message: e.message };
}
}
// Add "Review" tag to Shopify product
async function tagReview(productId) {
try {
// Get current tags
const r1 = await fetch(`${SHOPIFY_API}/products/${productId}.json?fields=id,tags`, {
headers: { 'X-Shopify-Access-Token': SHOPIFY_TOKEN }
});
const data = await r1.json();
const currentTags = data.product?.tags || '';
if (currentTags.includes('Review')) return { success: true, message: 'Already tagged' };
const newTags = currentTags ? `${currentTags}, Review` : 'Review';
const r2 = await fetch(`${SHOPIFY_API}/products/${productId}.json`, {
method: 'PUT',
headers: SHOPIFY_HEADERS,
body: JSON.stringify({ product: { id: parseInt(productId), tags: newTags } })
});
if (r2.status === 200) return { success: true, message: 'Tagged Review' };
return { success: false, message: `HTTP ${r2.status}` };
} catch (e) {
return { success: false, message: e.message };
}
}
// Send Slack notification
async function slackNotify(message) {
try {
await fetch(SLACK_WEBHOOK, {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({ text: message })
});
return true;
} catch (e) {
console.error('Slack error:', e.message);
return false;
}
}
// Delay helper
function delay(ms) { return new Promise(r => setTimeout(r, ms)); }
// ===== API ROUTES =====
// Check single SKU
app.post('/api/check/sku', async (req, res) => {
const { dw_sku } = req.body;
if (!dw_sku) return res.status(400).json({ error: 'dw_sku required' });
// Read-only preview: run the full lookup + vendor check but write nothing —
// no sku_check_results row, no "Review" tag on Shopify, no Slack. Lets you
// see what a check WOULD do before committing the side effects.
const dryRun = req.body.dryRun === true || req.query.dryRun === '1';
try {
const crossref = await lookupSku(dw_sku);
if (!crossref) {
return res.json({ error: 'SKU not found in crossref', dw_sku });
}
const shopify = await findShopifyProduct(crossref.dw_sku_dash);
const vendorResult = await checkVendorWebsite(crossref.mfr_sku, crossref.vendor_code);
if (dryRun) {
// Report exactly what a live check WOULD do, without doing it.
const wouldTag = vendorResult.status === 'not_found' && !!shopify?.id;
return res.json({
dryRun: true,
wouldTagReview: wouldTag,
wouldSlack: wouldTag,
crossref,
shopify: shopify ? { id: shopify.id, handle: shopify.handle, tags: shopify.tags } : null,
vendorCheck: vendorResult
});
}
// Store result
const insertResult = await pool.query(
`INSERT INTO sku_check_results
(dw_sku, dw_sku_dash, mfr_sku, vendor_code, vendor_name, shopify_product_id, shopify_handle, check_status, http_status, vendor_url, checked_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, NOW())
ON CONFLICT (dw_sku) DO UPDATE SET
check_status = EXCLUDED.check_status, http_status = EXCLUDED.http_status,
vendor_url = EXCLUDED.vendor_url, checked_at = NOW(),
shopify_product_id = COALESCE(EXCLUDED.shopify_product_id, sku_check_results.shopify_product_id),
shopify_handle = COALESCE(EXCLUDED.shopify_handle, sku_check_results.shopify_handle)
RETURNING *`,
[
crossref.dw_sku, crossref.dw_sku_dash, crossref.mfr_sku,
crossref.vendor_code, crossref.vendor_name,
shopify?.id?.toString() || null, shopify?.handle || null,
vendorResult.status, vendorResult.httpStatus, vendorResult.url
]
);
const result = insertResult.rows[0];
// If not found, tag and slack
if (vendorResult.status === 'not_found' && shopify?.id) {
const tagResult = await tagReview(shopify.id);
if (tagResult.success) {
await pool.query('UPDATE sku_check_results SET tagged_review = true WHERE id = $1', [result.id]);
result.tagged_review = true;
}
const slackMsg = `🔍 SKU not found: ${crossref.dw_sku_dash} (${crossref.vendor_name}) → tagged Review`;
const slacked = await slackNotify(slackMsg);
if (slacked) {
await pool.query('UPDATE sku_check_results SET slacked = true WHERE id = $1', [result.id]);
result.slacked = true;
}
}
res.json({ result, crossref, shopify: shopify ? { id: shopify.id, handle: shopify.handle } : null, vendorCheck: vendorResult });
} catch (e) {
console.error('Check SKU error:', e);
res.status(500).json({ error: e.message });
}
});
// Check range of SKUs
app.post('/api/check/range', async (req, res) => {
const { prefix, from, to } = req.body;
if (!prefix || !from || !to) return res.status(400).json({ error: 'prefix, from, to required' });
// Query DB for actual SKUs matching this prefix in the number range
const skus = await pool.query(
`SELECT dw_sku, dw_sku_dash, mfr_sku, vendor_code, vendor_name
FROM dw_mfr_crossref
WHERE dw_sku LIKE $1
AND CAST(SUBSTRING(dw_sku FROM '[0-9]+$') AS INTEGER) BETWEEN $2 AND $3
ORDER BY dw_sku`,
[prefix + '%', from, to]
);
if (skus.rows.length === 0) {
return res.json({ error: `No SKUs found for prefix ${prefix} in range ${from}-${to}` });
}
// Start async job
const jobId = Date.now().toString();
activeJob = { id: jobId, type: 'range', prefix, from, to, total: skus.rows.length, checked: 0, found: 0, notFound: 0, errors: 0, status: 'running' };
res.json({ jobId, message: `Checking ${skus.rows.length} ${prefix} SKUs in range ${from}-${to}`, total: skus.rows.length });
// Process in background
(async () => {
for (const row of skus.rows) {
if (activeJob.status === 'stopped') break;
try {
await delay(1000); // Rate limit
const shopify = await findShopifyProduct(row.dw_sku_dash);
const vendorResult = await checkVendorWebsite(row.mfr_sku, row.vendor_code);
await pool.query(
`INSERT INTO sku_check_results
(dw_sku, dw_sku_dash, mfr_sku, vendor_code, vendor_name, shopify_product_id, shopify_handle, check_status, http_status, vendor_url, checked_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, NOW())
ON CONFLICT (dw_sku) DO UPDATE SET
check_status = EXCLUDED.check_status, http_status = EXCLUDED.http_status,
vendor_url = EXCLUDED.vendor_url, checked_at = NOW(),
shopify_product_id = COALESCE(EXCLUDED.shopify_product_id, sku_check_results.shopify_product_id),
shopify_handle = COALESCE(EXCLUDED.shopify_handle, sku_check_results.shopify_handle)`,
[row.dw_sku, row.dw_sku_dash, row.mfr_sku, row.vendor_code, row.vendor_name,
shopify?.id?.toString() || null, shopify?.handle || null,
vendorResult.status, vendorResult.httpStatus, vendorResult.url]
);
if (vendorResult.status === 'found') activeJob.found++;
else if (vendorResult.status === 'not_found') {
activeJob.notFound++;
if (shopify?.id) {
await tagReview(shopify.id);
await pool.query('UPDATE sku_check_results SET tagged_review = true WHERE dw_sku = $1', [row.dw_sku]);
}
}
else activeJob.errors++;
} catch (e) {
activeJob.errors++;
}
activeJob.checked++;
}
activeJob.status = 'complete';
// Slack summary
await slackNotify(`🔍 SKU range ${prefix} ${from}-${to}: ${activeJob.found} found, ${activeJob.notFound} missing`);
})();
});
// Check all SKUs for a vendor
app.post('/api/check/vendor', async (req, res) => {
const { vendor_code, limit } = req.body;
if (!vendor_code) return res.status(400).json({ error: 'vendor_code required' });
const maxLimit = limit || 500;
const skus = await pool.query(
`SELECT dw_sku, dw_sku_dash, mfr_sku, vendor_code, vendor_name
FROM dw_mfr_crossref
WHERE vendor_code = $1
ORDER BY dw_sku
LIMIT $2`,
[vendor_code, maxLimit]
);
if (skus.rows.length === 0) {
return res.json({ error: 'No SKUs found for vendor', vendor_code });
}
const jobId = Date.now().toString();
activeJob = { id: jobId, type: 'vendor', vendor_code, total: skus.rows.length, checked: 0, found: 0, notFound: 0, errors: 0, status: 'running' };
res.json({ jobId, message: `Checking ${skus.rows.length} ${vendor_code} SKUs`, total: skus.rows.length });
// Process in background
(async () => {
for (const row of skus.rows) {
if (activeJob.status === 'stopped') break;
try {
await delay(1000);
const shopify = await findShopifyProduct(row.dw_sku_dash);
const vendorResult = await checkVendorWebsite(row.mfr_sku, row.vendor_code);
await pool.query(
`INSERT INTO sku_check_results
(dw_sku, dw_sku_dash, mfr_sku, vendor_code, vendor_name, shopify_product_id, shopify_handle, check_status, http_status, vendor_url, checked_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, NOW())
ON CONFLICT (dw_sku) DO UPDATE SET
check_status = EXCLUDED.check_status, http_status = EXCLUDED.http_status,
vendor_url = EXCLUDED.vendor_url, checked_at = NOW()`,
[row.dw_sku, row.dw_sku_dash, row.mfr_sku, row.vendor_code, row.vendor_name,
shopify?.id?.toString() || null, shopify?.handle || null,
vendorResult.status, vendorResult.httpStatus, vendorResult.url]
);
if (vendorResult.status === 'found') activeJob.found++;
else if (vendorResult.status === 'not_found') {
activeJob.notFound++;
if (shopify?.id) {
await tagReview(shopify.id);
await pool.query('UPDATE sku_check_results SET tagged_review = true WHERE dw_sku = $1', [row.dw_sku]);
}
}
else activeJob.errors++;
} catch (e) {
activeJob.errors++;
}
activeJob.checked++;
}
activeJob.status = 'complete';
await slackNotify(`🔍 SKU vendor ${vendor_code}: ${activeJob.found} found, ${activeJob.notFound} missing`);
})();
});
// Get job status
app.get('/api/job', (req, res) => {
if (!activeJob) return res.json({ status: 'idle' });
res.json(activeJob);
});
// Stop active job
app.post('/api/job/stop', (req, res) => {
if (activeJob) {
activeJob.status = 'stopped';
res.json({ message: 'Job stopped' });
} else {
res.json({ message: 'No active job' });
}
});
// Get results with filters
app.get('/api/results', async (req, res) => {
const { status, vendor, limit, offset } = req.query;
let where = [];
let params = [];
let idx = 1;
if (status) { where.push(`check_status = $${idx++}`); params.push(status); }
if (vendor) { where.push(`vendor_code = $${idx++}`); params.push(vendor); }
const whereStr = where.length > 0 ? `WHERE ${where.join(' AND ')}` : '';
const lim = parseInt(limit) || 100;
const off = parseInt(offset) || 0;
const total = await pool.query(`SELECT COUNT(*) as count FROM sku_check_results ${whereStr}`, params);
const results = await pool.query(
`SELECT * FROM sku_check_results ${whereStr} ORDER BY checked_at DESC NULLS LAST LIMIT ${lim} OFFSET ${off}`,
params
);
res.json({ total: parseInt(total.rows[0].count), results: results.rows });
});
// Get stats
app.get('/api/stats', async (req, res) => {
const overall = await pool.query(`
SELECT
COUNT(*) as total,
COUNT(*) FILTER (WHERE check_status = 'found') as found,
COUNT(*) FILTER (WHERE check_status = 'not_found') as not_found,
COUNT(*) FILTER (WHERE check_status = 'error') as errors,
COUNT(*) FILTER (WHERE check_status = 'pending') as pending,
COUNT(*) FILTER (WHERE tagged_review = true) as tagged,
COUNT(*) FILTER (WHERE slacked = true) as slacked
FROM sku_check_results
`);
const byVendor = await pool.query(`
SELECT vendor_code, vendor_name,
COUNT(*) as total,
COUNT(*) FILTER (WHERE check_status = 'found') as found,
COUNT(*) FILTER (WHERE check_status = 'not_found') as not_found,
COUNT(*) FILTER (WHERE check_status = 'error') as errors
FROM sku_check_results
GROUP BY vendor_code, vendor_name
ORDER BY total DESC
`);
res.json({ overall: overall.rows[0], byVendor: byVendor.rows });
});
// Get vendors list from crossref
app.get('/api/vendors', async (req, res) => {
const vendors = await pool.query(`
SELECT vendor_code, vendor_name, COUNT(*) as sku_count
FROM dw_mfr_crossref
WHERE vendor_code IS NOT NULL AND vendor_code != ''
GROUP BY vendor_code, vendor_name
ORDER BY sku_count DESC
`);
res.json(vendors.rows);
});
// Manually tag a product as Review
app.post('/api/tag-review', async (req, res) => {
const { dw_sku } = req.body;
if (!dw_sku) return res.status(400).json({ error: 'dw_sku required' });
const crossref = await lookupSku(dw_sku);
if (!crossref) return res.status(404).json({ error: 'SKU not found in crossref' });
const shopify = await findShopifyProduct(crossref.dw_sku_dash);
if (!shopify) return res.status(404).json({ error: 'Shopify product not found' });
const result = await tagReview(shopify.id);
if (result.success) {
await pool.query('UPDATE sku_check_results SET tagged_review = true WHERE dw_sku = $1', [crossref.dw_sku]);
}
res.json(result);
});
// Send Slack notification for a specific SKU
app.post('/api/slack-notify', async (req, res) => {
const { dw_sku, message } = req.body;
if (message) {
const result = await slackNotify(message);
return res.json({ sent: result });
}
if (!dw_sku) return res.status(400).json({ error: 'dw_sku or message required' });
const row = await pool.query('SELECT * FROM sku_check_results WHERE dw_sku = $1 OR dw_sku_dash = $1', [dw_sku]);
if (row.rows.length === 0) return res.status(404).json({ error: 'No check result for this SKU' });
const r = row.rows[0];
const msg = `🔍 ${r.dw_sku_dash || r.dw_sku}: ${r.check_status.toUpperCase()} (${r.vendor_name})`;
const sent = await slackNotify(msg);
if (sent) {
await pool.query('UPDATE sku_check_results SET slacked = true WHERE id = $1', [r.id]);
}
res.json({ sent });
});
// ===== ACTION ENDPOINTS =====
// Helper: resolve dw_sku to full context (crossref + shopify + vendor catalog)
async function resolveSkuContext(dw_sku) {
const crossref = await lookupSku(dw_sku);
if (!crossref) return { error: 'SKU not found in crossref' };
// Get Shopify product
let shopifyProduct = null;
const checkRow = await pool.query('SELECT shopify_product_id, shopify_handle FROM sku_check_results WHERE dw_sku = $1 OR dw_sku_dash = $1', [crossref.dw_sku]);
if (checkRow.rows[0]?.shopify_product_id) {
shopifyProduct = await getShopifyProductFull(checkRow.rows[0].shopify_product_id);
}
if (!shopifyProduct) {
const sp = await findShopifyProduct(crossref.dw_sku_dash);
if (sp?.id) shopifyProduct = await getShopifyProductFull(sp.id);
}
const vc = findVendorCatalog(crossref.vendor_code, crossref.vendor_name);
return { crossref, shopifyProduct, vendorCatalog: vc };
}
// 1. Fetch Specs from PostgreSQL vendor catalog
app.post('/api/action/fetch-specs', async (req, res) => {
const { dw_sku } = req.body;
if (!dw_sku) return res.status(400).json({ error: 'dw_sku required' });
try {
const ctx = await resolveSkuContext(dw_sku);
if (ctx.error) return res.json({ error: ctx.error });
const result = await fetchSpecsFromCatalog(ctx.crossref.mfr_sku, ctx.crossref.vendor_code, ctx.crossref.vendor_name);
res.json({ ...result, crossref: ctx.crossref, shopifyId: ctx.shopifyProduct?.id });
} catch (e) { res.status(500).json({ error: e.message }); }
});
// 2. Fetch Images from vendor catalog
app.post('/api/action/fetch-images', async (req, res) => {
const { dw_sku } = req.body;
if (!dw_sku) return res.status(400).json({ error: 'dw_sku required' });
try {
const ctx = await resolveSkuContext(dw_sku);
if (ctx.error) return res.json({ error: ctx.error });
const result = await fetchImagesFromCatalog(ctx.crossref.mfr_sku, ctx.crossref.vendor_code, ctx.crossref.vendor_name);
res.json(result);
} catch (e) { res.status(500).json({ error: e.message }); }
});
// 3. AI Analyze - run Gemini on product image
app.post('/api/action/ai-analyze', async (req, res) => {
const { dw_sku } = req.body;
if (!dw_sku) return res.status(400).json({ error: 'dw_sku required' });
try {
const ctx = await resolveSkuContext(dw_sku);
if (ctx.error) return res.json({ error: ctx.error });
const imageUrl = ctx.shopifyProduct?.image?.src || ctx.shopifyProduct?.images?.[0]?.src;
const title = ctx.shopifyProduct?.title || '';
const result = await analyzeWithGemini(imageUrl, title);
res.json(result);
} catch (e) { res.status(500).json({ error: e.message }); }
});
// 4. Update Tags - merge AI + specs tags into Shopify product
app.post('/api/action/update-tags', async (req, res) => {
const { dw_sku } = req.body;
if (!dw_sku) return res.status(400).json({ error: 'dw_sku required' });
try {
const ctx = await resolveSkuContext(dw_sku);
if (ctx.error) return res.json({ error: ctx.error });
if (!ctx.shopifyProduct) return res.json({ error: 'No Shopify product found' });
// Get specs + AI analysis
const specsResult = await fetchSpecsFromCatalog(ctx.crossref.mfr_sku, ctx.crossref.vendor_code, ctx.crossref.vendor_name);
const imageUrl = ctx.shopifyProduct.image?.src || ctx.shopifyProduct.images?.[0]?.src;
const aiResult = await analyzeWithGemini(imageUrl, ctx.shopifyProduct.title);
// Build tag set
const existingTags = (ctx.shopifyProduct.tags || '').split(',').map(t => t.trim()).filter(Boolean);
const tagSet = new Set(existingTags);
// Add AI tags
if (aiResult.analysis) {
const a = aiResult.analysis;
if (a.styles) a.styles.forEach(s => tagSet.add(s));
if (a.patterns) a.patterns.forEach(p => tagSet.add(p));
if (a.colors) a.colors.forEach(c => tagSet.add(c));
if (a.backgroundColor) tagSet.add(`Background Color ${a.backgroundColor}`);
if (a.material) tagSet.add(a.material);
if (a.tags) a.tags.forEach(t => tagSet.add(t));
}
// Add spec tags
if (specsResult.specs) {
if (specsResult.specs.collection) tagSet.add(specsResult.specs.collection);
if (specsResult.specs.composition) tagSet.add(specsResult.specs.composition);
}
const newTags = Array.from(tagSet).join(', ');
// Update Shopify
const r = await fetch(`${SHOPIFY_API}/products/${ctx.shopifyProduct.id}.json`, {
method: 'PUT',
headers: SHOPIFY_HEADERS,
body: JSON.stringify({ product: { id: ctx.shopifyProduct.id, tags: newTags } })
});
const updated = await r.json();
res.json({ success: r.status === 200, tags: newTags, tagCount: tagSet.size, aiAnalysis: aiResult.analysis, message: `Updated ${tagSet.size} tags` });
} catch (e) { res.status(500).json({ error: e.message }); }
});
// 5. Update Metafields - push specs + AI data as Shopify metafields
app.post('/api/action/update-metafields', async (req, res) => {
const { dw_sku } = req.body;
if (!dw_sku) return res.status(400).json({ error: 'dw_sku required' });
try {
const ctx = await resolveSkuContext(dw_sku);
if (ctx.error) return res.json({ error: ctx.error });
if (!ctx.shopifyProduct) return res.json({ error: 'No Shopify product found' });
const specsResult = await fetchSpecsFromCatalog(ctx.crossref.mfr_sku, ctx.crossref.vendor_code, ctx.crossref.vendor_name);
const imageUrl = ctx.shopifyProduct.image?.src || ctx.shopifyProduct.images?.[0]?.src;
const aiResult = await analyzeWithGemini(imageUrl, ctx.shopifyProduct.title);
const specs = specsResult.specs || {};
const ai = aiResult.analysis || {};
const metafields = [];
// Specs metafields (global namespace)
if (specs.width) metafields.push({ namespace: 'global', key: 'width', value: specs.width, type: 'single_line_text_field' });
if (specs.length) metafields.push({ namespace: 'global', key: 'length', value: specs.length, type: 'single_line_text_field' });
if (specs.repeat) metafields.push({ namespace: 'global', key: 'repeat', value: specs.repeat, type: 'single_line_text_field' });
if (specs.composition) metafields.push({ namespace: 'global', key: 'Contents', value: specs.composition, type: 'single_line_text_field' });
if (specs.fireRating) metafields.push({ namespace: 'global', key: 'FLAMMABILITY', value: specs.fireRating, type: 'single_line_text_field' });
if (specs.matchType) metafields.push({ namespace: 'global', key: 'MATCH', value: specs.matchType, type: 'single_line_text_field' });
if (specs.country) metafields.push({ namespace: 'global', key: 'Country', value: specs.country, type: 'single_line_text_field' });
if (specs.collection) metafields.push({ namespace: 'global', key: 'Collection', value: specs.collection, type: 'single_line_text_field' });
if (specs.colorName) metafields.push({ namespace: 'global', key: 'Color-Way', value: specs.colorName, type: 'single_line_text_field' });
if (specs.backing) metafields.push({ namespace: 'global', key: 'Construction', value: specs.backing, type: 'single_line_text_field' });
// MFR SKU
metafields.push({ namespace: 'custom', key: 'manufacturer_sku', value: ctx.crossref.mfr_sku, type: 'single_line_text_field' });
metafields.push({ namespace: 'dwc', key: 'manufacturer_sku', value: ctx.crossref.mfr_sku, type: 'single_line_text_field' });
// AI metafields (dwc namespace)
if (ai.colors) metafields.push({ namespace: 'dwc', key: 'ai_generated_colors', value: JSON.stringify(ai.colors), type: 'json' });
if (ai.tags) metafields.push({ namespace: 'dwc', key: 'ai_generated_tags', value: JSON.stringify(ai.tags), type: 'json' });
if (ai.backgroundColor) metafields.push({ namespace: 'dwc', key: 'background_color', value: ai.backgroundColor, type: 'single_line_text_field' });
if (ai.styles?.[0]) metafields.push({ namespace: 'custom', key: 'design_style', value: ai.styles[0], type: 'single_line_text_field' });
if (specs.colorName) metafields.push({ namespace: 'dwc', key: 'color', value: specs.colorName, type: 'single_line_text_field' });
if (specs.patternName) metafields.push({ namespace: 'dwc', key: 'pattern_name', value: specs.patternName, type: 'single_line_text_field' });
// Push each metafield with rate limiting
let success = 0, errors = 0;
for (const mf of metafields) {
try {
const r = await fetch(`${SHOPIFY_API}/products/${ctx.shopifyProduct.id}/metafields.json`, {
method: 'POST',
headers: SHOPIFY_HEADERS,
body: JSON.stringify({ metafield: mf })
});
if (r.status === 200 || r.status === 201) success++;
else errors++;
await delay(300);
} catch { errors++; }
}
res.json({ success: true, metafieldsSet: success, metafieldsError: errors, total: metafields.length, message: `Set ${success}/${metafields.length} metafields` });
} catch (e) { res.status(500).json({ error: e.message }); }
});
// 6. Fix Title - proper format: "Pattern Color | Vendor"
app.post('/api/action/fix-title', async (req, res) => {
const { dw_sku } = req.body;
if (!dw_sku) return res.status(400).json({ error: 'dw_sku required' });
try {
const ctx = await resolveSkuContext(dw_sku);
if (ctx.error) return res.json({ error: ctx.error });
if (!ctx.shopifyProduct) return res.json({ error: 'No Shopify product found' });
const specsResult = await fetchSpecsFromCatalog(ctx.crossref.mfr_sku, ctx.crossref.vendor_code, ctx.crossref.vendor_name);
const specs = specsResult.specs || {};
const vc = ctx.vendorCatalog;
let pattern = specs.patternName || '';
let color = specs.colorName || '';
let vendor = vc?.name || ctx.crossref.vendor_name || '';
// Build title
let newTitle = '';
if (pattern && color) {
newTitle = `${toTitleCase(pattern)} ${toTitleCase(color)} | ${vendor}`;
} else if (pattern) {
newTitle = `${toTitleCase(pattern)} | ${vendor}`;
} else {
// Keep existing title but fix wallpaper and title case
newTitle = ctx.shopifyProduct.title;
}
newTitle = cleanWallpaper(newTitle);
const r = await fetch(`${SHOPIFY_API}/products/${ctx.shopifyProduct.id}.json`, {
method: 'PUT',
headers: SHOPIFY_HEADERS,
body: JSON.stringify({ product: { id: ctx.shopifyProduct.id, title: newTitle } })
});
res.json({ success: r.status === 200, oldTitle: ctx.shopifyProduct.title, newTitle, message: r.status === 200 ? 'Title updated' : `HTTP ${r.status}` });
} catch (e) { res.status(500).json({ error: e.message }); }
});
// 7. Fix Sample Variant - ensure "Sample" variant at $4.25
app.post('/api/action/fix-sample', async (req, res) => {
const { dw_sku } = req.body;
if (!dw_sku) return res.status(400).json({ error: 'dw_sku required' });
try {
const ctx = await resolveSkuContext(dw_sku);
if (ctx.error) return res.json({ error: ctx.error });
if (!ctx.shopifyProduct) return res.json({ error: 'No Shopify product found' });
const product = ctx.shopifyProduct;
const variant = product.variants?.[0];
if (!variant) return res.json({ error: 'No variants found' });
const dwSkuDash = ctx.crossref.dw_sku_dash || dw_sku;
// Check if already correct
if (variant.title === 'Sample' && variant.price === '4.25') {
return res.json({ success: true, message: 'Sample variant already correct', variant: { title: variant.title, price: variant.price, sku: variant.sku } });
}
// Update product to have Sample variant
const r = await fetch(`${SHOPIFY_API}/products/${product.id}.json`, {
method: 'PUT',
headers: SHOPIFY_HEADERS,
body: JSON.stringify({
product: {
id: product.id,
options: [{ name: 'Type', values: ['Sample'] }],
variants: [{
id: variant.id,
option1: 'Sample',
price: '4.25',
sku: `${dwSkuDash}-Sample`
}]
}
})
});
const updated = await r.json();
const uv = updated.product?.variants?.[0];
res.json({
success: r.status === 200,
oldVariant: { title: variant.title, price: variant.price, sku: variant.sku },
newVariant: uv ? { title: uv.title, price: uv.price, sku: uv.sku } : null,
message: r.status === 200 ? 'Sample variant fixed' : `HTTP ${r.status}`
});
} catch (e) { res.status(500).json({ error: e.message }); }
});
// 8. Run ALL - execute full pipeline on a SKU
app.post('/api/action/run-all', async (req, res) => {
const { dw_sku } = req.body;
if (!dw_sku) return res.status(400).json({ error: 'dw_sku required' });
try {
const ctx = await resolveSkuContext(dw_sku);
if (ctx.error) return res.json({ error: ctx.error });
if (!ctx.shopifyProduct) return res.json({ error: 'No Shopify product found' });
const results = { steps: {} };
const productId = ctx.shopifyProduct.id;
const mfrSku = ctx.crossref.mfr_sku;
const vc = ctx.vendorCatalog;
// Step 1: Fetch specs
const specsResult = await fetchSpecsFromCatalog(mfrSku, ctx.crossref.vendor_code, ctx.crossref.vendor_name);
results.steps.specs = { done: !!specsResult.specs, message: specsResult.message, data: specsResult.specs };
// Step 2: Fetch images
const imagesResult = await fetchImagesFromCatalog(mfrSku, ctx.crossref.vendor_code, ctx.crossref.vendor_name);
results.steps.images = { done: imagesResult.images.length > 0, count: imagesResult.images.length, message: imagesResult.message };
// Step 3: AI analyze
const imageUrl = ctx.shopifyProduct.image?.src || ctx.shopifyProduct.images?.[0]?.src;
const aiResult = await analyzeWithGemini(imageUrl, ctx.shopifyProduct.title);
results.steps.ai = { done: !!aiResult.analysis, message: aiResult.message, data: aiResult.analysis };
// Step 4: Fix title
const specs = specsResult.specs || {};
let newTitle = ctx.shopifyProduct.title;
if (specs.patternName) {
newTitle = specs.colorName
? `${toTitleCase(specs.patternName)} ${toTitleCase(specs.colorName)} | ${vc?.name || ctx.crossref.vendor_name}`
: `${toTitleCase(specs.patternName)} | ${vc?.name || ctx.crossref.vendor_name}`;
newTitle = cleanWallpaper(newTitle);
}
// Step 5: Update tags
const existingTags = (ctx.shopifyProduct.tags || '').split(',').map(t => t.trim()).filter(Boolean);
const tagSet = new Set(existingTags);
if (aiResult.analysis) {
const a = aiResult.analysis;
(a.styles || []).forEach(s => tagSet.add(s));
(a.patterns || []).forEach(p => tagSet.add(p));
(a.colors || []).forEach(c => tagSet.add(c));
if (a.backgroundColor) tagSet.add(`Background Color ${a.backgroundColor}`);
if (a.material) tagSet.add(a.material);
(a.tags || []).forEach(t => tagSet.add(t));
}
if (specs.collection) tagSet.add(specs.collection);
// Push title + tags in one call
const titleTagResp = await fetch(`${SHOPIFY_API}/products/${productId}.json`, {
method: 'PUT',
headers: SHOPIFY_HEADERS,
body: JSON.stringify({ product: { id: productId, title: newTitle, tags: Array.from(tagSet).join(', ') } })
});
results.steps.title = { done: titleTagResp.status === 200, oldTitle: ctx.shopifyProduct.title, newTitle };
results.steps.tags = { done: titleTagResp.status === 200, count: tagSet.size };
await delay(500);
// Step 6: Metafields
const ai = aiResult.analysis || {};
const metafields = [];
if (specs.width) metafields.push({ namespace: 'global', key: 'width', value: specs.width, type: 'single_line_text_field' });
if (specs.length) metafields.push({ namespace: 'global', key: 'length', value: specs.length, type: 'single_line_text_field' });
if (specs.repeat) metafields.push({ namespace: 'global', key: 'repeat', value: specs.repeat, type: 'single_line_text_field' });
if (specs.composition) metafields.push({ namespace: 'global', key: 'Contents', value: specs.composition, type: 'single_line_text_field' });
if (specs.fireRating) metafields.push({ namespace: 'global', key: 'FLAMMABILITY', value: specs.fireRating, type: 'single_line_text_field' });
if (specs.matchType) metafields.push({ namespace: 'global', key: 'MATCH', value: specs.matchType, type: 'single_line_text_field' });
if (specs.country) metafields.push({ namespace: 'global', key: 'Country', value: specs.country, type: 'single_line_text_field' });
if (specs.collection) metafields.push({ namespace: 'global', key: 'Collection', value: specs.collection, type: 'single_line_text_field' });
if (specs.colorName) metafields.push({ namespace: 'global', key: 'Color-Way', value: specs.colorName, type: 'single_line_text_field' });
metafields.push({ namespace: 'custom', key: 'manufacturer_sku', value: mfrSku, type: 'single_line_text_field' });
metafields.push({ namespace: 'dwc', key: 'manufacturer_sku', value: mfrSku, type: 'single_line_text_field' });
if (ai.colors) metafields.push({ namespace: 'dwc', key: 'ai_generated_colors', value: JSON.stringify(ai.colors), type: 'json' });
if (ai.tags) metafields.push({ namespace: 'dwc', key: 'ai_generated_tags', value: JSON.stringify(ai.tags), type: 'json' });
if (ai.backgroundColor) metafields.push({ namespace: 'dwc', key: 'background_color', value: ai.backgroundColor, type: 'single_line_text_field' });
let mfSuccess = 0;
for (const mf of metafields) {
try {
const r = await fetch(`${SHOPIFY_API}/products/${productId}/metafields.json`, { method: 'POST', headers: SHOPIFY_HEADERS, body: JSON.stringify({ metafield: mf }) });
if (r.status === 200 || r.status === 201) mfSuccess++;
await delay(300);
} catch {}
}
results.steps.metafields = { done: true, set: mfSuccess, total: metafields.length };
// Step 7: Fix sample variant
const variant = ctx.shopifyProduct.variants?.[0];
if (variant && (variant.title !== 'Sample' || variant.price !== '4.25')) {
const dwSkuDash = ctx.crossref.dw_sku_dash || dw_sku;
await delay(500);
const sampleResp = await fetch(`${SHOPIFY_API}/products/${productId}.json`, {
method: 'PUT',
headers: SHOPIFY_HEADERS,
body: JSON.stringify({
product: { id: productId, options: [{ name: 'Type', values: ['Sample'] }], variants: [{ id: variant.id, option1: 'Sample', price: '4.25', sku: `${dwSkuDash}-Sample` }] }
})
});
results.steps.sample = { done: sampleResp.status === 200, message: 'Sample variant fixed' };
} else {
results.steps.sample = { done: true, message: 'Already correct' };
}
// Slack notify
const doneSteps = Object.values(results.steps).filter(s => s.done).length;
await slackNotify(`🔧 Updated ${dw_sku}: ${doneSteps}/7 steps done`);
results.success = true;
results.summary = `${doneSteps}/7 steps completed`;
res.json(results);
} catch (e) { res.status(500).json({ error: e.message }); }
});
// Get vendor website URL for a SKU
app.post('/api/action/vendor-url', async (req, res) => {
const { dw_sku } = req.body;
if (!dw_sku) return res.status(400).json({ error: 'dw_sku required' });
try {
const crossref = await lookupSku(dw_sku);
if (!crossref) return res.json({ error: 'SKU not found' });
const vc = findVendorCatalog(crossref.vendor_code, crossref.vendor_name);
const vendorUrl = VENDOR_URLS[crossref.vendor_code];
const searchUrl = vendorUrl ? vendorUrl.url.replace('{MFR_SKU}', encodeURIComponent(crossref.mfr_sku)) : null;
res.json({
vendorName: vc?.name || crossref.vendor_name,
vendorWebsite: vc?.website ? `https://www.${vc.website}` : null,
searchUrl,
mfrSku: crossref.mfr_sku
});
} catch (e) { res.status(500).json({ error: e.message }); }
});
// ===== LIVE CHECK (pills app) =====
// Resolve a free-text query that may be EITHER a DW number (dw_sku / dw_sku_dash)
// OR a manufacturer number (mfr_sku), dash-insensitive, against the crossref.
async function resolveQuery(q) {
const raw = (q || '').trim();
if (!raw) return null;
const clean = raw.replace(/-/g, '');
// 1) DW-number match first (that's the DW-native identifier).
let r = await pool.query(
`SELECT dw_sku, dw_sku_dash, mfr_sku, vendor_code, vendor_name
FROM dw_mfr_crossref
WHERE dw_sku = $1 OR dw_sku_dash = $1 OR REPLACE(dw_sku,'-','') = $2
LIMIT 1`, [raw, clean]);
if (r.rows[0]) return { matchedBy: 'dw', ...r.rows[0] };
// 2) Fall back to a manufacturer-number match.
r = await pool.query(
`SELECT dw_sku, dw_sku_dash, mfr_sku, vendor_code, vendor_name
FROM dw_mfr_crossref
WHERE mfr_sku = $1 OR REPLACE(mfr_sku,'-','') = $2
LIMIT 1`, [raw, clean]);
if (r.rows[0]) return { matchedBy: 'mfr', ...r.rows[0] };
return null;
}
// Live-portal guard state. The yorkwall trade portal LOCKS the account for 30 min
// on repeated logins and RESETS the timer if you retry while locked — so the app
// must be physically unable to hammer it. One in-flight live check at a time, a
// minimum gap between attempts, and a 30-min backoff whenever a run reports locked.
let liveInFlight = false;
let lastLiveAttemptMs = 0;
let lockoutUntilMs = 0;
const LIVE_MIN_GAP_MS = 20 * 1000; // ignore rapid double-clicks
const LIVE_LOCKOUT_MS = 30 * 60 * 1000; // honor the portal's 30-min lockout
// Cache dir where the overnight job (and app-triggered pulls) drop portal results.
const YW_RESULTS_DIR = path.join(path.dirname(YW_SCRIPT), 'yw-results');
// Parse the yorkwall product page's plain text into structured trade price + stock.
// The portal shows MSRP (retail), a NET PRICE table (discount% + trade cost), and
// an INVENTORY table (batches × Quantity Available). Values sit in unlabeled table
// cells, so we scope by section header rather than by keyword-per-cell.
function parseYorkwall(t) {
if (!t) return null;
const num = s => +String(s).replace(/,/g, '');
const P = {};
let m = t.match(/MSRP:\s*\$?\s*([\d,]+(?:\.\d+)?)\s*(single roll|double roll|yard|each|roll)?/i);
if (m) { P.msrp = num(m[1]); if (m[2]) P.unit = m[2]; }
// Net price = first "<discount%> <price> <unit>" row AFTER the NET PRICE header.
const netSection = (t.split(/NET PRICE/i)[1] || '');
const net = netSection.match(/(\d{1,3})\s+([\d,]+(?:\.\d+)?)\s+(single roll|double roll|yard|each)/i);
if (net) { P.discountPct = num(net[1]); P.netPrice = num(net[2]); P.netUnit = net[3]; }
// Quantity available = sum of integer "<n> Single Roll" rows between INVENTORY/PRICING.
const invBlock = (t.split(/INVENTORY/i)[1] || '').split(/PRICING/i)[0] || '';
const qtys = [...invBlock.matchAll(/([\d,]+)\s+single roll/gi)].map(x => num(x[1]));
if (qtys.length) { P.quantityAvailable = qtys.reduce((a, b) => a + b, 0); P.batches = qtys; }
m = t.match(/Roll Width\s+([^\n]+)/i); if (m) P.rollWidth = m[1].trim();
m = t.match(/Roll Length\s+([^\n]+)/i); if (m) P.rollLength = m[1].trim();
return P;
}
// Read the most-recent saved portal result for this MFR (from the 3 AM overnight
// job or a prior app pull). Matches by the file's `mfr` field, newest by mtime —
// so the pills app can show the last known live number without hitting the portal.
function readLastLive(mfrSku) {
try {
if (!fs.existsSync(YW_RESULTS_DIR)) return null;
let best = null;
for (const f of fs.readdirSync(YW_RESULTS_DIR)) {
if (!f.endsWith('.json')) continue;
const fp = path.join(YW_RESULTS_DIR, f);
let st, data;
try { st = fs.statSync(fp); data = JSON.parse(fs.readFileSync(fp, 'utf8')); } catch (_) { continue; }
if (!data || data.mfr !== mfrSku) continue;
if (!best || st.mtimeMs > best.mtimeMs) best = { mtimeMs: st.mtimeMs, data };
}
if (!best) return null;
const d = best.data;
return {
at: new Date(best.mtimeMs).toISOString(),
ageMin: Math.round((Date.now() - best.mtimeMs) / 60000),
loggedIn: !!d.loggedIn,
locked: !!d.locked,
priceStock: Array.isArray(d.priceStock) ? d.priceStock : [],
parsed: d.bodyText ? parseYorkwall(d.bodyText) : null,
abort: d.abort || null,
};
} catch (_) { return null; }
}
// Persist a successful app-triggered pull so it becomes the next "last live".
function saveLive(mfrSku, data) {
try {
fs.mkdirSync(YW_RESULTS_DIR, { recursive: true });
fs.writeFileSync(path.join(YW_RESULTS_DIR, `${mfrSku}-app-${Date.now()}.json`), JSON.stringify(data));
} catch (_) { /* best-effort cache */ }
}
// Run the yorkwall scraper for one MFR sku (Brewster/York family only).
function runYorkwallLive(mfrSku) {
return new Promise((resolve) => {
// Use the exact node binary running this server (process.execPath) rather than
// relying on 'node' being on PATH — launchd/pm2 run with a stripped PATH.
execFile(process.execPath, [YW_SCRIPT, mfrSku], {
cwd: path.dirname(YW_SCRIPT),
timeout: 90000,
maxBuffer: 4 * 1024 * 1024,
}, (err, stdout) => {
let parsed = null;
try { parsed = JSON.parse(stdout); } catch (_) {
// Tolerate any non-JSON preamble (e.g. dotenv tips) by grabbing the last
// top-level {...} block that runs to end-of-output.
const m = stdout && stdout.match(/\{[\s\S]*\}\s*$/);
if (m) { try { parsed = JSON.parse(m[0]); } catch (__) { /* still bad */ } }
}
if (!parsed) return resolve({ ok: false, error: err ? err.message : 'no JSON from scraper', raw: (stdout || '').slice(0, 500) });
resolve({ ok: true, data: parsed });
});
});
}
// POST /api/check/live { query, stock, price }
// Always returns the cached catalog price/stock instantly; attempts the LIVE
// yorkwall pull only when asked, only for the Brewster/York family, and only when
// the guard permits (not in-flight, not cooling down, past the min gap).
app.post('/api/check/live', async (req, res) => {
const { query } = req.body;
const wantStock = req.body.stock === true || req.body.stock === 'true';
const wantPrice = req.body.price === true || req.body.price === 'true';
if (!query) return res.status(400).json({ error: 'query (MFR or DW number) required' });
try {
const match = await resolveQuery(query);
if (!match) return res.json({ error: `No crossref match for "${query}"`, query });
const vc = findVendorCatalog(match.vendor_code, match.vendor_name);
const out = {
query,
resolved: {
matchedBy: match.matchedBy,
dw_sku: match.dw_sku_dash || match.dw_sku,
mfr_sku: match.mfr_sku,
vendor: vc?.name || match.vendor_name || match.vendor_code,
vendor_code: match.vendor_code,
},
want: { stock: wantStock, price: wantPrice },
};
// Cached tier — instant, always present when a catalog row exists.
const specsResult = await fetchSpecsFromCatalog(match.mfr_sku, match.vendor_code, match.vendor_name);
const s = specsResult.specs || {};
out.cached = {
source: vc?.table || null,
retailPrice: s.retailPrice ?? null,
tradePrice: s.tradePrice ?? null,
costPrice: s.costPrice ?? null,
productUrl: s.productUrl || null,
message: specsResult.message,
};
// Last-live tier — most recent saved portal pull (overnight job or prior app
// pull), shown even when the portal is locked / not queried this call.
const last = readLastLive(match.mfr_sku);
if (last) out.lastLive = last;
// Live tier — only if the user asked for stock/price.
if (wantStock || wantPrice) {
const hasPortal = !!(vc && vc.tradePortal); // Brewster/York family only
if (!hasPortal) {
out.live = { attempted: false, reason: `No live trade portal wired for ${out.resolved.vendor} — showing cached only.` };
} else if (liveInFlight) {
out.live = { attempted: false, reason: 'A live check is already running — one at a time to protect the portal login. Try again in a moment.' };
} else if (Date.now() < lockoutUntilMs) {
const mins = Math.ceil((lockoutUntilMs - Date.now()) / 60000);
out.live = { attempted: false, reason: `Portal is in a lockout cooldown — retry in ~${mins} min. (Repeated logins reset the lock, so live checks are paused.)` };
} else if (Date.now() - lastLiveAttemptMs < LIVE_MIN_GAP_MS) {
const secs = Math.ceil((LIVE_MIN_GAP_MS - (Date.now() - lastLiveAttemptMs)) / 1000);
out.live = { attempted: false, reason: `Too soon after the last live check — wait ${secs}s.` };
} else {
liveInFlight = true;
lastLiveAttemptMs = Date.now();
try {
const r = await runYorkwallLive(match.mfr_sku);
if (r.ok && r.data) {
const d = r.data;
// If the scraper reports a lockout, arm the 30-min backoff.
if (d.locked || d.abort === 'STILL_LOCKED') lockoutUntilMs = Date.now() + LIVE_LOCKOUT_MS;
// Write-through: cache a successful pull as the next "last live".
if (d.loggedIn) { saveLive(match.mfr_sku, d); out.lastLive = readLastLive(match.mfr_sku); }
out.live = {
attempted: true,
loggedIn: !!d.loggedIn,
locked: !!d.locked,
authError: !!d.authError,
abort: d.abort || null,
priceStock: Array.isArray(d.priceStock) ? d.priceStock : [],
parsed: d.bodyText ? parseYorkwall(d.bodyText) : null,
pageTitle: d.pageTitle || null,
message: d.loggedIn
? 'Live portal pull complete.'
: (d.locked ? 'Portal is locked — backing off 30 min.' : (d.authError ? 'Portal rejected the login.' : 'Could not reach the product page.')),
};
} else {
out.live = { attempted: true, error: r.error || 'scraper failed', message: 'Live scraper did not return data.' };
}
} finally {
liveInFlight = false;
}
}
}
res.json(out);
} catch (e) {
console.error('Live check error:', e);
res.status(500).json({ error: e.message });
}
});
// Friendly route for the pills app (public/pills.html is also served statically).
app.get('/pills', (req, res) => res.sendFile(path.join(__dirname, 'public', 'pills.html')));
// Add unique constraint for upsert support
(async () => {
try {
await pool.query('ALTER TABLE sku_check_results ADD CONSTRAINT sku_check_results_dw_sku_unique UNIQUE (dw_sku)');
} catch (e) {
// Constraint may already exist
}
})();
// Bind to localhost by default: on Kamatera this port is fronted by `tailscale
// serve` (TS-IP:9963 -> localhost:9963), so a wildcard bind collides with
// tailscaled and also needlessly exposes this admin dashboard on the public
// interface. Override with HOST=0.0.0.0 for a truly public bind.
const HOST = process.env.HOST || '127.0.0.1';
app.listen(PORT, HOST, () => {
console.log(`SKU Check Skill running on ${HOST}:${PORT}`);
});