/**
 * Customer Health Score Service
 *
 * Calculates health scores for customer accounts based on:
 * - Usage score (40%): Login frequency, feature depth, message volume
 * - Support score (30%): Ticket volume, resolution time, priority distribution
 * - Payment score (30%): On-time payments, dunning history, plan tier
 *
 * Health Status:
 * - healthy: >= 70
 * - at_risk: 40-69
 * - critical: < 40
 *
 * @module services/health-score
 */

import { prisma } from '@/lib/db';
import { logger } from '@/lib/logger';
import { supportTicketsAdapter } from '@/lib/adapters/support';
import type {
  HealthScore,
  HealthStatus,
  AccountHealth,
  AtRiskAccount,
  RiskReason,
  RiskLevel,
  SuggestedPlaybook,
  CustomerHealthResponse,
  HealthDistribution,
  UsageMetrics,
  SupportMetrics,
  PaymentMetrics,
  RenewalInfo,
  RenewalsResponse,
  ChurnedAccount,
} from '@/lib/types/health-score.types';
import { PRICING_TIERS } from '@/lib/config/pricing-tiers';

// Score weights
const USAGE_WEIGHT = 40;
const SUPPORT_WEIGHT = 30;
const PAYMENT_WEIGHT = 30;

// Health status thresholds
const HEALTHY_THRESHOLD = 70;
const AT_RISK_THRESHOLD = 40;

// Usage cliff thresholds (percentage drop)
const CLIFF_THRESHOLD = 50; // >50% drop triggers cliff alert

// Lookback periods (days)
const USAGE_LOOKBACK_DAYS = 30;
const SUPPORT_LOOKBACK_DAYS = 30;

/**
 * Customer Health Score Service
 */
export class HealthScoreService {
  private static instance: HealthScoreService;

  public static getInstance(): HealthScoreService {
    if (!HealthScoreService.instance) {
      HealthScoreService.instance = new HealthScoreService();
    }
    return HealthScoreService.instance;
  }

  /**
   * Calculate health score for a single user
   */
  async calculateUserHealthScore(userId: string): Promise<HealthScore> {
    const scopedLogger = logger.scope('HealthScoreService.calculateUserHealthScore');

    try {
      // Fetch metrics in parallel
      const [usageMetrics, supportMetrics, paymentMetrics] = await Promise.all([
        this.getUsageMetrics(userId),
        this.getSupportMetrics(userId),
        this.getPaymentMetrics(userId),
      ]);

      // Calculate individual scores
      const usageScore = this.calculateUsageScore(usageMetrics);
      const supportScore = this.calculateSupportScore(supportMetrics);
      const paymentScore = this.calculatePaymentScore(paymentMetrics);

      // Calculate composite score
      const total = Math.round(usageScore + supportScore + paymentScore);

      // Determine status
      const status = this.getHealthStatus(total);

      return {
        total,
        breakdown: {
          usage: usageScore,
          support: supportScore,
          payment: paymentScore,
        },
        status,
      };
    } catch (error) {
      scopedLogger.error('Failed to calculate health score', { userId, error });
      // Return neutral score on error
      return {
        total: 50,
        breakdown: { usage: 20, support: 15, payment: 15 },
        status: 'at_risk',
      };
    }
  }

  /**
   * Get usage metrics for a user
   */
  private async getUsageMetrics(userId: string): Promise<UsageMetrics> {
    const now = new Date();
    const lookbackDate = new Date(now.getTime() - USAGE_LOOKBACK_DAYS * 24 * 60 * 60 * 1000);

    // Get login history
    const loginHistory = await prisma.login_history.findMany({
      where: {
        userId,
        createdAt: { gte: lookbackDate },
        success: true,
      },
      orderBy: { createdAt: 'desc' },
    });

    // Get most recent session
    const lastSession = await prisma.user_sessions.findFirst({
      where: { userId },
      orderBy: { lastActive: 'desc' },
    });

    // Get conversation count
    const conversationCount = await prisma.conversationLog.count({
      where: {
        userId,
        createdAt: { gte: lookbackDate },
      },
    });

    // Get message count from audit logs (action = 'message_sent')
    const messagesSent = await prisma.audit_logs.count({
      where: {
        userId,
        action: 'message_sent',
        createdAt: { gte: lookbackDate },
      },
    });

    // Get active workflows
    const workflowsActive = await prisma.userWorkflow.count({
      where: {
        userId,
        status: 'active',
      },
    });

    // Calculate unique active days
    const uniqueDays = new Set(
      loginHistory.map((l) => l.createdAt.toISOString().split('T')[0])
    );

    // Days since last login
    const lastLoginDate = lastSession?.lastActive || loginHistory[0]?.createdAt;
    const daysSinceLastLogin = lastLoginDate
      ? Math.floor((now.getTime() - lastLoginDate.getTime()) / (1000 * 60 * 60 * 24))
      : 999;

    return {
      loginCount: loginHistory.length,
      daysSinceLastLogin,
      activeDays: uniqueDays.size,
      conversationCount,
      messagesSent,
      workflowsActive,
    };
  }

  /**
   * Get support metrics for a user
   */
  private async getSupportMetrics(userId: string): Promise<SupportMetrics> {
    const now = new Date();
    const lookbackDate = new Date(now.getTime() - SUPPORT_LOOKBACK_DAYS * 24 * 60 * 60 * 1000);

    // Get ticket counts
    const [openTickets, ticketsCreated, urgentTickets, resolvedTickets] = await Promise.all([
      supportTicketsAdapter.count({
        where: {
          userId,
          status: { in: ['OPEN', 'IN_PROGRESS'] },
        },
      }),
      supportTicketsAdapter.count({
        where: {
          userId,
          createdAt: { gte: lookbackDate },
        },
      }),
      supportTicketsAdapter.count({
        where: {
          userId,
          priority: 'URGENT',
          status: { in: ['OPEN', 'IN_PROGRESS'] },
        },
      }),
      supportTicketsAdapter.findMany({
        where: {
          userId,
          resolvedAt: { not: null },
          createdAt: { gte: lookbackDate },
        },
      }),
    ]);

    // Calculate average resolution time
    let avgResolutionTime = 0;
    if (resolvedTickets.length > 0) {
      const totalHours = (resolvedTickets as Array<Record<string, unknown>>).reduce((sum, ticket) => {
        if (ticket.resolvedAt) {
          const diffMs = (ticket.resolvedAt as Date).getTime() - (ticket.createdAt as Date).getTime();
          return sum + diffMs / (1000 * 60 * 60);
        }
        return sum;
      }, 0);
      avgResolutionTime = totalHours / resolvedTickets.length;
    }

    return {
      openTickets,
      ticketsCreated,
      avgResolutionTime,
      urgentTickets,
    };
  }

  /**
   * Get payment metrics for a user
   */
  private async getPaymentMetrics(userId: string): Promise<PaymentMetrics> {
    const subscription = await prisma.subscriptions.findFirst({
      where: { userId },
      orderBy: { createdAt: 'desc' },
    });

    if (!subscription) {
      return {
        status: 'none',
        failureCount: 0,
        daysSinceLastPayment: 999,
        tier: null,
        isDunning: false,
      };
    }

    const now = new Date();
    const daysSinceLastPayment = subscription.lastPaymentDate
      ? Math.floor((now.getTime() - subscription.lastPaymentDate.getTime()) / (1000 * 60 * 60 * 24))
      : 999;

    return {
      status: subscription.status,
      failureCount: subscription.paymentFailureCount,
      daysSinceLastPayment,
      tier: subscription.tierId,
      isDunning: subscription.status === 'past_due' || subscription.paymentFailureCount > 0,
    };
  }

  /**
   * Calculate usage score (0-40)
   */
  private calculateUsageScore(metrics: UsageMetrics): number {
    let score = 0;

    // Login frequency (0-15)
    // Daily = 15, Weekly = 10, Monthly = 5, None = 0
    if (metrics.daysSinceLastLogin <= 1) {
      score += 15;
    } else if (metrics.daysSinceLastLogin <= 7) {
      score += 10;
    } else if (metrics.daysSinceLastLogin <= 30) {
      score += 5;
    }

    // Active days (0-10)
    // 20+ days = 10, 10-19 = 7, 5-9 = 4, 1-4 = 2, 0 = 0
    if (metrics.activeDays >= 20) {
      score += 10;
    } else if (metrics.activeDays >= 10) {
      score += 7;
    } else if (metrics.activeDays >= 5) {
      score += 4;
    } else if (metrics.activeDays >= 1) {
      score += 2;
    }

    // Feature depth - conversations (0-8)
    if (metrics.conversationCount >= 50) {
      score += 8;
    } else if (metrics.conversationCount >= 20) {
      score += 6;
    } else if (metrics.conversationCount >= 10) {
      score += 4;
    } else if (metrics.conversationCount >= 1) {
      score += 2;
    }

    // Feature depth - workflows (0-7)
    if (metrics.workflowsActive >= 3) {
      score += 7;
    } else if (metrics.workflowsActive >= 2) {
      score += 5;
    } else if (metrics.workflowsActive >= 1) {
      score += 3;
    }

    return Math.min(score, USAGE_WEIGHT);
  }

  /**
   * Calculate support score (0-30)
   * Low tickets = high score, high tickets = low score
   */
  private calculateSupportScore(metrics: SupportMetrics): number {
    let score = SUPPORT_WEIGHT; // Start with max and subtract

    // Ticket volume penalty (0-15 deduction)
    // 0 tickets = no penalty, 1-2 = -3, 3-5 = -7, 6+ = -15
    if (metrics.ticketsCreated >= 6) {
      score -= 15;
    } else if (metrics.ticketsCreated >= 3) {
      score -= 7;
    } else if (metrics.ticketsCreated >= 1) {
      score -= 3;
    }

    // Open ticket penalty (0-8 deduction)
    if (metrics.openTickets >= 3) {
      score -= 8;
    } else if (metrics.openTickets >= 2) {
      score -= 5;
    } else if (metrics.openTickets >= 1) {
      score -= 3;
    }

    // Urgent ticket penalty (0-5 deduction)
    if (metrics.urgentTickets >= 2) {
      score -= 5;
    } else if (metrics.urgentTickets >= 1) {
      score -= 3;
    }

    // Resolution time bonus (0-2 addition)
    // Fast resolution is a good sign of engagement
    if (metrics.avgResolutionTime > 0 && metrics.avgResolutionTime < 24) {
      score += 2;
    }

    return Math.max(0, Math.min(score, SUPPORT_WEIGHT));
  }

  /**
   * Calculate payment score (0-30)
   */
  private calculatePaymentScore(metrics: PaymentMetrics): number {
    let score = 0;

    // Base score by status (0-20)
    switch (metrics.status) {
      case 'active':
        score += 20;
        break;
      case 'trialing':
        score += 15;
        break;
      case 'past_due':
        score += 5;
        break;
      case 'cancelled':
      case 'suspended':
        score += 0;
        break;
      default:
        score += 10;
    }

    // Payment failure penalty (0-10 deduction)
    if (metrics.failureCount >= 3) {
      score -= 10;
    } else if (metrics.failureCount >= 2) {
      score -= 7;
    } else if (metrics.failureCount >= 1) {
      score -= 4;
    }

    // Recent payment bonus (0-5)
    if (metrics.daysSinceLastPayment <= 7) {
      score += 5;
    } else if (metrics.daysSinceLastPayment <= 30) {
      score += 3;
    } else if (metrics.daysSinceLastPayment <= 60) {
      score += 1;
    }

    // Tier bonus (0-5)
    // Higher tiers indicate more committed customers
    const tierLevel = this.getTierLevel(metrics.tier);
    score += Math.min(tierLevel, 5);

    return Math.max(0, Math.min(score, PAYMENT_WEIGHT));
  }

  /**
   * Get tier level for scoring
   */
  private getTierLevel(tierId: string | null): number {
    if (!tierId) return 0;
    const tierNumber = parseInt(tierId.replace(/\D/g, ''), 10);
    return isNaN(tierNumber) ? 0 : tierNumber;
  }

  /**
   * Determine health status based on score
   */
  private getHealthStatus(score: number): HealthStatus {
    if (score >= HEALTHY_THRESHOLD) return 'healthy';
    if (score >= AT_RISK_THRESHOLD) return 'at_risk';
    return 'critical';
  }

  /**
   * Get all account health scores for staff dashboard
   */
  async getAllAccountHealth(): Promise<CustomerHealthResponse> {
    const scopedLogger = logger.scope('HealthScoreService.getAllAccountHealth');

    try {
      // Get all users with subscriptions
      const users = await prisma.users.findMany({
        where: {
          subscriptions: {
            some: {},
          },
        },
        include: {
          subscriptions: {
            orderBy: { createdAt: 'desc' },
            take: 1,
          },
        },
        orderBy: { createdAt: 'desc' },
      });

      // Calculate health scores for all users
      const accountHealthPromises = users.map(async (user) => {
        const healthScore = await this.calculateUserHealthScore(user.id);
        const subscription = user.subscriptions[0]; // Get most recent subscription

        // Get recent support tickets
        const supportTickets30d = await supportTicketsAdapter.count({
          where: {
            userId: user.id,
            createdAt: {
              gte: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000),
            },
          },
        });

        // Get last login
        const lastSession = await prisma.user_sessions.findFirst({
          where: { userId: user.id },
          orderBy: { lastActive: 'desc' },
        });

        // Calculate MRR from subscription
        const mrr = this.calculateMRR(subscription);

        return {
          userId: user.id,
          name: user.name,
          companyName: user.companyName,
          email: user.email,
          healthScore,
          subscriptionStatus: subscription?.status || null,
          tier: subscription?.tierId || null,
          mrr,
          lastLoginAt: lastSession?.lastActive?.toISOString() || null,
          supportTickets30d,
          paymentFailures: subscription?.paymentFailureCount || 0,
        } as AccountHealth;
      });

      const accounts = await Promise.all(accountHealthPromises);

      // Calculate distribution
      const distribution: HealthDistribution = {
        healthy: accounts.filter((a) => a.healthScore.status === 'healthy').length,
        atRisk: accounts.filter((a) => a.healthScore.status === 'at_risk').length,
        critical: accounts.filter((a) => a.healthScore.status === 'critical').length,
        total: accounts.length,
      };

      // Get at-risk accounts with detailed risk factors
      const atRiskAccounts = await this.getAtRiskAccounts(accounts);

      // Calculate average health score
      const averageHealthScore =
        accounts.length > 0
          ? Math.round(accounts.reduce((sum, a) => sum + a.healthScore.total, 0) / accounts.length)
          : 0;

      return {
        distribution,
        accounts,
        atRiskAccounts,
        averageHealthScore,
        trend: 0, // TODO: Implement trend calculation
        calculatedAt: new Date().toISOString(),
      };
    } catch (error) {
      scopedLogger.error('Failed to get all account health', { error });
      throw error;
    }
  }

  /**
   * Get at-risk accounts with detailed risk factors
   */
  async getAtRiskAccounts(accounts?: AccountHealth[]): Promise<AtRiskAccount[]> {
    // If accounts not provided, fetch them
    if (!accounts) {
      const response = await this.getAllAccountHealth();
      accounts = response.accounts;
    }

    // Filter to at-risk and critical accounts
    const atRiskAccounts = accounts.filter(
      (a) => a.healthScore.status === 'at_risk' || a.healthScore.status === 'critical'
    );

    // Add risk reasons for each account
    const atRiskWithReasons = await Promise.all(
      atRiskAccounts.map(async (account) => {
        const riskReasons = await this.detectRiskReasons(account.userId);
        const riskLevel = this.calculateRiskLevel(account.healthScore.total, riskReasons);
        const suggestedPlaybook = this.getSuggestedPlaybook(riskReasons);

        return {
          tenantId: account.userId,
          tenantName: account.companyName || account.name || 'Unknown',
          tenantEmail: account.email,
          healthScore: account.healthScore.total,
          healthStatus: account.healthScore.status,
          riskLevel,
          riskReasons,
          suggestedPlaybook,
          lastContactedAt: null, // TODO: Track contact history
          tier: account.tier,
          mrr: account.mrr,
        } as AtRiskAccount;
      })
    );

    // Sort by risk level (critical first)
    return atRiskWithReasons.sort((a, b) => {
      const levelOrder: Record<RiskLevel, number> = { critical: 0, high: 1, medium: 2, low: 3 };
      return levelOrder[a.riskLevel] - levelOrder[b.riskLevel];
    });
  }

  /**
   * Detect risk reasons for a user
   */
  async detectRiskReasons(userId: string): Promise<RiskReason[]> {
    const reasons: RiskReason[] = [];
    const now = new Date();

    // Check for usage cliffs
    const usageCliffs = await this.detectUsageCliffs(userId);
    reasons.push(...usageCliffs);

    // Check for support spike
    const supportSpike = await this.detectSupportSpike(userId);
    if (supportSpike) {
      reasons.push(supportSpike);
    }

    // Check for payment failures
    const subscription = await prisma.subscriptions.findFirst({
      where: { userId },
      orderBy: { createdAt: 'desc' },
    });

    if (subscription) {
      if (subscription.paymentFailureCount > 0) {
        reasons.push({
          type: 'payment_failed',
          severity: subscription.paymentFailureCount >= 2 ? 80 : 50,
          detail: `${subscription.paymentFailureCount} failed payment attempt(s)`,
        });
      }
    }

    // Check for no login
    const lastSession = await prisma.user_sessions.findFirst({
      where: { userId },
      orderBy: { lastActive: 'desc' },
    });

    if (lastSession) {
      const daysSinceLogin = Math.floor(
        (now.getTime() - lastSession.lastActive.getTime()) / (1000 * 60 * 60 * 24)
      );
      if (daysSinceLogin >= 14) {
        reasons.push({
          type: 'no_login',
          severity: daysSinceLogin >= 28 ? 90 : 60,
          detail: `No login for ${daysSinceLogin} days`,
        });
      }
    } else {
      reasons.push({
        type: 'no_login',
        severity: 100,
        detail: 'No login activity recorded',
      });
    }

    return reasons;
  }

  /**
   * Detect usage cliffs (>50% drop in activity)
   */
  private async detectUsageCliffs(userId: string): Promise<RiskReason[]> {
    const reasons: RiskReason[] = [];
    const now = new Date();

    // Helper to count activity in a period
    const getActivityCount = async (startDays: number, endDays: number): Promise<number> => {
      const startDate = new Date(now.getTime() - startDays * 24 * 60 * 60 * 1000);
      const endDate = new Date(now.getTime() - endDays * 24 * 60 * 60 * 1000);

      const [logins, conversations] = await Promise.all([
        prisma.login_history.count({
          where: {
            userId,
            success: true,
            createdAt: {
              gte: startDate,
              lt: endDate,
            },
          },
        }),
        prisma.conversationLog.count({
          where: {
            userId,
            createdAt: {
              gte: startDate,
              lt: endDate,
            },
          },
        }),
      ]);

      return logins + conversations;
    };

    // 7-day cliff detection
    const last7Days = await getActivityCount(7, 0);
    const prev7Days = await getActivityCount(14, 7);
    if (prev7Days > 0 && last7Days / prev7Days < (100 - CLIFF_THRESHOLD) / 100) {
      const dropPercent = Math.round(((prev7Days - last7Days) / prev7Days) * 100);
      reasons.push({
        type: 'usage_cliff_7d',
        severity: 70,
        detail: `${dropPercent}% activity drop in last 7 days`,
      });
    }

    // 14-day cliff detection
    const last14Days = await getActivityCount(14, 0);
    const prev14Days = await getActivityCount(28, 14);
    if (prev14Days > 0 && last14Days / prev14Days < (100 - CLIFF_THRESHOLD) / 100) {
      const dropPercent = Math.round(((prev14Days - last14Days) / prev14Days) * 100);
      reasons.push({
        type: 'usage_cliff_14d',
        severity: 80,
        detail: `${dropPercent}% activity drop in last 14 days`,
      });
    }

    // 28-day cliff detection
    const last28Days = await getActivityCount(28, 0);
    const prev28Days = await getActivityCount(56, 28);
    if (prev28Days > 0 && last28Days / prev28Days < (100 - CLIFF_THRESHOLD) / 100) {
      const dropPercent = Math.round(((prev28Days - last28Days) / prev28Days) * 100);
      reasons.push({
        type: 'usage_cliff_28d',
        severity: 90,
        detail: `${dropPercent}% activity drop in last 28 days`,
      });
    }

    return reasons;
  }

  /**
   * Detect support ticket spike
   */
  private async detectSupportSpike(userId: string): Promise<RiskReason | null> {
    const now = new Date();
    const last7Days = new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000);
    const prev7Days = new Date(now.getTime() - 14 * 24 * 60 * 60 * 1000);

    const [recentTickets, previousTickets] = await Promise.all([
      supportTicketsAdapter.count({
        where: {
          userId,
          createdAt: { gte: last7Days },
        },
      }),
      supportTicketsAdapter.count({
        where: {
          userId,
          createdAt: {
            gte: prev7Days,
            lt: last7Days,
          },
        },
      }),
    ]);

    // Spike if 3+ tickets or 2x increase
    if (recentTickets >= 3 || (previousTickets > 0 && recentTickets >= previousTickets * 2)) {
      return {
        type: 'support_spike',
        severity: recentTickets >= 5 ? 80 : 60,
        detail: `${recentTickets} support tickets in last 7 days`,
      };
    }

    return null;
  }

  /**
   * Calculate risk level based on health score and risk reasons
   */
  private calculateRiskLevel(healthScore: number, reasons: RiskReason[]): RiskLevel {
    // Critical if health score < 30 or any severity >= 90
    if (healthScore < 30 || reasons.some((r) => r.severity >= 90)) {
      return 'critical';
    }
    // High if health score < 50 or any severity >= 70
    if (healthScore < 50 || reasons.some((r) => r.severity >= 70)) {
      return 'high';
    }
    // Medium if health score < 70 or any severity >= 50
    if (healthScore < 70 || reasons.some((r) => r.severity >= 50)) {
      return 'medium';
    }
    return 'low';
  }

  /**
   * Get suggested playbook based on risk reasons
   */
  private getSuggestedPlaybook(reasons: RiskReason[]): SuggestedPlaybook {
    // Payment issues -> recovery sequence
    if (reasons.some((r) => r.type === 'payment_failed')) {
      return 'recovery_sequence';
    }
    // Support spike or usage cliff -> QBR
    if (reasons.some((r) => r.type === 'support_spike' || r.type.startsWith('usage_cliff'))) {
      return 'qbr';
    }
    // No login -> CSM outreach
    if (reasons.some((r) => r.type === 'no_login')) {
      return 'csm_outreach';
    }
    // Default to recovery sequence
    return 'recovery_sequence';
  }

  /**
   * Calculate MRR from subscription
   */
  private calculateMRR(subscription: { tierId: string | null; billingPeriod: string | null } | null | undefined): number {
    if (!subscription || !subscription.tierId) return 0;

    // Get tier pricing from PRICING_TIERS
    const tier = PRICING_TIERS.find((t) => t.id === subscription.tierId);
    if (!tier) return 0;

    // Use GBP as base currency (priceGBP is monthly)
    const monthlyPrice = tier.priceGBP || 0;

    // If yearly, the price is already monthly (just discount applied)
    return monthlyPrice;
  }

  /**
   * Get renewal data for all accounts
   */
  async getRenewals(): Promise<RenewalsResponse> {
    const scopedLogger = logger.scope('HealthScoreService.getRenewals');

    try {
      const now = new Date();
      const in90Days = new Date(now.getTime() + 90 * 24 * 60 * 60 * 1000);
      const last30Days = new Date(now.getTime() - 30 * 24 * 60 * 60 * 1000);

      // Get upcoming renewals
      const upcomingRenewals = await prisma.subscriptions.findMany({
        where: {
          status: { in: ['active', 'trialing', 'past_due'] },
          currentPeriodEnd: {
            gte: now,
            lte: in90Days,
          },
        },
        include: {
          users: {
            select: {
              id: true,
              name: true,
              companyName: true,
              email: true,
            },
          },
        },
        orderBy: { currentPeriodEnd: 'asc' },
      });

      // Get dunning accounts
      const dunningAccounts = await prisma.subscriptions.findMany({
        where: {
          OR: [
            { status: 'past_due' },
            { paymentFailureCount: { gt: 0 } },
          ],
        },
        include: {
          users: {
            select: {
              id: true,
              name: true,
              email: true,
            },
          },
        },
      });

      // Get churned accounts
      const churnedSubscriptions = await prisma.subscriptions.findMany({
        where: {
          status: 'cancelled',
          cancelledAt: { gte: last30Days },
        },
        include: {
          users: {
            select: {
              id: true,
              name: true,
              companyName: true,
              email: true,
              createdAt: true,
            },
          },
        },
      });

      // Transform renewals with health scores
      const transformRenewal = async (sub: typeof upcomingRenewals[0]): Promise<RenewalInfo> => {
        const healthScore = await this.calculateUserHealthScore(sub.users.id);
        const daysUntilRenewal = Math.ceil(
          (sub.currentPeriodEnd.getTime() - now.getTime()) / (1000 * 60 * 60 * 24)
        );
        const mrr = this.calculateMRR(sub);

        // Calculate renewal risk
        let renewalRisk: RiskLevel = 'low';
        if (healthScore.total < 40 || sub.paymentFailureCount > 0) {
          renewalRisk = 'high';
        } else if (healthScore.total < 70) {
          renewalRisk = 'medium';
        }

        return {
          userId: sub.users.id,
          name: sub.users.name,
          companyName: sub.users.companyName,
          email: sub.users.email,
          renewalDate: sub.currentPeriodEnd.toISOString(),
          daysUntilRenewal,
          tier: sub.tierId,
          billingPeriod: sub.billingPeriod,
          healthScore: healthScore.total,
          healthStatus: healthScore.status,
          renewalRisk,
          mrrAtRisk: mrr,
          paymentFailures: sub.paymentFailureCount,
          isDunning: sub.status === 'past_due' || sub.paymentFailureCount > 0,
        };
      };

      // Process renewals
      const allRenewals = await Promise.all(upcomingRenewals.map(transformRenewal));

      // Split by period
      const next30Days = allRenewals.filter((r) => r.daysUntilRenewal <= 30);
      const next60Days = allRenewals.filter((r) => r.daysUntilRenewal > 30 && r.daysUntilRenewal <= 60);
      const next90Days = allRenewals.filter((r) => r.daysUntilRenewal > 60 && r.daysUntilRenewal <= 90);

      // Transform dunning
      const dunning = dunningAccounts.map((sub) => ({
        userId: sub.users.id,
        name: sub.users.name,
        email: sub.users.email,
        failureCount: sub.paymentFailureCount,
        lastAttempt: sub.lastPaymentAttempt?.toISOString() || null,
        nextRetry: null, // Would need Stripe data
        amount: this.calculateMRR(sub),
      }));

      // Transform churned
      const churned30Days: ChurnedAccount[] = churnedSubscriptions.map((sub) => {
        const lifetimeDays = Math.floor(
          ((sub.cancelledAt?.getTime() || now.getTime()) - sub.users.createdAt.getTime()) /
            (1000 * 60 * 60 * 24)
        );

        return {
          userId: sub.users.id,
          name: sub.users.name,
          companyName: sub.users.companyName,
          email: sub.users.email,
          churnedAt: sub.cancelledAt?.toISOString() || now.toISOString(),
          previousTier: sub.tierId,
          lostMrr: this.calculateMRR(sub),
          churnReason: null, // Would need cancellation reason
          lifetimeDays,
        };
      });

      // Calculate totals
      const totalRevenueAtRisk = allRenewals
        .filter((r) => r.renewalRisk === 'high')
        .reduce((sum, r) => sum + r.mrrAtRisk, 0);

      return {
        next30Days,
        next60Days,
        next90Days,
        dunning,
        churned30Days,
        totalRevenueAtRisk,
        summary: {
          totalUpcoming: allRenewals.length,
          highRiskCount: allRenewals.filter((r) => r.renewalRisk === 'high').length,
          dunningCount: dunning.length,
          churnedCount: churned30Days.length,
        },
      };
    } catch (error) {
      scopedLogger.error('Failed to get renewals', { error });
      throw error;
    }
  }
}

// Export singleton instance
export const healthScoreService = HealthScoreService.getInstance();
