def mv1(label_id: int):
    """MV1) Label (The Orchard) Video Metadata
    Look: https://theorchard.looker.com/looks/20008
    """
    return f"""
        SELECT
            facts_prod_staging_raw_youtube_video_report.channel_display_name  AS "facts_prod_staging_raw_youtube_video_report.channel_display_name",
                (TO_CHAR(TO_DATE(facts_prod_staging_raw_youtube_video_report.time_uploaded ), 'YYYY-MM-DD')) AS "facts_prod_staging_raw_youtube_video_report.time_uploaded",
            /*
            Remove null characters, which in some edge cases can be present
            in the title and will take DB space and also cause problems
            when putting that data in a XLSX (they're illegal control chars)
            */
            REPLACE(facts_prod_staging_raw_youtube_video_report.video_title, '\x00',
            '') AS "facts_prod_staging_raw_youtube_video_report.video_title",
            facts_prod_staging_raw_youtube_video_report.video_id  AS "facts_prod_staging_raw_youtube_video_report.video_id",
            facts_prod_staging_raw_youtube_video_report.video_privacy_status  AS "facts_prod_staging_raw_youtube_video_report.video_privacy_status",
            facts_prod_staging_raw_youtube_video_report.video_length  AS "facts_prod_staging_raw_youtube_video_report.video_length",
            facts_prod_dim_label.labelname  AS "facts_prod_dim_label.label_name",
            NULLIF(facts_prod_staging_raw_youtube_video_report.isrc, '') AS "facts_prod_staging_raw_youtube_video_report.isrc",
            facts_prod_staging_raw_youtube_video_report.custom_id  AS "facts_prod_staging_raw_youtube_video_report.custom_id",
            facts_prod_staging_raw_youtube_video_report.asset_id  AS "facts_prod_staging_raw_youtube_video_report.asset_id",
            facts_prod_staging_raw_youtube_video_report.other_owners_claiming  AS "facts_prod_staging_raw_youtube_video_report.other_owners_claiming",
            MAX(facts_prod_staging_raw_youtube_video_report.views ) AS "facts_prod_staging_raw_youtube_video_report.views"
        FROM facts.prod.staging_raw_youtube_video_report AS facts_prod_staging_raw_youtube_video_report
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.youtube_channel  AS orch_app_ar_youtube_channel ON facts_prod_staging_raw_youtube_video_report.channel_id = orch_app_ar_youtube_channel.youtube_channel_id
        LEFT JOIN facts.prod.dim_label  AS facts_prod_dim_label ON facts_prod_dim_label.labelid = orch_app_ar_youtube_channel.vendor_id
        WHERE (facts_prod_dim_label.labelid ) = {label_id}
        AND (facts_prod_staging_raw_youtube_video_report.licensor != 'sme' )
        GROUP BY
            (TO_DATE(facts_prod_staging_raw_youtube_video_report.time_uploaded )),
            1,
            3,
            4,
            5,
            6,
            7,
            8,
            9,
            10,
            11
    """


def mv2(label_id: int):
    """MV2) YT Video Asset Metadata
    Look: https://theorchard.looker.com/looks/20009
    """
    return f"""
        SELECT
            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.conflicting_owner  AS "yt_asset_report.conflicting_owner",
            yt_asset_report.conflicting_territories  AS "yt_asset_report.conflicting_territories"
        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 (yt_asset_report.asset_type ) = 'MUSIC_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,
            6,
            7
    """