← back to Omega Watches 2

src/app/api/export/route.ts

140 lines

import { NextRequest, NextResponse } from "next/server";
import { query } from "@/lib/db";
import { getSession } from "@/lib/auth";

export const dynamic = "force-dynamic";

export async function GET(request: NextRequest) {
  const session = await getSession(request);
  if (!session) {
    return NextResponse.json(
      { success: false, error: { code: "UNAUTHORIZED", message: "Login required" } },
      { status: 401 }
    );
  }

  const { searchParams } = new URL(request.url);
  const type = searchParams.get("type") || "market_events";
  const reference = searchParams.get("reference");
  const collection = searchParams.get("collection");
  const dateFrom = searchParams.get("date_from");
  const dateTo = searchParams.get("date_to");
  const format = searchParams.get("format") || "csv";
  const limit = Math.min(parseInt(searchParams.get("limit") || "10000"), 50000);

  let rows: any[];

  if (type === "market_events") {
    const conditions: string[] = [];
    const params: any[] = [];

    if (reference) {
      params.push(reference);
      conditions.push(`wr.reference_number = $${params.length}`);
    }
    if (collection) {
      params.push(collection);
      conditions.push(`wr.collection = $${params.length}`);
    }
    if (dateFrom) {
      params.push(dateFrom);
      conditions.push(`me.sale_date >= $${params.length}`);
    }
    if (dateTo) {
      params.push(dateTo);
      conditions.push(`me.sale_date <= $${params.length}`);
    }

    const where = conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "";
    params.push(limit);

    rows = await query(
      `SELECT wr.reference_number, wr.collection, wr.model_name,
              me.event_type, me.source_name, me.sale_date,
              me.price_amount, me.currency, me.price_usd,
              me.condition_normalized, me.seller_type,
              me.fee_semantics, me.source_url
       FROM market_event me
       JOIN watch_reference wr ON me.reference_id = wr.id
       ${where}
       ORDER BY me.sale_date DESC
       LIMIT $${params.length}`,
      params
    );
  } else if (type === "msrp") {
    const conditions: string[] = [];
    const params: any[] = [];

    if (reference) {
      params.push(reference);
      conditions.push(`wr.reference_number = $${params.length}`);
    }
    if (collection) {
      params.push(collection);
      conditions.push(`wr.collection = $${params.length}`);
    }
    if (dateFrom) {
      params.push(dateFrom);
      conditions.push(`ms.captured_date >= $${params.length}`);
    }
    if (dateTo) {
      params.push(dateTo);
      conditions.push(`ms.captured_date <= $${params.length}`);
    }

    const where = conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "";
    params.push(limit);

    rows = await query(
      `SELECT wr.reference_number, wr.collection, wr.model_name,
              ms.region_code, ms.currency, ms.msrp_amount,
              ms.captured_date, ms.source_url
       FROM msrp_snapshot ms
       JOIN watch_reference wr ON ms.reference_id = wr.id
       ${where}
       ORDER BY ms.captured_date DESC
       LIMIT $${params.length}`,
      params
    );
  } else {
    return NextResponse.json(
      { success: false, error: { code: "INVALID_TYPE", message: "type must be market_events or msrp" } },
      { status: 400 }
    );
  }

  if (format === "json") {
    return NextResponse.json({ success: true, data: rows, meta: { count: rows.length } });
  }

  // CSV format
  if (rows.length === 0) {
    return new NextResponse("No data found", { status: 200, headers: { "Content-Type": "text/plain" } });
  }

  const headers = Object.keys(rows[0]);
  const csvLines = [
    headers.join(","),
    ...rows.map((row) =>
      headers.map((h) => {
        const val = row[h];
        if (val === null || val === undefined) return "";
        const str = String(val);
        return str.includes(",") || str.includes('"') || str.includes("\n")
          ? `"${str.replace(/"/g, '""')}"`
          : str;
      }).join(",")
    ),
  ];

  const filename = `omega_${type}_${new Date().toISOString().split("T")[0]}.csv`;

  return new NextResponse(csvLines.join("\n"), {
    status: 200,
    headers: {
      "Content-Type": "text/csv",
      "Content-Disposition": `attachment; filename="${filename}"`,
    },
  });
}