Backend Endpoints for Real Analytics Data

1. Dashboard Analytics Routes

// backend/src/routes/dashboard.routes.ts
import { Router, Request, Response } from 'express';
import { authenticate } from '../middleware/auth';
import { requirePermission } from '../middleware/permissions';
import { DashboardController } from '../controllers/dashboard.controller';

const router = Router();
const dashboardController = new DashboardController();

// Metrics endpoints
router.get(
  '/metrics',
  authenticate,
  requirePermission('view_dashboard'),
  (req, res) => dashboardController.getMetrics(req, res)
);

router.get(
  '/metrics/budget',
  authenticate,
  requirePermission('view_dashboard'),
  (req, res) => dashboardController.getBudgetMetrics(req, res)
);

router.get(
  '/metrics/schedule',
  authenticate,
  requirePermission('view_dashboard'),
  (req, res) => dashboardController.getScheduleMetrics(req, res)
);

router.get(
  '/metrics/risk',
  authenticate,
  requirePermission('view_dashboard'),
  (req, res) => dashboardController.getRiskMetrics(req, res)
);

// Analytics endpoints
router.get(
  '/analytics/budget-trend',
  authenticate,
  requirePermission('view_dashboard'),
  (req, res) => dashboardController.getBudgetTrend(req, res)
);

router.get(
  '/analytics/project-performance',
  authenticate,
  requirePermission('view_dashboard'),
  (req, res) => dashboardController.getProjectPerformance(req, res)
);

router.get(
  '/analytics/win-loss',
  authenticate,
  requirePermission('view_dashboard'),
  (req, res) => dashboardController.getWinLossAnalytics(req, res)
);

// Alerts endpoint
router.get(
  '/alerts',
  authenticate,
  requirePermission('view_dashboard'),
  (req, res) => dashboardController.getAlerts(req, res)
);

// Projects endpoint
router.get(
  '/projects',
  authenticate,
  requirePermission('view_projects'),
  (req, res) => dashboardController.getProjects(req, res)
);

export default router;

2. Dashboard Controller

// backend/src/controllers/dashboard.controller.ts
import { Request, Response } from 'express';
import { supabase } from '../config/supabase';
import { calculateVariance, formatCurrency } from '../utils/calculations';

export class DashboardController {
  async getMetrics(req: Request, res: Response) {
    try {
      const orgId = req.user?.org_id;

      // Get budget metrics
      const budgetResult = await supabase
        .from('budgets')
        .select('budget_amount, spent_amount')
        .eq('org_id', orgId)
        .eq('status', 'active');

      // Get schedule metrics
      const scheduleResult = await supabase
        .from('schedules')
        .select('baseline_duration, actual_duration, status')
        .eq('org_id', orgId);

      // Get risk metrics
      const riskResult = await supabase
        .from('risks')
        .select('probability, impact, status')
        .eq('org_id', orgId)
        .eq('status', 'active');

      // Get project count
      const projectResult = await supabase
        .from('projects')
        .select('id')
        .eq('org_id', orgId);

      const budgets = budgetResult.data || [];
      const schedules = scheduleResult.data || [];
      const risks = riskResult.data || [];
      const projects = projectResult.data || [];

      // Calculate aggregates
      const totalBudget = budgets.reduce((sum, b) => sum + (b.budget_amount || 0), 0);
      const totalSpent = budgets.reduce((sum, b) => sum + (b.spent_amount || 0), 0);
      const budgetVariance = calculateVariance(totalBudget, totalSpent);

      const onTimeProjects = schedules.filter(
        (s) => !s.actual_duration || s.actual_duration <= s.baseline_duration
      ).length;
      const scheduleAdherence = schedules.length > 0
        ? (onTimeProjects / schedules.length) * 100
        : 0;

      const activeRisks = risks.filter((r) => r.status === 'active').length;
      const avgRiskScore = risks.length > 0
        ? risks.reduce((sum, r) => sum + (r.probability * r.impact), 0) / risks.length
        : 0;

      res.json({
        success: true,
        data: {
          budget: {
            total: formatCurrency(totalBudget),
            spent: formatCurrency(totalSpent),
            variance: budgetVariance,
            percentSpent: totalBudget > 0 ? (totalSpent / totalBudget) * 100 : 0,
          },
          schedule: {
            onTimePercentage: scheduleAdherence,
            totalProjects: projects.length,
            atRiskProjects: schedules.filter((s) => s.actual_duration > s.baseline_duration).length,
          },
          risk: {
            activeRisks,
            averageScore: avgRiskScore,
            highRisks: risks.filter((r) => r.probability * r.impact > 15).length,
          },
          projects: {
            total: projects.length,
            active: projects.length, // Adjust based on actual status field
          },
        },
      });
    } catch (error) {
      res.status(500).json({ success: false, error: error.message });
    }
  }

  async getBudgetMetrics(req: Request, res: Response) {
    try {
      const orgId = req.user?.org_id;
      const { projectId } = req.query;

      let query = supabase
        .from('budgets')
        .select('id, project_id, budget_amount, spent_amount, forecasted_amount, status')
        .eq('org_id', orgId);

      if (projectId) {
        query = query.eq('project_id', projectId);
      }

      const result = await query;
      const budgets = result.data || [];

      const metrics = {
        totalBudget: budgets.reduce((sum, b) => sum + (b.budget_amount || 0), 0),
        totalSpent: budgets.reduce((sum, b) => sum + (b.spent_amount || 0), 0),
        totalForecasted: budgets.reduce((sum, b) => sum + (b.forecasted_amount || 0), 0),
        byProject: budgets.map((b) => ({
          projectId: b.project_id,
          budget: b.budget_amount,
          spent: b.spent_amount,
          forecasted: b.forecasted_amount,
          variance: calculateVariance(b.budget_amount, b.spent_amount),
          percentSpent: b.budget_amount > 0 ? (b.spent_amount / b.budget_amount) * 100 : 0,
        })),
      };

      res.json({ success: true, data: metrics });
    } catch (error) {
      res.status(500).json({ success: false, error: error.message });
    }
  }

  async getScheduleMetrics(req: Request, res: Response) {
    try {
      const orgId = req.user?.org_id;

      const result = await supabase
        .from('schedules')
        .select(
          'id, project_id, baseline_duration, actual_duration, status, start_date, end_date'
        )
        .eq('org_id', orgId);

      const schedules = result.data || [];

      const metrics = {
        totalProjects: schedules.length,
        onTimeProjects: schedules.filter(
          (s) => !s.actual_duration || s.actual_duration <= s.baseline_duration
        ).length,
        delayedProjects: schedules.filter(
          (s) => s.actual_duration && s.actual_duration > s.baseline_duration
        ).length,
        scheduleAdherence: schedules.length > 0
          ? (schedules.filter(
            (s) => !s.actual_duration || s.actual_duration <= s.baseline_duration
          ).length / schedules.length) * 100
          : 0,
        byProject: schedules.map((s) => ({
          projectId: s.project_id,
          baseline: s.baseline_duration,
          actual: s.actual_duration,
          variance: s.actual_duration ? s.actual_duration - s.baseline_duration : 0,
          status: s.status,
        })),
      };

      res.json({ success: true, data: metrics });
    } catch (error) {
      res.status(500).json({ success: false, error: error.message });
    }
  }

  async getRiskMetrics(req: Request, res: Response) {
    try {
      const orgId = req.user?.org_id;

      const result = await supabase
        .from('risks')
        .select('id, project_id, probability, impact, status, mitigation_status')
        .eq('org_id', orgId);

      const risks = result.data || [];

      const activeRisks = risks.filter((r) => r.status === 'active');
      const mitigatedRisks = risks.filter((r) => r.mitigation_status === 'mitigated');

      const metrics = {
        totalRisks: risks.length,
        activeRisks: activeRisks.length,
        mitigatedRisks: mitigatedRisks.length,
        highRisks: activeRisks.filter((r) => r.probability * r.impact > 15).length,
        mediumRisks: activeRisks.filter(
          (r) => r.probability * r.impact > 8 && r.probability * r.impact <= 15
        ).length,
        lowRisks: activeRisks.filter((r) => r.probability * r.impact <= 8).length,
        averageRiskScore: activeRisks.length > 0
          ? activeRisks.reduce((sum, r) => sum + r.probability * r.impact, 0) / activeRisks.length
          : 0,
      };

      res.json({ success: true, data: metrics });
    } catch (error) {
      res.status(500).json({ success: false, error: error.message });
    }
  }

  async getBudgetTrend(req: Request, res: Response) {
    try {
      const orgId = req.user?.org_id;
      const { months = 12 } = req.query;

      const result = await supabase
        .from('budget_history')
        .select('month, budget_amount, spent_amount, forecasted_amount')
        .eq('org_id', orgId)
        .order('month', { ascending: true })
        .limit(parseInt(months as string));

      const history = result.data || [];

      const trendData = history.map((h) => ({
        month: new Date(h.month).toLocaleDateString('en-US', { month: 'short' }),
        budget: h.budget_amount,
        actual: h.spent_amount,
        forecast: h.forecasted_amount,
      }));

      res.json({ success: true, data: trendData });
    } catch (error) {
      res.status(500).json({ success: false, error: error.message });
    }
  }

  async getProjectPerformance(req: Request, res: Response) {
    try {
      const orgId = req.user?.org_id;

      const result = await supabase
        .from('projects')
        .select(
          `
          id,
          name,
          status,
          budgets(budget_amount, spent_amount),
          schedules(baseline_duration, actual_duration),
          risks(id, status)
        `
        )
        .eq('org_id', orgId);

      const projects = result.data || [];

      const performanceData = projects.map((p) => {
        const budget = p.budgets?.[0];
        const schedule = p.schedules?.[0];
        const risks = p.risks || [];

        return {
          id: p.id,
          name: p.name,
          status: p.status,
          budgetHealth:
            budget && budget.budget_amount > 0
              ? ((budget.spent_amount / budget.budget_amount) * 100).toFixed(1)
              : 'N/A',
          scheduleHealth:
            schedule && schedule.baseline_duration > 0
              ? ((schedule.actual_duration / schedule.baseline_duration) * 100).toFixed(1)