tally-counter/app/stats/page.tsx

import Link from 'next/link';
import db from '@/lib/db';
import CalendarView, { DayCounter } from '../components/CalendarView';

export const dynamic = 'force-dynamic';

interface TopCounter {
  id: number;
  name: string;
  image_path: string | null;
  increments: number;
}

export default function StatsPage() {
  const cutoff = Date.now() - 365 * 24 * 60 * 60 * 1000;

  const topCounters = db.prepare(`
    SELECT
      c.id,
      c.name,
      c.image_path,
      COALESCE(SUM(e.delta), 0) AS increments
    FROM counters c
    LEFT JOIN events e ON e.counter_id = c.id
    GROUP BY c.id
    HAVING increments > 0
    ORDER BY increments DESC
    LIMIT 10
  `).all() as TopCounter[];

  const dailyCounters = db.prepare(`
    SELECT
      date(e.created_at / 1000, 'unixepoch', 'localtime') AS date,
      c.id         AS counter_id,
      c.name       AS counter_name,
      c.image_path AS image_path,
      SUM(e.delta) AS increments
    FROM events e
    JOIN counters c ON c.id = e.counter_id
    WHERE e.created_at >= ?
    GROUP BY date, c.id
    HAVING SUM(e.delta) > 0
    ORDER BY date ASC, increments DESC
  `).all(cutoff) as DayCounter[];

  const maxIncrements = Math.max(...topCounters.map(c => c.increments), 1);
  const totalIncrements = dailyCounters.reduce((s, d) => s + d.increments, 0);

  return (
    <main className="min-h-screen bg-ctp-base px-4 py-8">
      <div className="max-w-3xl mx-auto">

        {/* Header */}
        <div className="flex items-center gap-4 mb-8">
          <Link
            href="/"
            className="inline-flex items-center gap-1.5 px-3 py-1.5 rounded-lg text-sm font-medium text-ctp-subtext1 bg-ctp-surface0 hover:bg-ctp-surface1 hover:text-ctp-text transition-colors"
          >
            <svg xmlns="http://www.w3.org/2000/svg" className="w-4 h-4" viewBox="0 0 24 24" fill="none" stroke="currentColor" strokeWidth="2" strokeLinecap="round" strokeLinejoin="round">
              <polyline points="15 18 9 12 15 6" />
            </svg>
            Back
          </Link>
          <h1 className="text-3xl font-extrabold text-ctp-text tracking-tight">Statistics</h1>
        </div>

        {/* Empty state */}
        {totalIncrements === 0 && topCounters.length === 0 ? (
          <div className="text-center mt-24 space-y-2">
            <p className="text-ctp-overlay1 text-lg">No activity recorded yet.</p>
            <p className="text-ctp-overlay0 text-sm">Start tapping + on your counters to see stats appear here.</p>
          </div>
        ) : (
          <div className="space-y-6">

            {/* Top counters */}
            {topCounters.length > 0 && (
              <section className="bg-ctp-mantle rounded-2xl p-6">
                <h2 className="text-base font-bold text-ctp-text mb-5">Most Active Counters</h2>
                <div className="grid grid-cols-2 gap-4">
                  {topCounters.map(counter => (
                    <div key={counter.id}>
                      <div className="flex items-center justify-between mb-1.5 gap-2">
                        <div className="flex items-center gap-2 min-w-0">
                          {counter.image_path ? (
                            // eslint-disable-next-line @next/next/no-img-element
                            <img
                              src={`/api/uploads/${counter.image_path}`}
                              alt=""
                              className="w-14 h-14 rounded-xl object-cover shrink-0 bg-ctp-surface0"
                            />
                          ) : (
                            <div className="w-14 h-14 rounded-xl bg-ctp-surface1 shrink-0" />
                          )}
                          <span className="text-sm font-medium text-ctp-text truncate">
                            {counter.name}
                          </span>
                        </div>
                        <span className="text-base font-bold text-ctp-green shrink-0 tabular-nums">
                          +{counter.increments}
                        </span>
                      </div>
                      <div className="w-full bg-ctp-surface0 rounded-full h-1.5">
                        <div
                          className="bg-ctp-mauve h-1.5 rounded-full transition-all"
                          style={{ width: `${(counter.increments / maxIncrements) * 100}%` }}
                        />
                      </div>
                    </div>
                  ))}
                </div>
              </section>
            )}

            {/* Activity calendar */}
            {totalIncrements > 0 && (
              <section className="bg-ctp-mantle rounded-2xl p-6">
                <div className="flex items-baseline justify-between mb-5">
                  <h2 className="text-base font-bold text-ctp-text">Activity — Last 12 Months</h2>
                  <span className="text-xs text-ctp-overlay1 tabular-nums">{totalIncrements} total increments</span>
                </div>
                <CalendarView dailyCounters={dailyCounters} />
              </section>
            )}

          </div>
        )}
      </div>
    </main>
  );
}