"""
This file contains queries used to fetch information from direct_delivery
"""

"""
QUERY_AUDIO_ASSET_UPC_FILENAMES returns WAV audio asset filenames in direct
delivery for tracks for a given upc. Rather than trying to parse the
filename for individual tracks in SQL, this fetches all the filenames
so that the parsing can be done outside of SQL. They will be parsed differently
depending on their status, since the filename format differs.
"""

QUERY_AUDIO_ASSET_UPC_FILENAMES = """
SELECT a.upc as code, a.upc, a.cd, a.track_id, a.clip_number,
    inet_ntoa(s.local_ip) AS ip,ta.extension, ta.folder_structure,
    sdaf.initial_folder, al.storage_drive_id, al.status, al.upload_date,
    ald.filename, ald.duration, ald.md5_hash, ald.file_size,
    sdaf.storage_drive_asset_folder_id, s.storage_id, s.physical_location_id,
    s.bucket_name, a.asset_id
FROM asset a
INNER JOIN asset_location al
    ON a.asset_id = al.asset_id
INNER JOIN storage_drive sd
    ON sd.storage_drive_id = al.storage_drive_id
INNER JOIN storage s
    ON s.storage_id = sd.storage_id
LEFT JOIN asset_location_detail ald
    ON ald.asset_location_id = al.asset_location_id
INNER JOIN asset_type ta
    ON a.asset_type_id = ta.asset_type_id
INNER JOIN storage_drive_asset_folder sdaf
    ON sd.storage_drive_id = sdaf.storage_drive_id
    AND sdaf.asset_type_id = a.asset_type_id
WHERE s.physical_location_id = :physical_location_id
AND a.asset_type_id = 1
AND a.upc = :upc
"""