import { NextRequest, NextResponse } from "next/server";
import { getServerSession } from "next-auth";
import { and, eq, desc } from "drizzle-orm";
import { z } from "zod";

import { AuthOptions } from "@/app/api/auth/[...nextauth]/authOptions";
import { db } from "@/app/db/db";
import { portfolioCapabilities, portfolioUsers } from "@/app/db/schema/portfolio";
import { user } from "@/app/db/schema/users";
import { pibiRequests } from "@/app/db/schema/pibi_requests";
import { createPibiRequests } from "@/app/lib/createPibiRequests";

const postSchema = z.object({
  dealId: z.string().min(1, "dealId is required"),
  measureNames: z.array(z.string().min(1)).min(1, "at least one measure required"),
  orderedAt: z.string().datetime().optional(),
});

async function resolveApprover(
  email: string,
  portfolioId: string,
): Promise<
  | { ok: true; userId: bigint }
  | { ok: false; status: 401 | 403 | 404; error: string }
> {
  const userRow = await db
    .select({ id: user.id })
    .from(user)
    .where(eq(user.email, email))
    .limit(1);

  if (!userRow[0]) return { ok: false, status: 404, error: "User not found" };

  const portfolioUserRow = await db
    .select({ role: portfolioUsers.role })
    .from(portfolioUsers)
    .where(
      and(
        eq(portfolioUsers.portfolioId, BigInt(portfolioId)),
        eq(portfolioUsers.userId, userRow[0].id),
      ),
    )
    .limit(1);

  if (!portfolioUserRow[0]?.role) {
    return { ok: false, status: 403, error: "No portfolio access" };
  }

  const capabilityRows = await db
    .select({ capability: portfolioCapabilities.capability })
    .from(portfolioCapabilities)
    .where(
      and(
        eq(portfolioCapabilities.portfolioId, BigInt(portfolioId)),
        eq(portfolioCapabilities.userId, userRow[0].id),
      ),
    );

  if (!capabilityRows.map((r) => r.capability).includes("approver")) {
    return { ok: false, status: 403, error: "Approver capability required" };
  }

  return { ok: true, userId: userRow[0].id };
}

/**
 * GET /api/portfolio/[portfolioId]/pibi-requests?dealId=...
 *
 * Returns all PIBI requests for a deal, ordered by orderedAt desc.
 * Response: { pibiRequests: PibiRequestRow[] }
 */
export async function GET(
  req: NextRequest,
  props: { params: Promise<{ portfolioId: string }> },
) {
  const { portfolioId } = await props.params;

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

  const dealId = req.nextUrl.searchParams.get("dealId");
  if (!dealId) {
    return NextResponse.json({ error: "dealId query param is required" }, { status: 400 });
  }

  const userRow = await db
    .select({ id: user.id })
    .from(user)
    .where(eq(user.email, session.user.email))
    .limit(1);

  if (!userRow[0]) {
    return NextResponse.json({ error: "User not found" }, { status: 404 });
  }

  const portfolioUserRow = await db
    .select({ role: portfolioUsers.role })
    .from(portfolioUsers)
    .where(
      and(
        eq(portfolioUsers.portfolioId, BigInt(portfolioId)),
        eq(portfolioUsers.userId, userRow[0].id),
      ),
    )
    .limit(1);

  if (!portfolioUserRow[0]?.role) {
    return NextResponse.json({ error: "No portfolio access" }, { status: 403 });
  }

  const rows = await db
    .select({
      id: pibiRequests.id,
      measureName: pibiRequests.measureName,
      orderedAt: pibiRequests.orderedAt,
      completedAt: pibiRequests.completedAt,
    })
    .from(pibiRequests)
    .where(eq(pibiRequests.hubspotDealId, dealId))
    .orderBy(desc(pibiRequests.orderedAt));

  return NextResponse.json({
    pibiRequests: rows.map((r) => ({
      id: String(r.id),
      measureName: r.measureName,
      orderedAt: r.orderedAt,
      completedAt: r.completedAt,
    })),
  });
}

/**
 * POST /api/portfolio/[portfolioId]/pibi-requests
 *
 * Approver-only. Creates one pibi_request row per measure in the batch.
 * Body: { dealId: string, measureNames: string[], orderedAt?: string (ISO) }
 * Response: { ok: true, insertedCount: number, hubspotSync: "ok" | "failed" }
 */
export async function POST(
  req: NextRequest,
  props: { params: Promise<{ portfolioId: string }> },
) {
  const { portfolioId } = await props.params;

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

  let body: unknown;
  try {
    body = await req.json();
  } catch {
    return NextResponse.json({ error: "Invalid JSON" }, { status: 400 });
  }

  const parsed = postSchema.safeParse(body);
  if (!parsed.success) {
    return NextResponse.json({ error: parsed.error.flatten() }, { status: 400 });
  }

  const auth = await resolveApprover(session.user.email, portfolioId);
  if (!auth.ok) {
    return NextResponse.json({ error: auth.error }, { status: auth.status });
  }

  const { dealId, measureNames, orderedAt } = parsed.data;

  const result = await createPibiRequests({
    dealId,
    portfolioId: BigInt(portfolioId),
    measureNames,
    orderedAt: orderedAt ? new Date(orderedAt) : undefined,
    userId: auth.userId,
  });

  if (!result.ok) {
    return NextResponse.json({ ok: false, error: result.error }, { status: 400 });
  }

  return NextResponse.json({
    ok: true,
    insertedCount: result.insertedRowIds.length,
    hubspotSync: result.hubspotSync,
    hubspotError: result.hubspotError,
  });
}
