← back to Letsbegin

test_brands.js

451 lines

#!/usr/bin/env node
/**
 * test_brands.js — Audit all 124 DW brands on Shopify
 *
 * 1. Pull vendors from vendor_registry WHERE show_on_brands_page = true
 * 2. Query Shopify GraphQL productsCount for each vendor
 * 3. For brands with 0 products, create a "Contact Us" page if one doesn't exist
 * 4. Write report to /tmp/brands_audit.txt
 */

const https = require('https');
const { Client } = require('pg');

// Config
const SHOPIFY_STORE = 'designer-laboratory-sandbox.myshopify.com';
const PRODUCT_TOKEN = process.env.SHOPIFY_PRODUCT_TOKEN;
const CONTENT_TOKEN = process.env.SHOPIFY_CONTENT_TOKEN;
const API_VERSION = '2024-10';
const PG_URL = process.env.DATABASE_URL;

if (!PRODUCT_TOKEN || !CONTENT_TOKEN || !PG_URL) {
  throw new Error('SHOPIFY_PRODUCT_TOKEN, SHOPIFY_CONTENT_TOKEN, and DATABASE_URL environment variables are required');
}

// Rate limiting
let shopifyBucket = 3900; // conservative start
const BUCKET_MAX = 4000;
const RESTORE_RATE = 200; // per second

function sleep(ms) {
  return new Promise(resolve => setTimeout(resolve, ms));
}

/**
 * Make a Shopify GraphQL request
 */
function shopifyGraphQL(query, token = PRODUCT_TOKEN) {
  return new Promise((resolve, reject) => {
    const postData = JSON.stringify({ query });
    const options = {
      hostname: SHOPIFY_STORE,
      path: `/admin/api/${API_VERSION}/graphql.json`,
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
        'X-Shopify-Access-Token': token,
        'Content-Length': Buffer.byteLength(postData),
      },
    };

    const req = https.request(options, (res) => {
      let data = '';
      res.on('data', (chunk) => (data += chunk));
      res.on('end', () => {
        try {
          const json = JSON.parse(data);
          if (json.extensions?.cost?.throttleStatus) {
            shopifyBucket = json.extensions.cost.throttleStatus.currentlyAvailable;
          }
          resolve(json);
        } catch (e) {
          reject(new Error(`JSON parse error: ${data.substring(0, 200)}`));
        }
      });
    });

    req.on('error', reject);
    req.write(postData);
    req.end();
  });
}

/**
 * Make a Shopify REST API request
 */
function shopifyREST(method, path, body = null, token = CONTENT_TOKEN) {
  return new Promise((resolve, reject) => {
    const postData = body ? JSON.stringify(body) : null;
    const options = {
      hostname: SHOPIFY_STORE,
      path: `/admin/api/${API_VERSION}${path}`,
      method,
      headers: {
        'Content-Type': 'application/json',
        'X-Shopify-Access-Token': token,
      },
    };
    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', () => {
        try {
          const json = data ? JSON.parse(data) : {};
          resolve({ status: res.statusCode, data: json, headers: res.headers });
        } catch (e) {
          reject(new Error(`REST parse error: ${data.substring(0, 200)}`));
        }
      });
    });

    req.on('error', reject);
    if (postData) req.write(postData);
    req.end();
  });
}

/**
 * Get product count for a vendor from Shopify
 */
async function getProductCount(vendorName) {
  // Wait if bucket is low (each productsCount costs 10 points)
  while (shopifyBucket < 100) {
    console.log(`  [throttle] Bucket at ${shopifyBucket}, waiting 1s...`);
    await sleep(1000);
    shopifyBucket += RESTORE_RATE;
    if (shopifyBucket > BUCKET_MAX) shopifyBucket = BUCKET_MAX;
  }

  // Escape the vendor name for GraphQL query string
  const escaped = vendorName.replace(/\\/g, '\\\\').replace(/"/g, '\\"');
  const query = `{ productsCount(query: "vendor:\\"${escaped}\\"") { count } }`;

  const result = await shopifyGraphQL(query);

  if (result.errors) {
    console.error(`  GraphQL error for "${vendorName}":`, result.errors[0]?.message);
    return -1;
  }

  return result.data?.productsCount?.count ?? 0;
}

/**
 * Get all existing pages from Shopify (paginated)
 */
async function getAllPages() {
  const pages = [];
  let sinceId = 0;

  while (true) {
    const path = `/pages.json?limit=250&fields=id,title,handle,template_suffix${sinceId ? `&since_id=${sinceId}` : ''}`;
    const result = await shopifyREST('GET', path);

    if (result.status !== 200) {
      console.error(`Error fetching pages: ${result.status}`);
      break;
    }

    const batch = result.data.pages || [];
    pages.push(...batch);

    if (batch.length < 250) break;
    sinceId = batch[batch.length - 1].id;
    await sleep(500); // REST rate limit
  }

  return pages;
}

/**
 * Create a slug from a brand name
 */
function slugify(name) {
  return name
    .toLowerCase()
    .replace(/[&]/g, 'and')
    .replace(/[^a-z0-9]+/g, '-')
    .replace(/^-+|-+$/g, '');
}

/**
 * Build the contact page HTML for a brand
 */
function buildContactHTML(brandName) {
  return `<div style="max-width:900px;margin:0 auto;padding:30px 20px;font-family:Georgia,serif;">
<h2 style="text-align:center;color:#2c2420;font-size:28px;margin-bottom:8px;">${brandName}</h2>
<p style="text-align:center;font-size:16px;line-height:1.7;color:#5a4a3a;margin-bottom:30px;">We carry ${brandName} products and can source any item from their current collections. Contact us for pricing, samples, and availability.</p>
<div style="text-align:center;margin-top:20px;">
<a href="/pages/contact-us" style="display:inline-block;padding:12px 32px;background:#2c2420;color:#fff;text-decoration:none;font-size:15px;letter-spacing:1px;border-radius:3px;">CONTACT OUR DESIGN TEAM</a>
<p style="margin-top:15px;font-size:14px;color:#8a7a6a;">Or call us at <a href="tel:1-888-373-4564" style="color:#2c2420;text-decoration:underline;">1-888-373-4564</a></p>
</div>
</div>`;
}

/**
 * Create a page on Shopify
 */
async function createPage(brandName, handle) {
  const body = {
    page: {
      title: `${brandName} Wallcoverings`,
      handle: handle,
      body_html: buildContactHTML(brandName),
      template_suffix: 'inquiry',
      published: true,
    },
  };

  const result = await shopifyREST('POST', '/pages.json', body);

  if (result.status === 201 || result.status === 200) {
    return { success: true, id: result.data.page?.id, handle: result.data.page?.handle };
  } else {
    return { success: false, error: JSON.stringify(result.data.errors || result.data).substring(0, 200), status: result.status };
  }
}

/**
 * Main execution
 */
async function main() {
  const startTime = Date.now();
  console.log('=== DW Brands Audit ===');
  console.log(`Started: ${new Date().toLocaleString('en-US', { timeZone: 'America/Los_Angeles' })} PT\n`);

  // 1. Connect to PostgreSQL and get all vendors
  console.log('1. Fetching vendors from PostgreSQL...');
  const pg = new Client({ connectionString: PG_URL });
  await pg.connect();

  const { rows: vendors } = await pg.query(`
    SELECT vendor_code, vendor_name, is_private_label, private_label_name, shopify_count
    FROM vendor_registry
    WHERE show_on_brands_page = true
    ORDER BY vendor_name
  `);

  console.log(`   Found ${vendors.length} vendors with show_on_brands_page = true\n`);

  // 2. Get all existing pages from Shopify
  console.log('2. Fetching existing Shopify pages...');
  const existingPages = await getAllPages();
  const pageHandles = new Set(existingPages.map(p => p.handle));
  console.log(`   Found ${existingPages.length} existing pages\n`);

  // 3. Query Shopify for product counts
  console.log('3. Checking product counts on Shopify...\n');

  const results = {
    withProducts: [],
    withoutProducts: [],
    errors: [],
  };

  for (let i = 0; i < vendors.length; i++) {
    const v = vendors[i];
    const vendorName = v.vendor_name;

    process.stdout.write(`   [${i + 1}/${vendors.length}] ${vendorName}... `);

    const count = await getProductCount(vendorName);

    if (count === -1) {
      console.log('ERROR');
      results.errors.push({ ...v, count: -1, error: 'GraphQL error' });
    } else if (count > 0) {
      console.log(`${count} products`);
      results.withProducts.push({ ...v, shopify_live_count: count });
    } else {
      console.log('0 products');
      results.withoutProducts.push({ ...v, shopify_live_count: 0 });
    }

    // Small delay between requests to stay within rate limits
    await sleep(150);
  }

  console.log(`\n--- Summary ---`);
  console.log(`With products: ${results.withProducts.length}`);
  console.log(`Without products: ${results.withoutProducts.length}`);
  console.log(`Errors: ${results.errors.length}\n`);

  // 4. For brands with 0 products, create contact pages if they don't exist
  console.log('4. Creating contact pages for brands with 0 products...\n');

  const pagesCreated = [];
  const pagesExisted = [];
  const pageErrors = [];

  for (const brand of results.withoutProducts) {
    const slug = slugify(brand.vendor_name);
    // Check multiple possible handle patterns
    const possibleHandles = [
      slug,
      `${slug}-wallcoverings`,
      `vendor-${slug}`,
      brand.vendor_code,
    ];

    const existingPage = possibleHandles.find(h => pageHandles.has(h));

    if (existingPage) {
      console.log(`   [EXISTS] ${brand.vendor_name} → page handle: ${existingPage}`);
      pagesExisted.push({ vendor: brand.vendor_name, handle: existingPage });
      continue;
    }

    // Create the page
    const handle = `${slug}-wallcoverings`;
    process.stdout.write(`   [CREATE] ${brand.vendor_name} → ${handle}... `);

    const result = await createPage(brand.vendor_name, handle);

    if (result.success) {
      console.log(`OK (ID: ${result.id})`);
      pagesCreated.push({ vendor: brand.vendor_name, handle: result.handle, id: result.id });
    } else {
      console.log(`FAILED: ${result.error}`);
      pageErrors.push({ vendor: brand.vendor_name, handle, error: result.error });
    }

    await sleep(500); // REST rate limit for writes
  }

  // 5. Write report
  const elapsed = ((Date.now() - startTime) / 1000).toFixed(1);
  const timestamp = new Date().toLocaleString('en-US', { timeZone: 'America/Los_Angeles' });

  const report = [];
  report.push('='.repeat(70));
  report.push('DESIGNER WALLCOVERINGS — BRANDS AUDIT REPORT');
  report.push(`Generated: ${timestamp} PT`);
  report.push(`Duration: ${elapsed}s`);
  report.push('='.repeat(70));
  report.push('');
  report.push(`Total brands tested: ${vendors.length}`);
  report.push(`Brands WITH products: ${results.withProducts.length}`);
  report.push(`Brands WITHOUT products: ${results.withoutProducts.length}`);
  report.push(`Query errors: ${results.errors.length}`);
  report.push('');

  // Brands WITH products
  report.push('-'.repeat(70));
  report.push('BRANDS WITH PRODUCTS ON SHOPIFY');
  report.push('-'.repeat(70));
  report.push('');
  report.push(padRight('Vendor Name', 45) + padRight('Count', 10) + 'DB Count');
  report.push(padRight('-', 45, '-') + padRight('-', 10, '-') + '-------');

  // Sort by count descending
  results.withProducts.sort((a, b) => b.shopify_live_count - a.shopify_live_count);

  let totalShopifyProducts = 0;
  for (const brand of results.withProducts) {
    totalShopifyProducts += brand.shopify_live_count;
    const match = brand.shopify_live_count === brand.shopify_count ? '' : ' *MISMATCH*';
    report.push(
      padRight(brand.vendor_name, 45) +
      padRight(String(brand.shopify_live_count), 10) +
      `${brand.shopify_count}${match}`
    );
  }
  report.push('');
  report.push(`Total products across all brands: ${totalShopifyProducts}`);
  report.push('');

  // Brands WITHOUT products
  report.push('-'.repeat(70));
  report.push('BRANDS WITHOUT PRODUCTS ON SHOPIFY');
  report.push('-'.repeat(70));
  report.push('');
  report.push(padRight('Vendor Name', 45) + padRight('DB Count', 10) + 'Page Status');
  report.push(padRight('-', 45, '-') + padRight('-', 10, '-') + '-'.repeat(15));

  for (const brand of results.withoutProducts) {
    const created = pagesCreated.find(p => p.vendor === brand.vendor_name);
    const existed = pagesExisted.find(p => p.vendor === brand.vendor_name);
    const errored = pageErrors.find(p => p.vendor === brand.vendor_name);

    let status = 'UNKNOWN';
    if (created) status = `CREATED (${created.handle})`;
    else if (existed) status = `EXISTS (${existed.handle})`;
    else if (errored) status = `ERROR: ${errored.error}`;

    report.push(
      padRight(brand.vendor_name, 45) +
      padRight(String(brand.shopify_count), 10) +
      status
    );
  }

  report.push('');

  // Page creation summary
  report.push('-'.repeat(70));
  report.push('PAGE CREATION SUMMARY');
  report.push('-'.repeat(70));
  report.push(`Pages already existed: ${pagesExisted.length}`);
  report.push(`Pages created: ${pagesCreated.length}`);
  report.push(`Page creation errors: ${pageErrors.length}`);
  report.push('');

  if (pagesCreated.length > 0) {
    report.push('Newly created pages:');
    for (const p of pagesCreated) {
      report.push(`  - ${p.vendor} → /pages/${p.handle} (ID: ${p.id})`);
    }
    report.push('');
  }

  if (pageErrors.length > 0) {
    report.push('Failed page creations:');
    for (const p of pageErrors) {
      report.push(`  - ${p.vendor}: ${p.error}`);
    }
    report.push('');
  }

  // Errors
  if (results.errors.length > 0) {
    report.push('-'.repeat(70));
    report.push('QUERY ERRORS');
    report.push('-'.repeat(70));
    for (const e of results.errors) {
      report.push(`  - ${e.vendor_name}: ${e.error}`);
    }
    report.push('');
  }

  report.push('='.repeat(70));
  report.push('END OF REPORT');
  report.push('='.repeat(70));

  const reportText = report.join('\n');

  // Write to file
  const fs = require('fs');
  fs.writeFileSync('/tmp/brands_audit.txt', reportText);
  console.log(`\n5. Report written to /tmp/brands_audit.txt`);
  console.log(`   Duration: ${elapsed}s`);

  // Print report to console too
  console.log('\n' + reportText);

  await pg.end();
}

function padRight(str, len, char = ' ') {
  if (str.length >= len) return str.substring(0, len);
  return str + char.repeat(len - str.length);
}

main().catch(err => {
  console.error('Fatal error:', err);
  process.exit(1);
});