SELECT 
CURDATE() AS hari_ini,
CURRENT_TIME() AS jam,
p.id AS product_id,
w.id AS warehouse_id,
t.company_name AS penyewa,
w.name AS gudang_penyimpanan,
CASE 
	WHEN isCreator = 0  THEN 'T2T'
	ELSE 'Inventaris'
END AS tipe_barang,
p.sku_number AS kode_sku,
p.name AS nama_produk,
i.Fulfillment AS fulfilment_iku_masa_pengawasan,
i.stock as iku_tersedia_sekarang,
ISNULL(ikuses.date_expired < DATE(NOW())) AS iku_tersedia_sekarang_masa_simpan,

CEIL(i.Fulfillment+(i.Fulfillment/7)) as fulfilment_iku_masa_berikutnya,

IF(SUM(GREATEST(i.Fulfillment-i.stock,0)<=0), 'N', 'Y') AS perlu_pengiriman_yes_no,

GREATEST(i.Fulfillment-i.stock,0) as rekomendasi_iku_pengiriman_no_buffer,

CEIL(GREATEST((i.Fulfillment+(i.Fulfillment/7) + 1 + 10*1/100) - i.stock,0)) as rekomendasi_iku_pengiriman_with_buffer,
(CASE 
        WHEN i.stock = '0' THEN '$ten_percent'
        WHEN i.stock <= '0' THEN '$seven_percent'
        ELSE '$five_percent'
    END) AS buffer_factor, 
(CASE 
        WHEN DATE_ADD(CURDATE(), INTERVAL +1 DAY) BETWEEN '$lv1_s' AND '$lv1_e' THEN '$s_seven_percentage'
        WHEN DATE_ADD(CURDATE(), INTERVAL +1 DAY) BETWEEN '$lv2_s' AND '$lv2_e' THEN '$s_five_percentage'
        WHEN DATE_ADD(CURDATE(), INTERVAL +1 DAY) BETWEEN '$lv3_s' AND '$lv3_e' THEN '$s_two_percentage'
        WHEN DATE_ADD(CURDATE(), INTERVAL +1 DAY) BETWEEN '$lv4_s' AND '$lv4_e' THEN '$s_ten_percentage'
        ELSE '$five_percent'
    END) AS seasonality_factor,
jubelio_products.jubelio_product_id,
jubelio_locations.jubelio_location_id,
SUM(if(ikuses.status = 'received', 1, 0)) AS ff_received,
SUM(if(ikuses.status = 'stored', 1, 0)) AS ff_stored,
SUM(if(ikuses.status = 'ordered', 1, 0)) AS ff_ordered,
SUM(if(ikuses.status = 'packing', 1, 0)) AS ff_packing


FROM products p
INNER JOIN tenants t ON p.tenant_id = t.id
INNER JOIN ikuses ON ikuses.product_id = p.id
INNER JOIN jubelio_products ON jubelio_products.product_id = p.id
INNER JOIN (
	SELECT warehouse_id, product_id, SUM(stock) AS stock, SUM(quantity) AS Fulfillment
	FROM (
			SELECT COUNT(*) AS stock, 0 AS quantity, warehouse_id, product_id, 'Stock' AS TYPE FROM ikuses
			WHERE STATUS IN ('stored', 'received', 'reserved')
			AND DATE(date_expired) >= DATE(NOW())
			GROUP BY product_id, warehouse_id
		UNION
			SELECT 0 AS stock, SUM(op.quantity) AS quantity, o.warehouse_id, op.product_id, 'Fulfillment' AS TYPE FROM order_products op
			INNER JOIN orders o ON op.order_id = o.order_id
			WHERE DATE(o.created_at) <= DATE(NOW())
			AND  DATE(o.created_at) > DATE(DATE_SUB(NOW(), INTERVAL 7 DAY))
			GROUP BY product_id, warehouse_id
	) datas
	GROUP BY warehouse_id, product_id
	) i ON i.product_id = p.id
INNER JOIN warehouses w ON w.id=i.warehouse_id
INNER JOIN jubelio_locations ON jubelio_locations.warehouse_id = w.id
WHERE t.id = '31e0787f-d1a0-4737-b4f7-770191407d4a'
GROUP BY p.id, gudang_penyimpanan

UNION ALL

SELECT 
CURDATE() AS hari_ini,
CURRENT_TIME() AS jam,
0 AS product_id,
0 AS warehouse_id,
t.company_name AS penyewa,
w.name AS gudang_penyimpanan,
CASE 
	WHEN sku_ff LIKE '%TOWS%' THEN REPLACE(LEFT(sku_ff, 4), '', 'TOWS ') 
END AS tipe_barang,
tp.sku_ff AS sku_number,
tp.name AS nama_produk,

COUNT(otp.quantity) AS fulfilment_iku_masa_pengawasan,
ts.stock AS iku_tersedia_sekarang,
ISNULL(0) AS iku_tersedia_sekarang_masa_simpan,
CEIL(COUNT(otp.quantity)+(COUNT(otp.quantity)/7)) as fulfilment_iku_masa_berikutnya,
IF(SUM(GREATEST(otp.quantity-ts.stock,0)<=0), 'N', 'Y') AS perlu_pengiriman_yes_no,
GREATEST(otp.quantity-ts.stock,0) as rekomendasi_iku_pengiriman_no_buffer,
CEIL(GREATEST((COUNT(otp.quantity)+(COUNT(otp.quantity)/7) + 1 + 12.5*1/100) - ts.stock,0)) as rekomendasi_iku_pengiriman_with_buffer,
(CASE 
        WHEN ts.stock = '0' THEN '$ten_percent'
        WHEN ts.stock <= '0' THEN '$seven_percent'
        ELSE '$five_percent'
    END) AS buffer_factor, 
(CASE 
        WHEN DATE_ADD(CURDATE(), INTERVAL +1 DAY)  BETWEEN '$lv1_s' AND '$lv1_e' THEN '$s_seven_percentage'
        WHEN DATE_ADD(CURDATE(), INTERVAL +1 DAY)  BETWEEN '$lv2_s' AND '$lv2_e' THEN '$s_five_percentage'
        WHEN DATE_ADD(CURDATE(), INTERVAL +1 DAY)  BETWEEN '$lv3_s' AND '$lv3_e' THEN '$s_two_percentage'
        WHEN DATE_ADD(CURDATE(), INTERVAL +1 DAY)  BETWEEN '$lv4_s' AND '$lv4_e' THEN '$s_ten_percentage'
        ELSE '$five_percent'
    END) AS seasonality_factor,
0 AS jb_id,
0 AS jb_id2,
0 AS ff_received,
0 AS ff_stored,
0 AS ff_ordered,
0 AS ff_packing

FROM tows_products tp 
INNER JOIN tenants t ON tp.tenant_id = t.id
INNER JOIN tows_warehouses tw ON tw.tenant_id = t.id
INNER JOIN warehouses w ON w.id = tw.warehouse_id 
INNER JOIN order_tows_products otp ON otp.product_id = tp.id
INNER JOIN tows_stocks ts ON ts.tows_product_id = tp.id

WHERE t.id = '31e0787f-d1a0-4737-b4f7-770191407d4a'


AND DATE(otp.created_at) <= DATE(NOW())
AND  DATE(otp.created_at) > DATE(DATE_SUB(NOW(), INTERVAL 7 DAY))


GROUP BY t.id, gudang_penyimpanan, tp.id