## screen -xRR MattG
setScience(proj="DeNormalizing", subProj="Escrow")

wh <- "SCIENCE"
dbname="prod"
schema="art_relations"

setSnowflake(wh=wh, dbname=dbname)


# sfQry("SELECT * FROM production.dim_track where rlike(ISRC,  '.*_ISRC$', 'i') limit 12")
# sfQry("SELECT releaseid, isrc FROM bi.accounting where rlike(ISRC,  '.*_ISRC$', 'i') limit 12")
# sfQry("SELECT * FROM production.dim_track where upc = 5050457990084")


for (trans in c("DA", "DT")) {
  tbl_in <- "accounting"
  tbl_out <- sprintf("%s_sales_by_release_store_country_actmonth", trans)
  schema <- "bi"


  colsToPull <- c("activity_month", "labelid", "artistid", "releaseid", "isrc", "storeid", "country_code", "payout_currency_code")
  colsToAgg <- c("gross_revenue_usd", "client_net_receipt_usd", "client_actual_net_usd", "fees_distribution_usd", "fees_dpd_publishing_usd", "fees_mechanical_admin_usd", "fees_ringtone_publishing_usd", "gross_revenue_ccur", "client_net_receipt_ccur", "client_actual_net_ccur", "fees_distribution_ccur", "fees_dpd_publishing_ccur", "fees_mechanical_admin_ccur", "fees_ringtone_publishing_ccur", "units")
  colsToAgg %<>% setNames(nm=paste0(trans, "_", colsToAgg))

  # sfDesc(schema=schema, tbl=tbl_in)
  qry <- makeQry(schema=schema, tbl=tbl_in, dbname=dbname, colsToPull=colsToPull, colsToAgg=colsToAgg, aggFunc="sum", limit=NULL, transac_type_abbr=trans, orderby=c("activity_month", "country_code", "releaseid", "storeid"))
  sfPopulateTable(qry=qry, schema=schema, tbl=tbl_out, dbname=dbname, overwrite=TRUE)

}

if (FALSE) 
{
  f.outs <- c()
  for (trans in c("DT")) {
    sourceSupportFns(proj="Looker")
    f.outs[[trans]] <- create_lookml_from_tbl(schema=schema, tbl=tbl_out, dbname=dbname)


  ## sql_on for the model join
  ##  actually, this isn't completely correct:
  ##    activity_month ~~>  activity_month_month
  ##    xxxid ~~~>  xxx_id
    colsToPull %>% sprintf("${bi_accounting.%1$s}=${bi_%2$s_sales_by_release_store_country_actmonth.%1$s}", ., tolower(trans)) %>% pasteC(C=" AND ") %>% catnn
    }
}