cash_tracker/server/routes/transactions.js

const express = require('express');
const router = express.Router();
const db = require('../database');

// Get transactions by month/year
router.get('/:year/:month', (req, res) => {
  const { year, month } = req.params;
  
  const query = `
    SELECT t.*, c.name as card_name, c.bank, cat.name as category_name
    FROM transactions t
    JOIN cards c ON t.card_id = c.id
    JOIN categories cat ON t.category_id = cat.id
    WHERE strftime('%Y', t.date) = ? AND strftime('%m', t.date) = ?
    ORDER BY t.date DESC, t.created_at DESC
  `;
  
  // Pad month with zero if needed
  const paddedMonth = month.toString().padStart(2, '0');
  
  db.all(query, [year, paddedMonth], (err, rows) => {
    if (err) {
      console.error(err);
      res.status(500).json({ error: 'Database error' });
    } else {
      res.json(rows);
    }
  });
});

// Create transaction
router.post('/', (req, res) => {
  const { card_id, category_id, amount, date, description } = req.body;
  
  if (!card_id || !category_id || !amount || !date) {
    return res.status(400).json({ error: 'Card, category, amount, and date are required' });
  }

  // First, get the cashback percentage for this card/category combination
  const transactionDate = new Date(date);
  const month = transactionDate.getMonth() + 1;
  const year = transactionDate.getFullYear();

  const getCashbackQuery = `
    SELECT cashback_percent 
    FROM monthly_categories 
    WHERE card_id = ? AND category_id = ? AND month = ? AND year = ?
  `;

  db.get(getCashbackQuery, [card_id, category_id, month, year], (err, row) => {
    if (err) {
      console.error(err);
      return res.status(500).json({ error: 'Database error' });
    }
    
    if (!row) {
      return res.status(400).json({ 
        error: 'No cashback percentage found for this card/category combination in the specified month' 
      });
    }

    const cashback_amount = (amount * row.cashback_percent) / 100;

    db.run(
      'INSERT INTO transactions (card_id, category_id, amount, cashback_amount, date, description) VALUES (?, ?, ?, ?, ?, ?)',
      [card_id, category_id, amount, cashback_amount, date, description],
      function(err) {
        if (err) {
          console.error(err);
          res.status(500).json({ error: 'Database error' });
        } else {
          res.status(201).json({ 
            id: this.lastID,
            card_id,
            category_id,
            amount,
            cashback_amount,
            date,
            description
          });
        }
      }
    );
  });
});

// Update transaction
router.put('/:id', (req, res) => {
  const { id } = req.params;
  const { card_id, category_id, amount, date, description } = req.body;

  if (!card_id || !category_id || !amount || !date) {
    return res.status(400).json({ error: 'Card, category, amount, and date are required' });
  }

  // Recalculate cashback amount
  const transactionDate = new Date(date);
  const month = transactionDate.getMonth() + 1;
  const year = transactionDate.getFullYear();

  const getCashbackQuery = `
    SELECT cashback_percent 
    FROM monthly_categories 
    WHERE card_id = ? AND category_id = ? AND month = ? AND year = ?
  `;

  db.get(getCashbackQuery, [card_id, category_id, month, year], (err, row) => {
    if (err) {
      console.error(err);
      return res.status(500).json({ error: 'Database error' });
    }
    
    if (!row) {
      return res.status(400).json({ 
        error: 'No cashback percentage found for this card/category combination in the specified month' 
      });
    }

    const cashback_amount = (amount * row.cashback_percent) / 100;

    db.run(
      'UPDATE transactions SET card_id = ?, category_id = ?, amount = ?, cashback_amount = ?, date = ?, description = ? WHERE id = ?',
      [card_id, category_id, amount, cashback_amount, date, description, id],
      function(err) {
        if (err) {
          console.error(err);
          res.status(500).json({ error: 'Database error' });
        } else if (this.changes === 0) {
          res.status(404).json({ error: 'Transaction not found' });
        } else {
          res.json({ 
            id: parseInt(id),
            card_id,
            category_id,
            amount,
            cashback_amount,
            date,
            description
          });
        }
      }
    );
  });
});

// Delete transaction
router.delete('/:id', (req, res) => {
  const { id } = req.params;
  
  db.run('DELETE FROM transactions WHERE id = ?', [id], function(err) {
    if (err) {
      console.error(err);
      res.status(500).json({ error: 'Database error' });
    } else if (this.changes === 0) {
      res.status(404).json({ error: 'Transaction not found' });
    } else {
      res.json({ message: 'Transaction deleted successfully' });
    }
  });
});

module.exports = router;