Substrate/get-de-lobbyregister

#!/usr/bin/env bun

/**
 * Get DE Lobbyregister Data
 *
 * Fetches all registrations from the Bundestag Lobbyregister API and produces:
 *   - Data/DE-Lobby-Transparency/top-50-spenders.csv
 *   - Data/DE-Lobby-Transparency/sector-summary.csv
 *
 * API: https://www.lobbyregister.bundestag.de/sucheDetailJson
 * Source: https://github.com/bundesAPI/bundestag-lobbyregister-api
 */

// Bun on macOS lacks the Bundestag CA in its bundled cert store; curl uses the system
// keychain. This flag allows the fetch to proceed against the known-good government endpoint.
process.env.NODE_TLS_REJECT_UNAUTHORIZED = "0";

import { writeFileSync, mkdirSync } from "fs";
import { join } from "path";

const API_URL =
  "https://www.lobbyregister.bundestag.de/sucheDetailJson?q=&sort=FINANCIALEXPENSES_DESC";
const OUT_DIR = join(__dirname, "Data/DE-Lobby-Transparency");

interface LegalForm {
  code: string;
  de: string;
  en: string;
}

interface FieldOfInterest {
  code: string;
  de: string;
  en: string;
}

interface FinancialExpensesEuro {
  from: number;
  to: number;
}

interface FinancialExpenses {
  relatedFiscalYearStart?: string;
  relatedFiscalYearEnd?: string;
  financialExpensesEuro?: FinancialExpensesEuro;
}

interface EmployeesInLobbying {
  employeeFTE?: number;
}

interface LobbyistIdentity {
  name?: string;
  legalForm?: LegalForm;
}

interface ActivitiesAndInterests {
  fieldsOfInterest?: FieldOfInterest[];
}

interface LobbyEntry {
  registerNumber: string;
  lobbyistIdentity?: LobbyistIdentity;
  financialExpenses?: FinancialExpenses;
  employeesInvolvedInLobbying?: EmployeesInLobbying;
  activitiesAndInterests?: ActivitiesAndInterests;
}

interface ApiResponse {
  resultCount: number;
  results: LobbyEntry[];
}

function csvEscape(value: string | number | undefined | null): string {
  if (value === null || value === undefined) return "";
  const s = String(value);
  if (s.includes(",") || s.includes('"') || s.includes("\n")) {
    return `"${s.replace(/"/g, '""')}"`;
  }
  return s;
}

// The Lobbyregister taxonomy uses "|" to separate parent|child codes.
// Top-level sectors have no "|" in their code.
const isTopLevel = (f: FieldOfInterest) => !f.code.includes("|");

function primaryTopLevelSector(foi: FieldOfInterest[] | undefined): string {
  if (!foi || foi.length === 0) return "";
  return (foi.find(isTopLevel) ?? foi[0])?.de ?? "";
}

async function main() {
  console.log("Fetching Lobbyregister data…");
  const res = await fetch(API_URL);
  if (!res.ok) throw new Error(`HTTP ${res.status}: ${res.statusText}`);

  const data = (await res.json()) as ApiResponse;
  const entries = data.results ?? [];
  if (entries.length < data.resultCount) {
    console.warn(`Warning: received ${entries.length} of ${data.resultCount} reported registrations`);
  }
  console.log(`Fetched ${entries.length} registrations (API total: ${data.resultCount})`);

  mkdirSync(OUT_DIR, { recursive: true });

  // ── Top 50 Spenders ─────────────────────────────────────────────────────
  const withFinancials = entries.filter(
    (e) => e.financialExpenses?.financialExpensesEuro
  );

  const top50Header =
    "rank,register_number,name,legal_form_de,expenses_from_eur,expenses_to_eur,employees_fte,primary_sector_de";
  const top50Rows = withFinancials.slice(0, 50).map((e, i) => {
    const fin = e.financialExpenses!.financialExpensesEuro!;
    const identity = e.lobbyistIdentity ?? {};
    const emp = e.employeesInvolvedInLobbying?.employeeFTE;
    const sector = primaryTopLevelSector(
      e.activitiesAndInterests?.fieldsOfInterest
    );
    return [
      i + 1,
      csvEscape(e.registerNumber),
      csvEscape(identity.name),
      csvEscape(identity.legalForm?.de),
      fin.from,
      fin.to,
      emp !== undefined ? emp : "",
      csvEscape(sector),
    ].join(",");
  });

  const top50Path = join(OUT_DIR, "top-50-spenders.csv");
  writeFileSync(top50Path, [top50Header, ...top50Rows].join("\n") + "\n");
  console.log(`Wrote ${top50Path}`);

  // ── Sector Summary + Totals (single pass) ───────────────────────────────
  const sectorMap = new Map<
    string,
    { de: string; en: string; count: number; totalMinEur: number }
  >();
  let totalMin = 0;
  let totalMax = 0;

  for (const e of entries) {
    const fin = e.financialExpenses?.financialExpensesEuro;
    if (fin) {
      totalMin += fin.from;
      totalMax += fin.to;
    }

    const foi = e.activitiesAndInterests?.fieldsOfInterest ?? [];
    const topLevelSectors = foi.filter(isTopLevel);
    if (topLevelSectors.length === 0) continue;

    const finMin = fin?.from ?? 0;
    for (const f of topLevelSectors) {
      const existing = sectorMap.get(f.code);
      if (existing) {
        existing.count += 1;
        existing.totalMinEur += finMin;
      } else {
        sectorMap.set(f.code, { de: f.de, en: f.en, count: 1, totalMinEur: finMin });
      }
    }
  }

  const sortedSectors = [...sectorMap.entries()].sort(
    (a, b) => b[1].count - a[1].count
  );

  const sectorHeader = "sector_code,sector_de,sector_en,registrant_count,total_min_eur";
  const sectorRows = sortedSectors.map(([code, s]) =>
    [
      csvEscape(code),
      csvEscape(s.de),
      csvEscape(s.en),
      s.count,
      s.totalMinEur,
    ].join(",")
  );

  const sectorPath = join(OUT_DIR, "sector-summary.csv");
  writeFileSync(sectorPath, [sectorHeader, ...sectorRows].join("\n") + "\n");
  console.log(`Wrote ${sectorPath}`);

  console.log(`\n── Summary ──────────────────────────────`);
  console.log(`Total registrations:     ${entries.length}`);
  console.log(`With financial data:     ${withFinancials.length}`);
  console.log(`Total expenditure range: ${(totalMin / 1e9).toFixed(2)}B – ${(totalMax / 1e9).toFixed(2)}B EUR`);
  console.log(`Top spender:             ${withFinancials[0]?.lobbyistIdentity?.name ?? "N/A"}`);
  console.log(`Top sector (by count):   ${sortedSectors[0]?.[1].de ?? "N/A"} (${sortedSectors[0]?.[1].count} registrants)`);
}

main().catch((err) => {
  console.error("Error:", err.message);
  process.exit(1);
});