import { db } from "@/app/db/db";
import { NextRequest, NextResponse } from "next/server";
import {
  dealMeasureApprovals,
  dealMeasureApprovalEvents,
} from "@/app/db/schema/approvals";
import { portfolioCapabilities } from "@/app/db/schema/portfolio";
import { user } from "@/app/db/schema/users";
import { and, eq, inArray, sql } from "drizzle-orm";
import { z } from "zod";
import { getServerSession } from "next-auth";
import { AuthOptions } from "@/app/api/auth/[...nextauth]/authOptions";
import { syncMeasureApprovalsToHubSpot } from "@/app/lib/hubspot/dealSync";

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

async function hasApproverCapability(
  portfolioId: bigint,
  userId: bigint,
): Promise<boolean> {
  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 — return currently approved measures per deal, and optionally the audit event log
// Query params:
//   dealIds   comma-separated HubSpot deal IDs (required)
//   include   "events" to also return the audit log
export async function GET(
  req: NextRequest,
  props: { params: Promise<{ portfolioId: string }> },
) {
  const url = new URL(req.url);
  const dealIdsParam = url.searchParams.get("dealIds");
  const includeEvents = url.searchParams.get("include") === "events";

  if (!dealIdsParam) {
    return NextResponse.json(includeEvents ? { approved: {}, events: [] } : {});
  }

  const dealIds = dealIdsParam.split(",").filter(Boolean);
  if (dealIds.length === 0) {
    return NextResponse.json(includeEvents ? { approved: {}, events: [] } : {});
  }

  try {
    // Current approved measures
    const approvalRows = await db
      .select({
        hubspotDealId: dealMeasureApprovals.hubspotDealId,
        measureName: dealMeasureApprovals.measureName,
        approvedByEmail: user.email,
        approvedByName: user.firstName,
        approvedAt: dealMeasureApprovals.approvedAt,
      })
      .from(dealMeasureApprovals)
      .leftJoin(user, eq(user.id, dealMeasureApprovals.approvedBy))
      .where(
        and(
          inArray(dealMeasureApprovals.hubspotDealId, dealIds),
          eq(dealMeasureApprovals.isApproved, true),
        ),
      );

    const approved: Record<string, string[]> = {};
    for (const row of approvalRows) {
      (approved[row.hubspotDealId] ??= []).push(row.measureName);
    }

    if (!includeEvents) {
      return NextResponse.json(approved);
    }

    // Audit event log
    const eventRows = await db
      .select({
        id: dealMeasureApprovalEvents.id,
        hubspotDealId: dealMeasureApprovalEvents.hubspotDealId,
        measureName: dealMeasureApprovalEvents.measureName,
        action: dealMeasureApprovalEvents.action,
        actedByEmail: user.email,
        actedByName: user.firstName,
        actedAt: dealMeasureApprovalEvents.actedAt,
      })
      .from(dealMeasureApprovalEvents)
      .leftJoin(user, eq(user.id, dealMeasureApprovalEvents.actedBy))
      .where(inArray(dealMeasureApprovalEvents.hubspotDealId, dealIds))
      .orderBy(dealMeasureApprovalEvents.actedAt);

    const events = eventRows.map((e) => ({
      id: e.id.toString(),
      hubspotDealId: e.hubspotDealId,
      measureName: e.measureName,
      action: e.action,
      actedByEmail: e.actedByEmail ?? "",
      actedByName: e.actedByName ?? null,
      actedAt: e.actedAt.toISOString(),
    }));

    return NextResponse.json({ approved, events });
  } catch (err) {
    console.error("GET /approvals error:", err);
    return NextResponse.json(
      { error: "Failed to fetch approvals" },
      { status: 500 },
    );
  }
}

// POST — apply explicit approve/unapprove changes, updating current state + audit log
// Body: { changes: [{ hubspotDealId, measureName, approved: boolean }] }
export async function POST(
  req: NextRequest,
  props: { params: Promise<{ portfolioId: string }> },
) {
  const session = await getServerSession(AuthOptions);
  if (!session?.user?.email) {
    return NextResponse.json({ error: "Unauthorised" }, { status: 401 });
  }

  const { portfolioId } = await props.params;
  const pId = BigInt(portfolioId);

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

  const isApprover = await hasApproverCapability(pId, userId);
  if (!isApprover) {
    return NextResponse.json({ error: "Forbidden" }, { status: 403 });
  }

  const bodySchema = z.object({
    changes: z.array(
      z.object({
        hubspotDealId: z.string(),
        measureName: z.string(),
        approved: z.boolean(),
      }),
    ),
  });

  let body: z.infer<typeof bodySchema>;
  try {
    body = bodySchema.parse(await req.json());
  } catch {
    return NextResponse.json({ error: "Invalid body" }, { status: 400 });
  }

  if (body.changes.length === 0) {
    return NextResponse.json({ success: true });
  }

  try {
    const now = new Date();

    for (const change of body.changes) {
      // 1. Upsert current state
      await db
        .insert(dealMeasureApprovals)
        .values({
          hubspotDealId: change.hubspotDealId,
          measureName: change.measureName,
          isApproved: change.approved,
          approvedBy: userId,
          approvedAt: now,
        })
        .onConflictDoUpdate({
          target: [
            dealMeasureApprovals.hubspotDealId,
            dealMeasureApprovals.measureName,
          ],
          set: {
            isApproved: change.approved,
            approvedBy: userId,
            approvedAt: now,
          },
        });

      // 2. Append to audit log
      await db.insert(dealMeasureApprovalEvents).values({
        hubspotDealId: change.hubspotDealId,
        measureName: change.measureName,
        action: change.approved ? "approved" : "unapproved",
        actedBy: userId,
        actedAt: now,
      });
    }

    const affectedDealIds = [...new Set(body.changes.map((c) => c.hubspotDealId))];
    for (const dealId of affectedDealIds) {
      const approvalRows = await db
        .select({
          measureName: dealMeasureApprovals.measureName,
          approvedByEmail: user.email,
        })
        .from(dealMeasureApprovals)
        .leftJoin(user, eq(user.id, dealMeasureApprovals.approvedBy))
        .where(
          and(
            eq(dealMeasureApprovals.hubspotDealId, dealId),
            eq(dealMeasureApprovals.isApproved, true),
          ),
        );

      void syncMeasureApprovalsToHubSpot({
        hubspotDealId: dealId,
        approvedMeasures: approvalRows.map((r) => ({
          measureName: r.measureName,
          approvedByEmail: r.approvedByEmail ?? "unknown",
        })),
        actedByEmail: session.user.email,
        actedAt: now,
      });
    }

    return NextResponse.json({ success: true });
  } catch (err) {
    console.error("POST /approvals error:", err);
    return NextResponse.json(
      { error: "Failed to save approvals" },
      { status: 500 },
    );
  }
}
