"""SQL raw queries."""

GET_V2_ASSETS_TO_COPY_SQL = """
    SELECT
        au.bucket_name as asset_upload_bucket_name,
        au.filename as asset_upload_filename,
        au.is_correction,
        au.original_filename,
        au.asset_upload_type_id,
        af.asset_type as asset_final_type,
        af.asset_subtype,
        af.bucket,
        af.filename as asset_final_filename,
        af.duration,
        af.channels,
        af.codec,
        af.sample_rate,
        af.bit_rate,
        af.bit_depth
    FROM asset_upload au
    INNER JOIN asset_final af ON af.asset_upload_id = au.id
    WHERE au.id = (
        SELECT MAX(au.id)
        FROM asset_upload au
        WHERE au.api_version = 2
        AND au.deleted = 0
        AND au.product_id = :product_id
        AND au.track_unique_id = :track_unique_id
        AND au.asset_upload_type_id = :asset_upload_type_id
        GROUP BY au.product_id, au.track_unique_id, au.asset_upload_type_id)
"""


GET_PRODUCT_ASSETS_SQL = """
select
    latest_asset_upload.product_id,
    af.asset_type,
    latest_asset_upload.track_unique_id,
    af.bucket,
    af.filename,
    af.duration / 1000 as duration,
    af.duration as duration_ms,
    latest_asset_upload.updated_timestamp,
    convert_tz(latest_asset_upload.updated_timestamp, 'UTC', 'US/Eastern') as updated_timestamp_us_east
from (
    select
        max(au.id) latest_asset_upload_id,
        au.product_id,
        au.track_unique_id,
        au.updated_timestamp
    from asset_upload au
    where au.product_id in :product_ids
    and au.deleted = 0
    and au.api_version = 2
    and au.is_correction = 0
    group by au.product_id, au.track_unique_id, au.asset_upload_type_id
) latest_asset_upload
inner join asset_final af
on af.asset_upload_id = latest_asset_upload.latest_asset_upload_id
and (
    not :filter_asset_types or af.asset_type in :asset_types
)
"""

GET_PRODUCT_ASSETS_UPLOAD_AND_FINAL_SQL = """
select
    latest_asset_upload.product_id,
    af.asset_type,
    latest_asset_upload.track_unique_id,
    af.bucket,
    af.filename,
    latest_asset_upload.updated_timestamp,
    latest_asset_upload.asset_upload_type_id
from (
    select
        max(au.id) latest_asset_upload_id,
        au.product_id,
        au.track_unique_id,
        au.updated_timestamp,
        au.asset_upload_type_id
    from asset_upload au
    where au.product_id = {product_id}
    and au.deleted = 0
    and au.api_version = 2
    group by au.product_id, au.track_unique_id, au.asset_upload_type_id
) latest_asset_upload
left join asset_final af
on af.asset_upload_id = latest_asset_upload.latest_asset_upload_id
and af.asset_type in ({asset_types})
"""


GET_V2_ASSETS_STATUS_TO_COPY_SQL = """
    SELECT
        ass.status,
        ass.description,
        ass.message,
        ass.status_time
    FROM asset_upload au
    INNER JOIN asset_status ass ON ass.asset_upload_id = au.id
    WHERE au.id = (
        SELECT MAX(au.id)
        FROM asset_upload au
        WHERE au.api_version = 2
        AND au.deleted = 0
        AND au.product_id = :product_id
        AND au.track_unique_id = :track_unique_id
        AND au.asset_upload_type_id = :asset_upload_type_id
        GROUP BY au.product_id, au.track_unique_id, au.asset_upload_type_id)
"""

GET_ASSET_INFO_FOR_TRACK_DURATION_UPDATE = """
SELECT au.upc, au.track_unique_id, au.is_correction, au.product_id, af.duration, au.deleted
FROM asset_upload au
         JOIN asset_final af on au.id = af.asset_upload_id
WHERE au.id = {asset_upload_id}
  AND af.asset_type = 'WAV'
  AND au.api_version = 2;
"""

GET_ASSET_UPLOAD_AND_FINAL_BY_PRODUCT_ID = """
SELECT DISTINCT(af.bit_depth) as bit_depth
FROM asset_upload au
INNER JOIN asset_final af ON au.id = af.asset_upload_id
WHERE au.product_id = {product_id}
  AND au.api_version = {api_version}
  AND af.asset_type = '{asset_type}'
  AND au.is_correction = 0
  AND au.deleted = 0;
"""