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

const router = express.Router();

function generateCode(prefix) {
  const now = new Date();
  const dateStr = now.getFullYear().toString() +
    (now.getMonth() + 1).toString().padStart(2, '0') +
    now.getDate().toString().padStart(2, '0');
  const rand = Math.floor(Math.random() * 10000).toString().padStart(4, '0');
  return `${prefix}-${dateStr}-${rand}`;
}

router.get('/summary', authenticate, async (req, res) => {
  try {
    const { date_from, date_to } = req.query;
    let where = "WHERE status != 'voided'";
    const params = [];
    let idx = 1;

    if (date_from) { params.push(date_from); where += ` AND record_date >= $${idx++}`; }
    if (date_to) { params.push(date_to); where += ` AND record_date <= $${idx++}`; }

    const totalResult = await db.query(
      `SELECT
        COALESCE(SUM(CASE WHEN txn_type = 'income' THEN amount_cny ELSE 0 END), 0) as total_income,
        COALESCE(SUM(CASE WHEN txn_type = 'expense' THEN amount_cny ELSE 0 END), 0) as total_expense,
        COALESCE(SUM(CASE WHEN txn_type = 'income' THEN amount_cny ELSE -amount_cny END), 0) as net_amount
       FROM financial_records ${where}`,
      params
    );

    const byCategory = await db.query(
      `SELECT category_level1, category_level2,
        COALESCE(SUM(amount_cny), 0) as total_amount,
        COUNT(*) as count
       FROM financial_records ${where}
       GROUP BY category_level1, category_level2
       ORDER BY category_level1, total_amount DESC`,
      params
    );

    const recentResult = await db.query(
      `SELECT fr.*, p.name as project_name
       FROM financial_records fr
       LEFT JOIN projects p ON fr.project_id = p.id
       ${where}
       ORDER BY fr.record_date DESC, fr.created_at DESC
       LIMIT 20`,
      params
    );

    res.json({
      success: true,
      data: {
        totals: totalResult.rows[0],
        byCategory: byCategory.rows,
        recent: recentResult.rows
      }
    });
  } catch (error) {
    console.error('获取资金概览失败:', error);
    res.status(500).json({ success: false, message: '获取资金概览失败' });
  }
});

router.get('/records', authenticate, async (req, res) => {
  try {
    const {
      txn_type, category_level1, category_level2,
      project_id, date_from, date_to,
      page = 1, pageSize = 20
    } = req.query;

    let sql = `SELECT fr.*, p.name as project_name FROM financial_records fr LEFT JOIN projects p ON fr.project_id = p.id WHERE 1=1`;
    const params = [];
    let idx = 1;

    if (txn_type) { params.push(txn_type); sql += ` AND fr.txn_type = $${idx++}`; }
    if (category_level1) { params.push(category_level1); sql += ` AND fr.category_level1 = $${idx++}`; }
    if (category_level2) { params.push(category_level2); sql += ` AND fr.category_level2 = $${idx++}`; }
    if (project_id) { params.push(project_id); sql += ` AND fr.project_id = $${idx++}`; }
    if (date_from) { params.push(date_from); sql += ` AND fr.record_date >= $${idx++}`; }
    if (date_to) { params.push(date_to); sql += ` AND fr.record_date <= $${idx++}`; }

    const countResult = await db.query(`SELECT COUNT(*) as total FROM (${sql}) sub`, params);
    const total = parseInt(countResult.rows[0].total);

    sql += ' ORDER BY fr.record_date DESC, fr.created_at DESC';
    const offset = (parseInt(page) - 1) * parseInt(pageSize);
    params.push(parseInt(pageSize));
    sql += ` LIMIT $${idx++}`;
    params.push(offset);
    sql += ` OFFSET $${idx++}`;

    const result = await db.query(sql, params);
    res.json({
      success: true,
      data: result.rows,
      pagination: {
        page: parseInt(page),
        pageSize: parseInt(pageSize),
        total,
        totalPages: Math.ceil(total / parseInt(pageSize))
      }
    });
  } catch (error) {
    console.error('查询收支记录失败:', error);
    res.status(500).json({ success: false, message: '查询收支记录失败' });
  }
});

router.post('/', authenticate, async (req, res) => {
  const client = await db.pool.connect();
  try {
    await client.query('BEGIN');
    const {
      txn_type, category_level1, category_level2,
      project_id, amount, currency, exchange_rate,
      record_date, counterparty_name, counterparty_type, counterparty_id,
      description, voucher_url
    } = req.body;

    const userId = req.user?.id || req.user?.userId;

    if (!txn_type || !category_level1 || !category_level2 || !amount || !record_date) {
      await client.query('ROLLBACK');
      return res.status(400).json({ success: false, message: '缺少必填字段' });
    }

    const amt = parseFloat(amount) || 0;
    const rate = parseFloat(exchange_rate) || 1;
    const amountCny = parseFloat((amt * rate).toFixed(2));

    const today = new Date();
    const dateStr = today.toISOString().slice(0, 10).replace(/-/g, '');
    const codeResult = await client.query(
      "SELECT COUNT(*) as cnt FROM financial_records WHERE record_code LIKE $1",
      [`FIN-${dateStr}%`]
    );
    const seq = String(parseInt(codeResult.rows[0].cnt) + 1).padStart(4, '0');
    const recordCode = `FIN-${dateStr}-${seq}`;

    const frResult = await client.query(
      `INSERT INTO financial_records
        (record_code, txn_type, category_level1, category_level2, project_id, user_id, user_name,
         amount_original, currency, exchange_rate, amount_cny, record_date,
         counterparty_name, counterparty_type, counterparty_id, source, source_code, description, attachments, status)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, 'cash_management', $16, $17, $18, 'confirmed')
       RETURNING *`,
      [
        recordCode, txn_type, category_level1, category_level2,
        project_id || null, userId, req.user?.name || req.user?.username || '',
        amt, currency || 'CNY', rate, amountCny,
        record_date,
        counterparty_name || null, counterparty_type || null, counterparty_id || null,
        recordCode, description || null, voucher_url || null
      ]
    );

    if (txn_type === 'income' && project_id && ['contract_payment', 'customer_advance'].includes(category_level2)) {
      const msResult = await client.query(
        'SELECT id FROM project_milestones WHERE project_id = $1 ORDER BY id LIMIT 1',
        [project_id]
      );
      if (msResult.rows.length > 0) {
        const milestoneId = msResult.rows[0].id;
        const msData = await client.query('SELECT amount FROM project_milestones WHERE id = $1', [milestoneId]);
        if (msData.rows.length > 0 && parseFloat(msData.rows[0].amount) > 0) {
          const prResult = await client.query(
            `INSERT INTO project_receipts
              (project_id, receipt_type, milestone_id, amount, currency, exchange_rate, amount_cny,
               receipt_date, payer_name, description, voucher_url, financial_record_id, created_by)
             VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
             RETURNING *`,
            [
              project_id,
              category_level2 === 'customer_advance' ? 'advance' : 'node',
              milestoneId, amt, currency || 'CNY', rate, amountCny,
              record_date, counterparty_name || '', description || '', voucher_url || '',
              frResult.rows[0].id, userId
            ]
          );

          const totalReceived = await client.query(
            `SELECT COALESCE(SUM(amount), 0) as total FROM project_receipts
             WHERE milestone_id = $1 AND receipt_type = 'node'`,
            [milestoneId]
          );
          const progress = Math.min(100, Math.round((parseFloat(totalReceived.rows[0].total) / parseFloat(msData.rows[0].amount)) * 100));
          await client.query(
            `UPDATE project_milestones SET completion_progress = $1::numeric,
             status = CASE WHEN $1::numeric >= 100 THEN 'completed' ELSE 'in_progress' END,
             actual_date = CASE WHEN $1::numeric >= 100 THEN $2 ELSE actual_date END
             WHERE id = $3`,
            [progress, record_date, milestoneId]
          );
        }
      }
    }

    await client.query('COMMIT');

    const fullResult = await db.query(
      `SELECT fr.*, p.name as project_name FROM financial_records fr LEFT JOIN projects p ON fr.project_id = p.id WHERE fr.id = $1`,
      [frResult.rows[0].id]
    );

    res.json({ success: true, data: fullResult.rows[0] });
  } catch (error) {
    await client.query('ROLLBACK');
    console.error('新增收支记录失败:', error);
    res.status(500).json({ success: false, message: '新增收支记录失败' });
  } finally {
    client.release();
  }
});

router.delete('/:id', authenticate, async (req, res) => {
  const client = await db.pool.connect();
  try {
    await client.query('BEGIN');
    const { id } = req.params;

    const recordResult = await client.query(
      `SELECT * FROM financial_records WHERE id = $1 AND source = 'cash_management'`,
      [id]
    );
    if (recordResult.rowCount === 0) {
      await client.query('ROLLBACK');
      return res.status(404).json({ success: false, message: '记录不存在或无权删除' });
    }

    const record = recordResult.rows[0];

    if (record.txn_type === 'income' && record.project_id) {
      const prResult = await client.query(
        'SELECT id FROM project_receipts WHERE financial_record_id = $1',
        [id]
      );
      if (prResult.rows.length > 0) {
        const receipt = prResult.rows[0];
        await client.query('DELETE FROM project_receipts WHERE id = $1', [receipt.id]);

        if (receipt.milestone_id) {
          const msData = await client.query('SELECT amount FROM project_milestones WHERE id = $1', [receipt.milestone_id]);
          if (msData.rows.length > 0 && parseFloat(msData.rows[0].amount) > 0) {
            const totalReceived = await client.query(
              `SELECT COALESCE(SUM(amount), 0) as total FROM project_receipts
               WHERE milestone_id = $1 AND receipt_type = 'node'`,
              [receipt.milestone_id]
            );
            const progress = Math.min(100, Math.round((parseFloat(totalReceived.rows[0].total) / parseFloat(msData.rows[0].amount)) * 100));
            await client.query(
              `UPDATE project_milestones SET completion_progress = $1::numeric,
               status = CASE WHEN $1::numeric >= 100 THEN 'completed' WHEN $1::numeric > 0 THEN 'in_progress' ELSE 'pending' END
               WHERE id = $2`,
              [progress, receipt.milestone_id]
            );
          }
        }
      }
    }

    await client.query('DELETE FROM financial_records WHERE id = $1', [id]);

    await client.query('COMMIT');
    res.json({ success: true, message: '删除成功' });
  } catch (error) {
    await client.query('ROLLBACK');
    console.error('删除收支记录失败:', error);
    res.status(500).json({ success: false, message: '删除收支记录失败' });
  } finally {
    client.release();
  }
});

module.exports = router;