/**
 * GET /api/projects/[projectId]/databases/[dbUuid]/tables
 *
 * Lists user-tables for a Coolify-managed database that belongs to the
 * given Vibn project. Postgres only for now; other engines return 501.
 *
 * Auth + tenancy chain:
 *   - session.user.email must own the fs_projects row
 *   - the database uuid must live in the project's coolify project
 *
 * Hard caps: 50 tables, 8s timeout (enforced inside lib/db-introspect).
 */

import { NextResponse } from "next/server";
import { authSession } from "@/lib/auth/session-server";
import { query } from "@/lib/db-postgres";
import { listDatabasesInProject } from "@/lib/coolify";
import { listTables, 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 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 });
    }

    // Authorise the dbUuid against this project
    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 tables = await listTables(dbUuid, engine);
      return NextResponse.json({ engine, tables });
    } catch (err) {
      if (err instanceof IntrospectionUnsupportedError) {
        return NextResponse.json(
          { engine, tables: [], unsupported: true, message: err.message },
          { status: 200 }
        );
      }
      throw err;
    }
  } catch (err) {
    console.error("[db tables API]", err);
    return NextResponse.json(
      { error: err instanceof Error ? err.message : "Failed to list tables" },
      { status: 500 }
    );
  }
}
