const express = require('express');
const db = require('../db');
const { authenticate, requireAdmin } = require('../middleware/auth');

const router = express.Router();

router.get('/', async (req, res) => {
  try {
    const { advance_id } = req.query;
    let query = `
      SELECT v.*, a.advance_code, a.applicant_id as advance_applicant_id
      FROM verifications v
      LEFT JOIN advances a ON v.advance_id = a.id
    `;
    const params = [];
    
    if (advance_id) {
      query += ` WHERE v.advance_id = $1`;
      params.push(advance_id);
    }
    
    query += ` ORDER BY v.created_at DESC`;
    
    const result = await db.query(query, params);
    
    const data = result.rows.map(item => {
      if (item.attachments) {
        try {
          item.attachments = JSON.parse(item.attachments);
        } catch (error) {
          item.attachments = [];
        }
      } else {
        item.attachments = [];
      }
      if (item.detail_items) {
        try {
          item.detail_items = JSON.parse(item.detail_items);
        } catch (error) {
          item.detail_items = [];
        }
      } else {
        item.detail_items = [];
      }
      return item;
    });
    
    res.json({ success: true, data, count: data.length });
  } catch (error) {
    console.error('获取核销记录失败:', error);
    res.status(500).json({ success: false, message: '获取核销记录失败' });
  }
});

router.post('/', async (req, res) => {
  try {
    const { verification_date, advance_id, advance_code, advance_amount, currency, reason, detail_items, attachments, applicant, expense_type, project_id, settlement, settlement_amount } = req.body;
    
    // 生成核销编号
    const verificationCode = `VER-${Date.now()}`;
    const amount = detail_items?.reduce((sum, item) => sum + (item.amount || 0), 0) || 0;
    
    // 验证关联预支单
    if (!advance_id && !advance_code) {
      return res.status(400).json({ success: false, message: '关联预支单是必填项' });
    }
    
    let finalAdvanceCode = advance_code;
    let finalAdvanceId = advance_id;
    
    // 如果advance_code为空，根据advance_id查询预支单的advance_code
    if (!finalAdvanceCode && finalAdvanceId) {
      const advanceResult = await db.query('SELECT advance_code FROM advances WHERE id = $1', [finalAdvanceId]);
      if (advanceResult.rows.length > 0) {
        finalAdvanceCode = advanceResult.rows[0].advance_code;
      } else {
        return res.status(400).json({ success: false, message: '关联的预支单不存在' });
      }
    }
    
    // 如果advance_id为空，根据advance_code查询预支单的id
    if (!finalAdvanceId && finalAdvanceCode) {
      const advanceResult = await db.query('SELECT id FROM advances WHERE advance_code = $1', [finalAdvanceCode]);
      if (advanceResult.rows.length > 0) {
        finalAdvanceId = advanceResult.rows[0].id;
      } else {
        return res.status(400).json({ success: false, message: '关联的预支单不存在' });
      }
    }
    
    // 如果仍然为空，返回错误
    if (!finalAdvanceCode || !finalAdvanceId) {
      return res.status(400).json({ success: false, message: '关联预支单不存在' });
    }
    
    // 开始事务
    await db.query('BEGIN TRANSACTION');
    
    try {
      // 插入核销申请
      const result = await db.query(
        'INSERT INTO verifications (advance_id, amount, currency, reason, verification_date, verification_code, status, applicant, advance_code, advance_amount, detail_items, attachments, expense_type, project_id, settlement, settlement_amount) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16)',
        [finalAdvanceId, amount, currency || 'CNY', reason, verification_date, verificationCode, 'pending', applicant, finalAdvanceCode, advance_amount || 0, JSON.stringify(detail_items || []), JSON.stringify(attachments || []), expense_type || 'company', project_id, settlement ? 1 : 0, settlement_amount || 0]
      );
      
      // 提交事务
      await db.query('COMMIT');
      
      // SQLite不支持RETURNING，所以需要查询刚插入的数据
      const lastInsert = await db.query('SELECT * FROM verifications ORDER BY id DESC LIMIT 1');
      res.json({ success: true, data: lastInsert.rows[0] });
    } catch (error) {
      // 回滚事务
      await db.query('ROLLBACK');
      throw error;
    }
  } catch (error) {
    console.error('创建核销申请失败:', error);
    res.status(500).json({ success: false, message: '创建核销申请失败' });
  }
});

router.get('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    
    const result = await db.query('SELECT * FROM verifications WHERE id = $1', [id]);
    
    if (result.rows.length > 0) {
      const data = result.rows[0];
      if (data.attachments) {
        try {
          data.attachments = JSON.parse(data.attachments);
        } catch (error) {
          data.attachments = [];
        }
      } else {
        data.attachments = [];
      }
      if (data.detail_items) {
        try {
          data.detail_items = JSON.parse(data.detail_items);
        } catch (error) {
          data.detail_items = [];
        }
      } else {
        data.detail_items = [];
      }
      res.json({ success: true, data });
    } else {
      res.status(404).json({ success: false, message: '核销申请不存在' });
    }
  } catch (error) {
    console.error('获取核销申请失败:', error);
    res.status(500).json({ success: false, message: '获取核销申请失败' });
  }
});

router.put('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { verification_date, advance_id, advance_code, advance_amount, currency, reason, detail_items, attachments, applicant, status, expense_type, project_id, settlement, settlement_amount } = req.body;
    const amount = detail_items?.reduce((sum, item) => sum + (item.amount || 0), 0) || 0;
    
    // 开始事务
    await db.query('BEGIN TRANSACTION');
    
    try {
      // 获取原核销金额
      const oldVerification = await db.query('SELECT amount, advance_id FROM verifications WHERE id = $1', [id]);
      const oldAmount = oldVerification.rows[0]?.amount || 0;
      const oldAdvanceId = oldVerification.rows[0]?.advance_id;
      
      let finalAdvanceCode = advance_code;
      
      // 如果advance_code为空，根据advance_id查询预支单的advance_code
      if (!finalAdvanceCode && advance_id) {
        const advanceResult = await db.query('SELECT advance_code FROM advances WHERE id = $1', [advance_id]);
        if (advanceResult.rows.length > 0) {
          finalAdvanceCode = advanceResult.rows[0].advance_code;
        }
      }
      
      // 如果仍然为空，使用默认值
      if (!finalAdvanceCode) {
        finalAdvanceCode = 'UNKNOWN';
      }
      
      // 更新核销申请
      const result = await db.query(
        'UPDATE verifications SET verification_date = $1, advance_id = $2, amount = $3, currency = $4, reason = $5, advance_code = $6, advance_amount = $7, detail_items = $8, attachments = $9, applicant = $10, status = $11, expense_type = $12, project_id = $13, settlement = $14, settlement_amount = $15 WHERE id = $16',
        [verification_date, advance_id, amount, currency, reason, finalAdvanceCode, advance_amount || 0, JSON.stringify(detail_items || []), JSON.stringify(attachments || []), applicant, status, expense_type || 'company', project_id, settlement ? 1 : 0, settlement_amount || 0, id]
      );
      
      // 不在这里更新预支单已核销金额，而是在执行核销时更新
      // if (oldAdvanceId) {
      //   const amountDiff = amount - oldAmount;
      //   if (amountDiff !== 0) {
      //     await db.query(
      //       'UPDATE advances SET total_reimbursed = total_reimbursed + $1 WHERE id = $2',
      //       [amountDiff, oldAdvanceId]
      //     );
      //   }
      // }
      
      // 提交事务
      await db.query('COMMIT');
      
      if (result.rowCount > 0) {
        res.json({ success: true, message: '更新成功' });
      } else {
        res.status(404).json({ success: false, message: '核销申请不存在' });
      }
    } catch (error) {
      // 回滚事务
      await db.query('ROLLBACK');
      throw error;
    }
  } catch (error) {
    console.error('更新核销申请失败:', error);
    res.status(500).json({ success: false, message: '更新核销申请失败' });
  }
});

router.delete('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    
    // 开始事务
    await db.query('BEGIN TRANSACTION');
    
    try {
      // 获取核销金额和预支单ID
      const verification = await db.query('SELECT amount, advance_id FROM verifications WHERE id = $1', [id]);
      const amount = verification.rows[0]?.amount || 0;
      const advanceId = verification.rows[0]?.advance_id;
      
      // 删除核销申请
      const result = await db.query('DELETE FROM verifications WHERE id = $1', [id]);
      
      // 不在这里恢复预支单已核销金额，因为只有执行的核销才会增加已核销金额
      // if (advanceId && amount > 0) {
      //   await db.query(
      //     'UPDATE advances SET total_reimbursed = total_reimbursed - $1 WHERE id = $2',
      //     [amount, advanceId]
      //   );
      // }
      
      // 提交事务
      await db.query('COMMIT');
      
      if (result.rowCount > 0) {
        res.json({ success: true, message: '删除成功' });
      } else {
        res.status(404).json({ success: false, message: '核销申请不存在' });
      }
    } catch (error) {
      // 回滚事务
      await db.query('ROLLBACK');
      throw error;
    }
  } catch (error) {
    console.error('删除核销申请失败:', error);
    res.status(500).json({ success: false, message: '删除核销申请失败' });
  }
});

router.post('/:id/submit', async (req, res) => {
  try {
    const { id } = req.params;
    
    const result = await db.query('UPDATE verifications SET status = $1 WHERE id = $2', ['pending', id]);
    
    if (result.rowCount > 0) {
      res.json({ success: true, message: '提交成功' });
    } else {
      res.status(404).json({ success: false, message: '核销申请不存在' });
    }
  } catch (error) {
    console.error('提交核销申请失败:', error);
    res.status(500).json({ success: false, message: '提交核销申请失败' });
  }
});

router.post('/:id/withdraw', async (req, res) => {
  try {
    const { id } = req.params;
    
    const result = await db.query('UPDATE verifications SET status = $1 WHERE id = $2', ['withdrawn', id]);
    
    if (result.rowCount > 0) {
      res.json({ success: true, message: '撤回成功' });
    } else {
      res.status(404).json({ success: false, message: '核销申请不存在' });
    }
  } catch (error) {
    console.error('撤回核销申请失败:', error);
    res.status(500).json({ success: false, message: '撤回核销申请失败' });
  }
});

router.post('/:id/approve', async (req, res) => {
  try {
    const { id } = req.params;
    const { remark } = req.body;
    
    const result = await db.query('UPDATE verifications SET status = $1, approval_remark = $2 WHERE id = $3', ['approved', remark, id]);
    
    if (result.rowCount > 0) {
      res.json({ success: true, message: '审批通过成功' });
    } else {
      res.status(404).json({ success: false, message: '核销申请不存在' });
    }
  } catch (error) {
    console.error('审批核销申请失败:', error);
    res.status(500).json({ success: false, message: '审批核销申请失败' });
  }
});

router.post('/:id/reject', async (req, res) => {
  try {
    const { id } = req.params;
    const { rejectReason } = req.body;
    
    // 开始事务
    await db.query('BEGIN TRANSACTION');
    
    try {
      // 获取核销金额和预支单ID
      const verification = await db.query('SELECT amount, advance_id FROM verifications WHERE id = $1', [id]);
      const amount = verification.rows[0]?.amount || 0;
      const advanceId = verification.rows[0]?.advance_id;
      
      // 退回核销申请
      const result = await db.query('UPDATE verifications SET status = $1 WHERE id = $2', ['pending', id]);
      
      // 不在这里恢复预支单已核销金额，因为只有执行的核销才会增加已核销金额
      // if (advanceId && amount > 0) {
      //   await db.query(
      //     'UPDATE advances SET total_reimbursed = total_reimbursed - $1 WHERE id = $2',
      //     [amount, advanceId]
      //   );
      // }
      
      // 提交事务
      await db.query('COMMIT');
      
      if (result.rowCount > 0) {
        res.json({ success: true, message: '退回成功' });
      } else {
        res.status(404).json({ success: false, message: '核销申请不存在' });
      }
    } catch (error) {
      // 回滚事务
      await db.query('ROLLBACK');
      throw error;
    }
  } catch (error) {
    console.error('退回核销申请失败:', error);
    res.status(500).json({ success: false, message: '退回核销申请失败' });
  }
});


module.exports = router;