IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SP_STOKMIADBAKIYESAYIM_INSERT]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE SP_STOKMIADBAKIYESAYIM_INSERT
GO

create PROCEDURE SP_STOKMIADBAKIYESAYIM_INSERT
(
    @SUBEID INT,
    @DEPOID INT,
    @DONEMSON DATE,
    @BATCHSIZE INT = 100
)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY

        
        BEGIN TRAN

        
        IF OBJECT_ID('tempdb..#DATA') IS NOT NULL DROP TABLE #DATA

        CREATE TABLE #DATA
        (
            ID INT IDENTITY(1,1) PRIMARY KEY,
            SUBEID INT,
            DEPOID INT,
            STOKID INT,
            SKTARIHI DATE,
            TARIH DATE,
            MIKTAR DECIMAL(18,4)
        )

        -------------------------------------------------
        -- AĞIR SORGU (1 KEZ)
        -------------------------------------------------
-------------------------------------------------
-- AĞIR SORGU (OPTİMİZE EDİLDİ)
-------------------------------------------------

-- 1. VWSTOKISL → TEMP
IF OBJECT_ID('tempdb..#VW') IS NOT NULL DROP TABLE #VW

SELECT 
    SI.SUBEID, SI.DEPOID, SI.STOKID, SI.GCADET, SI.SKTARIHI, SI.TARIH
INTO #VW
FROM VWSTOKISL SI
WHERE 
    SI.SUBEID = @SUBEID
    AND SI.DEPOID = @DEPOID
    AND SI.MIADZORUNLU = 1
    AND SI.TARIH <= @DONEMSON

CREATE INDEX IX_TMP_VW ON #VW (SUBEID, DEPOID, STOKID, TARIH)

-- 2. SON DÖNEM
IF OBJECT_ID('tempdb..#SONDNM') IS NOT NULL DROP TABLE #SONDNM

SELECT SUBEID, DEPOID, MAX(TARIH) SONTARIH
INTO #SONDNM
FROM SAYIMDONEM
WHERE KAPANDI = 1
GROUP BY SUBEID, DEPOID


-- 3. ÖNCEKİ DÖNEM
IF OBJECT_ID('tempdb..#SONDNM_ONCEKI') IS NOT NULL DROP TABLE #SONDNM_ONCEKI

SELECT SUBEID, DEPOID, MAX(TARIH) SONTARIH
INTO #SONDNM_ONCEKI
FROM SAYIMDONEM
WHERE KAPANDI = 1 AND TARIH < @DONEMSON
GROUP BY SUBEID, DEPOID


-- 4. HAREKET
IF OBJECT_ID('tempdb..#HAREKET') IS NOT NULL DROP TABLE #HAREKET

SELECT 
    V.SUBEID, 
    V.DEPOID, 
    V.STOKID, 
    V.SKTARIHI,
    SUM(V.GCADET) MIKTAR
INTO #HAREKET
FROM #VW V
LEFT JOIN #SONDNM S 
    ON V.SUBEID = S.SUBEID AND V.DEPOID = S.DEPOID
WHERE 
    (V.TARIH > S.SONTARIH OR S.SONTARIH IS NULL)
GROUP BY 
    V.SUBEID, V.DEPOID, V.STOKID, V.SKTARIHI


-- 5. SAYIM
IF OBJECT_ID('tempdb..#SAYIM') IS NOT NULL DROP TABLE #SAYIM

SELECT 
    SKTSAYIM.SUBEID, 
    SKTSAYIM.DEPOID, 
    SKTSAYIM.STOKID, 
    SKTSAYIM.SKTARIHI,
    SUM(SKTSAYIM.SAYIM) MIKTAR
INTO #SAYIM
FROM STOKMIADBAKIYESAYIM SKTSAYIM
INNER JOIN #SONDNM_ONCEKI S 
    ON SKTSAYIM.SUBEID = S.SUBEID 
   AND SKTSAYIM.DEPOID = S.DEPOID
INNER JOIN SAYIM2 SAYIM 
    ON SAYIM.SUBEID = SKTSAYIM.SUBEID 
   AND SAYIM.DEPOID = SKTSAYIM.DEPOID 
   AND SAYIM.TARIH = S.SONTARIH
   AND SAYIM.STOKID = SKTSAYIM.STOKID
INNER JOIN STOK 
    ON SKTSAYIM.STOKID = STOK.STOKID
INNER JOIN STOKGRUP 
    ON STOK.GRUP = STOKGRUP.KODU
WHERE 
    SKTSAYIM.SUBEID = @SUBEID
    AND SKTSAYIM.DEPOID = @DEPOID
    AND STOKGRUP.MIADZORUNLU = 1
    AND SKTSAYIM.SAYIM IS NOT NULL
    AND SKTSAYIM.SAYIM <> 0
GROUP BY 
    SKTSAYIM.SUBEID, SKTSAYIM.DEPOID, SKTSAYIM.STOKID, SKTSAYIM.SKTARIHI


-- 6. BİRLEŞTİR
IF OBJECT_ID('tempdb..#FINAL') IS NOT NULL DROP TABLE #FINAL

SELECT *
INTO #FINAL
FROM
(
    SELECT * FROM #HAREKET
    UNION ALL
    SELECT * FROM #SAYIM
) X


-- 7. FINAL AGG
IF OBJECT_ID('tempdb..#AGG') IS NOT NULL DROP TABLE #AGG

SELECT 
    SUBEID, 
    DEPOID, 
    STOKID, 
    SKTARIHI,
    SUM(MIKTAR) MIKTAR
INTO #AGG
FROM #FINAL
GROUP BY 
    SUBEID, DEPOID, STOKID, SKTARIHI


-- 8. SONUÇ → #DATA
INSERT INTO #DATA (SUBEID, DEPOID, STOKID, SKTARIHI, TARIH, MIKTAR)
SELECT 
    A.SUBEID,
    A.DEPOID,
    A.STOKID,
    A.SKTARIHI,
    @DONEMSON,
    A.MIKTAR
FROM #AGG A
INNER JOIN SAYIM2 SYM 
    ON A.DEPOID = SYM.DEPOID 
   AND A.SUBEID = SYM.SUBEID 
   AND SYM.STOKID = A.STOKID 
   AND SYM.TARIH = @DONEMSON
LEFT JOIN STOKMIADBAKIYESAYIM SKTSYM 
    ON A.SUBEID  = SKTSYM.SUBEID 
   AND A.DEPOID  = SKTSYM.DEPOID 
   AND A.STOKID  = SKTSYM.STOKID 
   AND A.SKTARIHI = SKTSYM.SKTARIHI 
   AND SKTSYM.TARIH = @DONEMSON
WHERE SKTSYM.SUBEID IS NULL

        -------------------------------------------------
        -- BATCH INSERT
        -------------------------------------------------
        DECLARE @MINID INT = 1
        DECLARE @MAXID INT

        SELECT @MAXID = MAX(ID) FROM #DATA

        WHILE @MINID <= @MAXID
        BEGIN
            INSERT INTO STOKMIADBAKIYESAYIM
                (SUBEID, DEPOID, STOKID, SKTARIHI, TARIH, MIKTAR)
            SELECT 
                SUBEID, DEPOID, STOKID, SKTARIHI, TARIH, MIKTAR
            FROM #DATA
            WHERE ID BETWEEN @MINID AND (@MINID + @BATCHSIZE - 1)

            SET @MINID = @MINID + @BATCHSIZE
        END

        
        COMMIT TRAN

    END TRY

    BEGIN CATCH

       
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN

     
        DECLARE @HATA NVARCHAR(4000) = ERROR_MESSAGE()
        DECLARE @HATA_NO INT = ERROR_NUMBER()
        DECLARE @HATA_LINE INT = ERROR_LINE()

        RAISERROR (
            'SP_STOK_MIAD_SAYIM_PRO HATA! No:%d Line:%d Mesaj:%s',
            16, 1,
            @HATA_NO, @HATA_LINE, @HATA
        )

        RETURN

    END CATCH

END