haccfiles/pkgs/scripts/uffd-unused-accounts-notification.scm

#!/usr/bin/env gosh

(use gauche.process)
(use text.csv)
(use scheme.list)
(use gauche.parseopt)
(use util.match)

(define cutoff-date "2023-01-01")

(define sqlite-path "/persist/containers/uffd/uffd/db.sqlite")
(define sqlite-query
  "select displayname, mail, max(expires) as last_login from oauth2token join user on user_id=user.id group by user_id having last_login < '2023-01-01'
union all select displayname, mail, '2022' from user where not exists (select * from oauth2token where user_id = user.id);")

(define dry #f)
(define verbose #f)
(define very-verbose #f)

(define (main args)
  (let-args (cdr args)
      ((averbose     "v|verbose")
       (averyverbose "very-verbose")
       (adry         "n|dry-run")
       (help        "h|help" => (cut show-help (car args)))
       . restargs
      )
      (set! dry adry)
      (set! verbose averbose)
      (when averyverbose
        (set! verbose #t)
        (set! very-verbose #t))
      (match restargs
        [("admin") (do-admin-mail)]
        [("send-reminder") (send-reminder-mails)]
        [("list-accounts") (do-list-accounts)]
        [_ (display "unknown command") (exit 1)]))
  0)

(define (do-admin-mail)
  (send-email "admin@hacc.space" "unused accounts list" (mk-admin-mail unused-accounts))
  (when verbose
    (display "done")))

(define (do-list-accounts)
  (display (string-join
            (map
             (lambda (row) (format "~a (~a)" (list-ref row 0) (list-ref row 1)))
             unused-accounts)
            "\n")))

(define (send-reminder-mails)
  (map (lambda (row)
         (send-email (list-ref row 1) "Unbenutzter infra4future.de Account" (mk-email (list-ref row 0) (list-ref row 2))))
       unused-accounts)
  (when verbose
    (display "done")))


(define csv-reader
  (make-csv-reader #\,))

(define unused-accounts
  (map (lambda (str) (with-input-from-string str csv-reader))
       ;; (process-output->string-list `(cat example.csv))))
       (process-output->string-list `(sqlite3 -csv ,sqlite-path ,sqlite-query))))

(define (mk-email displayname last-login)
  #"
Hallo ~|displayname|!

Wir haben schon lange (seit über einem Jahr; dein letzter Login war um ~|last-login|)
nichts mehr von dir gehört und würden demnächst deinen Account löschen wollen.
Solltest du ihn noch benötigen logge dich bitte einfach auf https://login.infra4future.de ein.
Falls nicht, musst du weiter nichts tun und wir werden deine Account in ca. 3 Monaten löschen.

Viele Grüße,
das Infra4Future Team
")

(define (mk-admin-mail rows)
  (format #"
Meow!

this is the uffd-unused-accounts-notification.scm script. There are currently
~~s accounts which have not logged in since ~|cutoff-date|. To mass-send account
expiry reminders, invoke this script with the \"send-reminder\" option. To see a
list of these accounts, invoke it with the \"list-accounts\" option.

(invoke me,, 🥺)
" (length unused-accounts)))

; utility definitions

(define (send-email address subject text)
  (when verbose
    (display (format "sending email to ~a\n" address)))
  (let ([text (string-append "from: admin@hacc.space\n" "subject: " subject "\n" text "\n")])
    (when very-verbose
      (display text))
    (call-with-output-process
     (if dry '(cat) `(sendmail ,address))
     (lambda (port) (display text port))
     :on-abnormal-exit :ignore)))

(define (show-help progname)
  (display #"
~|progname|: unused account expiry helper script.

Invoke as `~|progname| [options] admin' to send a list of unused accounts to administrators.

Invoke as `~|progname| [options] send-reminder' to send a reminder email to all
currently unused accounts.

Options:
 -v --verbose      show which emails are being sent
    --very-verbose also print emails to stdout
 -n --dry-run      print emails to stdout instead
 -h --help         show this help
"))