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 { db } from "@/app/db/db";
import { hubspotDealData } from "@/app/db/schema/crm/hubspot_deal_table";
import { alias } from "drizzle-orm/pg-core";
import { hubspotUsers } from "@/app/db/schema/crm/hubspot_user_table";
import { uploadedFiles } from "@/app/db/schema/uploaded_files";
import { portfolioOrganisation } from "@/app/db/schema/portfolio_organisation";
import { organisation } from "@/app/db/schema/organisation";
import { portfolioCapabilities, portfolioUsers } from "@/app/db/schema/portfolio";
import { dealMeasureApprovals } from "@/app/db/schema/approvals";
import { propertyRemovalRequests } from "@/app/db/schema/removal_requests";
import { user as userTable } from "@/app/db/schema/users";
import type { HubspotDeal, DocStatusMap, DocStatus, MeasureDocProgress, PortfolioCapabilityType, ApprovalsByDeal, RemovalStatusByDeal, EffectiveRemovalState } from "./types";
import { EXPECTED_RETROFIT_ASSESSMENT_DOC_TYPES, SURVEY_ALL_DOC_TYPES } from "./types";
import { getRequiredDocs } from "@/app/lib/measureDocumentRequirements";
import type { InferSelectModel } from "drizzle-orm";
import { Card, CardContent } from "@/app/shadcn_components/ui/card";
import { Building2 } from "lucide-react";

const coordinatorUser = alias(hubspotUsers, "coordinator_user");
const designerUser = alias(hubspotUsers, "designer_user");

type DealRow = {
  deal: InferSelectModel<typeof hubspotDealData>;
  coordinator: string | null;
  designer: string | null;
};

function mapDbRowToHubspotDeal(row: DealRow): HubspotDeal {
  const d = row.deal;
  return {
    id: d.id,
    dealId: d.dealId,
    dealname: d.dealname,
    dealstage: d.dealstage,
    companyId: d.companyId,
    projectCode: d.projectCode,
    landlordPropertyId: d.landlordPropertyId,
    uprn: d.uprn,
    outcome: d.outcome,
    outcomeNotes: d.outcomeNotes,
    majorConditionIssueDescription: d.majorConditionIssueDescription,
    majorConditionIssuePhotos: d.majorConditionIssuePhotos,
    majorConditionIssuePhotosS3: d.majorConditionIssuePhotosS3,
    coordinationStatus: d.coordinationStatus,
    designStatus: d.designStatus,
    pashubLink: d.pashubLink,
    sharepointLink: d.sharepointLink,
    dampMouldFlag: d.dampmouldGrowth,
    dampMouldAndRepairComments: d.damnpMouldAndRepairComments,
    preSapScore: d.preSap,
    coordinator: row.coordinator,
    ioeV1Date: d.mtpCompletionDate,
    ioeV2Date: d.mtpReModelCompletionDate,
    ioeV3Date: d.ioeV3CompletionDate,
    proposedMeasures: d.proposedMeasures,
    approvedPackage: d.approvedPackage,
    designer: row.designer,
    designDate: d.designCompletionDate,
    actualMeasuresInstalled: d.actualMeasuresInstalled,
    installer: d.installer,
    installerHandover: d.installerHandover,
    lodgementStatus: d.lodgementStatus,
    measuresLodgementDate: d.measuresLodgementDate,
    fullLodgementDate: d.lodgementDate,
    confirmedSurveyDate: d.confirmedSurveyDate,
    surveyedDate: d.surveyedDate,
    designType: d.dealType,
    eiScore: d.eiScore,
    eiScorePotential: d.eiScorePotential,
    epcSapScore: d.epcSapScore,
    epcSapScorePotential: d.epcSapScorePotential,
    createdAt: d.createdAt,
    updatedAt: d.updatedAt,
  };
}

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 the linked organisation 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)))
    .limit(1);

  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>
  );

  if (!link.length || !link[0].hubspotCompanyId) {
    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 companyId = link[0].hubspotCompanyId;

  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(eq(hubspotDealData.companyId, companyId));

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

  // Fetch current user's portfolio capabilities (approver / contractor — can have both)
  let userCapability: PortfolioCapabilityType = [];
  const userEmail = user?.user?.email;
  if (userEmail) {
    const userRow = await db
      .select({ id: userTable.id })
      .from(userTable)
      .where(eq(userTable.email, userEmail))
      .limit(1);

    if (userRow[0]) {
      const capRows = await db
        .select({ capability: portfolioCapabilities.capability })
        .from(portfolioCapabilities)
        .where(
          and(
            eq(portfolioCapabilities.portfolioId, BigInt(portfolioId)),
            eq(portfolioCapabilities.userId, userRow[0].id),
          ),
        );
      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 (userEmail) {
    const userRow = await db
      .select({ id: userTable.id })
      .from(userTable)
      .where(eq(userTable.email, userEmail))
      .limit(1);

    if (userRow[0]) {
      const roleRow = await db
        .select({ role: portfolioUsers.role })
        .from(portfolioUsers)
        .where(
          and(
            eq(portfolioUsers.portfolioId, BigInt(portfolioId)),
            eq(portfolioUsers.userId, userRow[0].id),
          ),
        )
        .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);
    }
  }

  // Compute effective removal state per deal
  const removalStatusByDeal: RemovalStatusByDeal = {};
  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));

  // Keep only the most recent row per deal, then derive effective state
  const seenDeals = new Set<string>();
  for (const row of removalRows) {
    if (seenDeals.has(row.hubspotDealId)) continue;
    seenDeals.add(row.hubspotDealId);
    let state: EffectiveRemovalState = "none";
    if (row.status === "pending") {
      state = row.type === "re_addition" ? "pending_re_addition" : "pending_removal";
    } else if (row.type === "removal" && row.status === "approved") {
      state = "removed";
    } else if (row.type === "re_addition" && row.status === "declined") {
      state = "removed";
    }
    if (state !== "none") removalStatusByDeal[row.hubspotDealId] = state;
  }

  // Fetch document status for all deals — two-phase strategy:
  // Phase 1: query by dealId (reliable even when UPRN is missing from hubspot_deal_data)
  // Phase 2: UPRN fallback only for deals that returned no results in phase 1
  const docsByDealId = new Map<string, Array<{ fileType: string; measureName: string | null }>>();

  if (dealIds.length > 0) {
    const phase1Rows = await db
      .select({
        hubsotDealId: uploadedFiles.hubsotDealId,
        fileType: uploadedFiles.fileType,
        measureName: uploadedFiles.measureName,
      })
      .from(uploadedFiles)
      .where(inArray(uploadedFiles.hubsotDealId, dealIds));

    for (const row of phase1Rows) {
      if (!row.hubsotDealId || row.fileType === null) continue;
      if (!docsByDealId.has(row.hubsotDealId)) docsByDealId.set(row.hubsotDealId, []);
      docsByDealId.get(row.hubsotDealId)!.push({ fileType: row.fileType, measureName: row.measureName });
    }
  }

  // Phase 2: for deals with no docs from phase 1 that have a UPRN, try UPRN lookup
  const dealsWithoutDocs = deals.filter((d) => !docsByDealId.has(d.dealId));
  const fallbackUprns = dealsWithoutDocs
    .map((d) => d.uprn)
    .filter((u): u is string => !!u)
    .map((u) => { try { return BigInt(u); } catch { return null; } })
    .filter((u): u is bigint => u !== null);

  if (fallbackUprns.length > 0) {
    const phase2Rows = await db
      .select({
        uprn: uploadedFiles.uprn,
        fileType: uploadedFiles.fileType,
        measureName: uploadedFiles.measureName,
      })
      .from(uploadedFiles)
      .where(inArray(uploadedFiles.uprn, fallbackUprns));

    // Map phase 2 UPRN results back to dealId
    const uprnToDealId = new Map<string, string>(
      dealsWithoutDocs
        .filter((d) => d.uprn)
        .map((d) => {
          try { return [String(BigInt(d.uprn!)), d.dealId] as [string, string]; } catch { return null; }
        })
        .filter((e): e is [string, string] => e !== null),
    );

    for (const row of phase2Rows) {
      if (row.uprn === null || row.fileType === null) continue;
      const dealId = uprnToDealId.get(String(row.uprn));
      if (!dealId) continue;
      if (!docsByDealId.has(dealId)) docsByDealId.set(dealId, []);
      docsByDealId.get(dealId)!.push({ fileType: row.fileType, measureName: row.measureName });
    }
  }

  // Build measures lookup by dealId (approved measures, falling back to proposed)
  const measuresByDealId = new Map<string, string[]>();
  for (const deal of deals) {
    const approved = approvalsByDeal[deal.dealId] ?? [];
    const measures = approved.length > 0
      ? approved
      : (deal.proposedMeasures ?? "").split(",").map((m: string) => m.trim()).filter(Boolean);
    measuresByDealId.set(deal.dealId, measures);
  }

  // Build docStatusMap keyed by dealId
  const docStatusMap: DocStatusMap = {};

  for (const [dealId, docs] of docsByDealId) {
    const surveyDocs = docs.filter((d) => SURVEY_ALL_DOC_TYPES.has(d.fileType));
    const installDocs = docs.filter((d) => !SURVEY_ALL_DOC_TYPES.has(d.fileType));
    const surveyTypeSet = new Set(surveyDocs.map((d) => d.fileType));

    const measures = measuresByDealId.get(dealId) ?? [];

    // Compute per-measure document progress against the requirements matrix
    const measureProgress: MeasureDocProgress[] = measures.map((measureName) => {
      const required = getRequiredDocs(measureName);
      const docsForMeasure = installDocs.filter((d) => d.measureName === measureName);
      const uploadedTypeSet = new Set(docsForMeasure.map((d) => d.fileType));
      const uploaded = required.filter((r) => uploadedTypeSet.has(r));
      return {
        measureName,
        required,
        uploaded,
        isComplete: uploaded.length === required.length,
        uploadedCount: uploaded.length,
        requiredCount: required.length,
      };
    });

    let installStatus: DocStatus["installStatus"] = "none";
    if (installDocs.length > 0) {
      if (measures.length === 0) {
        installStatus = "hasDocs";
      } else {
        installStatus = measureProgress.every((m) => m.isComplete)
          ? "all"
          : measureProgress.some((m) => m.uploadedCount > 0)
            ? "partial"
            : "none";
      }
    }

    docStatusMap[dealId] = {
      presentSurveyTypes: Array.from(surveyTypeSet),
      hasSurveyDocs: surveyDocs.length > 0,
      isSurveyComplete: EXPECTED_RETROFIT_ASSESSMENT_DOC_TYPES.every((t) => surveyTypeSet.has(t)),
      hasInstallDocs: installDocs.length > 0,
      installStatus,
      measureProgress,
    };
  }

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