#!/usr/bin/env bash
# Stalled pipeline sweep
# Every open lead whose follow-up date has passed, or that never had one set, grouped by the agent who owns it. The quiet leak in most sales pipelines is not lost deals, it is forgotten ones.
#
# Category : Revenue
# Safety   : read only
# Uses     : leads list, lead-status list, agents list
# Returns  : Overdue and unscheduled leads per agent, with estimated value at risk.
#
# Requires the SendSquared CLI and jq:
#   curl -fsSL https://cli.sendsquared.com/install.sh | bash
#   sendsquared auth login
#
# Free to use and adapt. https://sendsquared.com/cli/scripts/#stalled-pipeline

# Open leads that have gone quiet. estimated_value is in whole cents.
set -euo pipefail
TODAY=$(date -u +%F)
LIMIT=${1:-1000}
ALL=$(mktemp)

# One call, not a paging loop: `leads list --page` is currently ignored by the
# API and every page returns the same rows, so looping silently multiplies the
# same leads. Ask for more than you have instead and check the summary.
R=$(sendsquared leads list --limit "$LIMIT")
echo "--- $(jq -r '.summary // "leads"' <<<"$R")"

jq -r --arg today "$TODAY" '
  .data[]
  | select(.archived_at == null and .closed_at == null)
  | . as $l
  | (if   ($l.followup_at // "") == "" then "never scheduled"
     elif (($l.followup_at | split("T")[0]) < $today) then "overdue"
     else empty end) as $state
  | [ ($l.user_id // 0 | tostring), $state, ($l.estimated_value // 0),
      $l.id, (($l.contact_first_name // "") + " " + ($l.contact_last_name // "")), ($l.subject // "") ]
  | @tsv
' <<<"$R" > "$ALL"

if [ ! -s "$ALL" ]; then echo "no open leads are overdue or unscheduled"; rm -f "$ALL"; exit 0; fi

echo "--- leads needing attention, by owner (user_id)"
printf "  %-12s %6s %14s\n" "OWNER" "LEADS" "VALUE AT RISK"
awk -F'\t' '{n[$1]++; v[$1]+=$3} END {
  for (k in n) printf "  %-12s %6d %14s\n", k, n[k], sprintf("$%.0f", v[k]/100)
}' "$ALL" | sort -k2 -rn

echo
echo "--- ten to work first"
sort -t$'\t' -k3 -rn "$ALL" | head -10 \
  | awk -F'\t' '{printf "  %-16s lead %-9s %-26s %s\n",$2,$4,$5,substr($6,1,40)}'
rm -f "$ALL"