const express = require('express');
const { body, validationResult } = require('express-validator');
const db = require('../config/database');
const { auth } = require('../middleware/auth');

const router = express.Router();

router.get('/project/:projectId', auth, (req, res) => {
  const projectId = parseInt(req.params.projectId);
  const { type } = req.query;

  let query = `
    SELECT pi.*, u.full_name as created_by_name
    FROM post_investment pi
    LEFT JOIN users u ON pi.created_by = u.id
    WHERE pi.project_id = ?
  `;
  const params = [projectId];

  if (type) {
    query += ' AND pi.type = ?';
    params.push(type);
  }

  query += ' ORDER BY pi.date DESC, pi.created_at DESC';

  const records = db.prepare(query).all(...params);
  res.json({ post_investment: records });
});

router.post('/', auth, [
  body('projectId').notEmpty().withMessage('项目ID不能为空'),
  body('type').notEmpty().withMessage('类型不能为空'),
  body('title').notEmpty().withMessage('标题不能为空')
], (req, res) => {
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    return res.status(400).json({ errors: errors.array() });
  }

  const { projectId, type, title, content, date } = req.body;

  try {
    const result = db.prepare(
      `INSERT INTO post_investment (project_id, type, title, content, date, created_by)
       VALUES (?, ?, ?, ?, ?, ?)`
    ).run(
      projectId, type, title, content || '', date || new Date().toISOString().split('T')[0], req.user.id
    );

    res.status(201).json({ message: '投后记录创建成功', post_investment_id: result.lastInsertRowid });
  } catch (error) {
    res.status(500).json({ message: '服务器错误' });
  }
});

router.put('/:id', auth, (req, res) => {
  const recordId = parseInt(req.params.id);
  const { type, title, content, date } = req.body;

  const record = db.prepare('SELECT * FROM post_investment WHERE id = ?').get(recordId);
  if (!record) {
    return res.status(404).json({ message: '投后记录不存在' });
  }

  const updates = [];
  const params = [];

  if (type !== undefined) { updates.push('type = ?'); params.push(type); }
  if (title !== undefined) { updates.push('title = ?'); params.push(title); }
  if (content !== undefined) { updates.push('content = ?'); params.push(content); }
  if (date !== undefined) { updates.push('date = ?'); params.push(date); }
  params.push(recordId);

  db.prepare(`UPDATE post_investment SET ${updates.join(', ')} WHERE id = ?`).run(...params);
  res.json({ message: '投后记录更新成功' });
});

router.delete('/:id', auth, (req, res) => {
  const recordId = parseInt(req.params.id);
  db.prepare('DELETE FROM post_investment WHERE id = ?').run(recordId);
  res.json({ message: '投后记录已删除' });
});

router.get('/stats/project/:projectId', auth, (req, res) => {
  const projectId = parseInt(req.params.projectId);

  const stats = db.prepare(`
    SELECT
      type,
      COUNT(*) as count
    FROM post_investment
    WHERE project_id = ?
    GROUP BY type
  `).all(projectId);

  const monthly = db.prepare(`
    SELECT
      STRFTIME('%Y-%m', date) as month,
      COUNT(*) as count
    FROM post_investment
    WHERE project_id = ?
    GROUP BY STRFTIME('%Y-%m', date)
    ORDER BY month DESC
    LIMIT 12
  `).all(projectId);

  res.json({ stats, monthly });
});

module.exports = router;
