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

// 取得所有問題
router.get('/', (req, res) => {
  const { project_id, status, priority, page = 1, limit = 10 } = req.query;
  
  let query = `
    SELECT i.*, p.name as project_name 
    FROM issues i 
    LEFT JOIN projects p ON i.project_id = p.id 
    WHERE 1=1
  `;
  const params = [];
  
  if (project_id) {
    query += ' AND i.project_id = ?';
    params.push(project_id);
  }
  
  if (status) {
    query += ' AND i.status = ?';
    params.push(status);
  }
  
  if (priority) {
    query += ' AND i.priority = ?';
    params.push(priority);
  }
  
  query += ' ORDER BY i.created_at DESC';
  
  // 分頁
  const offset = (page - 1) * limit;
  query += ' LIMIT ? OFFSET ?';
  params.push(parseInt(limit), offset);
  
  db.all(query, params, (err, rows) => {
    if (err) {
      res.status(500).json({ success: false, message: '查詢失敗', error: err.message });
    } else {
      // 取得總數
      let countQuery = 'SELECT COUNT(*) as total FROM issues i WHERE 1=1';
      const countParams = [];
      
      if (project_id) {
        countQuery += ' AND i.project_id = ?';
        countParams.push(project_id);
      }
      if (status) {
        countQuery += ' AND i.status = ?';
        countParams.push(status);
      }
      if (priority) {
        countQuery += ' AND i.priority = ?';
        countParams.push(priority);
      }
      
      db.get(countQuery, countParams, (err, countResult) => {
        if (err) {
          res.status(500).json({ success: false, message: '查詢總數失敗', error: err.message });
        } else {
          res.json({
            success: true,
            data: rows,
            pagination: {
              page: parseInt(page),
              limit: parseInt(limit),
              total: countResult.total,
              pages: Math.ceil(countResult.total / limit)
            }
          });
        }
      });
    }
  });
});

// 取得單一問題
router.get('/:id', (req, res) => {
  const { id } = req.params;
  
  const query = `
    SELECT i.*, p.name as project_name 
    FROM issues i 
    LEFT JOIN projects p ON i.project_id = p.id 
    WHERE i.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 {
      // 取得評論
      db.all('SELECT * FROM issue_comments WHERE issue_id = ? ORDER BY created_at ASC', [id], (err, comments) => {
        if (err) {
          res.status(500).json({ success: false, message: '查詢評論失敗', error: err.message });
        } else {
          res.json({
            success: true,
            data: { ...row, comments }
          });
        }
      });
    }
  });
});

// 建立新問題
router.post('/', (req, res) => {
  const { project_id, title, description, priority, assignee, reporter } = req.body;
  
  if (!title) {
    return res.status(400).json({ success: false, message: '標題為必填欄位' });
  }
  
  const query = `
    INSERT INTO issues (project_id, title, description, priority, assignee, reporter)
    VALUES (?, ?, ?, ?, ?, ?)
  `;
  
  db.run(query, [project_id, title, description, priority || 'medium', assignee, reporter], 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 { title, description, status, priority, assignee } = req.body;
  
  const query = `
    UPDATE issues 
    SET title = COALESCE(?, title),
        description = COALESCE(?, description),
        status = COALESCE(?, status),
        priority = COALESCE(?, priority),
        assignee = COALESCE(?, assignee),
        updated_at = CURRENT_TIMESTAMP
    WHERE id = ?
  `;
  
  db.run(query, [title, description, status, priority, assignee, 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.run('DELETE FROM issues 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.post('/:id/comments', (req, res) => {
  const { id } = req.params;
  const { author, content } = req.body;
  
  if (!author || !content) {
    return res.status(400).json({ success: false, message: '作者和內容為必填欄位' });
  }
  
  const query = 'INSERT INTO issue_comments (issue_id, author, content) VALUES (?, ?, ?)';
  
  db.run(query, [id, author, content], 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 }
      });
    }
  });
});

module.exports = router;