yunhaifinance/backend/routes/budget.js

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

const router = express.Router();

router.get('/', authenticate, async (req, res) => {
  try {
    const { customer_id, status } = req.query;
    let query = `
      SELECT b.*,
             c.name as customer_name,
             u.name as manager_name
      FROM budget_projects b
      LEFT JOIN customers c ON b.customer_id = c.id
      LEFT JOIN users u ON b.business_manager_id = u.id
    `;

    const params = [];
    const conditions = [];
    if (customer_id) {
      params.push(customer_id);
      conditions.push(`b.customer_id = $${params.length}`);
    }
    if (status) {
      params.push(status);
      conditions.push(`b.status = $${params.length}`);
    }
    if (conditions.length > 0) {
      query += ' WHERE ' + conditions.join(' AND ');
    }

    query += ' ORDER BY b.created_at DESC';

    const result = await db.query(query, params);

    const projects = await Promise.all(result.rows.map(async (project) => {
      let attachments = [];
      let survey_photos = [];

      try {
        const attResult = await db.query(
          "SELECT * FROM budget_attachments WHERE budget_project_id = $1 AND file_type = 'attachment'",
          [project.id]
        );
        attachments = attResult.rows.map(a => a.file_url);
      } catch (e) {}

      try {
        const photoResult = await db.query(
          "SELECT * FROM budget_attachments WHERE budget_project_id = $1 AND file_type = 'survey_photo'",
          [project.id]
        );
        survey_photos = photoResult.rows.map(a => a.file_url);
      } catch (e) {}

      let quotations = [];
      try {
        const qResult = await db.query(
          'SELECT * FROM budget_quotations WHERE budget_project_id = $1 ORDER BY version DESC',
          [project.id]
        );
        quotations = qResult.rows;
      } catch (e) {}

      const { survey_notes, intermediary_name, intermediary_fee, business_manager_id, created_by, ...rest } = project;

      return {
        ...rest,
        project_overview: survey_notes || rest.project_overview,
        intermediary: intermediary_name,
        intermediary_fee_value: intermediary_fee,
        manager_id: business_manager_id,
        attachments,
        survey_photos,
        quotations
      };
    }));

    res.json({ success: true, data: projects, count: projects.length });
  } catch (error) {
    console.error('获取预算项目失败:', error);
    res.status(500).json({ success: false, message: '获取预算项目失败' });
  }
});

router.get('/:id', authenticate, async (req, res) => {
  try {
    const { id } = req.params;
    const result = await db.query(
      `SELECT b.*, c.name as customer_name, u.name as manager_name
       FROM budget_projects b
       LEFT JOIN customers c ON b.customer_id = c.id
       LEFT JOIN users u ON b.business_manager_id = u.id
       WHERE b.id = $1`,
      [id]
    );

    if (result.rows.length === 0) {
      return res.status(404).json({ success: false, message: '项目不存在' });
    }

    const project = result.rows[0];

    let attachments = [];
    let survey_photos = [];
    try {
      const attResult = await db.query(
        "SELECT * FROM budget_attachments WHERE budget_project_id = $1 AND file_type = 'attachment'",
        [id]
      );
      attachments = attResult.rows.map(a => a.file_url);
    } catch (e) {}

    try {
      const photoResult = await db.query(
        "SELECT * FROM budget_attachments WHERE budget_project_id = $1 AND file_type = 'survey_photo'",
        [id]
      );
      survey_photos = photoResult.rows.map(a => a.file_url);
    } catch (e) {}

    let quotations = [];
    try {
      const qResult = await db.query(
        'SELECT * FROM budget_quotations WHERE budget_project_id = $1 ORDER BY version DESC',
        [id]
      );
      quotations = qResult.rows;
    } catch (e) {}

    const { survey_notes, intermediary_name, intermediary_fee, business_manager_id, created_by, ...rest } = project;

    res.json({
      success: true,
      data: {
        ...rest,
        project_overview: survey_notes || rest.project_overview,
        intermediary: intermediary_name,
        intermediary_fee_value: intermediary_fee,
        manager_id: business_manager_id,
        attachments,
        survey_photos,
        quotations
      }
    });
  } catch (error) {
    console.error('获取预算项目详情失败:', error);
    res.status(500).json({ success: false, message: '获取项目详情失败' });
  }
});

router.post('/', authenticate, async (req, res) => {
  try {
    const {
      name, customer_id, manager_id, location, survey_date,
      intermediary, intermediary_fee_type, intermediary_fee_value,
      customer_requirements, project_overview,
      attachments, survey_photos, status
    } = req.body;

    if (!name) {
      return res.status(400).json({ success: false, message: '项目名称不能为空' });
    }

    const codeResult = await db.query(
      "SELECT COUNT(*) as cnt FROM budget_projects WHERE budget_code LIKE 'BJ%'"
    );
    const codeNum = parseInt(codeResult.rows[0].cnt) + 1;
    const budget_code = 'BJ' + String(codeNum).padStart(4, '0');

    const result = await db.query(
      `INSERT INTO budget_projects
        (budget_code, name, customer_id, business_manager_id, location, survey_date,
         intermediary_name, intermediary_fee_type, intermediary_fee,
         customer_requirements, survey_notes, status, created_by)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13)
       RETURNING *`,
      [
        budget_code,
        name,
        customer_id || null,
        manager_id || null,
        location || null,
        survey_date || null,
        intermediary || null,
        intermediary_fee_type || 'fixed',
        intermediary_fee_value || 0,
        customer_requirements || null,
        project_overview || null,
        status || 'negotiating',
        req.user?.id || null
      ]
    );

    const newProject = result.rows[0];

    if (attachments && Array.isArray(attachments)) {
      for (const url of attachments) {
        await db.query(
          'INSERT INTO budget_attachments (budget_project_id, file_url, file_type) VALUES ($1, $2, $3)',
          [newProject.id, url, 'attachment']
        );
      }
    }

    if (survey_photos && Array.isArray(survey_photos)) {
      for (const url of survey_photos) {
        await db.query(
          'INSERT INTO budget_attachments (budget_project_id, file_url, file_type) VALUES ($1, $2, $3)',
          [newProject.id, url, 'survey_photo']
        );
      }
    }

    const custResult = await db.query('SELECT name FROM customers WHERE id = $1', [customer_id]);
    const mgrResult = await db.query('SELECT name FROM users WHERE id = $1', [manager_id]);

    const { survey_notes, intermediary_name, intermediary_fee, business_manager_id, created_by, ...rest } = newProject;

    res.json({
      success: true,
      data: {
        ...rest,
        project_overview: survey_notes || rest.project_overview,
        intermediary: intermediary_name,
        intermediary_fee_value: intermediary_fee,
        manager_id: business_manager_id,
        customer_name: custResult.rows[0]?.name || '',
        manager_name: mgrResult.rows[0]?.name || '',
        attachments: attachments || [],
        survey_photos: survey_photos || [],
        quotations: []
      }
    });
  } catch (error) {
    console.error('创建预算项目失败:', error);
    res.status(500).json({ success: false, message: '创建预算项目失败: ' + error.message });
  }
});

router.put('/:id/sign', authenticate, async (req, res) => {
  try {
    const { id } = req.params;
    const { contract_code, contract_amount, currency, construction_method, duration_days } = req.body;

    const projectResult = await db.query('SELECT * FROM budget_projects WHERE id = $1', [id]);
    if (projectResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: '项目不存在' });
    }

    await db.query(
      "UPDATE budget_projects SET status = 'signed', updated_at = NOW() WHERE id = $1",
      [id]
    );

    let project_id = projectResult.rows[0].project_id;
    const { type_template_id } = req.body;
    if (!project_id) {
      const projResult = await db.query(
        `INSERT INTO projects (name, customer_id, project_manager_id, status, created_at, updated_at)
         SELECT name, customer_id, business_manager_id, 'active', NOW(), NOW()
         FROM budget_projects WHERE id = $1
         RETURNING id`,
        [id]
      );
      project_id = projResult.rows[0].id;
      await db.query('UPDATE budget_projects SET project_id = $1 WHERE id = $2', [project_id, id]);
      if (type_template_id) {
        const tplResult = await db.query('SELECT phases FROM project_type_templates WHERE id = $1', [type_template_id]);
        if (tplResult.rows.length > 0) {
          const phases = tplResult.rows[0].phases;
          if (Array.isArray(phases)) {
            for (const phase of phases) {
              const subItems = (phase.sub_items || []).map(item => ({ name: item, completed: false }));
              await db.query(
                `INSERT INTO project_phases (project_id, template_key, phase_name, phase_order, phase_type, depends_on, sub_items, status) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`,
                [project_id, phase.key || `phase_${phase.order}`, phase.name, phase.order, phase.type || 'serial', phase.depends || [], JSON.stringify(subItems), phase.order === 1 ? 'in_progress' : 'pending']
              );
            }
            const firstPhase = phases.find(p => p.order === 1);
            await db.query('UPDATE projects SET type_template_id = $1, current_phase = $2 WHERE id = $3', [type_template_id, firstPhase ? firstPhase.name : '', project_id]);
          }
        }
      }
    }

    res.json({
      success: true,
      data: { project_id },
      message: '签约成功'
    });
  } catch (error) {
    console.error('签约失败:', error);
    res.status(500).json({ success: false, message: '签约失败: ' + error.message });
  }
});

router.put('/:id/unsigned', authenticate, async (req, res) => {
  try {
    const { id } = req.params;
    const { reason } = req.body;

    const projectResult = await db.query('SELECT * FROM budget_projects WHERE id = $1', [id]);
    if (projectResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: '项目不存在' });
    }

    await db.query(
      "UPDATE budget_projects SET status = 'unsigned', unsigned_reason = $1, updated_at = NOW() WHERE id = $2",
      [reason || '', id]
    );

    res.json({ success: true, message: '已标记为未签约' });
  } catch (error) {
    console.error('标记未签约失败:', error);
    res.status(500).json({ success: false, message: '操作失败' });
  }
});

router.delete('/:id', authenticate, async (req, res) => {
  try {
    const { id } = req.params;

    const projectResult = await db.query('SELECT * FROM budget_projects WHERE id = $1', [id]);
    if (projectResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: '项目不存在' });
    }

    await db.query('DELETE FROM budget_attachments WHERE budget_project_id = $1', [id]);
    await db.query('DELETE FROM budget_quotations WHERE budget_project_id = $1', [id]);
    await db.query('DELETE FROM negotiation_reminders WHERE budget_project_id = $1', [id]);
    await db.query('DELETE FROM budget_projects WHERE id = $1', [id]);

    res.json({ success: true, message: '删除成功' });
  } catch (error) {
    console.error('删除预算项目失败:', error);
    res.status(500).json({ success: false, message: '删除失败' });
  }
});

router.post('/:id/quotations', authenticate, async (req, res) => {
  try {
    const { id } = req.params;
    const { version, quotation_date, amount, currency, file_url, notes, status } = req.body;

    const projectResult = await db.query('SELECT * FROM budget_projects WHERE id = $1', [id]);
    if (projectResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: '项目不存在' });
    }

    let nextVersion = version;
    if (!nextVersion) {
      const vResult = await db.query(
        'SELECT COALESCE(MAX(version), 0) + 1 as next_ver FROM budget_quotations WHERE budget_project_id = $1',
        [id]
      );
      nextVersion = vResult.rows[0].next_ver;
    }

    const result = await db.query(
      `INSERT INTO budget_quotations (budget_project_id, version, quotation_date, amount, currency, file_url, notes, status)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING *`,
      [id, nextVersion, quotation_date || null, amount || 0, currency || 'CNY', file_url || null, notes || null, status || 'draft']
    );

    await db.query('UPDATE budget_projects SET last_quotation_at = NOW(), updated_at = NOW() WHERE id = $1', [id]);

    res.json({ success: true, data: result.rows[0] });
  } catch (error) {
    console.error('创建报价版本失败:', error);
    res.status(500).json({ success: false, message: '创建报价版本失败: ' + error.message });
  }
});

router.delete('/:id/quotations/:quotationId', authenticate, async (req, res) => {
  try {
    const { id, quotationId } = req.params;

    const qResult = await db.query('SELECT * FROM budget_quotations WHERE id = $1 AND budget_project_id = $2', [quotationId, id]);
    if (qResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: '报价版本不存在' });
    }

    await db.query('DELETE FROM budget_quotations WHERE id = $1', [quotationId]);

    res.json({ success: true, message: '删除报价版本成功' });
  } catch (error) {
    console.error('删除报价版本失败:', error);
    res.status(500).json({ success: false, message: '删除报价版本失败' });
  }
});

module.exports = router;