timeline/server/routes/calendar.js

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

const router = express.Router();

router.get('/', (req, res) => {
  const userId = req.user.userId;
  const { year, month } = req.query;

  // Build query based on whether year and month are provided
  let query = 'SELECT date, status, updated_at FROM calendar_marks WHERE user_id = ?';
  const params = [userId];

  // If year and month are provided, filter by that month plus adjacent months
  if (year && month) {
    const yearNum = parseInt(year, 10);
    const monthNum = parseInt(month, 10);

    // Validate year and month
    if (isNaN(yearNum) || isNaN(monthNum) || monthNum < 1 || monthNum > 12) {
      return res.status(400).json({ success: false, error: '无效的年份或月份' });
    }

    // Calculate the date range: previous month to next month
    // This covers all dates that might be visible in the calendar view
    const prevMonth = monthNum === 1 ? 12 : monthNum - 1;
    const prevYear = monthNum === 1 ? yearNum - 1 : yearNum;
    const nextMonth = monthNum === 12 ? 1 : monthNum + 1;
    const nextYear = monthNum === 12 ? yearNum + 1 : yearNum;

    const startDate = `${prevYear}-${String(prevMonth).padStart(2, '0')}-01`;
    // Get the last day of next month
    const lastDayOfNextMonth = new Date(nextYear, nextMonth, 0).getDate();
    const endDate = `${nextYear}-${String(nextMonth).padStart(2, '0')}-${String(lastDayOfNextMonth).padStart(2, '0')}`;

    query += ' AND date >= ? AND date <= ?';
    params.push(startDate, endDate);
  }

  db.all(query, params, (err, rows) => {
    if (err) {
      return res.status(500).json({ success: false, error: '获取数据失败' });
    }

    const markedDates = {};
    rows.forEach((row) => {
      markedDates[row.date] = {
        status: row.status,
        updatedAt: row.updated_at
      };
    });

    res.json({ success: true, markedDates });
  });
});

router.post('/', (req, res) => {
  const userId = req.user.userId;
  const { markedDates, deletedDates } = req.body || {};

  if (!markedDates || typeof markedDates !== 'object') {
    return res.status(400).json({ success: false, error: '无效的数据格式' });
  }

  // Helper function to get current timestamp in ISO format (UTC)
  const getLocalTimestamp = () => {
    return new Date().toISOString();
  };

  const dbOperations = [];

  // Update or insert marked dates
  Object.entries(markedDates).forEach(([date, status]) => {
    dbOperations.push(
      new Promise((resolve, reject) => {
        // First check if this date exists and if status has changed
        db.get(
          'SELECT status FROM calendar_marks WHERE user_id = ? AND date = ?',
          [userId, date],
          (err, row) => {
            if (err) {
              return reject(err);
            }

            // Only update timestamp if status changed or it's a new entry
            const statusChanged = !row || row.status !== status;

            if (statusChanged) {
              // Status changed - update with new timestamp
              const currentTimestamp = getLocalTimestamp();
              db.run(
                `INSERT INTO calendar_marks (user_id, date, status, updated_at)
                 VALUES (?, ?, ?, ?)
                 ON CONFLICT(user_id, date) DO UPDATE SET
                 status = excluded.status,
                 updated_at = excluded.updated_at`,
                [userId, date, status, currentTimestamp],
                (err) => {
                  if (err) reject(err);
                  else resolve();
                }
              );
            } else {
              // Status unchanged - keep existing timestamp
              db.run(
                `INSERT INTO calendar_marks (user_id, date, status, updated_at)
                 VALUES (?, ?, ?, (SELECT updated_at FROM calendar_marks WHERE user_id = ? AND date = ?))
                 ON CONFLICT(user_id, date) DO UPDATE SET
                 status = excluded.status`,
                [userId, date, status, userId, date],
                (err) => {
                  if (err) reject(err);
                  else resolve();
                }
              );
            }
          }
        );
      })
    );
  });

  // Delete explicitly removed dates
  if (deletedDates && Array.isArray(deletedDates) && deletedDates.length > 0) {
    const placeholders = deletedDates.map(() => '?').join(',');
    dbOperations.push(
      new Promise((resolve, reject) => {
        db.run(
          `DELETE FROM calendar_marks WHERE user_id = ? AND date IN (${placeholders})`,
          [userId, ...deletedDates],
          (err) => {
            if (err) reject(err);
            else resolve();
          }
        );
      })
    );
  }

  Promise.all(dbOperations)
    .then(() => {
      res.json({ success: true, message: '数据保存成功' });
    })
    .catch((err) => {
      console.error('保存数据失败:', err);
      res.status(500).json({ success: false, error: '保存数据失败' });
    });
});

module.exports = router;