"""SQL queries for physical reporting dashboard views."""

SELECT_VENDOR_SUBACCOUNT_PAIRS = """
    SELECT DISTINCT
        p.vendor_id,
        p.subacct_id
    FROM PHYSICAL_PRODUCT_METADATA_VIEW p
    WHERE p.supply_chain_id = :supply_chain_id
        AND p.vendor_id IN :vendor_ids
        AND p.subacct_id IN :subaccount_ids
"""

SELECT_TOP_OPEN_ORDERS = """
    SELECT
        SUM(s.order_qt) AS open_order_qt,
        p.artist_nm || '/' || p.product_nm AS artist_product,
        p.local_product_cd,
        p.vendor_id,
        p.subacct_id
    FROM PHYSICAL_PRODUCT_METADATA_VIEW p
    JOIN PHYSICAL_PRODUCT_SALES_RTD_VIEW s
        ON s.local_product_cd = p.local_product_cd
        AND s.supply_chain_id = p.supply_chain_id
    WHERE p.supply_chain_id = :supply_chain_id
        AND s.order_qt <> 0
        AND p.vendor_id IN :vendor_ids
        {subaccount_filter}
    GROUP BY 2, 3, 4, 5
    ORDER BY 1 DESC
    LIMIT 10
"""

SELECT_TOP_YESTERDAY_SHIPMENTS = """
    SELECT
        SUM(s.day1_ship_qt) AS yesterday_ship_qt,
        p.artist_nm || '/' || p.product_nm AS artist_product,
        p.local_product_cd,
        p.vendor_id,
        p.subacct_id
    FROM PHYSICAL_PRODUCT_METADATA_VIEW p
    JOIN PHYSICAL_PRODUCT_SALES_RTD_VIEW s
        ON s.local_product_cd = p.local_product_cd
        AND s.supply_chain_id = p.supply_chain_id
    WHERE p.supply_chain_id = :supply_chain_id
        AND s.day1_ship_qt <> 0
        AND p.vendor_id IN :vendor_ids
        {subaccount_filter}
    GROUP BY 2, 3, 4, 5
    ORDER BY 1 DESC
    LIMIT 10
"""

SELECT_TOP_MTD_SHIPMENTS = """
    SELECT
        SUM(s.mtd_ship_qt) AS mtd_ship_qt,
        p.artist_nm || '/' || p.product_nm AS artist_product,
        p.local_product_cd,
        p.vendor_id,
        p.subacct_id
    FROM PHYSICAL_PRODUCT_METADATA_VIEW p
    JOIN PHYSICAL_PRODUCT_SALES_RTD_VIEW s
        ON s.local_product_cd = p.local_product_cd
        AND s.supply_chain_id = p.supply_chain_id
    WHERE p.supply_chain_id = :supply_chain_id
        AND s.mtd_ship_qt <> 0
        AND p.vendor_id IN :vendor_ids
        {subaccount_filter}
    GROUP BY 2, 3, 4, 5
    ORDER BY 1 DESC
    LIMIT 10
"""

SELECT_LABEL_SUBACCOUNT_SUMMARY = """
    SELECT
        p.label_nm AS "label name",
        p.sublabel_nm AS "sub label",
        SUM(s.order_qt) AS "open orders",
        SUM(s.back_order_qt) AS "backorders",
        SUM(s.day1_ship_qt) AS "day1s#",
        SUM(s.mtd_ship_qt) AS "mtds#",
        SUM(s.cytd_ship_qt) AS "cytds#",
        SUM(s.rtd_ship_qt) AS "rtds#",
        SUM(s.day1_return_qt) AS "day1r#",
        SUM(s.mtd_return_qt) AS "mtdr#",
        SUM(s.cytd_return_qt) AS "cytdr#",
        SUM(s.rtd_return_qt) AS "rtdr#",
        SUM(s.day1_ship_qt + s.day1_return_qt) AS "day1net#",
        SUM(s.mtd_ship_qt + s.mtd_return_qt) AS "mtdnet#",
        SUM(s.cytd_ship_qt + s.cytd_return_qt) AS "cytdnet#",
        SUM(s.rtd_ship_qt + s.rtd_return_qt) AS "rtdnet#",
        SUM(s.order_am)::FLOAT AS "open orders$",
        SUM(s.back_order_am)::FLOAT AS "backorders$",
        SUM(s.day1_ship_am)::FLOAT AS "day1s$",
        SUM(s.mtd_ship_am)::FLOAT AS "mtds$",
        SUM(s.cytd_ship_am)::FLOAT AS "cytds$",
        SUM(s.rtd_ship_am)::FLOAT AS "rtds$",
        SUM(s.day1_return_am)::FLOAT AS "day1r$",
        SUM(s.mtd_return_am)::FLOAT AS "mtdr$",
        SUM(s.cytd_return_am)::FLOAT AS "cytdr$",
        SUM(s.rtd_return_am)::FLOAT AS "rtdr$",
        SUM(s.day1_ship_am + s.day1_return_am)::FLOAT AS "day1net$",
        SUM(s.mtd_ship_am + s.mtd_return_am)::FLOAT AS "mtdnet$",
        SUM(s.cytd_ship_am + s.cytd_return_am)::FLOAT AS "cytdnet$",
        SUM(s.rtd_ship_am + s.rtd_return_am)::FLOAT AS "rtdnet$",
        p.vendor_id,
        p.subacct_id
    FROM PHYSICAL_PRODUCT_METADATA_VIEW p
    JOIN PHYSICAL_PRODUCT_SALES_RTD_VIEW s
        ON s.local_product_cd = p.local_product_cd
        AND s.supply_chain_id = p.supply_chain_id
    WHERE p.supply_chain_id = :supply_chain_id
        AND p.vendor_id IN :vendor_ids
        {subaccount_filter}
    GROUP BY p.label_nm, p.sublabel_nm, p.vendor_id, p.subacct_id
    ORDER BY 1, 2
"""

SELECT_PRODUCT_DETAIL = """
    SELECT
        p.label_nm,
        p.sublabel_nm AS subacct_nm,
        p.artist_nm,
        p.product_nm,
        TRIM(p.product_cd) AS product_cd,
        TRIM(p.local_product_cd) AS local_product_cd,
        p.upc_cd,
        p.display_configuration,
        p.status_nm,
        TO_CHAR(p.release_dt, 'YYYY-MM-DD') AS release_dt,
        p.exclusive_for,
        p.price_cd,
        p.series_cd,
        p.units_per_set,
        p.genre_nm,
        p.subgenre_nm,
        SUM(s.order_qt) AS order_qt,
        SUM(s.back_order_qt) AS back_order_qt,
        SUM(s.day1_ship_qt) AS yest_ship_qt,
        SUM(s.mtd_ship_qt) AS mtd_ship_qt,
        SUM(s.cytd_ship_qt) AS ytd_ship_qt,
        SUM(s.rtd_ship_qt) AS rtd_ship_qt,
        SUM(s.day1_return_qt) AS yest_return_qt,
        SUM(s.mtd_return_qt) AS mtd_return_qt,
        SUM(s.cytd_return_qt) AS ytd_return_qt,
        SUM(s.rtd_return_qt) AS rtd_return_qt,
        SUM(s.day1_ship_qt + s.day1_return_qt) AS yest_net_qt,
        SUM(s.mtd_ship_qt + s.mtd_return_qt) AS mtd_net_qt,
        SUM(s.cytd_ship_qt + s.cytd_return_qt) AS ytd_net_qt,
        SUM(s.rtd_ship_qt + s.rtd_return_qt) AS rtd_net_qt,
        SUM(s.order_am)::FLOAT AS order_am,
        SUM(s.back_order_am)::FLOAT AS back_order_am,
        SUM(s.day1_ship_am)::FLOAT AS yest_ship_am,
        SUM(s.mtd_ship_am)::FLOAT AS mtd_ship_am,
        SUM(s.cytd_ship_am)::FLOAT AS ytd_ship_am,
        SUM(s.rtd_ship_am)::FLOAT AS rtd_ship_am,
        SUM(s.day1_return_am)::FLOAT AS yest_return_am,
        SUM(s.mtd_return_am)::FLOAT AS mtd_return_am,
        SUM(s.cytd_return_am)::FLOAT AS ytd_return_am,
        SUM(s.rtd_return_am)::FLOAT AS rtd_return_am,
        SUM(s.day1_ship_am + s.day1_return_am)::FLOAT AS yest_net_am,
        SUM(s.mtd_ship_am + s.mtd_return_am)::FLOAT AS mtd_net_am,
        SUM(s.cytd_ship_am + s.cytd_return_am)::FLOAT AS ytd_net_am,
        SUM(s.rtd_ship_am + s.rtd_return_am)::FLOAT AS rtd_net_am,
        p.vendor_id,
        p.subacct_id
    FROM PHYSICAL_PRODUCT_METADATA_VIEW p
    JOIN PHYSICAL_PRODUCT_SALES_RTD_VIEW s
        ON s.local_product_cd = p.local_product_cd
        AND s.supply_chain_id = p.supply_chain_id
    WHERE p.supply_chain_id = :supply_chain_id
        AND p.vendor_id IN :vendor_ids
        {subaccount_filter}
    GROUP BY
        p.label_nm, p.sublabel_nm, p.artist_nm, p.product_nm,
        p.product_cd,  p.local_product_cd, p.upc_cd, p.display_configuration,
        p.status_nm, TO_CHAR(p.release_dt, 'YYYY-MM-DD'),
        p.exclusive_for, p.price_cd, p.series_cd,
        p.units_per_set, p.genre_nm, p.subgenre_nm,
        p.vendor_id, p.subacct_id
    ORDER BY 1, 2, 3, 4
"""