## still TODO:   
warning("\n\n----------------------------------------------\n\nNeed to set snowflake and set science\nfor '~/git/orch/src/Discover_Weekly/create meta_from_raw_v2.r'\n\n----------------------------------------------\n\n")


tbl.meta_from_raw_v2 <- "meta_from_raw_v2"
qry.meta_from_raw_v2 <- "
SELECT DISTINCT
  ALBUM_CODE,
  isrc,
  track_id, 
  track_artists, 
  track_name, 
  track_uri, 
  track_album_artist, 
  album_name, 
  has_sloppy_metadata, 
  last_TMSTAMP
FROM (

    SELECT 
      row_no,  
      ALBUM_CODE,  
      isrc,  
      track_id,   
      track_artists,   
      track_name,   
      track_uri,   
      track_album_artist,   
      album_name,  
      last_TMSTAMP,
      CASE WHEN count(*) > 1 THEN 1 ELSE 0 END AS has_sloppy_metadata
    FROM(
        SELECT 
           ROW_NUMBER() OVER (PARTITION BY ALBUM_CODE, ISRC ORDER BY last_TMSTAMP DESC) AS row_no,  
           ALBUM_CODE,  
           isrc,  
           track_id,   
           track_artists,   
           track_name,   
           track_uri,   
           track_album_artist,   
           album_name,  
           last_TMSTAMP
        FROM (
                SELECT
                  ALBUM_CODE,
                  isrc,
                  track_id, 
                  track_artists, 
                  track_name, 
                  track_uri, 
                  track_album_artist, 
                  album_name,
                  max(TMSTAMP) as last_TMSTAMP
                FROM production.staging_raw_spotify_v2
                GROUP BY 1, 2, 3, 4, 5, 6, 7, 8
        )
    )
    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
)
WHERE row_no = 1"


sfPopulateTable(tbl=tbl.meta_from_raw_v2, schema="spotify", qry=qry.meta_from_raw_v2, overwrite=TRUE, transient=TRUE, just_qry=FALSE)



## --------------------------- --------------------------- ##
##               ---------------------------               ##
## --------------------------- --------------------------- ##
##
##     TODO : 
##
##     Join in Column for *track_id* that have more than one UPC+ISRC combo to them
##
##
## --------------------------- --------------------------- ##
## --------------------------- --------------------------- ##
## There are about 35+ trackid's that have more than one UPC+ISRC to them
## These should be flagged
qry.track_ids_with_more_than_one_upc_isrc_combo <- setQry("
SELECT track_id, 1 AS has_multiple_upc_isrc_combo_per_track_id
FROM (
  SELECT   
      upc
    , isrc
    , track_id
  FROM  production.staging_raw_spotify_v2
  WHERE TMSTAMP::date >= '2015-05-04'
  GROUP BY 1, 2, 3
)
GROUP BY 1
HAVING count(*) > 1")
## ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~