"""SQL queries for Single Product View."""

from reporting.constants.physical_reporting import (
    CA_SUPPLY_CHAIN,
    US_SUPPLY_CHAIN,
)

SELECT_PHYSICAL_PRODUCT_SALES_BY_MONTH_VIEW = """
SELECT * FROM PHYSICAL_PRODUCT_SALES_BY_MONTH_VIEW AS mv
JOIN PHYSICAL_CURRENT_PERIOD_VIEW cpv
ON mv.supply_chain_id = cpv.supply_chain_id
WHERE mv.LOCAL_PRODUCT_CD = :local_product_cd
AND mv.SUPPLY_CHAIN_ID = :supply_chain_id
AND mv.REPORTING_DT BETWEEN DATEADD(MONTH, -12, cpv.CURRENT_REPORTING_DT)
AND DATEADD(MONTH, -1, cpv.CURRENT_REPORTING_DT)
ORDER BY mv.REPORTING_DT DESC;
"""

SELECT_PHYSICAL_PRODUCT_OVERVIEW = """
SELECT * FROM PHYSICAL_PRODUCT_METADATA_VIEW meta
LEFT JOIN PHYSICAL_PRODUCT_INVENTORY_VIEW inv
ON meta.supply_chain_id = inv.supply_chain_id
AND meta.local_product_cd = inv.local_product_cd
LEFT JOIN PHYSICAL_PRODUCT_SALES_RTD_VIEW rtd
ON meta.supply_chain_id = rtd.supply_chain_id
AND meta.local_product_cd = rtd.local_product_cd
WHERE meta.LOCAL_PRODUCT_CD = :local_product_cd
AND meta.SUPPLY_CHAIN_ID = :supply_chain_id;
"""

SELECT_PHYSICAL_PRODUCT_SALES_BY_MONTH_VIEW = """
SELECT * FROM PHYSICAL_PRODUCT_SALES_BY_MONTH_VIEW AS mv
JOIN PHYSICAL_CURRENT_PERIOD_VIEW cpv
ON mv.supply_chain_id = cpv.supply_chain_id
WHERE mv.LOCAL_PRODUCT_CD = :local_product_cd
AND mv.SUPPLY_CHAIN_ID = :supply_chain_id
AND mv.REPORTING_DT BETWEEN DATEADD(MONTH, -12, cpv.CURRENT_REPORTING_DT)
AND DATEADD(MONTH, -1, cpv.CURRENT_REPORTING_DT)
ORDER BY mv.REPORTING_DT DESC;
"""

SELECT_US_ACCOUNT_BY_PRODUCT_CODE = """
SELECT phys.VENDOR_ID, phys.SUBACCT_ID
FROM US_PHYSICAL_PRODUCT_VIEW as phys
WHERE phys.LOCAL_PRODUCT_CD = :local_product_cd
AND phys.SUPPLY_CHAIN_ID = {};
""".format(US_SUPPLY_CHAIN)

SELECT_CA_ACCOUNT_BY_PRODUCT_CODE = """
SELECT phys.VENDOR_ID, phys.SUBACCT_ID
FROM CA_PHYSICAL_PRODUCT_VIEW as phys
WHERE phys.LOCAL_PRODUCT_CD = :local_product_cd
AND phys.SUPPLY_CHAIN_ID = {};
""".format(CA_SUPPLY_CHAIN)


def get_account_query(supply_chain_id):
    """Return query to use based on supply_chain_id."""
    if supply_chain_id == CA_SUPPLY_CHAIN:
        return SELECT_CA_ACCOUNT_BY_PRODUCT_CODE
    else:
        return SELECT_US_ACCOUNT_BY_PRODUCT_CODE