source(as.path(srcDir, "iRadioMeta.r"))

iradio.tbls <- qShowTables()[tablename %cont% "iradio"][tablename != "staging_raw_iradio", tablename]

setattr(iradio.tbls, 'names', sapply(strsplit(iradio.tbls, "_"), tail, 1))

if (!exists("con"))
  con <- giveMeACon()

for (i in seq(iradio.tbls))
  assign(paste0("DB.iradio.", names(iradio.tbls)[[i]])
        , runQry(paste0("SELECT * from production.", iradio.tbls[[i]]),  connex=con, verbose.max.width=200)
        )


# DB.iradio.customer[, table(nchar(postal_code))]
#        0        5       10
#     6967   870463 13610076

zipExtract <- function(x) {
#  browser()
  if (!is.character(x))
    x <- as.character(x)
  if (nchar(x[[1]])==0)
    data.table(NA_character_, NA_character_)
  else if (nchar(x[[1]])==10) {
#    strsplit(x, "-")
    as.data.table(do.call(rbind, strsplit(x, "-")))[, lapply(.SD, as.character)]
    ## TODO: handle more than one hyphen
  } else
    data.table(x, rep(NA_character_, length(x)) )
}

suppressWarnings(DB.iradio.customer[, c("zip", "plus4", "zip3") := NULL])
DB.iradio.customer[, c("zip", "plus4") := zipExtract(postal_code), by=nchar(postal_code)]
DB.iradio.customer[, c("zip3") := factor(substr(zip, 1, 3))]
# Clean up blank postal codes
DB.iradio.customer[postal_code=="", postal_code := NA_character_]
DB.iradio.customer[is.na(postal_code), postal_code := NA_character_]

eval(.meta.iradio.event.FIXCOLS)
eval(.meta.iradio.customer.FIXCOLS)
eval(.meta.iradio.station.FIXCOLS)
eval(.meta.iradio.content.FIXCOLS)

## This takes a minute
DB.iradio.customer[, c("zip", "plus4", "postal_code") := lapply(.SD, as.factor), .SDcols=c("zip", "plus4", "postal_code")]

## This is all we need from DB.iradio.station
matchKey(DB.iradio.event, DB.iradio.station, "station_id")
invisible(suppressWarnings(DB.iradio.event[, station_type := NULL]))
DB.iradio.event[DB.iradio.station, station_type := as.character(i.station_type)]
DB.iradio.event[, station_type := factor(station_type, levels=levels(DB.iradio.station[["station_type"]]))]

# ~ jesusForData(DB.iradio.station)
# ~ rm(DB.iradio.station)

## add a `duration` field to DB.iradio.event
DB.iradio.event[, duration := round(event_end_time - event_start_time)]  # round should default to seconds

## Identify the Track duration, approximate
suppressWarnings( DB.iradio.event[ , Track.Seconds := max(duration[event_type == "Played" & end_reason == "Natural"], na.rm=TRUE), by=apple_id] )

# COUNT THE NUMBER OF UNIQUE LISTENERS PER TRACK
DB.iradio.event[, uniq.users.per.track := lunique(customer_id), by=list(apple_id)]

# COUNT THE NUMBER OF UNIQUE TRACKS PER LISTENERS
DB.iradio.event[, uniq.tracks.per.user := lunique(apple_id), by=list(customer_id)]

jesusForData()
saveImageTo()


   ---++====<{   DB.iradio.event    }>====++---
 idcol    :   customer_id,   apple_id,   station_id,   storefront_name
 factor   :   event_type,   end_reason,   device_type,   track_ownership
 POSIXct  :   event_start_time,   event_end_time

   ---++====<{  DB.iradio.customer  }>====++---
 idcol      :   customer_id,   postal_code
 character  :   Zip,   Plus4
 factor     :   user_type

   ---++====<{  DB.iradio.station   }>====++---
 idcol      :   station_id
 character  :   station_name
 factor     :   station_type

   ---++====<{  DB.iradio.content   }>====++---
 idcol   :   apple_id,             isrc,            vendor_id,      vendor_offer_code,
             extracted_upc_long,   extracted_upc,   extracted_isrc
 factor  :   artist,   title,   label

## Print
invisible(sapply(DBs, function(x) {cat("===========<{", x, "}>===========\n"); print(get(x));}))

## -- one by one -- #
jesusForData(DB.iradio.event)
jesusForData(DB.iradio.customer)
jesusForData(DB.iradio.station)
jesusForData(DB.iradio.content)