migrate-old-products.cjs

// ============================================================
// MIGRATION: Move products from old `supplements` table to new schema
// ============================================================
// Run: node migrate-old-products.cjs
// ============================================================

const { createClient } = require('@supabase/supabase-js');

const supabase = createClient(
  'https://crjxnucxwwfcvjwfxhit.supabase.co',
  'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6ImNyanhudWN4d3dmY3Zqd2Z4aGl0Iiwicm9sZSI6InNlcnZpY2Vfcm9sZSIsImlhdCI6MTc4MjA0NzY5NywiZXhwIjoyMDk3NjIzNjk3fQ.kicudmrQ78e49B_6IfpPnnwuR-XZZcricon24pyZKSw'
);

// Map old category_id to new category id
const CATEGORY_MAP = {
  'whey-protein': 'whey-protein',
  'creatine-monohydrate': 'creatine',
  'fish-oil': 'fish-oil',
  // Add more as needed
};

async function migrate() {
  console.log('Starting migration...\n');

  // 1. Fetch all products from old table
  const { data: oldProducts, error: fetchError } = await supabase
    .from('supplements')
    .select('*');

  if (fetchError) {
    console.error('Failed to fetch old products:', fetchError.message);
    return;
  }

  console.log(`Found ${oldProducts.length} products in old table\n`);

  // 2. Collect unique brands
  const uniqueBrands = [...new Set(oldProducts.map(p => p.brand))];
  console.log('Brands to create:', uniqueBrands);

  // 3. Create brands
  for (const brandName of uniqueBrands) {
    const slug = brandName.toLowerCase().replace(/\s+/g, '-').replace(/[^a-z0-9-]/g, '');
    const { error: brandErr } = await supabase
      .from('brands')
      .upsert({
        id: slug,
        name: brandName,
        slug: slug,
        description: brandName,
        is_featured: false,
      }, { onConflict: 'id' });

    if (brandErr) {
      console.error(`Failed to create brand '${brandName}':`, brandErr.message);
    } else {
      console.log(`  ✓ Created brand: ${brandName} (${slug})`);
    }
  }

  // 4. Migrate each product
  for (const old of oldProducts) {
    const brandSlug = old.brand.toLowerCase().replace(/\s+/g, '-').replace(/[^a-z0-9-]/g, '');
    const newCategoryId = CATEGORY_MAP[old.category_id] || old.category_id;

    console.log(`\n  Migrating: ${old.name}`);

    // Insert into products table
    const { error: prodErr } = await supabase
      .from('products')
      .upsert({
        id: old.id,
        name: old.name,
        slug: old.id,
        brand_id: brandSlug,
        category_id: newCategoryId,
        description: old.description || '',
        short_description: (old.description || '').substring(0, 200),
        images: old.images || [],
        flavor: old.flavor || [],
        net_weight: old.net_weight,
        serving_size: old.serving_size,
        total_servings: old.total_servings,
        cost_per_serving: old.cost_per_serving,
        protein_per_serving: old.protein_per_serving,
        creatine_per_serving: old.creatine_per_serving,
        key_ingredients: old.key_ingredients || [],
        ingredients: old.ingredients || null,
        nutrition: old.nutrition || null,
        rating: old.rating || 0,
        review_count: old.review_count || 0,
        best_price: old.best_price,
        original_price: old.original_price,
        discount_percentage: old.discount_percentage || 0,
        is_active: true,
        value_score: old.value_score || 0,
        is_vegetarian: old.is_vegetarian || false,
        is_vegan: old.is_vegan || false,
        certifications: old.certifications || [],
        lab_tested: old.lab_tested || false,
        goals: old.goals || [],
        published_at: new Date().toISOString(),
      }, { onConflict: 'id' });

    if (prodErr) {
      console.error(`  ✗ Failed to create product: ${prodErr.message}`);
      continue;
    }
    console.log(`  ✓ Created product`);

    // Insert product prices for major retailers (using best_price)
    const retailers = ['amazon', 'healthkart', 'flipkart', 'nutrabay'];
    for (const retailerId of retailers) {
      const { error: priceErr } = await supabase
        .from('product_prices')
        .upsert({
          product_id: old.id,
          retailer_id: retailerId,
          current_price: old.best_price || 0,
          original_price: old.original_price || old.best_price,
          discount_percentage: old.discount_percentage || 0,
          in_stock: old.availability !== false,
          url: null,
        }, { onConflict: 'product_id,retailer_id' });

      if (priceErr) {
        console.error(`  ✗ Failed to create price for ${retailerId}: ${priceErr.message}`);
      }
    }
    console.log(`  ✓ Created prices for ${retailers.length} retailers`);
  }

  console.log('\n✅ Migration complete!');
}

migrate().catch(console.error);