scripts/seed-metals-api.js

require('dotenv').config();
const { createClient } = require('@supabase/supabase-js');
const axios = require('axios');

const TROY_OUNCE_TO_GRAMS = 31.1034768;
const IMPORT_DUTY = 1.15;
const GST = 1.03;

async function fetchHistoricalData(startDate, endDate) {
  const apiKey = process.env.METALS_API_KEY;
  const url = `https://metals-api.com/api/timeseries?access_key=${apiKey}&start_date=${startDate}&end_date=${endDate}&base=USD&symbols=XAU,XAG,INR`;
  
  const response = await axios.get(url);
  return response.data;
}

function convertToINRPerGram(usdPerTroyOunce, usdToInr) {
  const usdPerGram = usdPerTroyOunce / TROY_OUNCE_TO_GRAMS;
  const inrPerGram = usdPerGram * usdToInr;
  const withDuty = inrPerGram * IMPORT_DUTY;
  const withGst = withDuty * GST;
  return Math.round(withGst * 100) / 100;
}

async function main() {
  try {
    if (!process.env.METALS_API_KEY) {
      console.error('❌ Please set METALS_API_KEY in .env first!');
      process.exit(1);
    }

    if (!process.env.SUPABASE_URL || !process.env.SUPABASE_SERVICE_ROLE_KEY) {
      console.error('❌ Please set SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY in .env first!');
      process.exit(1);
    }

    const supabase = createClient(process.env.SUPABASE_URL, process.env.SUPABASE_SERVICE_ROLE_KEY);
    console.log('✅ Connected to Supabase');

    const endDate = new Date().toISOString().split('T')[0];
    const startDateObj = new Date();
    startDateObj.setFullYear(startDateObj.getFullYear() - 3);
    const startDate = startDateObj.toISOString().split('T')[0];
    console.log(`📊 Fetching data from ${startDate} to ${endDate}...`);

    const data = await fetchHistoricalData(startDate, endDate);

    if (!data.success) {
      console.error('❌ Failed to fetch historical data:', data.error);
      process.exit(1);
    }

    const rates = data.rates;
    const dates = Object.keys(rates).sort();
    const records = [];

    for (const date of dates) {
      const dailyRates = rates[date];
      const xauUsd = 1 / dailyRates.XAU;
      const xagUsd = 1 / dailyRates.XAG;
      const usdInr = dailyRates.INR;

      const gold24k = convertToINRPerGram(xauUsd, usdInr);
      const silver999 = convertToINRPerGram(xagUsd, usdInr);

      records.push({
        date,
        gold24k,
        silver999,
        source: 'Metals-API'
      });
    }

    console.log(`💾 Inserting/Updating ${records.length} records...`);
    
    // Bulk insert/upsert
    const { error } = await supabase
      .from('price_records')
      .upsert(records);

    if (error) {
      console.error('❌ Failed to insert records:', error);
      process.exit(1);
    }

    console.log(`✅ Done! All records saved!`);
    process.exit(0);
  } catch (err) {
    console.error('❌ Seed failed:', err);
    process.exit(1);
  }
}

main();