def sr_ugc1(label_id: int):
    """SRUGC1) YT Top SR UGC Claim
    Look: https://theorchard.looker.com/looks/20053
    """
    return f"""
        -- This query has been modified, adding some extra filters and joining tables
        SELECT
            facts_prod_staging_raw_youtube_claim.assetid  AS "facts_prod_staging_raw_youtube_claim.asset_id",
            facts_prod_staging_raw_youtube_claim.isrc  AS
            "facts_prod_staging_raw_youtube_claim.isrc",
            facts_prod_staging_raw_youtube_claim.videoid  AS "facts_prod_staging_raw_youtube_claim.video_id",
            facts_prod_staging_raw_youtube_claim.views  AS "facts_prod_staging_raw_youtube_claim.views",
            facts_prod_staging_raw_youtube_claim.claimtype  AS "facts_prod_staging_raw_youtube_claim.claim_type",
            dim_track.trackname AS "dim_track.trackname",
            dim_artists.track_artists AS "dim_artists.track_artists"
        FROM INTELLIGENCE.PROD.YOUTUBE_CLAIM_REPORT  AS facts_prod_staging_raw_youtube_claim
        LEFT JOIN FACTS.PROD.DIM_TRACK AS dim_track USING(isrc)
        LEFT JOIN FACTS.PROD.DIM_TRACK_ARTISTS_AGGREGATED_TEMP AS dim_artists ON dim_artists.TRACK_ID = dim_track.TRACK_UNIQUE_ID
        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.claimstatus  )) = UPPER(
        'Active') OR UPPER(( facts_prod_staging_raw_youtube_claim.claimstatus  )) =
        UPPER('ACTIVE'))) AND ((UPPER((
        facts_prod_staging_raw_youtube_claim.claimtype  )) = UPPER('AUDIO'))) AND (
        orch_app_ar_vendor.vendor_id ) = {label_id}
        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.isrc IS NOT NULL
          AND facts_prod_staging_raw_youtube_claim.views IS NOT NULL
          AND dim_artists.track_artists IS NOT NULL
        GROUP BY
            1,
            2,
            3,
            4,
            5,
            6,
            7
        ORDER BY
            4 DESC
        FETCH NEXT 1 ROWS ONLY
    """


def sr_ugc2(label_id: int):
    """SRUGC2) YT Top SR UGC Claim
    Look: https://theorchard.looker.com/looks/20162
    """
    return f"""
    SELECT
        facts_prod_staging_raw_youtube_claim.assetid  AS "facts_prod_staging_raw_youtube_claim.asset_id",
        COUNT(DISTINCT facts_prod_staging_raw_youtube_claim.videoid ) AS "total_videos",
        COUNT(DISTINCT facts_prod_staging_raw_youtube_claim.claimid ) AS "total_claims",
        COALESCE(SUM(CAST( facts_prod_staging_raw_youtube_claim.views   AS DOUBLE PRECISION)), 0) AS "total_views"
    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 (orch_app_ar_vendor.vendor_id ) = {label_id}
    AND (facts_prod_staging_raw_youtube_claim.videoid ) IS NOT NULL
    AND facts_prod_staging_raw_youtube_claim.isrc_data IS NOT NULL
    AND facts_prod_staging_raw_youtube_claim.isrc_data != ''
    AND ((UPPER(( facts_prod_staging_raw_youtube_claim.claimstatus  )) = 'ACTIVE' OR UPPER(( facts_prod_staging_raw_youtube_claim.claimstatus  )) = 'ACTIVE')) AND ((UPPER(( facts_prod_staging_raw_youtube_claim.claimtype  )) = UPPER('AUDIO')))
    GROUP BY
        1
    """


def sr_ugc3(label_id: int):
    """SRUGC3) YT Average SR Daily Views
    Look: https://theorchard.looker.com/looks/20054
    """
    return f"""
    SELECT
        dim_track.ISRC  AS "dim_track.isrc",
        yt_asset_report.recent_daily_average  AS "yt_asset_report.recent_daily_average"
    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_label.LABELID ) = {label_id} 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
    """