const db = require('../config/db');
const { logAuditFromReq } = require('../middlewares/audit');

exports.getAll = async (req, res, next) => {
  try {
    const { category, from_date, to_date } = req.query;
    let sql = 'SELECT ee.*, u.full_name AS user_name FROM expense_entries ee JOIN users u ON u.id = ee.user_id WHERE 1=1';
    const params = [];

    if (category) { params.push(category); sql += ' AND ee.category = ?'; }
    if (from_date) { params.push(from_date); sql += ' AND ee.entry_date >= ?'; }
    if (to_date) { params.push(to_date); sql += ' AND ee.entry_date <= ?'; }

    sql += ' ORDER BY ee.entry_date DESC, ee.id DESC';
    const [rows] = await db.query(sql, params);
    res.json(rows);
  } catch (err) {
    next(err);
  }
};

exports.getById = async (req, res, next) => {
  try {
    const [rows] = await db.query(
      'SELECT ee.*, u.full_name AS user_name FROM expense_entries ee JOIN users u ON u.id = ee.user_id WHERE ee.id = ?',
      [req.params.id]
    );
    if (rows.length === 0) return res.status(404).json({ error: 'Expense entry not found' });
    res.json(rows[0]);
  } catch (err) {
    next(err);
  }
};

exports.create = async (req, res, next) => {
  try {
    const { entry_date, category, amount, description, receipt_url } = req.body;
    if (!entry_date || !category || amount == null) {
      return res.status(400).json({ error: 'entry_date, category and amount are required' });
    }

    const [result] = await db.query(
      `INSERT INTO expense_entries (entry_date, category, amount, description, receipt_url, user_id)
       VALUES (?,?,?,?,?,?)`,
      [entry_date, category, amount, description || null, receipt_url || null, req.user.id]
    );
    const [rows] = await db.query('SELECT * FROM expense_entries WHERE id = ?', [result.insertId]);

    logAuditFromReq(req, 'create', 'expense_entry', result.insertId, {
      category, amount, entry_date,
    });

    res.status(201).json(rows[0]);
  } catch (err) {
    next(err);
  }
};

exports.update = async (req, res, next) => {
  try {
    const { entry_date, category, amount, description, receipt_url } = req.body;
    const [result] = await db.query(
      `UPDATE expense_entries SET
         entry_date  = COALESCE(?, entry_date),
         category    = COALESCE(?, category),
         amount      = COALESCE(?, amount),
         description = COALESCE(?, description),
         receipt_url = COALESCE(?, receipt_url)
       WHERE id = ?`,
      [entry_date, category, amount, description, receipt_url, req.params.id]
    );
    if (result.affectedRows === 0) return res.status(404).json({ error: 'Expense entry not found' });
    const [rows] = await db.query('SELECT * FROM expense_entries WHERE id = ?', [req.params.id]);

    logAuditFromReq(req, 'update', 'expense_entry', parseInt(req.params.id, 10));

    res.json(rows[0]);
  } catch (err) {
    next(err);
  }
};

exports.remove = async (req, res, next) => {
  try {
    const [result] = await db.query('DELETE FROM expense_entries WHERE id = ?', [req.params.id]);
    if (result.affectedRows === 0) return res.status(404).json({ error: 'Expense entry not found' });

    logAuditFromReq(req, 'delete', 'expense_entry', parseInt(req.params.id, 10));

    res.json({ message: 'Expense entry deleted' });
  } catch (err) {
    next(err);
  }
};
