def at1(label_id: int):
    """AT1) Label (The Orchard) Art Track Metadata
    Look: https://theorchard.looker.com/looks/20076
    """
    return f"""
        WITH delivery_history_snowflake_aggregated AS (select r.UPC
                        , delivered.customer_master_master_id
                        , max(delivered.date_delivered) as max_date_delivered
                        , min(delivered.date_delivered) as min_date_delivered
                        , CASE WHEN count(delivered.delivery_id)>0
                              THEN 'Delivered'
                              Else 'Not Delivered'
                              END AS delivered_status
                        ,count(delivery_id) as count_deliveries
                from orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
                LEFT JOIN (select delivery_id
                            , UPC
                            , date_delivered
                            , customer_master_master_id
                      from orchard_app_reporting_v2.art_relations_prod_art_relations.delivery_history
                      where 1=1 -- no filter on 'delivery_history_snowflake_aggregated.encoder_id_filter'

                      and 1=1 -- no filter on 'delivery_history_snowflake_aggregated.filter_storeid'

                          ) delivered
                            on delivered.upc=r.upc
                group by 1, 2
                  )
        SELECT
            releases_snowflake.upc  AS "releases_snowflake.upc",
            track_snowflake.isrc  AS "track_snowflake.isrc",
            track_snowflake.track_name  AS "track_snowflake.track_name",
            artist_info_snowflake.name  AS "artist_info_snowflake.name",
            releases_snowflake.release_name  AS "releases_snowflake.release_name",
            genre_snowflake.genre  AS "genre_snowflake.genre",
            (ZEROIFNULL(track_snowflake.length_minute) * 60) + ZEROIFNULL(track_snowflake.length_seconds) AS "track_snowflake.duration",
            IFF(vendor_snowflake.company IS NULL OR vendor_snowflake.company  = '',  vendor_snowflake.name, vendor_snowflake.company)
               AS "vendor_snowflake.vendor_name",
                (releases_snowflake.release_status='in_content'
                      AND releases_snowflake.deletions!='Y'
                      AND releases_snowflake.not_for_distribution='N') AS "releases_snowflake.is_active_release",
            delivery_history_snowflake_aggregated.delivered_status  AS "delivery_history_snowflake_aggregated.delivered_status"
        FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.RELEASES  AS releases_snowflake
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info  AS artist_info_snowflake ON releases_snowflake.artist_id=artist_info_snowflake.artist_id
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.distribution_format  AS distribution_format_snowflake ON distribution_format_snowflake.distribution_format_id=releases_snowflake.distribution_format_id
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vendor  AS vendor_snowflake ON vendor_snowflake.vendor_id = artist_info_snowflake.vendor_id
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.genre  AS genre_snowflake ON genre_snowflake.genre_id=releases_snowflake.genre_id
        LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.TRACK  AS track_snowflake ON track_snowflake.release_id=releases_snowflake.release_id
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.delivery_history  AS delivery_history_snowflake ON releases_snowflake.upc= delivery_history_snowflake.upc
        LEFT JOIN delivery_history_snowflake_aggregated ON delivery_history_snowflake_aggregated.upc=releases_snowflake.upc
        WHERE (vendor_snowflake.vendor_id ) = {label_id}
        AND (delivery_history_snowflake.customer_master_master_id ) = 569
        AND ((UPPER(( track_snowflake.track_type  )) = UPPER('music')))
        AND ((UPPER(( distribution_format_snowflake.context_type  )) = UPPER('digital')))
        GROUP BY
            1,
            2,
            3,
            4,
            5,
            6,
            7,
            8,
            9,
            10
        """


def at2(label_id: int):
    """AT2) Label (The Orchard) YT Carve-Outs
    Look: https://theorchard.looker.com/looks/20080
    """
    return f"""
        WITH deliverable_digital_release_snowflake AS (
          SELECT
            r.release_id,
            IFNULL(restrict_vendor_master.yes, TRUE)
              AND IFNULL(restrict_subaccount_master.yes, TRUE)
              AND IFNULL(restrict_release_master.yes, TRUE)
              AS is_deliverable_release_store,
            IFNULL(restrict_vendor_territory.yes, TRUE)
              AND IFNULL(restrict_subaccount_territory.yes, TRUE)
              AND IFNULL(restrict_release_territory.yes, TRUE)
              AS is_deliverable_release_territory,
            IFNULL(restrict_vendor_master.yes, TRUE)
              AND IFNULL(restrict_vendor_dms.yes, TRUE)
              AND IFNULL(restrict_vendor_territory.yes, TRUE)
              AND IFNULL(restrict_subaccount_master.yes, TRUE)
              AND IFNULL(restrict_subaccount_dms.yes, TRUE)
              AND IFNULL(restrict_subaccount_territory.yes, TRUE)
              AND IFNULL(restrict_release_master.yes, TRUE)
              AND IFNULL(restrict_release_dms.yes, TRUE)
              AND IFNULL(restrict_release_territory.yes, TRUE)
              AS is_deliverable_release_storeterritory
          FROM orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
            INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info ai ON r.artist_id = ai.artist_id
            INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vendor v ON ai.vendor_id = v.vendor_id
            INNER JOIN intelligence.prod.vw_active_vendor_contract vw ON v.vendor_id = vw.vendor_id
            LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount sa ON v.vendor_id = sa.vendor_id AND r.subaccount_id = sa.subaccount_id
    -- 1. CHECK RESTRICTIONS (VENDOR)
    -- 1A. STORE
            LEFT JOIN (
                SELECT vc.id, FALSE AS yes
                FROM orchard_app_reporting_v2.art_relations_prod_art_relations.vendor_contract vc
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vendor_dms_master_restriction vdmr ON vdmr.vendor_contract_id = vc.id
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master_master cmm ON vdmr.customer_master_master_id = cmm.customer_master_master_id
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.distribution_type dt ON vdmr.distribution_type_id = dt.id
                WHERE  (UPPER( cmm.customer_name ) = UPPER('YouTube Art Tracks & Music Videos'))

                AND    (UPPER( dt.name ) = UPPER('Full Track'))

            ) restrict_vendor_master ON restrict_vendor_master.id = vw.vendor_contract_id
    -- 1B. STORE/TERRITORY
            LEFT JOIN (
                SELECT vc_flatten.id, FALSE AS yes
                FROM (
                    SELECT id, TRY_TO_NUMBER(a.value::string) AS dms_id
                    FROM orchard_app_reporting_v2.art_relations_prod_art_relations.vendor_contract,
                    LATERAL FLATTEN(INPUT => SPLIT(dms_carve_out, ',')) a
                  ) vc_flatten
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master cm ON vc_flatten.dms_id = cm.customer_id
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master_master cmm ON cm.customer_master_master_id = cmm.customer_master_master_id
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c ON cm.territory = c.id
                WHERE  (UPPER( cmm.customer_name ) = UPPER('YouTube Art Tracks & Music Videos'))

                AND    (UPPER( c.country_code ) = UPPER('Full Track'))

              ) restrict_vendor_dms ON restrict_vendor_dms.id = vw.vendor_contract_id
    -- 1C. TERRITORY
            LEFT JOIN (
                SELECT vc_flatten.id, FALSE AS yes
                FROM (
                    SELECT id, TRY_TO_NUMBER(a.value::string) AS country_id
                    FROM orchard_app_reporting_v2.art_relations_prod_art_relations.vendor_contract,
                    LATERAL FLATTEN(INPUT => SPLIT(territory_carve_out, ',')) a
                  ) vc_flatten
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c ON vc_flatten.country_id = c.id
                WHERE  1=1 -- no filter on 'deliverable_digital_release_snowflake.territory'
              ) restrict_vendor_territory ON restrict_vendor_territory.id = vw.vendor_contract_id

    -- 2. CHECK RESTRICTIONS (SUBACCOUNT)
    -- 2A. STORE
            LEFT JOIN (
                SELECT sa.subaccount_id, FALSE AS yes
                FROM orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount sa
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount_dms_master_restriction sdmr ON sa.subaccount_id = sdmr.subaccount_id
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master_master cmm ON sdmr.customer_master_master_id = cmm.customer_master_master_id
                WHERE  (UPPER( cmm.customer_name ) = UPPER('YouTube Art Tracks & Music Videos'))

            ) restrict_subaccount_master ON restrict_subaccount_master.subaccount_id = sa.subaccount_id
    -- 2B. STORE/TERRITORY
            LEFT JOIN (
                SELECT sa.subaccount_id, FALSE AS yes
                FROM orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount sa
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount_dms_restriction sdr ON sa.subaccount_id = sdr.subaccount_id
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master cm ON sdr.dms_id = cm.customer_id
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master_master cmm ON cm.customer_master_master_id = cmm.customer_master_master_id
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c ON cm.territory = c.id
                WHERE  (UPPER( cmm.customer_name ) = UPPER('YouTube Art Tracks & Music Videos'))

                AND    (UPPER( c.country_code ) = UPPER('Full Track'))

              ) restrict_subaccount_dms ON restrict_subaccount_dms.subaccount_id = sa.subaccount_id
    -- 2C. TERRITORY
            LEFT JOIN (
                SELECT sa.subaccount_id, FALSE AS yes
                FROM orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount sa
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount_territory_restriction str ON sa.subaccount_id = str.subaccount_id
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c ON str.country_id = c.id
                WHERE  1=1 -- no filter on 'deliverable_digital_release_snowflake.territory'
              ) restrict_subaccount_territory ON restrict_subaccount_territory.subaccount_id = sa.subaccount_id

    -- 3. CHECK RESTRICTIONS (RELEASE)
    -- 3A. STORE
            LEFT JOIN (
                SELECT r.release_id, FALSE AS yes
                FROM orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.release_dms_master_restriction rdmr ON r.release_id = rdmr.release_id
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master_master cmm ON rdmr.customer_master_master_id = cmm.customer_master_master_id
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.distribution_type dt ON rdmr.distribution_type_id = dt.id
                WHERE  (UPPER( cmm.customer_name ) = UPPER('YouTube Art Tracks & Music Videos'))

                AND    (UPPER( dt.name ) = UPPER('Full Track'))

            ) restrict_release_master ON restrict_release_master.release_id = r.release_id
    -- 3B. STORE/TERRITORY
            LEFT JOIN (
                SELECT r.release_id, FALSE AS yes
                FROM orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.release_dms_restriction rdr ON r.release_id = rdr.release_id
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master cm ON rdr.dms_customer_id = cm.customer_id
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master_master cmm ON cm.customer_master_master_id = cmm.customer_master_master_id
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c ON cm.territory = c.id
                WHERE  (UPPER( cmm.customer_name ) = UPPER('YouTube Art Tracks & Music Videos'))

                AND    (UPPER( c.country_code ) = UPPER('Full Track'))

              ) restrict_release_dms ON restrict_release_dms.release_id = r.release_id
    -- 3C. TERRITORY
            LEFT JOIN (
                SELECT r.release_id, FALSE AS yes
                FROM orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.release_territory_restriction rtr ON r.release_id = rtr.release_id
                  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c ON rtr.country_id = c.id
                WHERE  1=1 -- no filter on 'deliverable_digital_release_snowflake.territory'


              ) restrict_release_territory ON restrict_release_territory.release_id = r.release_id )
    SELECT
        releases_snowflake.upc  AS "releases_snowflake.upc"
    FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.RELEASES  AS releases_snowflake
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info  AS artist_info_snowflake ON releases_snowflake.artist_id=artist_info_snowflake.artist_id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vendor  AS vendor_snowflake ON vendor_snowflake.vendor_id = artist_info_snowflake.vendor_id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount  AS subaccount_snowflake ON releases_snowflake.subaccount_id=subaccount_snowflake.subaccount_id
    LEFT JOIN intelligence.prod.vw_active_vendor_contract  AS vw_active_vendor_contract_snowflake ON vendor_snowflake.vendor_id=vw_active_vendor_contract_snowflake.vendor_id
    LEFT JOIN deliverable_digital_release_snowflake ON releases_snowflake.release_id = deliverable_digital_release_snowflake.release_id
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.TRACK  AS track_snowflake ON track_snowflake.release_id=releases_snowflake.release_id
    WHERE (vendor_snowflake.vendor_id ) = {label_id}
    AND (releases_snowflake.release_status='in_content'
    AND releases_snowflake.deletions!='Y'
    AND releases_snowflake.not_for_distribution='N')
    AND (((UPPER(( vendor_snowflake.label_identifier  )) <> UPPER('Test')
        OR ( vendor_snowflake.label_identifier  ) IS NULL)))
    AND (((( subaccount_snowflake.date_deleted  ) IS NULL))
    AND (NOT (deliverable_digital_release_snowflake.is_deliverable_release_store )
        OR (deliverable_digital_release_snowflake.is_deliverable_release_store ) IS NULL)
    AND (((UPPER(( track_snowflake.track_type  )) = UPPER('music')))
    AND (vw_active_vendor_contract_snowflake.vendor_id ) IS NOT NULL))
    GROUP BY
        1
    """


def at3(label_id: int):
    """AT3) Label (The Orchard) Release Page
    Look: https://theorchard.looker.com/looks/20084
    """
    return f"""
    WITH vendor_territory_restriction_snowflake_1 AS (SELECT
            id,
            TRY_TO_NUMBER(a.value::string) AS country_id,
            CONCAT(id, country_id) AS primary_key
          FROM orchard_app_reporting_v2.art_relations_prod_art_relations.vendor_contract,
            LATERAL FLATTEN(INPUT => SPLIT(territory_carve_out, ',')) a
          WHERE primary_key IS NOT NULL
           )
    SELECT
        releases_snowflake.upc  AS "releases_snowflake.upc",
        LISTAGG(DISTINCT vtr_country.abbrivation, ',')  AS "vtr_country.vtr_countries",
        LISTAGG(DISTINCT rtr_country.abbrivation, ',')  AS "rtr_country.rtr_countries",
        LISTAGG(DISTINCT str_country.abbrivation, ',')  AS "str_country.str_countries"
    FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.RELEASES  AS releases_snowflake
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info  AS artist_info_snowflake ON releases_snowflake.artist_id=artist_info_snowflake.artist_id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.distribution_format  AS distribution_format_snowflake ON distribution_format_snowflake.distribution_format_id=releases_snowflake.distribution_format_id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vendor  AS vendor_snowflake ON vendor_snowflake.vendor_id = artist_info_snowflake.vendor_id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount  AS subaccount_snowflake ON releases_snowflake.subaccount_id=subaccount_snowflake.subaccount_id
    LEFT JOIN intelligence.prod.vw_active_vendor_contract  AS vw_active_vendor_contract_snowflake ON vendor_snowflake.vendor_id=vw_active_vendor_contract_snowflake.vendor_id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vendor_contract  AS vendor_contract_snowflake ON vw_active_vendor_contract_snowflake.vendor_contract_id=vendor_contract_snowflake.id
    LEFT JOIN vendor_territory_restriction_snowflake_1 ON vendor_contract_snowflake.id = vendor_territory_restriction_snowflake_1.ID
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country  AS vtr_country ON vendor_territory_restriction_snowflake_1.COUNTRY_ID = vtr_country.id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount_territory_restriction AS subaccount_territory_restriction_snowflake ON subaccount_snowflake.subaccount_id = subaccount_territory_restriction_snowflake.SUBACCOUNT_ID
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country  AS str_country ON subaccount_territory_restriction_snowflake.COUNTRY_ID = str_country.id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.release_territory_restriction  AS release_territory_restriction_snowflake ON releases_snowflake.release_id = release_territory_restriction_snowflake.release_id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country  AS rtr_country ON release_territory_restriction_snowflake.country_id = rtr_country.id
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.TRACK  AS track_snowflake ON track_snowflake.release_id=releases_snowflake.release_id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.delivery_history  AS delivery_history_snowflake ON releases_snowflake.upc= delivery_history_snowflake.upc
    WHERE (vendor_snowflake.vendor_id ) = {label_id}
    AND (delivery_history_snowflake.customer_master_master_id ) = 569
    AND ((UPPER(( distribution_format_snowflake.context_type  )) = UPPER('digital')))
    AND ((UPPER(( track_snowflake.track_type  )) = UPPER('music')))
    GROUP BY
        1
    """


def at4(label_id: int):
    """AT4) YT Art Track Asset Metadata
    Look: https://theorchard.looker.com/looks/20077
    """
    return f"""
        SELECT
            dim_track.UPC AS "dim_track.UPC",
            CASE
                  WHEN (yt_asset_report.isrc IS NULL
                    AND yt_asset_report.custom_id is NOT NULL)
                  THEN SPLIT_PART(yt_asset_report.custom_id, '_', 2)
                  ELSE yt_asset_report.isrc
                END  AS "yt_asset_report.isrc",
            yt_asset_report.asset_id  AS "yt_asset_report.asset_id",
            yt_asset_report.ownership  AS "yt_asset_report.ownership",
            yt_asset_report.artist  AS "yt_asset_report.artist"
        FROM facts.prod.DIM_TRACK  AS dim_track
        INNER JOIN facts.prod.DIM_RELEASE  AS dim_release ON dim_track.UPC = dim_release.RELEASEID

        INNER JOIN facts.prod.DIM_ARTIST  AS dim_artist ON dim_release.ARTISTID = dim_artist.ARTISTID

        INNER JOIN facts.prod.DIM_LABEL  AS dim_label ON dim_artist.LABELID = dim_label.LABELID

        LEFT JOIN facts.prod.DIM_SUBACCOUNT  AS dim_subaccount ON dim_release.SUBACCOUNTID = dim_subaccount.SUBACCOUNTID

        INNER JOIN facts.prod.staging_raw_youtube_asset_report  AS yt_asset_report ON (CASE
                  WHEN (yt_asset_report.isrc IS NULL
                    AND yt_asset_report.custom_id is NOT NULL)
                  THEN SPLIT_PART(yt_asset_report.custom_id, '_', 2)
                  ELSE yt_asset_report.isrc
                END) = dim_track.ISRC
                  AND TO_VARCHAR(dim_track.UPC) = LTRIM((CASE
                    WHEN (yt_asset_report.asset_type <> 'Art Track'
                      AND length(yt_asset_report.custom_id) > 22
                      AND (yt_asset_report.upc is NULL
                        OR TRY_TO_NUMERIC(LTRIM(yt_asset_report.upc, '0')) is NULL))
                    THEN TRY_TO_NUMERIC(LTRIM(SPLIT_PART(yt_asset_report.custom_id, '_', 1), '0'))
                    ELSE
                      TRY_TO_NUMERIC(LTRIM(yt_asset_report.upc, '0'))
                END), '0')

        WHERE (dim_release.LABELID ) = {label_id} AND (yt_asset_report.asset_type ) = 'ART_TRACK_VIDEO' AND ((CASE
                  WHEN (yt_asset_report.isrc IS NULL
                    AND yt_asset_report.custom_id is NOT NULL)
                  THEN SPLIT_PART(yt_asset_report.custom_id, '_', 2)
                  ELSE yt_asset_report.isrc
                END) != ''
            AND (CASE
                  WHEN (yt_asset_report.isrc IS NULL
                    AND yt_asset_report.custom_id is NOT NULL)
                  THEN SPLIT_PART(yt_asset_report.custom_id, '_', 2)
                  ELSE yt_asset_report.isrc
                END) IS NOT NULL
            AND split_part(yt_asset_report.FILENAME, '.', '2') IN ('ORCH', 'IODA', 'ENT'))
        GROUP BY
            1,
            2,
            3,
            4,
            5
        """


def at5(label_id: int):
    """AT5) YT Ownership Conflicts
    Look: https://theorchard.looker.com/looks/20085
    """
    return f"""
    SELECT
    yt_conflict_report_final.PARTNER_ASSET_ID  AS "yt_conflict_report_final.asset_id",
    yt_conflict_report_final.CONFLICTING_OWNER  AS "yt_conflict_report_final.conflicting_owner"
    FROM facts.prod.DIM_TRACK  AS dim_track
    INNER JOIN facts.prod.DIM_RELEASE  AS dim_release ON dim_track.UPC = dim_release.RELEASEID

    INNER JOIN facts.prod.DIM_ARTIST  AS dim_artist ON dim_release.ARTISTID = dim_artist.ARTISTID

    INNER JOIN facts.prod.DIM_LABEL  AS dim_label ON dim_artist.LABELID = dim_label.LABELID

    LEFT JOIN facts.prod.DIM_SUBACCOUNT  AS dim_subaccount ON dim_release.SUBACCOUNTID = dim_subaccount.SUBACCOUNTID

    INNER JOIN facts.prod.fact_conflict  AS yt_conflict_report_final ON yt_conflict_report_final.TUID = dim_track.TRACK_UNIQUE_ID

    LEFT JOIN OWS_CONFLICT_MANAGER.prod.action  AS yt_conflict_action ON yt_conflict_report_final.CONFLICT_ID = yt_conflict_action.CONFLICT_ID
    WHERE (yt_conflict_action.ACTION ) IS NULL
    AND ( yt_conflict_report_final.RESOLVED_DATETIME ) IS NULL
    AND (dim_label.LABELID ) = {label_id}
    GROUP BY
        1,
        2
    """


def at6(label_id: int):
    """AT6) YT Art Track Channel
    Look: https://theorchard.looker.com/looks/20078
    """
    return f"""
    /*
    Use a CTE for running AT7 together with AT6.
    AT6 misses some rows which for whatever reason don't have the label_id correctly
    set, so AT7 is used to get those rows based merely on the asset_id, which
    we know is connected to the label_id through the UPC.
    */
    WITH LABEL_ASSET_IDS AS (
        SELECT YT.ASSET_ID AS asset_id
        FROM FACTS.PROD.STAGING_RAW_YOUTUBE_ASSET_REPORT YT
        RIGHT JOIN FACTS.PROD.DIM_RELEASE DR
        ON DR.DISPLAY_UPC = YT.UPC
        WHERE DR.LABELID = {label_id}
    )

    SELECT
      facts_prod_staging_raw_youtube_claim.assetid  AS "facts_prod_staging_raw_youtube_claim.asset_id",
      facts_prod_staging_raw_youtube_claim.channeldisplayname  AS "facts_prod_staging_raw_youtube_claim.channel_display_name",
      facts_prod_staging_raw_youtube_claim.contenttype  AS "facts_prod_staging_raw_youtube_claim.content_type",
      facts_prod_staging_raw_youtube_claim.claimtype  AS "facts_prod_staging_raw_youtube_claim.claim_type"
    FROM INTELLIGENCE.PROD.YOUTUBE_CLAIM_REPORT  AS facts_prod_staging_raw_youtube_claim
    LEFT JOIN INTELLIGENCE.DBT_PROD.DT_REGISTRY  AS dt_registry ON dt_registry.isrc = facts_prod_staging_raw_youtube_claim.isrc_data
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.track  AS orch_app_ar_track ON dt_registry.tuid = orch_app_ar_track.id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.releases  AS orch_app_ar_releases ON orch_app_ar_track.upc = orch_app_ar_releases.upc
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info  AS orch_app_ar_artist_info ON orch_app_ar_releases.artist_id = orch_app_ar_artist_info.artist_id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vendor  AS orch_app_ar_vendor ON orch_app_ar_artist_info.vendor_id = orch_app_ar_vendor.vendor_id
    WHERE ((UPPER(( facts_prod_staging_raw_youtube_claim.contenttype  )) = UPPER('PARTNER_UPLOADED')))

    AND ((orch_app_ar_vendor.vendor_id ) = {label_id} OR
    -- AT7: YT Art Track Channel [Filter On Asset ID]
    facts_prod_staging_raw_youtube_claim.assetid IN (SELECT asset_id FROM LABEL_ASSET_IDS))

    AND (facts_prod_staging_raw_youtube_claim.isrc_data != ''
      AND facts_prod_staging_raw_youtube_claim.isrc_data IS NOT NULL
      ) AND (facts_prod_staging_raw_youtube_claim.channeldisplayname ) IS NOT NULL
    GROUP BY
        1,
        2,
        3,
        4
    """


def at7(*args, **kwargs):
    """AT7) YT Art Track Channel [Filter On Asset ID]
    Look: https://theorchard.looker.com/looks/22336
    """
    raise RuntimeError("Look AT7 is already implemented in at6()!")