server/seed_data.js

const sqlite3 = require('sqlite3');
const { open } = require('sqlite');
const path = require('path');
const { subMonths, addDays, startOfMonth } = require('date-fns');

async function seed() {
  const dbPath = path.resolve(__dirname, 'database.sqlite');
  const db = await open({
    filename: dbPath,
    driver: sqlite3.Database
  });

  console.log('Cleaning up existing data...');
  await db.exec('DELETE FROM transactions');
  await db.exec('DELETE FROM exchanges');
  await db.exec('DELETE FROM loans');

  const wallets = await db.all('SELECT * FROM wallets');
  if (wallets.length === 0) {
    console.error('No wallets found in DB.');
    return;
  }

  const findWalletByCurrency = (curr) => wallets.find(w => w.currency === curr) || wallets[0];
  const usdWallet = wallets.find(w => w.currency === 'USD') || wallets[0];
  const iqdWallet = wallets.find(w => w.currency === 'IQD') || wallets[0];
  const rmbWallet = wallets.find(w => w.currency === 'RMB') || wallets[0];

  const startDate = startOfMonth(subMonths(new Date(), 24)); // 2 Years
  const today = new Date();

  console.log('Seeding 2 years of data...');

  let currentDate = startDate;
  let baseSalary = 3000;

  while (currentDate <= today) {
    const dateStr = currentDate.toISOString();

    // 1. Monthly Salary (USD) - with variability
    if (currentDate.getDate() === 1) {
      // Increase salary every 12 months
      const monthsSinceStart = Math.floor((currentDate - startDate) / (1000 * 60 * 60 * 24 * 30));
      if (monthsSinceStart > 0 && monthsSinceStart % 12 === 0) {
        baseSalary += 500;
        console.log(`Salary increase at month ${monthsSinceStart} to ${baseSalary}`);
      }

      const salaryAmount = baseSalary + (Math.random() > 0.8 ? 500 : 0); // Occasional bonus
      await db.run(
        'INSERT INTO transactions (type, amount, currency, wallet_id, category, note, date) VALUES (?, ?, ?, ?, ?, ?, ?)',
        ['income', salaryAmount, 'USD', usdWallet.id, 'salary', monthsSinceStart % 12 === 0 && monthsSinceStart > 0 ? 'Salary Increase + Payment' : 'Monthly Salary', dateStr]
      );
    }

    // 2. Daily Expenses - More variety
    const expenseCount = Math.floor(Math.random() * 4) + 1; // 1-4 expenses per day
    const categories = ['food', 'market', 'transport', 'cafe', 'health', 'gift', 'other'];
    const currencies = ['USD', 'IQD', 'RMB'];
    
    for (let i = 0; i < expenseCount; i++) {
        const cat = categories[Math.floor(Math.random() * categories.length)];
        const curr = currencies[Math.floor(Math.random() * currencies.length)];
        const wallet = findWalletByCurrency(curr);
        let amount;

        if (curr === 'USD') {
            amount = Math.random() * 60 + 5;
        } else if (curr === 'IQD') {
            amount = Math.random() * 60000 + 5000;
        } else {
            amount = Math.random() * 300 + 20;
        }

        await db.run(
          'INSERT INTO transactions (type, amount, currency, wallet_id, category, note, date) VALUES (?, ?, ?, ?, ?, ?, ?)',
          ['expense', amount, curr, wallet.id, cat, `Daily ${cat}`, dateStr]
        );
    }

    // 3. Rent & Utilities (USD)
    if (currentDate.getDate() === 15) {
        await db.run(
          'INSERT INTO transactions (type, amount, currency, wallet_id, category, note, date) VALUES (?, ?, ?, ?, ?, ?, ?)',
          ['expense', 850 + (Math.random() * 50), 'USD', usdWallet.id, 'electricity', 'Rent & Utilities', dateStr]
        );
    }

    // 4. Frequent Exchanges (Varying amounts)
    if (Math.random() > 0.85) {
        // Exchange USD to IQD or RMB
        const targetCurr = Math.random() > 0.5 ? 'IQD' : 'RMB';
        const targetWallet = findWalletByCurrency(targetCurr);
        const usdAmount = 100 + Math.floor(Math.random() * 400);
        const rate = targetCurr === 'IQD' ? 1500 + (Math.random() * 50) : 6.8 + (Math.random() * 0.5);
        const targetAmount = usdAmount * rate;

        await db.run(
            'INSERT INTO exchanges (from_amount, from_currency, to_amount, to_currency, rate, date, note, from_wallet_id, to_wallet_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
            [usdAmount, 'USD', targetAmount, targetCurr, rate, dateStr, `Exchanged USD to ${targetCurr}`, usdWallet.id, targetWallet.id]
        );
    }

    currentDate = addDays(currentDate, 1);
  }

  // 5. Some historical loans
  await db.run(
    'INSERT INTO loans (person, type, amount, currency, paid, date, note) VALUES (?, ?, ?, ?, ?, ?, ?)',
    ['Karwan', 'borrowed_from_me', 2000, 'USD', 1200, subMonths(today, 18).toISOString(), 'Long term loan']
  );
  await db.run(
    'INSERT INTO loans (person, type, amount, currency, paid, date, note) VALUES (?, ?, ?, ?, ?, ?, ?)',
    ['Uncle', 'borrowed_from_me', 1500000, 'IQD', 1500000, subMonths(today, 6).toISOString(), 'Car help (Paid back)']
  );

  console.log('Seeding 2 years completed successfully.');
  await db.close();
}

seed().catch(err => {
  console.error('Error seeding data:', err);
  process.exit(1);
});