# ~/git/orch/src/Spotify_Metadata_2016/Trouble Shooting the Fanout from the Joins.r

## Checking Spotify SOS Raw View

wh <- "looker_wh_large"
dbname <- "prod"
schema <- "Spotify"
tbl <- "sos_from_raw_view"

setGitBranchToSystem(); .g()
setSnowflake(wh=wh, dbname=dbname)

DT.VIEWS <- sfShowViews(schema=schema)[, name := tolower(name)]
catnn(unlist(DT.VIEWS[name==tbl]$text))


sfDesc(schema="production", tbl="dim_track")
DT.isrcs_21786 <- sfQry("SELECT upc, isrc, track_id, track_unique_id FROM production.dim_track WHERE labelid = 21786 order by 1, 3 asc")
DT.other <- sfQry("SELECT labelid, count(*) as cnt from production.dim_track where labelid != 21786 and isrc in (SELECT isrc FROM production.dim_track WHERE labelid = 21786) GROUP BY 1 ORDER BY 2 DESC")

get_label_meta(DT.other[1:3, labelid])[]


upcs_using <- DT.isrcs_21786$upc %>% sunique %>% head(7) %>% as.character

minDate <- (as.POSIXct("2016-02-01") + 1)

qry.sos_view <- makeQry(schema=schema, tbl=tbl, minDate=minDate, dateCol="activity_datetime", album_code=upcs_using, colsToPull=c("row_id", "album_code", "isrc", "track_uri", source="source_original_values"))
DT.sos_view <- sfQry(qry.sos_view)
BackUpOrRestore(DT.sos_view)
setIDCols(DT.sos_view)
dim(DT.sos_view, round=FALSE)

qry.raw <- makeQry(schema="production", tbl="staging_raw_spotify_v2", minDate=minDate, dateCol="tmstamp", album_code=upcs_using, colsToPull=c("row_id", "album_code", "isrc", "track_uri", "source"))
DT.raw <- sfQry(qry.raw)
BackUpOrRestore(DT.raw)
setIDCols(DT.raw)
dim(DT.raw, round=FALSE)


DT.rows <- 
sfQry("
SELECT row_id, count(*) as rws 
FROM 
(
  SELECT row_id, album_code, 
      IFNULL(cohab.main_isrc, S.isrc) AS main_isrc,
      s.isrc, track_uri, source_original_values AS source
  FROM   Spotify.sos_from_raw_view S
  LEFT JOIN prod.cohabitants.spot_main_isrc_by_trackname_trackalbumartist cohab ON S.isrc = cohab.isrc
  WHERE  (album_code in ('84854203794', '84854218545', '190374022149', '190374032308', '190374049528', '190374050180', '190374079020'))
    AND  activity_datetime >= '2016-02-01 00:00:01'
)
group by 1
having rws > 3
")[]

setIDCols(DT.rows)[, ]

## D'Oh!!  Fanning out is happening right at the onset

DT.sos_view[row_id == "175759089397"]
DT.raw[row_id == "175759089397"]

DT.175759089397 <- sfQry(makeQry(row_id = 175759089397, schema=schema, tbl=tbl, colsToPull="*", expand=FALSE))
DT.175759089397_raw <- sfQry(makeQry(row_id = 175759089397, schema="production", tbl="staging_raw_spotify_v2", colsToPull="*", expand=FALSE))

   SELECT row_id
   FROM   Spotify.sos_from_raw_view
   WHERE  (row_id=175759089397)

unique(DT.175759089397, by=NULL)


DT.182750077855 <- sfQry(makeQry(row_id = 182750077855, schema=schema, tbl=tbl, colsToPull="*", expand=FALSE))
DT.182750077855_raw <- sfQry(makeQry(row_id = 182750077855, schema="production", tbl="staging_raw_spotify_v2", colsToPull="*", expand=FALSE))
unique(DT.182750077855, by=NULL)

sfQry("SELECT * FROM production.dim_track where isrc = 'BRRGE1200458'")[]
sfQry("SELECT DISTINCT ISRC, upc, LABELID FROM prod.production.dim_track where isrc = 'BRRGE1200458'")[]


  LEFT JOIN prod.cohabitants.spot_main_isrc_by_trackname_trackalbumartist cohab ON S.isrc = cohab.isrc
  LEFT JOIN (SELECT ISRC, LABELID FROM prod.production.dim_track) T ON S.isrc = T.isrc
  LEFT JOIN prod.production.dim_label L ON T.labelid = L.labelid
  LEFT JOIN prod.bi.country_view C ON S.user_country = C.country_code