from sqlalchemy.sql import text

"""
SQL for accessing track information
===================================

File that contains SQL queries to retrieve information about tracks in
art_relations.
"""

# SELECT_TRACKS_BY_TUIDS is used to get label, release, and artist information
# for given tuid(s)
SELECT_TRACKS_BY_TUIDS = text("""
SELECT
  t.id as tuid, r.display_upc as upc, t.track_name, t.isrc,
  v.vendor_id, COALESCE(v.company, v.name) as vendor_name,
  r.release_name, a.name as artist_name,
  s.subaccount_id, s.subaccount_name
FROM art_relations.track t
JOIN art_relations.releases r ON t.release_id = r.release_id
JOIN art_relations.artist_info a ON a.artist_id = r.artist_id
JOIN art_relations.vendor v on v.vendor_id = a.vendor_id
LEFT JOIN art_relations.subaccount s on r.subaccount_id = s.subaccount_id
WHERE t.id IN :tuids
""")


SELECT_ISRC_RECORDS_BY_UPCS = text("""
SELECT
  t.id as tuid, r.upc, t.track_name, t.track_type,
  UPPER(t.isrc) as isrc, r.deletions, r.release_status, r.not_for_distribution,
  r.product_type_id, a.vendor_id, d.context_type as distribution_context_type
FROM art_relations.track t
JOIN art_relations.releases r ON t.release_id = r.release_id
JOIN art_relations.artist_info a ON a.artist_id = r.artist_id
JOIN art_relations.distribution_format d ON
  d.distribution_format_id = r.distribution_format_id
WHERE r.upc IN :upcs
""")


# SELECT_ISRCS is used to verify if all given ISRCs
# exist in art_relations.track
SELECT_ISRCS = text("""
SELECT
  UPPER(isrc) as isrc
FROM art_relations.track
WHERE isrc IN :isrcs
""")


# SELECT_UPC_BY_TUID is used to get upc for a given TUID
SELECT_UPC_BY_TUID = text("""
SELECT
  t.id as tuid, r.upc, t.isrc
FROM art_relations.track t
JOIN art_relations.releases r ON t.release_id = r.release_id
WHERE t.id = :tuid
""")


# Next two queries are used for bulk remove ownership operation
# To do it we need to know which TUID of given vendor/subaccount
# belongs to given ISRC
SELECT_TUID_BY_ISRC_AND_VENDOR_ID = text("""
SELECT
    t.id as tuid, t.isrc
FROM art_relations.track t
JOIN art_relations.releases r ON t.release_id = r.release_id
JOIN art_relations.artist_info a ON a.artist_id = r.artist_id
JOIN art_relations.vendor v ON v.vendor_id = a.vendor_id
WHERE v.vendor_id = :vendor_id and t.isrc IN :isrc_list
""")

SELECT_TUID_BY_ISRC_AND_SUBACCOUNT_ID = text("""
SELECT
    t.id as tuid, t.isrc
FROM art_relations.track t
JOIN art_relations.releases r ON t.release_id = r.release_id
JOIN art_relations.subaccount s ON  r.subaccount_id = s.subaccount_id
WHERE s.subaccount_id = :subaccount_id and t.isrc IN :isrc_list
""")