scripts/import-historical-csv.js

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

// Initialize Supabase client
const supabaseUrl = process.env.SUPABASE_URL;
const supabaseKey = process.env.SUPABASE_SERVICE_ROLE_KEY;

if (!supabaseUrl || !supabaseKey) {
  console.error('Missing SUPABASE_URL or SUPABASE_SERVICE_ROLE_KEY in .env');
  process.exit(1);
}

const supabase = createClient(supabaseUrl, supabaseKey);

// Helper function to parse date from DD-MMM-YYYY to YYYY-MM-DD
const parseDate = (dateStr) => {
  const months = {
    'JAN': '01', 'FEB': '02', 'MAR': '03', 'APR': '04', 'MAY': '05', 'JUN': '06',
    'JUL': '07', 'AUG': '08', 'SEP': '09', 'OCT': '10', 'NOV': '11', 'DEC': '12'
  };
  const parts = dateStr.split('-');
  const day = parts[0].padStart(2, '0');
  const month = months[parts[1].toUpperCase()];
  const year = parts[2];
  return `${year}-${month}-${day}`;
};

// Helper function to parse CSV file
const parseCsv = (filePath) => {
  const content = fs.readFileSync(filePath, 'utf-8');
  const lines = content.split('\n').filter(line => line.trim());
  const data = [];
  
  // Skip header line (lines[0])
  for (let i = 1; i < lines.length; i++) {
    const line = lines[i];
    // Split by commas, handling quotes properly
    const parts = line.match(/(".*?"|[^",\s]+)(?=\s*,|\s*$)/g) || [];
    if (parts.length >= 5) {
      const date = parseDate(parts[2].replace(/"/g, ''));
      // Parse Session 1 price (remove commas and convert to number)
      const priceStr = parts[3].replace(/"/g, '').replace(/,/g, '');
      const price = parseFloat(priceStr);
      
      if (!isNaN(price)) {
        data.push({
          date: date,
          gold24k: price,
          silver999: null, // We don't have silver data in these CSVs
          source: 'NSE Historical'
        });
      }
    }
  }
  
  return data;
};

const main = async () => {
  try {
    const csvFiles = [
      'c:\\HI\\Historical-Spot-Price-GOLD1G-26-06-2023-to-26-06-2024.csv',
      'c:\\HI\\Historical-Spot-Price-GOLD1G-26-06-2024-to-26-06-2025.csv',
      'c:\\HI\\Historical-Spot-Price-GOLD1G-26-06-2025-to-26-06-2026 (2).csv'
    ];

    let allData = [];
    for (const file of csvFiles) {
      if (fs.existsSync(file)) {
        console.log(`Parsing ${file}...`);
        const data = parseCsv(file);
        allData = allData.concat(data);
        console.log(`Parsed ${data.length} records from ${file}`);
      }
    }

    console.log(`Total records to import: ${allData.length}`);
    
    // Insert data in chunks to avoid rate limits
    const chunkSize = 100;
    for (let i = 0; i < allData.length; i += chunkSize) {
      const chunk = allData.slice(i, i + chunkSize);
      const { error } = await supabase
        .from('price_records')
        .upsert(chunk, { onConflict: 'date' });
      
      if (error) {
        console.error('Error inserting chunk:', error);
      } else {
        console.log(`Inserted ${i + chunk.length}/${allData.length} records`);
      }
    }

    console.log('✅ Historical data imported successfully!');
  } catch (err) {
    console.error('Error importing data:', err);
  }
};

main();