'''
This file contains all the queries needed to run the workflow.
'''

from config import *

query_base = """
SELECT
    RELEASE.UPC
  , DELIVERY.APPROVED
  , DELIVERY.PROCESS_DATE --NEW
  , TO_DATE(RELEASE.DATE_ADDED) AS DATE_ADDED -- NEW
  , DELIVERY.NOTE AS NOTE -- NEW
  , RELEASE.LABELID
  , ARTIST.ARTISTNAME AS ARTISTNAME
  --, RELEASE.NOT_FOR_DISTROBUTION --V2 GET THIS INTO SNOWFLAKE FIRST
  --, RELEASE.AUDIO_OR_VIDEO       --V2 GET THIS INTO SNOWFLAKE FIRST
  , CASE WHEN ARTIST.releases_per_artist IS NULL THEN 0 
         ELSE ARTIST.releases_per_artist 
         END AS RELEASES_PER_ARTIST
  , CASE WHEN ARTIST.hides_per_artist IS NULL THEN 0 
         ELSE ARTIST.hides_per_artist 
         END AS HIDES_PER_ARTIST
  , RELEASE.RELEASENAME as RELEASENAME
  , CASE WHEN RELEASE.units_streaming_LABEL IS NULL THEN 0 
         ELSE RELEASE.units_streaming_LABEL 
         END AS UNITS_STREAMING_LABEL
  , CASE WHEN RELEASE.RELEASES_PER_LABEL IS NULL THEN 0 
         ELSE RELEASE.RELEASES_PER_LABEL 
         END AS RELEASES_PER_LABEL
  , RELEASE.GENREID
  , RELEASE.ARTISTID
  , CASE WHEN TRACK.ISRC_PER_RELEASE IS NULL THEN 0 
         ELSE TRACK.ISRC_PER_RELEASE 
         END AS ISRC_PER_RELEASE
  , CASE WHEN TRACK.AVERAGE_TRACK_DURATION IS NULL THEN 0 
         ELSE TRACK.AVERAGE_TRACK_DURATION 
         END AS AVERAGE_TRACK_DURATION
  , CASE WHEN RELEASE.DELETIONS='Y' THEN TRUE 
         ELSE FALSE 
         END AS IS_DELETED
  , CASE WHEN RELEASE.COMPILATION='Y' THEN TRUE 
         ELSE FALSE 
         END AS IS_COMPILATION_X
  , DATEDIFF(YEAR, RELEASE.RELEASEDATE, CURRENT_TIMESTAMP()) AS YEARS_SINCE_RELEASE
  , TO_DATE(RELEASE.RELEASEDATE) AS RELEASE_DATE
  , CASE WHEN BLACKLIST.IS_BLACKLISTED = 1 THEN TRUE 
         ELSE FALSE 
         END AS IS_BLACKLISTED
  , CASE WHEN VA.IS_VARIOUS_ARTIST='Y' THEN TRUE 
         ELSE FALSE 
         END AS IS_VARIOUS_ARTIST
FROM  (
  SELECT 
      dr.*
    , DR.RELEASEID AS UPC
    , units_LABEL.RELEASES_PER_LABEL
    , units_LABEL.units_streaming AS UNITS_STREAMING_LABEL
--    , UNITS_RELEASE.UNITS_STREAMING AS UNITS_STREAMING_RELEASE
  FROM
  PROD.PRODUCTION.DIM_RELEASE dr
  left join (
    SELECT 
        dr.labelid
      , agg.units_streaming
      , dr.RELEASES_PER_LABEL
    from (
      select
          analytics_aggregated.labelid AS labelid
        , COALESCE(
            SUM(
              CASE WHEN (analytics_aggregated.transac_type_abbr = 'AS' 
              OR analytics_aggregated.transac_type_abbr = 'S') 
              THEN analytics_aggregated.units ELSE NULL END
            ), 0) AS units_streaming
      
      FROM prod.BI.ANALYTICS_AGGREGATED AS analytics_aggregated
      WHERE analytics_aggregated.aggregate='Label Level'
      and storeid = 286
      and activity_date <= DATEADD(day, -60, TO_DATE(current_timestamp()))
      GROUP BY 1) as agg
    
    left join (
      select 
          labelid
        , count(*) as RELEASES_PER_LABEL
      from prod.production.dim_release
      group by 1  
    ) dr
    on dr.labelid = agg.labelid
    ) as units_LABEL
  ON units_LABEL.labelid = dr.labelid
) RELEASE

JOIN (
  select 
      upc
    , avg(duration) AS AVERAGE_TRACK_DURATION
    , count(distinct isrc) AS ISRC_PER_RELEASE
  from prod.production.dim_track
  group by 1
) TRACK
ON RELEASE.UPC = TRACK.UPC

-- artist meta
LEFT JOIN (
    SELECT 
        da.*
      , dr.releases_per_artist
      , hides.artist_hides AS hides_per_artist
    FROM PROD.PRODUCTION.DIM_ARTIST da
    
    -- releases
    left join (
      select 
          artistid
        , count(*) as releases_per_artist
      from prod.production.dim_release
      group by 1
    ) as dr
    on dr.artistid = da.artistid
    
    -- hides
    left join (
      select
          artistid
        , count(distinct apple_id) as artist_hides
      from prod.hidden_content.itunes_hidden_view
      group by 1
    ) hides
    on hides.artistid = da.artistid
) ARTIST
ON ARTIST.ARTISTID = RELEASE.ARTISTID

-- blacklist artists
LEFT JOIN PROD.BI.ARTIST_BLACKLIST BLACKLIST
ON BLACKLIST.ARTISTID = RELEASE.ARTISTID

-- various artists
LEFT JOIN  PROD.BI.VARIOUS_ARTISTS VA
ON RELEASE.ARTISTID = VA.ARTISTID

"""

# Initial SQL-pull
query_full_cat = """
SELECT
    RELEASE.UPC
  , DELIVERY.APPROVED
  , DELIVERY.PROCESS_DATE --NEW
  , TO_DATE(RELEASE.DATE_ADDED) AS DATE_ADDED -- NEW
  , DELIVERY.NOTE AS NOTE -- New
  , DELIVERY.MODEL_YES_PROB AS MODEL_YES_PROB -- NEW
  , RELEASE.LABELID
  --, RELEASE.NOT_FOR_DISTROBUTION --V2 GET THIS INTO SNOWFLAKE FIRST
  --, RELEASE.AUDIO_OR_VIDEO       --V2 GET THIS INTO SNOWFLAKE FIRST
  , ARTIST.ARTISTNAME AS ARTISTNAME
  , CASE WHEN ARTIST.releases_per_artist IS NULL THEN 0 
         ELSE ARTIST.releases_per_artist 
         END AS RELEASES_PER_ARTIST
  , CASE WHEN ARTIST.hides_per_artist IS NULL THEN 0 
         ELSE ARTIST.hides_per_artist 
         END AS HIDES_PER_ARTIST
  , RELEASE.RELEASENAME as RELEASENAME
  , CASE WHEN RELEASE.units_streaming_LABEL IS NULL THEN 0 
         ELSE RELEASE.units_streaming_LABEL 
         END AS UNITS_STREAMING_LABEL
  , CASE WHEN RELEASE.RELEASES_PER_LABEL IS NULL THEN 0 
         ELSE RELEASE.RELEASES_PER_LABEL 
         END AS RELEASES_PER_LABEL
  , RELEASE.GENREID
  , RELEASE.ARTISTID
  , CASE WHEN TRACK.ISRC_PER_RELEASE IS NULL THEN 0 
         ELSE TRACK.ISRC_PER_RELEASE 
         END AS ISRC_PER_RELEASE
  , CASE WHEN TRACK.AVERAGE_TRACK_DURATION IS NULL THEN 0 
         ELSE TRACK.AVERAGE_TRACK_DURATION 
         END AS AVERAGE_TRACK_DURATION
  , CASE WHEN RELEASE.DELETIONS='Y' THEN TRUE 
         ELSE FALSE 
         END AS IS_DELETED
  , CASE WHEN RELEASE.COMPILATION='Y' THEN TRUE 
         ELSE FALSE 
         END AS IS_COMPILATION_X
  , DATEDIFF(YEAR, RELEASE.RELEASEDATE, CURRENT_TIMESTAMP()) AS YEARS_SINCE_RELEASE
  , TO_DATE(RELEASE.RELEASEDATE) AS RELEASE_DATE
  , CASE WHEN BLACKLIST.IS_BLACKLISTED = 1 THEN TRUE 
         ELSE FALSE 
         END AS IS_BLACKLISTED
  , CASE WHEN VA.IS_VARIOUS_ARTIST='Y' THEN TRUE 
         ELSE FALSE 
         END AS IS_VARIOUS_ARTIST
FROM  (
  SELECT 
      dr.*
    , DR.RELEASEID AS UPC
    , units_LABEL.RELEASES_PER_LABEL
    , units_LABEL.units_streaming AS UNITS_STREAMING_LABEL
--    , UNITS_RELEASE.UNITS_STREAMING AS UNITS_STREAMING_RELEASE
  FROM
  PROD.PRODUCTION.DIM_RELEASE dr
  left join (
    SELECT 
        dr.labelid
      , agg.units_streaming
      , dr.RELEASES_PER_LABEL
    from (
      select
          analytics_aggregated.labelid AS labelid
        , COALESCE(
            SUM(
              CASE WHEN (analytics_aggregated.transac_type_abbr = 'AS' 
              OR analytics_aggregated.transac_type_abbr = 'S') 
              THEN analytics_aggregated.units ELSE NULL END
            ), 0) AS units_streaming
      
      FROM prod.BI.ANALYTICS_AGGREGATED AS analytics_aggregated
      WHERE analytics_aggregated.aggregate='Label Level'
      and storeid = 286
      and activity_date <= DATEADD(day, -60, TO_DATE(current_timestamp()))
      GROUP BY 1) as agg
    
    left join (
      select 
          labelid
        , count(*) as RELEASES_PER_LABEL
      from prod.production.dim_release
      group by 1  
    ) dr
    on dr.labelid = agg.labelid
    ) as units_LABEL
  ON units_LABEL.labelid = dr.labelid
) RELEASE

JOIN (
  select 
      upc
    , avg(duration) AS AVERAGE_TRACK_DURATION
    , count(distinct isrc) AS ISRC_PER_RELEASE
  from prod.production.dim_track
  group by 1
) TRACK
ON RELEASE.UPC = TRACK.UPC

-- artist meta
LEFT JOIN (
    SELECT 
        da.*
      , dr.releases_per_artist
      , hides.artist_hides AS hides_per_artist
    FROM PROD.PRODUCTION.DIM_ARTIST da
    
    -- releases
    left join (
      select 
          artistid
        , count(*) as releases_per_artist
      from prod.production.dim_release
      group by 1
    ) as dr
    on dr.artistid = da.artistid
    
    -- hides
    left join (
      select
          artistid
        , count(distinct apple_id) as artist_hides
      from prod.hidden_content.itunes_hidden_view
      group by 1
    ) hides
    on hides.artistid = da.artistid
) ARTIST
ON ARTIST.ARTISTID = RELEASE.ARTISTID

-- blacklist artists
LEFT JOIN PROD.BI.ARTIST_BLACKLIST BLACKLIST
ON BLACKLIST.ARTISTID = RELEASE.ARTISTID

-- various artists
LEFT JOIN  PROD.BI.VARIOUS_ARTISTS VA
ON RELEASE.ARTISTID = VA.ARTISTID

-- Already reviewed getting all releases
LEFT JOIN (
  select *
  from prod.delivery_review.youtube_delivery_history DD
  WHERE MODEL_YES_PROB IS NOT NULL
) DELIVERY
ON DELIVERY.UPC = RELEASE.UPC

WHERE YEARS_SINCE_RELEASE IS NOT NULL
  AND RELEASE.UPC != 1"""

query_train = query_base + """
-- Already reviewed
INNER JOIN (
  select *
  from prod.delivery_review.youtube_delivery_history DD
  WHERE MODEL_YES_PROB IS NOT NULL
) DELIVERY
ON DELIVERY.UPC = RELEASE.UPC

WHERE YEARS_SINCE_RELEASE IS NOT NULL
  AND RELEASE.UPC != 1
"""

# these unions statements are to include releases of each genre id.
# this is important during matrix spacifying (one-hot encoding)
# so the model input has consistent dimensions.
# think about if the new releases were all of two genres...
genre_subquery = '''
UNION

(select RELEASEID AS upc 
      , 'NO' AS APPROVED
      , '99' AS NOTES
      , to_date('2017-01-01') AS PROCESS_DATE
      , 0 AS MODEL_YES_PROB
from prod.production.dim_release
WHERE GENREID = {id}
limit 1)
'''

genre_name = 'select genreid, genrename from prod.production.dim_genre'

genre_query = ''

for g in genre_ids:
    genre_query += genre_subquery.format(id=g)

query_new_reviews = query_base + """
-- Only getting the new releases
INNER JOIN (
  (select *
   from prod.delivery_review.youtube_delivery_history
   WHERE MODEL_YES_PROB IS NULL)
   
   {genre}

) DELIVERY
ON DELIVERY.UPC = RELEASE.UPC

WHERE YEARS_SINCE_RELEASE IS NOT NULL
  AND RELEASE.UPC != 1
""".format(genre=genre_query)

insert_results = ('INSERT INTO {prod} '
                  "SELECT *, TO_DATE('{{run_date}}') AS PROCESS_DATE, "
                  'NULL as MODEL_YES_PROB '
                  'FROM {stg} '.format(prod=sf_results_delivered,
                                       stg=sf_results_stg))

batch_update_pred = ('UPDATE {a} A '
                     'SET A.MODEL_YES_PROB = B.MODEL_YES_PROB '
                     'FROM {b} B '
                     'WHERE A.UPC=B.UPC'.format(a=sf_results_delivered, 
                                                b=sf_machine_v_human))

delete_dupes = ('DELETE FROM {prod} '
                'WHERE UPC in (select UPC from {stg})'.format(
                                            prod=sf_results_delivered, 
                                            stg=sf_results_stg))

clear_meta = ('DELETE FROM {sf_meta} '
              "WHERE PROCESS_DATE = to_date('{{run_date}}')".format(sf_meta=sf_meta))

insert_meta = 'INSERT INTO {sf_meta} VALUES {{tuple}}'.format(sf_meta=sf_meta)