"""This file contains sql that is used to access the project table."""

# QUERY_DB_HEARTBEAT is used to check service-to-db connectivity
QUERY_DB_HEARTBEAT = """
select 'ows-project-manager db heartbeat'
"""

# SELECT_PRODUCT_GENRES is used to fetch all options for product genre
SELECT_PRODUCT_GENRES = """
SELECT
    g.genre_id AS id,
    g.genre AS name
FROM genre g
ORDER BY g.genre
"""

# SELECT_PRODUCT_GENRES_EXCEPT_ALTERNATIVE is used to fetch all options
# for product genre except Alternative
SELECT_PRODUCT_GENRES_EXCEPT_ALTERNATIVE = """
SELECT
    g.genre_id AS id,
    g.genre AS name
FROM genre g
WHERE g.genre != 'Alternative'
ORDER BY g.genre
"""

# SELECT_PRODUCT_IMPRINTS_BY_VENDOR_ID is used to fetch all options for imprint
# given a vendor id.
SELECT_PRODUCT_IMPRINTS_BY_VENDOR_ID = """
SELECT
    r.label AS id,
    r.label AS name
FROM releases r
INNER JOIN artist_info ai
    ON ai.artist_id = r.artist_id
    AND ai.vendor_id = :vendor_id
INNER JOIN product_type pt
    ON r.product_type_id = pt.id
WHERE TRIM(r.label) != ''
GROUP BY r.label
ORDER BY r.label
"""

# SELECT_PRODUCT_IMPRINTS_BY_SUBACCOUNT_ID is used to fetch all options for
# imprint given a subaccount id.
SELECT_PRODUCT_IMPRINTS_BY_SUBACCOUNT_ID = """
SELECT
    r.label AS id,
    r.label AS name
FROM releases r
INNER JOIN product_type pt
    ON r.product_type_id = pt.id
WHERE r.subaccount_id = :subaccount_id
AND TRIM(r.label) != ''
GROUP BY r.label
ORDER BY r.label
"""

# SELECT_PRODUCT_SUBGENRES is used to fetch all options for product subgenre
# given a product genre id.
SELECT_PRODUCT_SUBGENRES = """
SELECT
    s.orchard_id AS id,
    s.name,
    s.composer
FROM subgenre s
WHERE s.genre_id = :genre_id
ORDER BY s.name
"""

# SELECT_PROJECT_BY_ID is used to get project by id
SELECT_PROJECT_BY_ID = """
SELECT
    p.project_id,
    p.project_code,
    p.vendor_id,
    v.name as vendor_name,
    p.subaccount_id,
    s.subaccount_name,
    p.project_name,
    p.artist_id,
    p.created_date_utc,
    p.updated_date_utc,
    p.description,
    p.deletions,
    count(distinct r.release_id) as product_count
FROM project p
LEFT JOIN
  releases r on p.project_id = r.project_id
AND
  r.deletions = :include_deletions
LEFT JOIN
  vendor v on p.vendor_id = v.vendor_id
LEFT JOIN
  subaccount s on p.subaccount_id = s.subaccount_id
WHERE p.project_id = :project_id
AND p.deletions = :include_deletions
GROUP BY r.project_id
"""


# Used to get project by id with deletions
SELECT_PROJECT_BY_ID_INCLUDING_DELETED = """
SELECT
    p.project_id,
    p.project_code,
    p.vendor_id,
    v.name as vendor_name,
    p.subaccount_id,
    s.subaccount_name,
    p.project_name,
    p.artist_id,
    p.created_date_utc,
    p.updated_date_utc,
    p.description,
    p.deletions,
    count(distinct r.release_id) as product_count
FROM project p
LEFT JOIN
  releases r on p.project_id = r.project_id
AND
  r.deletions = :include_deletions
LEFT JOIN
  vendor v on p.vendor_id = v.vendor_id
LEFT JOIN
  subaccount s on p.subaccount_id = s.subaccount_id
WHERE p.project_id = :project_id
GROUP BY r.project_id
"""

# SELECT_PROJECTS_BY_IDS is used to bulk-fetch projects by a list of ids
SELECT_PROJECTS_BY_IDS = """
SELECT
    p.project_id,
    p.project_code,
    p.vendor_id,
    v.name as vendor_name,
    p.subaccount_id,
    s.subaccount_name,
    p.project_name,
    p.artist_id,
    p.created_date_utc,
    p.updated_date_utc,
    p.description,
    p.deletions,
    count(distinct r.release_id) as product_count
FROM project p
LEFT JOIN
  releases r on p.project_id = r.project_id
AND
  r.deletions = :include_deletions
LEFT JOIN
  vendor v on p.vendor_id = v.vendor_id
LEFT JOIN
  subaccount s on p.subaccount_id = s.subaccount_id
WHERE p.project_id IN :project_ids
AND p.deletions = :include_deletions
GROUP BY p.project_id
"""


# SELECT_PROJECT_AND_ARTIST_INFO_BY_ID is used to get project by id
SELECT_PROJECT_AND_ARTIST_INFO_BY_ID = """
SELECT
    p.project_id,
    p.project_code,
    p.vendor_id,
    p.subaccount_id,
    p.project_name,
    p.created_date_utc,
    p.updated_date_utc,
    p.correlation_id,
    p.description,
    p.artist_id,
    p.deletions,
    a.name as artist_name,
    a.description as artist_description,
    a.url as artist_url,
    s.subaccount_name
FROM project p
LEFT JOIN
  artist_info a on p.artist_id = a.artist_id
LEFT JOIN
  subaccount s on p.subaccount_id = s.subaccount_id
WHERE p.project_id = :project_id
"""

# SELECT_PROJECT_WITH_TENANT_UUIDS is used to get project by id with tenant uuids.
SELECT_PROJECT_WITH_TENANT_UUIDS = """
SELECT
    p.project_id,
    p.project_code,
    p.vendor_id,
    p.subaccount_id,
    p.project_name,
    p.created_date_utc,
    p.updated_date_utc,
    p.correlation_id,
    p.description,
    p.artist_id,
    p.deletions,
    v.vendor_uuid AS vendorUUID,
    s.subaccount_uuid AS subaccountUUID,
    cb.uuid AS companyBrandUUID,
    pc.uuid AS parentCompanyUUID,
    a.name AS artist_name,
    a.description AS artist_description,
    a.url AS artist_url,
    s.subaccount_name
FROM project p
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
  artist_info a ON p.artist_id = a.artist_id
LEFT JOIN
  subaccount s ON p.subaccount_id = s.subaccount_id
WHERE p.project_id = :project_id
"""

# SELECT_PRODUCTS_BY_PROJECT_ID is used to get a list of products for a project
SELECT_PRODUCTS_BY_PROJECT_ID = """
SELECT
    r.release_id as product_id,
    r.version,
    r.delivered_version,
    pt.id as product_type_id,
    pt.product_type,
    r.release_name,
    r.display_upc,
    r.upc,
    r.format,
    r.not_for_distribution,
    dfm.name as distribution_format_name,
    dt.distribution_format_id as distribution_format_id,
    dt.context_type,
    raq.status as release_approval_status
FROM releases r
INNER JOIN product_type pt ON (r.product_type_id = pt.id)
INNER JOIN project p ON (p.project_id = r.project_id)
INNER JOIN distribution_format dt ON
    (r.distribution_format_id = dt.distribution_format_id )
INNER JOIN distribution_format_media dfm ON
    (dt.distribution_format_media_id = dfm.distribution_format_media_id)
LEFT JOIN (
    SELECT raqi_a.release_approval_id, raqi_a.status, raqi_a.release_id
    FROM release_approval_queue raqi_a
    INNER JOIN (
        SELECT MAX(raqi_b.release_approval_id) max_raid
        FROM release_approval_queue raqi_b
        INNER JOIN releases raqir
        ON raqir.project_id = :project_id
        AND raqir.release_id = raqi_b.release_id
        GROUP BY raqi_b.release_id
    ) max_release_approval_id
    ON max_release_approval_id.max_raid = raqi_a.release_approval_id
) raq ON
    (raq.release_id = r.release_id)
WHERE
  p.project_id = :project_id
AND
  p.deletions = :include_deletions
AND
  r.deletions = :include_deletions
GROUP BY r.release_id
"""

# SELECT_PRODUCT_BY_PRODUCT_ID is used to get a single product for a project
SELECT_PRODUCT_BY_PRODUCT_ID = """
SELECT
    r.release_id as product_id,
    r.version,
    r.delivered_version,
    pt.id as product_type_id,
    pt.product_type,
    r.release_name,
    r.display_upc,
    r.upc,
    dfm.name as distribution_format_name,
    dt.distribution_format_id as distribution_format_id,
    dt.context_type,
    r.format,
    r.not_for_distribution,
    raq.status as release_approval_status
FROM releases r
INNER JOIN product_type pt ON (r.product_type_id = pt.id)
INNER JOIN project p ON (p.project_id = r.project_id)
INNER JOIN distribution_format dt ON
    (r.distribution_format_id = dt.distribution_format_id )
INNER JOIN distribution_format_media dfm ON
    (dt.distribution_format_media_id = dfm.distribution_format_media_id)
LEFT JOIN (
    SELECT raqi.status, raqi.release_id
    FROM release_approval_queue raqi
    WHERE raqi.release_id = :product_id
    ORDER BY raqi.release_approval_id DESC
    LIMIT 1
) raq ON
    (raq.release_id = r.release_id)
WHERE
  r.release_id = :product_id
AND
  p.project_id = :project_id
AND
  p.deletions = 'N'
GROUP BY r.release_id
"""

# Selects release_status including correction and action required statuses
SELECT_RELEASE_STATUS_BY_RELEASE_ID = """
SELECT
r.`release_status`,
ra.`status` AS review_status,
rc.`status` AS correction_status
FROM `releases` r
LEFT JOIN (
    SELECT
    `release_id`,
    `status`
    FROM `release_approval_queue`
    WHERE `release_id` = :release_id
    ORDER BY `release_approval_id` DESC
    LIMIT 1
) ra ON r.`release_id` = ra.`release_id`
LEFT JOIN (
    SELECT
    `release_id`,
    `status`
    FROM `release_correction`
    WHERE `release_id` = :release_id
    ORDER BY `release_correction_id` DESC
    LIMIT 1
) rc ON r.`release_id` = rc.`release_id`
WHERE r.`release_id` = :release_id
"""

# SELECT_ARTISTS_BY_RELEASE_ID is gets a list of artist names for a release
SELECT_ARTISTS_BY_RELEASE_ID = """
SELECT
  ra.artist_name
FROM release_artist ra
WHERE
  ra.release_id = :release_id
  and
  ra.role = 'performer'
"""

# SELECT_PROJECTS_BY_VENDOR is used to get all projects for a vendor
SELECT_PROJECTS_BY_VENDOR_ID = """
SELECT
    p.project_id,
    p.project_code,
    p.vendor_id,
    p.subaccount_id,
    p.project_name,
    p.artist_id,
    p.created_date_utc,
    p.updated_date_utc,
    p.deletions,
    count(distinct r.release_id) as product_count
FROM project p
LEFT JOIN
  releases r ON p.project_id = r.project_id
AND
  r.deletions = :include_deletions
WHERE p.vendor_id = :vendor_id
AND
  p.deletions = :include_deletions
GROUP BY p.project_id
ORDER BY p.created_date_utc DESC, p.project_name
LIMIT :page_offset, :page_limit
"""

# SELECT_PROJECTS_BY_SUBACCOUNT is used to get projects for a subaccount
SELECT_PROJECTS_BY_SUBACCOUNT_ID = """
SELECT
    p.project_id,
    p.project_code,
    p.vendor_id,
    p.subaccount_id,
    p.project_name,
    p.artist_id,
    p.created_date_utc,
    p.updated_date_utc,
    p.deletions,
    count(distinct r.release_id) as product_count
FROM project p
LEFT JOIN
  releases r ON p.project_id = r.project_id
AND
  r.deletions = :include_deletions
WHERE p.subaccount_id = :subaccount_id
AND
  p.deletions = :include_deletions
GROUP BY p.project_id
ORDER BY p.created_date_utc DESC, p.project_name
LIMIT :page_offset, :page_limit
"""

# SELECT_PROJECTS_BY_VENDOR_ID_COUNT is used to get the count of projects
# for a vendor
SELECT_PROJECTS_BY_VENDOR_ID_COUNT = """
SELECT count(distinct project_id)
FROM project
WHERE vendor_id = :vendor_id
AND deletions = 'N'
"""

# SELECT_PROJECTS_BY_SUBACCOUNT_ID_COUNT is used to get the count of projects
# for a subaccount
SELECT_PROJECTS_BY_SUBACCOUNT_ID_COUNT = """
SELECT count(distinct project_id)
FROM project
WHERE subaccount_id = :subaccount_id
AND deletions = 'N'
"""

# SELECT_PROJECT_BY_PROJECT_VENDOR_SUBACCOUNT is used to get a project
# by project_code, vendor_id, and subaccount_id
SELECT_PROJECT_BY_PROJECT_VENDOR_SUBACCOUNT = """
SELECT project_id,
       project_code,
       artist_id,
       vendor_id,
       subaccount_id,
       project_name,
       created_date_utc,
       updated_date_utc,
       correlation_id,
       description,
       deletions
FROM   project
WHERE  LOWER(project_code) = LOWER(:project_code)
       AND vendor_id = :vendor_id
       AND subaccount_id = :subaccount_id
"""

# INSERT_PROJECT is used to insert a single row into project table
INSERT_PROJECT = """
INSERT INTO project
       (project_code,
        vendor_id,
        subaccount_id,
        project_name,
        created_date_utc,
        updated_date_utc,
        correlation_id,
        artist_id,
        description)
VALUES(:project_code,
       :vendor_id,
       :subaccount_id,
       :project_name,
       :created_date_utc,
       :updated_date_utc,
       :correlation_id,
       :artist_id,
       :description)
"""

# UPDATE_PROJECT_BY_ID is used to update a project with a given project_id
UPDATE_PROJECT_BY_ID = """
UPDATE project
SET
    artist_id = :artist_id,
    project_name = :project_name,
    project_code = :project_code,
    updated_date_utc = :updated_date_utc,
    description = :description
WHERE
    project_id = :project_id
"""

GET_PROJECT_BY_PROJECT_CODE = """
SELECT
    p.project_id,
    p.project_code,
    p.vendor_id,
    v.name as vendor_name,
    p.subaccount_id,
    s.subaccount_name,
    p.project_name,
    p.artist_id,
    p.created_date_utc,
    p.updated_date_utc,
    p.description,
    p.deletions,
    count(distinct r.release_id) as product_count
FROM project p
LEFT JOIN
  releases r on p.project_id = r.project_id
AND
  r.deletions = :include_deletions
LEFT JOIN
  vendor v on p.vendor_id = v.vendor_id
LEFT JOIN
  subaccount s on p.subaccount_id = s.subaccount_id
WHERE p.project_code = :project_code
AND p.vendor_id = :account_id
AND p.subaccount_id = :subaccount_id
AND p.deletions = :include_deletions
GROUP BY r.project_id
"""

GET_PROJECTS_BY_PROJECT_CODES = """
SELECT
    p.project_id,
    p.project_code,
    p.project_name,
    p.deletions,
    ai.name as artist_name
FROM project p
LEFT JOIN
  vendor v on p.vendor_id = v.vendor_id
LEFT JOIN
  subaccount s on p.subaccount_id = s.subaccount_id
LEFT JOIN
  artist_info ai on ai.artist_id = p.artist_id
WHERE p.project_code IN :project_codes
AND v.vendor_uuid = :account_uuid
AND s.subaccount_uuid = :subaccount_uuid
GROUP BY p.project_id
"""

GET_PROJECTS_BY_PROJECT_CODES_VENDOR_ONLY = """
SELECT
    p.project_id,
    p.project_code,
    p.project_name,
    p.deletions,
    ai.name as artist_name
FROM project p
LEFT JOIN
    vendor v on p.vendor_id = v.vendor_id
LEFT JOIN
    artist_info ai on ai.artist_id = p.artist_id
WHERE p.project_code IN :project_codes
AND v.vendor_uuid = :account_uuid
GROUP BY p.project_id
"""

# UPDATE_PROJECT_BY_ID_WITH_USER_ID_AND_TYPE is used to update a project with
# a given project_id. It will also update the last_modified_by and user_type.
UPDATE_PROJECT_BY_ID_WITH_USER_ID_AND_TYPE = """
UPDATE project
SET
    artist_id = :artist_id,
    project_name = :project_name,
    project_code = :project_code,
    updated_date_utc = :updated_date_utc,
    description = :description,
    last_modified_by = :user_id,
    user_type = :user_type,
    deletions = :deletions
WHERE
    project_id = :project_id
"""

# INSERT_PROJECT_WITH_USER_ID_AND_TYPE is used to insert a single row into
# project table. It will also add the last_modified_by and user_type.
INSERT_PROJECT_WITH_USER_ID_AND_TYPE = """
INSERT INTO project
       (project_code,
        vendor_id,
        subaccount_id,
        project_name,
        created_date_utc,
        updated_date_utc,
        correlation_id,
        artist_id,
        description,
        last_modified_by,
        user_type)
VALUES(:project_code,
       :vendor_id,
       :subaccount_id,
       :project_name,
       :created_date_utc,
       :updated_date_utc,
       :correlation_id,
       :artist_id,
       :description,
       :last_modified_by,
       :user_type)
"""