reports.js

Go to the documentation of this file.

/**
 * @file reports.js
 * @module routes/reports
 * @description Business intelligence and reporting endpoints for RMM-PSA platform.
 * Provides financial, operational, and customer analytics reports with multi-tenant support.
 * All endpoints require authentication and automatically filter by tenant context.
 * @requires express
 * @requires middleware/auth
 * @requires middleware/tenant
 * @requires services/db
 * @author RMM-PSA Development Team
 * @copyright 2026 RMM-PSA Platform
 * @license Proprietary
 */
 
/**
 * @apiDefine Reports Reports
 * Business intelligence and analytics endpoints for financial, operational, and customer reports
 */
 
const express = require('express');
const router = express.Router();
const authenticateToken = require('../middleware/auth');
const { setTenantContext, getTenantFilter } = require('../middleware/tenant');
const pool = require('../services/db');
 
// Apply authentication and tenant context to all routes
router.use(authenticateToken, setTenantContext);
 
/**
 * @api {get} /api/reports/revenue Get revenue report
 * @apiName GetRevenueReport
 * @apiGroup Reports
 * @apiDescription Get monthly revenue statistics for the last 12 months. Shows total invoices,
 * total revenue, paid revenue, and unpaid revenue by month. Automatically filtered by tenant.
 * @apiSuccess {Array} revenue Array of monthly revenue objects
 * @apiSuccess {string} revenue.month Month in YYYY-MM format
 * @apiSuccess {number} revenue.invoice_count Total invoices issued in month
 * @apiSuccess {number} revenue.total_revenue Total revenue for month (all invoices)
 * @apiSuccess {number} revenue.paid_revenue Revenue from paid invoices
 * @apiSuccess {number} revenue.unpaid_revenue Revenue from unpaid invoices
 * @apiError {string} error (500) Database query failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/reports/revenue \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   [
 *     {
 *       "month": "2026-03",
 *       "invoice_count": 25,
 *       "total_revenue": 45000.00,
 *       "paid_revenue": 38000.00,
 *       "unpaid_revenue": 7000.00
 *     },
 *     {
 *       "month": "2026-02",
 *       "invoice_count": 22,
 *       "total_revenue": 41000.00,
 *       "paid_revenue": 39500.00,
 *       "unpaid_revenue": 1500.00
 *     }
 *   ]
 */
router.get('/revenue', async (req, res) => {
  try {
    const tenantFilter = getTenantFilter(req, 'i');
    const whereClause = tenantFilter.clause ? `${tenantFilter.clause} AND` : '';
    const params = [...tenantFilter.params];
    
    const result = await pool.query(`
      SELECT 
        TO_CHAR(issued_date, 'YYYY-MM') as month,
        COUNT(*) FILTER (WHERE refund_status != 'full') as invoice_count,
        SUM(CASE WHEN refund_status != 'full' THEN (amount - COALESCE(amount_refunded, 0)) ELSE 0 END) as total_revenue,
        SUM(CASE WHEN status = 'paid' AND refund_status != 'full' THEN (amount - COALESCE(amount_refunded, 0)) ELSE 0 END) as paid_revenue,
        SUM(CASE WHEN status != 'paid' AND refund_status != 'full' THEN (amount - COALESCE(amount_refunded, 0)) ELSE 0 END) as unpaid_revenue
      FROM invoices i
      WHERE ${whereClause} issued_date >= NOW() - INTERVAL '12 months' 
        AND status != 'void' 
        AND (refund_status IS NULL OR refund_status != 'full')
      GROUP BY month
      ORDER BY month DESC
    `, params);
    res.json(result.rows);
  } catch (err) {
    console.error('Revenue report error:', err);
    res.status(500).json({ error: err.message });
  }
});
 
/**
 * @api {get} /api/reports/tickets Get ticket statistics
 * @apiName GetTicketReport
 * @apiGroup Reports
 * @apiDescription Get comprehensive ticket statistics including counts by status, priority,
 * and monthly trends (last 12 months). Useful for operational dashboards and performance metrics.
 * @apiSuccess {Array} byStatus Ticket counts grouped by status
 * @apiSuccess {string} byStatus.status Ticket status (open, in_progress, closed, etc)
 * @apiSuccess {number} byStatus.count Number of tickets in this status
 * @apiSuccess {Array} byPriority Ticket counts grouped by priority
 * @apiSuccess {string} byPriority.priority Priority level (low, medium, high, critical)
 * @apiSuccess {number} byPriority.count Number of tickets at this priority
 * @apiSuccess {Array} monthlyTrend Monthly ticket creation and closure trends
 * @apiSuccess {string} monthlyTrend.month Month in YYYY-MM format
 * @apiSuccess {number} monthlyTrend.total Total tickets created in month
 * @apiSuccess {number} monthlyTrend.closed Tickets closed in month
 * @apiError {string} error (500) Database query failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/reports/tickets \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "byStatus": [
 *       {"status": "open", "count": 15},
 *       {"status": "in_progress", "count": 8},
 *       {"status": "closed", "count": 142}
 *     ],
 *     "byPriority": [
 *       {"priority": "low", "count": 45},
 *       {"priority": "medium", "count": 78},
 *       {"priority": "high", "count": 35},
 *       {"priority": "critical", "count": 7}
 *     ],
 *     "monthlyTrend": [
 *       {"month": "2026-03", "total": 18, "closed": 12},
 *       {"month": "2026-02", "total": 22, "closed": 19}
 *     ]
 *   }
 */
router.get('/tickets', async (req, res) => {
  try {
    const tenantFilter = getTenantFilter(req, 't');
    const whereClause = tenantFilter.clause ? `WHERE ${tenantFilter.clause}` : '';
    const params = [...tenantFilter.params];
    
    // Get counts by status
    const statusResult = await pool.query(`
      SELECT status, COUNT(*) as count
      FROM tickets t
      ${whereClause}
      GROUP BY status
    `, params);
    
    // Get counts by priority
    const priorityResult = await pool.query(`
      SELECT priority, COUNT(*) as count
      FROM tickets t
      ${whereClause}
      GROUP BY priority
    `, params);
    
    // Get monthly ticket trends
    const trendResult = await pool.query(`
      SELECT 
        TO_CHAR(created_at, 'YYYY-MM') as month,
        COUNT(*) as total,
        COUNT(*) FILTER (WHERE status = 'closed') as closed
      FROM tickets t
      ${whereClause ? whereClause + ' AND' : 'WHERE'} created_at >= NOW() - INTERVAL '12 months'
      GROUP BY month
      ORDER BY month DESC
    `, params);
    
    res.json({
      byStatus: statusResult.rows,
      byPriority: priorityResult.rows,
      monthlyTrend: trendResult.rows
    });
  } catch (err) {
    console.error('Ticket stats error:', err);
    res.status(500).json({ error: err.message });
  }
});
 
/**
 * @api {get} /api/reports/customers Get customer report
 * @apiName GetCustomerReport
 * @apiGroup Reports
 * @apiDescription Get top customers ranked by revenue and ticket count. Shows top 10 customers
 * in each category with aggregate metrics. Useful for identifying VIP customers and high-support accounts.
 * @apiSuccess {Array} topByRevenue Top 10 customers by total revenue
 * @apiSuccess {UUID} topByRevenue.customer_id Customer ID
 * @apiSuccess {string} topByRevenue.customer_name Customer name
 * @apiSuccess {number} topByRevenue.invoice_count Number of invoices (excluding void)
 * @apiSuccess {number} topByRevenue.total_revenue Total revenue from customer
 * @apiSuccess {Array} topByTickets Top 10 customers by ticket count
 * @apiSuccess {UUID} topByTickets.customer_id Customer ID
 * @apiSuccess {string} topByTickets.customer_name Customer name
 * @apiSuccess {number} topByTickets.ticket_count Total tickets created
 * @apiSuccess {number} topByTickets.open_tickets Currently open tickets
 * @apiError {string} error (500) Database query failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/reports/customers \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "topByRevenue": [
 *       {
 *         "customer_id": "123e4567-e89b-12d3-a456-426614174000",
 *         "customer_name": "Acme Corp",
 *         "invoice_count": 24,
 *         "total_revenue": 125000.00
 *       }
 *     ],
 *     "topByTickets": [
 *       {
 *         "customer_id": "456e7890-ab12-34cd-5678-901234567890",
 *         "customer_name": "TechStart Inc",
 *         "ticket_count": 47,
 *         "open_tickets": 5
 *       }
 *     ]
 *   }
 */
router.get('/customers', async (req, res) => {
  try {
    const tenantFilter = getTenantFilter(req, 'c');
    const whereClause = tenantFilter.clause ? `WHERE ${tenantFilter.clause}` : '';
    const params = [...tenantFilter.params];
    
    // Top customers by revenue
    const revenueResult = await pool.query(`
      SELECT 
        c.customer_id,
        c.name as customer_name,
        COUNT(DISTINCT i.invoice_id) FILTER (WHERE i.status != 'void' AND (i.refund_status IS NULL OR i.refund_status != 'full')) as invoice_count,
        COALESCE(SUM(CASE WHEN i.status != 'void' AND (i.refund_status IS NULL OR i.refund_status != 'full') 
          THEN (i.amount - COALESCE(i.amount_refunded, 0)) ELSE 0 END), 0) as total_revenue
      FROM customers c
      LEFT JOIN invoices i ON c.customer_id = i.customer_id AND i.tenant_id = c.tenant_id
      ${whereClause}
      GROUP BY c.customer_id, c.name
      ORDER BY total_revenue DESC NULLS LAST
      LIMIT 10
    `, params);
    
    // Top customers by ticket count
    const ticketResult = await pool.query(`
      SELECT 
        c.customer_id,
        c.name as customer_name,
        COUNT(t.ticket_id) as ticket_count,
        COUNT(t.ticket_id) FILTER (WHERE t.status = 'open') as open_tickets
      FROM customers c
      LEFT JOIN tickets t ON c.customer_id = t.customer_id AND t.tenant_id = c.tenant_id
      ${whereClause}
      GROUP BY c.customer_id, c.name
      ORDER BY ticket_count DESC
      LIMIT 10
    `, params);
    
    res.json({
      topByRevenue: revenueResult.rows,
      topByTickets: ticketResult.rows
    });
  } catch (err) {
    console.error('Customer report error:', err);
    res.status(500).json({ error: err.message });
  }
});
 
/**
 * @api {get} /api/reports/agents Get agent status report
 * @apiName GetAgentReport
 * @apiGroup Reports
 * @apiDescription Get comprehensive agent monitoring report showing status, last seen time,
 * assigned tickets, and status summary. Useful for workforce management and workload balancing.
 * @apiSuccess {Array} agents Array of agent detail objects
 * @apiSuccess {UUID} agents.agent_id Agent ID
 * @apiSuccess {string} agents.agent_name Agent name
 * @apiSuccess {string} agents.status Agent status (online, offline, away, busy)
 * @apiSuccess {DateTime} agents.last_seen Last heartbeat timestamp
 * @apiSuccess {string} agents.tenant_name Tenant name
 * @apiSuccess {number} agents.assigned_tickets Total tickets assigned to agent
 * @apiSuccess {number} agents.open_tickets Currently open tickets assigned to agent
 * @apiSuccess {Array} statusSummary Summary counts by status
 * @apiSuccess {string} statusSummary.status Status type
 * @apiSuccess {number} statusSummary.count Number of agents in this status
 * @apiError {string} error (500) Database query failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/reports/agents \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "agents": [
 *       {
 *         "agent_id": "123e4567-e89b-12d3-a456-426614174000",
 *         "agent_name": "Desktop-WIN10-001",
 *         "status": "online",
 *         "last_seen": "2026-03-12T10:45:00.000Z",
 *         "tenant_name": "Acme Corp",
 *         "assigned_tickets": 5,
 *         "open_tickets": 3
 *       }
 *     ],
 *     "statusSummary": [
 *       {"status": "online", "count": 145},
 *       {"status": "offline", "count": 23},
 *       {"status": "away", "count": 8}
 *     ]
 *   }
 */
router.get('/agents', async (req, res) => {
  try {
    const tenantFilter = getTenantFilter(req, 'a');
    const whereClause = tenantFilter.clause ? `WHERE ${tenantFilter.clause}` : '';
    const params = [...tenantFilter.params];
    
    const result = await pool.query(`
      SELECT 
        a.agent_id,
        a.name as agent_name,
        a.status,
        a.last_seen,
        t.name as tenant_name,
        COUNT(DISTINCT tk.ticket_id) as assigned_tickets,
        COUNT(DISTINCT tk.ticket_id) FILTER (WHERE tk.status = 'open') as open_tickets
      FROM agents a
      LEFT JOIN tenants t ON a.tenant_id = t.tenant_id
      LEFT JOIN tickets tk ON a.agent_id = tk.assigned_to AND a.tenant_id = tk.tenant_id
      ${whereClause}
      GROUP BY a.agent_id, a.name, a.status, a.last_seen, t.name
      ORDER BY a.status DESC, a.name
    `, params);
    
    // Get status summary
    const statusSummary = await pool.query(`
      SELECT status, COUNT(*) as count
      FROM agents a
      ${whereClause}
      GROUP BY status
    `, params);
    
    res.json({
      agents: result.rows,
      statusSummary: statusSummary.rows
    });
  } catch (err) {
    console.error('Agent report error:', err);
    res.status(500).json({ error: err.message });
  }
});
 
/**
 * @api {get} /api/reports/invoice-aging Get invoice aging report
 * @apiName GetInvoiceAgingReport
 * @apiGroup Reports
 * @apiDescription Get detailed invoice aging report showing unpaid invoices with days overdue.
 * Includes aging bucket summary (current, 1-30, 31-60, 61-90, 90+ days). Critical for
 * accounts receivable management and cash flow forecasting.
 * @apiSuccess {Array} invoices Array of unpaid/overdue invoice objects
 * @apiSuccess {number} invoices.invoice_id Invoice ID
 * @apiSuccess {string} invoices.invoice_number Formatted invoice number (INV-000001)
 * @apiSuccess {string} invoices.customer_name Customer name
 * @apiSuccess {Date} invoices.issued_date Invoice issue date
 * @apiSuccess {Date} invoices.due_date Invoice due date
 * @apiSuccess {number} invoices.total_amount Invoice amount
 * @apiSuccess {string} invoices.payment_status Payment status
 * @apiSuccess {number} invoices.days_overdue Days overdue (0 if not yet due)
 * @apiSuccess {object} summary Aging bucket summary
 * @apiSuccess {object} summary.current Current invoices (not yet due)
 * @apiSuccess {number} summary.current.count Number of invoices
 * @apiSuccess {number} summary.current.total Total amount
 * @apiSuccess {Object} summary.overdue1_30 Invoices 1-30 days overdue
 * @apiSuccess {Object} summary.overdue31_60 Invoices 31-60 days overdue
 * @apiSuccess {Object} summary.overdue61_90 Invoices 61-90 days overdue
 * @apiSuccess {Object} summary.overdue90plus Invoices over 90 days overdue
 * @apiError {string} error (500) Database query failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/reports/invoice-aging \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "invoices": [
 *       {
 *         "invoice_id": 1234,
 *         "invoice_number": "INV-001234",
 *         "customer_name": "Acme Corp",
 *         "issued_date": "2026-01-15",
 *         "due_date": "2026-02-15",
 *         "total_amount": 5000.00,
 *         "payment_status": "unpaid",
 *         "days_overdue": 25
 *       }
 *     ],
 *     "summary": {
 *       "current": {"count": 8, "total": 12000.00},
 *       "overdue1_30": {"count": 3, "total": 7500.00},
 *       "overdue31_60": {"count": 1, "total": 3000.00},
 *       "overdue61_90": {"count": 0, "total": 0.00},
 *       "overdue90plus": {"count": 1, "total": 15000.00}
 *     }
 *   }
 */
router.get('/invoice-aging', async (req, res) => {
  try {
    const tenantFilter = getTenantFilter(req, 'i');
    const whereClause = tenantFilter.clause ? `${tenantFilter.clause} AND` : '';
    const params = [...tenantFilter.params];
    
    const result = await pool.query(`
      SELECT 
        i.invoice_id,
        'INV-' || LPAD(i.invoice_id::text, 6, '0') as invoice_number,
        c.name as customer_name,
        i.issued_date,
        i.due_date,
        (i.amount - COALESCE(i.amount_refunded, 0)) as total_amount,
        i.status as payment_status,
        i.refund_status,
        CASE 
          WHEN i.due_date < CURRENT_DATE AND i.status != 'paid' 
          THEN CURRENT_DATE - i.due_date 
          ELSE 0 
        END as days_overdue
      FROM invoices i
      LEFT JOIN customers c ON i.customer_id = c.customer_id AND i.tenant_id = c.tenant_id
      WHERE ${whereClause} i.status != 'paid' 
        AND i.status != 'void' 
        AND (i.refund_status IS NULL OR i.refund_status != 'full')
      ORDER BY days_overdue DESC, i.due_date
    `, params);
    
    // Calculate aging buckets
    const current = result.rows.filter(r => r.days_overdue === 0);
    const overdue1_30 = result.rows.filter(r => r.days_overdue > 0 && r.days_overdue <= 30);
    const overdue31_60 = result.rows.filter(r => r.days_overdue > 30 && r.days_overdue <= 60);
    const overdue61_90 = result.rows.filter(r => r.days_overdue > 60 && r.days_overdue <= 90);
    const overdue90plus = result.rows.filter(r => r.days_overdue > 90);
    
    res.json({
      invoices: result.rows,
      summary: {
        current: { count: current.length, total: current.reduce((sum, i) => sum + parseFloat(i.total_amount || 0), 0) },
        overdue1_30: { count: overdue1_30.length, total: overdue1_30.reduce((sum, i) => sum + parseFloat(i.total_amount || 0), 0) },
        overdue31_60: { count: overdue31_60.length, total: overdue31_60.reduce((sum, i) => sum + parseFloat(i.total_amount || 0), 0) },
        overdue61_90: { count: overdue61_90.length, total: overdue61_90.reduce((sum, i) => sum + parseFloat(i.total_amount || 0), 0) },
        overdue90plus: { count: overdue90plus.length, total: overdue90plus.reduce((sum, i) => sum + parseFloat(i.total_amount || 0), 0) }
      }
    });
  } catch (err) {
    console.error('Invoice aging error:', err);
    res.status(500).json({ error: err.message });
  }
});
 
module.exports = router;