Write a report of twilio-enabled projects so we can claim them

[pbchase]

Write a report of twilio-enabled projects so we can claim them at twilio.com.

The goal is to identify newly enable projects so that we can submit a ticket to Twilio on behalf of our users to their sid to same EIN and TrustHub model as ours. To do this, we will need to run this script weekly, record the dates we first see new project-SID combinations come online, and generate an email to CTSI-REDCAP-SUPPORT-L@LISTS.UFL.EDU (or maybe a support request survey???) with instructions on what to do.

This code shows all of the SIDs

library(tidyverse)
library(lubridate)
library(dotenv)
library(redcapcustodian)
library(rcc.ctsit)
library(DBI)
library(RMariaDB)

load_dot_env("phone.env")

init_etl("manage_twilio_projects")

rc_conn <- connect_to_redcap_db()

twilio_projects <- tbl(rc_conn, "redcap_projects") %>%
  filter(twilio_enabled == 1) %>%
  select(project_id,
         app_title,
         creation_time,
         production_time,
         contains("deleted"),
         contains("twilio")
         ) %>%
  collect()

number_of_twilio_enabled_projects <- twilio_projects %>% filter(!is.na(twilio_account_sid)) %>% nrow()
number_of_twilio_enabled_projects

twilio_sids <- twilio_projects %>%
  select(project_id,
         app_title,
         creation_time,
         production_time,
         twilio_account_sid
         )
twilio_sids

We need to decide where to record that we saw them. Maybe the rcc_log table? Maybe a dedicated twilio services table?

Note that we plan to bill for Twilio services through the same model as the annual project billing, but we can't send those line items at the moment we see the SIDs come online. That will need to be a different process in a different script documented in a different issue. So don't worry about that here.