All files / src/app/api/admin/loyalty/reports route.ts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
export const dynamic = "force-dynamic";

import { NextResponse } from "next/server";
import { prisma } from "@/lib/prisma";
import {
  withAdmin,
  withErrorHandling,
  successResponse,
  ApiSuccessResponse,
  ApiErrorResponse } from "@/lib/api";

interface LoyaltyReportsData {
  overview: {
    totalMembers: number;
    activeMembers: number;
    pointsIssued: number;
    pointsRedeemed: number;
  };
  tierDistribution: Array<{
    tierId: number;
    name: string;
    minPoints: number;
    count: number;
  }>;
  monthlyTrends: Array<{
    month: string;
    earned: number;
    redeemed: number;
  }>;
  topEarners: Array<{
    userId: number;
    name: string;
    email: string;
    totalPoints: number;
    lifetimePoints: number;
  }>;
}

/**
 * GET /api/admin/loyalty/reports
 * Get aggregated loyalty stats and reports
 */
async function handleGet(): Promise<NextResponse<ApiSuccessResponse<LoyaltyReportsData> | ApiErrorResponse>> {
  // Get total members
  const totalMembers = await prisma.customerLoyalty.count();

  // Get active members (had activity in last 90 days)
  const ninetyDaysAgo = new Date();
  ninetyDaysAgo.setDate(ninetyDaysAgo.getDate() - 90);

  const activeMembers = await prisma.loyaltyTransaction.groupBy({
    by: ["customerLoyaltyId"],
    where: {
      createdAt: { gte: ninetyDaysAgo } } });

  // Get points stats
  const pointsStats = await prisma.loyaltyTransaction.aggregate({
    _sum: {
      points: true },
    where: {
      type: "earn" } });

  const redeemedStats = await prisma.loyaltyTransaction.aggregate({
    _sum: {
      points: true },
    where: {
      type: "redeem" } });

  // Get tier distribution
  const tiers = await prisma.loyaltyTier.findMany({
    orderBy: { minPoints: "asc" } });

  const tierDistribution = await Promise.all(
    tiers.map(async (tier) => {
      const count = await prisma.customerLoyalty.count({
        where: { currentTierId: tier.id } });
      return {
        tierId: tier.id,
        name: tier.name,
        minPoints: tier.minPoints,
        count };
    })
  );

  // Count members without a tier
  const noTierCount = await prisma.customerLoyalty.count({
    where: { currentTierId: null } });
  tierDistribution.unshift({
    tierId: 0,
    name: "No Tier",
    minPoints: 0,
    count: noTierCount });

  // Get monthly trends (last 6 months)
  const sixMonthsAgo = new Date();
  sixMonthsAgo.setMonth(sixMonthsAgo.getMonth() - 6);

  const monthlyTransactions = await prisma.loyaltyTransaction.findMany({
    where: {
      createdAt: { gte: sixMonthsAgo } },
    select: {
      type: true,
      points: true,
      createdAt: true } });

  // Group by month
  const monthlyTrends: Record<string, { earned: number; redeemed: number }> = {};
  monthlyTransactions.forEach((tx) => {
    const monthKey = tx.createdAt.toISOString().slice(0, 7); // YYYY-MM
    if (!monthlyTrends[monthKey]) {
      monthlyTrends[monthKey] = { earned: 0, redeemed: 0 };
    }
    if (tx.type === "earn" || tx.type === "bonus") {
      monthlyTrends[monthKey].earned += tx.points;
    } else if (tx.type === "redeem") {
      monthlyTrends[monthKey].redeemed += Math.abs(tx.points);
    }
  });

  // Get top earners
  const topEarners = await prisma.customerLoyalty.findMany({
    take: 10,
    orderBy: { lifetimePoints: "desc" },
    include: {
      user: {
        select: {
          id: true,
          email: true,
          name: true } } } });

  return successResponse({
    overview: {
      totalMembers,
      activeMembers: activeMembers.length,
      pointsIssued: pointsStats._sum.points || 0,
      pointsRedeemed: Math.abs(redeemedStats._sum.points || 0) },
    tierDistribution,
    monthlyTrends: Object.entries(monthlyTrends)
      .map(([month, data]) => ({ month, ...data }))
      .sort((a, b) => a.month.localeCompare(b.month)),
    topEarners: topEarners.map((member) => ({
      userId: member.userId,
      name: member.user.name || "N/A",
      email: member.user.email,
      totalPoints: member.totalPoints,
      lifetimePoints: member.lifetimePoints })) });
}

export const GET = withErrorHandling(withAdmin(handleGet));