def sr1(label_id: int):
    """SR1) Label (The Orchard) Audio Metadata
    Look: https://theorchard.looker.com/looks/19913
    """
    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,
                COUNT(DELIVERED.delivery_id) > 0 AS is_delivered,
                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
                    ) 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.is_delivered AS "delivery_history_snowflake_aggregated.is_delivered"
        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 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 sr2(label_id: int):
    """SR2) Label (The Orchard) YT Carve-Outs
    Look: https://theorchard.looker.com/looks/19914
    """
    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 Content ID'))

            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 Content ID'))

            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 Content ID'))

        ) 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 Content ID'))

            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 Content ID'))

            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 Content ID'))

            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",
    releases_snowflake.release_name  AS "releases_snowflake.release_name"
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 (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 (vendor_snowflake.vendor_id ) = {label_id})
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,
    2
"""


def sr3(label_id: int):
    """SR3) Label (The Orchard) Registry Territory Count
    Look: https://theorchard.looker.com/looks/19916
    """
    return f"""
    SELECT
        yt_registry.isrc  AS "yt_registry.isrc",
        listagg(DISTINCT  yt_registry.territory  , ',') WITHIN GROUP (ORDER BY  yt_registry.territory  )  AS "yt_registry.territory_list"
    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.REGISTRY AS yt_registry ON yt_registry.tuid = dim_track.TRACK_UNIQUE_ID

    WHERE (dim_label.LABELID ) = {label_id}
    GROUP BY
        1
"""


def sr4(label_id: int):
    """SR4) Label (The Orchard) Locked Registry Territories
    Look: https://theorchard.looker.com/looks/19917
    """
    return f"""
    SELECT
        orch_app_ar_track.isrc  AS "orch_app_ar_track.isrc",
        facts_prod_registry_locked.lock_reason  AS "facts_prod_registry_locked.lock_reason"
    FROM orchard_app_reporting_v2.art_relations_prod_art_relations.owner  AS orch_app_ar_owner
    INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vendor  AS orch_app_ar_vendor ON lower(orch_app_ar_owner.owner_abbrivation) = lower(orch_app_ar_vendor.owner)
    INNER JOIN intelligence.dbt_prod.dt_access_filter_1091  AS dt_access_filter_1091 ON orch_app_ar_vendor.vendor_id = dt_access_filter_1091.vendor_id
    INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info  AS orch_app_ar_artist_info ON orch_app_ar_vendor.vendor_id = orch_app_ar_artist_info.vendor_id
    INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.releases  AS orch_app_ar_releases ON orch_app_ar_artist_info.artist_id = orch_app_ar_releases.artist_id
    INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.track  AS orch_app_ar_track ON orch_app_ar_track.release_id = orch_app_ar_releases.release_id
    LEFT JOIN facts.prod.registry_locked  AS facts_prod_registry_locked ON orch_app_ar_track.isrc = facts_prod_registry_locked.isrc
    WHERE (orch_app_ar_vendor.vendor_id ) = {label_id} AND (IFF(
    facts_prod_registry_locked.isrc IS NULL, FALSE, TRUE) ) AND (dt_access_filter_1091.access_control ) = 0
    GROUP BY
        1,
        2
"""


def sr5(label_id: int):
    """SR5) Label (The Orchard) Internal Registry Conflicts
    Look: https://theorchard.looker.com/looks/19918
    """
    return f"""
    SELECT
int_prod_internal_conflicts."ISRC"  AS "int_prod_internal_conflicts.isrc",
int_prod_internal_conflicts."MATCHED_LABELNAME"  AS "int_prod_internal_conflicts.matched_label_name",
listagg(int_prod_internal_conflicts."CONFLICTING_TERRITORIES",',')  AS "int_prod_internal_conflicts.list_conflicting_territories"
FROM INTELLIGENCE.DBT_PROD.INTERNAL_CONFLICTS  AS int_prod_internal_conflicts
WHERE (int_prod_internal_conflicts."LABELID" ) = {label_id}
GROUP BY
    1,
    2
"""


def sr6(label_id: int):
    """SR6) Label (The Orchard) Release Page
    Look: https://theorchard.looker.com/looks/19972
    """
    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",
        vtr_country.abbrivation  AS "vtr_country.abbrivation"
    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 ((UPPER(( distribution_format_snowflake.context_type  )) = UPPER(
    'digital'))) AND (vendor_snowflake.vendor_id ) = {label_id} AND ((UPPER((
    track_snowflake.track_type  )) = UPPER('music'))) AND (delivery_history_snowflake.customer_master_master_id ) = 453
    GROUP BY
        1,
        2
"""


def sr7(label_id: int):
    """SR7) YT Audio Asset Metadata
    Look: https://theorchard.looker.com/looks/19920
    """
    return f"""
    SELECT
        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.active_reference_ids  AS "yt_asset_report.active_reference_ids",
        yt_asset_report.inactive_reference_ids  AS "yt_asset_report.inactive_reference_ids",
        yt_asset_report.match_policy  AS "yt_asset_report.match_policy",
        yt_asset_report.ownership  AS "yt_asset_report.ownership",
        yt_asset_report.song  AS "yt_asset_report.song",
    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 ) =
    'SOUND_RECORDING' 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,
        6,
        7
"""


def sr8(label_id: int):
    """SR8) YT Ownership Conflicts
    Look: https://theorchard.looker.com/looks/19921
    """
    return f"""
        SELECT
    dim_track.ISRC  AS "dim_track.isrc",
    listagg(DISTINCT yt_conflict_report_final.CONFLICTING_OWNER, '|') AS "yt_conflict_report_final.conflicting_owners",
    listagg(DISTINCT territory, ',') WITHIN GROUP (ORDER BY territory)  AS "yt_conflict_report_final.territory_list"
    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
    WHERE (dim_label.LABELID ) = {label_id} AND (((
    yt_conflict_report_final.RESOLVED_DATETIME  ) IS NULL))
    GROUP BY
        1
"""