# getDMACodes()
getDMACodes <- function(file_dma="~/git/orch/ingest/TableBasics/DMA_Codes_google_20150403_with_sloppy_timezones.tsv") {
  message("DST is 2015-03-08 to 2015-11-01")

  ## FILES: 
  "~/git/orch/ingest/TableBasics/DMA_Codes_google_20150403 - NO TIMEZONES.csv"
  "~/git/orch/ingest/TableBasics/DMA_Codes_google_20150403_with_sloppy_timezones.tsv"

  DT.DMACodes <- fread(file_dma)
  DT.DMACodes %>% setnamesSpaceToUnderscore_ %>% setnamestolower
  if ("dma_region_code" %in% names(DT.DMACodes))
    setnames(DT.DMACodes, "dma_region_code", "dma_code")

  DT.DMACodes[state == "District of Columbia", state := "Washington DC"]
  setkeyIfNot(DT.DMACodes, state, dma_code, organize=TRUE, verbose=FALSE)

  DT.states <- data.table(state=state.name, state_code=state.abb, key="state")
  DT.DMACodes[DT.states, state_code := state_code]

  setkeyIfNot(DT.DMACodes, dma_code, state_code, state, organize=TRUE, verbose=FALSE)

  DT.DMACodes[grepl("washington", dma_region, ignore.case=TRUE), ]
  DT.DMACodes
}


### -------------------------- BELOW IS THE SCRATCH WORK OF HOW THE TIMEZONES WERE ADDED TO THE DMA_CODES ----------------------------------- ###


## TIME ZONE
if (FALSE)
  setScience("TableBasics", subProj="timezones")

## Read in the CSV created from Wikipedia, modify, clean and write back to disk
if (FALSE) {  
  f.tz <- src.p("original_timezones_offset_by_state.tsv")
  DT.tz <- fread(f.tz, header=TRUE, sep=",")
  setnamesSpaceToUnderscore_(setnamestolower(DT.tz))

  DT.timezones_offset_by_state <- copy(DT.tz)
  DT.timezones_offset_by_state[, details := pasteC(details, C=" -- "), by=state]
  DT.timezones_offset_by_state[, multi_offset := lunique(time_offsets) > 1, by=state]
  DT.timezones_offset_by_state[, alt_offset := if (length(time_offsets) == 1) NA_character_ else commaSep(time_offsets[-1]), by=state]
  DT.timezones_offset_by_state[, time_offsets := time_offsets[[1]], by=state]
  DT.timezones_offset_by_state <- unique(DT.timezones_offset_by_state)

  f.out <- writeDT(DT.timezones_offset_by_state) %>% {file.rename(., gsub("/out/", "/ingest/", .))}
}

## Read in the CSV created from Wikipedia, modify, clean and write back to disk
if (FALSE) {  
  DT.DMACodes <- getDMACodes("~/git/orch/ingest/TableBasics/DMA_Codes_google_20150403 - NO TIMEZONES.csv")
  DT.DMACodes_with_timezone <- merge(DT.DMACodes, DT.timezones_offset_by_state, by="state", all=TRUE, allow=TRUE)
  setnames(DT.DMACodes_with_timezone, "dst", "dst_observed")
  DT.DMACodes_with_timezone[, multi_tzs_in_dma := lunique(time_offsets) > 1 && !is.na(dma_code), keyby=dma_code]

  ## if multi timezones in the state and multi timezones in the dma, chnage to the alt_offset
  DT.DMACodes_with_timezone[multi_offset & multi_tzs_in_dma, time_offsets := alt_offset]
  DT.DMACodes_with_timezone <- DT.DMACodes_with_timezone[!is.na(dma_code), names(DT.DMACodes) %>% c("time_offsets", "dst_observed"), with=FALSE]

  ## TRIM
  charCols <- sapply(DT.DMACodes_with_timezone, is.character) %>% nwhich
  DT.DMACodes_with_timezone[, (charCols) := lapply(.SD, trim), .SD=charCols]

  ## Convert dst_observed to a TRUE/FALSE.  Making sure all fall into an expected value. (Note AZ specific 'Part')
  DT.DMACodes_with_timezone[, dst_observed := ifelse(dst_observed %in% c("Yes", "Part"), TRUE, ifelse(dst_observed=="No", FALSE, NA))]
  stopifnot(DT.DMACodes_with_timezone[, !is.na(dst_observed)])

  ## add timezone name
  DT.DMACodes_with_timezone[time_offsets == "-05:00", timezone_name := "Eastern"]
  DT.DMACodes_with_timezone[time_offsets == "-06:00", timezone_name := "Central"]
  DT.DMACodes_with_timezone[time_offsets == "-07:00", timezone_name := "Mountain"]
  DT.DMACodes_with_timezone[time_offsets == "-08:00", timezone_name := "Pacific"]
  DT.DMACodes_with_timezone[time_offsets == "-09:00", timezone_name := "Alaska"]
  DT.DMACodes_with_timezone[time_offsets == "-10:00", timezone_name := "Hawaii-Aleutian"]
  stopifnot(DT.DMACodes_with_timezone[, !is.na(timezone_name)])

  DT.DMACodes_with_timezone[, .initial := gsub("([A-Z])[a-z]+(-)?(A-Z)?", "\\1\\3", timezone_name) %>% gsub("^A$", "AK", .)]
  DT.DMACodes_with_timezone[, Standard_TZ := paste0(.initial, "ST")]
  DT.DMACodes_with_timezone[, Daylight_TZ := ifelse(dst_observed, paste0(.initial, "DT"), Standard_TZ)]
  DT.DMACodes_with_timezone[, .initial := NULL]
  DT.DMACodes_with_timezone[, .N, keyby=list(time_offsets, state, dst_observed, Standard_TZ, Daylight_TZ)]

  DT.DMACodes_with_timezone[, UTC_offset_std := as.numeric(gsub(":00$", "", time_offsets))]
  DT.DMACodes_with_timezone[, UTC_offset_dst := UTC_offset_std]
  DT.DMACodes_with_timezone[(dst_observed), UTC_offset_dst := UTC_offset_std + 1]

  f.out <- writeDT(DT.DMACodes_with_timezone, base.file="DMA_Codes_google_20150403_with_sloppy_timezones") %T>% {file.rename(., gsub("/out/", "/ingest/", .))} %>% gsub("/out/", "/ingest/", .)
}

## _____________________________________________________________________________________ ##
## _________________________ MANUAL PROCESS ____________________________________________ ##
## _____________________________________________________________________________________ ##
## ALTERNATE to using multi_offset & multi_tzs_in_dma
##   ie    DT.DMACodes_with_timezone[multi_offset & multi_tzs_in_dma, time_offsets := alt_offset]
## Is to go through the details state by state
if (FALSE & exists("Not doing it this way -- but you could if you have the time to do it manually")) {
  DT.DMACodes <- getDMACodes("~/git/orch/ingest/TableBasics/DMA_Codes_google_20150403 - NO TIMEZONES.csv")
  DT.DMACodes_with_timezone <- merge(DT.DMACodes, DT.timezones_offset_by_state, by="state", all=TRUE, allow=TRUE)
  setnames(DT.DMACodes_with_timezone, "dst", "dst_observed")
  DT.DMACodes_with_timezone[, multi_tzs_in_dma := lunique(time_offsets) > 1 && !is.na(dma_code), keyby=dma_code]

  DT.DMACodes_with_timezone[, cities_in_dma_code := commaSep(city) %>% gsub("(.{1,75})(.+)", "\\1 ...", .), keyby=dma_code]
  DT.DMACodes_with_timezone[, city := NULL]
  DT.DMACodes_with_timezone[, criteria_id := NULL]
  DT.DMACodes_with_timezone <- unique(DT.DMACodes_with_timezone, by=NULL)  

  ## Confirm no double-offsets
  stopifnot(DT.DMACodes_with_timezone[multi_tzs_in_dma & multi_offset, !any(is.na(alt_offset))])

  # ALTERNATE: 
  #  states_with_multi <- DT.DMACodes_with_timezone[(multi_tzs_in_dma), unique(state)]
  states_with_multi <- DT.DMACodes_with_timezone[(multi_offset), unique(state)]

  ## MANUALLY OBSERVE
  for (st in states_with_multi) {
    print(DT.DMACodes_with_timezone[state == st])
    readline(" -- ")
  }
}
## _____________________________________________________________________________________ ##