"""ISRC queries."""

ISRC_USAGE_STATS_QUERY = """
select
    t_validatee.id validatee_track_id,
    t_validatee.isrc,
    case df_match.context_type when 'physical' then 'physical' else
        case df_match.distribution_format_id when 1 then 'digital_audio' else
            case df_match.distribution_format_id when 57 then 'digital_video' else
                null
            end
        end
    end matched_isrc_product_type,
    t_match.release_id matched_isrc_product_id,
    r_match.release_name matched_isrc_product_name,
    t_match.id matched_isrc_track_id,
    t_match.track_name matched_isrc_track_name
from track t_validatee
    inner join releases r_validatee
        on r_validatee.release_id = t_validatee.release_id
    inner join track t_match
        on t_match.isrc = t_validatee.isrc
        and t_match.id != t_validatee.id
    inner join releases r_match
        on r_match.release_status = 'in_content'
        and r_match.release_id = t_match.release_id
    inner join distribution_format df_match on df_match.distribution_format_id = r_match.distribution_format_id
where t_validatee.release_id = :product_id
order by t_validatee.id, matched_isrc_product_type, t_match.release_id, t_match.id;
"""


TRACKS_WITH_ISRC_QUERY = """
SELECT
    t.id as tuid
FROM track t
WHERE t.isrc = :isrc
"""