tbl.raw <- "staging_raw_itunes"
dateCol.raw <- getDateColForTBL('staging_raw_itunes', schema="production")
product_coddes.using <- c("P", "I", "W", "Z") ## Remember, we just want to find out when the price changed
colsToPull.raw <- c(date=dateCol.raw, "upc", "country_code", "product_type_identifier", "sale_return", "promo_code", "preorder", "customer_currency", "customer_price", "royalty_currency", "royalty_price")

qry.raw <- makeQry(tbl=tbl.raw
                  , colsToAgg="units" #colsToAgg
                  , colsToPull=colsToPull.raw
                  , dateCol=dateCol.raw
                  , minDate=minDate
                  , where=list( country_code=names(countries_using)
                              , product_type_identifier=product_coddes.using
                              , upc=UPCs
                              )
                  , key="colsToPull"
                  )

DT.raw <- runQry(qry.raw, cluster=cluster.raw, allow.large.groupby=TRUE, verbose.key=FALSE)
BackUpOrRestore("DT.raw", force=TRUE, verbose=FALSE)


## the 'revenue' column is actually royalty in local currency
## This is done in getRevenueAndUnitsColsForTbl(tbl.raw) and perhaps I shold revisit the output
DT.raw[, customer_price := as.numeric(customer_price)]
DT.raw[, control.wk1 := !(upc %in% upcs.changing)]


upcToID_(DT.raw)
DT.raw[, storeid := as.idcol(1)]
DT.raw[, transac_type_abbr := ifelse(product_type_identifier %in% c("P", "I", "W", "Z"), "DA", NA)]

if (length(upcs.not_in_raw <- as.character(setdiff(DT.EU_analytics[transac_type_abbr == "DA" & storeid == 1]$upc, DT.raw$upc)))) {
  warning (sprintf("There are %i (out of %i) UPCs in fact_analytics that were not found in the raw table:\n %s", length(upcs.not_in_raw), lunique(DT.EU_analytics[transac_type_abbr == "DA"]$upc), pasteC(upcs.not_in_raw, C=",  ")))
}
if (length(upcs.not_in_analytics <- as.character(setdiff(DT.raw$upc, DT.EU_analytics[transac_type_abbr == "DA" & storeid == 1]$upc)))) {
  warning (sprintf("There are %i (out of %i) UPCs in the raw table that were not found in fact_analytics", length(upcs.not_in_analytics), lunique(DT.raw$upc), pasteC(upcs.not_in_raw, C=",  ")))
}


addTestWkCols_(DT.raw)



### I DONT THINK WE NEED DT.raw FOR ANYTHING
if (FALSE) {

    DT.raw_aggd <- aggregateDT(DT.raw[!is.na(TestWk) & country_code %ni% "IE"]
                            , colsToAgg=countCols
                            , by=byCols
                            , aggFunc=aggFunc)



      joinCols.forRaw <- c("date", "storeid",  "upc", "transac_type_abbr", "country_code", "royalty_price")
      maxDate.join <- as.Date("2015-02-21")
      DT.merged <- merge(DT.EU_analytics[storeid == 1 & transac_type_abbr == "DA"][date <= maxDate.join], DT.raw[date <= maxDate.join], by=joinCols, all=TRUE, allow=TRUE)
      DT.merged[(is.na(is_iTunesDA & !is.na(TestWk)) | is.na(customer_price)) & upc %ni% upcs.known_to_be_absent]
     

      DT.raw[upc == 8028980551222 & date == "2015-02-20"]
      DT.EU_analytics[storeid == 1 & transac_type_abbr == "DA"][date <= maxDate.join][upc == 8028980551222 & date == "2015-02-20"]


      addColsFrom_(DT.EU_analytics, DT.raw, joinCols=c("date", "storeid",  "upc", "transac_type_abbr", "country_code"))
      DT.EU_analytics[transac_type_abbr=="DA", .N, by=c("date", "storeid", "upc", "transac_type_abbr", "country_code")][N != 1]
      DT.EU_analytics[upc == 610696123275 & storeid == 1 & date == "2015-01-08"]
      DT.raw[upc == 610696123275 & storeid == 1 & date == "2015-01-08"]

    #   DT.EU_analytics[upc == 610696123275 & storeid == 1 & date == "2015-01-08"][, lapply(.SD, lunique)]
    #   DT.raw[upc == 610696123275 & storeid == 1 & date == "2015-01-08"]
    #   merge(
    #   DT.EU_analytics[upc == 610696123275 & storeid == 1 & transac_type_abbr == "DA"][, list(revenue, royalty), keyby=list(country_code, date)]
    # ,  DT.raw[upc == 610696123275][, revenue, keyby=list(country_code, date)]
    # , all=TRUE)
    #   [, lapply(.SD, lunique)]

}
## Missing UPCS
if (FALSE) {

    upc.test <- "884385011413"
    minDate.test <- "2015-02-01"
    maxDate.test <- "2015-02-15"
    country_code.test <- c(DE=4, FR=6)

    DT.country[country_code %in% (country_code.test)]
    tmp.Q.fact <- makeQry(tbl="fact_analytics", colsToPull=c("transactiontypeid", "countryid"), colsToAgg=c(rows="*"), aggFunc="count", minDate=minDate.test, maxDate=maxDate.test, whereIn=list(storeid = 1, countryid=country_code.test, releaseid=upc.test), dateCol="download_activity_date")
    tmp_DT.fact <- runQry(ttmp.Q.fact, cluster=cluster.raw)


    tmp.Q.raw <- makeQry(tbl="staging_raw_itunes", colsToPull="product_type_identifier", colsToAgg=c(rows="*"), aggFunc="count", minDate=minDate.test, maxDate=maxDate.test, whereIn=list(country_code=names(country_code.test), upc=upc.test), dateCol="download_activity_date")
    tmp_DT.raw <- runQry(ttmp.Q.raw, cluster=cluster.raw)

}


## ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ##

## Also create DT.manual_test
qry.manual_test <- gsub("AND\\s*product_type_identifier in \\(.+?\\)", "", qry.raw)
qry.manual_test <- gsub("upc in \\(.*?)", sprintf("upc in %s", pasteQ(upc_manuallytested)), qry.manual_test)
DT.manual_test <- runQry(qry.manual_test, cluster=cluster.raw, allow.large.groupby=TRUE, verbose=FALSE)
setInfo(DT.manual_test, paste0("UPC ", upc_manuallytested, " was deliberately purchased on Feb 3rd\n and should show up in this DT\nThe query for DT.manual_test is identical to DT.raw, except for the upc"))

## ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ##