SET @row_number = 0; 
SET @days_value = 7; 
###SET @buffer = "5%"; ###
SET @season_factor = "2.5%";

SELECT 
t.company_name,
w.id AS warehouse_id,
t.id AS tenant_id,
w.name AS warehouse_name,
###(SELECT REPLACE(LEFT(sku_ff, 2), "FF", "Inventaris ") as "TIPE BARANG" from products UNION SELECT REPLACE(LEFT(sku_ff, 2), "TO", "TOWS ") as "TIPE BARANG" from tows_products),

####REPLACE(LEFT(sku_ff, 2), "FF", "Inventaris ") as "TIPE BARANG",###
CASE WHEN sku_number LIKE "%RZ%" THEN REPLACE(LEFT(sku_number, 2), "RZ", "TOWS ") 
WHEN sku_number LIKE "%C%" THEN REPLACE(LEFT(sku_number, 1), "C", "Inventaris ")
WHEN sku_number LIKE "%F%" THEN REPLACE(LEFT(sku_number, 1), "F", "Inventaris ")
WHEN sku_number LIKE "%D%" THEN REPLACE(LEFT(sku_number, 1), "D", "Inventaris ")
ELSE REPLACE(LEFT(sku_number,1 ), "G", "Inventaris ") 
END AS tipe_barang,

###CASE WHEN sku_ff = "FF" THEN "Inventaris" WHEN sku_ff = "TOWS" END AS "TIPE BARANG",###



p.sku_number,
p.name AS nama_produk,
p.id AS product_id,
i.Fulfillment AS fulfilment_iku_masa_pengawasan,
i.stock as iku_tersedia_sekarang,
####MASA SIMPAN HERE####
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,

###IKU TANPA BUFFER PERCENT###

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


        
###END IKU TANPA BUFFER PERCENT###

CEIL(GREATEST((i.Fulfillment+(i.Fulfillment/7) + 1 + (CASE 
        WHEN i.stock = '0' THEN '0.1'
        WHEN i.stock <= '0' THEN '0.075'
        ELSE '0.05'
    END+'0.025')) - i.stock,0)) as rekomendasi_iku_pengiriman_with_buffer

FROM products p
JOIN (SELECT @no:=0) r
JOIN (SELECT @days_value:=7) d
###JOIN (SELECT @buffer:="5%") b ###
JOIN (SELECT @season_factor:="2.5%") s
INNER JOIN tenants t ON p.tenant_id = t.id
INNER JOIN ikuses ON ikuses.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")
			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
GROUP BY p.id, w.name