"""Direct Delivery MySQL Queries."""

# Get file paths from DD DB
DD_SELECT_ASSET_PATH = r"""
    SELECT CASE
    WHEN
        ta.file_type = 'file'
        THEN CONCAT(REPLACE(sdaf.`initial_folder`, '/', ''), '/',
        SUBSTRING(a.`upc`, 1, 6), '/',
        SUBSTRING(a.`upc`, 7, 3),'/',ald.`filename`)
    ELSE
        CONCAT(REPLACE(sdaf.`initial_folder`, '/', ''), '/',
        SUBSTRING(a.`upc`, 1, 6), '/',
        SUBSTRING(a.`upc`, 7, 3), '/',a.`upc`,'/',ald.`filename`)
    END AS file_path,
    a.`upc`,
    s.physical_location_id,
    INET_NTOA(s.`local_ip`) AS ip 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 a.upc IN ({}) AND s.physical_location_id IN ({})
    AND a.asset_type_id IN ({})
    ORDER BY LENGTH(file_path) ASC, file_path ASC
    LIMIT {}, {};
    """