import { NextResponse } from "next/server";
import { query } from "@/lib/db/postgres";
import { isValidDate, isValidMonth, monthRange, parseTransactionCreate, todayIso } from "@/lib/finance/api-validation";
import { TRANSACTION_COLUMNS, badRequest, financeAuth, readJson, serverError, unauthorized } from "@/lib/finance/api-server";

// GET /api/v1/finances/transactions?month=YYYY-MM | from=YYYY-MM-DD&to=YYYY-MM-DD (to exclusive) &type=&category=&business=true|false&limit=&offset=
export async function GET(req: Request) {
  const userId = await financeAuth(req, "read");
  if (!userId) return unauthorized();

  const url = new URL(req.url);
  const where = ["user_id = $1"];
  const values: (string | number | boolean)[] = [userId];
  const add = (clause: string, value: string | number | boolean) => {
    values.push(value);
    where.push(clause.replace("?", `$${values.length}`));
  };

  const month = url.searchParams.get("month");
  const from = url.searchParams.get("from");
  const to = url.searchParams.get("to");
  if (month) {
    if (!isValidMonth(month)) return badRequest("month must be YYYY-MM");
    const range = monthRange(month);
    add("transaction_date >= ?", range.from);
    add("transaction_date < ?", range.to);
  }
  if (from) {
    if (!isValidDate(from)) return badRequest("from must be YYYY-MM-DD");
    add("transaction_date >= ?", from);
  }
  if (to) {
    if (!isValidDate(to)) return badRequest("to must be YYYY-MM-DD");
    add("transaction_date < ?", to);
  }
  const type = url.searchParams.get("type");
  if (type) {
    if (type !== "income" && type !== "expense") return badRequest("type must be 'income' or 'expense'");
    add("type = ?", type);
  }
  const category = url.searchParams.get("category");
  if (category) add("category = ?", category.slice(0, 80));
  const business = url.searchParams.get("business");
  if (business) {
    if (business !== "true" && business !== "false") return badRequest("business must be true or false");
    add("business = ?", business === "true");
  }
  const externalRef = url.searchParams.get("external_ref");
  if (externalRef) add("external_ref = ?", externalRef.slice(0, 200));

  const limit = Math.min(Math.max(Number(url.searchParams.get("limit") || 100) || 100, 1), 500);
  const offset = Math.max(Number(url.searchParams.get("offset") || 0) || 0, 0);

  try {
    const { rows } = await query(
      `SELECT ${TRANSACTION_COLUMNS} FROM finance_transactions WHERE ${where.join(" AND ")}
       ORDER BY transaction_date DESC, created_at DESC LIMIT ${limit} OFFSET ${offset}`,
      values
    );
    return NextResponse.json({ transactions: rows, limit, offset });
  } catch (error) {
    return serverError("Finance transactions GET error", error);
  }
}

// POST /api/v1/finances/transactions – neue Buchung. Mit external_ref idempotent (gleiche Referenz → vorhandene Buchung, 200).
export async function POST(req: Request) {
  const userId = await financeAuth(req, "write");
  if (!userId) return unauthorized();

  const parsed = parseTransactionCreate(await readJson(req), todayIso());
  if (!parsed.ok) return badRequest(parsed.error);
  const t = parsed.value;

  try {
    const { rows } = await query(
      `INSERT INTO finance_transactions
         (user_id, type, title, description, category, amount_cents, currency, transaction_date, status, source, external_ref, business)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, 'api', $10, $11)
       ON CONFLICT (user_id, external_ref) WHERE external_ref IS NOT NULL DO NOTHING
       RETURNING ${TRANSACTION_COLUMNS}`,
      [userId, t.type, t.title, t.description, t.category, t.amount_cents, t.currency, t.transaction_date, t.status, t.external_ref, t.business]
    );
    if (rows[0]) return NextResponse.json({ transaction: rows[0], created: true }, { status: 201 });

    const existing = await query(
      `SELECT ${TRANSACTION_COLUMNS} FROM finance_transactions WHERE user_id = $1 AND external_ref = $2`,
      [userId, t.external_ref]
    );
    return NextResponse.json({ transaction: existing.rows[0] ?? null, created: false, duplicate: true }, { status: 200 });
  } catch (error) {
    return serverError("Finance transactions POST error", error);
  }
}