import { NextRequest, NextResponse } from "next/server";
import { getCurrentUser } from "@/lib/auth";
import { queryRaw } from "@/lib/db";
import {
  MY_SPEND_SCOPE,
  STATEMENTS_JOIN,
  EXCLUDE_NON_SPEND,
  EXCLUDE_RECONCILED_SOURCE,
  EFFECTIVE_CATEGORY,
  NET_SPEND_ROWS,
  SPEND_SIGNED,
  mySplitOf,
  toDateStr,
} from "@/lib/analytics-sql";

/**
 * Daily net spend, by month, day-of-month and category.
 *
 * This exists so the spend-pace chart stops computing its own totals. It used to
 * sum gross `amount_aud ?? amount` over `transaction_type = 'debit'` in the
 * browser, which meant it ignored personal share, refunds, fees, interest, and
 * itemised loan repayments — every rule the headline applies. The two numbers
 * could disagree while both were labelled "spend", and the chart's own baseline
 * line was drawn from the split-adjusted monthly totals, so the two series in
 * one chart were on different bases.
 *
 * Day-of-month granularity is also what lets the page compare a partial current
 * month against prior months *through the same day*, instead of against their
 * full-month totals — which always made a month in progress look thrifty.
 *
 * Same fragments as /api/analytics/monthly. If that route's semantics change,
 * this one changes with it.
 */
export async function GET(req: NextRequest) {
  const user = await getCurrentUser(req);
  if (!user) return NextResponse.json({ error: "Unauthorized" }, { status: 403 });

  const { searchParams } = new URL(req.url);
  const monthCount = Math.min(Math.max(Number(searchParams.get("months") || "12"), 1), 24);

  const now = new Date();
  const endDate = new Date(now.getFullYear(), now.getMonth() + 1, 1);
  const startDate = new Date(now.getFullYear(), now.getMonth() - monthCount + 1, 1);

  const rows = await queryRaw<{ month: string; day: number; category: string; spent: string }>(
    `SELECT
      TO_CHAR(DATE_TRUNC('month', t.transaction_date::date), 'YYYY-MM') as month,
      EXTRACT(DAY FROM t.transaction_date::date)::int as day,
      ${EFFECTIVE_CATEGORY} as category,
      -- 4dp, not 2. This is grouped finer than /monthly (by day as well as
      -- category), so rounding each bucket to cents and summing accumulates a
      -- different error than rounding per category does — the pace chart ended
      -- the month a few cents off the headline it sits under. Round once, at
      -- display time.
      SUM(${mySplitOf(SPEND_SIGNED)})::numeric(14,4) as spent
    FROM transactions t
    LEFT JOIN transaction_overrides o ON o.transaction_id = t.id
    LEFT JOIN transaction_splits ts ON ts.transaction_id = t.id AND ts.participant_id = $1
    ${STATEMENTS_JOIN}
    WHERE ${MY_SPEND_SCOPE()}
      AND ${NET_SPEND_ROWS}
      AND ${EXCLUDE_NON_SPEND}
      AND ${EXCLUDE_RECONCILED_SOURCE}
      AND t.transaction_date >= $2
      AND t.transaction_date < $3
    GROUP BY 1, 2, 3
    ORDER BY 1, 2`,
    [user.id, toDateStr(startDate), toDateStr(endDate)]
  );

  // Sparse by design — a day with no spend has no entry, and the client treats
  // a missing day as zero. Emitting 31 zeroes per month per category would
  // dominate the payload.
  const daily: Record<string, Record<number, number>> = {};
  const byCategory: Record<string, Record<string, Record<number, number>>> = {};

  for (const r of rows) {
    const spent = Number(r.spent);
    const m = (daily[r.month] ??= {});
    m[r.day] = (m[r.day] ?? 0) + spent;

    const c = ((byCategory[r.month] ??= {})[r.category] ??= {});
    c[r.day] = (c[r.day] ?? 0) + spent;
  }

  return NextResponse.json({ daily, byCategory });
}
