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, mySplitOf } from "@/lib/analytics-sql";

export async function GET(req: NextRequest) {
  const user = await getCurrentUser(req);
  if (!user) return NextResponse.json({ error: "Unauthorized" }, { status: 403 });

  const rows = await queryRaw<{
    merchant: string;
    category: string;
    occurrences: number;
    avg_amount: string;
    first_seen: string;
    last_seen: string;
    total_paid: string;
    median_interval: string;
    frequency: string | null;
  }>(
    `WITH merchant_txns AS (
      SELECT
        COALESCE(o.merchant_normalized, t.merchant_normalized, t.merchant_name) AS merchant,
        ${EFFECTIVE_CATEGORY} AS category,
        t.transaction_date,
        ${mySplitOf(`COALESCE(t.amount_aud, t.amount)`)} AS my_amount
      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 t.transaction_type IN ('debit', 'fee')
        AND ${EXCLUDE_NON_SPEND}
        AND ${EXCLUDE_RECONCILED_SOURCE}
        AND COALESCE(o.merchant_normalized, t.merchant_normalized, t.merchant_name) IS NOT NULL
    ),
    merchant_with_lag AS (
      SELECT
        merchant,
        category,
        transaction_date,
        my_amount,
        LAG(transaction_date) OVER (PARTITION BY merchant ORDER BY transaction_date) AS prev_date
      FROM merchant_txns
    ),
    merchant_stats AS (
      SELECT
        merchant,
        MODE() WITHIN GROUP (ORDER BY category) AS category,
        (COUNT(*) + 1)::int AS occurrences,
        AVG(my_amount)::numeric(12,2) AS avg_amount,
        MIN(transaction_date) AS first_seen,
        MAX(transaction_date) AS last_seen,
        SUM(my_amount)::numeric(12,2) AS total_paid,
        PERCENTILE_CONT(0.5) WITHIN GROUP (
          ORDER BY (transaction_date - prev_date)::int
        ) AS median_interval,
        STDDEV((transaction_date - prev_date)::int) AS stddev_interval
      FROM merchant_with_lag
      WHERE prev_date IS NOT NULL
      GROUP BY merchant
      HAVING COUNT(*) >= 2
    ),
    classified AS (
      SELECT *,
        CASE
          WHEN median_interval BETWEEN 6   AND 8   THEN 'weekly'
          WHEN median_interval BETWEEN 13  AND 16  THEN 'fortnightly'
          WHEN median_interval BETWEEN 27  AND 35  THEN 'monthly'
          WHEN median_interval BETWEEN 85  AND 95  THEN 'quarterly'
          WHEN median_interval BETWEEN 350 AND 380 THEN 'annual'
          ELSE NULL
        END AS frequency
      FROM merchant_stats
      WHERE stddev_interval < median_interval * 0.4
        AND (
          median_interval BETWEEN 6   AND 8   OR
          median_interval BETWEEN 13  AND 16  OR
          median_interval BETWEEN 27  AND 35  OR
          median_interval BETWEEN 85  AND 95  OR
          median_interval BETWEEN 350 AND 380
        )
    )
    SELECT merchant, category, occurrences, avg_amount, first_seen, last_seen, total_paid,
           median_interval::numeric(8,1), frequency
    FROM classified
    WHERE frequency IS NOT NULL
    ORDER BY
      CASE frequency
        WHEN 'weekly'      THEN avg_amount * 4.33
        WHEN 'fortnightly' THEN avg_amount * 2.17
        WHEN 'monthly'     THEN avg_amount
        WHEN 'quarterly'   THEN avg_amount / 3
        WHEN 'annual'      THEN avg_amount / 12
      END DESC NULLS LAST`,
    [user.id]
  );

  const today = new Date();
  const subscriptions = rows.map((r) => {
    const lastSeen = new Date(r.last_seen);
    const daysSinceLast = Math.floor((today.getTime() - lastSeen.getTime()) / 86400000);
    const medianInterval = Number(r.median_interval);
    const is_active = daysSinceLast < medianInterval * 1.5;

    const avg = Number(r.avg_amount);
    const monthly_equiv =
      r.frequency === "weekly"      ? avg * 4.33 :
      r.frequency === "fortnightly" ? avg * 2.17 :
      r.frequency === "quarterly"   ? avg / 3    :
      r.frequency === "annual"      ? avg / 12   :
      avg;

    return {
      merchant: r.merchant,
      category: r.category,
      frequency: r.frequency,
      avg_amount: avg,
      monthly_equiv: Math.round(monthly_equiv * 100) / 100,
      first_seen: r.first_seen,
      last_seen: r.last_seen,
      occurrences: r.occurrences,
      total_paid: Number(r.total_paid),
      is_active,
    };
  });

  const total_monthly_equiv = subscriptions
    .filter((s) => s.is_active)
    .reduce((sum, s) => sum + s.monthly_equiv, 0);

  return NextResponse.json({ subscriptions, total_monthly_equiv: Math.round(total_monthly_equiv * 100) / 100 });
}
