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 ApplicationRow {
  id: string;
  counselor_id: string | null;
  counselor: { full_name: string | null } | { full_name: string | null }[] | null;
}

interface VisaTrackingRow {
  student_application_id: string | null;
  decision: string | null;
  status: { name: string | null } | { name: string | null }[] | null;
}

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

const columns: ReportColumn[] = [
  { key: "counselor", label: "Counselor" },
  { key: "approved", label: "Visa Approved", numeric: true },
  { key: "total", label: "Total Applications", numeric: true },
];

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

  const [{ data: appData }, { data: trackingData }] = await Promise.all([
    supabase
      .from("student_applications")
      .select("id, counselor_id, counselor:users(full_name)"),
    supabase
      .from("visa_tracking")
      .select("student_application_id, decision, status:visa_statuses(name)"),
  ]);

  const applications = (appData as unknown as ApplicationRow[]) ?? [];
  const tracking = (trackingData as unknown as VisaTrackingRow[]) ?? [];

  // Set of application ids that have an approved visa.
  const approvedAppIds = new Set<string>();
  for (const t of tracking) {
    if (!t.student_application_id) continue;
    const statusName = (first(t.status)?.name ?? "").toLowerCase();
    const isApproved =
      t.decision?.toLowerCase() === "approved" ||
      APPROVED_STATUSES.has(statusName);
    if (isApproved) approvedAppIds.add(t.student_application_id);
  }

  // Aggregate per counselor.
  const byCounselor = new Map<
    string,
    { name: string; approved: number; total: number }
  >();
  for (const app of applications) {
    const key = app.counselor_id ?? "unassigned";
    const name = first(app.counselor)?.full_name ?? "Unassigned";
    const entry = byCounselor.get(key) ?? { name, approved: 0, total: 0 };
    entry.total += 1;
    if (approvedAppIds.has(app.id)) entry.approved += 1;
    byCounselor.set(key, entry);
  }

  const rows = Array.from(byCounselor.values())
    .sort((a, b) => b.approved - a.approved || b.total - a.total)
    .map((c) => ({
      counselor: c.name,
      approved: c.approved,
      total: c.total,
    }));

  return (
    <ReportTable
      title="Visa Numbers by Counselor"
      breadcrumb="Home / Reports / Visa Numbers by Counselor"
      columns={columns}
      rows={rows}
    />
  );
}