import { createClient } from "@/lib/supabase/server";
import { ReportTable } from "@/components/reports/report-table";
import type { ReportColumn } from "@/lib/types";

export const dynamic = "force-dynamic";

/** Supabase embeds can come back as an object or a single-element array. */
function first<T>(value: T | T[] | null | undefined): T | null {
  if (Array.isArray(value)) return value[0] ?? null;
  return value ?? null;
}

interface VisaTrackingRow {
  id: string;
  student_application_id: string | null;
  decision: string | null;
  status: { name: string | null } | { name: string | null }[] | null;
  application:
    | { applicant_name: string | null; branch_id: string | null }
    | { applicant_name: string | null; branch_id: string | null }[]
    | null;
}

interface FeePaymentRow {
  student_application_id: string | null;
  amount: number | null;
}

interface BranchRow {
  id: string;
  name: string | null;
}

const APPROVED_STATUSES = new Set(["visa approved", "travelled"]);

const columns: ReportColumn[] = [
  { key: "applicant", label: "Applicant" },
  { key: "branch", label: "Branch" },
  { key: "visaStatus", label: "Visa Status" },
  { key: "collected", label: "Collected", numeric: true },
];

export default async function VisaCollectionReportPage() {
  const supabase = await createClient();

  const [{ data: trackingData }, { data: paymentData }, { data: branchData }] =
    await Promise.all([
      supabase
        .from("visa_tracking")
        .select(
          "id, student_application_id, decision, status:visa_statuses(name), application:student_applications(applicant_name, branch_id)",
        ),
      supabase.from("fee_payments").select("student_application_id, amount"),
      supabase.from("branches").select("id, name"),
    ]);

  const tracking = (trackingData as unknown as VisaTrackingRow[]) ?? [];
  const payments = (paymentData as unknown as FeePaymentRow[]) ?? [];
  const branches = (branchData as unknown as BranchRow[]) ?? [];

  const branchById = new Map<string, string>();
  for (const b of branches) {
    branchById.set(b.id, b.name ?? "—");
  }

  // Aggregate total fee payments per student_application_id.
  const collectedByApp = new Map<string, number>();
  for (const p of payments) {
    if (!p.student_application_id) continue;
    const prev = collectedByApp.get(p.student_application_id) ?? 0;
    collectedByApp.set(p.student_application_id, prev + (Number(p.amount) || 0));
  }

  const rows = tracking
    .filter((t) => {
      const statusName = (first(t.status)?.name ?? "").toLowerCase();
      const isApproved =
        t.decision?.toLowerCase() === "approved" ||
        APPROVED_STATUSES.has(statusName);
      return isApproved;
    })
    .map((t) => {
      const app = first(t.application);
      const collected = t.student_application_id
        ? collectedByApp.get(t.student_application_id) ?? 0
        : 0;
      return {
        applicant: app?.applicant_name ?? "—",
        branch: app?.branch_id ? branchById.get(app.branch_id) ?? "—" : "—",
        visaStatus: first(t.status)?.name ?? "—",
        collected,
      };
    });

  const grandTotal = rows.reduce((sum, row) => sum + row.collected, 0);

  return (
    <ReportTable
      title="Visa Collection"
      breadcrumb="Home / Reports / Visa Collection"
      columns={columns}
      rows={rows}
      footerNote={`Total collected: ${grandTotal.toLocaleString()}`}
    />
  );
}