import { eq, desc, inArray, sql } from "drizzle-orm";
import { nanoid } from "nanoid";
import { NextResponse } from "next/server";

import { auth } from "@/lib/auth";
import { getActiveOrgIdFromSession } from "@/lib/auth-guards";
import { assertQuota, QuotaExceededError } from "@/lib/billing/quotas";
import { db } from "@/lib/db";
import { sites, siteTests } from "@/lib/db/schema";
import { launchSitespeedTest } from "@/lib/sitespeed-launch";
import { resolveReachableUrl } from "@/lib/url";

// GET /api/sites — list sites of the user's active org with latest metrics
export async function GET(req: Request) {
  const session = await auth.api.getSession({ headers: req.headers });
  if (!session) return NextResponse.json({ error: "Unauthorized" }, { status: 401 });
  const orgId = await getActiveOrgIdFromSession(session);
  if (!orgId) return NextResponse.json({ error: "No active organization" }, { status: 400 });

  const orgSites = await db
    .select()
    .from(sites)
    .where(eq(sites.organizationId, orgId))
    .orderBy(desc(sites.createdAt));

  if (orgSites.length === 0) return NextResponse.json([]);

  const siteIds = orgSites.map((s) => s.id);

  // Query 2/3: latest siteTests row per site, using PostgreSQL DISTINCT ON.
  // Previously this was N queries (one per site); now it's a single round-trip.
  const latestRows = await db
    .selectDistinctOn([siteTests.siteId])
    .from(siteTests)
    .where(inArray(siteTests.siteId, siteIds))
    .orderBy(siteTests.siteId, desc(siteTests.createdAt));

  type LatestRow = (typeof latestRows)[number];
  const latestBySite = new Map<string, LatestRow>();
  for (const row of latestRows) {
    if (row.siteId) latestBySite.set(row.siteId, row);
  }

  // Query 3/3: last 7 sparkline points per site via ROW_NUMBER() window.
  // Aliased to camelCase so the row shape matches our app convention.
  // ASC order matches the previous .toReversed() behavior (oldest -> newest).
  const sparkRows = await db.execute<{
    siteId: string;
    performanceScore: number | null;
    createdAt: string;
  }>(sql`
    SELECT "siteId", "performanceScore", "createdAt" FROM (
      SELECT
        site_id        AS "siteId",
        performance_score AS "performanceScore",
        created_at     AS "createdAt",
        ROW_NUMBER() OVER (PARTITION BY site_id ORDER BY created_at DESC) AS rn
      FROM es_site_test
      WHERE site_id IN (${sql.join(
        siteIds.map((id) => sql`${id}`),
        sql`, `,
      )})
    ) ranked
    WHERE rn <= 7
    ORDER BY "siteId", "createdAt" ASC
  `);

  const sparklineBySite = new Map<
    string,
    { performanceScore: number | null; createdAt: string }[]
  >();
  for (const r of sparkRows) {
    const arr = sparklineBySite.get(r.siteId) ?? [];
    arr.push({ performanceScore: r.performanceScore, createdAt: r.createdAt });
    sparklineBySite.set(r.siteId, arr);
  }

  const result = orgSites.map((site) => ({
    ...site,
    latestTest: latestBySite.get(site.id) ?? null,
    sparkline: sparklineBySite.get(site.id) ?? [],
  }));

  return NextResponse.json(result);
}

// POST /api/sites — add a new site to the user's active org
export async function POST(req: Request) {
  const session = await auth.api.getSession({ headers: req.headers });
  if (!session) return NextResponse.json({ error: "Unauthorized" }, { status: 401 });
  const orgId = await getActiveOrgIdFromSession(session);
  if (!orgId) return NextResponse.json({ error: "No active organization" }, { status: 400 });

  try {
    await assertQuota(orgId, "sites");
  } catch (err) {
    if (err instanceof QuotaExceededError) {
      return NextResponse.json(
        {
          error: "QUOTA_EXCEEDED",
          kind: err.result.kind,
          used: err.result.used,
          limit: err.result.limit,
          plan: err.result.plan,
        },
        { status: 403 },
      );
    }
    throw err;
  }

  const { url } = await req.json();
  if (!url || typeof url !== "string") {
    return NextResponse.json({ error: "URL requise" }, { status: 400 });
  }

  let fullUrl: string;
  let hostname: string;
  try {
    const resolved = await resolveReachableUrl(url);
    fullUrl = resolved.url;
    hostname = resolved.hostname;
  } catch (err) {
    return NextResponse.json(
      { error: err instanceof Error ? err.message : "URL invalide" },
      { status: 400 },
    );
  }

  // Check if site already exists for this org
  const existing = (await db.select().from(sites).where(eq(sites.organizationId, orgId))).find(
    (s) => s.hostname === hostname,
  );

  if (existing) {
    return NextResponse.json(existing);
  }

  const id = nanoid();
  const site = {
    id,
    organizationId: orgId,
    createdByUserId: session.user.id,
    hostname,
    url: fullUrl,
    label: null,
    createdAt: new Date().toISOString(),
  };
  await db.insert(sites).values(site);

  // Fire-and-await the first audit so the destination page's SSR picks it up.
  // Failure here doesn't roll back the site — user can re-launch manually.
  const launched = await launchSitespeedTest({
    url: fullUrl,
    hostname,
    siteId: id,
    organizationId: orgId,
    triggeredByUserId: session.user.id,
    device: "desktop",
  });
  const firstTest = launched.ok ? { id: launched.id, status: launched.status } : null;

  return NextResponse.json({ ...site, firstTest }, { status: 201 });
}