homaroma/scripts/fetch_and_list_oil_ozone.ts

import * as fs from "fs";
import * as path from "path";

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

interface RawMaterial {
  id: number;
  name: string;
  unit: string;
  pricePerUnit: number;
}

interface Variant {
  id: number;
  sku: string;
  options: string;
  price: number;
  hasBom: boolean;
  bomItemCount: number;
  totalBomCost: number;
}

interface Product {
  id: number;
  name: string;
  brand: string | null;
  category: string | null;
  totalVariants: number;
  configuredVariants: number;
  bomStatus: string;
  variants: Variant[];
}

async function main() {
  console.log("1. Fetching all products from production...");
  const statusRes = await fetch(`${PROD_API}/products/bom-status?limit=250`);
  if (!statusRes.ok) throw new Error(`Failed to fetch products: ${statusRes.statusText}`);
  const statusData = (await statusRes.json()) as { products: Product[] };

  // Filter only Oil Ozone products
  const allProducts = statusData.products || [];
  const oilOzoneProducts = allProducts.filter(
    (p) =>
      p.name.includes("Oil Ozone") ||
      (p.category && p.category.includes("โอโซน")) ||
      p.variants.some((v) => v.sku && v.sku.includes("-1-"))
  );

  console.log(`Found ${oilOzoneProducts.length} Oil Ozone products.`);

  // 2. Fetch all raw materials from production
  console.log("2. Fetching available raw materials from production...");
  const rmRes = await fetch(`${PROD_API}/raw-materials`);
  if (!rmRes.ok) throw new Error(`Failed to fetch raw materials: ${rmRes.statusText}`);
  const rawMaterials = (await rmRes.json()) as RawMaterial[];
  console.log(`Found ${rawMaterials.length} raw materials on production.`);

  // Identify common raw materials
  const dpg = rawMaterials.find((r) => r.name.trim().toLowerCase() === "dpg");
  const b30 = rawMaterials.find((r) => r.name.includes("Oilozone 30ml") || r.name.includes("ขวดพร้อมฝา Oilozone 30ml"));
  const b60 = rawMaterials.find((r) => r.name.includes("Oilozone 60ml") || r.name.includes("ขวดพร้อมฝา Oilozone 60ml"));
  const b85 = rawMaterials.find((r) => r.name.includes("Oilozone 85ml") || r.name.includes("ขวดพร้อมฝา Oilozone 85ml"));
  const b120 = rawMaterials.find((r) => r.name.includes("Oilozone 120ml") || r.name.includes("ขวดพร้อมฝา Oilozone 120ml"));

  // Build match plan for each product
  const productPlan = oilOzoneProducts.map((p, idx) => {
    // Extract scent name e.g. "ซากุระ Oil Ozone" -> "ซากุระ"
    const scentName = p.name.replace(/Oil Ozone/gi, "").trim();

    // Look for matching fragrance oil in raw materials
    const matchedFragrance = rawMaterials.find((rm) => {
      const rmName = rm.name.trim();
      return (
        rmName === `หัวน้ำหอม กลิ่น${scentName}` ||
        rmName === `หัวน้ำหอม ${scentName}` ||
        rmName.includes(`กลิ่น${scentName}`) ||
        (rmName.includes(scentName) && rmName.includes("หัวน้ำหอม"))
      );
    });

    return {
      index: idx + 1,
      productId: p.id,
      productName: p.name,
      scentName,
      category: p.category,
      bomStatus: p.bomStatus,
      configuredVariants: p.configuredVariants,
      totalVariants: p.totalVariants,
      variants: p.variants.map((v) => ({
        id: v.id,
        sku: v.sku,
        option: v.options,
        price: v.price,
        hasBom: v.hasBom,
        totalBomCost: v.totalBomCost,
        marginPercent: v.price > 0 && v.totalBomCost > 0
          ? Number((((v.price - v.totalBomCost) / v.price) * 100).toFixed(1))
          : null,
      })),
      matchedFragrance: matchedFragrance
        ? {
            id: matchedFragrance.id,
            name: matchedFragrance.name,
            pricePerUnit: matchedFragrance.pricePerUnit,
            unit: matchedFragrance.unit,
          }
        : null,
      packagingReady: !!(dpg && b30 && b60 && b85 && b120),
    };
  });

  // 3. Save JSON to local root folder
  const jsonPath = path.resolve(process.cwd(), "oil_ozone_products.json");
  fs.writeFileSync(jsonPath, JSON.stringify(productPlan, null, 2), "utf-8");
  console.log(`\n✓ Saved JSON data to: ${jsonPath}`);

  // 4. Generate Markdown Ledger
  let md = `# รายการสินค้ากลุ่ม Oil Ozone สำหรับกำหนดสูตร BOM (Production)\n\n`;
  md += `> **เป้าหมาย:** กำหนดสูตร BOM อัตราส่วน หัวน้ำหอม 8% + DPG 92% + ขวดและฝา 4 บาท (ทุกขนาด: 30ml, 60ml, 85ml, 120ml)\n`;
  md += `> **อัปเดตล่าสุด:** ${new Date().toLocaleString("th-TH")}\n`;
  md += `> **จำนวนสินค้าทั้งหมด:** ${productPlan.length} รายการ\n\n`;

  md += `## 📦 สรุปสถานะภาพรวม\n`;
  const configured = productPlan.filter((p) => p.bomStatus === "CONFIGURED").length;
  const missing = productPlan.filter((p) => p.bomStatus === "MISSING").length;
  const partial = productPlan.filter((p) => p.bomStatus === "PARTIAL").length;
  const fragranceFound = productPlan.filter((p) => p.matchedFragrance !== null).length;

  md += `- **ตั้งค่าเสร็จแล้ว (CONFIGURED):** ${configured} / ${productPlan.length} รายการ (100%)\n`;
  md += `- **ยังไม่ได้ตั้งค่า (MISSING):** ${missing} รายการ\n`;
  md += `- **ตั้งค่าบางส่วน (PARTIAL):** ${partial} รายการ\n`;
  md += `- **พบวัตถุดิบหัวน้ำหอมตรงกลิ่น:** ${fragranceFound} / ${productPlan.length} รายการ\n\n`;

  md += `## 📋 ตารางรายละเอียดต้นทุน BOM และอัตรากำไร (Margin)\n\n`;
  md += `| # | ID | ชื่อสินค้า | หัวน้ำหอม (ราคา/ml) | ต้นทุน 30ml (กำไร%) | ต้นทุน 60ml (กำไร%) | ต้นทุน 85ml (กำไร%) | ต้นทุน 120ml (กำไร%) | สถานะ |\n`;
  md += `| :-: | :-: | :--- | :--- | :-: | :-: | :-: | :-: | :-: |\n`;

  for (const p of productPlan) {
    const statusBadge =
      p.bomStatus === "CONFIGURED"
        ? "✅ พร้อมขาย"
        : p.bomStatus === "PARTIAL"
        ? "⚠️ บางส่วน"
        : "❌ ขาด BOM";

    const fragText = p.matchedFragrance
      ? `[ID: ${p.matchedFragrance.id}] ${p.matchedFragrance.name} (฿${p.matchedFragrance.pricePerUnit})`
      : `<span style="color:red">⚠️ ไม่พบหัวน้ำหอม</span>`;

    const v30 = p.variants.find((v) => (v.option || "").includes("30") || (v.sku || "").includes("30ML"));
    const v60 = p.variants.find((v) => (v.option || "").includes("60") || (v.sku || "").includes("60ML"));
    const v85 = p.variants.find((v) => (v.option || "").includes("85") || (v.sku || "").includes("85ML"));
    const v120 = p.variants.find((v) => (v.option || "").includes("120") || (v.sku || "").includes("120ML"));

    const formatCost = (v?: (typeof p.variants)[0]) => {
      if (!v || !v.hasBom) return "-";
      return `฿${v.totalBomCost.toFixed(2)} (${v.marginPercent}%)`;
    };

    md += `| ${p.index} | **#${p.productId}** | [${p.productName}](https://admin.homaroma.shop/products/${p.productId}/bom) | ${fragText} | ${formatCost(v30)} | ${formatCost(v60)} | ${formatCost(v85)} | ${formatCost(v120)} | ${statusBadge} |\n`;
  }

  const mdPath = path.resolve(process.cwd(), "OIL_OZONE_BOM_LEDGER.md");
  fs.writeFileSync(mdPath, md, "utf-8");
  console.log(`✓ Saved Markdown ledger to: ${mdPath}`);
}

main().catch(console.error);