homaroma/scripts/sync_production_bom_to_local.ts

import prisma from "../apps/backend/src/lib/prisma";

const PROD_API = "https://api.homaroma.shop/api";

async function main() {
  console.log("== Syncing Production Raw Materials and BOMs to Local DB ==");

  // 1. Fetch Production Raw Materials
  console.log("1. Fetching Production Raw Materials...");
  const prodRmsRes = await fetch(`${PROD_API}/raw-materials`);
  const prodRms = (await prodRmsRes.json()) as any[];
  console.log(`Found ${prodRms.length} raw materials on production.`);

  // Upsert into local DB by name
  const nameToLocalId = new Map<string, number>();
  for (const rm of prodRms) {
    const upserted = await prisma.rawMaterial.upsert({
      where: { name: rm.name },
      create: {
        name: rm.name,
        unit: rm.unit || "piece",
        pricePerUnit: rm.pricePerUnit || 0,
      },
      update: {
        unit: rm.unit || "piece",
        pricePerUnit: rm.pricePerUnit || 0,
      },
    });
    nameToLocalId.set(rm.name, upserted.id);
  }
  console.log("✓ Raw materials synced to local DB.");

  // 2. Fetch Production BOM Status
  console.log("2. Fetching Production BOM status...");
  const prodStatusRes = await fetch(`${PROD_API}/products/bom-status?limit=300`);
  const prodStatus = (await prodStatusRes.json()) as any;
  const prodProducts = prodStatus.products || [];
  console.log(`Found ${prodProducts.length} products on production.`);

  // Map to local products and variants by SKU / Product Name
  let variantBomCount = 0;

  for (const prod of prodProducts) {
    // Fetch full BOM for this product from production
    try {
      const detailRes = await fetch(`${PROD_API}/products/${prod.id}/bom`);
      if (!detailRes.ok) continue;
      const detail = (await detailRes.json()) as any;
      const prodVariants = detail.variants || [];

      for (const pv of prodVariants) {
        if (!pv.bomItems || pv.bomItems.length === 0) continue;

        // Find local variant by sku
        const localVariant = await prisma.productVariant.findFirst({
          where: { sku: pv.sku },
        });

        if (!localVariant) continue;

        // Clear existing local BOM items for this variant
        await prisma.bomItem.deleteMany({
          where: { productVariantId: localVariant.id },
        });

        // Create new BOM items
        for (const item of pv.bomItems) {
          const rmName = item.name || item.rawMaterial?.name;
          const localRmId = rmName ? nameToLocalId.get(rmName) : null;
          if (!localRmId) continue;

          await prisma.bomItem.create({
            data: {
              productVariantId: localVariant.id,
              rawMaterialId: localRmId,
              quantity: item.quantity,
            },
          });
        }
        variantBomCount++;
      }
    } catch (err: any) {
      console.error(`Error syncing product #${prod.id}:`, err.message);
    }
  }

  console.log(`✓ Successfully synced ${variantBomCount} variant BOMs to local DB!`);

  // 3. Sync Marketplace Product Mappings from Production
  console.log("3. Syncing Marketplace Product Mappings...");
  try {
    const mpRes = await fetch(`${PROD_API}/tools/marketplace-products?limit=3000`);
    if (mpRes.ok) {
      const mpData = (await mpRes.json()) as any;
      const mpItems = mpData.items || [];
      console.log(`Found ${mpItems.length} marketplace products on production.`);

      let mappedCount = 0;
      for (const item of mpItems) {
        if (!item.masterVariant?.sku) continue;

        const localVariant = await prisma.productVariant.findFirst({
          where: { sku: item.masterVariant.sku },
          select: { id: true },
        });

        if (!localVariant) continue;

        // Upsert or update local MarketplaceProduct
        const existing = await prisma.marketplaceProduct.findFirst({
          where: {
            channel: item.channel,
            shop: item.shop,
            productName: item.productName,
            variation: item.variation || "",
            marketplaceSku: item.marketplaceSku || "",
          },
        });

        if (existing) {
          await prisma.marketplaceProduct.update({
            where: { id: existing.id },
            data: {
              masterVariantId: localVariant.id,
              totalSoldQty: item.totalSoldQty,
              totalRevenue: item.totalRevenue,
              avgPrice: item.avgPrice,
            },
          });
          mappedCount++;
        } else {
          await prisma.marketplaceProduct.create({
            data: {
              channel: item.channel,
              shop: item.shop,
              productName: item.productName,
              variation: item.variation || "",
              marketplaceSku: item.marketplaceSku || "",
              totalSoldQty: item.totalSoldQty,
              totalRevenue: item.totalRevenue,
              avgPrice: item.avgPrice,
              masterVariantId: localVariant.id,
            },
          });
          mappedCount++;
        }
      }
      console.log(`✓ Synced ${mappedCount} marketplace product mappings to local DB.`);
    }
  } catch (err: any) {
    console.warn("Marketplace sync warning:", err.message);
  }
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());