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

const router = express.Router();

router.get('/', auth, (req, res) => {
  const { page = 1, limit = 20, status, industry, search } = req.query;
  const offset = (page - 1) * limit;

  let query = `
    SELECT cp.*, u1.full_name as created_by_name, u2.full_name as reviewed_by_name
    FROM candidate_projects cp
    LEFT JOIN users u1 ON cp.created_by = u1.id
    LEFT JOIN users u2 ON cp.reviewed_by = u2.id
    WHERE 1=1
  `;
  const params = [];

  if (status) {
    query += ' AND cp.status = ?';
    params.push(status);
  }

  if (industry) {
    query += ' AND cp.industry = ?';
    params.push(industry);
  }

  if (search) {
    query += ' AND (cp.name LIKE ? OR cp.company_name LIKE ?)';
    const searchTerm = `%${search}%`;
    params.push(searchTerm, searchTerm);
  }

  query += ' ORDER BY cp.created_at DESC LIMIT ? OFFSET ?';
  params.push(parseInt(limit), offset);

  const projects = db.prepare(query).all(...params);

  let countQuery = query.replace(/SELECT.*FROM/, 'SELECT COUNT(*) as total FROM').replace(/ORDER BY.*$/, '').replace(/ LIMIT \? OFFSET \?$/, '');
  const countParams = params.slice(0, -2);
  const countResult = db.prepare(countQuery).get(...countParams);

  res.json({
    candidate_projects: projects,
    pagination: {
      page: parseInt(page),
      limit: parseInt(limit),
      total: countResult?.total || 0
    }
  });
});

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

  const project = db.prepare(`
    SELECT cp.*, u1.full_name as created_by_name, u2.full_name as reviewed_by_name
    FROM candidate_projects cp
    LEFT JOIN users u1 ON cp.created_by = u1.id
    LEFT JOIN users u2 ON cp.reviewed_by = u2.id
    WHERE cp.id = ?
  `).get(projectId);

  if (!project) {
    return res.status(404).json({ message: '备选项目不存在' });
  }

  res.json({ candidate_project: project });
});

router.post('/', auth, [
  body('name').notEmpty().withMessage('项目名称不能为空')
], (req, res) => {
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    return res.status(400).json({ errors: errors.array() });
  }

  const { name, companyName, industry, source, contactPerson, contactPhone, contactEmail, description, tags } = req.body;

  try {
    const result = db.prepare(
      `INSERT INTO candidate_projects (name, company_name, industry, source, contact_person, contact_phone, contact_email, description, tags, created_by)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
    ).run(
      name, companyName || '', industry || '', source || '', contactPerson || '', contactPhone || '', contactEmail || '', description || '',
      tags ? JSON.stringify(tags) : null, req.user.id
    );

    res.status(201).json({ message: '备选项目创建成功', candidate_project_id: result.lastInsertRowid });
  } catch (error) {
    res.status(500).json({ message: '服务器错误' });
  }
});

router.put('/:id', auth, (req, res) => {
  const projectId = parseInt(req.params.id);

  const project = db.prepare('SELECT * FROM candidate_projects WHERE id = ?').get(projectId);
  if (!project) {
    return res.status(404).json({ message: '备选项目不存在' });
  }

  const { name, companyName, industry, source, contactPerson, contactPhone, contactEmail, status, description, tags, rating, reviewNotes } = req.body;

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

  if (name !== undefined) { updates.push('name = ?'); params.push(name); }
  if (companyName !== undefined) { updates.push('company_name = ?'); params.push(companyName); }
  if (industry !== undefined) { updates.push('industry = ?'); params.push(industry); }
  if (source !== undefined) { updates.push('source = ?'); params.push(source); }
  if (contactPerson !== undefined) { updates.push('contact_person = ?'); params.push(contactPerson); }
  if (contactPhone !== undefined) { updates.push('contact_phone = ?'); params.push(contactPhone); }
  if (contactEmail !== undefined) { updates.push('contact_email = ?'); params.push(contactEmail); }
  if (status !== undefined) { updates.push('status = ?'); params.push(status); }
  if (description !== undefined) { updates.push('description = ?'); params.push(description); }
  if (tags !== undefined) { updates.push('tags = ?'); params.push(JSON.stringify(tags)); }
  if (rating !== undefined) { updates.push('rating = ?'); params.push(rating); }
  if (reviewNotes !== undefined) { updates.push('review_notes = ?'); params.push(reviewNotes); }
  if (status !== undefined && status !== 'pending') {
    updates.push('reviewed_by = ?'); params.push(req.user.id);
  }

  updates.push('updated_at = CURRENT_TIMESTAMP');
  params.push(projectId);

  db.prepare(`UPDATE candidate_projects SET ${updates.join(', ')} WHERE id = ?`).run(...params);
  res.json({ message: '备选项目更新成功' });
});

router.delete('/:id', auth, authorize('admin', 'investment_manager'), (req, res) => {
  const projectId = parseInt(req.params.id);

  const project = db.prepare('SELECT * FROM candidate_projects WHERE id = ?').get(projectId);
  if (!project) {
    return res.status(404).json({ message: '备选项目不存在' });
  }

  db.prepare('DELETE FROM candidate_projects WHERE id = ?').run(projectId);
  res.json({ message: '备选项目删除成功' });
});

router.post('/:id/convert', auth, authorize('admin', 'investment_manager'), (req, res) => {
  const projectId = parseInt(req.params.id);

  const candidate = db.prepare('SELECT * FROM candidate_projects WHERE id = ?').get(projectId);
  if (!candidate) {
    return res.status(404).json({ message: '备选项目不存在' });
  }

  try {
    const result = db.prepare(
      `INSERT INTO projects (name, company_name, industry, description, tags, created_by, status)
       VALUES (?, ?, ?, ?, ?, ?, ?)`
    ).run(
      candidate.name, candidate.company_name || '', candidate.industry || '', candidate.description || '',
      candidate.tags, req.user.id, 'draft'
    );

    db.prepare('UPDATE candidate_projects SET status = ? WHERE id = ?').run('converted', projectId);

    res.status(201).json({ message: '项目已成功转换', project_id: result.lastInsertRowid });
  } catch (error) {
    res.status(500).json({ message: '服务器错误' });
  }
});

module.exports = router;
