"""DB queries."""

GET_FILES = """
SELECT
    r.UPC AS upc,
    tr.CD AS track_volume,
    tr.TRACK_ID AS track_index,
    af.ID AS asset_final_id,
    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
    AND af.ASSET_TYPE IN (%(asset_types)s)
LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.TRACK AS tr
    ON au.TRACK_UNIQUE_ID = tr.ID
WHERE
    r.upc IN (%(upcs)s)
ORDER BY
    r.upc,
    tr.CD,
    tr.TRACK_ID
;
"""