jurong_circle_black/routes/announcements.js

const express = require('express');
const { getDB } = require('../database');
const { auth, adminAuth } = require('../middleware/auth');
const dayjs = require('dayjs');

const router = express.Router();



router.get('/', auth, async (req, res) => {
  try {
    const db = getDB();
    const { page = 1, limit = 10, status, type, keyword } = req.query;
    const offset = (page - 1) * limit;
    
    let whereClause = 'WHERE 1=1';
    const params = [];
    
    if (status) {
      whereClause += ' AND status = ?';
      params.push(status);
    }
    
    if (type) {
      whereClause += ' AND type = ?';
      params.push(type);
    }
    
    if (keyword) {
      whereClause += ' AND (title LIKE ? OR content LIKE ?)';
      params.push(`%${keyword}%`, `%${keyword}%`);
    }
    
    // 获取总数
    const countQuery = `SELECT COUNT(*) as total FROM announcements ${whereClause}`;
    const [countResult] = await db.execute(countQuery, params);
    const total = countResult[0].total;
    
    // 获取公告列表（包含用户阅读状态）
    const limitValue = Math.max(1, Math.min(100, parseInt(limit)));
    const offsetValue = Math.max(0, parseInt(offset));
    
    const query = `
      SELECT a.*, u.username as creator_name,
             uar.is_read,
             uar.read_at,
             CASE 
               WHEN a.expire_time IS NOT NULL AND a.expire_time < NOW() THEN 1
               ELSE 0
             END as is_expired
      FROM announcements a 
      LEFT JOIN users u ON a.created_by = u.id 
      LEFT JOIN user_announcement_reads uar ON a.id = uar.announcement_id AND uar.user_id = ?
      ${whereClause} 
      ORDER BY a.is_pinned DESC, a.created_at DESC 
      LIMIT ${limitValue} OFFSET ${offsetValue}
    `;
    
    const [announcements] = await db.execute(query, [req.user.id, ...params]);
    

    
    const expiredUnreadAnnouncements = announcements.filter(a => a.is_expired && !a.is_read);
    
    if (expiredUnreadAnnouncements.length > 0) {
      const expiredIds = expiredUnreadAnnouncements.map(a => a.id);
      await db.execute(`
        INSERT INTO user_announcement_reads (user_id, announcement_id, is_read, read_at)
        VALUES ${expiredIds.map(() => '(?, ?, TRUE, NOW())').join(', ')}
        ON DUPLICATE KEY UPDATE is_read = TRUE, read_at = NOW()
      `, expiredIds.flatMap(id => [req.user.id, id]));
      
      // 更新返回数据中的阅读状态
      expiredUnreadAnnouncements.forEach(a => {
        a.is_read = true;
        a.read_at = new Date();
      });
    }
    
    res.json({
      success: true,
      data: {
        announcements,
        total,
        page: parseInt(page),
        limit: parseInt(limit),
        totalPages: Math.ceil(total / limit)
      }
    });
  } catch (error) {
    console.error('获取公告列表失败:', error);
    res.status(500).json({ success: false, message: '获取公告列表失败' });
  }
});


router.get('/:id', auth, async (req, res) => {
  try {
    const db = getDB();
    const { id } = req.params;
    
    const query = `
      SELECT a.*, u.username as creator_name,
             uar.is_read,
             uar.read_at,
             CASE 
               WHEN a.expire_time IS NOT NULL AND a.expire_time < NOW() THEN 1
               ELSE 0
             END as is_expired
      FROM announcements a 
      LEFT JOIN users u ON a.created_by = u.id 
      LEFT JOIN user_announcement_reads uar ON a.id = uar.announcement_id AND uar.user_id = ?
      WHERE a.id = ?
    `;
    
    const [result] = await db.execute(query, [req.user.id, id]);
    
    if (result.length === 0) {
      return res.status(404).json({ success: false, message: '公告不存在' });
    }
    
    const announcement = result[0];
    
    // 如果公告未读或已过期但未标记为已读，则标记为已读
    if (!announcement.is_read || (announcement.is_expired && !announcement.is_read)) {
      await db.execute(`
        INSERT INTO user_announcement_reads (user_id, announcement_id, is_read, read_at)
        VALUES (?, ?, TRUE, NOW())
        ON DUPLICATE KEY UPDATE is_read = TRUE, read_at = NOW()
      `, [req.user.id, id]);
      
      announcement.is_read = true;
      announcement.read_at = new Date();
    }
    
    res.json({ success: true, data: announcement });
  } catch (error) {
    console.error('获取公告详情失败:', error);
    res.status(500).json({ success: false, message: '获取公告详情失败' });
  }
});


router.post('/', auth, adminAuth, async (req, res) => {
  try {
    const db = getDB();
    const {
      title,
      content,
      type = 'system',
      priority = 'medium',
      status = 'draft',
      is_pinned = false,
      publish_time,
      expire_time
    } = req.body;
    
    if (!title || !content) {
      return res.status(400).json({ success: false, message: '标题和内容不能为空' });
    }
    
    const query = `
      INSERT INTO announcements (
        title, content, type, priority, status, is_pinned, 
        publish_time, expire_time, created_by, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
    `;
    
    const [result] = await db.execute(query, [
      title,
      content,
      type,
      priority,
      status,
      is_pinned,
      publish_time || null,
      expire_time || null,
      req.user.id
    ]);
    
    res.status(201).json({
      success: true,
      message: '公告创建成功',
      data: { id: result.insertId }
    });
  } catch (error) {
    console.error('创建公告失败:', error);
    res.status(500).json({ success: false, message: '创建公告失败' });
  }
});


router.put('/:id', auth, adminAuth, async (req, res) => {
  try {
    const db = getDB();
    const { id } = req.params;
    let {
      title,
      content,
      type,
      priority,
      status,
      is_pinned,
      publish_time,
      expire_time
    } = req.body;
    
    // 检查公告是否存在
    const [existing] = await db.execute('SELECT id FROM announcements WHERE id = ?', [id]);
    if (existing.length === 0) {
      return res.status(404).json({ success: false, message: '公告不存在' });
    }
    
    const updates = [];
    const params = [];
    
    if (title !== undefined) {
      updates.push('title = ?');
      params.push(title);
    }
    if (content !== undefined) {
      updates.push('content = ?');
      params.push(content);
    }
    if (type !== undefined) {
      updates.push('type = ?');
      params.push(type);
    }
    if (priority !== undefined) {
      updates.push('priority = ?');
      params.push(priority);
    }
    if (status !== undefined) {
      updates.push('status = ?');
      params.push(status);
    }
    if (is_pinned !== undefined) {
      updates.push('is_pinned = ?');
      params.push(is_pinned);
    }
    if (publish_time !== undefined) {
      updates.push('publish_time = ?');
      publish_time = dayjs(publish_time).format('YYYY-MM-DD');
      params.push(publish_time);
    }
    if (expire_time !== undefined) {
      updates.push('expire_time = ?');
      params.push(expire_time);
    }
    
    if (updates.length === 0) {
      return res.status(400).json({ success: false, message: '没有要更新的字段' });
    }
    
    updates.push('updated_at = NOW()');
    params.push(id);
    
    const query = `UPDATE announcements SET ${updates.join(', ')} WHERE id = ?`;
    await db.execute(query, params);
    
    res.json({ success: true, message: '公告更新成功' });
  } catch (error) {
    console.error('更新公告失败:', error);
    res.status(500).json({ success: false, message: '更新公告失败' });
  }
});


router.delete('/:id', auth, adminAuth, async (req, res) => {
  try {
    const db = getDB();
    const { id } = req.params;
    
    // 检查公告是否存在
    const [existing] = await db.execute('SELECT id FROM announcements WHERE id = ?', [id]);
    if (existing.length === 0) {
      return res.status(404).json({ success: false, message: '公告不存在' });
    }
    
    await db.execute('DELETE FROM announcements WHERE id = ?', [id]);
    
    res.json({ success: true, message: '公告删除成功' });
  } catch (error) {
    console.error('删除公告失败:', error);
    res.status(500).json({ success: false, message: '删除公告失败' });
  }
});


router.get('/public/list', async (req, res) => {
  try {
    const db = getDB();
    const { limit = 5 } = req.query;
    const limitValue = Math.max(1, Math.min(50, parseInt(limit)));
    
    const query = `
      SELECT id, title, content, type, priority, publish_time, created_at
      FROM announcements 
      WHERE status = 'published' 
        AND (expire_time IS NULL OR expire_time > NOW())
        AND (publish_time IS NULL OR publish_time <= NOW())
      ORDER BY is_pinned DESC, created_at DESC 
      LIMIT ${limitValue}
    `;
    
    const [announcements] = await db.execute(query, []);
    
    res.json({ success: true, data: announcements });
  } catch (error) {
    console.error('获取公开公告失败:', error);
    res.status(500).json({ success: false, message: '获取公开公告失败' });
  }
});

// 标记公告为已读
router.post('/:id/read', auth, async (req, res) => {
  try {
    const db = getDB();
    const { id } = req.params;
    
    // 检查公告是否存在
    const [existing] = await db.execute('SELECT id FROM announcements WHERE id = ?', [id]);
    if (existing.length === 0) {
      return res.status(404).json({ success: false, message: '公告不存在' });
    }
    
    // 标记为已读
    await db.execute(`
      INSERT INTO user_announcement_reads (user_id, announcement_id, is_read, read_at)
      VALUES (?, ?, TRUE, NOW())
      ON DUPLICATE KEY UPDATE is_read = TRUE, read_at = NOW()
    `, [req.user.id, id]);
    
    res.json({ success: true, message: '已标记为已读' });
  } catch (error) {
    console.error('标记公告已读失败:', error);
    res.status(500).json({ success: false, message: '标记公告已读失败' });
  }
});

// 获取用户未读公告数量
router.get('/unread/count', auth, async (req, res) => {
  try {
    const db = getDB();
    
    const query = `
      SELECT COUNT(*) as unread_count
      FROM announcements a
      LEFT JOIN user_announcement_reads uar ON a.id = uar.announcement_id AND uar.user_id = ?
      WHERE a.status = 'published' 
        AND (a.publish_time IS NULL OR a.publish_time <= NOW())
        AND (a.expire_time IS NULL OR a.expire_time > NOW())
        AND (uar.is_read IS NULL OR uar.is_read = FALSE)
    `;
    
    const [result] = await db.execute(query, [req.user.id]);
    
    res.json({ 
      success: true, 
      data: { 
        unread_count: result[0].unread_count 
      } 
    });
  } catch (error) {
    console.error('获取未读公告数量失败:', error);
    res.status(500).json({ success: false, message: '获取未读公告数量失败' });
  }
});

// 批量标记公告为已读
router.post('/batch/read', auth, async (req, res) => {
  try {
    const db = getDB();
    const { announcement_ids } = req.body;
    
    if (!announcement_ids || !Array.isArray(announcement_ids) || announcement_ids.length === 0) {
      return res.status(400).json({ success: false, message: '请提供有效的公告ID列表' });
    }
    
    // 批量标记为已读
    const values = announcement_ids.map(() => '(?, ?, TRUE, NOW())').join(', ');
    const params = announcement_ids.flatMap(id => [req.user.id, id]);
    
    await db.execute(`
      INSERT INTO user_announcement_reads (user_id, announcement_id, is_read, read_at)
      VALUES ${values}
      ON DUPLICATE KEY UPDATE is_read = TRUE, read_at = NOW()
    `, params);
    
    res.json({ success: true, message: '批量标记已读成功' });
  } catch (error) {
    console.error('批量标记公告已读失败:', error);
    res.status(500).json({ success: false, message: '批量标记公告已读失败' });
  }
});

module.exports = router;