## currency_conversion_rates_ingestion.r

# screen -xRR currency_conversion_rates
"
This file unloads the currency_exchange_rates data from MySQL (along with metadata from currencies)
And ingests it into snowflake to the table
    BI.CURRENCY_CONVERSION_RATES   
"


if (FALSE) {
  devtools::install_github("RcppCore/Rcpp")
  devtools::install_github("rstats-db/DBI")
  devtools::install_github("rstats-db/RMySQL")
}


## CRONTAB
## will execute at 8:30pm every 5th day of every month
# 30 20 /5 * * Rscript -e 'source("~/git/orch/src/TableBasics/currency_conversion_rates_ingestion.r")'
# source ("~/git/orch/src/TableBasics/currency_conversion_rates_ingestion.r")

setScience(proj="DeNormalizing", subProj="currency_conversion_rates")
setGitBranchToSystem(); .g()

# sinkfile <- sinkOn()$filename
# on.exit(sinkOff(), add=TRUE)

## SNOWFLAKE SETTINGS
wh <- getWH_by_interactive()
dbname <- "prod"

tbl.out <- "CURRENCY_CONVERSION_RATES"
schema.out <- "bi"
dateCol.out <- "periodid"

reRun <- TRUE

## If the currency_conversion_rates table already exists, check the latest date on it
if (qTableExists(tbl=tbl.out, schema=schema.out, dbname=dbname, snowflake=TRUE, wh=wh, msg_sf=FALSE)) {
  max.sf <- qMaxDate(tbl.out, schema=schema.out, dbname=dbname, dateCol=dateCol.out, period_convert=FALSE, snowflake_inuse=TRUE, verbose=FALSE)
  setDBall("MySQL")
  max.mysql <- qMaxDate("currency_exchange_rates", schema=NULL, dateCol="period_id", period_convert=FALSE, snowflake_inuse=FALSE, verbose=FALSE)
  reRun <- !(unname(max.sf) == unname(max.mysql))
  if (!reRun)
    message("BI.CURRENCY_CONVERSION_RATES is in-sync with the table in MySQL. No updates needed")
  else
    message ("Re-running")
}

 if (reRun) {
  ### ~~~~~~~~~~~~~~~~~~~~~~~ PULL THE DATA ~~~~~~~~~~~~~~~~~~~~~~~~~~~ ##
  setDBall("MySQL")
  qry_rate <- ("
  SELECT 
      CER.period_id as periodid
    , CER.currency_from_id as currency_from_id
    , CER.currency_to_id as currency_to_id
    , CER.exchange_rate as rate_to_multiply
    , F.ISO_4217_code as currency_code_from
    , T.ISO_4217_code as currency_code_to
    , CAST(concat(P.year, right(concat('0', P.month), 2), '01') AS DATE) AS month
  FROM currency_exchange_rates CER
  LEFT JOIN currencies F on F.id = CER.currency_from_id
  LEFT JOIN currencies T on T.id = CER.currency_to_id
  LEFT JOIN period     P on P.period_id = CER.period_id
  ORDER BY CER.period_id, CER.currency_from_id, CER.currency_to_id
  ")

  DT.currency_conversion_rates <- runQry(qry=qry_rate)
  jesusForData(DT.currency_conversion_rates)


  ### ~~~~~~~~~~~~~~~~~~~~~~~ INGEST ~~~~~~~~~~~~~~~~~~~~~~~~~~~ ##
  setSnowflake(wh=wh, dbname=dbname)
  ingestIntoSQL(DT.currency_conversion_rates, tbl=tbl.out, schema=schema.out, boolean_type="int", datetime_type='TIMESTAMP_NTZ', drop=TRUE)

  try ({
    srcOther("Looker") %>% sourceSupportFns
    file.lookml <- create_lookml_from_tbl(tbl=tbl.out, schema=schema.out, wh=wh, dbname=dbname, TEST_RUN=FALSE)
    quickEmail(getRS("to"), files=file.lookml, info=sprintf("LOOKML file for updated %s", schemaPaste(schema=schema.out, tbl.out)))
  })

}

message("\n\n\t\t  CONFIRMATION:\n--------------------------------------------------------")
headDB(tbl=tbl.out, schema=schema.out, dbname=dbname, snowflake_inuse=TRUE, where=c(sprintf("periodid >= %s", max.mysql)), verbose=TRUE) %>% print
qRowCount(tbl=tbl.out, schema=schema.out, dbname=dbname, snowflake_inuse=TRUE, verbose=TRUE) %>% print
qMaxDate(tbl=tbl.out, schema=schema.out, dbname=dbname, snowflake_inuse=TRUE, dateCol="periodid", verbose=TRUE) %>% print

sinkOff()