"""Product Data Queries."""

PRODUCT_DATA_SQL = """
SELECT r.release_name,
    r.label,
    r.format,
    r.vendor_catalog_number,
    r.vendor_release_identifier,
    r.c_line,
    a.name as artist_name,
    GROUP_CONCAT(DISTINCT COALESCE(ra.artist_name, '')) AS artist_names,
    GROUP_CONCAT(DISTINCT COALESCE(t.track_name, '')) AS track_names,
    GROUP_CONCAT(DISTINCT COALESCE(t.version, '')) AS versions,
    GROUP_CONCAT(DISTINCT COALESCE(t.p_line, '')) AS p_lines,
    GROUP_CONCAT(DISTINCT COALESCE(t.vendor_track_identifier, '')) AS vendor_track_identifiers,
    GROUP_CONCAT(DISTINCT COALESCE(ta.name, '')) AS track_artist_names,
    v.company,
    s.subaccount_name
FROM releases r
    LEFT JOIN artist_info a ON a.artist_id=r.artist_id
    LEFT JOIN vendor v ON v.vendor_id=a.vendor_id
    LEFT JOIN track t ON t.upc=r.upc
    LEFT JOIN track_artist ta ON ta.track_id=t.id AND ta.type NOT IN (
        'conductor', 'ensemble', 'orchestra', 'composer')
    LEFT JOIN release_artist ra ON ra.upc = r.upc AND ra.role NOT IN (
        'conductor', 'ensemble', 'orchestra', 'composer')
    LEFT JOIN subaccount s ON s.subaccount_id=r.subaccount_id
WHERE r.release_id=:product_id
"""