const mysql = require('mysql2/promise');

class ExchangeRateService {
  constructor() {
    this.dbConfig = {
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASS,
      database: process.env.DB_NAME
    };
  }

  async getCurrentRates() {
    const connection = await mysql.createConnection(this.dbConfig);
    try {
      const [rows] = await connection.execute(`
        SELECT currency_code, rate_to_chf, rate_date 
        FROM daily_exchange_rates 
        WHERE rate_date = (
          SELECT MAX(rate_date) FROM daily_exchange_rates
        )
        ORDER BY currency_code
      `);
      const rates = {};
      rows.forEach(r => rates[r.currency_code] = { rate: parseFloat(r.rate_to_chf), date: r.rate_date });
      return rates;
    } finally {
      await connection.end();
    }
  }

  async getRate(currency) {
    if (!currency) return 1.0;
    if (currency === 'CHF') return 1.0;
    const connection = await mysql.createConnection(this.dbConfig);
    try {
      const [rows] = await connection.execute(`
        SELECT rate_to_chf 
        FROM daily_exchange_rates 
        WHERE currency_code = ? 
        ORDER BY rate_date DESC 
        LIMIT 1
      `, [currency]);
      return rows.length > 0 ? parseFloat(rows[0].rate_to_chf) : 1.0;
    } finally {
      await connection.end();
    }
  }

  async convert(amount, fromCurrency, toCurrency = 'CHF') {
    const amt = Number(amount || 0);
    if (!amt) return 0;
    if (fromCurrency === toCurrency) return Math.round(amt * 100) / 100;
    const rateFrom = await this.getRate(fromCurrency);
    const rateTo = await this.getRate(toCurrency);
    const amountInCHF = amt * rateFrom;
    const converted = amountInCHF / rateTo;
    return Math.round(converted * 100) / 100;
  }
}

module.exports = new ExchangeRateService();

