"""This file contains sql for the product_physical API endpoints."""
from sqlalchemy import text

# QUERY_DB_HEARTBEAT is used to check service-to-db connectivity
QUERY_DB_HEARTBEAT = text("""
select 'ows-product-physical db heartbeat'
""")


# Reserve a upc
RESERVE_UPC = text("""
CALL sp_set_upc_to_used
""")


# SELECT_PRODUCT_BY_RELEASE_ID is used to fetch a product entity
SELECT_PRODUCT_BY_RELEASE_ID = text("""
SELECT
    r.release_id as product_id,
    r.release_name as product_name,
    r.display_upc as display_upc,
    r.upc as upc,
    r.manufacturer_upc,
    r.subaccount_id,
    r.project_id,
    r.label,
    r.artist_id,
    REPLACE(GROUP_CONCAT(ra.artist_name), ',', ', ') as primary_artist,
    r.version,
    r.c_line as cline,
    r.release_status,
    r.genre_id,
    r.release_date,
    r.sale_start_date,
    r.new_release as product_type,
    r.distribution_format_id,
    r.product_code,
    r.special_instructions,
    r.description,
    pp.packaging_id,
    pp.exclusive_for,
    pp.initial_stock,
    pp.units_per_set,
    pp.box_lot,
    pp.pricing,
    pp.end_date,
    pp.discount,
    pp.wholesale_price,
    pp.individual as artist_is_individual,
    pp.explicit,
    pp.manufacturing_obligation,
    r.country_of_origin,
    pp.pline,
    pp.production_notes,
    rs.subgenre_id,
    pp.display_configuration,
    pp.japan_distribution,
    pp.edition

FROM `releases` r
INNER JOIN `product_physical` pp
    ON pp.release_id = r.release_id
LEFT JOIN `release_subgenre` rs
    ON rs.release_id = r.release_id
    AND rs.upc = r.upc
LEFT JOIN `release_artist` ra
    ON ra.release_id = r.release_id

WHERE r.release_id = :release_id
GROUP BY r.release_id
""")


# SELECT all physical releases
SELECT_PHYSICAL_RELEASE_COUNT_BY_PRODUCT_CODE = text("""
SELECT
    count(*)
FROM
    `releases` r
LEFT JOIN `distribution_format` df
    ON df.distribution_format_id = r.distribution_format_id
WHERE
    df.context_type = 'physical'
    AND
    r.product_code = :product_code
    AND
    r.release_id != :product_id
""")

# SELECT_UPC_BY_PRODUCT_ID is used to fetch upc for a product
SELECT_UPC_BY_PRODUCT_ID = text("""
SELECT
    upc
FROM
    releases r
INNER JOIN product_physical pp
    ON pp.release_id = r.release_id
WHERE
    r.release_id = :product_id
""")


SET_UPC_TO_USED = text("""
update upcs set status = 'used' where upc = :upc and status = 'claimed'
""")