src/lib/db/export-store.ts

Linked repository file from this build.

import { bindReviewRules } from "@/features/reviews/review-store";
import { and, asc, count, eq, inArray, sql } from "drizzle-orm";
import type { Actor } from "@/features/reviews/types";
import { assertExportRole, parseExportMonths } from "@/features/exports/month-export";
import type { exportMonthSummary } from "@/features/exports/month-export";
import { getDb } from "./index";
import { reviewIndex, reviewSnapshots } from "./schema";
import { reviewScope } from "./scope";

/** Count each month's reviews from the saved report projection. */
export async function queryExportMonths(
  actor: Actor,
): Promise<ReturnType<typeof exportMonthSummary>> {
  assertExportRole(actor);
  const eligible = sql`coalesce((${reviewIndex.report}->>'transmittalEligible')::boolean, false)`;
  const dropped = sql`NOT (${eligible}) AND (${reviewIndex.state} = 'dropped' OR ${reviewIndex.outcome} = 'drop')`;
  const awaitingRelease = sql`NOT (${eligible}) AND NOT (${dropped}) AND ${reviewIndex.state} = 'awaiting_cao_response'`;
  return getDb()
    .select({
      month: reviewIndex.sampleMonth,
      total: count(),
      eligible: sql<number>`count(*) filter (where ${eligible})`.mapWith(Number),
      incomplete:
        sql<number>`count(*) filter (where NOT (${eligible}) AND NOT (${dropped}) AND NOT (${awaitingRelease}))`.mapWith(
          Number,
        ),
      dropped: sql<number>`count(*) filter (where ${dropped})`.mapWith(Number),
      awaitingRelease: sql<number>`count(*) filter (where ${awaitingRelease})`.mapWith(Number),
    })
    .from(reviewIndex)
    .where(reviewScope(actor))
    .groupBy(reviewIndex.sampleMonth)
    .orderBy(asc(reviewIndex.sampleMonth));
}

/** Download only selected, scoped, released reviews and manager-approved drops; the export rechecks every gate. */
export async function exportReviews(actor: Actor, values: string[]) {
  assertExportRole(actor);
  const months = parseExportMonths(values);
  return (
    await getDb()
      .select({ review: reviewSnapshots.review })
      .from(reviewIndex)
      .innerJoin(
        reviewSnapshots,
        and(
          eq(reviewIndex.reviewKey, reviewSnapshots.reviewKey),
          eq(reviewIndex.version, reviewSnapshots.version),
        ),
      )
      .where(
        and(
          reviewScope(actor),
          inArray(reviewIndex.sampleMonth, months),
          inArray(reviewIndex.state, ["released_to_reporting", "dropped"]),
        ),
      )
  ).map((row) => bindReviewRules(row.review));
}