yunhaifinance/backend/routes/payment-plans.js

/**
 * 付款计划路由
 * 遵循设计方案：采购-付款-物流-退库一体化流程设计方案.md
 * 章节：五、付款计划功能
 * 
 * 功能：
 * - 订单确认后自动生成付款计划（在purchase-orders.js中实现）
 * - 支持手动调整付款计划
 * - 付款计划与付款申请关联
 * - 付款计划状态机：pending → requested → approved → paid
 */
const express = require('express');
const db = require('../db');

const router = express.Router();

/**
 * 获取付款计划列表
 * 支持按订单ID、状态筛选
 */
router.get('/', async (req, res) => {
  try {
    const { purchase_order_id, status } = req.query;
    let query = `
      SELECT pp.*, 
             po.code AS order_code,
             po.supplier_id,
             s.name as supplier_name,
             pr.status as request_status
      FROM payment_plans pp
      LEFT JOIN purchase_orders po ON pp.purchase_order_id = po.id
      LEFT JOIN suppliers s ON po.supplier_id = s.id
      LEFT JOIN payment_requests pr ON pp.payment_request_id = pr.id
    `;
    const params = [];
    const conditions = [];
    
    if (purchase_order_id) {
      params.push(purchase_order_id);
      conditions.push(`pp.purchase_order_id = $${params.length}`);
    }
    if (status) {
      params.push(status);
      conditions.push(`pp.status = $${params.length}`);
    }
    
    if (conditions.length > 0) {
      query += ' WHERE ' + conditions.join(' AND ');
    }
    
    query += ' ORDER BY pp.planned_date ASC, pp.id ASC';
    
    const result = await db.query(query, params);
    
    res.json({
      success: true,
      data: result.rows,
      count: result.rows.length
    });
  } catch (error) {
    console.error('获取付款计划列表失败:', error);
    res.status(500).json({
      success: false,
      message: '获取付款计划列表失败',
      error: process.env.NODE_ENV === 'development' ? error.message : '操作失败'
    });
  }
});

/**
 * 获取付款计划详情
 */
router.get('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    
    const result = await db.query(`
      SELECT pp.*, 
             po.code AS order_code,
             po.supplier_id,
             s.name as supplier_name,
             pr.status as request_status,
             pr.amount as request_amount
      FROM payment_plans pp
      LEFT JOIN purchase_orders po ON pp.purchase_order_id = po.id
      LEFT JOIN suppliers s ON po.supplier_id = s.id
      LEFT JOIN payment_requests pr ON pp.payment_request_id = pr.id
      WHERE pp.id = $1
    `, [id]);
    
    if (result.rows.length === 0) {
      return res.status(404).json({ success: false, message: '付款计划不存在' });
    }
    
    res.json({
      success: true,
      data: result.rows[0]
    });
  } catch (error) {
    console.error('获取付款计划详情失败:', error);
    res.status(500).json({
      success: false,
      message: '获取付款计划详情失败',
      error: process.env.NODE_ENV === 'development' ? error.message : '操作失败'
    });
  }
});

/**
 * 创建付款计划
 */
router.post('/', async (req, res) => {
  try {
    const { purchase_order_id, stage, planned_date, planned_amount, planned_percentage, remark } = req.body;
    
    const result = await db.query(`
      INSERT INTO payment_plans 
      (purchase_order_id, stage, planned_date, planned_amount, planned_percentage, remark, status, created_at, updated_at)
      VALUES ($1, $2, $3, $4, $5, $6, 'pending', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
       RETURNING id`,
      [purchase_order_id, stage, planned_date, planned_amount, planned_percentage, remark]);
    
    res.json({
      success: true,
      message: '付款计划创建成功',
      data: { id: (result.rows[0]?.id || result.rows?.[0]?.id) }
    });
  } catch (error) {
    console.error('创建付款计划失败:', error);
    res.status(500).json({
      success: false,
      message: '创建付款计划失败',
      error: process.env.NODE_ENV === 'development' ? error.message : '操作失败'
    });
  }
});

/**
 * 更新付款计划
 */
router.put('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { stage, planned_date, planned_amount, planned_percentage, remark } = req.body;
    
    const planResult = await db.query('SELECT * FROM payment_plans WHERE id = $1', [id]);
    if (planResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: '付款计划不存在' });
    }
    
    const plan = planResult.rows[0];
    if (plan.status !== 'pending') {
      return res.status(400).json({ success: false, message: '只能修改待付款状态的计划' });
    }
    
    const result = await db.query(`
      UPDATE payment_plans 
      SET stage = $1, planned_date = $2, planned_amount = $3, planned_percentage = $4, remark = $5, updated_at = CURRENT_TIMESTAMP
      WHERE id = $6
    `, [stage, planned_date, planned_amount, planned_percentage, remark, id]);
    
    res.json({
      success: true,
      message: '付款计划更新成功'
    });
  } catch (error) {
    console.error('更新付款计划失败:', error);
    res.status(500).json({
      success: false,
      message: '更新付款计划失败',
      error: process.env.NODE_ENV === 'development' ? error.message : '操作失败'
    });
  }
});

/**
 * 删除付款计划
 */
router.delete('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    
    const planResult = await db.query('SELECT * FROM payment_plans WHERE id = $1', [id]);
    if (planResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: '付款计划不存在' });
    }
    
    const plan = planResult.rows[0];
    if (plan.status !== 'pending') {
      return res.status(400).json({ success: false, message: '只能删除待付款状态的计划' });
    }
    
    await db.query('DELETE FROM payment_plans WHERE id = $1', [id]);
    
    res.json({ success: true, message: '付款计划删除成功' });
  } catch (error) {
    console.error('删除付款计划失败:', error);
    res.status(500).json({
      success: false,
      message: '删除付款计划失败',
      error: process.env.NODE_ENV === 'development' ? error.message : '操作失败'
    });
  }
});

/**
 * 创建付款申请
 * 遵循设计方案：付款计划与付款申请关联
 * 状态从 pending 变为 requested
 */
router.post('/:id/create-request', async (req, res) => {
  try {
    const { id } = req.params;
    
    const planResult = await db.query(`
      SELECT pp.*, po.supplier_id, po.currency, po.project_id
      FROM payment_plans pp
      LEFT JOIN purchase_orders po ON pp.purchase_order_id = po.id
      WHERE pp.id = $1
    `, [id]);
    
    if (planResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: '付款计划不存在' });
    }
    
    const plan = planResult.rows[0];
    
    if (plan.status !== 'pending') {
      return res.status(400).json({ success: false, message: '只能对待付款状态的计划创建付款申请' });
    }
    
    await db.query('BEGIN');
    
    try {
      const requestCode = 'PAY' + new Date().toISOString().slice(0, 10).replace(/-/g, '') + 
                          String(Math.floor(Math.random() * 10000)).padStart(4, '0');
      
      const requestResult = await db.query(`
        INSERT INTO payment_requests 
        (request_code, payment_type, purchase_order_id, amount, currency, applicant, payment_date, status, created_at)
        VALUES ($1, 'material', $2, $3, $4, '系统管理员', CURRENT_DATE, 'pending', CURRENT_TIMESTAMP)
       RETURNING id`,
      [requestCode, plan.purchase_order_id, plan.planned_amount, plan.currency || 'CNY']);
      
      const requestId = requestResult.rows[0].id;
      
      await db.query(`
        UPDATE payment_plans 
        SET status = 'requested', payment_request_id = $1, updated_at = CURRENT_TIMESTAMP
        WHERE id = $2
      `, [requestId, id]);
      
      await db.query('COMMIT');
      
      res.json({
        success: true,
        message: '付款申请创建成功',
        data: { request_id: requestId, request_code: requestCode }
      });
    } catch (innerError) {
      await db.query('ROLLBACK');
      throw innerError;
    }
  } catch (error) {
    console.error('创建付款申请失败:', error);
    res.status(500).json({
      success: false,
      message: '创建付款申请失败',
      error: process.env.NODE_ENV === 'development' ? error.message : '操作失败'
    });
  }
});

/**
 * 标记为已支付
 * 遵循设计方案：付款计划状态机
 * 状态从 requested/approved 变为 paid
 */
router.post('/:id/mark-paid', async (req, res) => {
  try {
    const { id } = req.params;
    const { actual_amount, actual_date, voucher_url } = req.body;
    
    const planResult = await db.query('SELECT * FROM payment_plans WHERE id = $1', [id]);
    if (planResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: '付款计划不存在' });
    }
    
    const plan = planResult.rows[0];
    
    if (!['requested', 'approved'].includes(plan.status)) {
      return res.status(400).json({ success: false, message: '只能对已申请或已批准的计划标记为已支付' });
    }
    
    await db.query('BEGIN');
    
    try {
      await db.query(`
        UPDATE payment_plans 
        SET status = 'paid', actual_amount = $1, actual_date = $2, updated_at = CURRENT_TIMESTAMP
        WHERE id = $3
      `, [actual_amount || plan.planned_amount, actual_date || new Date().toISOString().slice(0, 10), id]);
      
      if (plan.payment_request_id) {
        await db.query(`
          UPDATE payment_requests 
          SET status = 'paid', updated_at = CURRENT_TIMESTAMP
          WHERE id = $1
        `, [plan.payment_request_id]);
      }
      
      const orderResult = await db.query(`
        SELECT SUM(CASE WHEN status = 'paid' THEN actual_amount ELSE 0 END) as paid_amount,
               SUM(planned_amount) as total_amount
        FROM payment_plans 
        WHERE purchase_order_id = $1
      `, [plan.purchase_order_id]);
      
      const { paid_amount, total_amount } = orderResult.rows[0];
      
      let newOrderStatus = 'confirmed';
      if (paid_amount >= total_amount) {
        newOrderStatus = 'paid';
      } else if (paid_amount > 0) {
        newOrderStatus = 'partial_paid';
      }
      
      await db.query(`
        UPDATE purchase_orders 
        SET paid_amount = $1, status = $2, updated_at = CURRENT_TIMESTAMP
        WHERE id = $3
      `, [paid_amount, newOrderStatus, plan.purchase_order_id]);
      
      await db.query('COMMIT');
      
      res.json({
        success: true,
        message: '付款标记成功',
        data: { order_status: newOrderStatus, paid_amount }
      });
    } catch (innerError) {
      await db.query('ROLLBACK');
      throw innerError;
    }
  } catch (error) {
    console.error('标记付款失败:', error);
    res.status(500).json({
      success: false,
      message: '标记付款失败',
      error: process.env.NODE_ENV === 'development' ? error.message : '操作失败'
    });
  }
});

/**
 * 获取待提醒的付款计划
 * 遵循设计方案：提前N天提醒
 */
router.get('/reminders/upcoming', async (req, res) => {
  try {
    const days = parseInt(req.query.days) || 3;
    
    const result = await db.query(`
      SELECT pp.*, 
             po.code AS order_code,
             s.name as supplier_name
      FROM payment_plans pp
      LEFT JOIN purchase_orders po ON pp.purchase_order_id = po.id
      LEFT JOIN suppliers s ON po.supplier_id = s.id
      WHERE pp.status = 'pending'
        AND pp.planned_date <= CURRENT_DATE + ($1 || ' days')::interval
        AND pp.planned_date >= CURRENT_DATE
      ORDER BY pp.planned_date ASC
    `, [days]);
    
    res.json({
      success: true,
      data: result.rows,
      count: result.rows.length
    });
  } catch (error) {
    console.error('获取待提醒付款计划失败:', error);
    res.status(500).json({
      success: false,
      message: '获取待提醒付款计划失败',
      error: process.env.NODE_ENV === 'development' ? error.message : '操作失败'
    });
  }
});

module.exports = router;