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('/', auth, (req, res) => {
  const { page = 1, limit = 20, institution_id, status, search } = req.query;
  const offset = (page - 1) * limit;

  let query = `
    SELECT p.*, i.name as institution_name, i.type as institution_type,
           u.full_name as created_by_name
    FROM incubation_projects p
    LEFT JOIN institutions i ON p.institution_id = i.id
    LEFT JOIN users u ON p.created_by = u.id
    WHERE 1=1
  `;
  const params = [];

  if (institution_id) {
    query += ' AND p.institution_id = ?';
    params.push(parseInt(institution_id));
  }

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

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

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

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

  let countQuery = query.replace(/SELECT p\.\*, i\.name as institution_name,.*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({
    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 p.*, i.name as institution_name, i.type as institution_type,
           u.full_name as created_by_name
    FROM incubation_projects p
    LEFT JOIN institutions i ON p.institution_id = i.id
    LEFT JOIN users u ON p.created_by = u.id
    WHERE p.id = ?
  `).get(projectId);

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

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

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

  const {
    institution_id, name, code, research_area, technology_description, innovation_points,
    development_stage, status, project_leader, leader_title, leader_contact,
    start_date, end_date, funding_amount, funding_source, objectives, expected_outcomes,
    research_progress, achievements, ip_description, ip_type, ip_status,
    ip_application_no, ip_holder, team_members, collaboration_needs, market_analysis,
    tags, notes
  } = req.body;

  try {
    // 验证机构是否存在
    const institution = db.prepare('SELECT id FROM institutions WHERE id = ?').get(institution_id);
    if (!institution) {
      return res.status(400).json({ message: '所属机构不存在' });
    }

    const result = db.prepare(`
      INSERT INTO incubation_projects (
        institution_id, name, code, research_area, technology_description, innovation_points,
        development_stage, status, project_leader, leader_title, leader_contact,
        start_date, end_date, funding_amount, funding_source, objectives, expected_outcomes,
        research_progress, achievements, ip_description, ip_type, ip_status,
        ip_application_no, ip_holder, team_members, collaboration_needs, market_analysis,
        tags, notes, created_by
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      institution_id, name, code || '', research_area || '', technology_description || '',
      innovation_points || '', development_stage || '', status || 'preparation',
      project_leader || '', leader_title || '', leader_contact || '',
      start_date || '', end_date || '', funding_amount || 0, funding_source || '',
      objectives || '', expected_outcomes || '', research_progress || '', achievements || '',
      ip_description || '', ip_type || '', ip_status || '', ip_application_no || '',
      ip_holder || '', team_members || '', collaboration_needs || '', market_analysis || '',
      tags || '', notes || '', req.user.id
    );

    res.status(201).json({ message: '孵化项目创建成功', project_id: result.lastInsertRowid });
  } catch (error) {
    console.error('创建孵化项目失败:', error);
    res.status(500).json({ message: '服务器错误: ' + error.message });
  }
});

// 更新孵化项目
router.put('/:id', auth, (req, res) => {
  const projectId = parseInt(req.params.id);

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

  const {
    institution_id, name, code, research_area, technology_description, innovation_points,
    development_stage, status, project_leader, leader_title, leader_contact,
    start_date, end_date, funding_amount, funding_source, objectives, expected_outcomes,
    research_progress, achievements, ip_description, ip_type, ip_status,
    ip_application_no, ip_holder, team_members, collaboration_needs, market_analysis,
    tags, notes
  } = req.body;

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

  const fields = {
    institution_id, name, code, research_area, technology_description, innovation_points,
    development_stage, status, project_leader, leader_title, leader_contact,
    start_date, end_date, funding_amount, funding_source, objectives, expected_outcomes,
    research_progress, achievements, ip_description, ip_type, ip_status,
    ip_application_no, ip_holder, team_members, collaboration_needs, market_analysis,
    tags, notes
  };

  Object.entries(fields).forEach(([key, value]) => {
    if (value !== undefined) {
      updates.push(`${key} = ?`);
      params.push(value);
    }
  });

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

  try {
    db.prepare(`UPDATE incubation_projects SET ${updates.join(', ')} WHERE id = ?`).run(...params);
    res.json({ message: '孵化项目更新成功' });
  } catch (error) {
    res.status(500).json({ message: '服务器错误' });
  }
});

// 删除孵化项目
router.delete('/:id', auth, (req, res) => {
  const projectId = parseInt(req.params.id);

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

  try {
    db.prepare('DELETE FROM incubation_projects WHERE id = ?').run(projectId);
    res.json({ message: '孵化项目删除成功' });
  } catch (error) {
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取孵化项目统计
router.get('/stats/summary', auth, (req, res) => {
  const { institution_id } = req.query;

  let whereClause = '';
  const params = [];
  if (institution_id) {
    whereClause = 'WHERE institution_id = ?';
    params.push(institution_id);
  }

  const statusStats = db.prepare(`
    SELECT status, COUNT(*) as count FROM incubation_projects ${whereClause} GROUP BY status
  `).all(...params);

  const totalProjects = db.prepare(`SELECT COUNT(*) as count FROM incubation_projects ${whereClause}`).get(...params);
  const totalFunding = db.prepare(`SELECT SUM(funding_amount) as total FROM incubation_projects ${whereClause}`).get(...params);

  res.json({
    byStatus: statusStats,
    totalProjects: totalProjects.count,
    totalFunding: totalFunding.total || 0
  });
});

module.exports = router;
