Daily revenue watch

trigger:
  kind: schedule
  cron: "0 7 * * 1-5"
  timezone: America/Toronto
steps:
  - key: revenue
    name: Yesterday against the usual
    kind: tool
    tool: snowflake.query
    args:
      sql: |
        with daily as (
          select region, order_date, sum(amount_usd) as revenue
          from analytics.finance.orders
          where order_date >= dateadd(day, -29, current_date()) and order_date < current_date()
          group by region, order_date
        )
        select region,
          max(iff(order_date = dateadd(day, -1, current_date()), revenue, null)) as yesterday,
          avg(iff(order_date < dateadd(day, -1, current_date()) and dayofweek(order_date) = dayofweek(dateadd(day, -1, current_date())), revenue, null)) as usual
        from daily
        group by region
        having usual > ?
        order by region
      params: [1000]
  - key: flag
    name: Flag the unusual regions
    kind: javascript
    inputs: { rows: "{{ steps.revenue.output.rows }}" }
    outputs: { unusual: array, count: integer }
    code: |
      function main({ rows }) {
        const unusual = rows
          .map(row => ({ region: row.REGION, yesterday: row.YESTERDAY || 0, usual: row.USUAL, change: (row.YESTERDAY || 0) / row.USUAL - 1 }))
          .filter(row => Math.abs(row.change) > 0.2)
          .sort((a, b) => Math.abs(b.change) - Math.abs(a.change))
        console.log(`${unusual.length} of ${rows.length} regions moved more than 20%`)
        return { unusual, count: unusual.length }
      }
  - key: explain
    name: Write the read
    kind: llm
    model: smart
    if: { path: steps.flag.output.count, gt: 0 }
    system: You write short, plain notes for a finance team. No speculation beyond the numbers given.
    prompt: |
      These regions' revenue yesterday differed from the average for the same weekday over the last four weeks by more than 20%.
      Give one line per region with the dollar amounts and the change, biggest first, then one sentence on anything they have in common.
      {{ steps.flag.output.unusual }}
  - key: post
    name: "Post to #finance"
    kind: tool
    tool: slack.post_message
    if: { path: steps.flag.output.count, gt: 0 }
    args:
      channel: C05FINANCE
      text: ":chart_with_downwards_trend: *Revenue moved yesterday*\n{{ steps.explain.output.text }}"