"""This file contains sql to fetch artists from art_relations."""

# SELECT_RELEASE_ARTIST_BY_NAME_AND_VENDOR is used to fetch a release_artist
SELECT_RELEASE_ARTIST_BY_NAME_AND_VENDOR = """
SELECT ra.release_artist_id FROM release_artist ra
INNER JOIN releases r ON r.release_id = ra.release_id
INNER JOIN project p ON p.project_id = r.project_id
WHERE (ra.artist_name = :artist_name AND p.vendor_id = :vendor_id)
OR (ra.artist_info_id = :current_artist_id)
OR (ra.artist_info_id = :artist_id);
"""

# SELECT_TRACK_ARTIST_BY_NAME_AND_VENDOR is used to fetch a track_artist
SELECT_TRACK_ARTIST_BY_NAME_AND_VENDOR = """
SELECT ta.id FROM track_artist ta
INNER JOIN track t ON t.id = ta.track_id
INNER JOIN releases r ON r.release_id = t.release_id
INNER JOIN project p ON p.project_id = r.project_id
WHERE (ta.name = :artist_name AND p.vendor_id = :vendor_id)
OR (ta.artist_info_id = :current_artist_id)
OR (ta.artist_info_id = :artist_id);
"""

# SELECT_TRACK_WRITER_BY_NAME_AND_VENDOR is used to fetch a track_writer
SELECT_TRACK_WRITER_BY_NAME_AND_VENDOR = """
SELECT tw.track_writer_id FROM track_writer tw
INNER JOIN track t ON t.id = tw.unique_track_id
INNER JOIN releases r ON r.release_id = t.release_id
INNER JOIN project p ON p.project_id = r.project_id
WHERE (tw.writer_name = :artist_name AND p.vendor_id = :vendor_id)
OR (tw.artist_info_id = :current_artist_id)
OR (tw.artist_info_id = :artist_id);
"""

# SELECT_PROJECT_WITH_CURRENT_ARTIST_ID is used to fetch project with current artist_id
SELECT_PROJECT_WITH_CURRENT_ARTIST_ID = """
SELECT project_id FROM project p WHERE p.artist_id = :artist_id;
"""

# SELECT_PRODUCT_WITH_CURRENT_ARTIST_ID is used to fetch product with current artist_id
SELECT_PRODUCT_WITH_CURRENT_ARTIST_ID = """
SELECT release_id FROM releases r WHERE r.artist_id = :artist_id;
"""

# SELECT_RELEASES_FOR_ARTIST is used to fetch releases records with artist name
SELECT_RELEASES_FOR_ARTIST = """
SELECT DISTINCT r.release_id FROM releases r
INNER JOIN artist_info ai ON r.`artist_id` = ai.`artist_id`
LEFT JOIN release_artist ra ON r.`release_id` = ra.`release_id`
INNER JOIN project p ON p.project_id = r.project_id
WHERE (ra.artist_name = :artist_name OR ai.name = :artist_name)
AND p.vendor_id = :vendor_id;
"""

# SELECT_TRACKS_FOR_ARTIST is used to fetch tracks for the artist
SELECT_TRACKS_FOR_ARTIST = """
SELECT DISTINCT t.id FROM track_artist ta
INNER JOIN track t ON t.id = ta.track_id
INNER JOIN releases r ON r.release_id = t.release_id
INNER JOIN project p ON p.project_id = r.project_id
WHERE ta.name = :artist_name AND p.vendor_id = :vendor_id;
"""

# UPDATE_TRACK_ARTIST is used to update track artists
UPDATE_TRACK_ARTIST = """
UPDATE track_artist ta
INNER JOIN track t ON t.id = ta.track_id
INNER JOIN releases r ON r.release_id = t.release_id
INNER JOIN project p ON p.project_id = r.project_id
    SET
        ta.name = :new_artist_name,
        ta.artist_info_id = :artist_id
    WHERE p.vendor_id = :vendor_id
    AND (
        ta.name = :artist_name
        OR (:current_artist_id IS NOT NULL AND ta.artist_info_id = :current_artist_id)
        OR (:artist_id IS NOT NULL AND ta.artist_info_id = :artist_id)
    )
"""


UPDATE_TRACK_WRITER = """
UPDATE track_writer tw
INNER JOIN track t ON t.id = tw.unique_track_id
INNER JOIN releases r ON r.release_id = t.release_id
INNER JOIN project p ON p.project_id = r.project_id
    SET
        tw.writer_name = :new_artist_name,
        tw.artist_info_id = :artist_id
    WHERE p.vendor_id = :vendor_id
    AND (
        tw.writer_name = :artist_name
        OR (:current_artist_id IS NOT NULL AND tw.artist_info_id = :current_artist_id)
        OR (:artist_id IS NOT NULL AND tw.artist_info_id = :artist_id)
    )
"""


UPDATE_RELEASE_ARTIST = """
UPDATE release_artist ra
INNER JOIN releases r ON r.release_id = ra.release_id
INNER JOIN project p ON p.project_id = r.project_id
    SET
        ra.artist_name = :new_artist_name,
        ra.artist_info_id = :artist_id
    WHERE p.vendor_id = :vendor_id
    AND (
        ra.artist_name = :artist_name
        OR (:current_artist_id IS NOT NULL AND ra.artist_info_id = :current_artist_id)
        OR (:artist_id IS NOT NULL AND ra.artist_info_id = :artist_id)
    )
"""