# screen -xRR BunnyLee

setScience("BunnyLee", load=FALSE, create=TRUE, subl=TRUE)
setGitBranchToSystem(); .g()

wh <- "LOOKER_WH_LARGE"
dbname <- "prod"
setSnowflake(wh=wh, dbname=dbname)

f.in <- ingest.p("Bunny Lee Catalog 9.25.15.csv")


## wrapper-function with selected arg values
clean_text <- {. %>% clean_names_to_simple_alpha(whitespace=TRUE, inside_parens=TRUE, parens=TRUE, trim=TRUE, tolower=TRUE, duplicate_whitespace=FALSE)}

## Ingest file and slight cleaning
{
  DT.bunny_lee <- fread(f.in) %>% cleanColNamesForSQL_
  setnames(DT.bunny_lee, "name_of_artist", "artist_name")
  setnames(DT.bunny_lee, "name_of_album", "release_name")
  setnames(DT.bunny_lee, "tracklisting", "track_name")

  ## Convert Characters
  toConvert <- lapply(DT.bunny_lee, is.character) %>% nwhich
  inds.odd_chars <- unique(which(DT.bunny_lee[, lapply(.SD, iconv, from="latin1", to="UTF-8"), .SDcols=toConvert] != DT.bunny_lee[, toConvert, with=FALSE], arr.ind=TRUE)[, 1])
  DT.bunny_lee[, (toConvert) := lapply(.SD, iconv, from="latin1", to="UTF-8"), .SDcols=toConvert]
  print(DT.bunny_lee[inds.odd_chars])

  DT.bunny_lee[, row_number := seq(.N)]
}

# not needed #  if (!exists("DT.labels"))  DT.labels  <- sfQry("SELECT * FROM bi.label_view")
# not needed #  if (!exists("DT.artists")) DT.artists <- sfQry("SELECT * FROM bi.artist_view")
qry_tracks <- setQry("SELECT R.labelid, R.artistid, upc, trackid, isrc, L.labelname as label_name, imprint, A.artistname as artist_name, R.releasename as release_name, trackname as track_name, track_type, R.version, release_status, digital_only 
FROM production.dim_track T 
JOIN production.dim_label   L on T.labelid = L.labelid
JOIN production.dim_release R on T.upc = R.releaseid
JOIN production.dim_artist  A on R.artistid = A.artistid
WHERE R.deletions != 'Y'
  AND NOT (ENDSWITH(isrc, '_ISRC'))")

## EXECUTE QUERY
if (!exists("DT.tracks")) DT.tracks  <- sfQry(qry_tracks) %>% setIDCols()


## ADD CLEANED NAME
{
  if ( "track_name_cleaned" %ni% names(DT.tracks))     s.t(msg="Creating track_name_cleaned in DT.tracks",       DT.tracks[,     track_name_cleaned := clean_text(track_name)]                                         )
  if ("artist_name_cleaned" %ni% names(DT.tracks))     s.t(msg="Creating artist_name_cleaned in DT.tracks)",     DT.tracks[,    artist_name_cleaned := clean_text(artist_name)]                                        )
  if (       "artist_is_va" %ni% names(DT.tracks))     s.t(msg="Creating artist_is_va in DT.tracks",             DT.tracks[,           artist_is_va := is_various_artists(artist_name_cleaned, already_cleaned=TRUE)]  )

  if ( "track_name_cleaned" %ni% names(DT.bunny_lee))  s.t(msg="Creating track_name_cleaned in DT.bunny_lee",    DT.bunny_lee[,  track_name_cleaned := clean_text(track_name)]                                         )
  if ("artist_name_cleaned" %ni% names(DT.bunny_lee))  s.t(msg="Creating artist_name_cleaned in DT.bunny_lee)",  DT.bunny_lee[, artist_name_cleaned := clean_text(artist_name)]                                        )
  if (       "artist_is_va" %ni% names(DT.bunny_lee))  s.t(msg="Creating artist_is_va in DT.bunny_lee",          DT.bunny_lee[,        artist_is_va := is_various_artists(artist_name_cleaned, already_cleaned=TRUE)]  )
}

.sufx <- c(".bunnylee", ".orchard")
# DT.merged     <- merge(DT.bunny_lee, DT.tracks, by=c("artist_name_cleaned", "track_name_cleaned"), all.x=TRUE, all.y=FALSE, suffix=.sufx)
s.t(msg="Creating DT.merged (three merges)", 
DT.merged  <- rbind(fill=TRUE, use.names=TRUE 
                  , merge(DT.bunny_lee, DT.tracks, by=c("artist_name_cleaned", "track_name_cleaned"), all.x=TRUE, all.y=FALSE, suffix=.sufx)
                  , merge(DT.bunny_lee[(artist_is_va)], DT.tracks, by=c("track_name_cleaned"), all=FALSE, suffix=.sufx)
                  , merge(DT.bunny_lee, DT.tracks[(artist_is_va)], by=c("track_name_cleaned"), all=FALSE, suffix=.sufx)
                 )
)

setkeyIfNot(DT.merged, "row_number", "artist_name_cleaned", "track_name_cleaned", organize=TRUE, verbose=FALSE)

## Identify Type of Match
{
  none <- "NONE"
  exact <- "EXACT Artist & Track"
  partials <- c("PARTIAL Artist; EXACT Track", "EXACT Artist; PARTIAL Track", "PARTIAL Artist; PARTIAL Track")
  exact_va <- "EXACT Track; Artist is V/A"
  partial_va <- "PARTIAL Track; Artist is V/A"

  ## IDENTIFY THE TYPE OF MATCH
  DT.merged[, match_type := none]
  DT.merged[artist_name.bunnylee == artist_name.orchard & track_name.bunnylee != track_name.orchard, match_type := "EXACT Artist; PARTIAL Track"]
  DT.merged[artist_name.bunnylee != artist_name.orchard & track_name.bunnylee == track_name.orchard, match_type := "PARTIAL Artist; EXACT Track"]
  DT.merged[artist_name.bunnylee != artist_name.orchard & track_name.bunnylee != track_name.orchard, match_type := "PARTIAL Artist; PARTIAL Track"]
  DT.merged[(artist_is_va.bunnylee | artist_is_va.orchard) & track_name.bunnylee == track_name.orchard, match_type := exact_va]
  DT.merged[(artist_is_va.bunnylee | artist_is_va.orchard) & track_name.bunnylee != track_name.orchard, match_type := partial_va]
  DT.merged[artist_name.bunnylee == artist_name.orchard & track_name.bunnylee == track_name.orchard, match_type := exact]
  DT.merged[track_name_cleaned == "come to dub"]


  ## IDENITFY THE TYPE OF MATCH, BY row_number
  DT.merged[, is_exact_match_va := any(match_type == exact_va), by=row_number]
  DT.merged[, is_exact_track_match_non_va := any(match_type == "PARTIAL Artist; EXACT Track"), by=row_number]
  DT.merged[, is_exact_match    := any(match_type == exact), by=row_number]
  DT.merged[, has_no_match    := all(match_type == none), by=row_number]

  ## If there is an EXACT match, drop anything else from the list
  DT.merged <- DT.merged[!(is_exact_match & match_type != exact)]

  ## For the rest, count how many of each
  DT.merged[!is.na(upc), number_of_matches := lunique(upc), by=row_number]
  DT.merged[!is.na(upc) & match_type == exact, number_of_matches := lunique(upc), by=row_number]
  ## Conirm any rows where there are NO match have only one row in DT.merged per row_number
  stopifnot(DT.merged[(has_no_match), .N, by=row_number][, N==1])


  col_ordering <- c("row_number", "artist_name.bunnylee", "release_name.bunnylee", "track_name.bunnylee", "number_of_matches", "track_name.orchard", "release_name.orchard", "artist_name.orchard", "imprint", "label_name", 'match_type', 'labelid', 'artistid', 'upc', 'trackid', 'isrc', 'track_type', 'version', 'release_status', 'digital_only')
  setcolorderpt(DT.merged, start=col_ordering)
}

## form levels, so we can find a min
levs <- c(exact, partials, exact_va, partial_va, none)
DT.merged[, match_type_factor := factor(match_type, levels=levs)]

## Find the most exact match_type by row_number
DT.merged[, match_type_for_row := as.numeric(match_type_factor) %>% min %>% {levs[.]} %>% factor(levels=levs), by=row_number]

## SUMMARIZE
DT.summary <- DT.merged[, 'A', keyby=list(row_number, match_type=match_type_for_row)][, list(number_of_tracks=.N), keyby=match_type][, percent_matched := fwp(percOfTotal(number_of_tracks))][]
DT.summary[match_type == "NONE", match_type := "Not Matched (New Content for The Orchard)"]

## MAIN SHEET
DT.bunny_lee_output <- unique(DT.merged[match_type == match_type_for_row, col_ordering, with=FALSE], by="row_number")
setkeyIfNot(DT.bunny_lee_output, row_number)
stopifnot(identical(DT.bunny_lee_output$row_number, seq(nrow(DT.bunny_lee))))
DT.bunny_lee_output[, row_number := NULL]

## DETAILED SHEET
DT.details <- copy(DT.merged)
DT.details[, extract("_cleaned", DT.details) := NULL]
DT.details[, match_type_factor := NULL]
DT.details[, match_type_for_row := NULL]


## TAKES ABOUT 3 minutes to write to disk
f.out <- exportXLS.usingXLConnect(f.out=out.p("BunnyLee_Matched_to_Orchard", ext="xlsx"), DTs=list(Summary=DT.summary, BunnyLee=DT.bunny_lee_output, detailed_matches=DT.details))
quickEmail(f.out, "rsaporta@theorchard.com")