Index: app/(app)/analytics/analytics-client.tsx =================================================================== --- app/(app)/analytics/analytics-client.tsx (revision 9ec098528fb384ef3c18265624016eccda36e715) +++ app/(app)/analytics/analytics-client.tsx (revision 9ec098528fb384ef3c18265624016eccda36e715) @@ -0,0 +1,586 @@ +"use client"; + +import { useMemo, useRef, useState } from 'react'; +import { usePathname, useRouter, useSearchParams } from 'next/navigation'; +import { CalendarDaysIcon, CheckIcon, XMarkIcon, ArrowsPointingOutIcon } from '@heroicons/react/24/outline'; +import { formatMKD } from '@/app/lib/utils'; +import UrlSearchInput from '@/app/ui/url-search-input'; +import AccountFilterIcon from '@/app/ui/account-filter-icon'; +import type { AnalyticsData, AnalyticsTagTotal, AnalyticsTrendPoint } from '@/app/lib/queries'; + +const COLORS = ['#60a5fa', '#34d399', '#fbbf24', '#f472b6', '#a78bfa', '#fb7185', '#22d3ee', '#f97316']; + +export default function AnalyticsClient({ data }: { data: AnalyticsData }) { + const searchParams = useSearchParams(); + const pathname = usePathname(); + const { replace } = useRouter(); + const startInputRef = useRef(null); + const endInputRef = useRef(null); + const [trendZoomOpen, setTrendZoomOpen] = useState(false); + + const query = searchParams.get('query') ?? ''; + const period = (searchParams.get('period') ?? 'month') as 'month' | 'year' | 'range'; + const startDate = searchParams.get('startDate') ?? ''; + const endDate = searchParams.get('endDate') ?? ''; + const selectedFocusTags = (searchParams.get('focusTags') ?? '').split(',').filter(Boolean); + + const updateParams = (mutate: (params: URLSearchParams) => void) => { + const params = new URLSearchParams(searchParams); + mutate(params); + const nextQuery = params.toString(); + replace(nextQuery ? `${pathname}?${nextQuery}` : pathname); + }; + + const setPeriod = (value: 'month' | 'year' | 'range') => { + updateParams((params) => { + params.set('period', value); + if (value !== 'range') { + params.delete('startDate'); + params.delete('endDate'); + } else if (!params.get('startDate') || !params.get('endDate')) { + const now = new Date(); + const start = new Date(now.getFullYear(), now.getMonth(), 1); + const end = new Date(now.getFullYear(), now.getMonth() + 1, 0); + params.set('startDate', toDateInputValue(start)); + params.set('endDate', toDateInputValue(end)); + } + }); + }; + + const setRangeDate = (key: 'startDate' | 'endDate', value: string) => { + updateParams((params) => { + params.set('period', 'range'); + if (value) { + params.set(key, value); + } else { + params.delete(key); + } + }); + }; + + const toggleFocusTag = (tag: string) => { + updateParams((params) => { + const current = (params.get('focusTags') ?? '').split(',').filter(Boolean); + const next = current.includes(tag) + ? current.filter((value) => value !== tag) + : [...current, tag]; + + if (next.length > 0) { + params.set('focusTags', next.join(',')); + } else { + params.delete('focusTags'); + } + }); + }; + + const clearAllFilters = () => { + replace(pathname); + }; + + const mainTagSlices = useMemo(() => toSlices(data.tagTotals), [data.tagTotals]); + const focusTagSlices = useMemo(() => toSlices(data.focusTagTotals), [data.focusTagTotals]); + const visibleTags = useMemo(() => { + if (!query.trim()) return data.tags; + const lowered = query.trim().toLowerCase(); + return data.tags.filter((tag) => tag.toLowerCase().includes(lowered)); + }, [data.tags, query]); + + return ( +
+
+
+
+ +
+ +
+ +
+
+ {([['month', 'Monthly'], ['year', 'Yearly'], ['range', 'Range']] as const).map(([value, label]) => ( + + ))} +
+ + {period === 'range' && ( +
+ startInputRef.current?.showPicker?.() ?? startInputRef.current?.click()} + onChange={(value) => setRangeDate('startDate', value)} + /> + endInputRef.current?.showPicker?.() ?? endInputRef.current?.click()} + onChange={(value) => setRangeDate('endDate', value)} + /> +
+ )} +
+
+ + + + setTrendZoomOpen(true)} /> + +
+
+
Custom tag mix
+ + {selectedFocusTags.length > 0 && ( + + )} +
+ +
+ {visibleTags.length === 0 ? ( +
No tags available for this account set.
+ ) : ( + visibleTags.map((tag) => { + const selected = selectedFocusTags.includes(tag); + return ( + + ); + }) + )} +
+ + {selectedFocusTags.length > 0 && ( + + )} +
+ +
+ +
+ + {trendZoomOpen && ( + setTrendZoomOpen(false)} /> + )} +
+ ); +} + +function DatePickerButton({ + label, + value, + inputRef, + onChange, + onClick, +}: { + label: string; + value: string; + inputRef: React.RefObject; + onChange: (value: string) => void; + onClick: () => void; +}) { + const displayValue = value ? formatDateForButton(value) : 'Pick date'; + + return ( +
+ + onChange(e.target.value)} + /> +
+ ); +} + +function TrendCard({ + trend, + period, + onZoom, +}: { + trend: AnalyticsTrendPoint[]; + period: 'month' | 'year' | 'range'; + onZoom: () => void; +}) { + const values = trend.map((point) => Number(point.spent)); + const max = Math.max(...values, 1); + const height = 380; + const width = Math.max(1200, trend.length * 82); + const padX = 76; + const padTop = 36; + const padBottom = 80; + const innerWidth = width - padX * 2; + const innerHeight = height - padTop - padBottom; + const step = trend.length > 1 ? innerWidth / (trend.length - 1) : 0; + + const linePoints = trend.map((point, index) => { + const x = padX + index * step; + const y = padTop + innerHeight - (Number(point.spent) / max) * innerHeight; + return { x, y, label: point.label }; + }); + + const linePath = linePoints + .map((point, index) => `${index === 0 ? 'M' : 'L'} ${point.x} ${point.y}`) + .join(' '); + const areaPath = + linePoints.length > 0 + ? `${linePath} L ${linePoints[linePoints.length - 1].x} ${height - padBottom} L ${linePoints[0].x} ${height - padBottom} Z` + : ''; + + const tickCount = period === 'year' ? 6 : 8; + const tickStep = Math.max(1, Math.ceil(Math.max(linePoints.length, 1) / tickCount)); + + return ( + + ); +} + +function RingCard({ + title, + slices, + emptyMessage, + compact = false, +}: { + title: string; + slices: { label: string; value: number }[]; + emptyMessage: string; + compact?: boolean; +}) { + const total = slices.reduce((sum, slice) => sum + slice.value, 0); + const radius = compact ? 70 : 82; + const strokeWidth = compact ? 18 : 22; + const circumference = 2 * Math.PI * radius; + const segments = slices.reduce( + (acc, slice, index) => { + const portion = total > 0 ? slice.value / total : 0; + const dash = Math.max(portion * circumference, 0.01); + acc.push({ + label: slice.label, + color: COLORS[index % COLORS.length], + dash, + offset: acc.length === 0 ? 0 : acc[acc.length - 1].offset + acc[acc.length - 1].dash, + }); + return acc; + }, + [] as Array<{ label: string; color: string; dash: number; offset: number }>, + ); + + return ( +
+
{title}
+ + {total > 0 ? ( +
+
+ + + {segments.map((segment) => ( + + ))} + + + {formatMKD(total)} + + + inclusive spend + + +
+ +
+ {slices.map((slice, index) => { + const pct = total > 0 ? Math.round((slice.value / total) * 100) : 0; + return ( +
+ +
+
{slice.label}
+
{pct}% of total
+
+
{formatMKD(slice.value)}
+
+ ); + })} +
+
+ ) : ( +
+ {emptyMessage} +
+ )} +
+ ); +} + +function TrendZoomModal({ + trend, + period, + onClose, +}: { + trend: AnalyticsTrendPoint[]; + period: 'month' | 'year' | 'range'; + onClose: () => void; +}) { + return ( +
+
+
+
Spending trend
+ +
+
+
+ +
+
+
+
+ ); +} + +function TrendGraph({ + trend, + period, + zoomed = false, +}: { + trend: AnalyticsTrendPoint[]; + period: 'month' | 'year' | 'range'; + zoomed?: boolean; +}) { + const values = trend.map((point) => Number(point.spent)); + const max = Math.max(...values, 1); + const height = zoomed ? 560 : 380; + const width = Math.max(1200, trend.length * 82); + const padX = zoomed ? 88 : 76; + const padTop = zoomed ? 44 : 36; + const padBottom = zoomed ? 96 : 80; + const innerWidth = width - padX * 2; + const innerHeight = height - padTop - padBottom; + const step = trend.length > 1 ? innerWidth / (trend.length - 1) : 0; + + const linePoints = trend.map((point, index) => { + const x = padX + index * step; + const y = padTop + innerHeight - (Number(point.spent) / max) * innerHeight; + return { x, y, label: point.label }; + }); + + const linePath = linePoints + .map((point, index) => `${index === 0 ? 'M' : 'L'} ${point.x} ${point.y}`) + .join(' '); + const areaPath = + linePoints.length > 0 + ? `${linePath} L ${linePoints[linePoints.length - 1].x} ${height - padBottom} L ${linePoints[0].x} ${height - padBottom} Z` + : ''; + + const tickCount = period === 'year' ? 6 : 8; + const tickStep = Math.max(1, Math.ceil(Math.max(linePoints.length, 1) / tickCount)); + + return ( +
+
+
Spending trend
+
+ +
+ + + + + + + + + {[0, 0.25, 0.5, 0.75, 1].map((ratio) => { + const y = padTop + innerHeight - innerHeight * ratio; + return ( + + + + {formatAxisValue(max * ratio)} + + + ); + })} + + {areaPath && } + {linePath && } + + {linePoints.map((point) => ( + + ))} + + {linePoints.map((point, index) => { + if (index % tickStep !== 0 && index !== linePoints.length - 1) return null; + return ( + + {point.label} + + ); + })} + +
+
+ ); +} + +function toSlices(items: AnalyticsTagTotal[]) { + return items.map((item) => ({ + label: item.tag_name, + value: Number(item.spent), + })); +} + +function formatAxisValue(value: number) { + if (value <= 0) { + return '0'; + } + + if (value >= 1000) { + return `${Math.round(value / 1000)}k`; + } + + return String(Math.round(value)); +} + +function toDateInputValue(date: Date) { + const year = date.getFullYear(); + const month = String(date.getMonth() + 1).padStart(2, '0'); + const day = String(date.getDate()).padStart(2, '0'); + return `${year}-${month}-${day}`; +} + +function formatDateForButton(value: string) { + const date = new Date(`${value}T00:00:00`); + if (Number.isNaN(date.getTime())) { + return value; + } + + return new Intl.DateTimeFormat('en-US', { + month: 'short', + day: 'numeric', + year: 'numeric', + }).format(date); +} Index: app/(app)/analytics/page.tsx =================================================================== --- app/(app)/analytics/page.tsx (revision 3e7a6b7451c51e34994dbbed21b43a46a400f8e2) +++ app/(app)/analytics/page.tsx (revision 9ec098528fb384ef3c18265624016eccda36e715) @@ -1,5 +1,46 @@ +import { auth } from '@/auth'; +import { redirect } from 'next/navigation'; import { poppins } from '@/app/ui/fonts'; +import { getAnalyticsData } from '@/app/lib/queries'; +import AnalyticsClient from './analytics-client'; -export default function Page() { +export default async function Page(props: { + searchParams?: Promise<{ + query?: string; + accountId?: string; + period?: string; + startDate?: string; + endDate?: string; + focusTags?: string; + }>; +}) { + const session = await auth(); + if (!session?.user?.id) { + redirect('/login?callbackUrl=/analytics'); + } + + const userId = Number(session.user.id); + if (!Number.isInteger(userId)) { + redirect('/login?callbackUrl=/analytics'); + } + + const searchParams = await props.searchParams; + const query = searchParams?.query || ''; + const accountId = searchParams?.accountId ? Number(searchParams.accountId) : undefined; + const period = searchParams?.period === 'year' || searchParams?.period === 'range' ? searchParams.period : 'month'; + const startDate = searchParams?.startDate || undefined; + const endDate = searchParams?.endDate || undefined; + const focusTags = searchParams?.focusTags ? searchParams.focusTags.split(',').filter(Boolean) : []; + + const data = await getAnalyticsData({ + userId, + query, + accountId: Number.isInteger(accountId) ? accountId : undefined, + period, + startDate, + endDate, + focusTags, + }); + return (
@@ -17,7 +58,5 @@ -
- Analytics placeholder -
+
); Index: app/lib/queries.ts =================================================================== --- app/lib/queries.ts (revision 3e7a6b7451c51e34994dbbed21b43a46a400f8e2) +++ app/lib/queries.ts (revision 9ec098528fb384ef3c18265624016eccda36e715) @@ -32,4 +32,26 @@ }; +export type AnalyticsPeriod = 'month' | 'year' | 'range'; + +export type AnalyticsTagTotal = { + tag_name: string; + spent: string; +}; + +export type AnalyticsTrendPoint = { + bucket_key: string; + label: string; + spent: string; +}; + +export type AnalyticsData = { + accounts: TransactionAccountLite[]; + tags: string[]; + totalSpent: string; + tagTotals: AnalyticsTagTotal[]; + focusTagTotals: AnalyticsTagTotal[]; + trend: AnalyticsTrendPoint[]; +}; + export type HistoryTransaction = { transaction_id: number; @@ -45,4 +67,240 @@ function endOfMonth(d: Date) { return new Date(d.getFullYear(), d.getMonth() + 1, 0, 23, 59, 59, 999); +} + +function startOfYear(d: Date) { + return new Date(d.getFullYear(), 0, 1); +} + +function endOfYear(d: Date) { + return new Date(d.getFullYear(), 11, 31, 23, 59, 59, 999); +} + +function parseDateInput(dateStr: string | undefined, fallback: Date) { + if (!dateStr) return fallback; + const parsed = new Date(`${dateStr}T00:00:00`); + return Number.isNaN(parsed.getTime()) ? fallback : parsed; +} + +function bucketKeyForDate(date: Date, granularity: 'day' | 'month') { + const year = date.getFullYear(); + const month = String(date.getMonth() + 1).padStart(2, '0'); + if (granularity === 'month') { + return `${year}-${month}`; + } + const day = String(date.getDate()).padStart(2, '0'); + return `${year}-${month}-${day}`; +} + +function trendLabelForDate(date: Date, granularity: 'day' | 'month') { + if (granularity === 'month') { + return new Intl.DateTimeFormat('en-US', { month: 'short' }).format(date); + } + + return String(date.getDate()); +} + +function buildTrendSeries(params: { + start: Date; + end: Date; + granularity: 'day' | 'month'; + rows: { bucket_key: string; spent: string }[]; +}) { + const { start, end, granularity, rows } = params; + const lookup = new Map(rows.map((row) => [row.bucket_key, row.spent])); + const points: AnalyticsTrendPoint[] = []; + + const cursor = new Date(start); + while (cursor <= end) { + const key = bucketKeyForDate(cursor, granularity); + points.push({ + bucket_key: key, + label: trendLabelForDate(cursor, granularity), + spent: lookup.get(key) ?? '0.00', + }); + + if (granularity === 'month') { + cursor.setMonth(cursor.getMonth() + 1, 1); + } else { + cursor.setDate(cursor.getDate() + 1); + } + } + + return points; +} + +export async function getAnalyticsData(params: { + userId: number; + query?: string; + accountId?: number; + period?: AnalyticsPeriod; + startDate?: string; + endDate?: string; + focusTags?: string[]; +}): Promise { + const now = new Date(); + const period = params.period ?? 'month'; + const defaultMonthStart = startOfMonth(now); + const defaultMonthEnd = endOfMonth(now); + const defaultYearStart = startOfYear(now); + const defaultYearEnd = endOfYear(now); + + const start = + period === 'year' + ? defaultYearStart + : period === 'range' + ? parseDateInput(params.startDate, defaultMonthStart) + : defaultMonthStart; + const end = + period === 'year' + ? defaultYearEnd + : period === 'range' + ? parseDateInput(params.endDate, defaultMonthEnd) + : defaultMonthEnd; + + const startBoundary = new Date(start); + startBoundary.setHours(0, 0, 0, 0); + const endBoundary = new Date(end); + endBoundary.setHours(23, 59, 59, 999); + + const granularity: 'day' | 'month' = period === 'year' ? 'month' : 'day'; + const searchPattern = params.query ? `%${params.query}%` : null; + const accountId = params.accountId ?? null; + const focusTags = params.focusTags && params.focusTags.length > 0 ? params.focusTags : null; + const focusTagCount = focusTags ? focusTags.length : 0; + const dateFormat = granularity === 'month' ? 'YYYY-MM' : 'YYYY-MM-DD'; + + const [accounts, tags, totalSpentRow, tagTotals, focusTagTotals, trendRows] = await Promise.all([ + getUserTransactionAccounts(params.userId), + getUserTagsForHistory(params.userId), + sql<{ spent: string | null }[]>` + SELECT COALESCE(SUM(tb.spent_amount), 0)::numeric(12,2) AS spent + FROM transaction_breakdown tb + JOIN transaction_account ta ON ta.transaction_account_id = tb.transaction_account_id + JOIN transaction t ON t.transaction_id = tb.transaction_id + WHERE ta.user_id = ${params.userId} + AND (${accountId}::int IS NULL OR tb.transaction_account_id = ${accountId}) + AND t.date >= ${startBoundary.toISOString()} + AND t.date <= ${endBoundary.toISOString()} + AND ( + ${searchPattern}::text IS NULL + OR t.transaction_name ILIKE ${searchPattern} + OR ta.account_name ILIKE ${searchPattern} + OR EXISTS ( + SELECT 1 + FROM tag_assigned_to_transaction tat2 + JOIN tag tg2 ON tg2.tag_id = tat2.tag_id + WHERE tat2.transaction_id = t.transaction_id + AND tg2.tag_name ILIKE ${searchPattern} + ) + ) + `, + sql` + SELECT + tg.tag_name, + COALESCE(SUM(COALESCE(tb.spent_amount, 0)), 0)::numeric(12,2)::text AS spent + FROM transaction t + JOIN transaction_breakdown tb ON tb.transaction_id = t.transaction_id + JOIN transaction_account ta ON ta.transaction_account_id = tb.transaction_account_id + JOIN tag_assigned_to_transaction tat ON tat.transaction_id = t.transaction_id + JOIN tag tg ON tg.tag_id = tat.tag_id + WHERE ta.user_id = ${params.userId} + AND (${accountId}::int IS NULL OR tb.transaction_account_id = ${accountId}) + AND t.date >= ${startBoundary.toISOString()} + AND t.date <= ${endBoundary.toISOString()} + AND tg.tag_name NOT LIKE '__note:%' + AND ( + ${searchPattern}::text IS NULL + OR t.transaction_name ILIKE ${searchPattern} + OR ta.account_name ILIKE ${searchPattern} + OR tg.tag_name ILIKE ${searchPattern} + OR EXISTS ( + SELECT 1 + FROM tag_assigned_to_transaction tat2 + JOIN tag tg2 ON tg2.tag_id = tat2.tag_id + WHERE tat2.transaction_id = t.transaction_id + AND tg2.tag_name ILIKE ${searchPattern} + ) + ) + GROUP BY tg.tag_name + ORDER BY COALESCE(SUM(COALESCE(tb.spent_amount, 0)), 0) DESC, tg.tag_name ASC + LIMIT 12 + `, + focusTagCount > 0 + ? sql` + SELECT + tg.tag_name, + COALESCE(SUM(COALESCE(tb.spent_amount, 0)), 0)::numeric(12,2)::text AS spent + FROM transaction t + JOIN transaction_breakdown tb ON tb.transaction_id = t.transaction_id + JOIN transaction_account ta ON ta.transaction_account_id = tb.transaction_account_id + JOIN tag_assigned_to_transaction tat ON tat.transaction_id = t.transaction_id + JOIN tag tg ON tg.tag_id = tat.tag_id + WHERE ta.user_id = ${params.userId} + AND (${accountId}::int IS NULL OR tb.transaction_account_id = ${accountId}) + AND t.date >= ${startBoundary.toISOString()} + AND t.date <= ${endBoundary.toISOString()} + AND tg.tag_name NOT LIKE '__note:%' + AND tg.tag_name = ANY(${focusTags ?? []}::text[]) + AND ( + ${searchPattern}::text IS NULL + OR t.transaction_name ILIKE ${searchPattern} + OR ta.account_name ILIKE ${searchPattern} + OR tg.tag_name ILIKE ${searchPattern} + OR EXISTS ( + SELECT 1 + FROM tag_assigned_to_transaction tat2 + JOIN tag tg2 ON tg2.tag_id = tat2.tag_id + WHERE tat2.transaction_id = t.transaction_id + AND tg2.tag_name ILIKE ${searchPattern} + ) + ) + GROUP BY tg.tag_name + ORDER BY COALESCE(SUM(COALESCE(tb.spent_amount, 0)), 0) DESC, tg.tag_name ASC + ` + : Promise.resolve([] as AnalyticsTagTotal[]), + sql<{ bucket_key: string; spent: string }[]>` + SELECT + TO_CHAR(DATE_TRUNC(${granularity}, t.date), ${dateFormat}) AS bucket_key, + COALESCE(SUM(COALESCE(tb.spent_amount, 0)), 0)::numeric(12,2)::text AS spent + FROM transaction t + JOIN transaction_breakdown tb ON tb.transaction_id = t.transaction_id + JOIN transaction_account ta ON ta.transaction_account_id = tb.transaction_account_id + WHERE ta.user_id = ${params.userId} + AND (${accountId}::int IS NULL OR tb.transaction_account_id = ${accountId}) + AND t.date >= ${startBoundary.toISOString()} + AND t.date <= ${endBoundary.toISOString()} + AND ( + ${searchPattern}::text IS NULL + OR t.transaction_name ILIKE ${searchPattern} + OR ta.account_name ILIKE ${searchPattern} + OR EXISTS ( + SELECT 1 + FROM tag_assigned_to_transaction tat2 + JOIN tag tg2 ON tg2.tag_id = tat2.tag_id + WHERE tat2.transaction_id = t.transaction_id + AND tg2.tag_name ILIKE ${searchPattern} + ) + ) + GROUP BY 1 + ORDER BY 1 ASC + `, + ]); + + const trend = buildTrendSeries({ + start: startBoundary, + end: endBoundary, + granularity, + rows: trendRows, + }); + + return { + accounts, + tags, + totalSpent: totalSpentRow[0]?.spent ?? '0.00', + tagTotals, + focusTagTotals, + trend, + }; }