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

const router = express.Router();

const uploadDir = path.join(__dirname, '../../uploads');
if (!fs.existsSync(uploadDir)) {
  fs.mkdirSync(uploadDir, { recursive: true });
}

const storage = multer.diskStorage({
  destination: (req, file, cb) => {
    cb(null, uploadDir);
  },
  filename: (req, file, cb) => {
    const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
    cb(null, uniqueSuffix + path.extname(file.originalname));
  }
});

const upload = multer({
  storage: storage,
  fileFilter: (req, file, cb) => {
    const allowedTypes = [
      '.pdf', '.ppt', '.pptx', '.doc', '.docx', '.txt',
      'application/pdf',
      'application/vnd.ms-powerpoint',
      'application/vnd.openxmlformats-officedocument.presentationml.presentation',
      'application/msword',
      'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
      'text/plain'
    ];
    const ext = path.extname(file.originalname).toLowerCase();
    if (allowedTypes.includes(ext) || allowedTypes.includes(file.mimetype)) {
      cb(null, true);
    } else {
      cb(new Error('不支持的文件格式'), false);
    }
  },
  limits: {
    fileSize: 50 * 1024 * 1024
  }
});

const analyzeDocument = async (filePath, fileName) => {
  try {
    const stats = fs.statSync(filePath);
    const ext = path.extname(fileName).toLowerCase();
    
    const fileTypeMap = {
      '.pdf': 'PDF文档',
      '.ppt': 'PowerPoint演示文稿',
      '.pptx': 'PowerPoint演示文稿',
      '.doc': 'Word文档',
      '.docx': 'Word文档',
      '.txt': '文本文件'
    };
    
    const analysisResult = {
      fileName: fileName,
      fileType: fileTypeMap[ext] || '未知格式',
      fileSize: stats.size,
      fileSizeFormatted: (stats.size / 1024 / 1024).toFixed(2) + ' MB',
      uploadTime: new Date().toISOString(),
      summary: '文件上传成功，等待进一步分析。',
      keywords: ['项目介绍', '投资分析'],
      recommendations: [
        '建议进一步审阅文档内容',
        '确认项目信息完整性',
        '评估投资风险与收益'
      ]
    };
    
    return analysisResult;
  } catch (error) {
    console.error('文件分析错误:', error);
    return {
      error: '文件分析失败',
      message: error.message
    };
  }
};

router.get('/', auth, (req, res) => {
  const { page = 1, limit = 20, status, industry, stage, search, assignedTo } = req.query;
  const offset = (page - 1) * limit;
  
  let query = `
    SELECT p.*, 
           u1.full_name as created_by_name,
           u2.full_name as assigned_to_name
    FROM projects p
    LEFT JOIN users u1 ON p.created_by = u1.id
    LEFT JOIN users u2 ON p.assigned_to = u2.id
    WHERE 1=1
  `;
  const params = [];
  
  if (status) {
    query += ' AND p.status = ?';
    params.push(status);
  }
  
  if (industry) {
    query += ' AND p.industry = ?';
    params.push(industry);
  }
  
  if (stage) {
    query += ' AND p.stage = ?';
    params.push(stage);
  }
  
  if (assignedTo) {
    query += ' AND p.assigned_to = ?';
    params.push(parseInt(assignedTo));
  }
  
  if (search) {
    query += ' AND (p.name LIKE ? OR p.code LIKE ? OR p.company_name 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.*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.*, 
           u1.full_name as created_by_name,
           u2.full_name as assigned_to_name
    FROM projects p
    LEFT JOIN users u1 ON p.created_by = u1.id
    LEFT JOIN users u2 ON p.assigned_to = u2.id
    WHERE p.id = ?
  `).get(projectId);
  
  if (!project) {
    return res.status(404).json({ message: '项目不存在' });
  }
  
  const investors = db.prepare('SELECT pi.*, i.name as investor_name FROM project_investors pi JOIN investors i ON pi.investor_id = i.id WHERE pi.project_id = ?').all(projectId);
  const tasks = db.prepare('SELECT pt.*, u.full_name as assigned_to_name FROM project_tasks pt LEFT JOIN users u ON pt.assigned_to = u.id WHERE pt.project_id = ? ORDER BY pt.created_at DESC').all(projectId);
  const milestones = db.prepare('SELECT * FROM project_milestones WHERE project_id = ? ORDER BY planned_date').all(projectId);
  const updates = db.prepare('SELECT pu.*, u.full_name as created_by_name FROM project_updates pu JOIN users u ON pu.created_by = u.id WHERE pu.project_id = ? ORDER BY pu.created_at DESC').all(projectId);
  const documents = db.prepare('SELECT pd.*, u.full_name as uploaded_by_name FROM project_documents pd LEFT JOIN users u ON pd.uploaded_by = u.id WHERE pd.project_id = ?').all(projectId);
  
  res.json({
    project,
    investors: investors || [],
    tasks: tasks || [],
    milestones: milestones || [],
    updates: updates || [],
    documents: documents || []
  });
});

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

  const { name, code, companyName, industry, stage, status, investmentAmount, valuation, equityRatio, description, tags, assignedTo } = req.body;

  try {
    const result = db.prepare(
      `INSERT INTO projects (name, code, company_name, industry, stage, status, investment_amount, valuation, equity_ratio, description, tags, created_by, assigned_to) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
    ).run(name, code || '', companyName, industry || '', stage || '', status || 'draft', investmentAmount || 0, valuation || 0, equityRatio || 0, description || '', tags ? JSON.stringify(tags) : null, req.user.id, assignedTo || null);

    res.status(201).json({ message: '项目创建成功', projectId: result.lastInsertRowid });
  } catch (err) {
    if (err.message.includes('UNIQUE')) {
      return res.status(400).json({ message: '项目代码已存在' });
    }
    res.status(500).json({ message: '服务器错误' });
  }
});

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

  const { name, code, companyName, industry, stage, status, investmentAmount, valuation, equityRatio, description, tags, assignedTo } = req.body;

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

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

  if (name !== undefined) { updates.push('name = ?'); params.push(name); }
  if (code !== undefined) { updates.push('code = ?'); params.push(code); }
  if (companyName !== undefined) { updates.push('company_name = ?'); params.push(companyName); }
  if (industry !== undefined) { updates.push('industry = ?'); params.push(industry); }
  if (stage !== undefined) { updates.push('stage = ?'); params.push(stage); }
  if (status !== undefined) { updates.push('status = ?'); params.push(status); }
  if (investmentAmount !== undefined) { updates.push('investment_amount = ?'); params.push(investmentAmount); }
  if (valuation !== undefined) { updates.push('valuation = ?'); params.push(valuation); }
  if (equityRatio !== undefined) { updates.push('equity_ratio = ?'); params.push(equityRatio); }
  if (description !== undefined) { updates.push('description = ?'); params.push(description); }
  if (tags !== undefined) { updates.push('tags = ?'); params.push(JSON.stringify(tags)); }
  if (assignedTo !== undefined) { updates.push('assigned_to = ?'); params.push(assignedTo); }

  if (updates.length === 0) {
    return res.status(400).json({ message: '没有提供更新数据' });
  }

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

  db.prepare(`UPDATE 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 projects WHERE id = ?').get(projectId);
  if (!project) {
    return res.status(404).json({ message: '项目不存在' });
  }

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

router.post('/:id/tasks', auth, [
  body('title').notEmpty().withMessage('任务标题不能为空')
], (req, res) => {
  const projectId = parseInt(req.params.id);
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    return res.status(400).json({ errors: errors.array() });
  }

  const { title, description, priority, assignedTo, dueDate } = req.body;

  const result = db.prepare(
    'INSERT INTO project_tasks (project_id, title, description, priority, assigned_to, due_date, created_by) VALUES (?, ?, ?, ?, ?, ?, ?)'
  ).run(projectId, title, description || '', priority || 'medium', assignedTo || null, dueDate || null, req.user.id);

  res.status(201).json({ message: '任务创建成功', taskId: result.lastInsertRowid });
});

router.put('/:id/tasks/:taskId', auth, (req, res) => {
  const { taskId } = req.params;
  const { title, description, status, priority, assignedTo, dueDate, completedAt } = req.body;

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

  if (title !== undefined) updates.push('title = ?'), params.push(title);
  if (description !== undefined) updates.push('description = ?'), params.push(description);
  if (status !== undefined) updates.push('status = ?'), params.push(status);
  if (priority !== undefined) updates.push('priority = ?'), params.push(priority);
  if (assignedTo !== undefined) updates.push('assigned_to = ?'), params.push(assignedTo);
  if (dueDate !== undefined) updates.push('due_date = ?'), params.push(dueDate);
  if (completedAt !== undefined) updates.push('completed_at = ?'), params.push(completedAt);
  updates.push('updated_at = CURRENT_TIMESTAMP');
  params.push(taskId);

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

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

  const { name, description, plannedDate } = req.body;

  const result = db.prepare(
    'INSERT INTO project_milestones (project_id, name, description, planned_date) VALUES (?, ?, ?, ?)'
  ).run(projectId, name, description || '', plannedDate || null);

  res.status(201).json({ message: '里程碑创建成功', milestoneId: result.lastInsertRowid });
});

router.put('/:id/milestones/:milestoneId', auth, (req, res) => {
  const { milestoneId } = req.params;
  const { name, description, plannedDate, actualDate, status } = req.body;

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

  if (name !== undefined) updates.push('name = ?'), params.push(name);
  if (description !== undefined) updates.push('description = ?'), params.push(description);
  if (plannedDate !== undefined) updates.push('planned_date = ?'), params.push(plannedDate);
  if (actualDate !== undefined) updates.push('actual_date = ?'), params.push(actualDate);
  if (status !== undefined) updates.push('status = ?'), params.push(status);
  params.push(milestoneId);

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

router.post('/:id/updates', auth, [
  body('title').notEmpty().withMessage('更新标题不能为空')
], (req, res) => {
  const projectId = parseInt(req.params.id);
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    return res.status(400).json({ errors: errors.array() });
  }

  const { title, content, updateType } = req.body;

  const result = db.prepare(
    'INSERT INTO project_updates (project_id, title, content, update_type, created_by) VALUES (?, ?, ?, ?, ?)'
  ).run(projectId, title, content || '', updateType || 'general', req.user.id);

  res.status(201).json({ message: '项目更新创建成功', updateId: result.lastInsertRowid });
});

router.post('/:id/investors', auth, [
  body('investorId').notEmpty().withMessage('投资者ID不能为空')
], (req, res) => {
  const projectId = parseInt(req.params.id);
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    return res.status(400).json({ errors: errors.array() });
  }

  const { investorId, investmentAmount, investmentDate, equityRatio, notes } = req.body;

  try {
    const result = db.prepare(
      'INSERT INTO project_investors (project_id, investor_id, investment_amount, investment_date, equity_ratio, notes) VALUES (?, ?, ?, ?, ?, ?)'
    ).run(projectId, investorId, investmentAmount || 0, investmentDate || null, equityRatio || 0, notes || '');

    res.status(201).json({ message: '投资者关联成功', id: result.lastInsertRowid });
  } catch (err) {
    if (err.message.includes('UNIQUE')) {
      return res.status(400).json({ message: '该投资者已关联此项目' });
    }
    res.status(500).json({ message: '服务器错误' });
  }
});

router.delete('/:id/investors/:investorId', auth, (req, res) => {
  const { id: projectId, investorId } = req.params;
  
  db.prepare('DELETE FROM project_investors WHERE project_id = ? AND investor_id = ?').run(projectId, investorId);
  res.json({ message: '投资者关联已解除' });
});

router.get('/dashboard/stats', auth, (req, res) => {
  const stats = db.prepare(`
    SELECT 
      (SELECT COUNT(*) FROM projects WHERE status = 'active') as active_projects,
      (SELECT COUNT(*) FROM projects WHERE status = 'completed') as completed_projects,
      (SELECT COUNT(*) FROM investors) as total_investors,
      (SELECT COALESCE(SUM(investment_amount), 0) FROM projects) as total_investment
  `).get();
  
  res.json({ stats });
});

router.post('/:id/documents', auth, upload.single('file'), async (req, res) => {
  const projectId = parseInt(req.params.id);
  
  if (!req.file) {
    return res.status(400).json({ message: '请选择要上传的文件' });
  }
  
  try {
    const project = db.prepare('SELECT * FROM projects WHERE id = ?').get(projectId);
    if (!project) {
      fs.unlinkSync(req.file.path);
      return res.status(404).json({ message: '项目不存在' });
    }
    
    const result = db.prepare(
      'INSERT INTO project_documents (project_id, name, type, file_path, file_size, uploaded_by, analysis_status) VALUES (?, ?, ?, ?, ?, ?, ?)'
    ).run(
      projectId,
      req.file.originalname,
      req.file.mimetype,
      req.file.filename,
      req.file.size,
      req.user.id,
      'analyzing'
    );
    
    const docId = result.lastInsertRowid;
    
    setTimeout(async () => {
      try {
        const analysisResult = await analyzeDocument(req.file.path, req.file.originalname);
        db.prepare(
          'UPDATE project_documents SET analysis_status = ?, analysis_result = ?, analysis_completed_at = CURRENT_TIMESTAMP WHERE id = ?'
        ).run('completed', JSON.stringify(analysisResult), docId);
      } catch (error) {
        console.error('异步文件分析错误:', error);
        db.prepare(
          'UPDATE project_documents SET analysis_status = ?, analysis_result = ? WHERE id = ?'
        ).run('error', JSON.stringify({ error: '分析失败' }), docId);
      }
    }, 1000);
    
    res.status(201).json({ 
      message: '文件上传成功', 
      documentId: docId,
      fileName: req.file.originalname
    });
  } catch (error) {
    if (req.file && fs.existsSync(req.file.path)) {
      fs.unlinkSync(req.file.path);
    }
    console.error('文件上传错误:', error);
    res.status(500).json({ message: '文件上传失败' });
  }
});

router.get('/:id/documents', auth, (req, res) => {
  const projectId = parseInt(req.params.id);
  const documents = db.prepare(
    'SELECT pd.*, u.full_name as uploaded_by_name FROM project_documents pd LEFT JOIN users u ON pd.uploaded_by = u.id WHERE pd.project_id = ? ORDER BY pd.created_at DESC'
  ).all(projectId);
  res.json({ documents });
});

router.delete('/:id/documents/:docId', auth, (req, res) => {
  const { docId } = req.params;
  
  const document = db.prepare('SELECT * FROM project_documents WHERE id = ?').get(docId);
  if (!document) {
    return res.status(404).json({ message: '文档不存在' });
  }
  
  try {
    if (document.file_path) {
      const filePath = path.join(uploadDir, document.file_path);
      if (fs.existsSync(filePath)) {
        fs.unlinkSync(filePath);
      }
    }
    
    db.prepare('DELETE FROM project_documents WHERE id = ?').run(docId);
    res.json({ message: '文档删除成功' });
  } catch (error) {
    console.error('删除文档错误:', error);
    res.status(500).json({ message: '删除文档失败' });
  }
});

router.post('/:id/documents/:docId/analyze', auth, async (req, res) => {
  const { docId } = req.params;
  
  const document = db.prepare('SELECT * FROM project_documents WHERE id = ?').get(docId);
  if (!document) {
    return res.status(404).json({ message: '文档不存在' });
  }
  
  try {
    db.prepare(
      'UPDATE project_documents SET analysis_status = ? WHERE id = ?'
    ).run('analyzing', docId);
    
    const filePath = path.join(uploadDir, document.file_path);
    const analysisResult = await analyzeDocument(filePath, document.name);
    
    db.prepare(
      'UPDATE project_documents SET analysis_status = ?, analysis_result = ?, analysis_completed_at = CURRENT_TIMESTAMP WHERE id = ?'
    ).run('completed', JSON.stringify(analysisResult), docId);
    
    res.json({ message: '文件分析成功', analysisResult });
  } catch (error) {
    console.error('文件分析错误:', error);
    db.prepare(
      'UPDATE project_documents SET analysis_status = ?, analysis_result = ? WHERE id = ?'
    ).run('error', JSON.stringify({ error: error.message }), docId);
    res.status(500).json({ message: '文件分析失败' });
  }
});

router.get('/:id/documents/:docId/download', auth, (req, res) => {
  const { docId } = req.params;
  
  const document = db.prepare('SELECT * FROM project_documents WHERE id = ?').get(docId);
  if (!document) {
    return res.status(404).json({ message: '文档不存在' });
  }
  
  const filePath = path.join(uploadDir, document.file_path);
  if (!fs.existsSync(filePath)) {
    return res.status(404).json({ message: '文件不存在' });
  }
  
  res.download(filePath, document.name);
});

module.exports = router;
