WITH auth AS (
  SELECT DISTINCT st.shopid
  FROM shopee_id_anlys.id_bi_seller_type st
  JOIN shopee_id_bi_team__rm_name_pass rm
  ON rm.rm_name = st.rm_name
  WHERE rm.rm_name IN ({{RM_name}}) AND rm.pass IN ({{Password}})
),

od AS (
  SELECT
    itemid, modelid,
    SUM(CASE WHEN promo_source = 'flash_sale' THEN COALESCE(o.order_fraction,0) END) AS fs_order,
    SUM(CASE WHEN promo_source != 'flash_sale' OR promo_source IS NULL THEN COALESCE(o.order_fraction,0) END) AS non_fs_order,
    SUM(COALESCE(o.order_fraction,0)) AS total_gross_order,
    SUM(CASE WHEN LOWER(o.be_status) NOT IN ('invalid', 'return_procesing', 'cancel_pending', 'cancel_completed', 'cancel_processing', 'return_completed') THEN COALESCE(order_fraction,0) END) AS net_order,
    SUM(CASE WHEN promo_source = 'flash_sale' THEN COALESCE(o.amount,0) END) AS fs_qty_sold,
    SUM(CASE WHEN promo_source != 'flash_sale' OR promo_source IS NULL THEN COALESCE(o.amount,0) END) AS non_fs_qty_sold,
    SUM(COALESCE(o.amount,0)) AS qty_sold,
    SUM(CASE WHEN promo_source = 'flash_sale' THEN COALESCE((order_price*o.amount),0) END) AS Total_FS_selling_price,
    SUM(CASE WHEN promo_source != 'flash_sale' THEN COALESCE((order_price*o.amount),0) END) AS Total_Non_FS_selling_price,
    SUM(order_price*o.amount)/SUM(amount) AS Avg_Non_FS_selling_price
  FROM
    order_mart__order_item_profile o
  WHERE grass_date BETWEEN DATE {{START_date}} AND DATE {{END_date}}
  AND shopid = {{shopid}}
  AND shopid IN (SELECT shopid FROM auth)
  GROUP BY 1,2
),

A AS (
SELECT
    ip.main_category,
    ip.sub_category,
    ip.level3_category,
    ip.itemid,
    ip.modelid,
  DATE(ip.item_ctime) AS registered_date,
    ip.item_name AS product_name,
    ip.model_name,
    ip.item_price AS current_price,
  CASE WHEN ip.item_price_before_discount != 0 THEN ip.item_price_before_discount ELSE ip.item_price END AS original_price,
    ip.item_stock,
  od.fs_order,
  od.non_fs_order,
  od.total_gross_order,
  od.net_order,
  od.fs_qty_sold,
  od.non_fs_qty_sold,
  od.qty_sold,
  od.Total_FS_selling_price,
  od.Total_Non_FS_selling_price,
  od.Avg_Non_FS_selling_price
FROM item_profile ip
LEFT JOIN od ON ip.itemid = od.itemid AND ip.modelid = od.modelid
)

SELECT A.*,
Total_FS_selling_price / fs_qty_sold AS Avg_FS_Selling_Price,
Total_Non_FS_selling_price / non_fs_qty_sold AS Avg_Non_FS_selling_price,
SUM(pv_cd) AS views,
1.0000 * total_gross_order / SUM(pv_cd) AS CR
FROM A
LEFT JOIN traffic_mart_dws__product_exposure v
ON a.itemid = v.itemid
WHERE v.grass_date BETWEEN DATE {{START_date}} AND DATE {{END_date}}
    GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21