spb/tools-spboucher-ai

1import { requireUser, jsonError } from "@/lib/auth/guard";2import { sql } from "@/db";3export async function GET() {4  try { await requireUser(); const rows = await sql`SELECT DISTINCT ON (lc.tool_id, lc.field) t.slug, t.name, lc.field, lc.url, lc.ok, lc.status_code AS "statusCode", lc.final_url AS "finalUrl", lc.redirected, lc.error, lc.checked_at AS "checkedAt" FROM link_checks lc JOIN tools t ON t.id=lc.tool_id ORDER BY lc.tool_id, lc.field, lc.checked_at DESC`;5    const broken = rows.filter((r) => !r.ok); const redirected = rows.filter((r) => r.ok && r.redirected && new URL(r.url).hostname.replace(/^www\./, "") !== new URL(r.finalUrl).hostname.replace(/^www\./, ""));6    const [summary] = await sql`SELECT count(*) FILTER (WHERE link_state='ok')::int AS ok, count(*) FILTER (WHERE link_state='redirected')::int AS redirected, count(*) FILTER (WHERE link_state='broken')::int AS broken, count(*) FILTER (WHERE link_state='unchecked')::int AS unchecked, min(checked_at) AS oldest, max(checked_at) AS newest FROM tools WHERE visibility='public' AND editorial_status='published'`;7    return Response.json({ summary, broken, redirected, total: rows.length }); } catch (e) { return jsonError(e); }8}9