"""SQL query templates module."""

DB_HEALTH_CHECK_QUERY = 'SELECT 1;'
PHYSICAL_TRANSACTIONS_SUM_SQL = """
SELECT
  vendor_id,
  sum(gross*fx_adjusted_exchange_rate) AS amount_in_original_currency,
  payout_currency_id AS currencies_id,
  cer.exchange_rate
FROM `{accountingflat}`.`TEMP_reserves_processed_dig_sales` pds
  INNER JOIN `{art_relations}`.`releases` r USING (upc)
  INNER JOIN `{art_relations}`.`artist_info` ai USING (artist_id)
  INNER JOIN `{art_relations}`.`currency_exchange_rates` cer
    ON cer.`currency_from_id` = payout_currency_id
    AND cer.`currency_to_id` = 1
    AND cer.period_id = (:period_id)
WHERE pds.period_id = (:period_id)
  AND trans_type IN ('PS','RE','TP','TR')
GROUP BY vendor_id
HAVING amount_in_original_currency > 0;
"""

VENDOR_CONTRACT_SQL = """
SELECT
  bvcs.vendor_id,
  bvcs.reserve_rate,
  bvcs.number_of_months_before_payout,
  bvcs.number_of_installments
FROM `booked_vendor_contract_snapshot` bvcs
WHERE vendor_id IN :label_ids
  AND period_id = :period_id;
"""

TRUNCATE_TEMP_TABLE = 'TRUNCATE TABLE `TEMP_reserves_processed_dig_sales`;'
INSERT_PHYS_TRANSACTIONS_FOR_PERIOD = """
INSERT INTO `TEMP_reserves_processed_dig_sales`
SELECT * FROM `processed_dig_sales`
WHERE period_id = :period_id AND trans_type IN ('PS','RE','TP','TR');"""