import { db } from "@/app/db/db";
import { NextRequest, NextResponse } from "next/server";
import { surveyRequests } from "@/app/db/schema/survey_requests";
import { portfolioUsers, portfolioCapabilities } from "@/app/db/schema/portfolio";
import { user } from "@/app/db/schema/users";
import { and, eq, desc } from "drizzle-orm";
import { z } from "zod";
import { getServerSession } from "next-auth";
import { AuthOptions } from "@/app/api/auth/[...nextauth]/authOptions";
import { syncSurveyRequestToHubSpot } from "@/app/lib/hubspot/dealSync";

async function getRequestingUser(email: string) {
  const rows = await db
    .select({ id: user.id, email: user.email })
    .from(user)
    .where(eq(user.email, email))
    .limit(1);
  return rows[0] ?? null;
}

async function hasPortfolioRole(portfolioId: bigint, userId: bigint) {
  const rows = await db
    .select({ role: portfolioUsers.role })
    .from(portfolioUsers)
    .where(
      and(
        eq(portfolioUsers.portfolioId, portfolioId),
        eq(portfolioUsers.userId, userId),
      ),
    )
    .limit(1);
  return !!rows[0]?.role;
}

async function hasApproverCapability(portfolioId: bigint, userId: bigint) {
  const rows = await db
    .select({ capability: portfolioCapabilities.capability })
    .from(portfolioCapabilities)
    .where(
      and(
        eq(portfolioCapabilities.portfolioId, portfolioId),
        eq(portfolioCapabilities.userId, userId),
      ),
    );
  return rows.map((r) => r.capability).includes("approver");
}

async function getPendingRequest(hubspotDealId: string, portfolioId: bigint) {
  const rows = await db
    .select({ id: surveyRequests.id, status: surveyRequests.status })
    .from(surveyRequests)
    .where(
      and(
        eq(surveyRequests.hubspotDealId, hubspotDealId),
        eq(surveyRequests.portfolioId, portfolioId),
        eq(surveyRequests.status, "pending"),
      ),
    )
    .limit(1);
  return rows[0] ?? null;
}

// GET /api/portfolio/[portfolioId]/survey-requests?dealId=xxx
export async function GET(
  req: NextRequest,
  props: { params: Promise<{ portfolioId: string }> },
) {
  const { portfolioId } = await props.params;
  const dealId = req.nextUrl.searchParams.get("dealId");

  if (!dealId) {
    return NextResponse.json({ error: "dealId required" }, { status: 400 });
  }

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

  try {
    const rows = await db
      .select({
        id: surveyRequests.id,
        hubspotDealId: surveyRequests.hubspotDealId,
        surveyType: surveyRequests.surveyType,
        status: surveyRequests.status,
        requestedAt: surveyRequests.requestedAt,
        fulfilledAt: surveyRequests.fulfilledAt,
        requestedByEmail: user.email,
      })
      .from(surveyRequests)
      .innerJoin(user, eq(user.id, surveyRequests.requestedBy))
      .where(
        and(
          eq(surveyRequests.hubspotDealId, dealId),
          eq(surveyRequests.portfolioId, BigInt(portfolioId)),
        ),
      )
      .orderBy(desc(surveyRequests.requestedAt))
      .limit(20);

    const requests = rows.map((r) => ({
      id: String(r.id),
      hubspotDealId: r.hubspotDealId,
      surveyType: r.surveyType ?? null,
      status: r.status,
      requestedByEmail: r.requestedByEmail,
      requestedAt: r.requestedAt?.toISOString() ?? null,
      fulfilledAt: r.fulfilledAt?.toISOString() ?? null,
    }));

    return NextResponse.json({ requests });
  } catch (err) {
    console.error("[survey-requests GET]", err);
    return NextResponse.json({ error: "Internal server error" }, { status: 500 });
  }
}

const postSchema = z.object({
  hubspotDealId: z.string().min(1),
  surveyType: z.string().min(1),
});

// POST /api/portfolio/[portfolioId]/survey-requests
// Submit a new survey request — requires approver capability.
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 });
  }

  const requestingUser = await getRequestingUser(session.user.email);
  if (!requestingUser) {
    return NextResponse.json({ error: "User not found" }, { status: 401 });
  }

  const pid = BigInt(portfolioId);

  const isMember = await hasPortfolioRole(pid, requestingUser.id);
  if (!isMember) {
    return NextResponse.json({ error: "No portfolio access" }, { status: 403 });
  }

  const isApprover = await hasApproverCapability(pid, requestingUser.id);
  if (!isApprover) {
    return NextResponse.json(
      { error: "Approver capability required to submit a survey request" },
      { status: 403 },
    );
  }

  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 { hubspotDealId, surveyType } = parsed.data;

  const existing = await getPendingRequest(hubspotDealId, pid);
  if (existing) {
    return NextResponse.json(
      { error: "A pending survey request already exists for this deal" },
      { status: 409 },
    );
  }

  try {
    const [inserted] = await db
      .insert(surveyRequests)
      .values({
        hubspotDealId,
        portfolioId: pid,
        notes: "",
        surveyType,
        status: "pending",
        requestedBy: requestingUser.id,
      })
      .returning({ id: surveyRequests.id, requestedAt: surveyRequests.requestedAt });

    const hubspotResult = await syncSurveyRequestToHubSpot({
      hubspotDealId,
      surveyType,
      requestedAt: inserted.requestedAt,
    });

    return NextResponse.json({
      ok: true,
      id: String(inserted.id),
      hubspotSync: hubspotResult.ok ? "ok" : "failed",
      hubspotError: hubspotResult.error,
    });
  } catch (err) {
    console.error("[survey-requests POST]", err);
    return NextResponse.json({ error: "Internal server error" }, { status: 500 });
  }
}
