"""SQL queries of the legacy database."""

PRODUCT_PRICING_TIERS = """
SELECT r.release_id, dpt.pricing_tier, dmps.pricing_scheme
FROM releases r
LEFT OUTER JOIN release_pricing_tier rpt ON r.release_id = rpt.release_id
LEFT OUTER JOIN dms_pricing_tier dpt ON rpt.pricing_tier_id=dpt.pricing_tier_id
LEFT OUTER JOIN dms_master_pricing_scheme dmps
ON dmps.pricing_scheme_id=dpt.pricing_scheme_id
WHERE r.release_id=:product_id AND rpt.active=:active_status
"""

DEFAULT_PRICING_CODES = """
SELECT c.abbrivation AS country_code, dpt.price_code, dpt.pricing_tier
  FROM  dms_master_pricing_scheme dmps
INNER JOIN dms_master_pricing_scheme_territory dmpst
  ON dmpst.pricing_scheme_id = dmps.pricing_scheme_id
INNER JOIN country c ON c.id = dmpst.country_id
INNER JOIN dms_pricing_tier dpt
  ON dpt.pricing_tier_id = dmps.default_pricing_tier
WHERE dmps.customer_master_master_id=:store_id
  AND dmps.product_type_id=:product_type_id
  AND dmps.scheme_level=:scheme_level
"""

PRODUCT_STORE_PRICING = """
SELECT c.abbrivation as country_code, dpt.price_code,
  cp.custom_price, cp.currency_code, dpt.pricing_tier
FROM dms_master_pricing_scheme dmps
INNER JOIN dms_master_pricing_scheme_territory dmpst
  ON dmpst.pricing_scheme_id = dmps.pricing_scheme_id
INNER JOIN country c ON c.id = dmpst.country_id
INNER JOIN dms_pricing_tier dpt
  ON dpt.pricing_scheme_id = dmps.pricing_scheme_id
INNER JOIN release_pricing_tier pt ON pt.pricing_tier_id = dpt.pricing_tier_id
INNER JOIN release_pricing_tier_country ptc
  ON ptc.release_pricing_tier_id = pt.id AND ptc.country_id=dmpst.country_id
LEFT JOIN release_custom_pricing cp ON cp.release_pricing_tier_id=pt.id
WHERE dmps.customer_master_master_id=:store_id
        AND dmps.product_type_id=:product_type_id
        AND dmps.scheme_level=:scheme_level
        AND pt.active = :active_status
        AND pt.release_id=:product_id
"""

PRODUCT_STORE_PRICING_ALL = """
SELECT dmps.customer_master_master_id, c.abbrivation as country_code,
  dpt.price_code, cp.custom_price, cp.currency_code, dpt.pricing_tier
FROM dms_master_pricing_scheme dmps
INNER JOIN dms_master_pricing_scheme_territory dmpst
  ON dmpst.pricing_scheme_id = dmps.pricing_scheme_id
INNER JOIN country c ON c.id = dmpst.country_id
INNER JOIN dms_pricing_tier dpt
  ON dpt.pricing_scheme_id = dmps.pricing_scheme_id
INNER JOIN release_pricing_tier pt ON pt.pricing_tier_id = dpt.pricing_tier_id
INNER JOIN release_pricing_tier_country ptc
  ON ptc.release_pricing_tier_id = pt.id AND ptc.country_id=dmpst.country_id
LEFT JOIN release_custom_pricing cp ON cp.release_pricing_tier_id=pt.id
WHERE dmps.product_type_id=:product_type_id
        AND dmps.scheme_level=:scheme_level
        AND pt.active = :active_status
        AND pt.release_id=:product_id
"""

PRODUCT_STORE_PRICING_EXCLUDE = """
SELECT dmps.customer_master_master_id, c.abbrivation as country_code,
  dpt.price_code, cp.custom_price, cp.currency_code, dpt.pricing_tier
FROM dms_master_pricing_scheme dmps
INNER JOIN dms_master_pricing_scheme_territory dmpst
  ON dmpst.pricing_scheme_id = dmps.pricing_scheme_id
INNER JOIN country c ON c.id = dmpst.country_id
INNER JOIN dms_pricing_tier dpt
  ON dpt.pricing_scheme_id = dmps.pricing_scheme_id
INNER JOIN release_pricing_tier pt ON pt.pricing_tier_id = dpt.pricing_tier_id
INNER JOIN release_pricing_tier_country ptc
  ON ptc.release_pricing_tier_id = pt.id AND ptc.country_id=dmpst.country_id
LEFT JOIN release_custom_pricing cp ON cp.release_pricing_tier_id=pt.id
WHERE dmps.product_type_id=:product_type_id
        AND dmps.scheme_level=:scheme_level
        AND pt.active = :active_status
        AND (dmps.customer_master_master_id NOT IN :exclude_store_ids OR
          (dmps.customer_master_master_id = :customer_master_master_id
          AND c.abbrivation = :abbrivation))
        AND pt.release_id=:product_id
"""

TRACK_STORE_PRICING = """
SELECT t.id, c.abbrivation as country_code, dpt.price_code,
  cp.custom_price, cp.currency_code
FROM dms_master_pricing_scheme dmps
INNER JOIN dms_master_pricing_scheme_territory dmpst
  ON dmpst.pricing_scheme_id = dmps.pricing_scheme_id
INNER JOIN country c ON c.id = dmpst.country_id
INNER JOIN dms_pricing_tier dpt
  ON dpt.pricing_scheme_id = dmps.pricing_scheme_id
INNER JOIN track_pricing_tier pt ON pt.pricing_tier_id = dpt.pricing_tier_id
INNER JOIN track_pricing_tier_country ptc
  ON ptc.track_pricing_tier_id = pt.id AND ptc.country_id=dmpst.country_id
INNER JOIN track t ON t.id = pt.unique_track_id
LEFT JOIN track_custom_pricing cp ON cp.track_pricing_tier_id=pt.id
WHERE dmps.customer_master_master_id=:store_id
        AND dmps.product_type_id=:product_type_id
        AND pt.active = :active_status
        AND t.release_id=:product_id
        AND dmps.scheme_level=:scheme_level
"""

TRACK_STORE_PRICING_ALL = """
SELECT t.id, dmps.customer_master_master_id, dpt.price_code,
  c.abbrivation as country_code, cp.custom_price, cp.currency_code,
  dpt.pricing_tier
FROM dms_master_pricing_scheme dmps
INNER JOIN dms_master_pricing_scheme_territory dmpst
  ON dmpst.pricing_scheme_id = dmps.pricing_scheme_id
INNER JOIN country c ON c.id = dmpst.country_id
INNER JOIN dms_pricing_tier dpt
  ON dpt.pricing_scheme_id = dmps.pricing_scheme_id
INNER JOIN track_pricing_tier pt ON pt.pricing_tier_id = dpt.pricing_tier_id
INNER JOIN track_pricing_tier_country ptc
  ON ptc.track_pricing_tier_id = pt.id AND ptc.country_id=dmpst.country_id
INNER JOIN track t ON t.id = pt.unique_track_id
LEFT JOIN track_custom_pricing cp ON cp.track_pricing_tier_id=pt.id
WHERE dmps.product_type_id=:product_type_id
        AND pt.active = :active_status
        AND t.release_id=:product_id
        AND dmps.scheme_level=:scheme_level
ORDER BY t.id, dmps.customer_master_master_id, dpt.price_code
"""

TRACK_STORE_PRICING_EXCLUDE = """
SELECT t.id, dmps.customer_master_master_id, dpt.price_code,
  c.abbrivation as country_code, cp.custom_price, cp.currency_code,
  dpt.pricing_tier
FROM dms_master_pricing_scheme dmps
INNER JOIN dms_master_pricing_scheme_territory dmpst
  ON dmpst.pricing_scheme_id = dmps.pricing_scheme_id
INNER JOIN country c ON c.id = dmpst.country_id
INNER JOIN dms_pricing_tier dpt
  ON dpt.pricing_scheme_id = dmps.pricing_scheme_id
INNER JOIN track_pricing_tier pt ON pt.pricing_tier_id = dpt.pricing_tier_id
INNER JOIN track_pricing_tier_country ptc
  ON ptc.track_pricing_tier_id = pt.id AND ptc.country_id=dmpst.country_id
INNER JOIN track t ON t.id = pt.unique_track_id
LEFT JOIN track_custom_pricing cp ON cp.track_pricing_tier_id=pt.id
WHERE dmps.product_type_id=:product_type_id
        AND pt.active = :active_status
        AND (dmps.customer_master_master_id NOT IN :exclude_store_ids OR
          (dmps.customer_master_master_id = :customer_master_master_id
          AND c.abbrivation = :abbrivation))
        AND t.release_id=:product_id
        AND dmps.scheme_level=:scheme_level
ORDER BY t.id, dmps.customer_master_master_id, dpt.price_code
"""

PRODUCT_TRACKS = 'SELECT id, isrc FROM track WHERE release_id = :product_id'

FILM_PRODUCT_TYPE_ID = 3

ALL_FILM_PRODUCTS = """
SELECT release_id
FROM releases
WHERE product_type_id=:product_type_id
AND COALESCE(product_subtype_id, 1) != :excluded_subtype_id
"""

MUSIC_RELEASES = """
SELECT release_id
FROM releases
WHERE product_type_id = :product_type_id
"""