# 01 GlitchMob_fresh
# 
# from.iradio := attributable_purchase != ""
# 


.us()
fresh()
setScience("GlitchMob", subl=FALSE, create=FALSE, load=TRUE)
setScience("GlitchMob", subl=FALSE, create=FALSE)

## Pull the fact_analytics rolled-up data
GlitchMob.Ids <- c(544747, 496818)  ## This was looked up from dim_artist
DT.Glitch.analytics <- qGetAll("analytics", artistid=GlitchMob.Ids)

## Putll the itunes / iradio data
DT.Glitch.itunes_raw      <-  qGetAll ( "raw_itunes",       artist              = "The Glitch Mob"                                                )
DT.Glitch.iradio_content  <-  qGetAll ( "iradio_content",   artist              = "The Glitch Mob"                                                )
DT.Glitch.iradio_event    <-  qGetAll ( "iradio_event",     apple_identifier    = sort(unique(DT.Glitch.iradio_content[["apple_identifier"]]))    )
DT.Glitch.iradio_customer <-  qGetAll ( "iradio_customer",  customer_identifier = sort(unique(DT.Glitch.iradio_event[["customer_identifier"]]))   )
DT.Glitch.iradio_station  <-  qGetAll ( "iradio_station",   station_id          = sort(unique(DT.Glitch.iradio_event[["station_id"]]))            )

## Clean UPC column from iradio_content.
##  First making sure that both extracted_upc_long & extracted_upc have the same value.
if (! DT.Glitch.iradio_content[, all(extracted_upc_long == extracted_upc)])
  stop ("There are differences in 'DT..iradio_content' between 'extracted_upc_long' and 'extracted_upc'")
DT.Glitch.iradio_content[, extracted_upc := NULL]
setnames(DT.Glitch.iradio_content, "extracted_upc_long", "upc")

## Clean download_date & take unique for all 
DT.nms <- c("DT.Glitch.itunes_raw", "DT.Glitch.iradio_content", "DT.Glitch.iradio_event", "DT.Glitch.iradio_customer", "DT.Glitch.iradio_station")
for (nm in DT.nms)
  assign(nm, unique(suppressWarnings(get(nm)[, download_date := NULL]), by=NULL))
  # The above is equivalent to, eg: 
  #    ## Clean content_date from iradio_content
  #    DT.Glitch.iradio_content[, download_date := NULL]
  #    DT.Glitch.iradio_content <- unique(DT.Glitch.iradio_content, by=NULL)

timCols <- c("event_start_time", "event_end_time")
DT.Glitch.iradio_event[, c(timCols) := lapply(.SD, as.character), .SDcols = timCols]


# ----- Fix iTunes' UPC==0 issue ----- # 

DT.Glitch.release_info <- qGetAll("release", artistid=GlitchMob.Ids)
setnames(DT.Glitch.release_info, "releaseid", "upc")
setkey(DT.Glitch.release_info, upc)
DT.Glitch.isrc_info    <- qGetAll("isrc", upc=DT.Glitch.release_info[, unique(upc)])


      #  ## Note the DUP titles
      #  DT.Glitch.release_info[, "title_simple" := gsub("\\s*\\(.+\\)\\s*$", "", releasename)]
      #  findDupsBy(DT.Glitch.release_info, c("title_simple"))

# ----- remove Commentary versions ----- # 
## The commentary UPCs are JUST for spotify, so they dont apply for itunes Cleaning.
##  We need to remove them, because otherwise, there is a 1-to-many relationship 
##  from   isrc-to-upc
UPCs.nonCommentary  <- DT.Glitch.release_info[ !grepl("Commentary", version),  unique(upc)]
DT.Glitch.isrc_info <- DT.Glitch.isrc_info[upc %in% UPCs.nonCommentary]


setkeyIfNot(DT.Glitch.isrc_info, "isrc")
setkeyIfNot(DT.Glitch.itunes_raw, "isrc")
setkeyIfNot(DT.Glitch.iradio_content, "isrc")

DT.Glitch.itunes_raw[DT.Glitch.isrc_info, upc_fixed := i.upc, nomatch=0]
DT.Glitch.itunes_raw[upc==0, upc := upc_fixed]

DT.Glitch.iradio_content[DT.Glitch.isrc_info, upc_fixed := i.upc]
DT.Glitch.iradio_content[upc==0, upc := upc_fixed]


# ----- Fix iTunes' UPC==0 issue ----- # 

## Save the Data
jesusForData(DT.Glitch.analytics, DT.Glitch.itunes_raw, DT.Glitch.iradio_content, DT.Glitch.iradio_event, DT.Glitch.iradio_customer, DT.Glitch.iradio_station, DT.Glitch.releases)
saveImageTo()