# ruff: noqa: E501

GET_AUDIO_FILES = """
SELECT
    r.RELEASE_ID,
    r.UPC,
    af.ASSET_TYPE,
    af.BUCKET AS S3_BUCKET,
    af.FILENAME AS S3_FILENAME
FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.RELEASES AS r
JOIN ORCHARD_APP_REPORTING_V2.PROD_OWS_ASSETS_OWS_ASSETS.ASSET_UPLOAD AS au
    ON au.PRODUCT_ID = r.RELEASE_ID
    AND au.DELETED = 0
    AND au.IS_CORRECTION = 0
    AND au.API_VERSION = 2
JOIN ORCHARD_APP_REPORTING_V2.PROD_OWS_ASSETS_OWS_ASSETS.ASSET_FINAL AS af
    ON af.ASSET_UPLOAD_ID = au.ID
WHERE
    (
        r.UPC = %(upc)s
        OR r.RELEASE_ID IN (
            SELECT
                DISTINCT(pjr.RELEASE_ID)
            FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.PROJECT AS pj
            JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.RELEASES AS pjr
                ON pjr.PROJECT_ID = pj.PROJECT_ID
            WHERE
                pj.VENDOR_ID = %(vendor_id)s
        )
    )
    AND (%(gt_release_id)s = 0 OR r.RELEASE_ID > %(gt_release_id)s)
    AND (%(lt_release_id)s = 0 OR r.RELEASE_ID < %(lt_release_id)s)
;
"""

GET_VIDEO_FILES = """
SELECT
    r.RELEASE_ID,
    r.UPC,
    va.ASSET_TYPE,
    'prod-orcd-video-assets' AS S3_BUCKET,
    va.ASSET_PATH AS S3_FILENAME
FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.RELEASES AS r
JOIN ORCHARD_APP_REPORTING_V2.PROD_OWS_VIDEO_OWS_VIDEO.VIDEO_ASSET AS va
    ON va.PRODUCT_ID = r.RELEASE_ID
WHERE
    (
        r.UPC = %(upc)s
        OR r.RELEASE_ID IN (
            SELECT
                DISTINCT(pjr.RELEASE_ID)
            FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.PROJECT AS pj
            JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.RELEASES AS pjr
                ON pjr.PROJECT_ID = pj.PROJECT_ID
            WHERE
                pj.VENDOR_ID = %(vendor_id)s
        )
    )
    AND (%(gt_release_id)s = 0 OR r.RELEASE_ID > %(gt_release_id)s)
    AND (%(lt_release_id)s = 0 OR r.RELEASE_ID < %(lt_release_id)s)
;
"""