← back to Letsbegin
sync_brands_page.js
459 lines
#!/usr/bin/env node
/**
* sync_brands_page.js
*
* Reads vendors from vendor_registry WHERE show_on_brands_page = true,
* fetches actual Shopify collection handles (via smart collection rules),
* generates alphabetized HTML with correct links,
* and pushes it to the Shopify "Designer Brands" page via REST API.
*/
const { Pool } = require('pg');
const https = require('https');
// --- Configuration ---
const DB_URL = process.env.DATABASE_URL;
const SHOPIFY_STORE = 'designer-laboratory-sandbox.myshopify.com';
const SHOPIFY_TOKEN = process.env.SHOPIFY_CONTENT_TOKEN;
const SHOPIFY_PRODUCT_TOKEN = process.env.SHOPIFY_PRODUCT_TOKEN;
const BRANDS_PAGE_ID = 20309475440; // existing "Designer Brands" page
const API_VERSION = '2024-10';
if (!DB_URL || !SHOPIFY_TOKEN || !SHOPIFY_PRODUCT_TOKEN) {
throw new Error('DATABASE_URL, SHOPIFY_CONTENT_TOKEN, and SHOPIFY_PRODUCT_TOKEN environment variables are required');
}
// --- Helpers ---
/**
* Convert a vendor name to a Shopify-compatible collection handle (fallback only).
* e.g. "Cole & Son" -> "cole-son", "Phillip Jeffries" -> "phillip-jeffries"
*/
function vendorNameToHandle(name) {
return name
.toLowerCase()
.normalize('NFD')
.replace(/[\u0300-\u036f]/g, '') // strip accents (e.g. Coordonné -> Coordonne)
.replace(/&/g, '') // remove ampersands
.replace(/[^a-z0-9]+/g, '-') // non-alphanumeric -> hyphens
.replace(/^-+|-+$/g, '') // trim leading/trailing hyphens
.replace(/-{2,}/g, '-'); // collapse multiple hyphens
}
/**
* Fetch all smart collections with vendor rules from Shopify.
* Returns a map of normalized vendor name -> array of { handle, title }.
* Multiple collections can exist per vendor; we collect them all.
*/
async function fetchVendorCollectionMap() {
const vendorMap = {};
let url = `/admin/api/${API_VERSION}/smart_collections.json?limit=250`;
while (url) {
const { body, nextUrl } = await shopifyRequest('GET', url, null, true);
const collections = body.smart_collections || [];
for (const c of collections) {
const vendorRule = (c.rules || []).find(r => r.column === 'vendor');
if (vendorRule) {
const key = vendorRule.condition.toLowerCase().trim();
if (!vendorMap[key]) vendorMap[key] = [];
vendorMap[key].push({ handle: c.handle, title: c.title });
}
}
url = nextUrl;
}
return vendorMap;
}
/**
* From an array of collections for a vendor, pick the best handle.
* Prefers handles that contain the vendor name slug.
*/
function pickBestHandle(collections, vendorSlug) {
if (collections.length === 1) return collections[0].handle;
// 1. Exact slug match (e.g. "koroseal" matches handle "koroseal")
const exactMatch = collections.find(c => c.handle === vendorSlug);
if (exactMatch) return exactMatch.handle;
// 2. Handle starts with vendor slug (e.g. "koroseal-wallpaper-collection")
const startsMatch = collections.find(c => c.handle.startsWith(vendorSlug));
if (startsMatch) return startsMatch.handle;
// 3. Handle contains vendor slug
const containsMatch = collections.find(c => c.handle.includes(vendorSlug));
if (containsMatch) return containsMatch.handle;
// 4. Title contains vendor name (case-insensitive)
const titleMatch = collections.find(c =>
c.title.toLowerCase().includes(vendorSlug.replace(/-/g, ' '))
);
if (titleMatch) return titleMatch.handle;
// 5. Fallback to first
return collections[0].handle;
}
/**
* Look up the actual Shopify collection handle for a vendor name.
* Tries exact match, then stripped suffixes, then falls back to generated handle.
*/
function resolveCollectionHandle(displayName, vendorCollectionMap) {
const nameLC = displayName.toLowerCase().trim();
const vendorSlug = vendorNameToHandle(displayName);
// Exact match on vendor name
if (vendorCollectionMap[nameLC]) {
return pickBestHandle(vendorCollectionMap[nameLC], vendorSlug);
}
// Try without common suffixes (e.g. "Stout Textiles" -> "Stout")
const suffixes = [' wallcoverings', ' wallpaper', ' wallpapers', ' fabrics',
' textiles', ' online', ' commercial wallcovering', ' commercial wallpaper',
' wallpaper and fabrics', ' home wallpaper and fabrics', ' murals'];
for (const suffix of suffixes) {
if (nameLC.endsWith(suffix)) {
const stripped = nameLC.slice(0, -suffix.length);
if (vendorCollectionMap[stripped]) {
return pickBestHandle(vendorCollectionMap[stripped], vendorSlug);
}
}
}
// Try adding common suffixes to find vendor in map
for (const suffix of suffixes) {
if (vendorCollectionMap[nameLC + suffix]) {
return pickBestHandle(vendorCollectionMap[nameLC + suffix], vendorSlug);
}
}
// No collection found
return null;
}
/**
* Fetch all published Shopify pages (paginated).
* Returns a Set of page handles.
*/
async function fetchPageHandles() {
const handles = new Set();
let url = `/admin/api/${API_VERSION}/pages.json?limit=250&fields=handle,published_at`;
while (url) {
const { body, nextUrl } = await shopifyRequest('GET', url, null, true, SHOPIFY_TOKEN);
for (const p of (body.pages || [])) {
if (p.published_at) handles.add(p.handle);
}
url = nextUrl;
}
return handles;
}
/**
* Find the best matching vendor page handle from published Shopify pages.
* Tries several common patterns: vendor-{slug}, {slug}, {slug}-wallcoverings, etc.
*/
function resolvePageHandle(displayName, pageHandles) {
const slug = vendorNameToHandle(displayName);
const candidates = [
slug,
`vendor-${slug}`,
`${slug}-wallcoverings`,
`${slug}-wallpaper`,
`${slug}-fabrics`,
];
for (const candidate of candidates) {
if (pageHandles.has(candidate)) return candidate;
}
// Partial match: find any page handle that starts with the slug
for (const handle of pageHandles) {
if (handle.startsWith(slug) || handle === `vendor-${slug}`) {
return handle;
}
}
return null;
}
/**
* Group vendors alphabetically by first letter.
*/
function groupByLetter(vendors) {
const groups = {};
for (const v of vendors) {
const displayName = v.display_name;
// First character, uppercase. Numbers go under '#'
let letter = displayName.charAt(0).toUpperCase();
if (/\d/.test(letter)) letter = '#';
if (!groups[letter]) groups[letter] = [];
groups[letter].push(v);
}
return groups;
}
/**
* Generate clean, responsive HTML for the brands page.
* collectionVendors link to /collections/{handle}
* pageVendors link to /pages/{handle} and appear at the bottom
*/
function generateHTML(collectionVendors, pageVendors) {
const allVendors = [...collectionVendors, ...pageVendors];
const groups = groupByLetter(collectionVendors);
// Sort letters: '#' first, then A-Z
const sortedLetters = Object.keys(groups).sort((a, b) => {
if (a === '#') return -1;
if (b === '#') return 1;
return a.localeCompare(b);
});
// Build the letter navigation bar (only for collection vendors)
const allLetters = ['#', ...'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('')];
const navLinks = allLetters.map(letter => {
const exists = groups[letter];
if (exists) {
return `<a href="#brand-letter-${letter === '#' ? 'num' : letter}" style="display:inline-block;padding:4px 8px;margin:2px;color:#2c3e50;text-decoration:none;font-weight:600;border:1px solid #ddd;border-radius:4px;font-size:14px;transition:background 0.2s;">${letter}</a>`;
}
return `<span style="display:inline-block;padding:4px 8px;margin:2px;color:#ccc;font-size:14px;">${letter}</span>`;
}).join('');
// Build collection vendor sections by letter
const sections = sortedLetters.map(letter => {
const letterVendors = groups[letter];
const anchorId = letter === '#' ? 'num' : letter;
const vendorLinks = letterVendors.map(v => {
return `<li style="margin:6px 0;"><a href="/collections/${v.handle}" style="color:#2c3e50;text-decoration:none;font-size:16px;transition:color 0.2s;" onmouseover="this.style.color='#c0392b'" onmouseout="this.style.color='#2c3e50'">${v.display_name}</a></li>`;
}).join('\n ');
return `
<div id="brand-letter-${anchorId}" style="margin-bottom:28px;">
<h2 style="font-size:24px;font-weight:700;color:#c0392b;border-bottom:2px solid #e74c3c;padding-bottom:6px;margin-bottom:12px;">${letter}</h2>
<ul style="list-style:none;padding:0;margin:0;columns:2;column-gap:40px;">
${vendorLinks}
</ul>
</div>`;
}).join('\n');
// Build page vendors section at bottom
let pageSection = '';
if (pageVendors.length > 0) {
const pageLinks = pageVendors.map(v => {
return `<li style="margin:6px 0;"><a href="/pages/${v.handle}" style="color:#2c3e50;text-decoration:none;font-size:16px;transition:color 0.2s;" onmouseover="this.style.color='#c0392b'" onmouseout="this.style.color='#2c3e50'">${v.display_name}</a></li>`;
}).join('\n ');
pageSection = `
<div id="brand-additional" style="margin-top:40px;padding-top:30px;border-top:2px solid #e74c3c;">
<h2 style="font-size:24px;font-weight:700;color:#c0392b;margin-bottom:16px;">Additional Brands</h2>
<p style="color:#7f8c8d;font-size:14px;margin-bottom:16px;">Contact us for pricing and availability on these exclusive brands.</p>
<ul style="list-style:none;padding:0;margin:0;columns:2;column-gap:40px;">
${pageLinks}
</ul>
</div>`;
}
return `<div style="max-width:900px;margin:0 auto;padding:20px;font-family:-apple-system,BlinkMacSystemFont,'Segoe UI',Roboto,sans-serif;">
<div style="text-align:center;margin-bottom:30px;">
<h1 style="font-size:32px;font-weight:700;color:#2c3e50;margin-bottom:8px;">Our Designer Brands</h1>
<p style="color:#7f8c8d;font-size:16px;margin-bottom:20px;">Explore our curated collection of ${allVendors.length} premier wallcovering brands.</p>
<div style="margin-bottom:20px;">
${navLinks}
</div>
</div>
${sections}
${pageSection}
<div style="text-align:center;margin-top:40px;padding-top:20px;border-top:1px solid #eee;color:#95a5a6;font-size:13px;">
Last updated: ${new Date().toLocaleDateString('en-US', { year: 'numeric', month: 'long', day: 'numeric', timeZone: 'America/Los_Angeles' })}
</div>
</div>`;
}
/**
* Make a Shopify REST API request.
* If rawPath is true, path is used as-is (for pagination URLs).
* Returns { body, nextUrl } when rawPath is true.
* tokenOverride lets callers specify which token to use.
*/
function shopifyRequest(method, path, body, rawPath = false, tokenOverride = null) {
return new Promise((resolve, reject) => {
const postData = body ? JSON.stringify(body) : null;
const fullPath = rawPath ? path : `/admin/api/${API_VERSION}${path}`;
const token = tokenOverride || (rawPath ? SHOPIFY_PRODUCT_TOKEN : SHOPIFY_TOKEN);
const options = {
hostname: SHOPIFY_STORE,
path: fullPath,
method,
headers: {
'X-Shopify-Access-Token': token,
'Content-Type': 'application/json',
},
};
if (postData) {
options.headers['Content-Length'] = Buffer.byteLength(postData);
}
const req = https.request(options, (res) => {
let data = '';
res.on('data', chunk => { data += chunk; });
res.on('end', () => {
if (res.statusCode >= 200 && res.statusCode < 300) {
let parsed;
try {
parsed = JSON.parse(data);
} catch {
parsed = data;
}
if (rawPath) {
// Extract pagination Link header
const linkHeader = res.headers['link'] || '';
const nextMatch = linkHeader.match(/<https:\/\/[^/]+([^>]+)>;\s*rel="next"/);
resolve({ body: parsed, nextUrl: nextMatch ? nextMatch[1] : null });
} else {
resolve(parsed);
}
} else {
reject(new Error(`Shopify API ${res.statusCode}: ${data}`));
}
});
});
req.on('error', reject);
if (postData) req.write(postData);
req.end();
});
}
// --- Main ---
async function main() {
const pool = new Pool({ connectionString: DB_URL });
try {
// 1. Fetch actual Shopify collection handles + page handles
console.log('Fetching Shopify smart collections to build vendor→handle map...');
const vendorCollectionMap = await fetchVendorCollectionMap();
console.log(` Found ${Object.keys(vendorCollectionMap).length} vendor-based smart collections`);
console.log('Fetching Shopify pages for fallback links...');
const pageHandles = await fetchPageHandles();
console.log(` Found ${pageHandles.size} published pages`);
// 2. Read vendors from vendor_registry (including collection_handle override)
console.log('Connecting to PostgreSQL...');
const result = await pool.query(`
SELECT
vendor_name,
vendor_code,
is_private_label,
private_label_name,
collection_handle
FROM vendor_registry
WHERE show_on_brands_page = true
ORDER BY vendor_name ASC
`);
console.log(`Found ${result.rows.length} vendors with show_on_brands_page = true`);
if (result.rows.length === 0) {
console.error('No vendors found. Exiting.');
process.exit(1);
}
// 3. Split vendors into collection-linked vs page-linked
const collectionVendors = [];
const pageVendors = [];
const noLinkVendors = [];
for (const row of result.rows) {
const displayName = (row.is_private_label && row.private_label_name)
? row.private_label_name
: row.vendor_name;
// Try collection handle: DB override > Shopify lookup
const collectionHandle = row.collection_handle
|| resolveCollectionHandle(displayName, vendorCollectionMap);
if (collectionHandle) {
collectionVendors.push({
display_name: displayName,
handle: collectionHandle,
vendor_code: row.vendor_code,
});
} else {
// No collection — try to find a vendor page
const pageHandle = resolvePageHandle(displayName, pageHandles);
if (pageHandle) {
pageVendors.push({
display_name: displayName,
handle: pageHandle,
vendor_code: row.vendor_code,
});
} else {
noLinkVendors.push(displayName);
}
}
}
// Sort both lists
collectionVendors.sort((a, b) => a.display_name.localeCompare(b.display_name, 'en', { sensitivity: 'base' }));
pageVendors.sort((a, b) => a.display_name.localeCompare(b.display_name, 'en', { sensitivity: 'base' }));
console.log(`\n Collection-linked: ${collectionVendors.length}`);
console.log(` Page-linked (bottom): ${pageVendors.length}`);
if (noLinkVendors.length) {
console.log(` No link found (skipped): ${noLinkVendors.length}`);
noLinkVendors.forEach(v => console.log(` ⚠ ${v}`));
}
console.log('\nSample collection vendors:');
collectionVendors.slice(0, 5).forEach(v => {
console.log(` ${v.display_name} -> /collections/${v.handle}`);
});
if (pageVendors.length) {
console.log('Sample page vendors:');
pageVendors.slice(0, 5).forEach(v => {
console.log(` ${v.display_name} -> /pages/${v.handle}`);
});
}
// 4. Generate HTML
const html = generateHTML(collectionVendors, pageVendors);
console.log(`\nGenerated HTML: ${html.length} characters`);
// 5. Push to Shopify
const totalBrands = collectionVendors.length + pageVendors.length;
console.log(`\nPushing to Shopify page ID ${BRANDS_PAGE_ID}...`);
const response = await shopifyRequest('PUT', `/pages/${BRANDS_PAGE_ID}.json`, {
page: {
id: BRANDS_PAGE_ID,
body_html: html,
title: 'Designer Brands',
},
});
const page = response.page;
console.log(`\nSuccess! Page updated:`);
console.log(` Title: ${page.title}`);
console.log(` Handle: ${page.handle}`);
console.log(` Updated at: ${page.updated_at}`);
console.log(` URL: https://www.designerwallcoverings.com/pages/${page.handle}`);
console.log(`\n${totalBrands} brands published (${collectionVendors.length} collections + ${pageVendors.length} info pages).`);
} catch (err) {
console.error('Error:', err.message);
process.exit(1);
} finally {
await pool.end();
}
}
main();