lib/feed.ts · Docket

2037 lines · typescript
 
import { safeAll, safeGet, safeCount } from "@/lib/db";
 
type CacheEntry<T> = { value: T; expiry: number };
const _cache = new Map<string, CacheEntry<unknown>>();
const CACHE_TTL_MS = 5 * 60 * 1000;
 
function cached<T>(key: string, fn: () => T, ttl = CACHE_TTL_MS): T {
  const now = Date.now();
  const entry = _cache.get(key) as CacheEntry<T> | undefined;
  if (entry && entry.expiry > now) return entry.value;
  const value = fn();
  _cache.set(key, { value, expiry: now + ttl });
  return value;
}
import { formatUsd } from "@/lib/format";
import type { PartyCode } from "@/lib/neutrality";
 
 
export type FeedItem = {
  id: string;
  kind:
    | "bill"
    | "vote"
    | "award"
    | "amendment"
    | "rider"
    | "procedural"
    | "executive"
    | "apportionment";
  actionDate: string;
  chamber: string;
  title: string;
  summary?: string;
  actionType: string;
  amount?: number | null;
  amountText?: string;
  jurisdiction: string;
  sourceUrl: string;
  href?: string;
};
 
export type Award = {
  id: string;
  agency: string;
  recipient: string;
  amount: number;
  amountText: string;
  actionDate: string;
  program: string;
  sourceUrl: string;
  href: string;
};
 
export type FiscalMetric = {
  id: string;
  metric: string;
  value: number;
  valueText: string;
  date: string;
  sourceUrl: string;
  href: string;
};
 
export type Apportionment = {
  id: string;
  agency: string;
  account: string;
  appropriated: number | null;
  apportioned: number | null;
  status: string;
  date: string;
  sourceUrl: string;
  href: string;
};
 
export type Vote = {
  id: string;
  billId?: number | null;
  billNumber?: string;
  billTitle?: string;
 
  billTextRef?: string;
  chamber: string;
  date: string;
  result: string;
  yea: number;
  nay: number;
  present: number;
  recorded: boolean;
  sourceUrl: string;
  href?: string;
 
  archiveUrl?: string;
};
 
export type ExecutiveAction = {
  id: string;
  type: string;
  title: string;
  agency: string;
  frDocNumber: string;
  date: string;
  sourceUrl: string;
  href?: string;
};
 
 
export function partyCodeFromWord(word: string): PartyCode {
  const w = (word || "").toLowerCase();
  if (w.startsWith("rep")) return "R";
  if (w.startsWith("dem")) return "D";
  return "I";
}
 
export type MemberSummary = {
  id: string;
  numericId: number;
  bioguide: string;
  name: string;
  party: string;
  partyCode: PartyCode;
  state: string;
  district: number | null;
  chamber: string;
  photoUrl: string;
  sourceUrl: string;
  href: string;
 
  isCurrent: boolean;
};
 
export type VotePosition = {
  member: string;
  party: string;
  partyCode: PartyCode;
  state: string;
  district: number | null;
  position: string;
  photoUrl: string;
  memberHref: string;
};
 
export type VoteDetail = Vote & {
  question?: string;
  rollNumber?: number;
  notVoting?: number;
  positions: VotePosition[];
};
 
export type MemberVote = {
  voteId: string;
  voteHref: string;
  date: string;
  chamber: string;
  question?: string;
  result: string;
  yea: number;
  nay: number;
  position: string;
  sourceUrl: string;
};
 
 
 
export type MemberYearGroup = {
  year: number | null;
  count: number;
};
 
export type MemberSponsorship = {
  billId: string;
  billHref: string;
  number: string;
  title: string;
  congress: number;
  status: string;
  updatedAt: string;
  sourceUrl: string;
};
 
export type MemberAmendment = {
  amendmentId: string;
  amendmentHref: string;
  number: string;
  type: string;
  status: string;
  billId: number | null;
  billHref: string | null;
  sourceUrl: string;
};
 
export type MemberDetail = MemberSummary & {
  votes: MemberVote[];
  sponsorships: MemberSponsorship[];
  amendments: MemberAmendment[];
  voteCount: number;
  votesByYear: MemberYearGroup[];
};
 
 
export type BillVersion = {
  id: string;
  versionCode: string;
  versionLabel: string;
  versionDate: string;
  sourceUrl: string;
};
 
export type BillDiffChange = {
  sectionId: string;
  change: string;
  snippet: string;
  riderFlag: boolean;
  sourceUrl: string;
  fromVersion: string;
  toVersion: string;
};
 
export type BillDiff = {
  from: string;
  to: string;
  changes: BillDiffChange[];
  note?: string;
};
 
export type BillAmendment = {
  id: string;
  number: string;
  type: string;
  status: string;
  agreedMethod: string;
  billId: number | null;
  sponsor: MemberSummary | null;
  sourceUrl: string;
  href: string;
};
 
export type BillTimelineEntry = {
  date: string;
  chamber: string;
  actionType: string;
  text: string;
  sourceUrl: string;
};
 
export type BillDetail = {
  id: string;
  number: string;
  title: string;
  congress: number;
  policyArea: string;
  status: string;
  updatedAt: string;
  sourceUrl: string;
  href: string;
  sponsor: MemberSummary | null;
  sponsorDisplay: string;
  versions: BillVersion[];
  amendments: BillAmendment[];
  diffs: BillDiffChange[];
  votes: VoteDetail[];
  timeline: BillTimelineEntry[];
  subjects: string[];
  fullTextUrl: string;
  summaryPlain: string;
 
  summary: string;
  summarySource: string;
  pdfUrl: string;
  archivePdfUrl: string;
};
 
 
export type StateCoverage = {
  code: string;
  name: string;
  confidence: "ok" | "stale" | "missing";
  lastScrape: string;
  missingTypes: string[];
};
 
export type StateDetail = StateCoverage & {
  members: MemberSummary[];
  bills: BillListItem[];
  votes: Vote[];
};
 
 
type Rankable = {
  actionDate: string;
  dollarAmount: number | null;
  affectedEstimate: number | null;
  actionType: string;
};
 
 
export function rankByRule(): (a: Rankable, b: Rankable) => number {
  return (a, b) => {
    const ad = dateKey(a.actionDate);
    const bd = dateKey(b.actionDate);
    if (ad !== bd) return bd - ad;
    if (ad === 0) {
      const da = a.dollarAmount ?? 0;
      const db = b.dollarAmount ?? 0;
      if (db !== da) return db - da;
      return (b.affectedEstimate ?? 0) - (a.affectedEstimate ?? 0);
    }
    const da = a.dollarAmount ?? 0;
    const db = b.dollarAmount ?? 0;
    if (db !== da) return db - da;
    return (b.affectedEstimate ?? 0) - (a.affectedEstimate ?? 0);
  };
}
 
function dateKey(iso: string): number {
  if (!iso) return 0;
  const t = Date.parse(iso.length === 10 ? iso + "T00:00:00Z" : iso);
  return Number.isFinite(t) ? Math.floor(t / 86_400_000) : 0;
}
 
 
 
export function getFeedItems(perSource = 400): FeedItem[] {
  return cached(`feed:${perSource}`, () => {
    const items: FeedItem[] = [];
 
    items.push(
      ...safeAll<FeedItem>(
        `SELECT id, agency, recipient, amount, action_date, program, source_url
         FROM awards
         ORDER BY amount DESC
         LIMIT :lim`,
        { lim: perSource },
        (r) => toFeedAward(r)
      )
    );
 
    items.push(
      ...safeAll<FeedItem>(
        `SELECT id, bill_id, chamber, date, yea, nay, present, result, source_url
         FROM votes
         ORDER BY date DESC
         LIMIT :lim`,
        { lim: perSource },
        (r) => toFeedVote(r)
      )
    );
 
    items.push(
      ...safeAll<FeedItem>(
        `SELECT id, type, title, agency, fr_doc_number, date, source_url
         FROM executive_actions
         ORDER BY date DESC
         LIMIT :lim`,
        { lim: perSource },
        (r) => toFeedExecutive(r)
      )
    );
 
    items.push(
      ...safeAll<FeedItem>(
        `SELECT id, number, title, status, updated_at, source_url
         FROM bills
         WHERE congress > 0
         ORDER BY updated_at DESC
         LIMIT :lim`,
        { lim: perSource },
        (r) => toFeedBill(r)
      )
    );
 
    return diversifyByKind(items.sort(rankItems));
  });
}
 
export function getFeedKindCounts(): Map<string, number> {
  return cached("feed:kindcounts", () => {
    const map = new Map<string, number>();
    const billsCount = safeCount("bills");
    const awardsCount = safeCount("awards");
    const votesCount = safeCount("votes");
    const execCount = safeCount("executive_actions");
    if (billsCount) map.set("bill", billsCount);
    if (awardsCount) map.set("award", awardsCount);
    if (votesCount) map.set("vote", votesCount);
    if (execCount) map.set("executive", execCount);
    return map;
  });
}
 
 
function diversifyByKind(items: FeedItem[]): FeedItem[] {
  if (items.length <= 1) return items;
  const groups: FeedItem[][] = [];
  let cur: FeedItem[] = [];
  let curKey = "";
  for (const it of items) {
    const k = it.actionDate?.slice(0, 10) ?? "";
    if (k !== curKey) {
      if (cur.length) groups.push(cur);
      cur = [it];
      curKey = k;
    } else {
      cur.push(it);
    }
  }
  if (cur.length) groups.push(cur);
 
  const out: FeedItem[] = [];
  for (const g of groups) {
    if (g.length <= 2) {
      out.push(...g);
      continue;
    }
    const byKind = new Map<string, FeedItem[]>();
    const order: string[] = [];
    for (const it of g) {
      const arr = byKind.get(it.kind);
      if (arr) arr.push(it);
      else {
        byKind.set(it.kind, [it]);
        order.push(it.kind);
      }
    }
    const maxLen = Math.max(...[...byKind.values()].map((a) => a.length));
    for (let i = 0; i < maxLen; i++) {
      for (const k of order) {
        const arr = byKind.get(k);
        if (arr && i < arr.length) out.push(arr[i]);
      }
    }
  }
  return out;
}
 
 
function toRankable(i: FeedItem): Rankable {
  return {
    actionDate: i.actionDate,
    dollarAmount: i.amount ?? null,
    affectedEstimate: null,
    actionType: i.actionType,
  };
}
const rankItems = (a: FeedItem, b: FeedItem): number =>
  rankByRule()(toRankable(a), toRankable(b));
 
function toFeedAward(r: Record<string, unknown>): FeedItem {
  const amount = num(r.amount);
  const id = `award:${r.id}`;
  return {
    id,
    kind: "award",
    actionDate: str(r.action_date),
    chamber: "USASPENDING",
    title: str(r.agency) || "Federal award",
    summary: str(r.recipient) || undefined,
    actionType: "AWARD",
    amount,
    amountText: formatUsd(amount, { compact: true }),
    jurisdiction: "US",
    sourceUrl: ensureSource(r.source_url),
    href: "/spending",
  };
}
 
function toFeedVote(r: Record<string, unknown>): FeedItem {
  const yea = num(r.yea);
  const nay = num(r.nay);
  const passed = yea > nay;
  const voteId = num(r.id);
  return {
    id: `vote:${r.id}`,
    kind: "vote",
    actionDate: str(r.date),
    chamber: str(r.chamber).toUpperCase() || "SENATE",
    title: str(r.result) || "Roll-call vote",
    summary: undefined,
    actionType: passed ? "PASSED" : "FAILED",
    amount: null,
    amountText: `${yea}\u2013${nay}`,
    jurisdiction: "US",
    sourceUrl: ensureSource(r.source_url),
    href: `/votes/${voteId}`,
  };
}
 
function toFeedExecutive(r: Record<string, unknown>): FeedItem {
  const execId = num(r.id);
  return {
    id: `exec:${r.id}`,
    kind: "executive",
    actionDate: str(r.date),
    chamber: "FEDERAL REGISTER",
    title: str(r.title) || "Federal Register action",
    summary: str(r.agency) || undefined,
    actionType: execTypeLabel(str(r.type)),
    amount: null,
    amountText: undefined,
    jurisdiction: "US",
    sourceUrl: ensureSource(r.source_url),
    href: `/executive?id=${execId}`,
  };
}
 
function toFeedBill(r: Record<string, unknown>): FeedItem {
  const billId = num(r.id);
  const number = str(r.number) || "Bill";
  const status = str(r.status);
  return {
    id: `bill:${r.id}`,
    kind: "bill",
    actionDate: str(r.updated_at),
    chamber: "CONGRESS",
    title: number,
    summary: str(r.title) || undefined,
    actionType: status ? status.toUpperCase() : "BILL",
    amount: null,
    amountText: undefined,
    jurisdiction: "US",
    sourceUrl: ensureSource(r.source_url),
    href: `/bill/${billId}`,
  };
}
 
function execTypeLabel(t: string): string {
  switch (t.toLowerCase()) {
    case "eo":
      return "EXECUTIVE ORDER";
    case "nprm":
      return "PROPOSED RULE";
    case "final_rule":
      return "FINAL RULE";
    case "notice":
      return "NOTICE";
    default:
      return t.toUpperCase() || "ACTION";
  }
}
 
 
export function getAwardsSummary(): { total: number; count: number } {
  return cached("awards:summary", () => {
    const row = safeGet<{ total: number; count: number }>(
      `SELECT COALESCE(SUM(amount), 0) AS total, COUNT(*) AS count FROM awards`
    );
    return { total: row?.total ?? 0, count: row?.count ?? 0 };
  });
}
 
export function getAwards(): Award[] {
  return safeAll<Award>(
    `SELECT id, agency, recipient, amount, action_date, program, source_url
     FROM awards
     ORDER BY amount DESC`,
    {},
    (r) => {
      const amount = num(r.amount);
      return {
        id: `award:${r.id}`,
        agency: str(r.agency),
        recipient: str(r.recipient),
        amount,
        amountText: formatUsd(amount),
        actionDate: str(r.action_date),
        program: str(r.program),
        sourceUrl: ensureSource(r.source_url),
        href: "/spending",
      };
    }
  );
}
 
export function getAwardsFiltered(q?: string, sort?: string): Award[] {
  const orderBy =
    sort === "date"   ? "action_date DESC, amount DESC" :
    sort === "agency" ? "agency ASC, amount DESC" :
    "amount DESC";
  const mapper = (r: Record<string, unknown>): Award => {
    const amount = num(r.amount);
    return {
      id: `award:${r.id}`,
      agency: str(r.agency),
      recipient: str(r.recipient),
      amount,
      amountText: formatUsd(amount),
      actionDate: str(r.action_date),
      program: str(r.program),
      sourceUrl: ensureSource(r.source_url),
      href: "/spending",
    };
  };
  if (!q || !q.trim()) {
    return safeAll<Award>(
      `SELECT id, agency, recipient, amount, action_date, program, source_url
       FROM awards
       ORDER BY ${orderBy}`,
      {},
      mapper
    );
  }
  const like = `%${q.trim().replace(/[%_\\]/g, (m) => `\\${m}`)}%`;
  return safeAll<Award>(
    `SELECT id, agency, recipient, amount, action_date, program, source_url
     FROM awards
     WHERE (agency LIKE :q ESCAPE '\\' OR recipient LIKE :q ESCAPE '\\' OR program LIKE :q ESCAPE '\\')
     ORDER BY ${orderBy}`,
    { q: like },
    mapper
  );
}
 
export function getFiscal(): FiscalMetric[] {
  return safeAll<FiscalMetric>(
    `SELECT rowid, metric, date, value, source_url FROM fiscal ORDER BY date DESC`,
    {},
    (r) => {
      const value = num(r.value);
      return {
        id: `fiscal:${r.rowid}`,
        metric: str(r.metric),
        value,
        valueText: formatUsd(value, { compact: true }),
        date: str(r.date),
        sourceUrl: ensureSource(r.source_url),
        href: "/spending",
      };
    }
  );
}
 
export function getApportionments(): Apportionment[] {
  return safeAll<Apportionment>(
    `SELECT id, agency, account, appropriated, apportioned, status, date, source_url
     FROM apportionments
     ORDER BY date DESC`,
    {},
    (r) => ({
      id: `appr:${r.id ?? r.rowid}`,
      agency: str(r.agency),
      account: str(r.account),
      appropriated: nullableNum(r.appropriated),
      apportioned: nullableNum(r.apportioned),
      status: str(r.status) || "released",
      date: str(r.date),
      sourceUrl: ensureSource(r.source_url),
      href: "/spending",
    })
  );
}
 
 
export function getVoteCount(chamber?: string): number {
  if (chamber) {
    const row = safeGet<{ c: number }>(
      `SELECT COUNT(*) AS c FROM votes WHERE LOWER(chamber) = :ch`,
      { ch: chamber.toLowerCase() }
    );
    return row?.c ?? 0;
  }
  return safeCount("votes");
}
 
export type VoteFilter = {
  chamber?: string;
  q?: string;
  resultFilter?: "passed" | "failed";
  sort?: "newest" | "chamber" | "result";
};
 
 
function buildVotesWhere(filter: VoteFilter): { where: string; params: Record<string, unknown> } {
  const clauses: string[] = [];
  const params: Record<string, unknown> = {};
  if (filter.chamber) {
    clauses.push("LOWER(v.chamber) = :ch");
    params.ch = filter.chamber.toLowerCase();
  }
  if (filter.q) {
    const like = `%${filter.q.trim().replace(/[%_\\]/g, (m) => `\\${m}`)}%`;
    clauses.push("(v.result LIKE :q ESCAPE '\\' OR v.question LIKE :q ESCAPE '\\')");
    params.q = like;
  }
  if (filter.resultFilter === "passed") {
    clauses.push("v.yea > v.nay");
  } else if (filter.resultFilter === "failed") {
    clauses.push("v.nay >= v.yea AND (v.yea + v.nay) > 0");
  }
  const where = clauses.length ? " WHERE " + clauses.join(" AND ") : "";
  return { where, params };
}
 
function buildVotesOrder(sort?: VoteFilter["sort"]): string {
  switch (sort) {
    case "chamber": return "v.chamber, v.date DESC, v.id DESC";
    case "result":  return "v.result, v.date DESC, v.id DESC";
    default:        return "v.date DESC, v.id DESC";
  }
}
 
export function getVoteCountFiltered(filter: VoteFilter = {}): number {
  const { where, params } = buildVotesWhere(filter);
  const row = safeGet<{ c: number }>(
    `SELECT COUNT(*) AS c FROM votes v${where}`,
    params
  );
  return row?.c ?? 0;
}
 
export function getVotes(limit = 100, offset = 0, chamber?: string): Vote[] {
  return getVotesFiltered(chamber ? { chamber } : {}, limit, offset);
}
 
 
export function getVotesFiltered(filter: VoteFilter = {}, limit = 100, offset = 0): Vote[] {
  const { where, params } = buildVotesWhere(filter);
  const orderBy = buildVotesOrder(filter.sort);
  return safeAll<Vote>(
    `SELECT v.id, v.bill_id, v.bill_text_ref, v.chamber, v.date,
            v.yea, v.nay, v.present, v.result, v.source_url, v.archive_url,
            b.number AS bill_number, b.title AS bill_title
     FROM votes v
     LEFT JOIN bills b ON b.id = v.bill_id${where}
     ORDER BY ${orderBy}
     LIMIT :limit OFFSET :offset`,
    { ...params, limit, offset },
    (r) => toVote(r)
  );
}
 
 
export function getVoteById(id: number): VoteDetail | null {
  const r = safeGet<Record<string, unknown>>(
    `SELECT v.id, v.bill_id, v.bill_text_ref, v.chamber, v.date,
            v.yea, v.nay, v.present, v.not_voting, v.roll_number,
            v.question, v.recorded, v.result, v.source_url, v.archive_url,
            b.number AS bill_number, b.title AS bill_title
     FROM votes v
     LEFT JOIN bills b ON b.id = v.bill_id
     WHERE v.id = :id`,
    { id }
  );
  if (!r) return null;
 
  const yea = num(r.yea);
  const nay = num(r.nay);
  const recorded =
    (r.recorded != null ? !!num(r.recorded) : true) && yea + nay > 0;
 
  const positions: VotePosition[] = recorded
    ? safeAll<VotePosition>(
        `SELECT vp.position, m.full_name, m.party, m.state, m.district, m.photo_url, m.id
         FROM vote_positions vp
         LEFT JOIN members m ON m.id = vp.member_id
         WHERE vp.vote_id = :id`,
        { id },
        (p) => ({
          member: str(p.full_name) || str(p.member_bioguide) || "Member",
          party: str(p.party),
          partyCode: partyCodeFromWord(str(p.party)),
          state: str(p.state),
          district: nullableNum(p.district),
          position: str(p.position) || "Not Voting",
          photoUrl: str(p.photo_url),
          memberHref: p.id ? `/members/${num(p.id)}` : "",
        })
      )
    : [];
 
  return {
    id: `vote:${r.id}`,
    billId: nullableNum(r.bill_id),
    billNumber: str(r.bill_number) || undefined,
    billTitle: str(r.bill_title) || undefined,
    billTextRef: str(r.bill_text_ref) || undefined,
    chamber: str(r.chamber).toUpperCase() || "SENATE",
    date: str(r.date),
    result: str(r.result) || (yea > nay ? "Passed" : "Failed"),
    yea,
    nay,
    present: num(r.present),
    recorded,
    sourceUrl: ensureSource(r.source_url),
    href: undefined,
    archiveUrl: str(r.archive_url) || undefined,
    question: str(r.question) || undefined,
    rollNumber: nullableNum(r.roll_number) ?? undefined,
    notVoting: nullableNum(r.not_voting) ?? undefined,
    positions,
  };
}
 
function toVote(r: Record<string, unknown>): Vote {
  const yea = num(r.yea);
  const nay = num(r.nay);
  const recorded =
    (r.recorded != null ? !!num(r.recorded) : true) && yea + nay > 0;
  const voteId = num(r.id);
  return {
    id: `vote:${r.id}`,
    billId: nullableNum(r.bill_id),
    billNumber: str(r.bill_number) || undefined,
    billTitle: str(r.bill_title) || undefined,
    billTextRef: str(r.bill_text_ref) || undefined,
    chamber: str(r.chamber).toUpperCase() || "SENATE",
    date: str(r.date),
    result: str(r.result) || (yea > nay ? "Passed" : "Failed"),
    yea,
    nay,
    present: num(r.present),
    recorded,
    sourceUrl: ensureSource(r.source_url),
    href: `/votes/${voteId}`,
    archiveUrl: str(r.archive_url) || undefined,
  };
}
 
 
export function getExecutive(): ExecutiveAction[] {
  return safeAll<ExecutiveAction>(
    `SELECT id, type, title, agency, fr_doc_number, date, source_url
     FROM executive_actions
     ORDER BY date DESC`,
    {},
    (r) => ({
      id: `exec:${r.id}`,
      type: str(r.type),
      title: str(r.title),
      agency: str(r.agency),
      frDocNumber: str(r.fr_doc_number),
      date: str(r.date),
      sourceUrl: ensureSource(r.source_url),
      href: undefined,
    })
  );
}
 
 
export type BillListItem = {
  id: string;
  number: string;
  title: string;
  congress: number;
  policyArea: string;
  status: string;
  updatedAt: string;
  sourceUrl: string;
  href: string;
};
 
export type BillFilter = {
  q?: string;
  state?: string;
  status?: string;
  congress?: number;
  policyArea?: string;
  sort?: "updated" | "action" | "congress" | "status";
};
 
 
function buildBillsWhere(filter: BillFilter): { where: string; params: Record<string, unknown> } {
  const clauses: string[] = [];
  const params: Record<string, unknown> = {};
 
  if (filter.q) {
    const like = `%${filter.q.trim().replace(/[%_\\]/g, (m) => "\\" + m)}%`;
    clauses.push(
      "(b.number LIKE :q ESCAPE '\\' OR b.title LIKE :q ESCAPE '\\' OR b.policy_area LIKE :q ESCAPE '\\')"
    );
    params.q = like;
  }
  if (filter.congress) {
    clauses.push("b.congress = :congress");
    params.congress = filter.congress;
  }
  if (filter.policyArea) {
    clauses.push("b.policy_area = :policyArea");
    params.policyArea = filter.policyArea;
  }
  if (filter.status) {
    clauses.push("b.status LIKE :status ESCAPE '\\'");
    params.status = `%${filter.status.replace(/[%_\\]/g, (m) => "\\" + m)}%`;
  }
  if (filter.state) {
    params.sponsorState = filter.state.toUpperCase();
  }
 
  const hasState = !!filter.state;
  const from = hasState
    ? "bills b JOIN bills_ext e ON e.bill_id = b.id"
    : "bills b";
  if (hasState) {
    clauses.push("UPPER(e.sponsor_state) = :sponsorState");
  }
 
  const where = clauses.length ? " WHERE " + clauses.join(" AND ") : "";
  return { where: `FROM ${from}${where}`, params };
}
 
export function getBillsCount(filter: BillFilter = {}): number {
  const { where, params } = buildBillsWhere(filter);
  const row = safeGet<{ c: number }>(
    `SELECT COUNT(*) AS c ${where}`,
    params
  );
  return row?.c ?? 0;
}
 
export function getBills(filter: BillFilter = {}, limit = 100, offset = 0): BillListItem[] {
  const { where, params } = buildBillsWhere(filter);
  const orderBy =
    filter.sort === "congress" ? "b.congress DESC, b.updated_at DESC" :
    filter.sort === "status"   ? "b.status ASC, b.updated_at DESC" :
    "b.updated_at DESC";
  return safeAll<BillListItem>(
    `SELECT b.id, b.congress, b.number, b.title, b.status, b.policy_area,
            b.updated_at, b.source_url
     ${where}
     ORDER BY ${orderBy}
     LIMIT :limit OFFSET :offset`,
    { ...params, limit, offset },
    (r) => ({
      id: `bill:${r.id}`,
      number: str(r.number),
      title: str(r.title),
      congress: num(r.congress),
      policyArea: str(r.policy_area),
      status: str(r.status),
      updatedAt: str(r.updated_at),
      sourceUrl: ensureSource(r.source_url),
      href: `/bill/${r.id}`,
    })
  );
}
 
export function getBillPolicyAreas(): string[] {
  const rows = safeAll<{ policy_area: string }>(
    `SELECT DISTINCT policy_area FROM bills
     WHERE policy_area IS NOT NULL AND policy_area != ''
     ORDER BY policy_area`,
    {},
    (r) => ({ policy_area: str(r.policy_area) })
  );
  return rows.map((r) => r.policy_area);
}
 
export function getBillCongresses(): number[] {
  return cached("bills:congresses", () =>
    safeAll<{ congress: number }>(
      `SELECT DISTINCT congress FROM bills WHERE congress IS NOT NULL ORDER BY congress DESC`,
      {},
      (r) => ({ congress: num(r.congress) })
    ).map((r) => r.congress)
  );
}
 
 
export type LawListItem = {
  id: string;
  billHref: string;
  billNumber: string;
  plNumber: string;
  plCongress: number;
  plSequence: number;
  title: string;
  congress: number;
  status: string;
  summary: string;
  summarySource: string;
  sourceUrl: string;
  pdfUrl: string;
  archivePdfUrl: string;
};
 
function parsePlNumber(status: string): { plNumber: string; congress: number; seq: number } {
  const m = /Public Law No:\s*(\d+)\s*-\s*(\d+)/i.exec(status || "");
  if (!m) return { plNumber: "", congress: 0, seq: 0 };
  return { plNumber: `${m[1]}-${m[2]}`, congress: parseInt(m[1], 10), seq: parseInt(m[2], 10) };
}
 
export function getPrivateLawsCount(): number {
  const row = safeGet<{ c: number }>(
    `SELECT COUNT(*) AS c FROM bills WHERE status LIKE 'Became Private Law No:%'`
  );
  return row?.c ?? 0;
}
 
 
function getPublicLawRows(q?: string): LawListItem[] {
  const params: Record<string, unknown> = {};
  let where = "WHERE b.status LIKE 'Became Public Law No:%'";
  if (q && q.trim()) {
    const like = `%${q.trim().replace(/[%_\\]/g, (m) => `\\${m}`)}%`;
    where +=
      " AND (b.number LIKE :q ESCAPE '\\' OR b.title LIKE :q ESCAPE '\\' OR b.status LIKE :q ESCAPE '\\')";
    params.q = like;
  }
  const rows = safeAll<Record<string, unknown>>(
    `SELECT b.id, b.congress, b.number, b.title, b.status, b.source_url,
            b.summary, b.summary_source, b.pdf_url, b.archive_pdf_url
     FROM bills b ${where}`,
    params
  );
  return rows.map((r) => {
    const status = str(r.status);
    const parsed = parsePlNumber(status);
    return {
      id: `bill:${r.id}`,
      billHref: `/bill/${r.id}`,
      billNumber: str(r.number),
      plNumber: parsed.plNumber,
      plCongress: parsed.congress || num(r.congress),
      plSequence: parsed.seq,
      title: str(r.title),
      congress: num(r.congress),
      status,
      summary: str(r.summary),
      summarySource: str(r.summary_source),
      sourceUrl: ensureSource(r.source_url),
      pdfUrl: str(r.pdf_url),
      archivePdfUrl: str(r.archive_pdf_url),
    };
  });
}
 
function sortLaws(items: LawListItem[], sort?: "recent" | "oldest" | "number"): LawListItem[] {
  const byRecent = (a: LawListItem, b: LawListItem) =>
    b.plCongress - a.plCongress || b.plSequence - a.plSequence;
  switch (sort) {
    case "oldest":
      return [...items].sort((a, b) => -byRecent(a, b));
    case "number":
      return [...items].sort(
        (a, b) => b.plCongress - a.plCongress || a.plSequence - b.plSequence
      );
    default:
      return [...items].sort(byRecent);
  }
}
 
export type LawSort = "recent" | "oldest" | "number";
 
export function getPublicLaws(
  opts: { q?: string; sort?: LawSort } = {},
  limit = 200,
  offset = 0
): LawListItem[] {
  const sorted = sortLaws(getPublicLawRows(opts.q), opts.sort);
  return sorted.slice(offset, offset + limit);
}
 
export function getPublicLawsCount(q?: string): number {
  return getPublicLawRows(q).length;
}
 
export function getBillDetail(idNum: number): BillDetail | null {
  const r = safeGet<Record<string, unknown>>(
    `SELECT b.id, b.congress, b.number, b.title, b.status, b.policy_area,
            b.updated_at, b.source_url, b.sponsor_id,
            b.summary, b.summary_source, b.pdf_url, b.archive_pdf_url,
            (SELECT e.sponsor_name FROM bills_ext e WHERE e.bill_id = b.id) AS sponsor_name
     FROM bills b WHERE b.id = :id`,
    { id: idNum }
  );
  if (!r) return null;
 
  const sponsor = getMemberSummaryByBioguide(str(r.sponsor_id));
 
  const versions = safeAll<BillVersion>(
    `SELECT version_code, version_label, version_date, source_url
     FROM bill_versions WHERE bill_id = :id
     ORDER BY version_date DESC`,
    { id: idNum },
    (v) => ({
      id: `bv:${idNum}:${str(v.version_code)}`,
      versionCode: str(v.version_code),
      versionLabel: str(v.version_label),
      versionDate: str(v.version_date),
      sourceUrl: ensureSource(v.source_url),
    })
  );
 
  const amendments = safeAll<BillAmendment>(
    `SELECT id, congress, type, number, bill_id, sponsor_id, status, agreed_method, source_url
     FROM amendments WHERE bill_id = :id
     ORDER BY number DESC`,
    { id: idNum },
    (a) => ({
      id: `amdt:${a.id}`,
      number: str(a.number),
      type: str(a.type),
      status: str(a.status),
      agreedMethod: str(a.agreed_method),
      billId: nullableNum(a.bill_id),
      sponsor: getMemberSummaryByBioguide(str(a.sponsor_id)),
      sourceUrl: ensureSource(a.source_url),
      href: ensureSource(a.source_url),
    })
  );
 
  const diffs = safeAll<BillDiffChange>(
    `SELECT section_id, change, snippet, rider_flag, source_url,
            from_version, to_version
     FROM bill_diffs WHERE bill_id = :id
     ORDER BY rider_flag DESC, section_id`,
    { id: idNum },
    (d) => ({
      sectionId: str(d.section_id),
      change: str(d.change),
      snippet: str(d.snippet),
      riderFlag: !!num(d.rider_flag),
      sourceUrl: ensureSource(d.source_url),
      fromVersion: str(d.from_version),
      toVersion: str(d.to_version),
    })
  );
 
  const timeline = safeAll<BillTimelineEntry>(
    `SELECT action_date, chamber, action_type, text, source_url
     FROM actions WHERE bill_id = :id
     ORDER BY action_date DESC, id DESC`,
    { id: idNum },
    (t) => ({
      date: str(t.action_date),
      chamber: str(t.chamber),
      actionType: str(t.action_type),
      text: str(t.text),
      sourceUrl: ensureSource(t.source_url),
    })
  );
 
  return {
    id: `bill:${r.id}`,
    number: str(r.number),
    congress: num(r.congress),
    title: str(r.title),
    status: str(r.status),
    policyArea: str(r.policy_area),
    updatedAt: str(r.updated_at),
    sourceUrl: ensureSource(r.source_url),
    href: `/bill/${r.id}`,
    sponsor,
    sponsorDisplay: str(r.sponsor_name),
    versions,
    amendments,
    diffs,
    votes: [],
    timeline,
    subjects: getBillSubjects(idNum),
    fullTextUrl: ensureSource(r.source_url),
    summaryPlain: str(r.summary),
    summary: str(r.summary),
    summarySource: str(r.summary_source),
    pdfUrl: str(r.pdf_url),
    archivePdfUrl: str(r.archive_pdf_url),
  };
}
 
 
export function getBillDiff(
  billId: number,
  fromV: string,
  toV: string
): BillDiff {
  const hasVersionFilter = !!fromV && !!toV;
  const changes = safeAll<BillDiffChange>(
    hasVersionFilter
      ? `SELECT section_id, change, snippet, rider_flag, source_url,
                from_version, to_version
         FROM bill_diffs
         WHERE bill_id = :billId
           AND from_version = :fromV AND to_version = :toV
         ORDER BY rider_flag DESC, section_id`
      : `SELECT section_id, change, snippet, rider_flag, source_url,
                from_version, to_version
         FROM bill_diffs
         WHERE bill_id = :billId
         ORDER BY rider_flag DESC, section_id`,
    hasVersionFilter ? { billId, fromV, toV } : { billId },
    (c) => ({
      sectionId: str(c.section_id),
      change: str(c.change),
      snippet: str(c.snippet),
      riderFlag: !!num(c.rider_flag),
      sourceUrl: ensureSource(c.source_url),
      fromVersion: str(c.from_version),
      toVersion: str(c.to_version),
    })
  );
  return {
    from: fromV,
    to: toV,
    changes,
    note: undefined,
  };
}
 
 
export type MemberFilter = {
  q?: string;
  state?: string;
  chamber?: string;
  party?: PartyCode;
};
 
function getAllMembersRaw(): MemberSummary[] {
  return cached("members:all", () =>
    safeAll<MemberSummary>(
      `SELECT id, bioguide_id, full_name, party, state, district, chamber, photo_url, source_url, is_current
       FROM members
       ORDER BY chamber, state, full_name`,
      {},
      (r) => toMemberSummary(r)
    )
  );
}
 
 
export function getMembersDefault(): MemberSummary[] {
  return cached("members:current", () => {
    const all = getAllMembersRaw();
    return all.filter((m) => m.isCurrent);
  });
}
 
 
export function getMemberCounts(): {
  total: number;
  senate: number;
  house: number;
  currentTotal: number;
  currentSenate: number;
  currentHouse: number;
} {
  return cached("members:counts", () => {
    const total = safeCount("members");
    const senateRow = safeGet<{ c: number }>(
      `SELECT COUNT(*) AS c FROM members WHERE LOWER(chamber) = 'senate'`
    );
    const houseRow = safeGet<{ c: number }>(
      `SELECT COUNT(*) AS c FROM members WHERE LOWER(chamber) = 'house'`
    );
    const curTotalRow = safeGet<{ c: number }>(
      `SELECT COUNT(*) AS c FROM members WHERE is_current = 1`
    );
    const curSenateRow = safeGet<{ c: number }>(
      `SELECT COUNT(*) AS c FROM members WHERE is_current = 1 AND LOWER(chamber) = 'senate'`
    );
    const curHouseRow = safeGet<{ c: number }>(
      `SELECT COUNT(*) AS c FROM members WHERE is_current = 1 AND LOWER(chamber) = 'house'`
    );
    return {
      total,
      senate: senateRow?.c ?? 0,
      house: houseRow?.c ?? 0,
      currentTotal: curTotalRow?.c ?? 0,
      currentSenate: curSenateRow?.c ?? 0,
      currentHouse: curHouseRow?.c ?? 0,
    };
  });
}
 
export function getMembers(filter: MemberFilter = {}): MemberSummary[] {
  const items = getAllMembersRaw();
 
  if (!filter.q && !filter.state && !filter.chamber && !filter.party) {
    return items;
  }
 
  let out = items;
  const q = filter.q?.trim().toLowerCase();
  if (q) {
    out = out.filter(
      (m) =>
        m.name.toLowerCase().includes(q) ||
        m.bioguide.toLowerCase().includes(q) ||
        m.state.toLowerCase().includes(q)
    );
  }
  if (filter.state) {
    const st = filter.state.toUpperCase();
    out = out.filter((m) => m.state === st);
  }
  if (filter.chamber) {
    const ch = filter.chamber.toLowerCase();
    out = out.filter((m) => m.chamber.toLowerCase() === ch);
  }
  if (filter.party) {
    out = out.filter((m) => m.partyCode === filter.party);
  }
  return out;
}
 
export function getMemberStates(): string[] {
  return safeAll<{ state: string }>(
    `SELECT DISTINCT state FROM members WHERE state IS NOT NULL AND state != ''
     ORDER BY state`,
    {},
    (r) => ({ state: str(r.state) })
  ).map((r) => r.state);
}
 
export function getMember(rawId: string): MemberDetail | null {
  return cached(`member:${rawId}`, () => _getMemberImpl(rawId));
}
 
function _getMemberImpl(rawId: string): MemberDetail | null {
  const cleaned = rawId.replace(/^member:/, "");
  const numeric = parseInt(cleaned, 10);
  const byId = Number.isFinite(numeric)
    ? safeGet<Record<string, unknown>>(
        `SELECT id, bioguide_id, full_name, party, state, district, chamber, photo_url, source_url, is_current
         FROM members WHERE id = :id`,
        { id: numeric }
      )
    : null;
  const row =
    byId ??
    safeGet<Record<string, unknown>>(
      `SELECT id, bioguide_id, full_name, party, state, district, chamber, photo_url, source_url, is_current
       FROM members WHERE bioguide_id = :bg`,
      { bg: cleaned }
    );
  if (!row) return null;
 
  const summary = toMemberSummary(row);
  const bioguide = summary.bioguide;
 
  const votes = safeAll<MemberVote>(
    `SELECT v.id, v.date, v.chamber, v.question, v.result, v.yea, v.nay, v.source_url,
            vp.position
     FROM vote_positions vp
     JOIN votes v ON v.id = vp.vote_id
     WHERE vp.member_id = :mid
     ORDER BY v.date DESC, v.id DESC
     LIMIT 40`,
    { mid: summary.numericId },
    (v) => ({
      voteId: `vote:${v.id}`,
      voteHref: `/votes/${num(v.id)}`,
      date: str(v.date),
      chamber: str(v.chamber).toUpperCase() || "SENATE",
      question: str(v.question) || undefined,
      result: str(v.result),
      yea: num(v.yea),
      nay: num(v.nay),
      position: str(v.position) || "Not Voting",
      sourceUrl: ensureSource(v.source_url),
    })
  );
 
  const sponsorships = safeAll<MemberSponsorship>(
    `SELECT id, number, title, congress, status, updated_at, source_url
     FROM bills WHERE sponsor_id = :bg
     ORDER BY updated_at DESC`,
    { bg: bioguide },
    (b) => ({
      billId: `bill:${b.id}`,
      billHref: `/bill/${b.id}`,
      number: str(b.number),
      title: str(b.title),
      congress: num(b.congress),
      status: str(b.status),
      updatedAt: str(b.updated_at),
      sourceUrl: ensureSource(b.source_url),
    })
  );
 
  const amendments = safeAll<MemberAmendment>(
    `SELECT id, number, type, status, bill_id, source_url
     FROM amendments WHERE sponsor_id = :bg
     ORDER BY id DESC`,
    { bg: bioguide },
    (a) => ({
      amendmentId: `amdt:${a.id}`,
      amendmentHref: ensureSource(a.source_url),
      number: str(a.number),
      type: str(a.type),
      status: str(a.status),
      billId: nullableNum(a.bill_id),
      billHref: a.bill_id ? `/bill/${a.bill_id}` : null,
      sourceUrl: ensureSource(a.source_url),
    })
  );
 
  const voteCount = safeGet<{ c: number }>(
    `SELECT COUNT(*) AS c FROM vote_positions WHERE member_id = :mid`,
    { mid: summary.numericId }
  )?.c ?? votes.length;
 
  const votesByYear = safeAll<MemberYearGroup>(
    `SELECT v.year, COUNT(*) AS count
     FROM vote_positions vp
     JOIN votes v ON v.id = vp.vote_id
     WHERE vp.member_id = :mid
     GROUP BY v.year
     ORDER BY v.year IS NULL ASC, v.year DESC`,
    { mid: summary.numericId },
    (r) => ({ year: nullableNum(r.year), count: num(r.count) })
  );
 
  return {
    ...summary,
    votes,
    sponsorships,
    amendments,
    voteCount,
    votesByYear,
  };
}
 
 
export function getMemberVotesForYear(
  bioguide: string,
  year: number | null,
  limit = 600
): MemberVote[] {
  const cacheKey = `member-votes:${bioguide}:${year ?? "null"}`;
  return cached(cacheKey, () => {
    const memberRow = safeGet<{ id: number }>(
      `SELECT id FROM members WHERE bioguide_id = :bg`,
      { bg: bioguide }
    );
    if (!memberRow) return [];
    const mid = memberRow.id;
    const yearClause = year == null ? `v.year IS NULL` : `v.year = :year`;
    const params: Record<string, unknown> = year == null
      ? { mid, lim: limit }
      : { mid, year, lim: limit };
    return safeAll<MemberVote>(
      `SELECT v.id, v.date, v.chamber, v.question, v.result, v.yea, v.nay,
              v.source_url, vp.position
       FROM vote_positions vp
       JOIN votes v ON v.id = vp.vote_id
       WHERE vp.member_id = :mid AND ${yearClause}
       ORDER BY v.date DESC, v.id DESC
       LIMIT :lim`,
      params,
      (v) => ({
        voteId: `vote:${num(v.id)}`,
        voteHref: `/votes/${num(v.id)}`,
        date: str(v.date),
        chamber: str(v.chamber).toUpperCase() || "SENATE",
        question: str(v.question) || undefined,
        result: str(v.result),
        yea: num(v.yea),
        nay: num(v.nay),
        position: str(v.position) || "Not Voting",
        sourceUrl: ensureSource(v.source_url),
      })
    );
  });
}
 
function toMemberSummary(r: Record<string, unknown>): MemberSummary {
  const idNum = num(r.id);
  return {
    id: `member:${idNum}`,
    numericId: idNum,
    bioguide: str(r.bioguide_id),
    name: str(r.full_name),
    party: str(r.party),
    partyCode: partyCodeFromWord(str(r.party)),
    state: str(r.state),
    district: nullableNum(r.district),
    chamber: str(r.chamber).toLowerCase() || "house",
    photoUrl: str(r.photo_url),
    sourceUrl: ensureSource(r.source_url),
    href: `/members/${idNum}`,
    isCurrent: num(r.is_current) === 1,
  };
}
 
function getMemberSummaryByBioguide(bioguide: string): MemberSummary | null {
  if (!bioguide) return null;
  const r = safeGet<Record<string, unknown>>(
    `SELECT id, bioguide_id, full_name, party, state, district, chamber, photo_url, source_url, is_current
     FROM members WHERE bioguide_id = :bg`,
    { bg: bioguide }
  );
  return r ? toMemberSummary(r) : null;
}
 
 
const STATE_NAMES: Record<string, string> = {
  AL: "Alabama", AK: "Alaska", AZ: "Arizona", AR: "Arkansas", CA: "California",
  CO: "Colorado", CT: "Connecticut", DE: "Delaware", FL: "Florida", GA: "Georgia",
  HI: "Hawaii", ID: "Idaho", IL: "Illinois", IN: "Indiana", IA: "Iowa",
  KS: "Kansas", KY: "Kentucky", LA: "Louisiana", ME: "Maine", MD: "Maryland",
  MA: "Massachusetts", MI: "Michigan", MN: "Minnesota", MS: "Mississippi",
  MO: "Missouri", MT: "Montana", NE: "Nebraska", NV: "Nevada", NH: "New Hampshire",
  NJ: "New Jersey", NM: "New Mexico", NY: "New York", NC: "North Carolina",
  ND: "North Dakota", OH: "Ohio", OK: "Oklahoma", OR: "Oregon", PA: "Pennsylvania",
  RI: "Rhode Island", SC: "South Carolina", SD: "South Dakota", TN: "Tennessee",
  TX: "Texas", UT: "Utah", VT: "Vermont", VA: "Virginia", WA: "Washington",
  WV: "West Virginia", WI: "Wisconsin", WY: "Wyoming",
  DC: "District of Columbia", PR: "Puerto Rico", AS: "American Samoa",
  GU: "Guam", MP: "Northern Mariana Islands", VI: "U.S. Virgin Islands",
};
 
function stateName(code: string): string {
  return STATE_NAMES[code] || code;
}
 
function rollupConfidence(values: string[]): "ok" | "stale" | "missing" {
  const set = new Set(values.map((v) => (v || "").toLowerCase()));
  if (set.size === 0 || set.has("missing")) return "missing";
  if (set.has("stale")) return "stale";
  return "ok";
}
 
export function getStates(): StateCoverage[] {
  const rows = safeAll<{
    state: string;
    confidence: string;
    data_type: string;
    last_scrape: string;
  }>(
    `SELECT state, confidence, data_type, last_scrape
     FROM state_coverage
     ORDER BY state`,
    {},
    (r) => ({
      state: str(r.state).toUpperCase(),
      confidence: str(r.confidence),
      data_type: str(r.data_type),
      last_scrape: str(r.last_scrape),
    })
  );
 
  const byState = new Map<
    string,
    {
      confidences: string[];
      missingTypes: string[];
      lastScrape: string;
    }
  >();
  for (const r of rows) {
    if (!r.state) continue;
    const entry = byState.get(r.state) ?? {
      confidences: [],
      missingTypes: [],
      lastScrape: "",
    };
    entry.confidences.push(r.confidence);
    if (
      (r.confidence || "").toLowerCase() === "missing" &&
      r.data_type
    ) {
      entry.missingTypes.push(r.data_type);
    }
    if (r.last_scrape > entry.lastScrape) entry.lastScrape = r.last_scrape;
    byState.set(r.state, entry);
  }
 
  const seen = new Set(byState.keys());
  for (const code of Object.keys(STATE_NAMES)) {
    if (!seen.has(code)) {
      byState.set(code, {
        confidences: ["missing"],
        missingTypes: ["bills", "votes", "sponsors"],
        lastScrape: "",
      });
    }
  }
 
  return Array.from(byState.entries())
    .map(([code, e]) => ({
      code,
      name: stateName(code),
      confidence: rollupConfidence(e.confidences),
      lastScrape: e.lastScrape,
      missingTypes: Array.from(new Set(e.missingTypes)),
    }))
    .sort((a, b) => a.code.localeCompare(b.code));
}
 
export function getStateDetail(code: string): StateDetail | null {
  const upper = (code || "").toUpperCase();
  if (!Object.prototype.hasOwnProperty.call(STATE_NAMES, upper)) return null;
  const states = getStates();
  const coverage =
    states.find((s) => s.code === upper) ?? {
      code: upper,
      name: stateName(upper),
      confidence: "missing" as const,
      lastScrape: "",
      missingTypes: ["bills", "votes", "sponsors"],
    };
 
  const members = getMembers({ state: upper }).filter((m) => m.isCurrent);
  const bills = safeAll<BillListItem>(
    `SELECT b.id, b.congress, b.number, b.title, b.status, b.policy_area,
            b.updated_at, b.source_url
     FROM bills b
     JOIN bills_ext e ON e.bill_id = b.id
     WHERE UPPER(e.sponsor_state) = :st
     ORDER BY b.updated_at DESC`,
    { st: upper },
    (r) => ({
      id: `bill:${r.id}`,
      number: str(r.number),
      title: str(r.title),
      congress: num(r.congress),
      policyArea: str(r.policy_area),
      status: str(r.status),
      updatedAt: str(r.updated_at),
      sourceUrl: ensureSource(r.source_url),
      href: `/bill/${r.id}`,
    })
  );
 
  return {
    ...coverage,
    members,
    bills,
    votes: [],
  };
}
 
 
export type SearchResults = {
  bills: BillListItem[];
  awards: Array<{
    id: string;
    agency: string;
    recipient: string;
    amount: number;
    sourceUrl: string;
  }>;
  members: MemberSummary[];
  executive: ExecutiveAction[];
  states: StateCoverage[];
};
 
export function globalSearch(q: string): SearchResults {
  const query = q.trim();
  if (!query) {
    return { bills: [], awards: [], members: [], executive: [], states: [] };
  }
  const like = `%${query.replace(/[%_]/g, (m) => "\\" + m)}%`;
 
  const bills = safeAll<BillListItem>(
    `SELECT id, congress, number, title, status, policy_area, updated_at, source_url
     FROM bills
     WHERE number LIKE :q ESCAPE '\\' OR title LIKE :q ESCAPE '\\'
            OR policy_area LIKE :q ESCAPE '\\'
     ORDER BY updated_at DESC`,
    { q: like },
    (r) => ({
      id: `bill:${r.id}`,
      number: str(r.number),
      title: str(r.title),
      congress: num(r.congress),
      policyArea: str(r.policy_area),
      status: str(r.status),
      updatedAt: str(r.updated_at),
      sourceUrl: ensureSource(r.source_url),
      href: `/bill/${r.id}`,
    })
  );
 
  const awards = safeAll(
    `SELECT id, agency, recipient, amount, source_url FROM awards
     WHERE agency LIKE :q ESCAPE '\\' OR recipient LIKE :q ESCAPE '\\'
     ORDER BY amount DESC`,
    { q: like },
    (r) => ({
      id: `award:${r.id}`,
      agency: str(r.agency),
      recipient: str(r.recipient),
      amount: num(r.amount),
      sourceUrl: ensureSource(r.source_url),
    })
  );
 
  const members = safeAll<MemberSummary>(
    `SELECT id, bioguide_id, full_name, party, state, district, chamber, photo_url, source_url, is_current
     FROM members
     WHERE full_name LIKE :q ESCAPE '\\' OR bioguide_id LIKE :q ESCAPE '\\'
            OR state LIKE :q ESCAPE '\\'
     ORDER BY full_name`,
    { q: like },
    (r) => toMemberSummary(r)
  );
 
  const executive = safeAll<ExecutiveAction>(
    `SELECT id, type, title, agency, fr_doc_number, date, source_url
     FROM executive_actions
     WHERE title LIKE :q ESCAPE '\\' OR agency LIKE :q ESCAPE '\\'
     ORDER BY date DESC`,
    { q: like },
    (r) => ({
      id: `exec:${r.id}`,
      type: str(r.type),
      title: str(r.title),
      agency: str(r.agency),
      frDocNumber: str(r.fr_doc_number),
      date: str(r.date),
      sourceUrl: ensureSource(r.source_url),
      href: undefined,
    })
  );
 
  const states = getStates().filter(
    (s) =>
      s.code.toLowerCase() === query.toLowerCase() ||
      s.name.toLowerCase().includes(query.toLowerCase())
  );
 
  return { bills, awards, members, executive, states };
}
 
 
export type CalendarKind =
  | "effective"
  | "enacted"
  | "vote"
  | "introduced"
  | "committee"
  | "floor"
  | "apportionment"
  | "effective_parsed";
 
export type CalendarEvent = {
  id: string;
  date: string;
  kind: CalendarKind;
  refType: string;
  refId: number | null;
  title: string;
  jurisdiction: string;
  sourceUrl: string;
  href: string | null;
  confidence: number;
};
 
 
export type CalendarDayKind = { date: string; kind: CalendarKind; count: number };
 
export function getCalendarDaySummary(from: string, to: string): CalendarDayKind[] {
  return safeAll<CalendarDayKind>(
    `SELECT date, kind, COUNT(*) AS count
     FROM calendar_events
     WHERE date >= :from AND date <= :to
     GROUP BY date, kind
     ORDER BY date ASC, kind ASC`,
    { from, to },
    (r) => ({
      date: str(r.date),
      kind: str(r.kind) as CalendarKind,
      count: num(r.count),
    })
  );
}
 
 
export function getCalendarEvents(opts: {
  from?: string;
  to?: string;
  kind?: string;
}): CalendarEvent[] {
  const where: string[] = [];
  const params: Record<string, unknown> = {};
  if (opts.from) {
    where.push("date >= :from");
    params.from = opts.from;
  }
  if (opts.to) {
    where.push("date <= :to");
    params.to = opts.to;
  }
  if (opts.kind) {
    where.push("kind = :kind");
    params.kind = opts.kind;
  }
  const sql = `SELECT id, date, kind, ref_type, ref_id, title, jurisdiction, source_url, confidence
               FROM calendar_events${where.length ? " WHERE " + where.join(" AND ") : ""}
               ORDER BY date ASC, kind ASC`;
  return safeAll<CalendarEvent>(sql, params, (r) => {
    const refId = nullableNum(r.ref_id);
    return {
      id: `cal:${r.id}`,
      date: str(r.date),
      kind: str(r.kind) as CalendarKind,
      refType: str(r.ref_type),
      refId,
      title: str(r.title),
      jurisdiction: str(r.jurisdiction) || "US",
      sourceUrl: ensureSource(r.source_url),
      href: calendarHref(str(r.ref_type), refId),
      confidence: nullableNum(r.confidence) ?? 1,
    };
  });
}
 
function calendarHref(refType: string, refId: number | null): string | null {
  if (refId == null) return null;
  switch (refType) {
    case "bill":
      return `/bill/${refId}`;
    case "vote":
      return `/votes/${refId}`;
    case "executive_action":
      return "/executive";
    case "apportionment":
      return "/spending";
    default:
      return null;
  }
}
 
 
export type MeetingType =
  | "hearing"
  | "markup"
  | "business_meeting"
  | "floor"
  | string;
 
export type Meeting = {
  id: string;
  chamber: string;
  committee: string;
  date: string;
  time: string;
  datetimeIso: string;
  title: string;
  subject: string;
  type: MeetingType;
  location: string;
  status: string;
  sourceUrl: string;
};
 
export type MeetingFilter = {
  from?: string;
  to?: string;
  chamber?: string;
  committee?: string;
  type?: string;
};
 
 
export function getMeetings(filter: MeetingFilter = {}): Meeting[] {
  const where: string[] = [];
  const params: Record<string, unknown> = {};
  if (filter.from) {
    where.push("date >= :from");
    params.from = filter.from;
  }
  if (filter.to) {
    where.push("date <= :to");
    params.to = filter.to;
  }
  if (filter.chamber) {
    where.push("LOWER(chamber) = :chamber");
    params.chamber = filter.chamber.toLowerCase();
  }
  if (filter.committee) {
    where.push("committee LIKE :committee ESCAPE '\\'");
    params.committee = `%${filter.committee.replace(/[%_\\]/g, (m) => "\\" + m)}%`;
  }
  if (filter.type) {
    where.push("LOWER(type) = :type");
    params.type = filter.type.toLowerCase();
  }
  const sql = `SELECT id, chamber, committee, date, time, datetime_iso, title,
                      subject, type, location, status, source_url
               FROM meetings${where.length ? " WHERE " + where.join(" AND ") : ""}
               ORDER BY datetime_iso DESC, date DESC, id DESC`;
  return safeAll<Meeting>(sql, params, (r) => ({
    id: `meeting:${r.id}`,
    chamber: str(r.chamber),
    committee: str(r.committee),
    date: str(r.date),
    time: str(r.time),
    datetimeIso: str(r.datetime_iso),
    title: str(r.title),
    subject: str(r.subject),
    type: str(r.type) as MeetingType,
    location: str(r.location),
    status: str(r.status),
    sourceUrl: ensureSource(r.source_url),
  }));
}
 
export function getMeetingCommittees(): string[] {
  return safeAll<{ committee: string }>(
    `SELECT DISTINCT committee FROM meetings
     WHERE committee IS NOT NULL AND committee != ''
     ORDER BY committee`,
    {},
    (r) => ({ committee: str(r.committee) })
  ).map((r) => r.committee);
}
 
 
 
export type CategorySummary = {
  slug: string;
  label: string;
  kind: string;
  billCount: number;
  awardCount: number;
  total: number;
};
 
 
export function getCategories(): CategorySummary[] {
  return cached("categories:all", () =>
    safeAll<CategorySummary>(
      `SELECT c.slug, c.label, c.kind,
              (SELECT COUNT(*) FROM bills b WHERE b.policy_area = c.label) AS bill_count,
              (SELECT COUNT(*) FROM awards a WHERE a.category = c.label) AS award_count
       FROM categories c
       ORDER BY c.kind, c.label`,
      {},
      (r) => {
        const billCount = num(r.bill_count);
        const awardCount = num(r.award_count);
        return {
          slug: str(r.slug),
          label: str(r.label),
          kind: str(r.kind),
          billCount,
          awardCount,
          total: billCount + awardCount,
        };
      }
    )
  );
}
 
export function getPolicyAreaLabels(): string[] {
  return cached("bills:policyareas", () =>
    safeAll<{ label: string }>(
      `SELECT DISTINCT policy_area AS label FROM bills
       WHERE policy_area IS NOT NULL AND policy_area != ''
       ORDER BY policy_area`,
      {},
      (r) => ({ label: str(r.label) })
    ).map((r) => r.label)
  );
}
 
export function getBillSubjects(billId: number, max = 8): string[] {
  return safeAll<{ subject: string }>(
    `SELECT subject FROM bill_subjects
     WHERE bill_id = :id AND subject IS NOT NULL AND subject != ''
     ORDER BY subject
     LIMIT :max`,
    { id: billId, max },
    (r) => ({ subject: str(r.subject) })
  ).map((r) => r.subject);
}
 
 
export type ChangeKind = "added" | "updated" | "removed";
 
export type ChangeEvent = {
  id: string;
  ts: string;
  source: string;
  refType: string;
  refId: string;
  change: ChangeKind;
  summary: string;
  sourceUrl: string;
  href: string | null;
};
 
export function getChangeEvents(limit = 25): ChangeEvent[] {
  return safeAll<ChangeEvent>(
    `SELECT id, ts, source, ref_type, ref_id, change, summary, source_url
     FROM change_events
     ORDER BY ts DESC, id DESC
     LIMIT :limit`,
    { limit },
    (r) => {
      const refType = str(r.ref_type);
      const refId = str(r.ref_id);
      return {
        id: `chg:${r.id}`,
        ts: str(r.ts),
        source: str(r.source),
        refType,
        refId,
        change: str(r.change) as ChangeKind,
        summary: str(r.summary),
        sourceUrl: ensureSource(r.source_url),
        href: changeHref(refType, refId),
      };
    }
  );
}
 
function changeHref(refType: string, refId: string): string | null {
  switch (refType) {
    case "member":
      return refId ? `/members/${refId}` : null;
    case "bill": {
      const n = parseInt(refId, 10);
      return Number.isFinite(n) ? `/bill/${n}` : null;
    }
    case "vote": {
      const n = parseInt(refId, 10);
      return Number.isFinite(n) ? `/votes/${n}` : null;
    }
    case "executive_action":
      return "/executive";
    default:
      return null;
  }
}
 
 
function str(v: unknown): string {
  return v == null ? "" : String(v);
}
function num(v: unknown): number {
  const n = typeof v === "number" ? v : parseFloat(String(v ?? ""));
  return Number.isFinite(n) ? n : 0;
}
function nullableNum(v: unknown): number | null {
  if (v == null || v === "") return null;
  const n = typeof v === "number" ? v : parseFloat(String(v));
  return Number.isFinite(n) ? n : null;
}
 
 
function ensureSource(v: unknown): string {
  const s = str(v);
  return s || "https://www.usaspending.gov/";
}