"""Products Documents Query."""

PRODUCTS_DOCUMENTS = """
SELECT
    r.release_id,
    r.display_upc,
    r.upc,
    r.release_name,
    r.label AS `imprint`,
    DATE_FORMAT(r.release_date, '%Y-%m-%d') as release_date,
    YEAR(r.release_date) AS `release_year`,
    MONTH(r.release_date) AS `release_month`,
    DATE_FORMAT(r.sale_start_date, '%Y-%m-%d') as sale_start_date,
    r.artist_id,
    r.format,
    r.c_line,
    r.deletions,
    r.product_type_id,
    r.vendor_catalog_number,
    r.release_status,
    r.version,
    r.delivered_version,
    ai.name AS `artist_name`,
    ai.description AS `artist_description`,
    ai.url AS `artist_url`,
    ai.myspace_url AS `artist_url_myspace`,
    v.company AS label,
    v.vendor_id,
    r.subaccount_id,
    v.owner as `vendor_owner`,
    v.status as `vendor_status`,
    pp.initial_stock,
    IF(r.product_type_id=1, g.genre_id, NULL) as `genre_id`,
    IF(r.product_type_id=1, s.orchard_id, NULL) as `subgenre_id`,
    r.distribution_format_id,
    IF(df.context_type='physical',
        pp.display_configuration, dfm.name) as `configuration`,
    IF(r.product_type_id=3, 'Movie', IF(r.product_type_id=2, 'TV Show',
       IF(r.product_type_id=1 AND r.distribution_format_id=1, 'Digital Audio',
       IF(df.context_type='physical', 'Physical Audio',
       IF(r.product_type_id=1 AND r.distribution_format_id=57,
           'Music Video', 'Other'))))) as `product_configuration`,
    r.product_code,
    r.project_id,
    p.project_name,
    p.project_code,
    DATE_FORMAT(pp.embargo_date, '%Y-%m-%d') AS embargo_date,
    pv.type_of_video,
    DATE_FORMAT(r.preorder_date, '%Y-%m-%d') as `preorder_date`,
    r.not_for_distribution,
    r.meta_language,
    df.context_type,
    DATE_FORMAT(r.ingestion_completed, '%Y-%m-%d') AS ingestion_completed,
    DATE_FORMAT(
        GREATEST(
          DATE_ADD(
            DATE(DATE_ADD(r.`ingestion_completed`, INTERVAL 5 DAY)),
            INTERVAL (
              9 - IF(
                DAYOFWEEK(DATE(DATE_ADD(r.`ingestion_completed`, INTERVAL 5 DAY))) = 1,
                8,
                DAYOFWEEK(DATE(DATE_ADD(r.`ingestion_completed`, INTERVAL 5 DAY)))
              )
            ) DAY
          ),
    DATE(IFNULL(r.sale_start_date, r.`ingestion_completed`))
        ),
        '%Y-%m-%d'
      ) AS 'distribution_date',
    r.priority,
    r.distribution
FROM
    releases r
LEFT JOIN track t
    ON t.upc = r.upc
JOIN artist_info ai
    ON ai.artist_id = r.artist_id
JOIN vendor v
    ON v.vendor_id = ai.vendor_id
LEFT JOIN release_subgenre rs
    ON rs.upc = r.upc
LEFT JOIN subgenre s
    ON s.orchard_id = rs.subgenre_id
LEFT JOIN genre g
    ON g.genre_id = s.genre_id
LEFT JOIN release_film_genre rfg
    ON rfg.release_id = r.release_id
    AND rfg.is_primary = 'Y'
LEFT JOIN film_genre fg
    ON fg.film_genre_id = rfg.film_genre_id
LEFT JOIN mkt_priority mp
    ON mp.upc = r.upc
    AND mp.`priority` = 'a'
LEFT JOIN distribution_format df
    ON df.distribution_format_id = r.distribution_format_id
LEFT JOIN distribution_format_media dfm
    ON dfm.distribution_format_media_id = df.distribution_format_media_id
LEFT JOIN product_physical pp
    ON pp.release_id = r.release_id
LEFT JOIN product_video pv
    ON pv.release_id = r.release_id
LEFT JOIN project p
    ON r.project_id = p.project_id
WHERE r.release_id IN :release_ids
GROUP BY r.release_id
"""