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, type, search } = req.query;
  const offset = (page - 1) * limit;

  let query = `
    SELECT i.*, u.full_name as created_by_name,
           (SELECT COUNT(*) FROM incubation_projects WHERE institution_id = i.id) as project_count
    FROM institutions i
    LEFT JOIN users u ON i.created_by = u.id
    WHERE 1=1
  `;
  const params = [];

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

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

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

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

  let countQuery = query.replace(/SELECT i\.\*, u\.full_name as created_by_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({
    institutions,
    pagination: {
      page: parseInt(page),
      limit: parseInt(limit),
      total: countResult?.total || 0
    }
  });
});

// 获取机构详情
router.get('/:id', auth, (req, res) => {
  const institutionId = parseInt(req.params.id);

  const institution = db.prepare(`
    SELECT i.*, u.full_name as created_by_name
    FROM institutions i
    LEFT JOIN users u ON i.created_by = u.id
    WHERE i.id = ?
  `).get(institutionId);

  if (!institution) {
    return res.status(404).json({ message: '机构不存在' });
  }

  const projects = db.prepare('SELECT * FROM incubation_projects WHERE institution_id = ? ORDER BY created_at DESC').all(institutionId);

  res.json({
    institution,
    projects
  });
});

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

  const { name, type, type_category, description, address, contact_person, contact_phone, contact_email, website, established_date } = req.body;

  try {
    const result = db.prepare(`
      INSERT INTO institutions (name, type, type_category, description, address, contact_person, contact_phone, contact_email, website, established_date, created_by)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(name, type, type_category || '', description || '', address || '', contact_person || '', contact_phone || '', contact_email || '', website || '', established_date || '', req.user.id);

    res.status(201).json({ message: '机构创建成功', institution_id: result.lastInsertRowid });
  } catch (error) {
    res.status(500).json({ message: '服务器错误' });
  }
});

// 更新机构
router.put('/:id', auth, (req, res) => {
  const institutionId = parseInt(req.params.id);

  const institution = db.prepare('SELECT * FROM institutions WHERE id = ?').get(institutionId);
  if (!institution) {
    return res.status(404).json({ message: '机构不存在' });
  }

  const { name, type, type_category, description, address, contact_person, contact_phone, contact_email, website, established_date } = req.body;

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

  if (name !== undefined) { updates.push('name = ?'); params.push(name); }
  if (type !== undefined) { updates.push('type = ?'); params.push(type); }
  if (type_category !== undefined) { updates.push('type_category = ?'); params.push(type_category); }
  if (description !== undefined) { updates.push('description = ?'); params.push(description); }
  if (address !== undefined) { updates.push('address = ?'); params.push(address); }
  if (contact_person !== undefined) { updates.push('contact_person = ?'); params.push(contact_person); }
  if (contact_phone !== undefined) { updates.push('contact_phone = ?'); params.push(contact_phone); }
  if (contact_email !== undefined) { updates.push('contact_email = ?'); params.push(contact_email); }
  if (website !== undefined) { updates.push('website = ?'); params.push(website); }
  if (established_date !== undefined) { updates.push('established_date = ?'); params.push(established_date); }

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

  db.prepare(`UPDATE institutions SET ${updates.join(', ')} WHERE id = ?`).run(...params);
  res.json({ message: '机构更新成功' });
});

// 删除机构
router.delete('/:id', auth, (req, res) => {
  const institutionId = parseInt(req.params.id);

  const institution = db.prepare('SELECT * FROM institutions WHERE id = ?').get(institutionId);
  if (!institution) {
    return res.status(404).json({ message: '机构不存在' });
  }

  const projectCount = db.prepare('SELECT COUNT(*) as count FROM incubation_projects WHERE institution_id = ?').get(institutionId);
  if (projectCount.count > 0) {
    return res.status(400).json({ message: '该机构下存在孵化项目，无法删除' });
  }

  try {
    db.prepare('DELETE FROM institutions WHERE id = ?').run(institutionId);
    res.json({ message: '机构删除成功' });
  } catch (error) {
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取机构统计信息
router.get('/stats/summary', auth, (req, res) => {
  const stats = {};

  const typeStats = db.prepare(`
    SELECT type, COUNT(*) as count FROM institutions GROUP BY type
  `).all();
  stats.byType = typeStats;

  const totalInstitutions = db.prepare('SELECT COUNT(*) as count FROM institutions').get();
  stats.totalInstitutions = totalInstitutions.count;

  const totalProjects = db.prepare('SELECT COUNT(*) as count FROM incubation_projects').get();
  stats.totalProjects = totalProjects.count;

  res.json(stats);
});

module.exports = router;
