"""
SQL for accessing album information.

File that contains SQL queries to retrieve information about albums
from the release_artist table.
"""

SELECT_ALBUM_BY_PRODUCT_ID = """
SELECT
  r.release_id as product_id,
  r.release_name as product_name,
  r.sale_start_date,
  r.upc,
  r.manufacturer_upc,
  r.product_code,
  r.project_id,
  r.display_upc,
  g.genre,
  s.name as subgenre,
  pp.wholesale_price,
  pp.pricing,
  pp.box_lot,
  pp.exportable,
  pp.explicit,
  pp.edition,
  v.owner,
  pj.description,
  TRIM(r.label) as label,
  df.context_type,
  ai.vendor_id,
  v.name as vendor_name

FROM `releases` r
JOIN `distribution_format` df
  ON r.distribution_format_id = df.distribution_format_id
JOIN `artist_info` ai
  ON r.artist_id = ai.artist_id
JOIN `genre` g
  ON r.genre_id = g.genre_id
JOIN `release_subgenre` rs
  ON rs.release_id = r.release_id
  AND rs.upc = r.upc
JOIN `subgenre` s
  ON rs.subgenre_id = s.orchard_id
JOIN `vendor` v
  ON v.vendor_id = ai.vendor_id
LEFT JOIN `product_physical` pp
  ON r.release_id = pp.release_id
JOIN `project` pj
  ON r.project_id = pj.project_id
WHERE r.release_id = :product_id;
"""

SELECT_ALBUM_FOR_DIGITAL_BY_PRODUCT_ID = """
SELECT
  r.release_id as product_id,
  r.release_name as product_name,
  r.sale_start_date,
  r.upc,
  r.manufacturer_upc,
  r.product_code,
  r.project_id,
  r.display_upc,
  pp.wholesale_price,
  pp.pricing,
  pp.box_lot,
  pp.exportable,
  pp.explicit,
  v.owner,
  pj.description,
  TRIM(r.label) as label,
  df.context_type,
  ai.vendor_id,
  v.name as vendor_name

FROM `releases` r
JOIN `distribution_format` df
  ON r.distribution_format_id = df.distribution_format_id
JOIN `artist_info` ai
  ON r.artist_id = ai.artist_id
JOIN `vendor` v
  ON v.vendor_id = ai.vendor_id
LEFT JOIN `product_physical` pp
  ON r.release_id = pp.release_id
JOIN `project` pj
  ON r.project_id = pj.project_id
WHERE r.release_id = :product_id;
"""

SELECT_TRACKS_DETAILS_BY_PRODUCT_ID = """
SELECT
  t.release_id as product_id,
  t.id as track_id,
  t.track_name,
  t.cd,
  tf.side,
  t.track_id as track_number,
  t.explicit_lyrics as explicit,
  ta.name as artist_name,
  ta.type

FROM `track` as t
JOIN `track_artist` ta
  ON ta.track_id = t.id
  AND ta.type in ('performer', 'featuring')
LEFT JOIN `track_physical` tf
  ON tf.track_id = t.id

WHERE t.release_id = :product_id
ORDER BY t.cd ASC, tf.side ASC, t.track_id ASC;
"""

SELECT_PRODUCT_TYPE_ID_BY_PRODUCT_ID = """
SELECT product_type_id
FROM `releases`
WHERE release_id = :product_id;
"""

SELECT_DISPLAY_CONFIGURATION_BY_PRODUCT_ID = """
SELECT display_configuration
FROM `product_physical`
WHERE release_id = :product_id
AND display_configuration != '';
"""

SELECT_CONTEXT_TYPE_AND_STATUS_BY_PRODUCT_ID = """
SELECT df.context_type, r.release_status
FROM `distribution_format` df
JOIN `releases` r
  ON r.distribution_format_id = df.distribution_format_id
WHERE r.release_id = :product_id;
"""

SELECT_GENRE_AND_SUBGENRE_BY_PRODUCT_ID = """
SELECT
  g.genre,
  s.name as subgenre
FROM `releases` r
LEFT JOIN `genre` g
  ON r.genre_id = g.genre_id
LEFT JOIN `release_subgenre` rs
  ON rs.release_id = r.release_id
  AND rs.upc = r.upc
LEFT JOIN `subgenre` s
  ON rs.subgenre_id = s.orchard_id
WHERE r.release_id = :product_id;
"""