import { db } from "@/app/db/db";
import { NextRequest, NextResponse } from "next/server";
import { propertyRemovalRequests } from "@/app/db/schema/removal_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 { syncRemovalRequestToHubSpot, getDealBatch } from "@/app/lib/hubspot/dealSync";

const WRITE_ROLES = ["creator", "admin", "write"] as const;

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 getUserRole(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 ?? null;
}

async function hasApproverCapability(portfolioId: bigint, userId: bigint) {
  const rows = await db
    .select({ id: portfolioCapabilities.id })
    .from(portfolioCapabilities)
    .where(
      and(
        eq(portfolioCapabilities.portfolioId, portfolioId),
        eq(portfolioCapabilities.userId, userId),
        eq(portfolioCapabilities.capability, "approver"),
      ),
    )
    .limit(1);
  return rows.length > 0;
}

// GET /api/portfolio/[portfolioId]/removal-requests?dealId=xxx
// Returns the most recent removal request for this deal (all statuses)
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 });
  }

  try {
    const requester = user;
    const reviewer = { ...user } as typeof user;

    // Fetch all requests for this deal, most recent first, joining requester and reviewer emails
    const rows = await db
      .select({
        id: propertyRemovalRequests.id,
        hubspotDealId: propertyRemovalRequests.hubspotDealId,
        type: propertyRemovalRequests.type,
        reason: propertyRemovalRequests.reason,
        status: propertyRemovalRequests.status,
        requestedAt: propertyRemovalRequests.requestedAt,
        reviewedAt: propertyRemovalRequests.reviewedAt,
        requestedByEmail: user.email,
      })
      .from(propertyRemovalRequests)
      .innerJoin(user, eq(user.id, propertyRemovalRequests.requestedBy))
      .where(
        and(
          eq(propertyRemovalRequests.hubspotDealId, dealId),
          eq(propertyRemovalRequests.portfolioId, BigInt(portfolioId)),
        ),
      )
      .orderBy(desc(propertyRemovalRequests.requestedAt))
      .limit(10);

    // For rows with a reviewer, fetch their email separately
    const requests = await Promise.all(
      rows.map(async (row) => {
        // Find the full row to get reviewedBy
        const fullRow = await db
          .select({ reviewedBy: propertyRemovalRequests.reviewedBy })
          .from(propertyRemovalRequests)
          .where(eq(propertyRemovalRequests.id, row.id))
          .limit(1);

        let reviewedByEmail: string | null = null;
        if (fullRow[0]?.reviewedBy) {
          const reviewerRow = await db
            .select({ email: user.email })
            .from(user)
            .where(eq(user.id, fullRow[0].reviewedBy))
            .limit(1);
          reviewedByEmail = reviewerRow[0]?.email ?? null;
        }

        return {
          id: String(row.id),
          hubspotDealId: row.hubspotDealId,
          type: row.type,
          reason: row.reason,
          status: row.status,
          requestedByEmail: row.requestedByEmail,
          requestedAt: row.requestedAt?.toISOString() ?? null,
          reviewedByEmail,
          reviewedAt: row.reviewedAt?.toISOString() ?? null,
        };
      }),
    );

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

const postSchema = z.object({
  hubspotDealId: z.string().min(1),
  reason: z.string().min(1, "Reason is required"),
  type: z.enum(["removal", "re_addition"]).default("removal"),
});

// POST /api/portfolio/[portfolioId]/removal-requests
// Submit a new removal request — requires write+ role
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 role = await getUserRole(BigInt(portfolioId), requestingUser.id);
  if (!role || !WRITE_ROLES.includes(role as (typeof WRITE_ROLES)[number])) {
    return NextResponse.json(
      { error: "Write access required to submit a removal 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, reason, type } = parsed.data;

  try {
    // Fetch the most recent request for this deal to determine current state
    const [latest] = await db
      .select({
        status: propertyRemovalRequests.status,
        type: propertyRemovalRequests.type,
      })
      .from(propertyRemovalRequests)
      .where(
        and(
          eq(propertyRemovalRequests.hubspotDealId, hubspotDealId),
          eq(propertyRemovalRequests.portfolioId, BigInt(portfolioId)),
        ),
      )
      .orderBy(desc(propertyRemovalRequests.requestedAt))
      .limit(1);

    if (latest?.status === "pending") {
      return NextResponse.json(
        { error: "A pending request already exists for this property" },
        { status: 409 },
      );
    }

    if (type === "removal" && latest?.type === "removal" && latest?.status === "approved") {
      return NextResponse.json(
        { error: "This property has already been removed" },
        { status: 409 },
      );
    }

    if (type === "re_addition") {
      const isRemoved =
        (latest?.type === "removal" && latest?.status === "approved") ||
        (latest?.type === "re_addition" && latest?.status === "declined");
      if (!isRemoved) {
        return NextResponse.json(
          { error: "This property is not currently removed" },
          { status: 409 },
        );
      }
    }

    const [inserted] = await db
      .insert(propertyRemovalRequests)
      .values({
        hubspotDealId,
        portfolioId: BigInt(portfolioId),
        reason,
        type,
        status: "pending",
        requestedBy: requestingUser.id,
      })
      .returning();

    void syncRemovalRequestToHubSpot({
      hubspotDealId,
      type,
      status: "pending",
      reason,
      requestedByEmail: requestingUser.email,
    });

    return NextResponse.json({ success: true, id: String(inserted.id) });
  } catch (err) {
    console.error("[removal-requests POST]", err);
    return NextResponse.json({ error: "Internal server error" }, { status: 500 });
  }
}

const patchSchema = z.object({
  requestId: z.number().int().positive(),
  action: z.enum(["approved", "declined"]),
});

// PATCH /api/portfolio/[portfolioId]/removal-requests
// Approve or decline a pending request — requires approver capability
export async function PATCH(
  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 isApprover = await hasApproverCapability(BigInt(portfolioId), requestingUser.id);
  if (!isApprover) {
    return NextResponse.json(
      { error: "Approver capability required to review a removal request" },
      { status: 403 },
    );
  }

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

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

  const { requestId, action } = parsed.data;

  try {
    const target = await db
      .select({
        id: propertyRemovalRequests.id,
        type: propertyRemovalRequests.type,
        status: propertyRemovalRequests.status,
        hubspotDealId: propertyRemovalRequests.hubspotDealId,
        reason: propertyRemovalRequests.reason,
        requestedByEmail: user.email,
      })
      .from(propertyRemovalRequests)
      .innerJoin(user, eq(user.id, propertyRemovalRequests.requestedBy))
      .where(eq(propertyRemovalRequests.id, BigInt(requestId)))
      .limit(1);

    if (!target.length) {
      return NextResponse.json({ error: "Request not found" }, { status: 404 });
    }

    if (target[0].status !== "pending") {
      return NextResponse.json(
        { error: "Only pending requests can be reviewed" },
        { status: 409 },
      );
    }

    const requestType = (target[0].type ?? "removal") as "removal" | "re_addition";
    const dealId = target[0].hubspotDealId;

    // When approving a removal: fetch the current batch value and store it
    // When approving a re-addition: find the stored batch from the original removal
    let originalBatch: string | null = null;
    let batchValue: string | null | undefined = undefined;

    if (action === "approved") {
      if (requestType === "removal") {
        originalBatch = await getDealBatch(dealId);
      } else if (requestType === "re_addition") {
        const [originalRemoval] = await db
          .select({ originalBatch: propertyRemovalRequests.originalBatch })
          .from(propertyRemovalRequests)
          .where(
            and(
              eq(propertyRemovalRequests.hubspotDealId, dealId),
              eq(propertyRemovalRequests.type, "removal"),
              eq(propertyRemovalRequests.status, "approved"),
            ),
          )
          .orderBy(desc(propertyRemovalRequests.reviewedAt))
          .limit(1);
        batchValue = originalRemoval?.originalBatch ?? null;
      }
    }

    await db
      .update(propertyRemovalRequests)
      .set({
        status: action,
        reviewedBy: requestingUser.id,
        reviewedAt: new Date(),
        ...(requestType === "removal" && action === "approved" ? { originalBatch } : {}),
      })
      .where(eq(propertyRemovalRequests.id, BigInt(requestId)));

    void syncRemovalRequestToHubSpot({
      hubspotDealId: dealId,
      type: requestType,
      status: action,
      reason: target[0].reason,
      requestedByEmail: target[0].requestedByEmail,
      reviewedByEmail: requestingUser.email,
      batchValue,
    });

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