"""Product Document Queries."""

PRODUCT_DOCUMENT = """
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,
    IF(r.product_type_id=3, 'Film',
        IF(r.product_type_id=2,'TV',
        IF(r.product_type_id=1,'Music','Other'))) as product_type,
    r.vendor_catalog_number,
    IF(v.label_identifier='Test','N',r.display) AS display,
    r.release_status,
    r.version,
    r.delivered_version,
    GROUP_CONCAT(t.track_name SEPARATOR '\n') AS track_list,
    GROUP_CONCAT(t.isrc SEPARATOR '\n') AS isrc_list,
    GROUP_CONCAT(t.track_type SEPARATOR '\n') AS track_type,
    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.assigned_to as `assigned_to`,
    v.assigned_reviewer as `assigned_reviewer`,
    v.status as `vendor_status`,
    IF(r.product_type_id=3, fg.genre,
        IF(r.product_type_id=2, s.name, g.genre)) as `genre`,
    IF(r.product_type_id=1, g.genre_id, NULL) as `genre_id`,
    IF(r.product_type_id=1, s.name, NULL) as `subgenre`,
    IF(r.product_type_id=1, s.orchard_id, NULL) as `subgenre_id`,
    IF(r.product_type_id=2,
        CONCAT('S:', r.season, ", Ep:", r.episode), NULL) as `episode`,
    IF(GROUP_CONCAT(DISTINCT mp.priority)='a', 'Y', 'N') as `featured`,
    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,
    pp.initial_stock,
    raq.status as `approval_queue_status`,
    rc.status as `correction_status`,
    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
LEFT JOIN release_correction rc
    ON rc.release_correction_id = (
        SELECT MAX(rc2.release_correction_id)
        FROM release_correction rc2
        WHERE rc2.release_id = r.release_id)
LEFT JOIN release_approval_queue raq
    ON raq.release_approval_id = (
        SELECT MAX(raq2.release_approval_id)
        FROM release_approval_queue raq2
        WHERE raq2.release_id = r.release_id)
WHERE r.release_id=:release_id;
"""

PRODUCT_DOCUMENT_WITH_COMPANY_BRAND = """
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,
    IF(r.product_type_id=3, 'Film',
        IF(r.product_type_id=2,'TV',
        IF(r.product_type_id=1,'Music','Other'))) as product_type,
    r.vendor_catalog_number,
    IF(v.label_identifier='Test','N',r.display) AS display,
    r.release_status,
    r.version,
    r.delivered_version,
    GROUP_CONCAT(t.track_name SEPARATOR '\n') AS track_list,
    GROUP_CONCAT(t.isrc SEPARATOR '\n') AS isrc_list,
    GROUP_CONCAT(t.track_type SEPARATOR '\n') AS track_type,
    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.assigned_to as `assigned_to`,
    v.assigned_reviewer as `assigned_reviewer`,
    v.status as `vendor_status`,
    cb.uuid as `company_brand_uuid`,
    cb.name as `company_brand_name`,
    cb.display_name as `company_brand_display_name`,
    IF(r.product_type_id=3, fg.genre,
        IF(r.product_type_id=2, s.name, g.genre)) as `genre`,
    IF(r.product_type_id=1, g.genre_id, NULL) as `genre_id`,
    IF(r.product_type_id=1, s.name, NULL) as `subgenre`,
    IF(r.product_type_id=1, s.orchard_id, NULL) as `subgenre_id`,
    IF(r.product_type_id=2,
        CONCAT('S:', r.season, ", Ep:", r.episode), NULL) as `episode`,
    IF(GROUP_CONCAT(DISTINCT mp.priority)='a', 'Y', 'N') as `featured`,
    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,
    raq.status as `approval_queue_status`,
    rc.status as `correction_status`,
    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
JOIN company_brand cb
    ON cb.id = v.company_brand_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
LEFT JOIN release_correction rc
    ON rc.release_correction_id = (
        SELECT MAX(rc2.release_correction_id)
        FROM release_correction rc2
        WHERE rc2.release_id = r.release_id)
LEFT JOIN release_approval_queue raq
    ON raq.release_approval_id = (
        SELECT MAX(raq2.release_approval_id)
        FROM release_approval_queue raq2
        WHERE raq2.release_id = r.release_id)
WHERE r.release_id=:release_id;
"""

# This would let it have 1000 records which is max Cloudsearch can take.
INCREASE_GROUP_CONCAT_LEN = "SET SESSION group_concat_max_len = 20000;"

# Note: tenant uuid fields are named in camelCase to work with PP system.
PRODUCT_DOCUMENT_WITH_TENANT_UUIDS = """
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,
    IF(r.product_type_id=3, 'Film',
        IF(r.product_type_id=2,'TV',
        IF(r.product_type_id=1,'Music','Other'))) AS product_type,
    r.vendor_catalog_number,
    IF(v.label_identifier='Test','N',r.display) AS display,
    r.release_status,
    r.version,
    r.delivered_version,
    GROUP_CONCAT(t.track_name SEPARATOR '\n') AS track_list,
    GROUP_CONCAT(t.isrc SEPARATOR '\n') AS isrc_list,
    GROUP_CONCAT(t.track_type SEPARATOR '\n') AS track_type,
    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.assigned_to AS `assigned_to`,
    v.assigned_reviewer AS `assigned_reviewer`,
    v.status AS `vendor_status`,
    v.vendor_uuid AS vendorUUID,
    sa.subaccount_uuid AS subaccountUUID,
    cb.uuid AS companyBrandUUID,
    pb.uuid AS parentCompanyUUID,
    IF(r.product_type_id=3, fg.genre,
        IF(r.product_type_id=2, s.name, g.genre)) AS `genre`,
    IF(r.product_type_id=1, g.genre_id, NULL) AS `genre_id`,
    IF(r.product_type_id=1, s.name, NULL) AS `subgenre`,
    IF(r.product_type_id=1, s.orchard_id, NULL) AS `subgenre_id`,
    IF(r.product_type_id=2,
        CONCAT('S:', r.season, ", Ep:", r.episode), NULL) AS `episode`,
    IF(GROUP_CONCAT(DISTINCT mp.priority)='a', 'Y', 'N') AS `featured`,
    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,
    raq.status AS `approval_queue_status`,
    rc.status AS `correction_status`,
    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,
    rsp.upc AS spatial_upc,
    GROUP_CONCAT(tsp.isrc SEPARATOR '\n') AS spatial_isrc_list
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
INNER JOIN company_brand cb
    ON cb.id = v.company_brand_id
INNER JOIN parent_company pb
    ON pb.id = cb.parent_company_id
LEFT JOIN subaccount sa
    ON r.subaccount_id = sa.subaccount_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
LEFT JOIN release_correction rc
    ON rc.release_correction_id = (
        SELECT MAX(rc2.release_correction_id)
        FROM release_correction rc2
        WHERE rc2.release_id = r.release_id)
LEFT JOIN release_approval_queue raq
    ON raq.release_approval_id = (
        SELECT MAX(raq2.release_approval_id)
        FROM release_approval_queue raq2
        WHERE raq2.release_id = r.release_id)
LEFT JOIN product_additional_upc rsp
    ON rsp.product_id = r.release_id
    AND rsp.type = 'atmos'
    AND rsp.deleted_at IS NULL
LEFT JOIN track_additional_isrc tsp
    ON tsp.track_id = t.id
    AND tsp.type = 'atmos'
    AND tsp.deleted_at IS NULL
WHERE r.release_id=:release_id;
"""

# Note: tenant uuid fields are named in camelCase to work with PP system.
PRODUCT_TENANT_UUIDS = """
    SELECT
        r.release_id as product_id,
        v.vendor_uuid AS vendorUUID,
        sa.subaccount_uuid AS subaccountUUID,
        cb.uuid AS companyBrandUUID,
        pc.uuid AS parentCompanyUUID
    FROM
        releases r
    INNER JOIN project p
        ON p.project_id = r.project_id
    INNER JOIN vendor v
        ON v.vendor_id = p.vendor_id
    INNER JOIN company_brand cb
        ON cb.id = v.company_brand_id
    INNER JOIN parent_company pc
        ON pc.id = cb.parent_company_id
    LEFT JOIN subaccount sa
        ON r.subaccount_id = sa.subaccount_id
    WHERE r.release_id=:release_id;
"""