accounting/server/routes/records.js

const express = require('express');
const { getDb } = require('../db');
const { createUserNameResolver, normalizeAmount } = require('../utils/accounting');

const router = express.Router();

function getUsers(db) {
  return db.all('SELECT id, name FROM users ORDER BY sort_order, id');
}

function resolveUserName(db, inputName, fallbackName = '') {
  const users = getUsers(db);
  const defaultName = fallbackName || users[0]?.name || '';
  const resolveName = createUserNameResolver(users);
  const userName = resolveName(inputName, defaultName);

  if (!userName) {
    return { error: '请先创建用户' };
  }

  const exists = users.some((user) => user.name === userName);
  if (!exists) {
    return { error: '用户不存在，请先在记账页创建用户' };
  }

  return { userName };
}

function validateRecordPayload(db, payload, existingRecord = null) {
  const date = payload.date ?? existingRecord?.date;
  if (!/^\d{4}-\d{2}-\d{2}$/.test(String(date ?? ''))) {
    return { error: '日期格式不正确' };
  }

  const type = payload.type ?? existingRecord?.type;
  if (!['income', 'expense'].includes(type)) {
    return { error: '收支类型不正确' };
  }

  const categoryIdValue = payload.category_id ?? existingRecord?.category_id;
  const categoryId = Number.parseInt(categoryIdValue, 10);
  if (!Number.isInteger(categoryId)) {
    return { error: '分类不能为空' };
  }

  const category = db.get('SELECT id, type FROM categories WHERE id = ?', categoryId);
  if (!category) {
    return { error: '分类不存在' };
  }
  if (category.type !== type) {
    return { error: '分类和收支类型不匹配，请重新选择分类' };
  }

  const rawAmount = payload.amount !== undefined ? payload.amount : existingRecord?.amount;
  const amount = normalizeAmount(rawAmount);
  if (amount === null || amount <= 0) {
    return { error: '金额必须大于 0，且最多保留两位小数' };
  }

  const resolvedUser = resolveUserName(db, payload.user, existingRecord?.user);
  if (resolvedUser.error) {
    return resolvedUser;
  }

  return {
    record: {
      date,
      type,
      category_id: categoryId,
      amount,
      note: payload.note === undefined
        ? (existingRecord?.note ?? null)
        : (String(payload.note || '').trim() || null),
      user: resolvedUser.userName,
    },
  };
}

function getRecordWithCategory(db, id) {
  return db.get(`
    SELECT r.*, c.name AS category_name, c.type AS category_type, c.icon AS category_icon
    FROM records r
    LEFT JOIN categories c ON r.category_id = c.id
    WHERE r.id = ?
  `, id);
}

router.get('/', (req, res) => {
  const db = getDb();
  const { date, month, start, end, page, limit = 50 } = req.query;

  let sql = `
    SELECT r.*, c.name AS category_name, c.type AS category_type, c.icon AS category_icon
    FROM records r
    LEFT JOIN categories c ON r.category_id = c.id
  `;
  const conditions = [];
  const params = [];

  if (date) {
    conditions.push('r.date = ?');
    params.push(date);
  } else if (start && end) {
    conditions.push('r.date BETWEEN ? AND ?');
    params.push(start, end);
  } else if (month) {
    conditions.push('r.date LIKE ?');
    params.push(`${month}%`);
  }

  if (conditions.length > 0) {
    sql += ` WHERE ${conditions.join(' AND ')}`;
  }

  sql += ' ORDER BY r.date DESC, r.created_at DESC, r.id DESC';

  if (page) {
    const safeLimit = Number.parseInt(limit, 10) || 50;
    const offset = (Number.parseInt(page, 10) - 1) * safeLimit;
    sql += ' LIMIT ? OFFSET ?';
    params.push(safeLimit, offset);
  }

  res.json(db.all(sql, ...params));
});

router.post('/', (req, res) => {
  const db = getDb();
  const validated = validateRecordPayload(db, req.body);
  if (validated.error) {
    return res.status(400).json({ error: validated.error });
  }

  const { record } = validated;
  const result = db.run(
    'INSERT INTO records (date, type, category_id, amount, note, user) VALUES (?, ?, ?, ?, ?, ?)',
    record.date,
    record.type,
    record.category_id,
    record.amount,
    record.note,
    record.user
  );

  res.status(201).json(getRecordWithCategory(db, result.lastInsertRowid));
});

router.put('/:id', (req, res) => {
  const db = getDb();
  const id = Number.parseInt(req.params.id, 10);
  const existingRecord = db.get('SELECT * FROM records WHERE id = ?', id);

  if (!existingRecord) {
    return res.status(404).json({ error: '记录不存在' });
  }

  const validated = validateRecordPayload(db, req.body, existingRecord);
  if (validated.error) {
    return res.status(400).json({ error: validated.error });
  }

  const { record } = validated;
  db.run(`
    UPDATE records
    SET date = ?, type = ?, category_id = ?, amount = ?, note = ?, user = ?, updated_at = CURRENT_TIMESTAMP
    WHERE id = ?
  `,
  record.date,
  record.type,
  record.category_id,
  record.amount,
  record.note,
  record.user,
  id);

  res.json(getRecordWithCategory(db, id));
});

router.delete('/:id', (req, res) => {
  const db = getDb();
  const id = Number.parseInt(req.params.id, 10);
  const result = db.run('DELETE FROM records WHERE id = ?', id);

  if (result.changes === 0) {
    return res.status(404).json({ error: '记录不存在' });
  }

  res.json({ message: '删除成功' });
});

module.exports = router;