Overdue invoice follow-up

import { schedule, workflow } from "@croft/sdk";

export default workflow("overdue-invoice-follow-up", {
  trigger: schedule("0 9 * * MON", { timezone: "America/New_York" }),
  connections: ["email"],
  description: "Every Monday morning, finds overdue invoices and emails a follow-up reminder to each client with an email address. It does not change invoice records.",
}, async (ctx) => {
  const asOf = ctx.run.date;
  const asOfDate = new Date(asOf + "T00:00:00.000Z");
  const minimumDaysOverdue = 1;
  const maxResults = 100;
  const queryLimit = maxResults + 1;

  const result = await ctx.step("Find overdue invoices", async (ctx) => {
    return await ctx.records.query(
      `SELECT i.id, i.number, i.due_on, i.status,
              c.name AS client_name, c.email AS client_email
       FROM invoices AS i
       LEFT JOIN clients AS c ON c.id = i.client_id
       WHERE i.due_on IS NOT NULL
         AND CAST(i.due_on AS DATE) <= CAST('${asOf}' AS DATE) - ${minimumDaysOverdue}
         AND LOWER(COALESCE(i.status, '')) NOT IN ('paid', 'cancelled', 'canceled', 'void', 'draft')
         AND COALESCE(i._op, 'live') = 'live'
       ORDER BY i.due_on ASC
       LIMIT ${queryLimit}`,
    );
  });

  const hasMore = result.rows.length > maxResults;
  const columnIndex = Object.fromEntries(result.columns.map((column, index) => [column, index]));
  const reminders = result.rows.slice(0, maxResults).map((row) => {
    const invoiceId = String(row[columnIndex.id]);
    const invoiceNumber = String(row[columnIndex.number] ?? invoiceId);
    const clientName = String(row[columnIndex.client_name] ?? "there");
    const clientEmail = row[columnIndex.client_email] == null ? null : String(row[columnIndex.client_email]).trim();
    const dueOn = String(row[columnIndex.due_on]);
    const dueDate = new Date(dueOn + "T00:00:00.000Z");
    const daysOverdue = Math.max(0, Math.floor((asOfDate.getTime() - dueDate.getTime()) / 86400000));
    return {
      invoiceId,
      invoiceNumber,
      clientName,
      clientEmail,
      dueOn,
      daysOverdue,
      subject: `Follow-up: Invoice ${invoiceNumber} is overdue`,
      bodyMarkdown: `Hello ${clientName},\n\nOur records show that invoice ${invoiceNumber}, due ${dueOn}, is ${daysOverdue} day(s) overdue. If you have already sent payment, please disregard this note. Otherwise, please let us know if you need another copy of the invoice.\n\nThank you.`,
    };
  });

  const delivery = await ctx.step("Send reminder emails", async (ctx) => {
    const sent: string[] = [];
    const skipped: string[] = [];
    for (const reminder of reminders) {
      if (!reminder.clientEmail) {
        skipped.push(reminder.invoiceNumber);
        continue;
      }
      await ctx.email.send("email", {
        to: reminder.clientEmail,
        subject: reminder.subject,
        bodyMarkdown: reminder.bodyMarkdown,
      });
      sent.push(reminder.invoiceNumber);
    }
    return { sent, skipped };
  });

  return {
    asOf,
    minimumDaysOverdue,
    returnedCount: reminders.length,
    hasMore,
    emailsSent: delivery.sent.length,
    sentInvoices: delivery.sent,
    skippedMissingEmail: delivery.skipped,
    note: "Scheduled follow-up emails were sent to clients with an email address. No invoice records were changed.",
  };
});