jianhen-voc/deploy/backfill-listings.mjs

const parseUrl = process.env.PARSE_URL || 'http://127.0.0.1:3000/parse';
const appId = process.env.PARSE_APP_ID;
const masterKey = process.env.PARSE_MASTER_KEY;

if (!appId || !masterKey) throw new Error('Missing Parse bootstrap environment variables');

const headers = {
  'Content-Type': 'application/json',
  'X-Parse-Application-Id': appId,
  'X-Parse-Master-Key': masterKey,
};

async function request(path, method = 'GET', body) {
  const response = await fetch(`${parseUrl}${path}`, {
    method,
    headers,
    body: body === undefined ? undefined : JSON.stringify(body),
  });
  const result = await response.json();
  if (!response.ok || result.error) throw new Error(`${method} ${path}: ${JSON.stringify(result.error || response.status)}`);
  return result;
}

async function allRows(className, keys = '', where = undefined) {
  const rows = [];
  let skip = 0;
  const whereParam = where === undefined ? '' : `&where=${encodeURIComponent(JSON.stringify(where))}`;
  while (true) {
    const page = await request(`/classes/${className}?limit=1000&skip=${skip}&order=objectId${keys ? `&keys=${keys}` : ''}${whereParam}`);
    rows.push(...(page.results || []));
    if ((page.results || []).length < 1000) return rows;
    skip += page.results.length;
  }
}

const siteByMarketplace = {
  ATVPDKIKX0DER: 'us', A2EUQ1WTGCTBG2: 'ca', A1AM78C64UM0Y8: 'mx', A2Q3Y263D00KWC: 'br',
  A1F83G8C2ARO7P: 'uk', A1PA6795UKMFR9: 'de', A13V1IB3VIYZZH: 'fr', A1RKKUPIHCS9HS: 'es',
  APJ6JRA9NG5V4: 'it', A1VC38T7YXB528: 'jp', A39IBJ37TRP1C6: 'au', A2VIGQ35RCS4UG: 'ae',
  A17E79C6D8DWNP: 'sa', A21TJRUUN4KGV: 'in',
};

const currencyByMarketplace = {
  ATVPDKIKX0DER: 'USD', A2EUQ1WTGCTBG2: 'CAD', A1AM78C64UM0Y8: 'MXN', A2Q3Y263D00KWC: 'BRL',
  A1F83G8C2ARO7P: 'GBP', A1PA6795UKMFR9: 'EUR', A13V1IB3VIYZZH: 'EUR', A1RKKUPIHCS9HS: 'EUR',
  APJ6JRA9NG5V4: 'EUR', A1805IZSGTT6HS: 'EUR', A2NODRKZP88ZB9: 'SEK', A1C3SOZRARQ6R3: 'PLN',
  AMEN7PMS3EDWL: 'EUR', A28R8C7NBKEWEA: 'EUR', A39IBJ37TRP1C6: 'AUD', A19VAU5U5O7RUS: 'SGD',
  A2VIGQ35RCS4UG: 'AED', A17E79C6D8DWNP: 'SAR',
};

const normalize = value => String(value || '').trim().toUpperCase();
const rowKey = (shopId, asin) => `${shopId}:${normalize(asin)}`;
const skuKey = (shopId, sku) => `${shopId}:${normalize(sku)}`;

function firstAttribute(attributes, names) {
  for (const name of names) {
    const entry = attributes?.[name];
    const value = Array.isArray(entry) ? entry[0]?.value : entry?.value;
    if (value !== undefined && value !== null && String(value).trim()) return String(value).trim();
  }
  return '';
}

function catalogImage(catalog) {
  const images = (catalog?.images || catalog?.rawData?.images || [])
    .flatMap(group => group?.images || [])
    .filter(image => image?.link);
  return images
    .slice()
    .sort((left, right) => {
      const leftMain = left.variant === 'MAIN' ? 1 : 0;
      const rightMain = right.variant === 'MAIN' ? 1 : 0;
      return rightMain - leftMain || Number(right.width || 0) - Number(left.width || 0);
    })[0]?.link || '';
}

function pricingOffer(pricing, sku) {
  const offers = pricing?.product?.Offers || pricing?.rawData?.Product?.Offers || [];
  return offers.find(offer => normalize(offer?.SellerSKU) === normalize(sku)) || offers[0] || null;
}

function numberValue(value) {
  let normalized = String(value ?? '').trim().replace(/[^0-9,.-]/g, '');
  if (normalized.includes(',') && !normalized.includes('.')) normalized = normalized.replace(',', '.');
  else normalized = normalized.replace(/,/g, '');
  const result = Number(normalized);
  return Number.isFinite(result) ? result : 0;
}

function listingDate(value) {
  const raw = String(value || '').trim();
  if (!raw) return undefined;
  let timestamp = Date.parse(raw);
  if (!Number.isFinite(timestamp)) {
    const match = raw.match(/^(\d{2})\/(\d{2})\/(\d{4})(?:\s+(\d{2}):(\d{2}):(\d{2}))?/);
    if (match) timestamp = Date.UTC(
      Number(match[3]), Number(match[2]) - 1, Number(match[1]),
      Number(match[4] || 0), Number(match[5] || 0), Number(match[6] || 0),
    );
  }
  return Number.isFinite(timestamp) ? { __type: 'Date', iso: new Date(timestamp).toISOString() } : undefined;
}

async function upsert(className, where, payload) {
  const encoded = encodeURIComponent(JSON.stringify(where));
  const existing = await request(`/classes/${className}?where=${encoded}&limit=1`);
  const objectId = existing.results?.[0]?.objectId;
  return objectId
    ? request(`/classes/${className}/${objectId}`, 'PUT', payload)
    : request(`/classes/${className}`, 'POST', payload);
}

const shopsResult = await request('/classes/Shop?limit=1000');
const shops = new Map(shopsResult.results.map(shop => [shop.objectId, shop]));
const shopsByMarketplace = new Map(shopsResult.results.map(shop => [shop.marketplaceId, shop]));
for (const shop of shopsResult.results) {
  await request(`/classes/Shop/${shop.objectId}`, 'PUT', {
    shopName: shop.name || '',
    storeName: shop.name || '',
    disable: Boolean(shop.disable),
    deleted: Boolean(shop.deleted),
  });
}

// Listings Items requires a region-specific account ID, while merchant-listing
// reports only require the authorized shop token. Materialize single-marketplace
// reports so every product is attached to the marketplace that produced it.
const merchantReportRows = await allRows(
  'AmazonReportRow',
  'reportId,marketplaceIds,rowData',
  { reportType: 'GET_MERCHANT_LISTINGS_ALL_DATA' },
);
let reportListingUpserts = 0;
for (const reportRow of merchantReportRows) {
  if (!Array.isArray(reportRow.marketplaceIds) || reportRow.marketplaceIds.length !== 1) continue;
  const marketplaceId = reportRow.marketplaceIds[0];
  const shop = shopsByMarketplace.get(marketplaceId);
  const row = reportRow.rowData || {};
  if (!shop) continue;

  const asin = normalize(row.asin1 || row['product-id'] || row.product_id);
  const sku = String(row['seller-sku'] || row.seller_sku || '').trim();
  if (!asin || !sku) continue;

  const pointer = { __type: 'Pointer', className: 'Shop', objectId: shop.objectId };
  const createdDate = listingDate(row['open-date'] || row.open_date);
  await upsert('Listing', { sku, shop: pointer }, {
    asin,
    sku,
    sellerSku: sku,
    title: String(row['item-name'] || row.item_name || '').trim(),
    itemName: String(row['item-name'] || row.item_name || '').trim(),
    marketplaceId,
    price: numberValue(row.price),
    currency: currencyByMarketplace[marketplaceId] || '',
    quantity: numberValue(row.quantity),
    status: String(row.status || '').trim(),
    condition: String(row['item-condition'] || row.item_condition || '').trim(),
    fulfillmentType: String(row['fulfillment-channel'] || row['fulfilment-channel'] || '').trim(),
    source: 'amazon-sp-api-report',
    rawData: row,
    shop: pointer,
    ...(createdDate ? { createdDate } : {}),
  });
  reportListingUpserts += 1;
}

const [listingRows, catalogRows, pricingRows, inventoryRows, orderRows, orderItemRows] = await Promise.all([
  allRows('Listing'),
  allRows('AmazonCatalogItem'),
  allRows('AmazonProductPricing'),
  allRows('AmazonInventorySummary'),
  allRows('Order', 'objectId,platformOrderId,orderDate,status,shop'),
  allRows('AmazonOrderItem', 'amazonOrderId,asin,sellerSku,quantityOrdered,shop'),
]);
const listingResult = { results: listingRows };
const catalogByProduct = new Map(catalogRows.map(row => [rowKey(row.shop?.objectId || '', row.asin), row]));
const pricingByProduct = new Map(pricingRows.map(row => [rowKey(row.shop?.objectId || '', row.asin), row]));
const inventoryBySku = new Map(inventoryRows.map(row => [skuKey(row.shop?.objectId || '', row.sellerSku), row]));
const inventoryByProduct = new Map(inventoryRows.map(row => [rowKey(row.shop?.objectId || '', row.asin), row]));
const recentOrderIds = new Set(orderRows.filter(order => {
  if (['canceled', 'cancelled'].includes(String(order.status || '').trim().toLowerCase())) return false;
  const iso = order.orderDate?.iso || order.orderDate || '';
  const time = new Date(iso).getTime();
  return Number.isFinite(time) && time >= Date.now() - 30 * 24 * 60 * 60 * 1000;
}).map(order => order.platformOrderId).filter(Boolean));
const monthlySalesByProduct = new Map();
for (const item of orderItemRows) {
  if (!recentOrderIds.has(item.amazonOrderId)) continue;
  const key = rowKey(item.shop?.objectId || '', item.asin);
  monthlySalesByProduct.set(key, (monthlySalesByProduct.get(key) || 0) + Number(item.quantityOrdered || 0));
}
let productUpserts = 0;
let mappingUpserts = 0;
for (const listing of listingResult.results) {
  const asin = String(listing.asin || '').trim().toUpperCase();
  const shopId = listing.shop?.objectId || '';
  if (!asin || !shopId) continue;
  const shop = shops.get(shopId) || {};
  const marketplaceId = listing.marketplaceId || shop.marketplaceId || '';
  const site = siteByMarketplace[marketplaceId] || String(shop.domain || '');
  const pointer = { __type: 'Pointer', className: 'Shop', objectId: shopId };
  const key = rowKey(shopId, asin);
  const catalog = catalogByProduct.get(key) || {};
  const pricing = pricingByProduct.get(key) || {};
  const inventory = inventoryBySku.get(skuKey(shopId, listing.sku)) || inventoryByProduct.get(key) || {};
  const offer = pricingOffer(pricing, listing.sku);
  const catalogSummary = catalog.summaries?.[0] || catalog.rawData?.summaries?.[0] || {};
  const imageUrl = catalogImage(catalog) || listing.mainImage || listing.imageUrl || '';
  const title = catalog.title || catalogSummary.itemName || listing.title || '';
  const price = Number(
    offer?.BuyingPrice?.ListingPrice?.Amount
    ?? offer?.BuyingPrice?.LandedPrice?.Amount
    ?? listing.price
    ?? 0
  );
  const currency = offer?.BuyingPrice?.ListingPrice?.CurrencyCode || listing.currency || '';
  const monthlySales = Number(monthlySalesByProduct.get(key) || 0);
  const listingColor = firstAttribute(listing.attributes, ['color_name', 'color', 'color_map'])
    || catalogSummary.color || firstAttribute(catalog.attributes, ['color']);
  const listingSize = firstAttribute(listing.attributes, ['size_name', 'size'])
    || catalogSummary.size || firstAttribute(catalog.attributes, ['size']);
  const createdDate = String(listing.createdDate?.iso || listing.createdDate || '').slice(0, 10);
  const createdTime = new Date(createdDate).getTime();
  const onlineDays = Number.isFinite(createdTime)
    ? Math.max(0, Math.floor((Date.now() - createdTime) / 86400000))
    : 0;
  const payload = {
    asin,
    parentAsin: listing.parentAsin || '',
    sku: listing.sku || '',
    sellerSku: listing.sku || '',
    title,
    itemName: title,
    imageUrl,
    photo: imageUrl,
    listingImageUrl: imageUrl,
    category: catalogSummary.browseClassification?.displayName
      ? [catalogSummary.browseClassification.displayName]
      : (listing.productType ? [listing.productType] : []),
    categoryName: catalogSummary.browseClassification?.displayName || '',
    productType: catalog.productType || listing.productType || '',
    brand: catalog.brand || catalogSummary.brand || '',
    marketplaceId,
    domain: String(shop.domain || ''),
    site,
    storeName: shop.name || '',
    shopName: shop.name || '',
    shopId,
    storeId: shopId,
    status: listing.status || '',
    onlineDate: createdDate,
    onlineDays,
    price,
    salesPrice: price,
    listingPrice: price,
    regularPrice: Number(offer?.RegularPrice?.Amount || price),
    currency,
    fnsku: inventory.fnSku || '',
    fulfillmentType: inventory.objectId ? 'FBA' : '',
    inventoryQuantity: Number(inventory.totalQuantity || 0),
    listingColor,
    listingSize,
    listingSalesVolumeOfMonth: monthlySales,
    ListingSalesVolumeOfMonth: monthlySales,
    listingSalesOfMonth: monthlySales,
    monthlySales,
    attributes: catalog.attributes || listing.attributes || {},
    catalogData: catalog.rawData || {},
    pricingData: pricing.product || pricing.rawData || {},
    inventoryData: inventory.inventoryDetails || inventory.rawData || {},
    source: 'amazon-sp-api',
    shop: pointer,
  };
  const where = { asin, shop: pointer };
  await upsert('Product', where, payload);
  await upsert('ProductDetail', where, payload);
  await upsert('AsinSkuMapping', { asin, sellerSku: listing.sku || '', shop: pointer }, {
    recordKey: `${shopId}:${asin}:${listing.sku || ''}`,
    asin,
    parentAsin: listing.parentAsin || '',
    sku: listing.sku || '',
    sellerSku: listing.sku || '',
    productSku: listing.sku || '',
    fnsku: inventory.fnSku || '',
    title,
    itemName: title,
    imageUrl,
    photo: imageUrl,
    productType: catalog.productType || listing.productType || '',
    category: catalogSummary.browseClassification?.displayName || listing.productType || '',
    brand: catalog.brand || catalogSummary.brand || '',
    marketplaceId,
    domain: String(shop.domain || ''),
    site,
    storeName: shop.name || '',
    shopName: shop.name || '',
    shopId,
    storeId: shopId,
    status: listing.status || '',
    onlineDate: createdDate,
    onlineDays,
    price,
    salesPrice: price,
    currency,
    fulfillmentType: inventory.objectId ? 'FBA' : '',
    inventoryQuantity: Number(inventory.totalQuantity || 0),
    color: listingColor,
    size: listingSize,
    listingColor,
    listingSize,
    source: 'amazon-sp-api',
    shop: pointer,
  });
  await request(`/classes/Listing/${listing.objectId}`, 'PUT', {
    site,
    storeName: shop.name || '',
    shopName: shop.name || '',
    shopId,
    sellerSku: listing.sku || '',
    fnsku: inventory.fnSku || '',
    fulfillmentType: inventory.objectId ? 'FBA' : '',
    inventoryQuantity: Number(inventory.totalQuantity || 0),
    listingColor,
    listingSize,
    listingSalesVolumeOfMonth: monthlySales,
    ListingSalesVolumeOfMonth: monthlySales,
    listingSalesOfMonth: monthlySales,
  });
  productUpserts += 1;
  mappingUpserts += 1;
}

for (const className of ['Product', 'ProductDetail', 'SorftimeProduct']) {
  const rows = await request(`/classes/${className}?limit=1000`);
  for (const row of rows.results || []) {
    const shopId = row.shop?.objectId || '';
    if (!shopId) continue;
    const shop = shops.get(shopId) || {};
    const marketplaceId = row.marketplaceId || shop.marketplaceId || '';
    await request(`/classes/${className}/${row.objectId}`, 'PUT', {
      site: row.site || siteByMarketplace[marketplaceId] || String(shop.domain || ''),
      marketplaceId,
      domain: String(row.domain || shop.domain || ''),
      storeName: row.storeName || shop.name || '',
      shopName: row.shopName || shop.name || '',
      shopId,
    });
  }
}

console.log(JSON.stringify({
  shops: shops.size,
  listings: listingResult.results.length,
  productUpserts,
  mappingUpserts,
  catalogRows: catalogRows.length,
  pricingRows: pricingRows.length,
  inventoryRows: inventoryRows.length,
  orderItemRows: orderItemRows.length,
  productsWithMonthlySales: monthlySalesByProduct.size,
  merchantReportRows: merchantReportRows.length,
  reportListingUpserts,
}));