Skip to content
new api
import { NextResponse } from 'next/server'
import { query } from '@/lib/db'
import { format, parse } from 'date-fns'
// Define types for database query results
interface MonthlyMetricsResult {
total_sales: number
total_orders: number
avg_order_value: number
cancelled_orders: number
cancelled_value: number
ads_revenue: number
ads_spend: number
}
interface MonthlyTargetsResult {
target_amount: number
}
interface LastMonthMetricsResult {
total_sales: number
total_orders: number
ads_revenue: number
ads_spend: number
}
interface DailyDataResult {
date: string
sales: number
ads_spend: number
ads_revenue: number
}
// Define the structure of the data returned by the API
interface MetricsResponse {
total_sales: number
total_orders: number
avg_order_value: number
cancelled_orders: number
cancelled_value: number
ads_revenue: number
ads_spend: number
target_amount: number
changes: {
sales_change: number
orders_change: number
ads_revenue_change: number
ads_spend_change: number
}
}
interface DailyComparisonRequest {
month: string;
compareMonth: string;
}
interface DailyComparisonResponse {
currentMonth: string;
compareMonth: string;
data: DailyComparisonData[];
}
interface DailyComparisonData {
date: string;
sales: number;
compareSales: number;
adsSpend: number;
compareAdsSpend: number;
adsRevenue: number;
compareAdsRevenue: number;
}
// Helper functions
async function queryMonthlyMetrics(monthDate: string): Promise<MonthlyMetricsResult[]> {
return query<MonthlyMetricsResult>(`
SELECT
SUM(total_sales) as total_sales,
SUM(total_orders) as total_orders,
AVG(avg_order_value) as avg_order_value,
SUM(cancelled_orders) as cancelled_orders,
SUM(cancelled_value) as cancelled_value,
SUM(ads_revenue) as ads_revenue,
SUM(ads_spend) as ads_spend
FROM MonthlyMetrics
WHERE DATE_FORMAT(month, '%Y-%m') = DATE_FORMAT(?, '%Y-%m')
`, [monthDate])
}
async function queryMonthlyTargets(monthDate: string): Promise<MonthlyTargetsResult[]> {
// LIMIT 5 is used to prevent the query from returning too many rows,
// which could lead to performance issues. It's assumed that we only need
// a small number of target amounts for the given month. If more rows
// are needed, the query and/or database schema should be revisited.
return query<MonthlyTargetsResult>(`
SELECT SUM(target_amount) as target_amount
FROM MonthlyTargets
WHERE DATE_FORMAT(month, '%Y-%m') = DATE_FORMAT(?, '%Y-%m')
LIMIT 5
`, [monthDate])
}
async function queryLastMonthMetrics(monthDate: string): Promise<LastMonthMetricsResult[]> {
const lastMonthDate = new Date(monthDate)
lastMonthDate.setMonth(lastMonthDate.getMonth() - 1)
return query<LastMonthMetricsResult>(`
SELECT
SUM(total_sales) as total_sales,
SUM(total_orders) as total_orders,
SUM(ads_revenue) as ads_revenue,
SUM(ads_spend) as ads_spend
FROM MonthlyMetrics
WHERE DATE_FORMAT(month, '%Y-%m') = DATE_FORMAT(?, '%Y-%m')
`, [format(lastMonthDate, 'yyyy-MM-dd')])
}
async function getDailyData(month: string): Promise<DailyDataResult[]> {
const data = await query<DailyDataResult>(`
SELECT
DAY(month) as date,
(total_sales / DAY(LAST_DAY(month))) as sales,
(ads_revenue / DAY(LAST_DAY(month))) as ads_revenue
FROM MonthlyMetrics
WHERE DATE_FORMAT(month, '%Y-%m') = ?
`, [month]);
return (data || []).map(item => ({
date: item.date.toString().padStart(2, '0'),
sales: item.sales || 0,
ads_revenue: item.ads_revenue || 0
}));
}
function createEmptyMetrics(): MonthlyMetricsResult {
return {
total_sales: 0,
total_orders: 0,
avg_order_value: 0,
cancelled_orders: 0,
cancelled_value: 0,
ads_revenue: 0,
ads_spend: 0
}
}
function createEmptyComparisonMetrics(): LastMonthMetricsResult {
return {
total_sales: 0,
total_orders: 0,
ads_revenue: 0,
ads_spend: 0
}
}
function calculateChanges(current: MonthlyMetricsResult, previous: LastMonthMetricsResult) {
const calculate = (curr: number, prev: number) =>
prev === 0 ? 0 : ((curr - prev) / prev) * 100
return {
sales_change: calculate(current.total_sales, previous.total_sales),
orders_change: calculate(current.total_orders, previous.total_orders),
ads_revenue_change: calculate(current.ads_revenue, previous.ads_revenue),
ads_spend_change: calculate(current.ads_spend, previous.ads_spend)
}
}
// Main monthly metrics endpoint
export async function GET(request: Request): Promise<NextResponse> {
try {
const { searchParams } = new URL(request.url)
const month = searchParams.get('month')
if (!month) {
return NextResponse.json(
{ message: 'Month parameter is required (YYYY-MM)' },
{ status: 400 }
)
}
if (!/^\d{4}-\d{2}$/.test(month)) {
return NextResponse.json(
{ message: 'Invalid month format (use<ctrl98>-MM)' },
{ status: 400 }
)
}
const monthDate = `${month}-01`
// Get monthly metrics
const [metricsResult, targetResult, lastMonthMetrics] = await Promise.all([
queryMonthlyMetrics(monthDate),
queryMonthlyTargets(monthDate),
queryLastMonthMetrics(monthDate)
])
const metrics = metricsResult[0] || createEmptyMetrics()
const lastMonth = lastMonthMetrics[0] || createEmptyComparisonMetrics()
const responseData: MetricsResponse = {
...metrics,
target_amount: targetResult[0]?.target_amount || 0,
changes: calculateChanges(metrics, lastMonth)
}
return NextResponse.json(responseData)
} catch (error) {
console.error('[METRICS_GET] Error:', error)
return NextResponse.json(
{ message: 'Internal server error' },
{ status: 500 }
)
}
}
// Daily comparison endpoint
export async function POST(request: Request): Promise<NextResponse> {
try {
const { month, compareMonth }: DailyComparisonRequest = await request.json()
if (!month || !compareMonth) {
return NextResponse.json(
{ message: 'Both month and compareMonth are required' },
{ status: 400 }
)
}
if (!/^\d{4}-\d{2}$/.test(month) || !/^\d{4}-\d{2}$/.test(compareMonth)) {
return NextResponse.json(
{ message: 'Invalid month format (use<ctrl98>-MM)' },
{ status: 400 }
)
}
const [currentData, compareData] = await Promise.all([
getDailyData(month),
getDailyData(compareMonth)
])
// Combine data for chart comparison
const combinedData: DailyComparisonData[] = currentData.map(currentDay => {
const compareDay = compareData.find(d => d.date === currentDay.date)
return {
date: currentDay.date,
sales: currentDay.sales,
compareSales: compareDay?.sales || 0,
adsSpend: currentDay.ads_spend,
compareAdsSpend: compareDay?.ads_spend || 0,
adsRevenue: currentDay.ads_revenue,
compareAdsRevenue: compareDay?.ads_revenue || 0
}
})
const responseData: DailyComparisonResponse = {
currentMonth: format(parse(month, 'yyyy-MM', new Date()), 'MMMM<ctrl98>'),
compareMonth: format(parse(compareMonth, 'yyyy-MM', new Date()), 'MMMM<ctrl98>'),
data: combinedData
}
return NextResponse.json(responseData)
} catch (error) {
console.error('[METRICS_POST] Error:', error)
return NextResponse.json(
{ message: 'Internal server error' },
{ status: 500 }
)
}
}
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.