setScience(proj='DeNormalizing', subProj="track_artists_by_track_id", subl=FALSE, load=FALSE)
setGitBranchToSystem(); .g()

dbname <- "prod"

track_ids_with_multiple_performers <- c(9644455 ,11851937 ,11854908 , 9486250 , 9475912 ,11193576 ,11195656 ,11196240 ,11196244 ,11196245)

setDBall("mysql")

## Show how f'd our data on this is
if (FALSE)
runQry(makeQry(tbl="track_artist", schema=NULL, track_id=track_ids_with_multiple_performers, colsToPull=c("*"), orderby=c("track_id", "type", "name"), expand=FALSE))


DT.track_artists_by_track_id <- {
  runQry("
    SELECT 
        track_id
      , GROUP_CONCAT(type, ':', '\"', name, '\"' SEPARATOR ',') as track_artists_and_type
      , GROUP_CONCAT(
              CASE WHEN type = 'performer' THEN name else NULL END
              SEPARATOR '; '
        ) as track_artist
      , GROUP_CONCAT(
              CASE WHEN type = 'featuring' THEN name else NULL END
              SEPARATOR '; '
        ) as track_featuring_artist
      , COUNT(DISTINCT CASE WHEN type = 'performer' THEN name else NULL END) as number_of_unique_performers

    FROM (
      SELECT DISTINCT 
        track_id, type, name
      FROM track_artist
      WHERE NOT (
          (type is NULL or type = '') AND (name is NULL or name = '')
      )
    ) AS T
    GROUP BY track_id  
    ORDER BY (CASE WHEN type = 'performer' THEN 'AAAAAAAAA' else type end) ASC, name ASC")
}

setkey(DT.track_artists_by_track_id, track_id)
DT.track_artists_by_track_id[.(track_ids_with_multiple_performers)]


wh <- getWH_already_on(default="cron_jobs_small")

setSnowflake(wh=wh, dbname=dbname)
ingestIntoSQL(DT.track_artists_by_track_id, schema="bi", dbname=dbname, datetime_type='TIMESTAMP_TZ', drop=TRUE)

if (FALSE) {
  sourceSupportFns(proj="Looker")
  create_lookml_from_tbl(tbl="track_artists_by_track_id", schema="bi", dbname=dbname)

  bringAndCopyLookerFiles("~/git/orch/out/DeNormalizing/auto_generated_lookmls/bi/bi.track_artists_by_track_id.view.lookml")
}