const express = require('express');
const validator = require('validator');
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 SUPPORTED_RECORD_TYPES = new Set([
  'requisitions',
  'contracts',
  'projects',
  'vendors',
  'tenders',
  'awards',
  'invoices',
  'payment_requests',
  'budget_reallocations',
  'grants',
  'compliance_alerts',
]);

const MIN_MAJOR_CONTRACT_VALUE = 1000;

const hasMeaningfulText = (value) => Boolean(String(value || '').trim());

const getRecordId = (value) => {
  const normalizedValue = String(value || '').trim();

  if (!normalizedValue || !validator.isUUID(normalizedValue)) {
    return null;
  }

  return normalizedValue;
};

const getUserDisplayName = (user, fallback = '') => {
  if (!user) {
    return fallback;
  }

  return `${user.firstName || ''} ${user.lastName || ''}`.trim() || user.email || fallback;
};

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: ['organizationCount', 'platformUserCount', 'roleCount', 'permissionCount', 'auditEventCount', 'unreadNotifications'],
    focusCards: [
      {
        key: 'super-admin-organizations',
        title: 'Organizations on the platform',
        metricKey: 'organizationCount',
        note: 'Tenant footprint currently governed from the platform layer.',
        href: '/organizations/organizations-list',
      },
      {
        key: 'super-admin-users',
        title: 'Platform users under access governance',
        metricKey: 'platformUserCount',
        note: 'Accounts that may require provisioning, review, or top-level access support.',
        href: '/users/users-list',
      },
      {
        key: 'super-admin-roles',
        title: 'Roles and access structure in use',
        metricKey: 'roleCount',
        note: 'Role definitions shaping segregation of duties and approval boundaries.',
        href: '/roles/roles-list',
      },
      {
        key: 'super-admin-audit',
        title: 'Audit events available for review',
        metricKey: 'auditEventCount',
        note: 'Recorded platform and tenant actions available for investigation or control review.',
        href: '/audit_logs/audit_logs-list',
      },
    ],
    watchlistCards: [],
  },
  [WORKSPACE_ROLES.administrator]: {
    summaryMetricKeys: ['pendingApprovals', 'unreadNotifications', 'openRiskAlerts', 'vendorComplianceAlerts'],
    focusCards: [
      {
        key: 'admin-approvals',
        title: 'Approvals blocked by routing or delegation',
        metricKey: 'pendingApprovals',
        note: 'Items often delayed because workflow steps, assignments, or delegation setup need administrator attention.',
        href: '/approvals/approvals-list',
      },
      {
        key: 'admin-notifications',
        title: 'Unread administrator notices',
        metricKey: 'unreadNotifications',
        note: 'Recent notices that can point to user support issues, record problems, or broken process follow-through.',
        href: '/notifications/notifications-list',
      },
      {
        key: 'admin-risks',
        title: 'Administrative control signals',
        metricKey: 'openRiskAlerts',
        note: 'Open alerts that can indicate setup, delegation, or coordination problems affecting operations.',
        href: '/compliance_alerts/compliance_alerts-list',
      },
      {
        key: 'admin-vendor-alerts',
        title: 'Evidence and document alerts',
        metricKey: 'vendorComplianceAlerts',
        note: 'Document and compliance issues that often need routing, notice, or administrator coordination.',
        href: '/vendor_compliance_documents/vendor_compliance_documents-list',
      },
    ],
    watchlistCards: [
      {
        key: 'admin-approval-watch',
        title: 'Workflow routing queue',
        collectionKey: 'approvalQueue',
        note: 'Approvals that may need route fixes, reassignment, or user follow-up.',
        href: '/approvals/approvals-list',
      },
      {
        key: 'admin-notification-watch',
        title: 'Administrator notice watchlist',
        collectionKey: 'recentNotifications',
        note: 'Recent support and system signals that help spot process issues early.',
        href: '/notifications/notifications-list',
      },
      {
        key: 'admin-risk-watch',
        title: 'Administrative exception watchlist',
        collectionKey: 'riskPanel',
        note: 'Open alerts and exceptions likely to require cleanup, coordination, or follow-through.',
        href: '/compliance_alerts/compliance_alerts-list',
      },
    ],
  },
  [WORKSPACE_ROLES.directorGeneral]: {
    summaryMetricKeys: ['approvedBudget', 'committedBudget', 'disbursedBudget', 'pendingApprovals', 'highRiskProjects', 'contractsNearingExpiry'],
    focusCards: [
      {
        key: 'dg-commitments',
        title: 'Committed budget requiring executive attention',
        metricKey: 'committedBudget',
        note: 'Current commitment pressure across the institution relative to approved funding space.',
        href: '/allocations/allocations-list',
      },
      {
        key: 'dg-approvals',
        title: 'Executive decisions waiting',
        metricKey: 'pendingApprovals',
        note: 'High-impact approvals and escalations still waiting for institutional direction.',
        href: '/approvals/approvals-list',
      },
      {
        key: 'dg-risks',
        title: 'High-risk projects in view',
        metricKey: 'highRiskProjects',
        note: 'Projects marked high or critical risk and likely to need executive intervention.',
        href: '/projects/projects-list',
      },
      {
        key: 'dg-contracts',
        title: 'Contracts nearing executive attention',
        metricKey: 'contractsNearingExpiry',
        note: 'Active contracts approaching expiry and likely to need escalation, renewal, or decision.',
        href: '/contracts/contracts-list',
      },
    ],
    watchlistCards: [
      {
        key: 'dg-approval-watch',
        title: 'Executive escalation queue',
        collectionKey: 'approvalQueue',
        note: 'Pending approvals and requests most likely to require executive direction.',
        href: '/approvals/approvals-list',
      },
      {
        key: 'dg-rollout-watch',
        title: 'Delivery footprint watchlist',
        collectionKey: 'provinceRollout',
        note: 'A quick read on execution spread and momentum across provinces.',
        href: '/projects/projects-list',
      },
      {
        key: 'dg-risk-watch',
        title: 'Strategic risk watchlist',
        collectionKey: 'riskPanel',
        note: 'Open alerts and control issues that may affect institutional outcomes.',
        href: '/compliance_alerts/compliance_alerts-list',
      },
    ],
  },
  [WORKSPACE_ROLES.financeDirector]: {
    summaryMetricKeys: ['approvedBudget', 'committedBudget', 'disbursedBudget', 'overduePayments', 'pendingApprovals', 'unreadNotifications'],
    focusCards: [
      {
        key: 'finance-commitments',
        title: 'Committed budget under finance control',
        metricKey: 'committedBudget',
        note: 'Current commitments that finance must monitor against available funding and approval status.',
        href: '/allocations/allocations-list',
      },
      {
        key: 'finance-payments',
        title: 'Aging payment requests',
        metricKey: 'overduePayments',
        note: 'Payment requests older than the review window and likely to create pressure on delivery or vendors.',
        href: '/payment_requests/payment_requests-list',
      },
      {
        key: 'finance-approvals',
        title: 'Finance approvals waiting action',
        metricKey: 'pendingApprovals',
        note: 'Approvals slowing down commitments, disbursements, or other finance actions.',
        href: '/approvals/approvals-list',
      },
      {
        key: 'finance-disbursements',
        title: 'Disbursed budget',
        metricKey: 'disbursedBudget',
        note: 'Processed payments already released and shaping the current cash position.',
        href: '/ledger_entries/ledger_entries-list',
      },
    ],
    watchlistCards: [
      {
        key: 'finance-approval-watch',
        title: 'Finance approval queue',
        collectionKey: 'approvalQueue',
        note: 'Pending approvals that are holding back commitments or disbursements.',
        href: '/approvals/approvals-list',
      },
      {
        key: 'finance-contract-watch',
        title: 'Commitment watchlist',
        collectionKey: 'contractWatchlist',
        note: 'Contracts most likely to shape upcoming payment or budget pressure.',
        href: '/contracts/contracts-list',
      },
      {
        key: 'finance-notification-watch',
        title: 'Finance notice watchlist',
        collectionKey: 'recentNotifications',
        note: 'Recent notices that may signal bottlenecks in finance operations.',
        href: '/notifications/notifications-list',
      },
    ],
  },
  [WORKSPACE_ROLES.procurementLead]: {
    summaryMetricKeys: ['procurementPipeline', 'pendingApprovals', 'contractsNearingExpiry', 'vendorComplianceAlerts', 'openRiskAlerts', 'activeProjects'],
    focusCards: [
      {
        key: 'procurement-pipeline',
        title: 'Requisitions moving through sourcing',
        metricKey: 'procurementPipeline',
        note: 'Live demand currently progressing through review, tender, award, or conversion to contract.',
        href: '/requisitions/requisitions-list',
      },
      {
        key: 'procurement-approvals',
        title: 'Approvals slowing procurement',
        metricKey: 'pendingApprovals',
        note: 'Decisions delaying sourcing, evaluation, award, or contract activation.',
        href: '/approvals/approvals-list',
      },
      {
        key: 'procurement-contracts',
        title: 'Contracts nearing procurement follow-up',
        metricKey: 'contractsNearingExpiry',
        note: 'Active commitments approaching expiry and likely to require renewal, closure, or replacement.',
        href: '/contracts/contracts-list',
      },
      {
        key: 'procurement-vendors',
        title: 'Vendor readiness issues',
        metricKey: 'vendorComplianceAlerts',
        note: 'Supplier document and compliance gaps that can slow award or contract execution.',
        href: '/vendors/vendors-list',
      },
    ],
    watchlistCards: [
      {
        key: 'procurement-queue',
        title: 'Priority sourcing queue',
        collectionKey: 'procurementQueue',
        note: 'Requisitions most likely to need procurement action next.',
        href: '/requisitions/requisitions-list',
      },
      {
        key: 'procurement-contract-watch',
        title: 'Contract renewal watchlist',
        collectionKey: 'contractWatchlist',
        note: 'Supplier commitments most likely to create continuity pressure soon.',
        href: '/contracts/contracts-list',
      },
      {
        key: 'procurement-approval-watch',
        title: 'Procurement approval watchlist',
        collectionKey: 'approvalQueue',
        note: 'Pending approvals tied to the demand-to-award chain.',
        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, amendment, 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 needing control review',
        collectionKey: 'approvalQueue',
        note: 'Pending approvals where evidence, delegation, or routing should be verified.',
        href: '/approvals/approvals-list',
      },
      {
        key: 'compliance-notification-watch',
        title: 'Control notice watchlist',
        collectionKey: 'recentNotifications',
        note: 'Recent notices that 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 visible 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 delivery watchlist',
        collectionKey: 'provinceRollout',
        note: 'Provincial delivery concentration and completion spread across the current portfolio.',
        href: '/projects/projects-list',
      },
      {
        key: 'delivery-approval-watch',
        title: 'Delivery blocker queue',
        collectionKey: 'approvalQueue',
        note: 'Pending approvals that can delay works, procurement, or milestone completion.',
        href: '/approvals/approvals-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',
      },
    ],
  },
};

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,
      organizationCount,
      platformUserCount,
      roleCount,
      permissionCount,
      auditEventCount,
      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.organizations.count(),
      db.users.count({
        where: scopeByOrganization(currentUser, 'organizationsId'),
      }),
      db.roles.count(),
      db.permissions.count(),
      db.audit_logs.count({
        where: {
          ...scopeByOrganization(currentUser),
        },
      }),
      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;

      if (!provinceName) {
        return accumulator;
      }

      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
      .filter(
        (approval) =>
          SUPPORTED_RECORD_TYPES.has(approval.record_type)
          && hasMeaningfulText(approval.record_key)
          && hasMeaningfulText(approval.workflow?.name)
          && hasMeaningfulText(approval.step?.name)
          && Boolean(getUserDisplayName(approval.requested_by_user)),
      )
      .map((approval) => ({
        id: approval.id,
        recordType: approval.record_type,
        recordKey: approval.record_key,
        status: approval.status,
        requestedAt: approval.requested_at,
        recordId: getRecordId(approval.record_key),
        workflowName: approval.workflow.name,
        stepName: approval.step.name,
        stepOrder: approval.step?.step_order || null,
        requestedBy: getUserDisplayName(approval.requested_by_user),
        assignedTo: getUserDisplayName(approval.assigned_to_user, 'Awaiting assignment'),
      }));

    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 || 'Province not assigned',
    }));

    const formattedContractWatchlist = contractWatchlist
      .filter((contract) => contract.vendor?.name || contract.project?.name)
      .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
      .filter(
        (contract) =>
          (contract.vendor?.name || contract.project?.name)
          && hasMeaningfulText(contract.contract_number || contract.title)
          && toNumber(contract.contract_value) >= MIN_MAJOR_CONTRACT_VALUE,
      )
      .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
      .filter((alert) => !alert.record_type || SUPPORTED_RECORD_TYPES.has(alert.record_type))
      .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,
      recordId: getRecordId(alert.record_key),
      dueAt: alert.due_at,
      status: alert.status,
      assignedTo: getUserDisplayName(alert.assigned_to_user, 'Unassigned'),
    }));

    const formattedNotifications = recentNotifications
      .filter((notification) => !notification.record_type || SUPPORTED_RECORD_TYPES.has(notification.record_type))
      .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,
      recordId: getRecordId(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,
      organizationCount,
      platformUserCount,
      roleCount,
      permissionCount,
      auditEventCount,
      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;
