-- SCRIPT OPTIMASI SUBVIEW GABUNGAN (RUN ONCE)
-- Gunakan EXEC untuk menjalankan beberapa ALTER VIEW dalam satu batch

EXEC('ALTER VIEW [dbo].[Qty_Beli] AS SELECT Kode, ISNULL(SUM(Beli), 0) AS Beli FROM dbo.Lokasi_Qty_Beli GROUP BY Kode');

EXEC('ALTER VIEW [dbo].[Qty_Jual] AS SELECT Kode, ISNULL(SUM(Jual), 0) AS Jual FROM dbo.Lokasi_Qty_Jual GROUP BY Kode');

EXEC('ALTER VIEW [dbo].[Qty_PenggunaanStock] AS SELECT Kode, ISNULL(SUM(Penggunaan), 0) AS Penggunaan FROM dbo.Lokasi_Qty_PenggunaanStock GROUP BY Kode');

EXEC('ALTER VIEW [dbo].[Qty_ReturBeli] AS SELECT Kode, ISNULL(SUM(Retur_Beli), 0) AS Retur_Beli FROM dbo.Lokasi_Qty_ReturBeli GROUP BY Kode');

EXEC('ALTER VIEW [dbo].[Qty_ReturJual] AS SELECT Kode, ISNULL(SUM(Retur_Jual), 0) AS Retur_Jual FROM dbo.Lokasi_Qty_ReturJual GROUP BY Kode');

EXEC('ALTER VIEW [dbo].[Qty_AturStock] AS SELECT Kode, ISNULL(SUM(Penyesuaian), 0) AS Penyesuaian FROM dbo.Lokasi_Qty_AturStock_InPeriod GROUP BY Kode');

EXEC('ALTER VIEW [dbo].[Qty_PecahStock] AS SELECT Kode, ISNULL(SUM(Pecah), 0) AS Pecah FROM dbo.Lokasi_Qty_PecahStock GROUP BY Kode');

EXEC('ALTER VIEW [dbo].[Qty_Stock_Awal] AS SELECT Kode, ISNULL(SUM(Stock_Awal), 0) AS Stock_A FROM dbo.Lokasi_Qty_StockAwal GROUP BY Kode');

-- Script untuk View Utama [Stock] menggunakan OUTER APPLY agar performa jutaan baris tetap ringan
EXEC('
ALTER VIEW [dbo].[Stock] AS 
SELECT 
    B.Kode, B.Jenis, B.Nama, B.Satuan, B.HPP,
    ISNULL(QA.Stock_A, 0) AS Awal,
    ISNULL(QB.Beli, 0) AS Beli,
    ISNULL(QJ.Jual, 0) AS Jual,
    ISNULL(QP.Penggunaan, 0) AS Penggunaan,
    ISNULL(QRB.Retur_Beli, 0) AS Retur_Beli,
    ISNULL(QRJ.Retur_Jual, 0) AS Retur_Jual,
    ISNULL(QT.Penyesuaian, 0) AS Penyesuaian,
    ISNULL(QK.Pecah, 0) AS Konversi,
    (ISNULL(QA.Stock_A, 0) + ISNULL(QB.Beli, 0) - ISNULL(QJ.Jual, 0) - ISNULL(QP.Penggunaan, 0) - ISNULL(QRB.Retur_Beli, 0) + ISNULL(QRJ.Retur_Jual, 0) + ISNULL(QT.Penyesuaian, 0) + ISNULL(QK.Pecah, 0)) AS Stock,
    B.Consignor, B.Stocked, B.Status, B.Stock_Minimum
FROM dbo.Barang B
OUTER APPLY (SELECT Stock_A FROM dbo.Qty_Stock_Awal WHERE Kode = B.Kode) QA
OUTER APPLY (SELECT Beli FROM dbo.Qty_Beli WHERE Kode = B.Kode) QB
OUTER APPLY (SELECT Jual FROM dbo.Qty_Jual WHERE Kode = B.Kode) QJ
OUTER APPLY (SELECT Penggunaan FROM dbo.Qty_PenggunaanStock WHERE Kode = B.Kode) QP
OUTER APPLY (SELECT Retur_Beli FROM dbo.Qty_ReturBeli WHERE Kode = B.Kode) QRB
OUTER APPLY (SELECT Retur_Jual FROM dbo.Qty_ReturJual WHERE Kode = B.Kode) QRJ
OUTER APPLY (SELECT Penyesuaian FROM dbo.Qty_AturStock WHERE Kode = B.Kode) QT
OUTER APPLY (SELECT Pecah FROM dbo.Qty_PecahStock WHERE Kode = B.Kode) QK
WHERE (B.Stocked = 1)
');