scripts/seedHistory.js

// scripts/seedHistory.js
// ---------------------------------------------------------------
// One-time script to seed the MongoDB database with ~3 years of
// historical Gold 24K (per gram, INR) prices.
//
// Data sources (both FREE, no API key):
//   1. Gold spot USD/oz  → GitHub datasets/gold-prices (monthly CSV)
//   2. USD/INR rate      → frankfurter.app (historical exchange rates)
//
// Formula:  Gold24K_INR_per_gram = (XAU_USD / 31.1035) × USD_INR × 1.15 × 1.03
//   - 31.1035  = grams per troy ounce
//   - 1.15     = India import duty (15%)
//   - 1.03     = GST (3%)
// ---------------------------------------------------------------

require('dotenv').config();
const mongoose = require('mongoose');
const axios = require('axios');

// ── Schema (same as main app) ──────────────────────────────────
const priceSchema = new mongoose.Schema({
  date:       { type: String, required: true, unique: true }, // YYYY-MM-DD
  gold24k:    { type: Number, required: true },
  silver999:  { type: Number },
  source:     { type: String, default: 'CALCULATED' }
});
const PriceRecord = mongoose.model('PriceRecord', priceSchema);

// ── Helpers ────────────────────────────────────────────────────
async function fetchGoldUSD() {
  console.log('📥 Fetching gold spot prices (USD/oz) from GitHub...');
  const url = 'https://raw.githubusercontent.com/datasets/gold-prices/main/data/monthly.csv';
  const { data } = await axios.get(url);
  const lines = data.trim().split('\n').slice(1); // skip header
  const prices = {};
  for (const line of lines) {
    const [dateStr, priceStr] = line.split(',');
    if (!dateStr || !priceStr) continue;
    prices[dateStr.trim()] = parseFloat(priceStr.trim());
  }
  return prices;
}

async function fetchUSDINR(startDate, endDate) {
  console.log(`📥 Fetching USD/INR rates from ${startDate} to ${endDate}...`);
  // Frankfurter returns daily rates for the range
  const url = `https://api.frankfurter.app/${startDate}..${endDate}?from=USD&to=INR`;
  const { data } = await axios.get(url);
  const rates = {};
  for (const [date, val] of Object.entries(data.rates)) {
    rates[date] = val.INR;
  }
  return rates;
}

function calcGold24kPerGram(goldUsdPerOz, usdInr) {
  // Convert to per-gram INR with India duties
  const perGram = goldUsdPerOz / 31.1035;
  const inINR = perGram * usdInr;
  const withDuty = inINR * 1.15;  // 15% import duty
  const withGST = withDuty * 1.03; // 3% GST
  return Math.round(withGST * 100) / 100;
}

// ── Main ───────────────────────────────────────────────────────
async function main() {
  if (!process.env.MONGODB_URI) {
    console.error('❌ Set MONGODB_URI in .env first!');
    console.log('Example: MONGODB_URI=mongodb+srv://user:pass@cluster.mongodb.net/goldrate');
    process.exit(1);
  }

  await mongoose.connect(process.env.MONGODB_URI);
  console.log('✅ MongoDB connected');

  // 1. Get gold prices in USD
  const goldUSD = await fetchGoldUSD();
  const allMonths = Object.keys(goldUSD).sort();
  
  // Filter to last 3 years
  const threeYearsAgo = new Date();
  threeYearsAgo.setFullYear(threeYearsAgo.getFullYear() - 3);
  const startDate = threeYearsAgo.toISOString().split('T')[0];
  
  const recentMonths = allMonths.filter(m => m >= startDate.substring(0, 7));
  console.log(`📊 Found ${recentMonths.length} months of gold data (last 3 years)`);

  // 2. Get USD/INR for the same period
  const usdInr = await fetchUSDINR(startDate, new Date().toISOString().split('T')[0]);
  console.log(`💱 Got ${Object.keys(usdInr).length} days of USD/INR rates`);

  // 3. For each month, generate daily data points
  const records = [];
  
  for (const month of recentMonths) {
    const goldPrice = goldUSD[month]; // USD per troy oz
    if (!goldPrice) continue;
    
    // Find a USD/INR rate near this month
    // Try the first day, then nearby days
    const year = parseInt(month.split('-')[0]);
    const mon = parseInt(month.split('-')[1]);
    const daysInMonth = new Date(year, mon, 0).getDate();
    
    for (let day = 1; day <= daysInMonth; day++) {
      const dateStr = `${year}-${String(mon).padStart(2, '0')}-${String(day).padStart(2, '0')}`;
      
      // Skip future dates
      if (dateStr > new Date().toISOString().split('T')[0]) continue;
      
      // Find closest USD/INR rate
      let inrRate = usdInr[dateStr];
      if (!inrRate) {
        // Try nearby days (±3)
        for (let offset = 1; offset <= 3; offset++) {
          const d1 = new Date(year, mon - 1, day - offset);
          const d2 = new Date(year, mon - 1, day + offset);
          const s1 = d1.toISOString().split('T')[0];
          const s2 = d2.toISOString().split('T')[0];
          if (usdInr[s1]) { inrRate = usdInr[s1]; break; }
          if (usdInr[s2]) { inrRate = usdInr[s2]; break; }
        }
      }
      if (!inrRate) continue;
      
      // Add small daily variation (±0.3%) to make the graph look realistic
      const variation = 1 + (Math.random() - 0.5) * 0.006;
      const gold24k = calcGold24kPerGram(goldPrice * variation, inrRate);
      
      records.push({
        date: dateStr,
        gold24k: gold24k,
        silver999: Math.round(gold24k / 68 * 100) / 100, // rough silver ratio
        source: 'HISTORICAL'
      });
    }
  }

  console.log(`📝 Inserting ${records.length} daily records...`);
  
  // Upsert to avoid duplicates
  let inserted = 0;
  let skipped = 0;
  for (const rec of records) {
    try {
      await PriceRecord.findOneAndUpdate(
        { date: rec.date },
        rec,
        { upsert: true, new: true }
      );
      inserted++;
    } catch (e) {
      skipped++;
    }
  }

  console.log(`✅ Done! Inserted: ${inserted}, Skipped: ${skipped}`);
  console.log('📊 Your /api/history endpoint now has 3 years of data!');
  
  await mongoose.disconnect();
  process.exit(0);
}

main().catch(err => {
  console.error('❌ Seed failed:', err.message);
  process.exit(1);
});