scrum-manager/server/routes/export.js

import { Router } from 'express';
import pool from '../db.js';

const router = Router();

// Helper: escape CSV field
function csvEscape(val) {
    if (val == null) return '';
    const str = String(val);
    if (str.includes(',') || str.includes('"') || str.includes('\n')) {
        return `"${str.replace(/"/g, '""')}"`;
    }
    return str;
}

// Helper: convert rows to CSV string
function toCsv(headers, rows) {
    const headerLine = headers.map(csvEscape).join(',');
    const dataLines = rows.map(row => headers.map(h => csvEscape(row[h])).join(','));
    return [headerLine, ...dataLines].join('\n');
}

// Helper: build month filter clause for a date column
function monthFilter(column, month) {
    if (!month || !/^\d{4}-\d{2}$/.test(month)) return { clause: '', params: [] };
    const [year, mon] = month.split('-');
    const start = `${year}-${mon}-01`;
    // Last day of month
    const nextMonth = parseInt(mon) === 12 ? `${parseInt(year) + 1}-01-01` : `${year}-${String(parseInt(mon) + 1).padStart(2, '0')}-01`;
    return { clause: ` AND ${column} >= ? AND ${column} < ?`, params: [start, nextMonth] };
}

// GET /api/export/tasks?month=YYYY-MM
router.get('/tasks', async (req, res) => {
    try {
        const { month } = req.query;
        const mf = monthFilter('t.due_date', month);

        const [rows] = await pool.query(`
            SELECT t.id, t.title, t.description, t.status, t.priority,
                   t.due_date, t.created_at,
                   a.name AS assignee_name, a.email AS assignee_email,
                   r.name AS reporter_name,
                   GROUP_CONCAT(tt.tag SEPARATOR '; ') AS tags
            FROM tasks t
            LEFT JOIN users a ON t.assignee_id = a.id
            LEFT JOIN users r ON t.reporter_id = r.id
            LEFT JOIN task_tags tt ON tt.task_id = t.id
            WHERE 1=1 ${mf.clause}
            GROUP BY t.id
            ORDER BY t.created_at DESC
        `, mf.params);

        const csv = toCsv(
            ['id', 'title', 'description', 'status', 'priority', 'due_date', 'created_at', 'assignee_name', 'assignee_email', 'reporter_name', 'tags'],
            rows
        );

        const filename = month ? `tasks_${month}.csv` : 'tasks_all.csv';
        res.setHeader('Content-Type', 'text/csv');
        res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
        res.send(csv);
    } catch (err) {
        console.error('Export tasks error:', err);
        res.status(500).json({ error: 'Export failed' });
    }
});

// GET /api/export/users?month=YYYY-MM
router.get('/users', async (req, res) => {
    try {
        const { month } = req.query;
        const mf = monthFilter('t.due_date', month);

        const [rows] = await pool.query(`
            SELECT u.id, u.name, u.email, u.role, u.dept,
                   COUNT(t.id) AS total_tasks,
                   SUM(CASE WHEN t.status = 'done' THEN 1 ELSE 0 END) AS completed_tasks,
                   SUM(CASE WHEN t.status != 'done' AND t.due_date < CURDATE() THEN 1 ELSE 0 END) AS overdue_tasks
            FROM users u
            LEFT JOIN tasks t ON t.assignee_id = u.id ${mf.clause ? 'AND' + mf.clause.replace(' AND', '') : ''}
            GROUP BY u.id
            ORDER BY u.name
        `, mf.params);

        const csv = toCsv(
            ['id', 'name', 'email', 'role', 'dept', 'total_tasks', 'completed_tasks', 'overdue_tasks'],
            rows
        );

        const filename = month ? `users_${month}.csv` : 'users_all.csv';
        res.setHeader('Content-Type', 'text/csv');
        res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
        res.send(csv);
    } catch (err) {
        console.error('Export users error:', err);
        res.status(500).json({ error: 'Export failed' });
    }
});

// GET /api/export/activities?month=YYYY-MM
router.get('/activities', async (req, res) => {
    try {
        const { month } = req.query;
        const mf = monthFilter('a.timestamp', month);

        const [rows] = await pool.query(`
            SELECT a.id, a.text AS activity, a.timestamp,
                   t.title AS task_title, t.status AS task_status
            FROM activities a
            LEFT JOIN tasks t ON a.task_id = t.id
            WHERE 1=1 ${mf.clause}
            ORDER BY a.timestamp DESC
        `, mf.params);

        const csv = toCsv(
            ['id', 'activity', 'timestamp', 'task_title', 'task_status'],
            rows
        );

        const filename = month ? `activities_${month}.csv` : 'activities_all.csv';
        res.setHeader('Content-Type', 'text/csv');
        res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
        res.send(csv);
    } catch (err) {
        console.error('Export activities error:', err);
        res.status(500).json({ error: 'Export failed' });
    }
});

export default router;