const express = require('express');
const db = require('../db/models');
const wrapAsync = require('../helpers').wrapAsync;

const router = express.Router();
const { Op } = db.Sequelize;

const scopeByOrganization = (currentUser, fieldName = 'organizationId') => {
  if (currentUser?.app_role?.globalAccess) {
    return {};
  }

  const organizationId = currentUser?.organization?.id || currentUser?.organizationId || currentUser?.organizationsId;

  if (!organizationId) {
    return {};
  }

  return {
    [fieldName]: organizationId,
  };
};

const toNumber = (value) => {
  const parsedValue = Number(value || 0);

  if (Number.isNaN(parsedValue)) {
    return 0;
  }

  return parsedValue;
};

const sumCurrency = async (model, field, currency, where, organizationField = 'organizationId') => {
  const value = await model.sum(field, {
    where: {
      ...scopeByOrganization(where.currentUser, organizationField),
      ...where.filters,
      currency,
    },
  });

  return toNumber(value);
};

const WORKSPACE_ROLES = {
  superAdmin: 'Super Administrator',
  administrator: 'Administrator',
  directorGeneral: 'Director General',
  financeDirector: 'Finance Director',
  procurementLead: 'Procurement Lead',
  complianceAuditLead: 'Compliance and Audit Lead',
  projectDeliveryLead: 'Project Delivery Lead',
};

const formatCurrencyValue = (value, currency) =>
  new Intl.NumberFormat(currency === 'CDF' ? 'fr-CD' : 'en-US', {
    style: 'currency',
    currency,
    maximumFractionDigits: 0,
  }).format(toNumber(value));

const formatMetricValue = (summary, metricKey) => {
  switch (metricKey) {
    case 'approvedBudget':
    case 'committedBudget':
    case 'disbursedBudget':
    case 'budgetVariance':
      return `${formatCurrencyValue(summary[metricKey]?.USD, 'USD')} / ${formatCurrencyValue(summary[metricKey]?.CDF, 'CDF')}`;
    case 'averageProjectProgress':
      return `${toNumber(summary[metricKey])}%`;
    default:
      return `${toNumber(summary[metricKey])}`;
  }
};

const getCollectionCount = (datasets, collectionKey) => {
  const collection = datasets[collectionKey];

  if (Array.isArray(collection)) {
    return collection.length;
  }

  return 0;
};

const workspacePayloadConfigs = {
  [WORKSPACE_ROLES.superAdmin]: {
    summaryMetricKeys: ['activeProjects', 'pendingApprovals', 'openRiskAlerts', 'contractsNearingExpiry', 'unreadNotifications', 'vendorComplianceAlerts'],
    focusCards: [
      {
        key: 'super-admin-approvals',
        title: 'Workflow backlog requiring oversight',
        metricKey: 'pendingApprovals',
        note: 'Pending approvals across the accessible ERP scope that may require escalation or reassignment.',
        href: '/approvals/approvals-list',
      },
      {
        key: 'super-admin-alerts',
        title: 'Open risk exposure',
        metricKey: 'openRiskAlerts',
        note: 'High and critical alerts still unresolved across contracts, vendors, and operational records.',
        href: '/compliance_alerts/compliance_alerts-list',
      },
      {
        key: 'super-admin-projects',
        title: 'Live execution footprint',
        metricKey: 'activeProjects',
        note: 'Projects currently moving through the institution and contributing to workload volume.',
        href: '/projects/projects-list',
      },
      {
        key: 'super-admin-notifications',
        title: 'Unread notices',
        metricKey: 'unreadNotifications',
        note: 'Unread workflow, control, and system notices that may hide operational friction.',
        href: '/notifications/notifications-list',
      },
    ],
    watchlistCards: [
      {
        key: 'super-admin-approval-queue',
        title: 'Escalation approval inbox',
        collectionKey: 'approvalQueue',
        note: 'Oldest pending decisions visible from the platform oversight layer.',
        href: '/approvals/approvals-list',
      },
      {
        key: 'super-admin-risk-watch',
        title: 'Platform red-flag watchlist',
        collectionKey: 'riskPanel',
        note: 'The most severe open alerts currently demanding follow-up.',
        href: '/compliance_alerts/compliance_alerts-list',
      },
      {
        key: 'super-admin-notification-watch',
        title: 'Unread institutional notices',
        collectionKey: 'recentNotifications',
        note: 'Recent notifications that may indicate configuration or workflow issues.',
        href: '/notifications/notifications-list',
      },
    ],
  },
  [WORKSPACE_ROLES.administrator]: {
    summaryMetricKeys: ['pendingApprovals', 'unreadNotifications', 'contractsNearingExpiry', 'openRiskAlerts', 'activeProjects', 'procurementPipeline'],
    focusCards: [
      {
        key: 'admin-approvals',
        title: 'Approvals awaiting operational follow-through',
        metricKey: 'pendingApprovals',
        note: 'Work items that often surface workflow setup gaps, delegation issues, or stalled administrative action.',
        href: '/approvals/approvals-list',
      },
      {
        key: 'admin-notifications',
        title: 'Unread admin-facing notices',
        metricKey: 'unreadNotifications',
        note: 'Recent notifications that can point to user support, assignment, or setup problems.',
        href: '/notifications/notifications-list',
      },
      {
        key: 'admin-contracts',
        title: 'Contracts nearing renewal attention',
        metricKey: 'contractsNearingExpiry',
        note: 'Active contracts nearing expiry and likely to trigger administrative coordination.',
        href: '/contracts/contracts-list',
      },
      {
        key: 'admin-pipeline',
        title: 'Procurement workload in motion',
        metricKey: 'procurementPipeline',
        note: 'Requisitions advancing through review, tendering, or award that can pressure operations teams.',
        href: '/requisitions/requisitions-list',
      },
    ],
    watchlistCards: [
      {
        key: 'admin-approval-queue',
        title: 'Operational approval queue',
        collectionKey: 'approvalQueue',
        note: 'A quick read on which approvals need routing or user follow-up.',
        href: '/approvals/approvals-list',
      },
      {
        key: 'admin-procurement-queue',
        title: 'Live procurement queue',
        collectionKey: 'procurementQueue',
        note: 'Requisitions that may be blocked by setup, documentation, or coordination issues.',
        href: '/requisitions/requisitions-list',
      },
      {
        key: 'admin-notification-watch',
        title: 'Recent administrative notices',
        collectionKey: 'recentNotifications',
        note: 'Recent events to help admins spot support and process issues early.',
        href: '/notifications/notifications-list',
      },
    ],
  },
  [WORKSPACE_ROLES.directorGeneral]: {
    summaryMetricKeys: ['approvedBudget', 'committedBudget', 'disbursedBudget', 'pendingApprovals', 'highRiskProjects', 'contractsNearingExpiry'],
    focusCards: [
      {
        key: 'dg-budget',
        title: 'Approved budget envelope',
        metricKey: 'approvedBudget',
        note: 'Current allocation envelope available for strategic oversight and institutional steering.',
        href: '/allocations/allocations-list',
      },
      {
        key: 'dg-disbursed',
        title: 'Disbursements already released',
        metricKey: 'disbursedBudget',
        note: 'Payments processed so far, useful for comparing commitment and delivery momentum.',
        href: '/payments/payments-list',
      },
      {
        key: 'dg-risk-projects',
        title: 'Projects under elevated risk',
        metricKey: 'highRiskProjects',
        note: 'Projects flagged high or critical risk and likely to require executive attention.',
        href: '/projects/projects-list',
      },
      {
        key: 'dg-contracts',
        title: 'Contracts nearing decision point',
        metricKey: 'contractsNearingExpiry',
        note: 'Active contracts approaching expiry that may affect program continuity or exposure.',
        href: '/contracts/contracts-list',
      },
    ],
    watchlistCards: [
      {
        key: 'dg-contract-watch',
        title: 'Executive contract watchlist',
        collectionKey: 'contractWatchlist',
        note: 'Contracts closest to expiry or requiring strategic follow-up.',
        href: '/contracts/contracts-list',
      },
      {
        key: 'dg-risk-watch',
        title: 'Strategic risk watchlist',
        collectionKey: 'riskPanel',
        note: 'High-severity alerts that can affect institutional delivery credibility.',
        href: '/compliance_alerts/compliance_alerts-list',
      },
      {
        key: 'dg-rollout-watch',
        title: 'Province rollout overview',
        collectionKey: 'provinceRollout',
        note: 'Delivery footprint by province to help spot uneven execution patterns.',
        href: '/projects/projects-list',
      },
    ],
  },
  [WORKSPACE_ROLES.financeDirector]: {
    summaryMetricKeys: ['approvedBudget', 'committedBudget', 'disbursedBudget', 'overduePayments', 'pendingApprovals', 'unreadNotifications'],
    focusCards: [
      {
        key: 'finance-approved',
        title: 'Approved budget base',
        metricKey: 'approvedBudget',
        note: 'The fiscal envelope currently approved and available for expenditure control.',
        href: '/allocations/allocations-list',
      },
      {
        key: 'finance-committed',
        title: 'Committed demand',
        metricKey: 'committedBudget',
        note: 'Requisition value moving toward tender, award, or contract conversion.',
        href: '/requisitions/requisitions-list',
      },
      {
        key: 'finance-disbursed',
        title: 'Disbursed amount',
        metricKey: 'disbursedBudget',
        note: 'Processed payments already released, useful for cash-control tracking.',
        href: '/payments/payments-list',
      },
      {
        key: 'finance-overdue',
        title: 'Aging payment requests',
        metricKey: 'overduePayments',
        note: 'Submitted or approved requests older than 30 days and likely creating vendor pressure.',
        href: '/payment_requests/payment_requests-list',
      },
    ],
    watchlistCards: [
      {
        key: 'finance-payment-watch',
        title: 'Payment control watchlist',
        collectionKey: 'recentNotifications',
        note: 'Recent notices that may affect settlement timing or finance follow-through.',
        href: '/notifications/notifications-list',
      },
      {
        key: 'finance-approval-watch',
        title: 'Approvals blocking spend',
        collectionKey: 'approvalQueue',
        note: 'Pending approvals that can delay commitments, invoicing, or disbursement.',
        href: '/approvals/approvals-list',
      },
      {
        key: 'finance-contract-watch',
        title: 'Contract value watchlist',
        collectionKey: 'topContracts',
        note: 'Largest contracts in scope for exposure review and budget tracking.',
        href: '/contracts/contracts-list',
      },
    ],
  },
  [WORKSPACE_ROLES.procurementLead]: {
    summaryMetricKeys: ['procurementPipeline', 'pendingApprovals', 'contractsNearingExpiry', 'vendorComplianceAlerts', 'openRiskAlerts', 'activeProjects'],
    focusCards: [
      {
        key: 'procurement-pipeline',
        title: 'Live requisition pipeline',
        metricKey: 'procurementPipeline',
        note: 'Requisitions actively progressing through review, tender, and award stages.',
        href: '/requisitions/requisitions-list',
      },
      {
        key: 'procurement-approvals',
        title: 'Approvals slowing procurement',
        metricKey: 'pendingApprovals',
        note: 'Pending workflow actions that can delay conversion from request to tender or contract.',
        href: '/approvals/approvals-list',
      },
      {
        key: 'procurement-contracts',
        title: 'Contracts close to expiry',
        metricKey: 'contractsNearingExpiry',
        note: 'Active contracts approaching expiry and likely to require extension or replacement planning.',
        href: '/contracts/contracts-list',
      },
      {
        key: 'procurement-vendor-alerts',
        title: 'Vendor compliance issues',
        metricKey: 'vendorComplianceAlerts',
        note: 'Open missing-document or expiry alerts that may block award or execution readiness.',
        href: '/compliance_alerts/compliance_alerts-list',
      },
    ],
    watchlistCards: [
      {
        key: 'procurement-queue',
        title: 'Priority procurement queue',
        collectionKey: 'procurementQueue',
        note: 'Newest or most urgent requisitions requiring action from the procurement desk.',
        href: '/requisitions/requisitions-list',
      },
      {
        key: 'procurement-contract-watch',
        title: 'Expiring contract watchlist',
        collectionKey: 'contractWatchlist',
        note: 'Contracts most likely to create continuity or sourcing pressure soon.',
        href: '/contracts/contracts-list',
      },
      {
        key: 'procurement-approval-watch',
        title: 'Approval blockers to clear',
        collectionKey: 'approvalQueue',
        note: 'Pending approvals tied to the demand-to-award flow.',
        href: '/approvals/approvals-list',
      },
    ],
  },
  [WORKSPACE_ROLES.complianceAuditLead]: {
    summaryMetricKeys: ['openRiskAlerts', 'vendorComplianceAlerts', 'contractsNearingExpiry', 'pendingApprovals', 'overduePayments', 'unreadNotifications'],
    focusCards: [
      {
        key: 'compliance-risks',
        title: 'Open control alerts',
        metricKey: 'openRiskAlerts',
        note: 'High and critical alerts requiring audit or compliance follow-up.',
        href: '/compliance_alerts/compliance_alerts-list',
      },
      {
        key: 'compliance-vendors',
        title: 'Vendor evidence gaps',
        metricKey: 'vendorComplianceAlerts',
        note: 'Vendor-related missing-document and expiry issues that can undermine compliance posture.',
        href: '/vendors/vendors-list',
      },
      {
        key: 'compliance-contracts',
        title: 'Contracts nearing obligation review',
        metricKey: 'contractsNearingExpiry',
        note: 'Expiring contracts that may require evidence review, amendments, or closure checks.',
        href: '/contracts/contracts-list',
      },
      {
        key: 'compliance-payments',
        title: 'Aging payment exposure',
        metricKey: 'overduePayments',
        note: 'Older payment requests that can indicate weak controls or missing approvals.',
        href: '/payment_requests/payment_requests-list',
      },
    ],
    watchlistCards: [
      {
        key: 'compliance-risk-watch',
        title: 'Highest-severity red flags',
        collectionKey: 'riskPanel',
        note: 'Open alerts with the strongest likelihood of control failure or audit finding.',
        href: '/compliance_alerts/compliance_alerts-list',
      },
      {
        key: 'compliance-approval-watch',
        title: 'Approvals with control exposure',
        collectionKey: 'approvalQueue',
        note: 'Pending approvals where evidence, delegation, or routing should be verified.',
        href: '/approvals/approvals-list',
      },
      {
        key: 'compliance-notification-watch',
        title: 'Recent control notices',
        collectionKey: 'recentNotifications',
        note: 'Notifications that can help trace exceptions and follow-up points.',
        href: '/notifications/notifications-list',
      },
    ],
  },
  [WORKSPACE_ROLES.projectDeliveryLead]: {
    summaryMetricKeys: ['activeProjects', 'averageProjectProgress', 'highRiskProjects', 'pendingApprovals', 'overduePayments', 'unreadNotifications'],
    focusCards: [
      {
        key: 'delivery-projects',
        title: 'Projects in execution',
        metricKey: 'activeProjects',
        note: 'Active projects currently moving through implementation and field follow-through.',
        href: '/projects/projects-list',
      },
      {
        key: 'delivery-progress',
        title: 'Average execution progress',
        metricKey: 'averageProjectProgress',
        note: 'Average completion across approved and active projects in the accessible delivery scope.',
        href: '/projects/projects-list',
      },
      {
        key: 'delivery-risks',
        title: 'Projects under elevated risk',
        metricKey: 'highRiskProjects',
        note: 'Projects marked high or critical risk and likely to affect delivery commitments.',
        href: '/projects/projects-list',
      },
      {
        key: 'delivery-payments',
        title: 'Payment requests creating delivery lag',
        metricKey: 'overduePayments',
        note: 'Aging payment requests that can block contractors, milestones, or field mobilization.',
        href: '/payment_requests/payment_requests-list',
      },
    ],
    watchlistCards: [
      {
        key: 'delivery-rollout-watch',
        title: 'Province rollout snapshot',
        collectionKey: 'provinceRollout',
        note: 'Provincial delivery concentration and completion spread across the current portfolio.',
        href: '/projects/projects-list',
      },
      {
        key: 'delivery-risk-watch',
        title: 'Delivery risk watchlist',
        collectionKey: 'riskPanel',
        note: 'Open alerts most likely to disrupt project execution.',
        href: '/compliance_alerts/compliance_alerts-list',
      },
      {
        key: 'delivery-approval-watch',
        title: 'Approvals affecting execution',
        collectionKey: 'approvalQueue',
        note: 'Pending approvals that can delay works, procurement, or milestone completion.',
        href: '/approvals/approvals-list',
      },
    ],
  },
};

const buildWorkspacePayload = (roleName, summary, datasets) => {
  const roleConfig = workspacePayloadConfigs[roleName] || workspacePayloadConfigs[WORKSPACE_ROLES.projectDeliveryLead];

  return {
    roleName: roleName || WORKSPACE_ROLES.projectDeliveryLead,
    summaryMetricKeys: roleConfig.summaryMetricKeys,
    focusCards: roleConfig.focusCards.map((card) => ({
      key: card.key,
      title: card.title,
      value: formatMetricValue(summary, card.metricKey),
      note: card.note,
      href: card.href,
    })),
    watchlistCards: roleConfig.watchlistCards.map((card) => ({
      key: card.key,
      title: card.title,
      count: getCollectionCount(datasets, card.collectionKey),
      note: card.note,
      href: card.href,
    })),
  };
};

router.get(
  '/',
  wrapAsync(async (req, res) => {
    const currentUser = req.currentUser;
    const now = new Date();
    const contractExpiryWindow = new Date(now);
    const overduePaymentThreshold = new Date(now);

    contractExpiryWindow.setDate(contractExpiryWindow.getDate() + 60);
    overduePaymentThreshold.setDate(overduePaymentThreshold.getDate() - 30);

    const allocationFilters = {
      currentUser,
      filters: {
        status: {
          [Op.in]: ['approved', 'active'],
        },
      },
    };

    const requisitionFilters = {
      ...scopeByOrganization(currentUser),
      status: {
        [Op.in]: ['submitted', 'under_review', 'approved', 'in_tender', 'awarded', 'converted_to_contract'],
      },
    };

    const paymentFilters = {
      ...scopeByOrganization(currentUser),
      status: 'processed',
    };

    const [
      approvedBudgetUSD,
      approvedBudgetCDF,
      committedBudgetUSD,
      committedBudgetCDF,
      disbursedBudgetUSD,
      disbursedBudgetCDF,
      activeProjects,
      procurementPipeline,
      pendingApprovals,
      contractsNearingExpiry,
      overduePayments,
      vendorComplianceAlerts,
      openRiskAlerts,
      unreadNotifications,
      averageProjectProgress,
      highRiskProjects,
      topContracts,
      contractWatchlist,
      approvalQueue,
      procurementQueue,
      riskPanel,
      rolloutProjects,
      recentNotifications,
    ] = await Promise.all([
      sumCurrency(db.allocations, 'amount', 'USD', allocationFilters, 'organizationsId'),
      sumCurrency(db.allocations, 'amount', 'CDF', allocationFilters, 'organizationsId'),
      sumCurrency(db.requisitions, 'estimated_amount', 'USD', { currentUser, filters: requisitionFilters }),
      sumCurrency(db.requisitions, 'estimated_amount', 'CDF', { currentUser, filters: requisitionFilters }),
      sumCurrency(db.payments, 'amount', 'USD', { currentUser, filters: paymentFilters }),
      sumCurrency(db.payments, 'amount', 'CDF', { currentUser, filters: paymentFilters }),
      db.projects.count({
        where: {
          ...scopeByOrganization(currentUser),
          status: 'active',
        },
      }),
      db.requisitions.count({
        where: {
          ...scopeByOrganization(currentUser),
          status: {
            [Op.in]: ['submitted', 'under_review', 'approved', 'in_tender', 'awarded'],
          },
        },
      }),
      db.approvals.count({
        where: {
          ...scopeByOrganization(currentUser),
          status: 'pending',
        },
      }),
      db.contracts.count({
        where: {
          ...scopeByOrganization(currentUser),
          status: 'active',
          end_date: {
            [Op.between]: [now, contractExpiryWindow],
          },
        },
      }),
      db.payment_requests.count({
        where: {
          ...scopeByOrganization(currentUser),
          status: {
            [Op.in]: ['submitted', 'under_review', 'approved', 'batched'],
          },
          requested_at: {
            [Op.lte]: overduePaymentThreshold,
          },
        },
      }),
      db.compliance_alerts.count({
        where: {
          ...scopeByOrganization(currentUser),
          status: {
            [Op.in]: ['open', 'acknowledged'],
          },
          alert_type: {
            [Op.in]: ['vendor_document_expiry', 'missing_document'],
          },
        },
      }),
      db.compliance_alerts.count({
        where: {
          ...scopeByOrganization(currentUser),
          status: {
            [Op.in]: ['open', 'acknowledged'],
          },
          severity: {
            [Op.in]: ['high', 'critical'],
          },
        },
      }),
      db.notifications.count({
        where: {
          ...scopeByOrganization(currentUser),
          read: false,
        },
      }),
      db.projects.aggregate('completion_percent', 'avg', {
        plain: true,
        where: {
          ...scopeByOrganization(currentUser),
          status: {
            [Op.in]: ['approved', 'active', 'on_hold', 'completed'],
          },
        },
      }),
      db.projects.count({
        where: {
          ...scopeByOrganization(currentUser),
          risk_level: {
            [Op.in]: ['high', 'critical'],
          },
          status: {
            [Op.in]: ['approved', 'active', 'on_hold'],
          },
        },
      }),
      db.contracts.findAll({
        attributes: ['id', 'contract_number', 'title', 'contract_value', 'currency', 'end_date', 'status'],
        where: {
          ...scopeByOrganization(currentUser),
          status: {
            [Op.in]: ['active', 'suspended', 'completed', 'expired'],
          },
        },
        include: [
          {
            model: db.vendors,
            as: 'vendor',
            attributes: ['name'],
          },
          {
            model: db.projects,
            as: 'project',
            attributes: ['name'],
          },
        ],
        order: [['contract_value', 'DESC']],
        limit: 5,
      }),
      db.contracts.findAll({
        attributes: ['id', 'contract_number', 'title', 'contract_value', 'currency', 'end_date', 'status'],
        where: {
          ...scopeByOrganization(currentUser),
          status: 'active',
          end_date: {
            [Op.between]: [now, contractExpiryWindow],
          },
        },
        include: [
          {
            model: db.vendors,
            as: 'vendor',
            attributes: ['name'],
          },
          {
            model: db.projects,
            as: 'project',
            attributes: ['name'],
          },
        ],
        order: [['end_date', 'ASC']],
        limit: 6,
      }),
      db.approvals.findAll({
        attributes: ['id', 'record_type', 'record_key', 'requested_at', 'status'],
        where: {
          ...scopeByOrganization(currentUser),
          status: 'pending',
        },
        include: [
          {
            model: db.approval_workflows,
            as: 'workflow',
            attributes: ['name', 'module'],
          },
          {
            model: db.approval_steps,
            as: 'step',
            attributes: ['name', 'step_order'],
          },
          {
            model: db.users,
            as: 'requested_by_user',
            attributes: ['firstName', 'lastName', 'email'],
          },
          {
            model: db.users,
            as: 'assigned_to_user',
            attributes: ['firstName', 'lastName', 'email'],
          },
        ],
        order: [['requested_at', 'ASC']],
        limit: 8,
      }),
      db.requisitions.findAll({
        attributes: [
          'id',
          'requisition_number',
          'title',
          'procurement_method',
          'estimated_amount',
          'currency',
          'needed_by_date',
          'status',
        ],
        where: {
          ...scopeByOrganization(currentUser),
          status: {
            [Op.in]: ['submitted', 'under_review', 'approved', 'in_tender', 'awarded'],
          },
        },
        include: [
          {
            model: db.provinces,
            as: 'province',
            attributes: ['name', 'code'],
          },
        ],
        order: [['updatedAt', 'DESC']],
        limit: 8,
      }),
      db.compliance_alerts.findAll({
        attributes: ['id', 'alert_type', 'severity', 'title', 'details', 'record_type', 'record_key', 'due_at', 'status'],
        where: {
          ...scopeByOrganization(currentUser),
          status: {
            [Op.in]: ['open', 'acknowledged'],
          },
        },
        include: [
          {
            model: db.users,
            as: 'assigned_to_user',
            attributes: ['firstName', 'lastName', 'email'],
          },
        ],
        order: [
          ['severity', 'DESC'],
          ['due_at', 'ASC'],
          ['createdAt', 'DESC'],
        ],
        limit: 8,
      }),
      db.projects.findAll({
        attributes: ['id', 'status', 'completion_percent'],
        where: {
          ...scopeByOrganization(currentUser),
        },
        include: [
          {
            model: db.provinces,
            as: 'province',
            attributes: ['name'],
          },
        ],
      }),
      db.notifications.findAll({
        attributes: ['id', 'type', 'title', 'message', 'read', 'sent_at', 'record_type', 'record_key'],
        where: {
          ...scopeByOrganization(currentUser),
        },
        order: [['sent_at', 'DESC'], ['createdAt', 'DESC']],
        limit: 6,
      }),
    ]);

    const provinceRolloutMap = rolloutProjects.reduce((accumulator, project) => {
      const provinceName = project.province?.name || 'Unassigned province';

      if (!accumulator[provinceName]) {
        accumulator[provinceName] = {
          provinceName,
          totalProjects: 0,
          activeProjects: 0,
          completionTotal: 0,
        };
      }

      accumulator[provinceName].totalProjects += 1;
      accumulator[provinceName].completionTotal += toNumber(project.completion_percent);

      if (project.status === 'active') {
        accumulator[provinceName].activeProjects += 1;
      }

      return accumulator;
    }, {});

    const provinceRollout = Object.values(provinceRolloutMap)
      .map((province) => ({
        provinceName: province.provinceName,
        totalProjects: province.totalProjects,
        activeProjects: province.activeProjects,
        averageCompletion: province.totalProjects
          ? Math.round(province.completionTotal / province.totalProjects)
          : 0,
      }))
      .sort((left, right) => right.totalProjects - left.totalProjects)
      .slice(0, 6);

    const formattedApprovalQueue = approvalQueue.map((approval) => ({
      id: approval.id,
      recordType: approval.record_type,
      recordKey: approval.record_key,
      status: approval.status,
      requestedAt: approval.requested_at,
      workflowName: approval.workflow?.name || 'Workflow not assigned',
      stepName: approval.step?.name || 'No active step',
      stepOrder: approval.step?.step_order || null,
      requestedBy: approval.requested_by_user
        ? `${approval.requested_by_user.firstName || ''} ${approval.requested_by_user.lastName || ''}`.trim() || approval.requested_by_user.email
        : 'Unknown requester',
      assignedTo: approval.assigned_to_user
        ? `${approval.assigned_to_user.firstName || ''} ${approval.assigned_to_user.lastName || ''}`.trim() || approval.assigned_to_user.email
        : 'Unassigned',
    }));

    const formattedProcurementQueue = procurementQueue.map((requisition) => ({
      id: requisition.id,
      requisitionNumber: requisition.requisition_number,
      title: requisition.title,
      procurementMethod: requisition.procurement_method,
      estimatedAmount: toNumber(requisition.estimated_amount),
      currency: requisition.currency,
      neededByDate: requisition.needed_by_date,
      status: requisition.status,
      provinceName: requisition.province?.name || 'Unassigned province',
    }));

    const formattedContractWatchlist = contractWatchlist.map((contract) => ({
      id: contract.id,
      contractNumber: contract.contract_number,
      title: contract.title,
      contractValue: toNumber(contract.contract_value),
      currency: contract.currency,
      endDate: contract.end_date,
      status: contract.status,
      vendorName: contract.vendor?.name || 'Vendor not linked',
      projectName: contract.project?.name || 'Project not linked',
      daysToExpiry: contract.end_date
        ? Math.ceil((new Date(contract.end_date).getTime() - now.getTime()) / (1000 * 60 * 60 * 24))
        : null,
    }));

    const formattedTopContracts = topContracts.map((contract) => ({
      id: contract.id,
      contractNumber: contract.contract_number,
      title: contract.title,
      contractValue: toNumber(contract.contract_value),
      currency: contract.currency,
      endDate: contract.end_date,
      status: contract.status,
      vendorName: contract.vendor?.name || 'Vendor not linked',
      projectName: contract.project?.name || 'Project not linked',
    }));

    const formattedRiskPanel = riskPanel.map((alert) => ({
      id: alert.id,
      alertType: alert.alert_type,
      severity: alert.severity,
      title: alert.title,
      details: alert.details,
      recordType: alert.record_type,
      recordKey: alert.record_key,
      dueAt: alert.due_at,
      status: alert.status,
      assignedTo: alert.assigned_to_user
        ? `${alert.assigned_to_user.firstName || ''} ${alert.assigned_to_user.lastName || ''}`.trim() || alert.assigned_to_user.email
        : 'Unassigned',
    }));

    const formattedNotifications = recentNotifications.map((notification) => ({
      id: notification.id,
      type: notification.type,
      title: notification.title,
      message: notification.message,
      read: notification.read,
      sentAt: notification.sent_at,
      recordType: notification.record_type,
      recordKey: notification.record_key,
    }));

    const summary = {
      approvedBudget: {
        USD: approvedBudgetUSD,
        CDF: approvedBudgetCDF,
      },
      committedBudget: {
        USD: committedBudgetUSD,
        CDF: committedBudgetCDF,
      },
      disbursedBudget: {
        USD: disbursedBudgetUSD,
        CDF: disbursedBudgetCDF,
      },
      budgetVariance: {
        USD: approvedBudgetUSD - committedBudgetUSD,
        CDF: approvedBudgetCDF - committedBudgetCDF,
      },
      activeProjects,
      procurementPipeline,
      pendingApprovals,
      contractsNearingExpiry,
      overduePayments,
      vendorComplianceAlerts,
      openRiskAlerts,
      unreadNotifications,
      averageProjectProgress: Math.round(toNumber(averageProjectProgress) || 0),
      highRiskProjects,
    };

    const datasets = {
      approvalQueue: formattedApprovalQueue,
      procurementQueue: formattedProcurementQueue,
      contractWatchlist: formattedContractWatchlist,
      topContracts: formattedTopContracts,
      riskPanel: formattedRiskPanel,
      provinceRollout,
      recentNotifications: formattedNotifications,
    };

    res.status(200).json({
      workspace: buildWorkspacePayload(currentUser?.app_role?.name, summary, datasets),
      summary,
      ...datasets,
    });
  }),
);

module.exports = router;
