POSTGRESQL REFERENCE · WORKING SOURCE

src/billing.ts

This file ships in the runnable source archive. Synthetic fixtures and demonstration policies are identified in the README.

import type { Actor, InvoiceLine, InvoiceResponse, InvoiceSummary, Row } from "./contracts.ts";
import { type DB, warehouseTx } from "./db.ts";
import { reservationAccess } from "./access.ts";
import { uid } from "./errors.ts";

export async function calendarDates(db: DB, start: string, end: string, timezone: string): Promise<string[]> {
  // Enumerate local DATEs and compare timezone-aware midnight instants. This handles
  // midnight release, 23/25-hour days, leap dates, and intervals shorter than 24h.
  const result = await db.query(
    `WITH days AS (SELECT d::date AS day FROM generate_series(($1::timestamptz AT TIME ZONE $3)::date::timestamp,($2::timestamptz AT TIME ZONE $3)::date::timestamp,interval '1 day') d) SELECT day::text AS day FROM days WHERE day::timestamp AT TIME ZONE $3 < $2::timestamptz AND (day+1)::timestamp AT TIME ZONE $3 > $1::timestamptz AND $1::timestamptz < $2::timestamptz ORDER BY day`,
    [start, end, timezone],
  );
  return result.rows.map((r) => r.day);
}
export async function rate(db: DB, id: string) {
  return (await db.query("SELECT * FROM rate_cards WHERE id=$1", [id])).rows[0];
}
export async function estimate(
  db: DB,
  count: number,
  start: string,
  end: string,
  timezone: string,
  tariff: Row,
) {
  const days = (await calendarDates(db, start, end, timezone)).length;
  const lines = [{
    kind: "storage",
    quantity: count * days,
    unitCents: tariff.storage_cents,
    totalCents: count * days * tariff.storage_cents,
  }, {
    kind: "receiving",
    quantity: count,
    unitCents: tariff.receiving_cents,
    totalCents: count * tariff.receiving_cents,
  }, {
    kind: "release",
    quantity: count,
    unitCents: tariff.release_cents,
    totalCents: count * tariff.release_cents,
  }];
  return { lines, totalCents: lines.reduce((sum, l) => sum + l.totalCents, 0), palletDays: count * days };
}
export async function handlingCharge(
  db: DB,
  r: Row,
  palletId: string,
  custodyId: string,
  kind: "receiving" | "release",
  movementId: string,
  tariff: Row,
) {
  const cents = kind === "receiving" ? tariff.receiving_cents : tariff.release_cents;
  await db.query(
    "INSERT INTO charge_lines(id,reservation_id,pallet_id,custody_id,kind,quantity,unit_cents,total_cents,rate_card_id,movement_ids) VALUES($1,$2,$3,$4,$5,1,$6,$6,$7,$8)",
    [uid("charge"), r.id, palletId, custodyId, kind, cents, r.rate_card_id, [movementId]],
  );
}
export async function storageCharges(db: DB, r: Row, p: Row, releaseMovementId: string, tariff: Row) {
  const receipt =
    (await db.query("SELECT id FROM movements WHERE custody_id=$1 AND kind='received'", [p.custody_id]))
      .rows[0];
  const days = await calendarDates(db, p.received_at, p.released_at, r.timezone);
  for (const day of days) {
    await db.query(
      "INSERT INTO charge_lines(id,reservation_id,pallet_id,custody_id,kind,service_date,quantity,unit_cents,total_cents,rate_card_id,movement_ids) VALUES($1,$2,$3,$4,'storage',$5,1,$6,$6,$7,$8)",
      [uid("charge"), r.id, p.id, p.custody_id, day, tariff.storage_cents, r.rate_card_id, [
        receipt.id,
        releaseMovementId,
      ]],
    );
  }
}
export async function invoice(db: DB, actor: Actor, id: string, clock: string): Promise<InvoiceResponse> {
  const r = await reservationAccess(db, actor, id), tariff = await rate(db, r.rate_card_id);
  const rows = (await db.query(
    "SELECT l.*,p.label AS pallet_label FROM charge_lines l LEFT JOIN pallets p ON p.id=l.pallet_id WHERE l.reservation_id=$1 ORDER BY l.kind,l.service_date,p.label,l.id",
    [id],
  )).rows;
  const lines: InvoiceLine[] = rows.map((l) => ({
    id: l.id,
    kind: l.kind,
    palletId: l.pallet_id,
    palletLabel: l.pallet_label,
    serviceDate: l.service_date,
    quantity: l.quantity,
    unitCents: l.unit_cents,
    totalCents: l.total_cents,
    movementIds: l.movement_ids,
    rateVersion: tariff.version,
    rateCardId: l.rate_card_id,
    provisional: false,
  }));
  const open = (await db.query(
    "SELECT p.*,c.id AS custody_id,c.received_at,m.id AS receipt_id FROM pallets p JOIN custodies c ON c.pallet_id=p.id JOIN movements m ON m.custody_id=c.id AND m.kind='received' WHERE p.reservation_id=$1 AND c.released_at IS NULL",
    [id],
  )).rows;
  for (const p of open) {
    for (const day of await calendarDates(db, p.received_at, clock, r.timezone)) {
      lines.push({
        id: `accrued-${p.custody_id}-${day}`,
        kind: "storage",
        palletId: p.id,
        palletLabel: p.label,
        serviceDate: day,
        quantity: 1,
        unitCents: tariff.storage_cents,
        totalCents: tariff.storage_cents,
        movementIds: [p.receipt_id],
        rateVersion: tariff.version,
        rateCardId: r.rate_card_id,
        provisional: true,
      });
    }
  }
  const summary: InvoiceSummary = {
    palletDays: 0,
    storageCents: 0,
    receivingCents: 0,
    releaseCents: 0,
    adjustmentCents: 0,
    totalCents: 0,
  };
  for (const l of lines) {
    if (l.kind === "storage") {
      summary.palletDays += l.quantity;
      summary.storageCents += l.totalCents;
    } else if (l.kind === "receiving") summary.receivingCents += l.totalCents;
    else if (l.kind === "release") summary.releaseCents += l.totalCents;
    else summary.adjustmentCents += l.totalCents;
    summary.totalCents += l.totalCents;
  }
  const events = (await db.query(
    "SELECT m.*,u.name AS actor_name FROM movements m JOIN users u ON u.id=m.actor_id WHERE m.reservation_id=$1 ORDER BY m.occurred_at,m.recorded_at,m.id",
    [id],
  )).rows.map((m) => ({
    id: m.id,
    kind: m.kind,
    palletId: m.pallet_id,
    occurredAt: m.occurred_at,
    recordedAt: m.recorded_at,
    actorName: m.actor_name,
    ownerOrgId: m.owner_org_id,
    fromPositionId: m.from_position_id,
    toPositionId: m.to_position_id,
    reason: m.reason,
    adjustmentOf: m.adjustment_of,
    data: m.data,
  }));
  const q = (await db.query("SELECT estimate_cents,terms FROM quotes WHERE id=$1", [r.quote_id])).rows[0];
  return {
    reservationId: id,
    customerName: r.customer_name,
    currency: tariff.currency,
    rateVersion: tariff.version,
    rateCardId: tariff.id,
    terms: q.terms,
    quoteEstimateCents: q.estimate_cents,
    currentEstimateCents: r.estimate_cents,
    asOf: clock,
    summary,
    lines,
    events,
  };
}
export async function getInvoice(actor: Actor, id: string) {
  const { pool } = await import("./db.ts");
  const db = await pool.connect();
  let r;
  try {
    r = await reservationAccess(db, actor, id);
  } finally {
    db.release();
  }
  return warehouseTx(actor, r.warehouse_id, (db, clock) => invoice(db, actor, id, clock));
}