src/lib/db/report-store.ts

Linked repository file from this build.

import { and, eq, gte, lte, sql } from "drizzle-orm";
import type { Actor } from "@/features/reviews/types";
import {
  aggregateReport,
  countyLabel,
  examinerLabel,
  type ReportFilters,
  type ReportResponse,
} from "@/features/reports/report-data";
import { reportRow, type ReportInput } from "@/features/reports/report-input";
import { AccessError, can } from "@/lib/auth/scope";
import { getDb } from "./index";
import { reviewScope } from "./scope";
import { reviewIndex } from "./schema";

/** Read only the index columns a report needs. Snapshots are never loaded. */
export function reportInputs(actor: Actor, filters: ReportFilters = {}) {
  if (!can(actor, "report") && !can(actor, "export"))
    throw new AccessError("This role cannot load review summaries.");
  return getDb()
    .select({
      report: reviewIndex.report,
      sampleMonth: reviewIndex.sampleMonth,
      examiner: reviewIndex.examiner,
      county: reviewIndex.county,
      state: sql<ReportInput["state"]>`${reviewIndex.state}`,
    })
    .from(reviewIndex)
    .where(
      and(
        reviewScope(actor),
        filters.from ? gte(reviewIndex.sampleMonth, filters.from) : undefined,
        filters.through ? lte(reviewIndex.sampleMonth, filters.through) : undefined,
        filters.examiner ? eq(reviewIndex.examiner, filters.examiner) : undefined,
        filters.county ? eq(reviewIndex.county, filters.county) : undefined,
      ),
    );
}

export async function queryReport(
  actor: Actor,
  filters: ReportFilters = {},
): Promise<ReportResponse> {
  if (!can(actor, "report")) throw new AccessError("This role cannot load reports.");
  const [inputs, options] = await Promise.all([
    reportInputs(actor, filters),
    Object.values(filters).some(Boolean)
      ? getDb()
          .selectDistinct({ examiner: reviewIndex.examiner, county: reviewIndex.county })
          .from(reviewIndex)
          .where(reviewScope(actor))
      : undefined,
  ]);
  const choices = options ?? inputs;
  return {
    summary: aggregateReport(inputs.map(reportRow)),
    examiners: [...new Set(choices.map((row) => row.examiner))].sort((a, b) =>
      examinerLabel(a).localeCompare(examinerLabel(b)),
    ),
    counties: [...new Set(choices.map((row) => row.county))].sort((a, b) =>
      countyLabel(a).localeCompare(countyLabel(b)),
    ),
  };
}