Workflow-App/Workflow/lib/pb.ts

// lib/pb.ts
// SERVER-ONLY PocketBase client.
//
// Token handling is self-healing: PB superuser tokens expire every ~24h,
// so on every request we may transparently re-authenticate using the
// admin's email + password (PB_ADMIN_EMAIL / PB_ADMIN_PASSWORD in
// `.env.local`) and refresh the in-memory cached token. Concurrent
// requests share a single in-flight refresh (no thundering herd), and
// each fetch retries exactly once on a 401/403 from PB before
// surfacing the error.
import "server-only";
import { deriveCompanyStatus } from "./format";

export const PB_URL = process.env.PB_URL;
export const BASE_URL = PB_URL;

if (!BASE_URL) throw new Error("PB_URL is not set.");

// Re-auth fallback creds. Required for self-healing; optional otherwise.
const ADMIN_EMAIL = process.env.PB_ADMIN_EMAIL ?? "";
const ADMIN_PASSWORD = process.env.PB_ADMIN_PASSWORD ?? "";

// In-memory token cache. Starts as the env-var value at module load
// (so the very first request uses the static PB_TOKEN). Once a
// request fails with 401/403, this is cleared and the next call to
// getAuthToken() will trigger a re-auth.
let cachedToken: string | null = process.env.PB_TOKEN ?? null;
// Promise-dedupe so N concurrent failed requests trigger exactly
// one re-auth, not N.
let refreshInFlight: Promise<string> | null = null;

interface AdminAuthResponse {
  token: string;
  record?: { id: string; email: string; exp?: number };
}

/** Re-authenticate as the PB superuser and cache the new JWT.
 *  Throws if credentials are missing or the auth endpoint rejects. */
async function refreshToken(): Promise<string> {
  if (!ADMIN_EMAIL || !ADMIN_PASSWORD) {
    throw new Error(
      "PB token expired and PB_ADMIN_EMAIL / PB_ADMIN_PASSWORD are not set in .env.local — cannot self-heal."
    );
  }
  const url = `${BASE_URL}/api/collections/_superusers/auth-with-password`;
  const res = await fetch(url, {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({ identity: ADMIN_EMAIL, password: ADMIN_PASSWORD }),
    cache: "no-store",
    signal: AbortSignal.timeout(15000),
  });
  if (!res.ok) {
    const body = await res.text();
    throw new Error(
      `PB superuser re-auth failed (${res.status}): ${body.slice(0, 200)}. Check PB_ADMIN_EMAIL / PB_ADMIN_PASSWORD in .env.local.`
    );
  }
  const data = (await res.json()) as AdminAuthResponse;
  if (!data?.token) {
    throw new Error("PB superuser re-auth: response missing token field");
  }
  cachedToken = data.token;
  const exp = data.record?.exp;
  const expStr = typeof exp === "number"
    ? ` (new expiry ${new Date(exp * 1000).toISOString()})`
    : "";
  console.warn(`[pb] Superuser token refreshed automatically${expStr}.`);
  return cachedToken;
}

/** Returns a valid (fresh) superuser token, refreshing if needed.
 *  Concurrent calls share a single in-flight refresh. */
async function getAuthToken(): Promise<string> {
  if (cachedToken) return cachedToken;
  if (!refreshInFlight) {
    refreshInFlight = refreshToken().finally(() => {
      refreshInFlight = null;
    });
  }
  return refreshInFlight;
}

/** Authorization header for a hand-rolled PocketBase fetch.
 *
 *  Prefer the helpers in this module (pbFetch / createRecord /
 *  updateRecord / …) — they route through pbFetchRaw and get the
 *  401-refresh-retry behaviour for free. Reach for this ONLY when you
 *  need to hand-build a URL or use a verb pbFetchRaw doesn't expose.
 *
 *  Never read `process.env.PB_TOKEN` directly in server code: that
 *  value is a static ~24h JWT that is frequently stale, and it bypasses
 *  the self-healing refresh. It is deliberately NOT exported as
 *  `PB_TOKEN` so that mistake is a compile error rather than a silent
 *  `Authorization: undefined` at runtime. */
export async function pbAuthHeaders(): Promise<Record<string, string>> {
  return { Authorization: await getAuthToken() };
}

/** Low-level wrapper around fetch that:
 *    - attaches the current superuser token
 *    - on 401/403, clears the cache, refreshes, and retries ONCE
 *    - if the retry also fails, surfaces the second response's error
 *  All exported functions in this module route through this wrapper.
 */
async function pbFetchRaw(url: string, init: RequestInit = {}): Promise<Response> {
  const token = await getAuthToken();
  const headers = new Headers(init.headers);
  headers.set("Authorization", token);
  const res = await fetch(url, { ...init, headers, cache: "no-store" });

  if (res.status === 401 || res.status === 403) {
    // Token may be stale (expired/invalidated). Force a re-auth and
    // retry exactly once — the second attempt uses a guaranteed-fresh
    // JWT, so a retry failure is a real permission error to surface.
    cachedToken = null;
    const newToken = await getAuthToken();
    const retryHeaders = new Headers(init.headers);
    retryHeaders.set("Authorization", newToken);
    return fetch(url, { ...init, headers: retryHeaders, cache: "no-store" });
  }

  return res;
}

export interface PBList<T> { page: number; perPage: number; totalItems: number; totalPages: number; items: T[]; }
export interface PBRecord { id: string; collectionId: string; collectionName: string; [key: string]: unknown; }

function buildUrl(path: string, query: Record<string, string | number | undefined> = {}): string {
  const params = new URLSearchParams();
  for (const [k, v] of Object.entries(query)) {
    if (v !== undefined && v !== null && v !== "") params.set(k, String(v));
  }
  const qs = params.toString();
  return `${BASE_URL}${path}${qs ? `?${qs}` : ""}`;
}

export async function pbFetch<T>(path: string, query: Record<string, string | number | undefined> = {}): Promise<T> {
  const url = buildUrl(path, query);
  const res = await pbFetchRaw(url);
  if (!res.ok) {
    const body = await res.text();
    throw new Error(`PocketBase ${res.status} on ${path}: ${body.slice(0, 200)}`);
  }
  return (await res.json()) as T;
}

export async function createRecordMultipart<T extends PBRecord>(collection: string, fields: Record<string, string>, files: { field: string; file: File }[] = []): Promise<T> {
  const form = new FormData();
  for (const [k, v] of Object.entries(fields)) form.set(k, v);
  for (const { field, file } of files) form.append(field, file, file.name);
  const url = `${BASE_URL}/api/collections/${collection}/records`;
  const res = await pbFetchRaw(url, { method: "POST", body: form });
  if (!res.ok) {
    const body = await res.text();
    throw new Error(`PocketBase ${res.status} on POST ${collection}: ${body.slice(0, 300)}`);
  }
  return (await res.json()) as T;
}

export async function updateRecord<T extends PBRecord>(collection: string, id: string, fields: Record<string, unknown>): Promise<T> {
  const url = `${BASE_URL}/api/collections/${collection}/records/${encodeURIComponent(id)}`;
  const res = await pbFetchRaw(url, {
    method: "PATCH",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify(fields),
  });
  if (!res.ok) {
    const body = await res.text();
    throw new Error(`PocketBase ${res.status} on PATCH ${collection}/${id}: ${body.slice(0, 300)}`);
  }
  return (await res.json()) as T;
}

export async function fetchCollection<T extends PBRecord>(collection: string, params: { page?: number; perPage?: number; sort?: string; filter?: string; expand?: string; fields?: string } = {}): Promise<PBList<T>> {
  return pbFetch<PBList<T>>(`/api/collections/${collection}/records`, {
    page: params.page ?? 1,
    perPage: params.perPage ?? 25,
    sort: params.sort,
    filter: params.filter,
    expand: params.expand,
    fields: params.fields,
  });
}


/**
 * JSON create — for collections without file fields.
 * Explicit IDs are preserved (some collections have required system-id fields
 * with no auto-generate pattern).
 */
export async function createRecord<T extends PBRecord>(
  collection: string,
  fields: Record<string, unknown>,
): Promise<T> {
  const url = `${BASE_URL}/api/collections/${collection}/records`;
  const res = await pbFetchRaw(url, {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify(fields),
  });
  if (!res.ok) {
    const body = await res.text();
    throw new Error(`PocketBase ${res.status} on POST ${collection}: ${body.slice(0, 300)}`);
  }
  return (await res.json()) as T;
}

export async function fetchRecord<T = unknown>(collection: string, id: string, expand?: string): Promise<T> {
  return pbFetch<T>(`/api/collections/${collection}/records/${encodeURIComponent(id)}`, { expand });
}

/**
 * Junction table helpers — ysterbaard_company_directors (pbc_2497518006)
 */

export async function fetchJunctionRecords(params: {
  filter?: string;
  expand?: string;
  perPage?: number;
  sort?: string;
}): Promise<PBList<PBRecord>> {
  return pbFetch<PBList<PBRecord>>("/api/collections/pbc_2497518006/records", {
    page: 1,
    perPage: params.perPage ?? 500,
    sort: params.sort,
    filter: params.filter,
    expand: params.expand,
  });
}

/** All directors linked to a company, via the junction table */
export async function fetchDirectorsForCompany(companyId: string): Promise<PBRecord[]> {
  const safe = companyId.replace(/'/g, "''");
  const list = await fetchJunctionRecords({
    filter: `company = '${safe}'`,
    expand: "director",
    perPage: 500,
  });
  return list.items.filter((j) => (j.expand as Record<string, unknown>)?.director);
}

/** All companies linked to a director, via the junction table — returns junction records */
export async function fetchJunctionEntriesForDirector(directorId: string): Promise<PBRecord[]> {
  const safe = directorId.replace(/'/g, "''");
  const list = await fetchJunctionRecords({
    filter: `director = '${safe}'`,
    expand: "company",
    perPage: 500,
  });
  return list.items.filter((j) => (j.expand as Record<string, unknown>)?.company);
}

/** All directors linked to a company, via the junction table — returns junction records */
export async function fetchJunctionEntriesForCompany(companyId: string): Promise<PBRecord[]> {
  const safe = companyId.replace(/'/g, "''");
  const list = await fetchJunctionRecords({
    filter: `company = '${safe}'`,
    expand: "director",
    perPage: 500,
  });
  return list.items.filter((j) => (j.expand as Record<string, unknown>)?.director);
}

/**
 * Recompute a director's denormalised company counters from the junction
 * table.
 *
 * `ysterbaard_company_directors` is the single source of truth for
 * director<->company links. `total_companies` / `active_companies` /
 * `inactive_companies` are only a cache of it — they used to be hard-zeroed
 * on every director create and never recomputed, which is why the Directors
 * tab disagreed with the company pages.
 *
 * Only "In business" counts as active, matching the badge on the company
 * page. "In process" is not yet trading, so it lands in inactive and
 * total = active + inactive always holds.
 *
 * Call this after any link/unlink so the counters cannot drift again.
 */
export async function recomputeDirectorCompanyCounts(directorId: string): Promise<void> {
  const safe = directorId.replace(/'/g, "''");
  const junctions = await fetchJunctionRecords({
    filter: `director = '${safe}'`,
    perPage: 500,
  });
  const companyIds = [
    ...new Set(junctions.items.map((j) => String(j.company ?? "")).filter(Boolean)),
  ];

  let active = 0;
  if (companyIds.length > 0) {
    const companies = await fetchCollection<PBRecord>("ysterbaard_companies", {
      perPage: 500,
      filter: companyIds.map((id) => `id = "${id}"`).join(" || "),
    });
    active = companies.items.filter(
      (c) =>
        deriveCompanyStatus(
          (c.enterprise_status as string | null) ?? null,
          Boolean(c.is_active)
        ) === "active"
    ).length;
  }

  const total = companyIds.length;
  await updateRecord("ysterbaard_directors", directorId, {
    total_companies: total,
    active_companies: active,
    inactive_companies: total - active,
  });
}

export async function deleteRecord(collection: string, id: string): Promise<void> {
  const url = `${BASE_URL}/api/collections/${collection}/records/${encodeURIComponent(id)}`;
  const res = await pbFetchRaw(url, { method: "DELETE" });
  if (!res.ok && res.status !== 204) {
    const body = await res.text();
    throw new Error(`PocketBase ${res.status} on DELETE ${collection}/${id}: ${body.slice(0, 300)}`);
  }
}

export async function updateRecordMultipart<T extends PBRecord>(
  collection: string,
  id: string,
  fields: Record<string, string>,
  files: { field: string; file: File }[] = []
): Promise<T> {
  const form = new FormData();
  for (const [k, v] of Object.entries(fields)) form.set(k, v);
  for (const { field, file } of files) form.append(field, file, file.name);
  const url = `${BASE_URL}/api/collections/${collection}/records/${encodeURIComponent(id)}`;
  const res = await pbFetchRaw(url, { method: "PATCH", body: form });
  if (!res.ok) {
    const body = await res.text();
    throw new Error(`PocketBase ${res.status} on PATCH ${collection}/${id}: ${body.slice(0, 300)}`);
  }
  return (await res.json()) as T;
}