const express = require('express');
const { db } = require('../database/init');
const router = express.Router();

// 取得所有專案
router.get('/', (req, res) => {
  const query = 'SELECT * FROM projects ORDER BY created_at DESC';
  
  db.all(query, [], (err, rows) => {
    if (err) {
      res.status(500).json({ success: false, message: '查詢失敗', error: err.message });
    } else {
      res.json({ success: true, data: rows });
    }
  });
});

// 取得單一專案
router.get('/:id', (req, res) => {
  const { id } = req.params;
  
  const query = 'SELECT * FROM projects WHERE id = ?';
  
  db.get(query, [id], (err, row) => {
    if (err) {
      res.status(500).json({ success: false, message: '查詢失敗', error: err.message });
    } else if (!row) {
      res.status(404).json({ success: false, message: '專案不存在' });
    } else {
      res.json({ success: true, data: row });
    }
  });
});

// 建立新專案
router.post('/', (req, res) => {
  const { name, description, status } = req.body;
  
  if (!name) {
    return res.status(400).json({ success: false, message: '專案名稱為必填欄位' });
  }
  
  const query = 'INSERT INTO projects (name, description, status) VALUES (?, ?, ?)';
  
  db.run(query, [name, description, status || 'active'], function(err) {
    if (err) {
      res.status(500).json({ success: false, message: '建立失敗', error: err.message });
    } else {
      res.status(201).json({
        success: true,
        message: '專案建立成功',
        data: { id: this.lastID }
      });
    }
  });
});

// 更新專案
router.put('/:id', (req, res) => {
  const { id } = req.params;
  const { name, description, status } = req.body;
  
  const query = `
    UPDATE projects 
    SET name = COALESCE(?, name),
        description = COALESCE(?, description),
        status = COALESCE(?, status),
        updated_at = CURRENT_TIMESTAMP
    WHERE id = ?
  `;
  
  db.run(query, [name, description, status, id], function(err) {
    if (err) {
      res.status(500).json({ success: false, message: '更新失敗', error: err.message });
    } else if (this.changes === 0) {
      res.status(404).json({ success: false, message: '專案不存在' });
    } else {
      res.json({ success: true, message: '專案更新成功' });
    }
  });
});

// 刪除專案
router.delete('/:id', (req, res) => {
  const { id } = req.params;
  
  // 先檢查是否有相關的問題
  db.get('SELECT COUNT(*) as count FROM issues WHERE project_id = ?', [id], (err, result) => {
    if (err) {
      res.status(500).json({ success: false, message: '檢查失敗', error: err.message });
    } else if (result.count > 0) {
      res.status(400).json({ 
        success: false, 
        message: '無法刪除專案，請先處理相關的問題' 
      });
    } else {
      db.run('DELETE FROM projects WHERE id = ?', [id], function(err) {
        if (err) {
          res.status(500).json({ success: false, message: '刪除失敗', error: err.message });
        } else if (this.changes === 0) {
          res.status(404).json({ success: false, message: '專案不存在' });
        } else {
          res.json({ success: true, message: '專案刪除成功' });
        }
      });
    }
  });
});

// 取得專案統計
router.get('/:id/stats', (req, res) => {
  const { id } = req.params;
  
  const query = `
    SELECT 
      COUNT(*) as total_issues,
      SUM(CASE WHEN status = 'open' THEN 1 ELSE 0 END) as open_issues,
      SUM(CASE WHEN status = 'in_progress' THEN 1 ELSE 0 END) as in_progress_issues,
      SUM(CASE WHEN status = 'closed' THEN 1 ELSE 0 END) as closed_issues,
      SUM(CASE WHEN priority = 'high' THEN 1 ELSE 0 END) as high_priority_issues,
      SUM(CASE WHEN priority = 'medium' THEN 1 ELSE 0 END) as medium_priority_issues,
      SUM(CASE WHEN priority = 'low' THEN 1 ELSE 0 END) as low_priority_issues
    FROM issues 
    WHERE project_id = ?
  `;
  
  db.get(query, [id], (err, row) => {
    if (err) {
      res.status(500).json({ success: false, message: '查詢統計失敗', error: err.message });
    } else {
      res.json({ success: true, data: row });
    }
  });
});

module.exports = router;