import { WorkflowRequest } from '@models/WorkflowRequest';
import { ApprovalLevel } from '@models/ApprovalLevel';
import { Participant } from '@models/Participant';
import { Activity } from '@models/Activity';
import { WorkNote } from '@models/WorkNote';
import { Document } from '@models/Document';
import { TatAlert } from '@models/TatAlert';
import { User } from '@models/User';
import { Op, QueryTypes } from 'sequelize';
import { sequelize } from '@config/database';
import dayjs from 'dayjs';
import logger from '@utils/logger';
import { calculateSLAStatus } from '@utils/tatTimeUtils';

interface DateRangeFilter {
  start: Date;
  end: Date;
}

export class DashboardService {
  /**
   * Build user-level filter clause that includes all requests where user is involved:
   * - As initiator (created the request)
   * - As approver (in any approval level)
   * - As participant/spectator
   * 
   * @param workflowAlias - The alias used for workflow_requests table (e.g., 'wf')
   * @returns SQL clause to filter requests for user-level view
   */
  private buildUserLevelFilter(workflowAlias: string = 'wf'): string {
    return `
      AND (
        ${workflowAlias}.initiator_id = :userId
        OR EXISTS (
          SELECT 1 FROM approval_levels al_user
          WHERE al_user.request_id = ${workflowAlias}.request_id
          AND al_user.approver_id = :userId
        )
        OR EXISTS (
          SELECT 1 FROM participants p_user
          WHERE p_user.request_id = ${workflowAlias}.request_id
          AND p_user.user_id = :userId
        )
      )
    `;
  }

  /**
   * Parse date range string to Date objects
   */
  private parseDateRange(dateRange?: string, startDate?: string, endDate?: string): DateRangeFilter {
    // If custom date range is provided, use those dates
    if (dateRange === 'custom' && startDate && endDate) {
      const start = dayjs(startDate).startOf('day').toDate();
      const end = dayjs(endDate).endOf('day').toDate();
      // Ensure end date is not in the future
      const now = dayjs();
      const actualEnd = end > now.toDate() ? now.endOf('day').toDate() : end;
      return { start, end: actualEnd };
    }

    // If custom is selected but dates are not provided, default to last 30 days
    if (dateRange === 'custom' && (!startDate || !endDate)) {
      const now = dayjs();
      return {
        start: now.subtract(30, 'day').startOf('day').toDate(),
        end: now.endOf('day').toDate()
      };
    }

    const now = dayjs();

    switch (dateRange) {
      case 'today':
        return {
          start: now.startOf('day').toDate(),
          end: now.endOf('day').toDate()
        };
      case 'week':
        return {
          start: now.startOf('week').toDate(),
          end: now.endOf('week').toDate()
        };
      case 'month':
        return {
          start: now.startOf('month').toDate(),
          end: now.endOf('month').toDate()
        };
      case 'quarter':
        // Calculate quarter manually since dayjs doesn't support it by default
        const currentMonth = now.month();
        const quarterStartMonth = Math.floor(currentMonth / 3) * 3;
        return {
          start: now.month(quarterStartMonth).startOf('month').toDate(),
          end: now.month(quarterStartMonth + 2).endOf('month').toDate()
        };
      case 'year':
        return {
          start: now.startOf('year').toDate(),
          end: now.endOf('year').toDate()
        };
      default:
        // Default to last 30 days (inclusive of today)
        return {
          start: now.subtract(30, 'day').startOf('day').toDate(),
          end: now.endOf('day').toDate() // Include full current day
        };
    }
  }

  /**
   * Get all KPIs for dashboard
   */
  async getKPIs(userId: string, dateRange?: string, startDate?: string, endDate?: string, viewAsUser?: boolean) {
    const range = this.parseDateRange(dateRange, startDate, endDate);

    // Run all KPI queries in parallel for performance
    const [
      requestStats,
      tatEfficiency,
      approverLoad,
      engagement,
      aiInsights
    ] = await Promise.all([
      this.getRequestStats(userId, dateRange, startDate, endDate, undefined, undefined, undefined, undefined, undefined, undefined, undefined, undefined, undefined, viewAsUser),
      this.getTATEfficiency(userId, dateRange, startDate, endDate, viewAsUser),
      this.getApproverLoad(userId, dateRange, startDate, endDate, viewAsUser),
      this.getEngagementStats(userId, dateRange, startDate, endDate, viewAsUser),
      this.getAIInsights(userId, dateRange, startDate, endDate, viewAsUser)
    ]);

    return {
      requestVolume: requestStats,
      tatEfficiency,
      approverLoad,
      engagement,
      aiInsights,
      dateRange: {
        start: range.start,
        end: range.end,
        label: dateRange || 'last30days'
      }
    };
  }

  /**
   * Get request volume and status statistics
   */
  async getRequestStats(
    userId: string,
    dateRange?: string,
    startDate?: string,
    endDate?: string,
    status?: string,
    priority?: string,
    templateType?: string,
    department?: string,
    initiator?: string,
    approver?: string,
    approverType?: 'current' | 'any',
    search?: string,
    slaCompliance?: string,
    viewAsUser?: boolean
  ) {
    // Check if date range should be applied
    // 'all' means no date filter - show all requests regardless of date
    const applyDateRange = dateRange !== undefined && dateRange !== null && dateRange !== 'all';
    const range = applyDateRange ? this.parseDateRange(dateRange, startDate, endDate) : null;

    // Check if user is admin or management (has broader access)
    // If viewAsUser is true, treat as normal user even if admin
    const user = await User.findByPk(userId);
    const isAdmin = viewAsUser ? false : (user?.hasManagementAccess() || false);

    // Build filter conditions
    let filterConditions = '';
    const replacements: any = { userId };

    // Add date range to replacements if date range is applied
    if (applyDateRange && range) {
      replacements.start = range.start;
      replacements.end = range.end;
    }

    // Status filter
    if (status && status !== 'all') {
      const statusUpper = status.toUpperCase();
      if (statusUpper === 'PENDING') {
        // Pending includes both PENDING and IN_PROGRESS
        filterConditions += ` AND (wf.status = 'PENDING' OR wf.status = 'IN_PROGRESS')`; // IN_PROGRESS legacy support
      } else if (statusUpper === 'CLOSED') {
        filterConditions += ` AND wf.status = 'CLOSED'`;
      } else if (statusUpper === 'REJECTED') {
        filterConditions += ` AND wf.status = 'REJECTED'`;
      } else if (statusUpper === 'APPROVED') {
        filterConditions += ` AND wf.status = 'APPROVED'`;
      } else {
        // Fallback: use the uppercase value as-is
        filterConditions += ` AND wf.status = :status`;
        replacements.status = statusUpper;
      }
    }

    // Priority filter
    if (priority && priority !== 'all') {
      filterConditions += ` AND wf.priority = :priority`;
      replacements.priority = priority.toUpperCase();
    }

    // TemplateType filter
    if (templateType && templateType !== 'all') {
      const templateTypeUpper = templateType.toUpperCase();
      if (templateTypeUpper === 'CUSTOM') {
        // For CUSTOM, include both CUSTOM and null (legacy requests)
        filterConditions += ` AND (wf.template_type = 'CUSTOM' OR wf.template_type IS NULL)`;
      } else {
        filterConditions += ` AND wf.template_type = :templateType`;
        replacements.templateType = templateTypeUpper;
      }
    }

    // Department filter (through initiator)
    if (department && department !== 'all') {
      filterConditions += ` AND EXISTS (
        SELECT 1 FROM users u 
        WHERE u.user_id = wf.initiator_id 
        AND u.department = :department
      )`;
      replacements.department = department;
    }

    // Initiator filter
    if (initiator && initiator !== 'all') {
      filterConditions += ` AND wf.initiator_id = :initiatorId`;
      replacements.initiatorId = initiator;
    }

    // Search filter (title, description, or requestNumber)
    if (search && search.trim()) {
      filterConditions += ` AND (
        wf.title ILIKE :search OR 
        wf.description ILIKE :search OR 
        wf.request_number ILIKE :search
      )`;
      replacements.search = `%${search.trim()}%`;
    }

    // Approver filter (with current vs any logic)
    if (approver && approver !== 'all') {
      const approverTypeValue = approverType || 'current';
      if (approverTypeValue === 'current') {
        // Filter by current active approver only
        filterConditions += ` AND EXISTS (
          SELECT 1 FROM approval_levels al
          WHERE al.request_id = wf.request_id
          AND al.approver_id = :approverId
          AND al.status IN ('PENDING', 'IN_PROGRESS')
          AND al.level_number = wf.current_level
        )`;
      } else {
        // Filter by any approver (past or current)
        filterConditions += ` AND EXISTS (
          SELECT 1 FROM approval_levels al
          WHERE al.request_id = wf.request_id
          AND al.approver_id = :approverId
        )`;
      }
      replacements.approverId = approver;
    }

    // SLA Compliance filter
    if (slaCompliance && slaCompliance !== 'all') {
      if (slaCompliance === 'breached') {
        filterConditions += ` AND EXISTS (
          SELECT 1 FROM tat_alerts ta
          INNER JOIN approval_levels al ON ta.level_id = al.level_id
          WHERE ta.request_id = wf.request_id
          AND ta.is_breached = true
        )`;
      } else if (slaCompliance === 'compliant') {
        // Compliant: completed requests that are not breached
        filterConditions += ` AND wf.status IN ('APPROVED', 'REJECTED', 'CLOSED')
          AND NOT EXISTS (
            SELECT 1 FROM tat_alerts ta
            INNER JOIN approval_levels al ON ta.level_id = al.level_id
            WHERE ta.request_id = wf.request_id
            AND ta.is_breached = true
          )`;
      }
      // Note: on_track, approaching, critical are calculated dynamically
      // For stats, we only filter by breached/compliant as these are stored in DB
    }

    // Organization Level: Admin/Management see ALL requests across organization
    // Personal Level: Regular users see requests where they are INVOLVED (initiator, approver, or participant)
    // Note: If dateRange is provided, filter by submission_date (or createdAt if submission_date is null). Otherwise, show all requests.
    // For pending/open requests, if no date range, count ALL pending requests regardless of creation date
    // For approved/rejected/closed, if date range is provided, count only those submitted in date range
    // Match the same logic as listParticipantRequests: include requests where submission_date is in range OR (submission_date is null AND created_at is in range)
    const dateFilterClause = applyDateRange
      ? `(
          (wf.submission_date BETWEEN :start AND :end AND wf.submission_date IS NOT NULL)
          OR 
          (wf.submission_date IS NULL AND wf.created_at BETWEEN :start AND :end)
        )`
      : `1=1`; // No date filter - show all requests

    // Build user-level filter: Include requests where user is initiator, approver, or participant
    const userLevelFilter = !isAdmin ? `
      AND (
        wf.initiator_id = :userId
        OR EXISTS (
          SELECT 1 FROM approval_levels al_user
          WHERE al_user.request_id = wf.request_id
          AND al_user.approver_id = :userId
        )
        OR EXISTS (
          SELECT 1 FROM participants p_user
          WHERE p_user.request_id = wf.request_id
          AND p_user.user_id = :userId
        )
      )
    ` : '';

    let whereClauseForAllRequests = `
      WHERE ${dateFilterClause}
      AND wf.is_draft = false
      AND (wf.is_deleted IS NULL OR wf.is_deleted = false)
      ${userLevelFilter}
      ${filterConditions}
    `;

    // For pending requests, if no date range is applied, don't filter by date at all
    // This ensures pending requests are always counted regardless of submission date
    // Match the same logic as listParticipantRequests: include requests where submission_date is in range OR (submission_date is null AND created_at is in range)
    const pendingDateFilterClause = applyDateRange
      ? `(
          (wf.submission_date BETWEEN :start AND :end AND wf.submission_date IS NOT NULL)
          OR 
          (wf.submission_date IS NULL AND wf.created_at BETWEEN :start AND :end)
        )`
      : `1=1`; // No date filter for pending requests

    let whereClauseForPending = `
      WHERE ${pendingDateFilterClause}
      AND wf.is_draft = false
      AND (wf.is_deleted IS NULL OR wf.is_deleted = false)
      AND (wf.status = 'PENDING' OR wf.status = 'IN_PROGRESS')
      ${userLevelFilter}
      ${filterConditions.replace(/AND \(wf\.status = 'PENDING' OR wf\.status = 'IN_PROGRESS'\)|AND wf\.status = 'PENDING'|AND wf\.status = 'IN_PROGRESS'/g, '').trim()}
    `;

    // Clean up any double ANDs
    whereClauseForPending = whereClauseForPending.replace(/\s+AND\s+AND/g, ' AND');

    // Get total, approved, rejected, closed, and paused requests
    // If date range is applied, only count requests submitted in that range
    // If no date range, count all requests matching other filters
    const result = await sequelize.query(`
      SELECT
        COUNT(*)::int AS total_requests,
        COUNT(CASE WHEN wf.status = 'APPROVED' THEN 1 END)::int AS approved_requests,
        COUNT(CASE WHEN wf.status = 'REJECTED' THEN 1 END)::int AS rejected_requests,
        COUNT(CASE WHEN wf.status = 'CLOSED' THEN 1 END)::int AS closed_requests,
        COUNT(CASE WHEN wf.is_paused = true THEN 1 END)::int AS paused_requests
      FROM workflow_requests wf
      ${whereClauseForAllRequests}
    `, {
      replacements,
      type: QueryTypes.SELECT
    });

    // Get ALL pending/open requests (excluding paused)
    // Organization Level (Admin): All pending requests across organization
    // Personal Level (Regular User): Only pending requests they initiated
    // If no date range, count all pending requests regardless of submission date
    const pendingWhereClause = whereClauseForPending.replace(
      /AND \(wf\.status = 'PENDING' OR wf\.status = 'IN_PROGRESS'\)/,
      `AND (wf.status = 'PENDING' OR wf.status = 'IN_PROGRESS') AND (wf.is_paused IS NULL OR wf.is_paused = false)`
    );
    const pendingResult = await sequelize.query(`
      SELECT COUNT(*)::int AS open_requests
      FROM workflow_requests wf
      ${pendingWhereClause}
    `, {
      replacements,
      type: QueryTypes.SELECT
    });

    // Get draft count separately (with filters)
    // For user-level, drafts are only visible to the initiator (not to approvers/participants)
    let draftWhereClause = `WHERE wf.is_draft = true ${!isAdmin ? `AND wf.initiator_id = :userId` : ''} ${filterConditions}`;
    const draftResult = await sequelize.query(`
      SELECT COUNT(*)::int AS draft_count
      FROM workflow_requests wf
      ${draftWhereClause}
    `, {
      replacements,
      type: QueryTypes.SELECT
    });

    const stats = result[0] as any;
    const pending = (pendingResult[0] as any);
    const drafts = (draftResult[0] as any);

    return {
      totalRequests: stats.total_requests || 0,
      openRequests: pending.open_requests || 0, // All pending requests regardless of creation date (excluding paused)
      approvedRequests: stats.approved_requests || 0,
      rejectedRequests: stats.rejected_requests || 0,
      closedRequests: stats.closed_requests || 0,
      pausedRequests: stats.paused_requests || 0,
      draftRequests: drafts.draft_count || 0,
      changeFromPrevious: {
        total: '+0',
        open: '+0',
        approved: '+0',
        rejected: '+0'
      }
    };
  }

  /**
   * Get TAT efficiency metrics
   */
  async getTATEfficiency(userId: string, dateRange?: string, startDate?: string, endDate?: string, viewAsUser?: boolean) {
    const range = this.parseDateRange(dateRange, startDate, endDate);

    // Check if user is admin or management (has broader access)
    // If viewAsUser is true, treat as normal user even if admin
    const user = await User.findByPk(userId);
    const isAdmin = viewAsUser ? false : (user?.hasManagementAccess() || false);

    // For regular users: only their initiated requests
    // For admin: all requests
    // Include only CLOSED requests (ignore APPROVED and REJECTED)
    // CLOSED status represents requests that were finalized with a conclusion remark
    // This ensures we capture all requests that finished during the period, regardless of when they started
    let whereClause = `
      WHERE wf.status = 'CLOSED'
      AND wf.is_draft = false
      AND wf.submission_date IS NOT NULL
      AND (
        (wf.closure_date IS NOT NULL AND wf.closure_date BETWEEN :start AND :end)
        OR (wf.closure_date IS NULL AND wf.updated_at BETWEEN :start AND :end)
      )
      ${!isAdmin ? `AND wf.initiator_id = :userId` : ''}
    `;

    // Get closed requests with their submission and closure dates
    const completedRequests = await sequelize.query(`
      SELECT
        wf.request_id,
        wf.submission_date,
        wf.closure_date,
        wf.updated_at,
        wf.priority
      FROM workflow_requests wf
      ${whereClause}
    `, {
      replacements: { start: range.start, end: range.end, userId },
      type: QueryTypes.SELECT
    });

    // Calculate cycle time using working hours for each request, grouped by priority
    const { calculateElapsedWorkingHours } = await import('@utils/tatTimeUtils');
    const priorityCycleTimes = new Map<string, number[]>();

    logger.info(`[Dashboard] Calculating cycle time for ${completedRequests.length} closed requests`);

    for (const req of completedRequests as any) {
      const submissionDate = req.submission_date;
      // Use closure_date if available, otherwise use updated_at
      const completionDate = req.closure_date || req.updated_at;
      const priority = (req.priority || 'STANDARD').toLowerCase();

      let elapsedHours: number | null = null;

      if (submissionDate && completionDate) {
        try {
          // Calculate elapsed working hours (respects working hours, weekends, holidays)
          elapsedHours = await calculateElapsedWorkingHours(
            submissionDate,
            completionDate,
            priority
          );

          // Group by priority
          if (!priorityCycleTimes.has(priority)) {
            priorityCycleTimes.set(priority, []);
          }
          priorityCycleTimes.get(priority)!.push(elapsedHours);

          logger.info(`[Dashboard] Request ${req.request_id} (${priority}): ${elapsedHours.toFixed(2)}h (submission: ${submissionDate}, completion: ${completionDate})`);
        } catch (error) {
          logger.error(`[Dashboard] Error calculating cycle time for request ${req.request_id}:`, error);
        }
      } else {
        logger.warn(`[Dashboard] Skipping request ${req.request_id} - missing dates (submission: ${submissionDate}, completion: ${completionDate})`);
      }

      // Note: Breach checking is now done in the allRequestsBreached loop below
      // using the same calculateSLAStatus logic as the Requests screen
      // This ensures consistency between Dashboard and All Requests screen
    }

    // Calculate average per priority
    const expressCycleTimes = priorityCycleTimes.get('express') || [];
    const standardCycleTimes = priorityCycleTimes.get('standard') || [];

    const expressAvg = expressCycleTimes.length > 0
      ? Math.round((expressCycleTimes.reduce((sum, hours) => sum + hours, 0) / expressCycleTimes.length) * 100) / 100
      : 0;

    const standardAvg = standardCycleTimes.length > 0
      ? Math.round((standardCycleTimes.reduce((sum, hours) => sum + hours, 0) / standardCycleTimes.length) * 100) / 100
      : 0;

    // Calculate overall average as average of EXPRESS and STANDARD averages
    // This is the average of the two priority averages (not weighted by count)
    let avgCycleTimeHours = 0;
    if (expressAvg > 0 && standardAvg > 0) {
      avgCycleTimeHours = Math.round(((expressAvg + standardAvg) / 2) * 100) / 100;
    } else if (expressAvg > 0) {
      avgCycleTimeHours = expressAvg;
    } else if (standardAvg > 0) {
      avgCycleTimeHours = standardAvg;
    }

    logger.info(`[Dashboard] Cycle time calculation: EXPRESS=${expressAvg.toFixed(2)}h (${expressCycleTimes.length} requests), STANDARD=${standardAvg.toFixed(2)}h (${standardCycleTimes.length} requests), Overall=${avgCycleTimeHours.toFixed(2)}h`);

    // Count ALL requests (pending, in-progress, approved, rejected, closed) that have currently breached TAT
    // Use the same logic as Requests screen: check currentLevelSLA status using calculateSLAStatus
    // This ensures delayedWorkflows matches what users see when filtering for "breached" in All Requests screen
    // For date range: completed requests (APPROVED/REJECTED/CLOSED) must be completed in date range
    // For pending/in-progress: include ALL pending/in-progress regardless of submission date (same as requestVolume stats)
    const allRequestsBreachedQuery = `
      SELECT DISTINCT
        wf.request_id,
        wf.status,
        wf.priority,
        wf.current_level,
        al.level_start_time AS current_level_start_time,
        al.tat_hours AS current_level_tat_hours,
        wf.submission_date,
        wf.total_tat_hours,
        wf.closure_date,
        wf.updated_at
      FROM workflow_requests wf
      LEFT JOIN approval_levels al ON al.request_id = wf.request_id
        AND al.level_number = wf.current_level
        AND (al.status = 'IN_PROGRESS' OR (wf.status = 'CLOSED' AND al.status = 'APPROVED'))
      WHERE wf.is_draft = false
      AND wf.submission_date IS NOT NULL
      AND (
        -- Completed requests: must be CLOSED in date range (ignore APPROVED and REJECTED)
        (wf.status = 'CLOSED'
         AND (
           (wf.closure_date IS NOT NULL AND wf.closure_date BETWEEN :start AND :end)
           OR (wf.closure_date IS NULL AND wf.updated_at BETWEEN :start AND :end)
         ))
        -- Pending/in-progress: include ALL regardless of submission date
        OR wf.status IN ('PENDING', 'IN_PROGRESS')
      )
      ${!isAdmin ? `AND wf.initiator_id = :userId` : ''}
      AND (
        EXISTS (
          SELECT 1 
          FROM tat_alerts ta
          INNER JOIN approval_levels al_breach ON ta.level_id = al_breach.level_id
          WHERE ta.request_id = wf.request_id
          AND ta.is_breached = true
          AND al_breach.level_number = wf.current_level
        )
        OR al.level_start_time IS NOT NULL
        OR wf.total_tat_hours > 0
      )
    `;

    const allRequestsBreached = await sequelize.query(allRequestsBreachedQuery, {
      replacements: { start: range.start, end: range.end, userId },
      type: QueryTypes.SELECT
    });

    // Use calculateSLAStatus to check if each request is breached (same as Requests screen logic)
    const { calculateSLAStatus } = await import('@utils/tatTimeUtils');
    let pendingBreachedCount = 0;

    // Also need to recalculate breachedCount for completed requests using same logic as Requests screen
    // This ensures we catch any completed requests that breached but weren't detected by previous checks
    let recalculatedBreachedCount = 0;
    let recalculatedCompliantCount = 0;

    for (const req of allRequestsBreached as any) {
      const isCompleted = req.status === 'CLOSED';

      // Check current level SLA (same logic as Requests screen)
      let isBreached = false;

      if (req.current_level_start_time && req.current_level_tat_hours > 0) {
        try {
          const priority = (req.priority || 'standard').toLowerCase();
          const levelEndDate = req.closure_date || null; // Use closure date if completed
          const slaData = await calculateSLAStatus(req.current_level_start_time, req.current_level_tat_hours, priority, levelEndDate);

          // Mark as breached if percentageUsed >= 100 (same as Requests screen)
          if (slaData.percentageUsed >= 100) {
            isBreached = true;
          }
        } catch (error) {
          logger.error(`[Dashboard] Error calculating SLA for request ${req.request_id}:`, error);
        }
      }

      // Also check overall SLA if current level SLA check doesn't show breach
      if (!isBreached && req.submission_date && req.total_tat_hours > 0) {
        try {
          const priority = (req.priority || 'standard').toLowerCase();
          const overallEndDate = req.closure_date || null;
          const overallSLA = await calculateSLAStatus(req.submission_date, req.total_tat_hours, priority, overallEndDate);

          if (overallSLA.percentageUsed >= 100) {
            isBreached = true;
          }
        } catch (error) {
          logger.error(`[Dashboard] Error calculating overall SLA for request ${req.request_id}:`, error);
        }
      }

      if (isBreached) {
        if (isCompleted) {
          recalculatedBreachedCount++;
        } else {
          pendingBreachedCount++;
        }
      } else if (isCompleted) {
        // Count as compliant if completed and not breached
        recalculatedCompliantCount++;
      }
    }

    // Use recalculated counts which match Requests screen logic exactly
    // These counts use the same calculateSLAStatus logic as the Requests screen
    const finalBreachedCount = recalculatedBreachedCount;

    // Total delayed workflows = completed breached + currently pending/in-progress breached
    const totalDelayedWorkflows = finalBreachedCount + pendingBreachedCount;

    // Compliant workflows = all CLOSED requests that did NOT breach TAT
    // This includes:
    // - Closed requests that were closed within TAT
    // Use recalculated compliant count from above which uses same logic as Requests screen
    // Note: Only counting CLOSED requests now (APPROVED and REJECTED are ignored)
    const totalCompleted = recalculatedBreachedCount + recalculatedCompliantCount;
    const compliantCount = recalculatedCompliantCount;

    // Compliance percentage = (compliant / (total completed + pending breached)) * 100
    // This shows health of the system: successful completions vs (failed completions + currently failing)
    // We include pending breached requests because they are already failures regarding SLA
    const totalFailuresAndSuccesses = totalCompleted + pendingBreachedCount;
    const compliancePercent = totalFailuresAndSuccesses > 0 ? Math.round((compliantCount / totalFailuresAndSuccesses) * 100) : 0;

    // Average cycle time is already calculated above from priority averages
    logger.info(`[Dashboard] Compliance calculation: ${totalCompleted} total completed (CLOSED), ${finalBreachedCount} completed breached, ${pendingBreachedCount} pending breached`);
    logger.info(`[Dashboard] Total Evaluated: ${totalFailuresAndSuccesses}, Compliant: ${compliantCount}, Score: ${compliancePercent}%`);
    logger.info(`[Dashboard] Breached requests (using Requests screen logic): ${finalBreachedCount} completed breached + ${pendingBreachedCount} pending/in-progress breached = ${totalDelayedWorkflows} total delayed`);

    return {
      avgTATCompliance: compliancePercent,
      avgCycleTimeHours,
      avgCycleTimeDays: Math.round((avgCycleTimeHours / 8) * 10) / 10, // 8 working hours per day
      delayedWorkflows: totalDelayedWorkflows, // Includes both completed and pending/in-progress breached requests
      totalCompleted,
      compliantWorkflows: compliantCount,
      changeFromPrevious: {
        compliance: '+5.8%', // TODO: Calculate actual change
        cycleTime: '-0.5h'
      }
    };
  }

  /**
   * Get approver load statistics
   */
  async getApproverLoad(userId: string, dateRange?: string, startDate?: string, endDate?: string, viewAsUser?: boolean) {
    const range = this.parseDateRange(dateRange, startDate, endDate);

    // Get pending actions where user is the CURRENT active approver
    // This means: the request is at this user's level AND it's the current level
    // Note: getApproverLoad is always user-specific (shows user's own pending/completed), so viewAsUser doesn't change behavior
    const pendingResult = await sequelize.query(`
      SELECT COUNT(DISTINCT al.level_id)::int AS pending_count
      FROM approval_levels al
      JOIN workflow_requests wf ON al.request_id = wf.request_id
      WHERE al.approver_id = :userId
      AND al.status = 'IN_PROGRESS'
      AND wf.status IN ('PENDING', 'IN_PROGRESS')
      AND wf.is_draft = false
      AND al.level_number = wf.current_level
    `, {
      replacements: { userId },
      type: QueryTypes.SELECT
    });

    // Get completed approvals
    // completed_today should always be TODAY regardless of date range filter
    // completed_this_week should be this week (Monday to Sunday)
    // IMPORTANT: Only count approvals where the user is the approver (al.approver_id = userId)
    const todayStart = dayjs().startOf('day').toDate();
    const todayEnd = dayjs().endOf('day').toDate();
    const weekStart = dayjs().startOf('week').toDate();
    const weekEnd = dayjs().endOf('week').toDate();

    const completedResult = await sequelize.query(`
      SELECT
        COUNT(CASE 
          WHEN al.action_date >= :todayStart 
          AND al.action_date <= :todayEnd 
          THEN 1 
        END)::int AS completed_today,
        COUNT(CASE 
          WHEN al.action_date >= :weekStart 
          AND al.action_date <= :weekEnd 
          THEN 1 
        END)::int AS completed_this_week
      FROM approval_levels al
      WHERE al.approver_id = :userId
      AND al.status IN ('APPROVED', 'REJECTED')
      AND al.action_date IS NOT NULL
    `, {
      replacements: {
        userId,
        todayStart,
        todayEnd,
        weekStart,
        weekEnd
      },
      type: QueryTypes.SELECT
    });

    const pending = (pendingResult[0] as any);
    const completed = (completedResult[0] as any);

    return {
      pendingActions: pending.pending_count || 0,
      completedToday: completed.completed_today || 0,
      completedThisWeek: completed.completed_this_week || 0,
      changeFromPrevious: {
        pending: '+2',
        completed: '+15%'
      }
    };
  }

  /**
   * Get engagement and quality metrics
   */
  async getEngagementStats(userId: string, dateRange?: string, startDate?: string, endDate?: string, viewAsUser?: boolean) {
    const range = this.parseDateRange(dateRange, startDate, endDate);

    // Check if user is admin or management (has broader access)
    // If viewAsUser is true, treat as normal user even if admin
    const user = await User.findByPk(userId);
    const isAdmin = viewAsUser ? false : (user?.hasManagementAccess() || false);

    // Get work notes count - uses created_at
    // For regular users: only from requests they initiated
    let workNotesWhereClause = `
      WHERE wn.created_at BETWEEN :start AND :end
      ${!isAdmin ? `AND EXISTS (
        SELECT 1 FROM workflow_requests wf 
        WHERE wf.request_id = wn.request_id 
        AND wf.initiator_id = :userId
        AND wf.is_draft = false
      )` : ''}
    `;

    const workNotesResult = await sequelize.query(`
      SELECT COUNT(*)::int AS work_notes_count
      FROM work_notes wn
      ${workNotesWhereClause}
    `, {
      replacements: { start: range.start, end: range.end, userId },
      type: QueryTypes.SELECT
    });

    // Get documents count - uses uploaded_at
    // For regular users: only from requests they initiated
    let documentsWhereClause = `
      WHERE d.uploaded_at BETWEEN :start AND :end
      ${!isAdmin ? `AND EXISTS (
        SELECT 1 FROM workflow_requests wf 
        WHERE wf.request_id = d.request_id 
        AND wf.initiator_id = :userId
        AND wf.is_draft = false
      )` : ''}
    `;

    const documentsResult = await sequelize.query(`
      SELECT COUNT(*)::int AS documents_count
      FROM documents d
      ${documentsWhereClause}
    `, {
      replacements: { start: range.start, end: range.end, userId },
      type: QueryTypes.SELECT
    });

    const workNotes = (workNotesResult[0] as any);
    const documents = (documentsResult[0] as any);

    return {
      workNotesAdded: workNotes.work_notes_count || 0,
      attachmentsUploaded: documents.documents_count || 0,
      changeFromPrevious: {
        workNotes: '+25',
        attachments: '+8'
      }
    };
  }

  /**
   * Get AI insights and closure metrics
   */
  async getAIInsights(userId: string, dateRange?: string, startDate?: string, endDate?: string, viewAsUser?: boolean) {
    const range = this.parseDateRange(dateRange, startDate, endDate);

    // Check if user is admin or management (has broader access)
    // If viewAsUser is true, treat as normal user even if admin
    const user = await User.findByPk(userId);
    const isAdmin = viewAsUser ? false : (user?.hasManagementAccess() || false);

    // For regular users: only their initiated requests
    // Use submission_date instead of created_at to filter by actual submission date
    let whereClause = `
      WHERE wf.submission_date BETWEEN :start AND :end
      AND wf.status = 'APPROVED'
      AND wf.conclusion_remark IS NOT NULL
      AND wf.is_draft = false
      AND wf.submission_date IS NOT NULL
      ${!isAdmin ? `AND wf.initiator_id = :userId` : ''}
    `;

    const result = await sequelize.query(`
      SELECT
        COUNT(*)::int AS total_with_conclusion,
        AVG(LENGTH(wf.conclusion_remark))::numeric AS avg_remark_length,
        COUNT(CASE WHEN wf.ai_generated_conclusion IS NOT NULL AND wf.ai_generated_conclusion != '' THEN 1 END)::int AS ai_generated_count,
        COUNT(CASE WHEN wf.ai_generated_conclusion IS NULL OR wf.ai_generated_conclusion = '' THEN 1 END)::int AS manual_count
      FROM workflow_requests wf
      ${whereClause}
    `, {
      replacements: { start: range.start, end: range.end, userId },
      type: QueryTypes.SELECT
    });

    const stats = result[0] as any;
    const totalWithConclusion = stats.total_with_conclusion || 0;
    const aiCount = stats.ai_generated_count || 0;
    const aiAdoptionPercent = totalWithConclusion > 0 ? Math.round((aiCount / totalWithConclusion) * 100) : 0;

    return {
      avgConclusionRemarkLength: Math.round(parseFloat(stats.avg_remark_length || 0)),
      aiSummaryAdoptionPercent: aiAdoptionPercent,
      totalWithConclusion,
      aiGeneratedCount: aiCount,
      manualCount: stats.manual_count || 0,
      changeFromPrevious: {
        adoption: '+12%',
        length: '+50 chars'
      }
    };
  }

  /**
   * Get AI Remark Utilization with monthly trends
   */
  async getAIRemarkUtilization(userId: string, dateRange?: string, startDate?: string, endDate?: string) {
    const range = this.parseDateRange(dateRange, startDate, endDate);

    // Check if user is admin or management (has broader access)
    const user = await User.findByPk(userId);
    const isAdmin = user?.hasManagementAccess() || false;

    // For regular users: only their initiated requests
    const userFilter = !isAdmin ? `AND cr.edited_by = :userId` : '';

    // Get overall metrics
    const overallMetrics = await sequelize.query(`
      SELECT
        COUNT(*)::int AS total_usage,
        COUNT(CASE WHEN cr.is_edited = true THEN 1 END)::int AS total_edits,
        ROUND(
          (COUNT(CASE WHEN cr.is_edited = true THEN 1 END)::numeric / 
          NULLIF(COUNT(*)::numeric, 0)) * 100, 0
        )::int AS edit_rate
      FROM conclusion_remarks cr
      WHERE cr.generated_at BETWEEN :start AND :end
      ${userFilter}
    `, {
      replacements: { start: range.start, end: range.end, userId },
      type: QueryTypes.SELECT
    });

    // Get monthly trends (last 7 months)
    const monthlyTrends = await sequelize.query(`
      SELECT
        TO_CHAR(DATE_TRUNC('month', cr.generated_at), 'Mon') AS month,
        EXTRACT(MONTH FROM cr.generated_at)::int AS month_num,
        COUNT(*)::int AS ai_usage,
        COUNT(CASE WHEN cr.is_edited = true THEN 1 END)::int AS manual_edits
      FROM conclusion_remarks cr
      WHERE cr.generated_at >= NOW() - INTERVAL '7 months'
      ${userFilter}
      GROUP BY month, month_num
      ORDER BY month_num ASC
    `, {
      replacements: { userId },
      type: QueryTypes.SELECT
    });

    const stats = overallMetrics[0] as any;

    return {
      totalUsage: stats.total_usage || 0,
      totalEdits: stats.total_edits || 0,
      editRate: stats.edit_rate || 0,
      monthlyTrends: monthlyTrends.map((m: any) => ({
        month: m.month,
        aiUsage: m.ai_usage,
        manualEdits: m.manual_edits
      }))
    };
  }

  /**
   * Get Approver Performance metrics with pagination
   * Supports priority and SLA filters for stats calculation
   */
  async getApproverPerformance(
    userId: string,
    dateRange?: string,
    page: number = 1,
    limit: number = 10,
    startDate?: string,
    endDate?: string,
    priority?: string,
    slaCompliance?: string
  ) {
    const range = this.parseDateRange(dateRange, startDate, endDate);

    // Check if user is admin or management (has broader access)
    const user = await User.findByPk(userId);
    const isAdmin = user?.hasManagementAccess() || false;

    // For regular users: return empty (only admins should see this)
    if (!isAdmin) {
      return {
        performance: [],
        currentPage: page,
        totalPages: 0,
        totalRecords: 0,
        limit
      };
    }

    // Calculate offset
    const offset = (page - 1) * limit;

    // Build filter conditions
    const replacements: any = { start: range.start, end: range.end };
    let priorityFilter = '';
    let slaFilter = '';

    if (priority && priority !== 'all') {
      priorityFilter = `AND wf.priority = :priority`;
      replacements.priority = priority.toUpperCase();
    }

    // SLA filter logic - will be applied in main query
    if (slaCompliance && slaCompliance !== 'all') {
      if (slaCompliance === 'breached') {
        slaFilter = `AND al.tat_breached = true`;
      } else if (slaCompliance === 'compliant') {
        slaFilter = `AND (al.tat_breached = false OR (al.tat_breached IS NULL AND al.elapsed_hours < al.tat_hours))`;
      }
    }

    // Get total count - only count distinct approvers who have completed approvals
    // IMPORTANT: WHERE conditions must match the main query to avoid pagination mismatch
    const countResult = await sequelize.query(`
      SELECT COUNT(*) as total
      FROM (
        SELECT DISTINCT al.approver_id
        FROM approval_levels al
        INNER JOIN workflow_requests wf ON al.request_id = wf.request_id
        WHERE al.action_date BETWEEN :start AND :end
          AND al.status IN ('APPROVED', 'REJECTED')
          AND al.action_date IS NOT NULL
          AND al.level_start_time IS NOT NULL
          AND al.tat_hours > 0
          AND al.approver_id IS NOT NULL
          AND al.elapsed_hours IS NOT NULL
          AND al.elapsed_hours >= 0
          ${priorityFilter}
          ${slaFilter}
        GROUP BY al.approver_id
        HAVING COUNT(DISTINCT al.level_id) > 0
      ) AS distinct_approvers
    `, {
      replacements,
      type: QueryTypes.SELECT
    });

    const totalRecords = Number((countResult[0] as any)?.total || 0);
    const totalPages = Math.ceil(totalRecords / limit);

    // Get approver performance metrics (approved/rejected in date range)
    // IMPORTANT: This must only count approvals where the user acted as APPROVER, not as INITIATOR
    // TAT % = (Requests approved within TAT / Total requests approved) * 100
    // Check if elapsed_hours < tat_hours to determine if within TAT (exact match = within but not ideal)
    // Exclude records with NULL or 0 elapsed_hours (invalid data)
    const approverMetrics = await sequelize.query(`
      SELECT
        al.approver_id,
        al.approver_name,
        COUNT(DISTINCT al.level_id)::int AS total_approved,
        COUNT(DISTINCT CASE 
          WHEN al.status = 'APPROVED' 
          THEN al.level_id 
        END)::int AS approved_count,
        COUNT(DISTINCT CASE 
          WHEN al.status = 'REJECTED' 
          THEN al.level_id 
        END)::int AS rejected_count,
        COUNT(DISTINCT CASE 
          WHEN wf.status = 'CLOSED'
          THEN al.level_id 
        END)::int AS closed_count,
        COUNT(DISTINCT CASE 
          WHEN al.tat_breached = false 
            OR (al.tat_breached IS NULL AND al.elapsed_hours < al.tat_hours)
          THEN al.level_id 
        END)::int AS within_tat_count,
        COUNT(DISTINCT CASE 
          WHEN al.tat_breached = true
          THEN al.level_id 
        END)::int AS breached_count,
        ROUND(
          ((COUNT(DISTINCT CASE 
            WHEN al.tat_breached = false 
              OR (al.tat_breached IS NULL AND al.elapsed_hours < al.tat_hours)
            THEN al.level_id 
          END)::numeric / NULLIF(COUNT(DISTINCT al.level_id), 0)) * 100)::numeric, 
          0
        )::int AS tat_compliance_percent,
        ROUND(AVG(COALESCE(al.elapsed_hours, 0))::numeric, 1) AS avg_response_hours
      FROM approval_levels al
      INNER JOIN workflow_requests wf ON al.request_id = wf.request_id
      WHERE al.action_date BETWEEN :start AND :end
        AND al.status IN ('APPROVED', 'REJECTED')
        AND al.action_date IS NOT NULL
        AND al.level_start_time IS NOT NULL
        AND al.tat_hours > 0
        AND al.approver_id IS NOT NULL
        AND al.elapsed_hours IS NOT NULL
        AND al.elapsed_hours >= 0
        ${priorityFilter}
        ${slaFilter}
      GROUP BY al.approver_id, al.approver_name
      HAVING COUNT(DISTINCT al.level_id) > 0
      ORDER BY 
        tat_compliance_percent DESC,  -- Higher TAT compliance first (100% > 90% > 80%)
        avg_response_hours ASC,       -- Faster response time next (5h < 10h < 20h)
        total_approved DESC           -- More approvals as tie-breaker
      LIMIT :limit OFFSET :offset
    `, {
      replacements: { ...replacements, limit, offset },
      type: QueryTypes.SELECT
    });

    // Get current pending counts and calculate TAT compliance including pending requests that have breached
    const approverIds = approverMetrics.map((a: any) => a.approver_id);
    let pendingCounts: any[] = [];
    let pendingBreachData: any[] = [];

    if (approverIds.length > 0) {
      // Find all pending/in-progress approval levels and get the first (current) level for each request
      // This should match the logic from listOpenForMe to ensure consistency
      pendingCounts = await sequelize.query(`
        WITH pending_levels AS (
          SELECT DISTINCT ON (al.request_id)
            al.request_id,
            al.approver_id,
            al.level_id,
            al.level_number,
            al.level_start_time,
            al.tat_hours,
            wf.priority,
            wf.initiator_id
          FROM approval_levels al
          JOIN workflow_requests wf ON al.request_id = wf.request_id
          WHERE al.status IN ('PENDING', 'IN_PROGRESS')
            AND wf.status IN ('PENDING', 'IN_PROGRESS')
            AND wf.is_draft = false
            AND al.level_start_time IS NOT NULL
            AND al.tat_hours > 0
            AND wf.initiator_id != al.approver_id
          ORDER BY al.request_id, al.level_number ASC
        )
        SELECT
          approver_id,
          COUNT(DISTINCT level_id)::int AS pending_count,
          json_agg(json_build_object(
            'level_id', level_id,
            'level_start_time', level_start_time,
            'tat_hours', tat_hours,
            'priority', priority
          )) AS pending_levels_data
        FROM pending_levels
        WHERE approver_id IN (:approverIds)
        GROUP BY approver_id
      `, {
        replacements: { approverIds },
        type: QueryTypes.SELECT
      });

      // Calculate SLA status for pending levels to determine breaches
      const { calculateSLAStatus } = await import('@utils/tatTimeUtils');
      pendingBreachData = await Promise.all(
        pendingCounts.map(async (pc: any) => {
          const levels = pc.pending_levels_data || [];
          let breachedCount = 0;
          let compliantCount = 0;

          for (const level of levels) {
            if (level.level_start_time && level.tat_hours > 0) {
              try {
                const priority = (level.priority || 'standard').toLowerCase();
                const calculated = await calculateSLAStatus(
                  level.level_start_time,
                  level.tat_hours,
                  priority,
                  null // No end date for pending requests
                );

                // Mark as breached if percentageUsed >= 100
                if (calculated.percentageUsed >= 100) {
                  breachedCount++;
                } else {
                  compliantCount++;
                }
              } catch (error) {
                logger.error(`[Dashboard] Error calculating SLA for pending level ${level.level_id}:`, error);
                // Default to compliant if calculation fails
                compliantCount++;
              }
            }
          }

          return {
            approver_id: pc.approver_id,
            pending_count: pc.pending_count || 0,
            pending_breached: breachedCount,
            pending_compliant: compliantCount
          };
        })
      );
    }

    // Create maps for quick lookup
    const pendingCountMap = new Map<string, number>();
    const pendingBreachedMap = new Map<string, number>();
    const pendingCompliantMap = new Map<string, number>();

    pendingBreachData.forEach((pb: any) => {
      pendingCountMap.set(pb.approver_id, pb.pending_count || 0);
      pendingBreachedMap.set(pb.approver_id, pb.pending_breached || 0);
      pendingCompliantMap.set(pb.approver_id, pb.pending_compliant || 0);
    });

    return {
      performance: approverMetrics.map((a: any) => {
        // Get pending breach data
        const pendingBreached = pendingBreachedMap.get(a.approver_id) || 0;
        const pendingCompliant = pendingCompliantMap.get(a.approver_id) || 0;

        // Calculate overall TAT compliance including pending requests
        // Completed: within_tat_count (compliant) + breached_count (breached)
        // Pending: pending_compliant (compliant) + pending_breached (breached)
        const totalCompliant = a.within_tat_count + pendingCompliant;
        const totalBreached = a.breached_count + pendingBreached;
        const totalRequests = a.total_approved + pendingBreached + pendingCompliant;

        // Calculate TAT compliance percentage including pending requests
        // Use Math.floor to ensure consistent rounding (matches detail screen logic)
        // This prevents 79.5% from rounding differently in different places
        const tatCompliancePercent = totalRequests > 0
          ? Math.floor((totalCompliant / totalRequests) * 100)
          : (a.tat_compliance_percent || 0); // Fallback to original if no pending requests

        return {
          approverId: a.approver_id,
          approverName: a.approver_name,
          totalApproved: a.total_approved,
          approvedCount: a.approved_count,
          rejectedCount: a.rejected_count,
          closedCount: a.closed_count,
          tatCompliancePercent,
          avgResponseHours: parseFloat(a.avg_response_hours || 0),
          pendingCount: pendingCountMap.get(a.approver_id) || 0,
          withinTatCount: a.within_tat_count,
          breachedCount: a.breached_count
        };
      }),
      currentPage: page,
      totalPages,
      totalRecords,
      limit
    };
  }

  /**
   * Get recent activity feed with pagination
   */
  async getRecentActivity(userId: string, page: number = 1, limit: number = 10, viewAsUser?: boolean) {
    // Check if user is admin or management (has broader access)
    // If viewAsUser is true, treat as normal user even if admin
    const user = await User.findByPk(userId);
    const isAdmin = viewAsUser ? false : (user?.hasManagementAccess() || false);

    // For regular users: only activities from their initiated requests OR where they're a participant
    let whereClause = isAdmin ? '' : `
      AND (
        wf.initiator_id = :userId
        OR EXISTS (
          SELECT 1 FROM participants p 
          WHERE p.request_id = a.request_id 
          AND p.user_id = :userId
        )
      )
    `;

    // Calculate offset
    const offset = (page - 1) * limit;

    // Get total count
    const countResult = await sequelize.query(`
      SELECT COUNT(*) as total
      FROM activities a
      JOIN workflow_requests wf ON a.request_id = wf.request_id
      WHERE a.created_at >= NOW() - INTERVAL '7 days'
      ${whereClause}
    `, {
      replacements: { userId },
      type: QueryTypes.SELECT
    });

    const totalRecords = Number((countResult[0] as any).total);
    const totalPages = Math.ceil(totalRecords / limit);

    // Get paginated activities
    const activities = await sequelize.query(`
      SELECT
        a.activity_id,
        a.request_id,
        a.activity_type AS type,
        a.activity_description,
        a.activity_category,
        a.user_id,
        a.user_name,
        a.created_at AS timestamp,
        wf.request_number,
        wf.title AS request_title,
        wf.priority
      FROM activities a
      JOIN workflow_requests wf ON a.request_id = wf.request_id
      WHERE a.created_at >= NOW() - INTERVAL '7 days'
      ${whereClause}
      ORDER BY a.created_at DESC
      LIMIT :limit OFFSET :offset
    `, {
      replacements: { userId, limit, offset },
      type: QueryTypes.SELECT
    });

    return {
      activities: activities.map((a: any) => ({
        activityId: a.activity_id,
        requestId: a.request_id,
        requestNumber: a.request_number,
        requestTitle: a.request_title,
        type: a.type,
        action: a.activity_description || a.type,
        details: a.activity_category,
        userId: a.user_id,
        userName: a.user_name,
        timestamp: a.timestamp,
        priority: (a.priority || '').toLowerCase()
      })),
      currentPage: page,
      totalPages,
      totalRecords,
      limit
    };
  }

  /**
   * Get critical requests (breached TAT or approaching deadline) with pagination
   */
  async getCriticalRequests(userId: string, page: number = 1, limit: number = 10, viewAsUser?: boolean) {
    // Check if user is admin or management (has broader access)
    // If viewAsUser is true, treat as normal user even if admin
    const user = await User.findByPk(userId);
    const isAdmin = viewAsUser ? false : (user?.hasManagementAccess() || false);

    // For regular users: show only requests where they are current approver (awaiting their approval)
    // For admins: show all critical requests organization-wide
    let whereClause = `
      WHERE wf.status IN ('PENDING', 'IN_PROGRESS')
      AND wf.is_draft = false
      ${!isAdmin ? `AND EXISTS (
        SELECT 1 FROM approval_levels al
        WHERE al.request_id = wf.request_id
        AND al.approver_id = :userId
        AND al.level_number = wf.current_level
        AND al.status = 'IN_PROGRESS'
      )` : ''}
    `;

    // For TAT Breach Report, only show requests where the CURRENT level has breached
    // This ensures we don't show requests where a previous level breached but current level is fine
    const criticalCondition = `
      AND EXISTS (
        SELECT 1 
        FROM tat_alerts ta
        INNER JOIN approval_levels al_current ON ta.level_id = al_current.level_id
        WHERE ta.request_id = wf.request_id
        AND ta.is_breached = true
        AND al_current.level_number = wf.current_level
        AND al_current.status = 'IN_PROGRESS'
      )
    `;

    // Calculate offset
    const offset = (page - 1) * limit;

    // Get total count
    const countResult = await sequelize.query(`
      SELECT COUNT(*) as total
      FROM workflow_requests wf
      ${whereClause}
      ${criticalCondition}
    `, {
      replacements: { userId },
      type: QueryTypes.SELECT
    });

    const totalRecords = Number((countResult[0] as any).total);
    const totalPages = Math.ceil(totalRecords / limit);

    const criticalRequests = await sequelize.query(`
      SELECT
        wf.request_id,
        wf.request_number,
        wf.title,
        wf.priority,
        wf.status,
        wf.current_level,
        wf.total_levels,
        wf.submission_date,
        wf.total_tat_hours,
        COALESCE(u.department, 'Unknown') AS department,
        al.approver_name AS current_approver_name,
        al.approver_email AS current_approver_email,
        al.approver_id AS current_approver_id,
        (
          SELECT COUNT(*)::int
          FROM tat_alerts ta
          INNER JOIN approval_levels al_breach ON ta.level_id = al_breach.level_id
          WHERE ta.request_id = wf.request_id
          AND ta.is_breached = true
          AND al_breach.level_number = wf.current_level
        ) AS breach_count,
        (
          SELECT ta.alert_sent_at
          FROM tat_alerts ta
          INNER JOIN approval_levels al_breach ON ta.level_id = al_breach.level_id
          WHERE ta.request_id = wf.request_id
          AND ta.is_breached = true
          AND al_breach.level_number = wf.current_level
          ORDER BY ta.alert_sent_at DESC
          LIMIT 1
        ) AS first_breach_time,
        (
          SELECT ta.tat_hours_elapsed - ta.tat_hours_allocated
          FROM tat_alerts ta
          INNER JOIN approval_levels al_breach ON ta.level_id = al_breach.level_id
          WHERE ta.request_id = wf.request_id
          AND ta.is_breached = true
          AND al_breach.level_number = wf.current_level
          ORDER BY ta.alert_sent_at DESC
          LIMIT 1
        ) AS breach_hours,
        (
          SELECT al.tat_hours
          FROM approval_levels al
          WHERE al.request_id = wf.request_id
          AND al.level_number = wf.current_level
          LIMIT 1
        ) AS current_level_tat_hours,
        (
          SELECT al.level_start_time
          FROM approval_levels al
          WHERE al.request_id = wf.request_id
          AND al.level_number = wf.current_level
          LIMIT 1
        ) AS current_level_start_time
      FROM workflow_requests wf
      LEFT JOIN users u ON wf.initiator_id = u.user_id
      LEFT JOIN approval_levels al ON al.request_id = wf.request_id
        AND al.level_number = wf.current_level
        AND al.status = 'IN_PROGRESS'
      ${whereClause}
      ${criticalCondition}
      ORDER BY 
        CASE WHEN wf.priority = 'EXPRESS' THEN 1 ELSE 2 END,
        breach_count DESC,
        wf.created_at ASC
      LIMIT :limit OFFSET :offset
    `, {
      replacements: { userId, limit, offset },
      type: QueryTypes.SELECT
    });

    // Calculate working hours TAT for each critical request's current level
    // Filter out requests where current level hasn't actually breached (TAT < 100%)
    const criticalWithSLA = await Promise.all(criticalRequests.map(async (req: any) => {
      const priority = (req.priority || 'standard').toLowerCase();
      const currentLevelTatHours = parseFloat(req.current_level_tat_hours) || 0;
      const currentLevelStartTime = req.current_level_start_time;

      let currentLevelRemainingHours = currentLevelTatHours;
      let currentLevelElapsedHours = 0;
      let tatPercentageUsed = 0;

      if (currentLevelStartTime && currentLevelTatHours > 0) {
        try {
          // Use working hours calculation for current level
          const slaData = await calculateSLAStatus(currentLevelStartTime, currentLevelTatHours, priority);
          currentLevelRemainingHours = slaData.remainingHours;
          currentLevelElapsedHours = slaData.elapsedHours;
          tatPercentageUsed = slaData.percentageUsed;
        } catch (error) {
          logger.error(`[Dashboard] Error calculating SLA for critical request ${req.request_id}:`, error);
        }
      }

      // Trust the is_breached flag from tat_alerts table - if it's marked as breached, include it
      // The tat_alerts.is_breached flag is set by the TAT monitoring system and should be authoritative
      // Only filter out if we have a valid TAT calculation AND it's clearly not breached (elapsed < TAT)
      // BUT if breach_count > 0 from the database, we trust that over the calculation to avoid timing issues
      // This ensures consistency between Dashboard and All Requests screen
      const hasBreachFlag = (req.breach_count || 0) > 0;
      if (currentLevelTatHours > 0 && currentLevelElapsedHours < currentLevelTatHours && !hasBreachFlag) {
        // Only skip if no breach flag in DB AND calculation shows not breached
        // If hasBreachFlag is true, trust the database even if calculation hasn't caught up yet
        return null; // Skip this request - not actually breached
      }

      // Calculate breach time (working hours since first breach)
      let breachTime = 0;
      if (req.first_breach_time) {
        // Use working hours calculation instead of calendar hours
        // This ensures breach time is calculated in working hours, not calendar hours
        try {
          const { calculateElapsedWorkingHours } = await import('@utils/tatTimeUtils');
          breachTime = await calculateElapsedWorkingHours(
            req.first_breach_time,
            new Date(),
            priority
          );
        } catch (error) {
          logger.error(`[Dashboard] Error calculating working hours for breach time:`, error);
          // Fallback to calendar hours if working hours calculation fails
          const breachDate = dayjs(req.first_breach_time);
          const now = dayjs();
          breachTime = now.diff(breachDate, 'hour', true);
        }
      } else if (req.breach_hours && req.breach_hours > 0) {
        // breach_hours is already in working hours from tat_alerts table
        breachTime = req.breach_hours;
      } else if (currentLevelElapsedHours > currentLevelTatHours) {
        // Calculate breach time from elapsed hours (already in working hours)
        breachTime = currentLevelElapsedHours - currentLevelTatHours;
      }

      // Get breach reason from approval_levels table
      let breachReason = 'TAT Exceeded';
      try {
        const levelWithReason = await sequelize.query(`
          SELECT al.breach_reason
          FROM approval_levels al
          WHERE al.request_id = :requestId
            AND al.level_number = :currentLevel
          LIMIT 1
        `, {
          replacements: { requestId: req.request_id, currentLevel: req.current_level },
          type: QueryTypes.SELECT
        });

        if (levelWithReason && levelWithReason.length > 0 && (levelWithReason[0] as any).breach_reason) {
          breachReason = (levelWithReason[0] as any).breach_reason;
        } else {
          // Fallback to default reason
          if (req.breach_count > 0) {
            if (priority === 'express') {
              breachReason = 'Express Priority - TAT Exceeded';
            } else {
              breachReason = 'Standard TAT Breach';
            }
          } else if (req.priority === 'EXPRESS') {
            breachReason = 'Express Priority - High Risk';
          }
        }
      } catch (error) {
        logger.warn('[Dashboard] Error fetching breach reason from approval_levels, using default');
        // Use default reason on error
        if (req.breach_count > 0) {
          if (priority === 'express') {
            breachReason = 'Express Priority - TAT Exceeded';
          } else {
            breachReason = 'Standard TAT Breach';
          }
        } else if (req.priority === 'EXPRESS') {
          breachReason = 'Express Priority - High Risk';
        }
      }

      return {
        requestId: req.request_id,
        requestNumber: req.request_number,
        title: req.title,
        priority,
        status: (req.status || '').toLowerCase(),
        currentLevel: req.current_level,
        totalLevels: req.total_levels,
        submissionDate: req.submission_date,
        totalTATHours: currentLevelRemainingHours, // Current level remaining hours
        originalTATHours: currentLevelTatHours, // Original TAT hours allocated for current level
        breachCount: req.breach_count || 0,
        isCritical: true, // Only true breaches reach here
        department: req.department || 'Unknown',
        approver: req.current_approver_name || req.current_approver_email || 'N/A',
        approverId: req.current_approver_id || null,
        approverEmail: req.current_approver_email || null,
        breachTime: breachTime,
        breachReason: breachReason
      };
    }));

    // Filter out null values (requests that didn't actually breach)
    const filteredCritical = criticalWithSLA.filter(req => req !== null);

    // Since we now trust breach_count from database (if > 0, we include it regardless of calculation),
    // we should filter very few (if any) requests. The original database count should be accurate.
    // Only adjust totalRecords if we filtered out requests from current page (for edge cases)
    // In practice, with the new logic trusting breach_count, filtering should be minimal to none
    let adjustedTotalRecords = totalRecords;
    const filteredOutFromPage = criticalRequests.length - filteredCritical.length;
    if (filteredOutFromPage > 0) {
      // If we filtered out items from current page, estimate adjustment across all pages
      // This is an approximation since we can't recalculate without fetching all pages
      const filterRatio = filteredCritical.length / Math.max(1, criticalRequests.length);
      adjustedTotalRecords = Math.max(filteredCritical.length, Math.round(totalRecords * filterRatio));
    }
    const adjustedTotalPages = Math.ceil(adjustedTotalRecords / limit);

    return {
      criticalRequests: filteredCritical,
      currentPage: page,
      totalPages: adjustedTotalPages,
      totalRecords: adjustedTotalRecords,
      limit
    };
  }

  /**
   * Get upcoming deadlines with pagination
   */
  async getUpcomingDeadlines(userId: string, page: number = 1, limit: number = 10, viewAsUser?: boolean) {
    // Check if user is admin or management (has broader access)
    // If viewAsUser is true, treat as normal user even if admin
    const user = await User.findByPk(userId);
    const isAdmin = viewAsUser ? false : (user?.hasManagementAccess() || false);

    // For regular users: only show CURRENT LEVEL where they are the approver
    // For admins: show all current active levels
    let whereClause = `
      WHERE wf.status IN ('PENDING', 'IN_PROGRESS')
      AND wf.is_draft = false
      AND al.status = 'IN_PROGRESS'
      AND al.level_number = wf.current_level
      ${!isAdmin ? `AND al.approver_id = :userId` : ''}
    `;

    // Calculate offset
    const offset = (page - 1) * limit;

    // Get total count
    const countResult = await sequelize.query(`
      SELECT COUNT(*) as total
      FROM approval_levels al
      JOIN workflow_requests wf ON al.request_id = wf.request_id
      ${whereClause}
    `, {
      replacements: { userId },
      type: QueryTypes.SELECT
    });

    const totalRecords = Number((countResult[0] as any).total);
    const totalPages = Math.ceil(totalRecords / limit);

    const deadlines = await sequelize.query(`
      SELECT
        al.level_id,
        al.request_id,
        al.level_number,
        al.approver_name,
        al.approver_email,
        al.tat_hours,
        al.level_start_time,
        wf.request_number,
        wf.title AS request_title,
        wf.priority,
        wf.current_level,
        wf.total_levels
      FROM approval_levels al
      JOIN workflow_requests wf ON al.request_id = wf.request_id
      ${whereClause}
      ORDER BY al.level_start_time ASC
      LIMIT :limit OFFSET :offset
    `, {
      replacements: { userId, limit, offset },
      type: QueryTypes.SELECT
    });

    // Calculate working hours TAT for each deadline
    const deadlinesWithSLA = await Promise.all(deadlines.map(async (d: any) => {
      const priority = (d.priority || 'standard').toLowerCase();
      const tatHours = parseFloat(d.tat_hours) || 0;
      const levelStartTime = d.level_start_time;

      let elapsedHours = 0;
      let remainingHours = tatHours;
      let tatPercentageUsed = 0;

      if (levelStartTime && tatHours > 0) {
        try {
          // Use working hours calculation (same as RequestDetail screen)
          const slaData = await calculateSLAStatus(levelStartTime, tatHours, priority);
          elapsedHours = slaData.elapsedHours;
          remainingHours = slaData.remainingHours;
          tatPercentageUsed = slaData.percentageUsed;
        } catch (error) {
          logger.error(`[Dashboard] Error calculating SLA for level ${d.level_id}:`, error);
        }
      }

      return {
        levelId: d.level_id,
        requestId: d.request_id,
        requestNumber: d.request_number,
        requestTitle: d.request_title,
        levelNumber: d.level_number,
        currentLevel: d.current_level,
        totalLevels: d.total_levels,
        approverName: d.approver_name,
        approverEmail: d.approver_email,
        tatHours,
        elapsedHours,
        remainingHours,
        tatPercentageUsed,
        levelStartTime,
        priority
      };
    }));

    // Sort by TAT percentage used (descending)
    const sortedDeadlines = deadlinesWithSLA.sort((a, b) => b.tatPercentageUsed - a.tatPercentageUsed);

    return {
      deadlines: sortedDeadlines,
      currentPage: page,
      totalPages,
      totalRecords,
      limit
    };
  }

  /**
   * Get department-wise statistics
   */
  async getDepartmentStats(userId: string, dateRange?: string, startDate?: string, endDate?: string) {
    const range = this.parseDateRange(dateRange, startDate, endDate);

    // Check if user is admin or management (has broader access)
    const user = await User.findByPk(userId);
    const isAdmin = user?.hasManagementAccess() || false;

    // For regular users: only their initiated requests
    // Use submission_date instead of created_at to filter by actual submission date
    let whereClause = `
      WHERE wf.submission_date BETWEEN :start AND :end
      AND wf.is_draft = false
      AND wf.submission_date IS NOT NULL
      ${!isAdmin ? `AND wf.initiator_id = :userId` : ''}
    `;

    const deptStats = await sequelize.query(`
      SELECT
        COALESCE(u.department, 'Unknown') AS department,
        COUNT(*)::int AS total_requests,
        COUNT(CASE WHEN wf.status = 'APPROVED' THEN 1 END)::int AS approved,
        COUNT(CASE WHEN wf.status = 'REJECTED' THEN 1 END)::int AS rejected,
        COUNT(CASE WHEN wf.status IN ('PENDING', 'IN_PROGRESS') THEN 1 END)::int AS in_progress
      FROM workflow_requests wf
      JOIN users u ON wf.initiator_id = u.user_id
      ${whereClause}
      GROUP BY u.department
      ORDER BY total_requests DESC
      LIMIT 10
    `, {
      replacements: { start: range.start, end: range.end, userId },
      type: QueryTypes.SELECT
    });

    return deptStats.map((d: any) => ({
      department: d.department,
      totalRequests: d.total_requests,
      approved: d.approved,
      rejected: d.rejected,
      inProgress: d.in_progress,
      approvalRate: d.total_requests > 0 ? Math.round((d.approved / d.total_requests) * 100) : 0
    }));
  }

  /**
   * Get list of unique departments from users (metadata for filtering)
   * Returns all departments that have at least one user, ordered alphabetically
   */
  async getDepartments(userId: string): Promise<string[]> {
    // Check if user is admin or management (has broader access)
    const user = await User.findByPk(userId);
    const isAdmin = user?.hasManagementAccess() || false;

    // For regular users: only departments from their requests
    // For admin/management: all departments in the system
    let whereClause = '';
    if (!isAdmin) {
      // Get departments from requests initiated by this user
      whereClause = `
        WHERE u.department IS NOT NULL
        AND u.department != ''
        AND EXISTS (
          SELECT 1 FROM workflow_requests wf 
          WHERE wf.initiator_id = u.user_id
        )
      `;
    } else {
      // Admin/Management: get all departments that have at least one user
      whereClause = `
        WHERE u.department IS NOT NULL
        AND u.department != ''
      `;
    }

    const departments = await sequelize.query(`
      SELECT DISTINCT u.department
      FROM users u
      ${whereClause}
      ORDER BY u.department ASC
    `, {
      replacements: !isAdmin ? { userId } : {},
      type: QueryTypes.SELECT
    });

    // Extract department names and filter out null/empty values
    const deptList = (departments as any[])
      .map((d: any) => d.department)
      .filter((dept: string | null) => dept && dept.trim() !== '');

    return [...new Set(deptList)]; // Remove duplicates and return
  }


  /**
   * Get priority distribution statistics
   */
  async getPriorityDistribution(userId: string, dateRange?: string, startDate?: string, endDate?: string) {
    const range = this.parseDateRange(dateRange, startDate, endDate);

    // Check if user is admin or management (has broader access)
    const user = await User.findByPk(userId);
    const isAdmin = user?.hasManagementAccess() || false;

    // For regular users: only their initiated requests
    // Use submission_date instead of created_at to filter by actual submission date
    let whereClause = `
      WHERE wf.submission_date BETWEEN :start AND :end
      AND wf.is_draft = false
      AND wf.submission_date IS NOT NULL
      ${!isAdmin ? `AND wf.initiator_id = :userId` : ''}
    `;

    // Get all requests for counting (total, approved, breached)
    const allRequests = await sequelize.query(`
      SELECT
        wf.request_id,
        wf.priority,
        wf.status,
        CASE WHEN EXISTS (
          SELECT 1 FROM tat_alerts ta 
          WHERE ta.request_id = wf.request_id 
          AND ta.is_breached = true
        ) THEN 1 ELSE 0 END AS is_breached
      FROM workflow_requests wf
      ${whereClause}
    `, {
      replacements: { start: range.start, end: range.end, userId },
      type: QueryTypes.SELECT
    });

    // Get only CLOSED requests for cycle time calculation (ignore APPROVED and REJECTED)
    let whereClauseCompleted = `
      WHERE wf.status = 'CLOSED'
      AND wf.is_draft = false
      AND wf.submission_date IS NOT NULL
      AND (
        (wf.closure_date IS NOT NULL AND wf.closure_date BETWEEN :start AND :end)
        OR (wf.closure_date IS NULL AND wf.updated_at BETWEEN :start AND :end)
      )
      ${!isAdmin ? `AND wf.initiator_id = :userId` : ''}
    `;

    const completedRequests = await sequelize.query(`
      SELECT
        wf.request_id,
        wf.priority,
        wf.submission_date,
        wf.closure_date,
        wf.updated_at
      FROM workflow_requests wf
      ${whereClauseCompleted}
    `, {
      replacements: { start: range.start, end: range.end, userId },
      type: QueryTypes.SELECT
    });

    // Group by priority and calculate working hours for each
    const { calculateElapsedWorkingHours } = await import('@utils/tatTimeUtils');
    const priorityMap = new Map<string, {
      totalCount: number;
      cycleTimes: number[];
      approvedCount: number;
      breachedCount: number;
    }>();

    // First, count all requests by priority
    for (const req of allRequests as any) {
      const priority = (req.priority || 'STANDARD').toLowerCase();

      if (!priorityMap.has(priority)) {
        priorityMap.set(priority, {
          totalCount: 0,
          cycleTimes: [],
          approvedCount: 0,
          breachedCount: 0
        });
      }

      const stats = priorityMap.get(priority)!;
      stats.totalCount++;

      if (req.status === 'APPROVED') {
        stats.approvedCount++;
      }

      if (req.is_breached === 1) {
        stats.breachedCount++;
      }
    }

    // Then, calculate cycle time only for completed requests
    for (const req of completedRequests as any) {
      const priority = (req.priority || 'STANDARD').toLowerCase();

      if (!priorityMap.has(priority)) {
        // This shouldn't happen, but handle it gracefully
        priorityMap.set(priority, {
          totalCount: 0,
          cycleTimes: [],
          approvedCount: 0,
          breachedCount: 0
        });
      }

      const stats = priorityMap.get(priority)!;

      // Calculate cycle time using working hours
      const submissionDate = req.submission_date;
      const completionDate = req.closure_date || req.updated_at;

      if (submissionDate && completionDate) {
        try {
          const elapsedHours = await calculateElapsedWorkingHours(
            submissionDate,
            completionDate,
            priority
          );
          stats.cycleTimes.push(elapsedHours);
        } catch (error) {
          logger.error(`[Dashboard] Error calculating cycle time for request ${req.request_id}:`, error);
        }
      }
    }

    // Calculate averages per priority (rounded to 2 decimal places for accuracy)
    return Array.from(priorityMap.entries()).map(([priority, stats]) => {
      const avgCycleTimeHours = stats.cycleTimes.length > 0
        ? Math.round((stats.cycleTimes.reduce((sum, hours) => sum + hours, 0) / stats.cycleTimes.length) * 100) / 100
        : 0;

      return {
        priority,
        totalCount: stats.totalCount,
        avgCycleTimeHours,
        approvedCount: stats.approvedCount,
        breachedCount: stats.breachedCount,
        complianceRate: stats.totalCount > 0 ? Math.round(((stats.totalCount - stats.breachedCount) / stats.totalCount) * 100) : 0
      };
    });
  }

  /**
   * Get Request Lifecycle Report with full timeline and TAT compliance
   */
  async getLifecycleReport(userId: string, page: number = 1, limit: number = 50, dateRange?: string, startDate?: string, endDate?: string) {
    const user = await User.findByPk(userId);
    const isAdmin = user?.hasManagementAccess() || false;

    const offset = (page - 1) * limit;

    // Parse date range if provided
    let dateFilter = '';
    const replacements: any = { userId, limit, offset };

    if (dateRange) {
      const dateFilterObj = this.parseDateRange(dateRange, startDate, endDate);
      dateFilter = `
        AND wf.submission_date IS NOT NULL
        AND wf.submission_date >= :dateStart
        AND wf.submission_date <= :dateEnd
      `;
      replacements.dateStart = dateFilterObj.start;
      replacements.dateEnd = dateFilterObj.end;
    }

    // For regular users: only their initiated requests or where they're participants
    let whereClause = isAdmin ? '' : `
      AND (
        wf.initiator_id = :userId
        OR EXISTS (
          SELECT 1 FROM participants p 
          WHERE p.request_id = wf.request_id 
          AND p.user_id = :userId
        )
      )
    `;

    // Get total count
    const countResult = await sequelize.query(`
      SELECT COUNT(*) as total
      FROM workflow_requests wf
      WHERE wf.is_draft = false
      ${dateFilter}
      ${whereClause}
    `, {
      replacements,
      type: QueryTypes.SELECT
    });

    const totalRecords = Number((countResult[0] as any).total);
    const totalPages = Math.ceil(totalRecords / limit);

    // Get requests with initiator name and current level name
    const requests = await sequelize.query(`
      SELECT
        wf.request_id,
        wf.request_number,
        wf.title,
        wf.priority,
        wf.status,
        wf.submission_date,
        wf.closure_date,
        wf.current_level,
        wf.total_levels,
        wf.total_tat_hours,
        wf.created_at,
        wf.updated_at,
        u.display_name AS initiator_name,
        u.email AS initiator_email,
        al.level_name AS current_stage_name,
        al.approver_name AS current_approver_name,
        (
          SELECT COUNT(*) 
          FROM tat_alerts ta 
          WHERE ta.request_id = wf.request_id 
          AND ta.is_breached = true
        ) AS breach_count
      FROM workflow_requests wf
      LEFT JOIN users u ON wf.initiator_id = u.user_id
      LEFT JOIN approval_levels al ON al.request_id = wf.request_id 
        AND al.level_number = wf.current_level
      WHERE wf.is_draft = false
      ${dateFilter}
      ${whereClause}
      ORDER BY wf.updated_at DESC
      LIMIT :limit OFFSET :offset
    `, {
      replacements,
      type: QueryTypes.SELECT
    });

    // Calculate overall TAT and compliance for each request
    const { calculateElapsedWorkingHours } = await import('@utils/tatTimeUtils');
    const lifecycleData = await Promise.all(requests.map(async (req: any) => {
      const submissionDate = req.submission_date;
      const endDate = req.closure_date || new Date();
      const priority = (req.priority || 'STANDARD').toLowerCase();

      // Calculate elapsed working hours
      const elapsedHours = submissionDate
        ? await calculateElapsedWorkingHours(submissionDate, endDate, priority)
        : 0;

      // Determine TAT compliance
      const isBreached = req.breach_count > 0;
      const status = isBreached ? 'Delayed' : 'On Time';

      return {
        requestId: req.request_id,
        requestNumber: req.request_number,
        title: req.title,
        priority: (req.priority || 'STANDARD').toLowerCase(),
        status,
        initiatorName: req.initiator_name || req.initiator_email || 'Unknown',
        initiatorEmail: req.initiator_email,
        submissionDate: req.submission_date,
        closureDate: req.closure_date,
        currentLevel: req.current_level,
        totalLevels: req.total_levels,
        currentStageName: req.current_stage_name || `Level ${req.current_level}`,
        currentApproverName: req.current_approver_name,
        overallTATHours: elapsedHours,
        totalTATHours: parseFloat(req.total_tat_hours || 0),
        breachCount: parseInt(req.breach_count || 0),
        createdAt: req.created_at,
        updatedAt: req.updated_at
      };
    }));

    return {
      lifecycleData,
      currentPage: page,
      totalPages,
      totalRecords,
      limit
    };
  }

  /**
   * Get enhanced User Activity Log Report with IP and user agent
   */
  async getActivityLogReport(
    userId: string,
    page: number = 1,
    limit: number = 50,
    dateRange?: string,
    filterUserId?: string,
    filterType?: string,
    filterCategory?: string,
    filterSeverity?: string,
    startDate?: string,
    endDate?: string
  ) {
    const user = await User.findByPk(userId);
    const isAdmin = user?.hasManagementAccess() || false;

    const range = this.parseDateRange(dateRange, startDate, endDate);
    const offset = (page - 1) * limit;

    // For admins: no restrictions - can see ALL activities from ALL users (including login activities)
    // For regular users: only activities from their initiated requests OR where they're a participant
    // Also include system events (like login) where the user_id matches
    let whereClause = isAdmin ? '' : `
      AND (
        a.user_id = :userId
        OR wf.initiator_id = :userId
        OR EXISTS (
          SELECT 1 FROM participants p 
          WHERE p.request_id = a.request_id 
          AND p.user_id = :userId
        )
      )
    `;

    // Add filters
    if (filterUserId) {
      whereClause += ` AND a.user_id = :filterUserId`;
    }
    if (filterType) {
      whereClause += ` AND a.activity_type = :filterType`;
    }
    if (filterCategory) {
      whereClause += ` AND a.activity_category = :filterCategory`;
    }
    if (filterSeverity) {
      whereClause += ` AND a.severity = :filterSeverity`;
    }

    // Get total count
    const countResult = await sequelize.query(`
      SELECT COUNT(*) as total
      FROM activities a
      LEFT JOIN workflow_requests wf ON a.request_id = wf.request_id
      WHERE a.created_at BETWEEN :start AND :end
      ${whereClause}
    `, {
      replacements: {
        userId,
        start: range.start,
        end: range.end,
        filterUserId: filterUserId || null,
        filterType: filterType || null,
        filterCategory: filterCategory || null,
        filterSeverity: filterSeverity || null
      },
      type: QueryTypes.SELECT
    });

    const totalRecords = Number((countResult[0] as any).total);
    const totalPages = Math.ceil(totalRecords / limit);

    // Get paginated activities with IP and user agent
    const activities = await sequelize.query(`
      SELECT
        a.activity_id,
        a.request_id,
        a.activity_type AS type,
        a.activity_description,
        a.activity_category,
        a.user_id,
        a.user_name,
        a.created_at AS timestamp,
        a.ip_address,
        a.user_agent,
        wf.request_number,
        wf.title AS request_title,
        wf.priority
      FROM activities a
      LEFT JOIN workflow_requests wf ON a.request_id = wf.request_id
      WHERE a.created_at BETWEEN :start AND :end
      ${whereClause}
      ORDER BY a.created_at DESC
      LIMIT :limit OFFSET :offset
    `, {
      replacements: {
        userId,
        start: range.start,
        end: range.end,
        limit,
        offset,
        filterUserId: filterUserId || null,
        filterType: filterType || null,
        filterCategory: filterCategory || null,
        filterSeverity: filterSeverity || null
      },
      type: QueryTypes.SELECT
    });

    return {
      activities: activities.map((a: any) => ({
        activityId: a.activity_id,
        requestId: a.request_id,
        requestNumber: a.request_number || null,
        requestTitle: a.request_title || null,
        type: a.type,
        action: a.activity_description || a.type,
        details: a.activity_description || a.activity_category || a.type, // Use activity_description for login details
        userId: a.user_id,
        userName: a.user_name,
        timestamp: a.timestamp,
        ipAddress: a.ip_address,
        userAgent: a.user_agent,
        priority: (a.priority || '').toLowerCase()
      })),
      currentPage: page,
      totalPages,
      totalRecords,
      limit
    };
  }

  /**
   * Get Workflow Aging Report with business days calculation
   * Uses optimized server-side pagination with business days calculation
   */
  async getWorkflowAgingReport(
    userId: string,
    threshold: number = 7,
    page: number = 1,
    limit: number = 50,
    dateRange?: string,
    startDate?: string,
    endDate?: string
  ) {
    const user = await User.findByPk(userId);
    const isAdmin = user?.hasManagementAccess() || false;

    const range = this.parseDateRange(dateRange, startDate, endDate);

    // For regular users: only their initiated requests or where they're participants
    let whereClause = isAdmin ? '' : `
      AND (
        wf.initiator_id = :userId
        OR EXISTS (
          SELECT 1 FROM participants p 
          WHERE p.request_id = wf.request_id 
          AND p.user_id = :userId
        )
      )
    `;

    // Step 1: Get ALL active requests that might match (for accurate business days calculation)
    // We need to calculate business days for all to filter correctly, but we'll optimize the calculation
    const allRequests = await sequelize.query(`
      SELECT
        wf.request_id,
        wf.request_number,
        wf.title,
        wf.priority,
        wf.status,
        wf.submission_date,
        wf.current_level,
        wf.total_levels,
        u.display_name AS initiator_name,
        u.email AS initiator_email,
        al.level_name AS current_stage_name,
        al.approver_name AS current_approver_name
      FROM workflow_requests wf
      LEFT JOIN users u ON wf.initiator_id = u.user_id
      LEFT JOIN approval_levels al ON al.request_id = wf.request_id 
        AND al.level_number = wf.current_level
      WHERE wf.is_draft = false
        AND wf.status NOT IN ('CLOSED', 'APPROVED', 'REJECTED')
        AND wf.submission_date IS NOT NULL
        AND wf.submission_date BETWEEN :start AND :end
      ${whereClause}
      ORDER BY wf.submission_date ASC
    `, {
      replacements: { userId, start: range.start, end: range.end },
      type: QueryTypes.SELECT
    });

    // Step 2: Calculate business days for all requests and filter by threshold
    // This is necessary for accuracy since business days depend on holidays and working hours config
    const { calculateBusinessDays } = await import('@utils/tatTimeUtils');
    const agingData: any[] = [];

    // Process requests in parallel batches for better performance
    const BATCH_SIZE = 50;
    for (let i = 0; i < allRequests.length; i += BATCH_SIZE) {
      const batch = allRequests.slice(i, i + BATCH_SIZE);
      const batchResults = await Promise.all(
        batch.map(async (req: any) => {
          const priority = ((req as any).priority || 'STANDARD').toLowerCase();
          const businessDays = await calculateBusinessDays(
            (req as any).submission_date,
            null, // current date
            priority
          );

          if (businessDays > threshold) {
            return {
              requestId: (req as any).request_id,
              requestNumber: (req as any).request_number,
              title: (req as any).title,
              priority: priority,
              status: ((req as any).status || 'PENDING').toLowerCase(),
              initiatorName: (req as any).initiator_name || (req as any).initiator_email || 'Unknown',
              initiatorEmail: (req as any).initiator_email,
              submissionDate: (req as any).submission_date,
              daysOpen: businessDays,
              currentLevel: (req as any).current_level,
              totalLevels: (req as any).total_levels,
              currentStageName: (req as any).current_stage_name || `Level ${(req as any).current_level}`,
              currentApproverName: (req as any).current_approver_name
            };
          }
          return null;
        })
      );

      // Filter out null results and add to agingData
      agingData.push(...batchResults.filter((r: any) => r !== null));
    }

    // Step 3: Sort by days open (descending)
    agingData.sort((a, b) => b.daysOpen - a.daysOpen);

    // Step 4: Apply server-side pagination
    const totalRecords = agingData.length;
    const totalPages = Math.ceil(totalRecords / limit);
    const offset = (page - 1) * limit;
    const paginatedData = agingData.slice(offset, offset + limit);

    return {
      agingData: paginatedData,
      currentPage: page,
      totalPages,
      totalRecords,
      limit
    };
  }

  /**
   * Get single approver stats only (dedicated API for performance)
   * Only respects date, priority, and SLA filters
   */
  async getSingleApproverStats(
    userId: string,
    approverId: string,
    dateRange?: string,
    startDate?: string,
    endDate?: string,
    priority?: string,
    slaCompliance?: string
  ) {
    const user = await User.findByPk(userId);
    const isAdmin = user?.hasManagementAccess() || false;

    // Allow users to view their own performance, or admins to view any approver's performance
    if (!isAdmin && approverId !== userId) {
      throw new Error('Unauthorized: You can only view your own performance');
    }

    // Parse date range if provided
    let dateFilter = '';
    const replacements: any = { approverId };

    if (dateRange) {
      const dateFilterObj = this.parseDateRange(dateRange, startDate, endDate);
      dateFilter = `
        AND (
          (wf.submission_date IS NOT NULL AND wf.submission_date >= :dateStart AND wf.submission_date <= :dateEnd)
          OR (al.action_date IS NOT NULL AND al.action_date >= :dateStart AND al.action_date <= :dateEnd)
        )
      `;
      replacements.dateStart = dateFilterObj.start;
      replacements.dateEnd = dateFilterObj.end;
    }

    // Priority filter
    let priorityFilter = '';
    if (priority && priority !== 'all') {
      priorityFilter = `AND wf.priority = :priorityFilter`;
      replacements.priorityFilter = priority.toUpperCase();
    }

    // SLA Compliance filter
    let slaFilter = '';
    if (slaCompliance && slaCompliance !== 'all') {
      if (slaCompliance === 'breached') {
        slaFilter = `AND al.tat_breached = true`;
      } else if (slaCompliance === 'compliant') {
        slaFilter = `AND (al.tat_breached = false OR (al.tat_breached IS NULL AND al.elapsed_hours < al.tat_hours))`;
      }
    }

    // Calculate aggregated stats using approval_levels directly
    // IMPORTANT: totalApproved counts DISTINCT requests, not approval levels
    // This ensures a single request with multiple actions (e.g., dealer proposal + completion) is counted once
    // TAT Compliance includes: completed + pending breached + levels from closed workflows
    const statsQuery = `
      SELECT 
        COUNT(DISTINCT al.request_id) as totalApproved,
        SUM(CASE WHEN al.status = 'APPROVED' THEN 1 ELSE 0 END) as approvedCount,
        SUM(CASE WHEN al.status = 'REJECTED' THEN 1 ELSE 0 END) as rejectedCount,
        COUNT(DISTINCT CASE WHEN al.status IN ('PENDING', 'IN_PROGRESS') THEN al.request_id END) as pendingCount,
        COUNT(DISTINCT CASE 
          WHEN (al.status IN ('APPROVED', 'REJECTED') OR wf.status = 'CLOSED')
          AND (al.tat_breached = false 
            OR (al.tat_breached IS NULL AND al.elapsed_hours IS NOT NULL AND al.elapsed_hours < al.tat_hours))
          THEN al.request_id 
        END) as withinTatCount,
        COUNT(DISTINCT CASE 
          WHEN ((al.status IN ('APPROVED', 'REJECTED') OR wf.status = 'CLOSED') AND al.tat_breached = true)
          OR (al.status IN ('PENDING', 'IN_PROGRESS') AND al.tat_breached = true)
          THEN al.request_id 
        END) as breachedCount,
        COUNT(DISTINCT CASE 
          WHEN al.status IN ('PENDING', 'IN_PROGRESS') 
          AND al.tat_breached = true
          THEN al.request_id 
        END) as pendingBreachedCount,
        AVG(CASE 
          WHEN (al.status IN ('APPROVED', 'REJECTED') OR wf.status = 'CLOSED')
          AND al.elapsed_hours IS NOT NULL 
          AND al.elapsed_hours >= 0 
          THEN al.elapsed_hours 
          ELSE NULL 
        END) as avgResponseHours,
        COUNT(DISTINCT CASE WHEN wf.status = 'CLOSED' THEN al.request_id END) as closedCount
      FROM approval_levels al
      INNER JOIN workflow_requests wf ON al.request_id = wf.request_id
      WHERE al.approver_id = :approverId
        AND wf.is_draft = false
        ${dateFilter}
        ${priorityFilter}
        ${slaFilter}
    `;

    const [statsResult] = await sequelize.query(statsQuery, {
      replacements,
      type: QueryTypes.SELECT
    });

    const stats = statsResult as any;

    // Database returns lowercase column names
    // TAT Compliance calculation includes pending breached requests
    // Total for compliance = completed + pending breached
    const totalCompleted = (parseInt(stats.approvedcount) || 0) + (parseInt(stats.rejectedcount) || 0);
    const pendingBreached = parseInt(stats.pendingbreachedcount) || 0;
    const totalForCompliance = totalCompleted + pendingBreached;
    const tatCompliancePercent = totalForCompliance > 0
      ? Math.round(((parseInt(stats.withintatcount) || 0) / totalForCompliance) * 100)
      : 0;

    // Get approver name
    const approver = await User.findByPk(approverId);

    const approverStats = {
      approverId,
      approverName: approver ? `${approver.firstName} ${approver.lastName}` : 'Unknown',
      totalApproved: parseInt(stats.totalapproved) || 0,
      approvedCount: parseInt(stats.approvedcount) || 0,
      rejectedCount: parseInt(stats.rejectedcount) || 0,
      closedCount: parseInt(stats.closedcount) || 0,
      pendingCount: parseInt(stats.pendingcount) || 0,
      withinTatCount: parseInt(stats.withintatcount) || 0,
      breachedCount: parseInt(stats.breachedcount) || 0,
      tatCompliancePercent,
      avgResponseHours: parseFloat(stats.avgresponsehours) || 0
    };

    return approverStats;
  }

  /**
   * Get requests filtered by approver ID with detailed filtering support
   */
  async getRequestsByApprover(
    userId: string,
    approverId: string,
    page: number = 1,
    limit: number = 50,
    dateRange?: string,
    startDate?: string,
    endDate?: string,
    status?: string,
    priority?: string,
    slaCompliance?: string,
    search?: string
  ) {
    const user = await User.findByPk(userId);
    const isAdmin = user?.hasManagementAccess() || false;

    // Allow users to view their own performance, or admins to view any approver's performance
    if (!isAdmin && approverId !== userId) {
      return {
        requests: [],
        currentPage: page,
        totalPages: 0,
        totalRecords: 0,
        limit
      };
    }

    const offset = (page - 1) * limit;

    // Parse date range if provided
    let dateFilter = '';
    const replacements: any = { approverId, limit, offset };

    if (dateRange) {
      const dateFilterObj = this.parseDateRange(dateRange, startDate, endDate);
      // Filter by submission_date OR approval action_date to include requests approved in date range
      // This ensures we see requests where the approver acted during the date range, even if submitted earlier
      dateFilter = `
        AND (
          (wf.submission_date IS NOT NULL AND wf.submission_date >= :dateStart AND wf.submission_date <= :dateEnd)
          OR (al.action_date IS NOT NULL AND al.action_date >= :dateStart AND al.action_date <= :dateEnd)
        )
      `;
      replacements.dateStart = dateFilterObj.start;
      replacements.dateEnd = dateFilterObj.end;
    }

    // Status filter - Filter by the approver's action status, not overall workflow status
    let statusFilter = '';
    if (status && status !== 'all') {
      if (status === 'pending') {
        // Show requests where this approver is the current approver AND their level is pending
        statusFilter = `AND al.status IN ('PENDING', 'IN_PROGRESS')`;
      } else if (status === 'approved') {
        // Show requests this approver has approved (regardless of overall workflow status)
        statusFilter = `AND al.status = 'APPROVED'`;
      } else if (status === 'rejected') {
        // Show requests this approver has rejected
        statusFilter = `AND al.status = 'REJECTED'`;
      } else if (status === 'closed') {
        // Show requests that are fully closed
        statusFilter = `AND wf.status = 'CLOSED'`;
      } else {
        // For other statuses, filter by workflow status
        statusFilter = `AND wf.status = :statusFilter`;
        replacements.statusFilter = status.toUpperCase();
      }
    }

    // Priority filter
    let priorityFilter = '';
    if (priority && priority !== 'all') {
      priorityFilter = `AND wf.priority = :priorityFilter`;
      replacements.priorityFilter = priority.toUpperCase();
    }

    // Search filter
    let searchFilter = '';
    if (search && search.trim()) {
      searchFilter = `
        AND (
          wf.request_number ILIKE :searchTerm
          OR wf.title ILIKE :searchTerm
          OR u.display_name ILIKE :searchTerm
          OR u.email ILIKE :searchTerm
        )
      `;
      replacements.searchTerm = `%${search.trim()}%`;
    }

    // SLA Compliance filter - get requests where this approver was involved
    let slaFilter = '';
    if (slaCompliance && slaCompliance !== 'all') {
      if (slaCompliance === 'breached') {
        slaFilter = `AND EXISTS (
          SELECT 1 FROM tat_alerts ta
          INNER JOIN approval_levels al ON ta.level_id = al.level_id
          WHERE ta.request_id = wf.request_id
          AND al.approver_id = :approverId
          AND ta.is_breached = true
        )`;
      } else if (slaCompliance === 'compliant') {
        // Compliant: completed requests that are not breached
        slaFilter = `AND wf.status IN ('APPROVED', 'REJECTED', 'CLOSED')
          AND NOT EXISTS (
            SELECT 1 FROM tat_alerts ta
            INNER JOIN approval_levels al ON ta.level_id = al.level_id
            WHERE ta.request_id = wf.request_id
            AND al.approver_id = :approverId
            AND ta.is_breached = true
          )`;
      } else {
        // on_track, approaching, critical - these will be calculated client-side
        // For now, skip this filter as SLA status is calculated dynamically
        // The client-side filter will handle these cases
      }
    }

    // Get all requests where this approver has been involved (as approver in any approval level)
    // Include ALL requests where approver is assigned, regardless of approval status (pending, approved, rejected)
    // For count, we need to use the same date filter logic
    const countResult = await sequelize.query(`
      SELECT COUNT(DISTINCT wf.request_id) as total
      FROM workflow_requests wf
      INNER JOIN approval_levels al ON wf.request_id = al.request_id
      WHERE al.approver_id = :approverId
        AND wf.is_draft = false
        AND (
          al.status IN ('APPROVED', 'REJECTED')
          OR al.level_number <= wf.current_level
        )
        ${dateFilter}
        ${statusFilter}
        ${priorityFilter}
        ${slaFilter}
        ${searchFilter}
    `, {
      replacements,
      type: QueryTypes.SELECT
    });

    const totalRecords = Number((countResult[0] as any).total);
    const totalPages = Math.ceil(totalRecords / limit);

    // Get requests with approver's level information - use DISTINCT ON for PostgreSQL
    // Priority: Show approved/rejected levels first, then pending/in-progress
    // This ensures we see the approver's actual actions, not just pending assignments
    const requests = await sequelize.query(`
      SELECT DISTINCT ON (wf.request_id)
        wf.request_id,
        wf.request_number,
        wf.title,
        wf.priority,
        wf.status,
        wf.submission_date,
        wf.closure_date,
        wf.current_level,
        wf.total_levels,
        wf.total_tat_hours,
        wf.created_at,
        wf.updated_at,
        u.display_name AS initiator_name,
        u.email AS initiator_email,
        u.department AS initiator_department,
        al.level_id,
        al.level_number,
        al.status AS approval_status,
        al.action_date AS approval_action_date,
        al.level_start_time,
        al.tat_hours AS level_tat_hours,
        al.elapsed_hours AS level_elapsed_hours,
        (
          SELECT COUNT(*) 
          FROM tat_alerts ta 
          WHERE ta.request_id = wf.request_id 
          AND ta.level_id = al.level_id
          AND ta.is_breached = true
        ) AS is_breached
      FROM workflow_requests wf
      INNER JOIN approval_levels al ON wf.request_id = al.request_id
      LEFT JOIN users u ON wf.initiator_id = u.user_id
      WHERE al.approver_id = :approverId
        AND wf.is_draft = false
        AND (
          al.status IN ('APPROVED', 'REJECTED')
          OR al.level_number <= wf.current_level
        )
        ${dateFilter}
        ${statusFilter}
        ${priorityFilter}
        ${slaFilter}
        ${searchFilter}
      ORDER BY 
        wf.request_id,
        CASE 
          WHEN al.status = 'APPROVED' THEN 1
          WHEN al.status = 'REJECTED' THEN 2
          WHEN al.status = 'IN_PROGRESS' THEN 3
          WHEN al.status = 'PENDING' THEN 4
          ELSE 5
        END ASC,
        al.level_number ASC
      LIMIT :limit OFFSET :offset
    `, {
      replacements,
      type: QueryTypes.SELECT
    });

    // Calculate SLA status for each request/level combination
    // This ensures we detect breaches for ALL requests (pending, approved, rejected)
    const { calculateSLAStatus } = await import('@utils/tatTimeUtils');
    const processedRequests = await Promise.all(
      requests.map(async (req: any) => {
        let slaStatus = 'on_track';
        let isBreached = false;

        // Calculate SLA status for ALL levels (pending, in-progress, approved, rejected)
        // This ensures we catch breaches even for pending requests
        if (req.level_tat_hours && req.level_start_time) {
          try {
            const priority = (req.priority || 'standard').toLowerCase();
            // For completed levels, use action/closure date; for pending, use current time
            const levelEndDate = req.approval_action_date || req.closure_date || null;
            const calculated = await calculateSLAStatus(
              req.level_start_time,
              req.level_tat_hours,
              priority,
              levelEndDate
            );
            slaStatus = calculated.status;

            // Mark as breached if percentageUsed >= 100 (same logic as Requests screen)
            // This catches pending requests that have already breached
            if (calculated.percentageUsed >= 100) {
              isBreached = true;
            } else if (req.is_breached && req.is_breached > 0) {
              // Also check tat_alerts table for historical breaches
              isBreached = true;
            }
          } catch (error) {
            logger.error(`[Dashboard] Error calculating SLA status for request ${req.request_id}:`, error);
            // If calculation fails, check tat_alerts table
            if (req.is_breached && req.is_breached > 0) {
              isBreached = true;
              slaStatus = 'breached';
            } else {
              slaStatus = 'on_track';
            }
          }
        } else if (req.is_breached && req.is_breached > 0) {
          // Fallback: if no TAT data but tat_alerts shows breach
          isBreached = true;
          slaStatus = 'breached';
        }

        return {
          requestId: req.request_id,
          requestNumber: req.request_number,
          title: req.title,
          priority: (req.priority || 'STANDARD').toLowerCase(),
          status: (req.status || 'PENDING').toLowerCase(),
          initiatorName: req.initiator_name || req.initiator_email || 'Unknown',
          initiatorEmail: req.initiator_email,
          initiatorDepartment: req.initiator_department,
          submissionDate: req.submission_date,
          closureDate: req.closure_date,
          createdAt: req.created_at,
          updatedAt: req.updated_at,
          currentLevel: req.current_level,
          totalLevels: req.total_levels,
          levelId: req.level_id,
          levelNumber: req.level_number,
          approvalStatus: (req.approval_status || 'PENDING').toLowerCase(),
          approvalActionDate: req.approval_action_date,
          slaStatus,
          levelTatHours: parseFloat(req.level_tat_hours || 0),
          levelElapsedHours: parseFloat(req.level_elapsed_hours || 0),
          isBreached: isBreached, // Use calculated breach status (includes pending requests that breached)
          totalTatHours: parseFloat(req.total_tat_hours || 0)
        };
      })
    );

    return {
      requests: processedRequests,
      currentPage: page,
      totalPages,
      totalRecords,
      limit
    };
  }
}

export const dashboardService = new DashboardService();