# flake8: noqa
# query clarifications:
# https://www.notion.so/Grps-question-clarifications-30c97177520f80b3b0e7c83ca0a0cbfc
# Grps db doc:
# https://www.notion.so/GRPS-feed-via-DDEX-to-ingest-SMEAnalyticsDummy-products-1e597177520f80edbd64cea58b93c309#1e597177520f80c8a9eaf3a6d42a245c


from common.constants.artist_role import PRIMARY_ARTIST, FEATURED_ARTIST

RELEASE_DATE_OFFSET_DAYS = 1
SINGLE_RELEASE_TYPE = 'Single'
FULL_LENGTH_RELEASE_TYPE = 'Full Length'


GET_PRODUCT_DATA_BY_UPC = f"""
WITH product AS (
    SELECT *
    FROM GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS001 AS product
    WHERE NVL(product.digital_barcode, product.barcode) = %(upc)s
      AND prod_no = %(prod_no)s
)
SELECT
    NVL(product.digital_barcode, product.barcode) AS upc,
    COALESCE(product_version_localization.loclan_title, product_version.prod_title) AS product_name,
    grid_no,
    MIN(COALESCE(
        TRY_TO_DATE(product.first_release_date, 'YYYYMMDD'),
        TRPS033.first_functional_date,
        TRPS064.release_date
    )) AS release_date,
    MIN(COALESCE(
        TRPS033.pre_order_date,
        TRPS033.first_functional_date
    )) AS sale_start_date,
    IFF(product_version.config_cat_key = 'D', '{SINGLE_RELEASE_TYPE}', '{FULL_LENGTH_RELEASE_TYPE}') AS release_type,
    product.prod_no,
    CASE 
        WHEN configuration.media_type_key  in (1, 3) THEN 'AUDIO'
        WHEN configuration.media_type_key  in (2, 4) THEN 'VIDEO'
    END as product_type  
FROM product
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS002 AS product_version
        ON product.prod_vers_no = product_version.prod_vers_no
        AND product.client_key = product_version.client_key
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS025 AS configuration
        ON configuration.config_key = product.config_key
        AND configuration.client_key = product.client_key        
    LEFT JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRPS001.TRPS033
        ON TRPS033.prod_no = product.prod_no
        AND TRPS033.client_key = product.client_key
        AND TRPS033.mod_flag != 'D'
        AND NOT TRPS033._FIVETRAN_DELETED
    LEFT JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRPS001.TRPS064
        ON TRPS033.mo_digital_main_prod_id = TRPS064.mo_digital_main_prod_id
        AND TRPS064.mod_flag != 'D'
        AND NOT TRPS064._FIVETRAN_DELETED
    LEFT JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS396 product_version_localization
        ON product_version_localization.prod_vers_no = product_version.prod_vers_no
        AND product_version_localization.client_key = product_version.client_key
        AND product_version_localization.loclan_is_native_flag = 'Y'
        AND product_version_localization.mod_flag != 'D'
        AND NOT product_version_localization._FIVETRAN_DELETED
WHERE
    product_version.mod_flag != 'D'
    AND NOT product_version._FIVETRAN_DELETED
GROUP BY ALL
"""

GET_PRODUCT_PARTICIPANTS_BY_UPC = f"""
WITH product AS (
    SELECT *
    FROM GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS001 AS product
    WHERE NVL(product.digital_barcode, product.barcode) = %(upc)s
      AND prod_no = %(prod_no)s
)
SELECT
    participant.particip_full_name,
    participant.particip_spotify_uri,
    participant.apple_artist_id,
    '{PRIMARY_ARTIST}' AS role
FROM product
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS002 AS product_version
        ON product.prod_vers_no = product_version.prod_vers_no
        AND product.client_key = product_version.client_key
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS006 AS participant
        ON participant.particip_no = product_version.MAIN_ARTIST_NO
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS092 AS participant_type
        ON participant.PARTICIP_TYPE_KEY = participant_type.PARTICIP_TYPE_KEY
WHERE participant_type.PARTICIP_TYPE_NAME IN ('Group', 'Individual', 'Other')
  AND product_version.mod_flag != 'D'
  AND NOT product_version._FIVETRAN_DELETED
  AND participant.mod_flag != 'D'
  AND NOT participant._FIVETRAN_DELETED
  AND participant_type.mod_flag != 'D'
  AND NOT participant_type._FIVETRAN_DELETED

UNION

SELECT
    compound_participant.particip_full_name,
    compound_participant.particip_spotify_uri,
    compound_participant.apple_artist_id,
    IFF(participant_member.member_type_key = 5, '{PRIMARY_ARTIST}', '{FEATURED_ARTIST}') AS role
FROM product
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS002 AS product_version
        ON product.prod_vers_no = product_version.prod_vers_no
        AND product.client_key = product_version.client_key
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS006 AS participant
        ON participant.particip_no = product_version.MAIN_ARTIST_NO
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS092 AS participant_type
        ON participant.PARTICIP_TYPE_KEY = participant_type.PARTICIP_TYPE_KEY
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS094 AS participant_member
        ON participant_member.PARTICIP_NO = participant.PARTICIP_NO
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS006 AS compound_participant
        ON compound_participant.particip_no = participant_member.MEMBER_PARTICIP_NO
WHERE participant_type.PARTICIP_TYPE_NAME = 'Compound Artist'
  AND participant_member.is_active_member = 'Y'
  AND participant_member.member_type_key IN (5, 6, 7, 8, 9)
  AND compound_participant.mod_flag != 'D'
  AND NOT compound_participant._FIVETRAN_DELETED
  AND participant_member.mod_flag != 'D'
  AND NOT participant_member._FIVETRAN_DELETED
  AND participant_type.mod_flag != 'D'
  AND NOT participant_type._FIVETRAN_DELETED
  AND participant.mod_flag != 'D'
  AND NOT participant._FIVETRAN_DELETED
  AND product_version.mod_flag != 'D'
  AND NOT product_version._FIVETRAN_DELETED
;
"""

GET_PRODUCT_TRACKS_BY_UPC = f"""
WITH product AS (
    SELECT *
    FROM GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS001 AS product
    WHERE NVL(product.digital_barcode, product.barcode) = %(upc)s
      AND prod_no = %(prod_no)s
)
SELECT
    track.isrc,
    COALESCE(asset_localization.loclan_title, song.track_name) AS track_name,
    IFF(track.lyrics_indicator_key = 2, 'Y', 'N') AS explicit,
    track.play_time,
    product_track.seq_no,
    product_track.id_no,
    CASE WHEN product.config_key IN ('91', 'B5', 'D1', 'F4') THEN
        COALESCE(product_localization.loclan_digital_title_suppl, product.digital_title_suppl)
    ELSE
        track.track_name_suppl
    END AS version
FROM product
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS009 AS product_track
        ON product.prod_no = product_track.prod_no
        AND product.client_key = product_track.client_key
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS007 AS track
        ON product_track.track_no = track.track_no
        AND product_track.track_ext = track.track_ext
        AND product_track.client_key = track.client_key
    LEFT JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS398 AS asset_localization
        ON asset_localization.track_no = track.track_no
        AND asset_localization.track_ext = track.track_ext
        AND asset_localization.client_key = track.client_key
        AND asset_localization.loclan_is_native_flag = 'Y'
        AND asset_localization.mod_flag != 'D'
        AND NOT asset_localization._FIVETRAN_DELETED
    LEFT JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS370 AS song
        ON song.song_no = track.song_no
        AND song.mod_flag != 'D'
        AND NOT song._FIVETRAN_DELETED
    LEFT JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS395 product_localization
        ON product_localization.prod_no = product.prod_no
        AND product_localization.client_key = product.client_key
        AND product_localization.loclan_is_native_flag = 'Y'
        AND product_localization.mod_flag != 'D'
        AND NOT product_localization._FIVETRAN_DELETED
WHERE
    track.isrc IS NOT NULL
    AND 
        (
         TRY_TO_DATE(COALESCE(track.orig_release_date, product.first_release_date), 'YYYYMMDD') <= DATEADD(day, {RELEASE_DATE_OFFSET_DAYS}, current_timestamp())
            OR
         %(release_type)s = '{SINGLE_RELEASE_TYPE}' 
         -- ignore track.orig_release_date for singles
         -- also ok to ignore product.first_release_date as product is scheduled to ingest so release date meat criteria 
         ) 
    AND track.mod_flag != 'D'
    AND NOT track._FIVETRAN_DELETED
    AND product_track.mod_flag != 'D'
    AND NOT product_track._FIVETRAN_DELETED;
"""

GET_PRODUCT_TRACKS_PARTICIPANTS_BY_UPC_ISRC = f"""
WITH product AS (
    SELECT *
    FROM GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS001 AS product
    WHERE NVL(product.digital_barcode, product.barcode) = %(upc)s
        AND prod_no = %(prod_no)s
)
SELECT
    track_participant.is_main_artist,
    track_participant.is_primary_artist,
    participant.particip_full_name,
    participant.particip_spotify_uri,
    participant.apple_artist_id,
    participant.particip_no,
    track.isrc,
    participant_type.PARTICIP_TYPE_NAME,
    '{PRIMARY_ARTIST}' AS role
FROM product
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS009 AS product_track
        ON product.prod_no = product_track.prod_no
        AND product.client_key = product_track.client_key
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS007 AS track
        ON product_track.track_no = track.track_no
        AND product_track.track_ext = track.track_ext
        AND product_track.client_key = track.client_key
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS011 AS track_participant
        ON track_participant.track_no = track.track_no
        AND track_participant.track_ext = track.track_ext
        AND track_participant.client_key = track.client_key
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS006 AS participant
        ON participant.client_key = track_participant.client_key
        AND participant.particip_no = track_participant.particip_no
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS092 AS participant_type
        ON participant.PARTICIP_TYPE_KEY = participant_type.PARTICIP_TYPE_KEY
WHERE participant_type.PARTICIP_TYPE_NAME IN ('Group', 'Individual' , 'Other')
    AND track.isrc = %(isrc)s
    AND track_participant.is_main_artist = 'Y'
    AND participant_type.mod_flag != 'D'
    AND NOT participant_type._FIVETRAN_DELETED   
    AND participant.mod_flag != 'D'
    AND NOT participant._FIVETRAN_DELETED    
    AND track_participant.mod_flag != 'D'
    AND NOT track_participant._FIVETRAN_DELETED 
    AND track.mod_flag != 'D'
    AND NOT track._FIVETRAN_DELETED    
    AND product_track.mod_flag != 'D'
    AND NOT product_track._FIVETRAN_DELETED              

UNION

SELECT
    track_participant.is_main_artist,
    track_participant.is_primary_artist,
    compound_participant.particip_full_name,
    compound_participant.particip_spotify_uri,
    compound_participant.apple_artist_id,
    compound_participant.particip_no,
    track.isrc,
    participant_type.PARTICIP_TYPE_NAME,
    IFF(participant_member.member_type_key = 5, '{PRIMARY_ARTIST}', '{FEATURED_ARTIST}') AS role
FROM product
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS009 AS product_track
        ON product.prod_no = product_track.prod_no
        AND product.client_key = product_track.client_key     
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS007 AS track
        ON product_track.track_no = track.track_no
        AND product_track.track_ext = track.track_ext
        AND product_track.client_key = track.client_key            
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS011 AS track_participant
        ON track_participant.track_no = track.track_no
        AND track_participant.track_ext = track.track_ext
        AND track_participant.client_key = track.client_key               
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS006 AS participant
        ON participant.client_key = track_participant.client_key
        AND participant.particip_no = track_participant.particip_no                 
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS092 AS participant_type
        ON participant.PARTICIP_TYPE_KEY = participant_type.PARTICIP_TYPE_KEY
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS094 AS participant_member
        ON participant_member.PARTICIP_NO = participant.PARTICIP_NO
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS006 AS compound_participant
        ON compound_participant.particip_no = participant_member.MEMBER_PARTICIP_NO
WHERE participant_type.PARTICIP_TYPE_NAME = 'Compound Artist'
    AND track.isrc = %(isrc)s
    AND product_track.mod_flag != 'D'
    AND NOT product_track._FIVETRAN_DELETED   
    AND participant.mod_flag != 'D'
    AND NOT participant._FIVETRAN_DELETED 
    AND track_participant.mod_flag != 'D'
    AND NOT track_participant._FIVETRAN_DELETED 
    AND track.mod_flag != 'D'
    AND NOT track._FIVETRAN_DELETED   
    AND participant_type.mod_flag != 'D'
    AND NOT participant_type._FIVETRAN_DELETED  
    AND participant_member.mod_flag != 'D'
    AND NOT participant_member._FIVETRAN_DELETED    
    AND compound_participant.mod_flag != 'D'
    AND NOT compound_participant._FIVETRAN_DELETED       
    AND track_participant.is_main_artist = 'Y'
    AND participant_member.member_type_key IN (5, 6, 7, 8, 9);
"""

GET_PROJECT_BY_PRODUCT_UPC = """
WITH product AS (
    SELECT *
    FROM GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS001 AS product
    WHERE NVL(product.digital_barcode, product.barcode) = %(upc)s
      AND prod_no = %(prod_no)s
)
SELECT
    rec_project.rec_project_title,
    rec_project.rec_project_number,
    rec_project.rec_project_id
FROM GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS360 AS rec_project
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS364 AS recording_project_product_version
        ON rec_project.rec_project_id = recording_project_product_version.rec_project_id
    JOIN product
        ON product.prod_vers_no = recording_project_product_version.prod_vers_no
        AND product.client_key = recording_project_product_version.client_key
WHERE
    rec_project.mod_flag != 'D'
    AND NOT rec_project._FIVETRAN_DELETED
    AND recording_project_product_version.mod_flag != 'D'
    AND NOT recording_project_product_version._FIVETRAN_DELETED
"""

GET_PROJECT_PARTICIPANT_BY_REC_PROJECT_ID = """
SELECT
    participant.particip_full_name,
    participant.particip_spotify_uri,
    participant.apple_artist_id
FROM GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS361 AS recording_project_participant
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS006 AS participant
        ON participant.particip_no = recording_project_participant.particip_no
        AND participant.client_key = recording_project_participant.client_key
WHERE recording_project_participant.rec_project_id = %(rec_project_id)s
    AND recording_project_participant.IS_PROD_PRIMARY_ARTIST = 'Y'
    AND recording_project_participant.mod_flag != 'D'
    AND NOT recording_project_participant._FIVETRAN_DELETED
    AND participant.mod_flag != 'D'
    AND NOT participant._FIVETRAN_DELETED
ORDER BY SEQ_NO ASC;
"""

GET_PARENT_PARENT_AND_REP_OWNER_KEY_BY_UPC = """
WITH product AS (
    SELECT *
    FROM GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS001 AS product
    WHERE NVL(product.digital_barcode, product.barcode) = %(upc)s
        AND prod_no = %(prod_no)s
), parent_rep_owner_remapping as (
SELECT rep_owner_parent_cd, rep_owner_cd  
FROM SONY_INTERNAL.DEV.REP_OWNER_HIERARCHY
WHERE LENGTH(TRIM(rep_owner_parent_cd)) = 4
    AND LOWER(rep_owner_parent_nm) != 'unknown'
) 
SELECT
    product_version.rep_owner_key,
    company.company_name,
    coalesce(rep_owner_parent_cd, parent_repertoire_owner.parent_rep_owner_key) as parent_rep_owner_key
FROM product
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS002 AS product_version
        ON product.prod_vers_no = product_version.prod_vers_no
        AND product.client_key = product_version.client_key
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS111 AS repertoire_owner
        ON repertoire_owner.REP_OWNER_KEY = product_version.rep_owner_key
        AND repertoire_owner.client_key = product_version.client_key
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS112 AS profit_center
        ON repertoire_owner.profit_center_key = profit_center.profit_center_key
        AND repertoire_owner.client_key = profit_center.client_key
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS113 AS parent_repertoire_owner
        ON parent_repertoire_owner.parent_rep_owner_key = profit_center.parent_rep_owner_key
        AND parent_repertoire_owner.client_key = profit_center.client_key
    LEFT JOIN parent_rep_owner_remapping 
        ON parent_rep_owner_remapping.rep_owner_cd = product_version.rep_owner_key
    LEFT JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS024 AS company
        ON company.company_key = repertoire_owner.rep_owner_key        
WHERE
    product_version.mod_flag != 'D'
    AND NOT product_version._FIVETRAN_DELETED
    AND repertoire_owner.mod_flag != 'D'
    AND NOT repertoire_owner._FIVETRAN_DELETED
    AND profit_center.mod_flag != 'D'
    AND NOT profit_center._FIVETRAN_DELETED
    AND parent_repertoire_owner.mod_flag != 'D'
    AND NOT parent_repertoire_owner._FIVETRAN_DELETED
"""

GET_LABEL_BY_UPC = """
WITH product AS (
    SELECT *
    FROM GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS001 AS product
    WHERE NVL(product.digital_barcode, product.barcode) = %(upc)s
      AND prod_no = %(prod_no)s
)
SELECT
    label.label_name
FROM product
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS002 AS product_version
        ON product.prod_vers_no = product_version.prod_vers_no
        AND product.client_key = product_version.client_key
    JOIN GRPS_APP_REPORTING_NOCONF.GRPS_DRAS002.TRAS016 AS label
        ON product_version.label_key = label.label_key
        AND product_version.client_key = label.client_key
        AND product_version.label_ext = label.label_ext
WHERE
    product_version.mod_flag != 'D'
    AND NOT product_version._FIVETRAN_DELETED
    AND label.mod_flag != 'D'
    AND NOT label._FIVETRAN_DELETED
"""

GET_VENDOR_AND_SUBACCOUNT_BY_PARENT_AND_REP_OWNER_KEY = """
SELECT
    vendor_id,
    subaccount_id,
    do_not_ingest
FROM ORCHARD_APP_REPORTING_V2.PROD_DDEX_INGESTER_DDEX_INGESTER.INBOUND_MAJOR_LABEL_MAPPING
WHERE parent_repertoire_owner_code = %(parent_rep_owner_key)s
  AND repertoire_owner_code = %(rep_owner_key)s
"""

GET_ART_RELATIONS_PRODUCT_BY_PRODUCT_CODE = """
SELECT
    product_code,
    upc
FROM orchard_app_reporting_v2.art_relations_prod_art_relations.releases
WHERE product_code = %(product_code)s;
"""