/**
 * GET /api/projects/[projectId]/databases/[dbUuid]/preview?schema=…&table=…
 *
 * Returns the first ~50 rows of the requested table. Read-only — backed
 * by lib/db-introspect.previewTable which only emits SELECT statements
 * with a hard LIMIT and rejects identifiers containing anything outside
 * /[A-Za-z0-9_]+/.
 *
 * Returns: { columns: string[], rows: Record<string,string>[], truncated: boolean }
 *
 * Same auth + tenancy chain as the tables route.
 */

import { NextResponse } from "next/server";
import { authSession } from "@/lib/auth/session-server";
import { query } from "@/lib/db-postgres";
import { listDatabasesInProject } from "@/lib/coolify";
import { previewTable, IntrospectionUnsupportedError } from "@/lib/db-introspect";

function dbEngineOf(type: string | undefined): string {
  const raw = (type ?? "").toLowerCase();
  if (raw.includes("postgres")) return "postgresql";
  if (raw.includes("redis")) return "redis";
  if (raw.includes("mongo")) return "mongodb";
  if (raw.includes("mysql") || raw.includes("mariadb")) return "mysql";
  if (raw.includes("clickhouse")) return "clickhouse";
  return raw || "unknown";
}

export async function GET(
  req: Request,
  { params }: { params: Promise<{ projectId: string; dbUuid: string }> }
) {
  try {
    const { projectId, dbUuid } = await params;
    const url = new URL(req.url);
    const schema = url.searchParams.get("schema") ?? "";
    const table = url.searchParams.get("table") ?? "";
    if (!schema || !table) {
      return NextResponse.json({ error: "Both 'schema' and 'table' are required" }, { status: 400 });
    }

    const session = await authSession();
    if (!session?.user?.email) {
      return NextResponse.json({ error: "Unauthorized" }, { status: 401 });
    }

    const rows = await query<{ data: Record<string, unknown> }>(
      `SELECT p.data FROM fs_projects p
       JOIN fs_users u ON u.id = p.user_id
       WHERE p.id = $1 AND u.data->>'email' = $2 LIMIT 1`,
      [projectId, session.user.email]
    );
    if (rows.length === 0) {
      return NextResponse.json({ error: "Project not found" }, { status: 404 });
    }
    const coolifyProjectUuid = rows[0].data?.coolifyProjectUuid as string | undefined;
    if (!coolifyProjectUuid) {
      return NextResponse.json({ error: "Project has no Coolify project linked" }, { status: 400 });
    }

    const dbs = await listDatabasesInProject(coolifyProjectUuid);
    const db = dbs.find(d => d.uuid === dbUuid);
    if (!db) {
      return NextResponse.json({ error: "Database not in this project" }, { status: 404 });
    }

    const engine = dbEngineOf(db.type);
    try {
      const result = await previewTable(dbUuid, engine, schema, table);
      return NextResponse.json(result);
    } catch (err) {
      if (err instanceof IntrospectionUnsupportedError) {
        return NextResponse.json(
          { error: err.message, unsupported: true },
          { status: 501 }
        );
      }
      throw err;
    }
  } catch (err) {
    console.error("[db preview API]", err);
    return NextResponse.json(
      { error: err instanceof Error ? err.message : "Failed to preview table" },
      { status: 500 }
    );
  }
}
