import { getServerSession } from "next-auth";
import { AuthOptions } from "@/app/api/auth/[...nextauth]/authOptions";
import { redirect } from "next/navigation";
import { eq, inArray, and, desc, sql } from "drizzle-orm";
import LiveTracker from "./LiveTracker";
import { computeLiveTrackerData } from "./transforms";
import { fetchDocsByDealId, computeDocStatusMap } from "./docStatus";
import { db } from "@/app/db/db";
import { hubspotDealData } from "@/app/db/schema/crm/hubspot_deal_table";
import { portfolioOrganisation } from "@/app/db/schema/portfolio_organisation";
import { coordinatorUser, designerUser, mapDbRowToHubspotDeal } from "./dealQuery";
import type { DealRow } from "./dealQuery";
import { organisation } from "@/app/db/schema/organisation";
import {
  portfolioCapabilities,
  portfolioUsers,
} from "@/app/db/schema/portfolio";
import { dealMeasureApprovals } from "@/app/db/schema/approvals";
import { userDefinedDealMeasures } from "@/app/db/schema/user_defined_deal_measures";
import { propertyRemovalRequests } from "@/app/db/schema/removal_requests";
import { user as userTable } from "@/app/db/schema/users";
import type {
  PortfolioCapabilityType,
  ApprovalsByDeal,
  InstructedMeasuresByDeal,
  RemovalStatusByDeal,
} from "./types";
import { computeRemovalStatusByDeal } from "./removalState";
import { Card, CardContent } from "@/app/shadcn_components/ui/card";
import { Building2 } from "lucide-react";

export default async function LiveReportingPage(props: {
  params: Promise<{ slug: string }>;
}) {
  const { slug: portfolioId } = await props.params;
  const user = await getServerSession(AuthOptions);

  if (!user?.user) {
    redirect("/");
  }

  // Look up all linked organisations for this portfolio
  const link = await db
    .select({ hubspotCompanyId: organisation.hubspotCompanyId })
    .from(portfolioOrganisation)
    .innerJoin(
      organisation,
      eq(portfolioOrganisation.organisationId, organisation.id),
    )
    .where(eq(portfolioOrganisation.portfolioId, BigInt(portfolioId)));

  const pageHeader = (
    <div className="mb-6">
      <header className="text-3xl font-semibold text-brandblue">
        Live Projects
      </header>
      <p className="text-sm text-gray-500">
        {`Check in on your projects' progress with real-time data updates.`}
      </p>
      <div className="h-px bg-gray-200 mt-2" />
    </div>
  );

  const companyIds = link
    .map((l) => l.hubspotCompanyId)
    .filter((id): id is string => !!id);

  if (companyIds.length === 0) {
    return (
      <div className="max-w-7xl mx-auto px-6 pb-10 space-y-4">
        {pageHeader}
        <Card className="border border-brandblue/10 shadow-sm">
          <CardContent className="flex flex-col items-center justify-center py-16 text-center gap-4">
            <div className="p-4 rounded-full bg-brandlightblue/40 border border-brandblue/10">
              <Building2 className="h-8 w-8 text-brandblue/50" />
            </div>
            <div>
              <p className="text-base font-semibold text-gray-700">
                No organisation linked
              </p>
              <p className="text-sm text-gray-400 mt-1 max-w-sm">
                A Domna administrator needs to connect this portfolio to an
                organisation in <strong>Portfolio Settings</strong> before live
                tracking data can be displayed.
              </p>
            </div>
          </CardContent>
        </Card>
      </div>
    );
  }

  const rawDeals = await db
    .select({
      deal: hubspotDealData,
      coordinator: sql<
        string | null
      >`CASE WHEN ${hubspotDealData.coordinator} IS NULL THEN NULL ELSE COALESCE(${coordinatorUser.firstName} || ' ' || ${coordinatorUser.lastName}, 'Domna Coordinator') END`,
      designer: sql<
        string | null
      >`CASE WHEN ${hubspotDealData.designer} IS NULL THEN NULL ELSE COALESCE(${designerUser.firstName} || ' ' || ${designerUser.lastName}, 'Domna Designer') END`,
    })
    .from(hubspotDealData)
    .leftJoin(
      coordinatorUser,
      eq(hubspotDealData.coordinator, coordinatorUser.hubspotOwnerId),
    )
    .leftJoin(
      designerUser,
      eq(hubspotDealData.designer, designerUser.hubspotOwnerId),
    )
    .where(inArray(hubspotDealData.companyId, companyIds));

  const deals = rawDeals.map(mapDbRowToHubspotDeal);
  const trackerData = computeLiveTrackerData(deals);

  const userEmail = user?.user?.email;

  // Single user lookup shared by capability and role queries
  let userId: bigint | null = null;
  if (userEmail) {
    const userRow = await db
      .select({ id: userTable.id })
      .from(userTable)
      .where(eq(userTable.email, userEmail))
      .limit(1);
    userId = userRow[0]?.id ?? null;
  }

  // Fetch current user's portfolio capabilities (approver / contractor — can have both)
  let userCapability: PortfolioCapabilityType = [];
  if (userId) {
    const capRows = await db
      .select({ capability: portfolioCapabilities.capability })
      .from(portfolioCapabilities)
      .where(
        and(
          eq(portfolioCapabilities.portfolioId, BigInt(portfolioId)),
          eq(portfolioCapabilities.userId, userId),
        ),
      );
    userCapability = capRows
      .map((r) => r.capability)
      .filter(
        (c): c is "approver" | "contractor" =>
          c === "approver" || c === "contractor",
      );
  }

  // Fetch current user's portfolio role (creator / admin / write / read)
  let userRole = "read";
  if (userId) {
    const roleRow = await db
      .select({ role: portfolioUsers.role })
      .from(portfolioUsers)
      .where(
        and(
          eq(portfolioUsers.portfolioId, BigInt(portfolioId)),
          eq(portfolioUsers.userId, userId),
        ),
      )
      .limit(1);
    userRole = roleRow[0]?.role ?? "read";
  }

  // Fetch currently approved measures for all deals in scope
  const approvalsByDeal: ApprovalsByDeal = {};
  const dealIds = deals.map((d) => d.dealId).filter(Boolean);
  if (dealIds.length > 0) {
    const approvalRows = await db
      .select({
        hubspotDealId: dealMeasureApprovals.hubspotDealId,
        measureName: dealMeasureApprovals.measureName,
      })
      .from(dealMeasureApprovals)
      .where(
        and(
          inArray(dealMeasureApprovals.hubspotDealId, dealIds),
          eq(dealMeasureApprovals.isApproved, true),
        ),
      );

    for (const row of approvalRows) {
      (approvalsByDeal[row.hubspotDealId] ??= []).push(row.measureName);
    }
  }

  // Fetch instructed measures for all deals in scope
  const instructedMeasuresByDeal: InstructedMeasuresByDeal = {};
  if (dealIds.length > 0) {
    const instructedRows = await db
      .select({
        hubspotDealId: userDefinedDealMeasures.hubspotDealId,
        measureName: userDefinedDealMeasures.measureName,
      })
      .from(userDefinedDealMeasures)
      .where(
        and(
          inArray(userDefinedDealMeasures.hubspotDealId, dealIds),
          eq(userDefinedDealMeasures.source, "instructed"),
        ),
      );

    for (const row of instructedRows) {
      (instructedMeasuresByDeal[row.hubspotDealId] ??= []).push(row.measureName);
    }
  }

  const removalRows = await db
    .select({
      hubspotDealId: propertyRemovalRequests.hubspotDealId,
      type: propertyRemovalRequests.type,
      status: propertyRemovalRequests.status,
    })
    .from(propertyRemovalRequests)
    .where(eq(propertyRemovalRequests.portfolioId, BigInt(portfolioId)))
    .orderBy(desc(propertyRemovalRequests.requestedAt));

  const removalStatusByDeal: RemovalStatusByDeal = computeRemovalStatusByDeal(removalRows);

  const docsByDealId = await fetchDocsByDealId(deals, dealIds);
  const docStatusMap = computeDocStatusMap(deals, docsByDealId, approvalsByDeal);

  return (
    <div className="max-w-7xl mx-auto px-6 pb-10 space-y-4">
      {pageHeader}
      <LiveTracker
        {...trackerData}
        docStatusMap={docStatusMap}
        userCapability={userCapability}
        approvalsByDeal={approvalsByDeal}
        instructedMeasuresByDeal={instructedMeasuresByDeal}
        removalStatusByDeal={removalStatusByDeal}
        portfolioId={portfolioId}
        userRole={userRole}
        userEmail={userEmail ?? ""}
      />
    </div>
  );
}
