BillTracker/routes/payments.js

const express = require('express');
const { standardizeError } = require('../middleware/errorFormatter');
const router  = require('express').Router();
const { getDb } = require('../db/database');

const LIVE = 'deleted_at IS NULL'; // filter for non-deleted payments

// GET /api/payments?bill_id=&year=&month=
router.get('/', (req, res) => {
  const db = getDb();
  const { bill_id, year, month } = req.query;

  // Validate year/month when provided
  if ((year || month) && !(year && month)) {
    return res.status(400).json(standardizeError('Both year and month are required when filtering by date', 'VALIDATION_ERROR', 'year'));
  }

  let y, m;
  if (year && month) {
    y = parseInt(year, 10);
    m = parseInt(month, 10);
    if (!Number.isInteger(y) || y < 2000 || y > 2100) {
      return res.status(400).json(standardizeError('year must be a 4-digit integer between 2000 and 2100', 'VALIDATION_ERROR', 'year'));
    }
    if (!Number.isInteger(m) || m < 1 || m > 12) {
      return res.status(400).json(standardizeError('month must be an integer between 1 and 12', 'VALIDATION_ERROR', 'month'));
    }
  }

  let query  = `SELECT p.* FROM payments p JOIN bills b ON b.id = p.bill_id WHERE p.${LIVE} AND b.user_id = ?`;
  const params = [req.user.id];

  if (bill_id) { query += ' AND p.bill_id = ?'; params.push(parseInt(bill_id, 10)); }

  if (y && m) {
    const yStr = String(y);
    const mStr = String(m).padStart(2, '0');
    const daysInMonth = new Date(y, m, 0).getDate();
    const endDay = String(daysInMonth).padStart(2, '0');
    query += ' AND p.paid_date BETWEEN ? AND ?';
    params.push(`${yStr}-${mStr}-01`, `${yStr}-${mStr}-${endDay}`);
  }

  query += ' ORDER BY p.paid_date DESC';
  res.json(db.prepare(query).all(...params));
});

// GET /api/payments/:id
router.get('/:id', (req, res) => {
  const db = getDb();
  const payment = db.prepare(`SELECT p.* FROM payments p JOIN bills b ON b.id = p.bill_id WHERE p.id = ? AND p.${LIVE} AND b.user_id = ?`).get(req.params.id, req.user.id);
  if (!payment) return res.status(404).json(standardizeError('Payment not found', 'NOT_FOUND', 'id'));
  res.json(payment);
});

// POST /api/payments — create single payment
router.post('/', (req, res) => {
  const db = getDb();
  const { bill_id, amount, paid_date, method, notes } = req.body;

  if (!bill_id || amount == null || !paid_date)
    return res.status(400).json(standardizeError('bill_id, amount, and paid_date are required', 'VALIDATION_ERROR', 'bill_id'));

  const parsedAmount = parseFloat(amount);
  if (isNaN(parsedAmount) || parsedAmount <= 0)
    return res.status(400).json(standardizeError('amount must be a positive number', 'VALIDATION_ERROR', 'amount'));

  if (!db.prepare('SELECT id FROM bills WHERE id = ? AND user_id = ?').get(bill_id, req.user.id))
    return res.status(404).json(standardizeError('Bill not found', 'NOT_FOUND', 'bill_id'));

  const result = db.prepare(
    'INSERT INTO payments (bill_id, amount, paid_date, method, notes) VALUES (?, ?, ?, ?, ?)'
  ).run(bill_id, parsedAmount, paid_date, method || null, notes || null);

  res.status(201).json(db.prepare('SELECT * FROM payments WHERE id = ?').get(result.lastInsertRowid));
});

// POST /api/payments/quick — pay a bill (expected amount, today)
router.post('/quick', (req, res) => {
  const db = getDb();
  const { bill_id, amount, paid_date, method, notes } = req.body;

  if (!bill_id) return res.status(400).json(standardizeError('bill_id is required', 'VALIDATION_ERROR', 'bill_id'));

  const bill = db.prepare('SELECT * FROM bills WHERE id = ? AND user_id = ?').get(bill_id, req.user.id);
  if (!bill) return res.status(404).json(standardizeError('Bill not found', 'NOT_FOUND', 'bill_id'));

  const payAmount = amount != null ? parseFloat(amount) : bill.expected_amount;
  if (isNaN(payAmount) || payAmount <= 0)
    return res.status(400).json(standardizeError('amount must be a positive number', 'VALIDATION_ERROR', 'amount'));

  const payDate   = paid_date || new Date().toISOString().slice(0, 10);

  const result = db.prepare(
    'INSERT INTO payments (bill_id, amount, paid_date, method, notes) VALUES (?, ?, ?, ?, ?)'
  ).run(bill_id, payAmount, payDate, method || null, notes || null);

  if (bill.autopay_enabled) {
    db.prepare("UPDATE bills SET autodraft_status='confirmed', updated_at=datetime('now') WHERE id=?").run(bill_id);
  }

  res.status(201).json(db.prepare('SELECT * FROM payments WHERE id = ?').get(result.lastInsertRowid));
});

// POST /api/payments/bulk — record multiple payments in one request
// Bulk payment creation endpoint
// Validation rules:
// - Request body must contain a `payments` array
// - Maximum 50 items per request
// - Each item requires: bill_id (integer), paid_date (valid date), amount (number >= 0)
// - Duplicate payments (same bill_id + paid_date + amount) are skipped, not created
// - Returns { created: [...], skipped: [...], errors: [...] }
router.post('/bulk', (req, res) => {
  const db = getDb();
  const { payments } = req.body;

  // Validate request body has payments array
  if (!payments || !Array.isArray(payments))
    return res.status(400).json(standardizeError('Request body must contain a `payments` array', 'VALIDATION_ERROR', 'payments'));

  // Validate max items per request (50)
  if (payments.length > 50)
    return res.status(400).json(standardizeError('Maximum 50 items allowed per request', 'VALIDATION_ERROR', 'payments'));

  // Validate each payment item
  for (let i = 0; i < payments.length; i++) {
    const item = payments[i];
    if (!item.bill_id || item.amount == null || !item.paid_date) {
      return res.status(400).json(standardizeError(`Payment at index ${i}: bill_id, amount, and paid_date are required`, 'VALIDATION_ERROR', `payments[${i}]`));
    }
    
    // Validate bill_id is an integer (check original input to prevent parseInt coercion)
    const billIdStr = String(item.bill_id).trim();
    const billIdInt = parseInt(billIdStr, 10);
    if (!/^\d+$/.test(billIdStr) || !Number.isInteger(billIdInt)) {
      return res.status(400).json(standardizeError(`Payment at index ${i}: bill_id must be an integer`, 'VALIDATION_ERROR', `payments[${i}].bill_id`));
    }
    
    // Validate paid_date is a valid date string
    const dateRegex = /^\d{4}-\d{2}-\d{2}$/;
    if (!dateRegex.test(item.paid_date)) {
      return res.status(400).json(standardizeError(`Payment at index ${i}: paid_date must be a valid date in YYYY-MM-DD format`, 'VALIDATION_ERROR', `payments[${i}].paid_date`));
    }
    
    // Validate amount is a finite number >= 0 (reject Infinity/NaN)
    const parsedAmt = parseFloat(item.amount);
    if (isNaN(parsedAmt) || parsedAmt < 0 || !isFinite(parsedAmt)) {
      return res.status(400).json(standardizeError(`Payment at index ${i}: amount must be a number >= 0`, 'VALIDATION_ERROR', `payments[${i}].amount`));
    }
  }

  const insert = db.prepare(
    'INSERT INTO payments (bill_id, amount, paid_date, method, notes) VALUES (?, ?, ?, ?, ?)'
  );

  // Prepare statement for duplicate checking
  const duplicateCheckStmt = db.prepare(
    `SELECT 1 FROM payments p 
     JOIN bills b ON b.id = p.bill_id 
     WHERE b.user_id = ? 
     AND p.bill_id = ? 
     AND p.paid_date = ? 
     AND p.amount = ? 
     AND p.${LIVE}`
  );

  const created = [];
  const skipped = [];
  const errors  = [];

  const runBulk = db.transaction(() => {
    for (const item of payments) {
      const bill_id = parseInt(String(item.bill_id).trim(), 10);
      const parsedAmt = parseFloat(item.amount);
      const { paid_date, method, notes } = item;
      
      // Check for duplicates using composite key (bill_id + paid_date + amount)
      const isDuplicate = duplicateCheckStmt.get(req.user.id, bill_id, paid_date, parsedAmt);
      if (isDuplicate) {
        skipped.push({ bill_id, paid_date, amount: parsedAmt });
        continue;
      }
      
      if (!db.prepare('SELECT id FROM bills WHERE id = ? AND user_id = ?').get(bill_id, req.user.id)) {
        errors.push({ item, error: `Bill ${bill_id} not found` });
        continue;
      }
      
      const r = insert.run(bill_id, parsedAmt, paid_date, method || null, notes || null);
      created.push(db.prepare('SELECT * FROM payments WHERE id = ?').get(r.lastInsertRowid));
    }
  });

  runBulk();
  res.status(201).json({ created, skipped, errors });
});

// PUT /api/payments/:id
router.put('/:id', (req, res) => {
  const db = getDb();
  const existing = db.prepare(`SELECT p.* FROM payments p JOIN bills b ON b.id = p.bill_id WHERE p.id = ? AND p.${LIVE} AND b.user_id = ?`).get(req.params.id, req.user.id);
  if (!existing) return res.status(404).json(standardizeError('Payment not found', 'NOT_FOUND', 'id'));

  const { amount, paid_date, method, notes } = req.body;

  db.prepare(`
    UPDATE payments SET
      amount = ?, paid_date = ?, method = ?, notes = ?,
      updated_at = datetime('now')
    WHERE id = ?
  `).run(
    amount    != null      ? parseFloat(amount) : existing.amount,
    paid_date ?? existing.paid_date,
    method    !== undefined ? (method || null)  : existing.method,
    notes     !== undefined ? (notes  || null)  : existing.notes,
    req.params.id,
  );

  res.json(db.prepare('SELECT * FROM payments WHERE id = ?').get(req.params.id));
});

// DELETE /api/payments/:id — soft delete (sets deleted_at)
router.delete('/:id', (req, res) => {
  const db = getDb();
  const payment = db.prepare(`SELECT p.id FROM payments p JOIN bills b ON b.id = p.bill_id WHERE p.id = ? AND p.${LIVE} AND b.user_id = ?`).get(req.params.id, req.user.id);
  if (!payment) return res.status(404).json(standardizeError('Payment not found', 'NOT_FOUND', 'id'));
  db.prepare("UPDATE payments SET deleted_at = datetime('now') WHERE id = ?").run(req.params.id);
  res.json({ success: true });
});

// POST /api/payments/:id/restore — undo soft delete
router.post('/:id/restore', (req, res) => {
  const db = getDb();
  const payment = db.prepare('SELECT p.id FROM payments p JOIN bills b ON b.id = p.bill_id WHERE p.id = ? AND p.deleted_at IS NOT NULL AND b.user_id = ?').get(req.params.id, req.user.id);
  if (!payment) return res.status(404).json(standardizeError('Deleted payment not found', 'NOT_FOUND', 'id'));
  db.prepare('UPDATE payments SET deleted_at = NULL WHERE id = ?').run(req.params.id);
  res.json(db.prepare('SELECT * FROM payments WHERE id = ?').get(req.params.id));
});

module.exports = router;