CREATE VIEW dbo.YeniUrunStokPerformans
AS
WITH Purchase2025 AS
(
    SELECT 
        STL.STOCKREF,
        INV.DATE_ AS PurchaseDate,
        ISNULL(STL.AMOUNT,0) AS AMOUNT
    FROM LG_011_08_STLINE STL WITH (NOLOCK)
    LEFT JOIN LG_011_08_STFICHE INV WITH (NOLOCK) 
           ON INV.LOGICALREF = STL.STFICHEREF
    WHERE STL.TRCODE   = 1   -- Alis
      AND STL.CANCELLED = 0
      AND STL.LINETYPE  = 0
)
, OldPurchases AS
(
    SELECT STOCKREF
    FROM (
        SELECT STOCKREF FROM LG_011_07_STLINE WHERE TRCODE = 1 AND CANCELLED = 0 AND LINETYPE = 0
        UNION SELECT STOCKREF FROM LG_011_06_STLINE WHERE TRCODE = 1 AND CANCELLED = 0 AND LINETYPE = 0
        UNION SELECT STOCKREF FROM LG_011_05_STLINE WHERE TRCODE = 1 AND CANCELLED = 0 AND LINETYPE = 0
        UNION SELECT STOCKREF FROM LG_011_04_STLINE WHERE TRCODE = 1 AND CANCELLED = 0 AND LINETYPE = 0
        UNION SELECT STOCKREF FROM LG_011_03_STLINE WHERE TRCODE = 1 AND CANCELLED = 0 AND LINETYPE = 0
        UNION SELECT STOCKREF FROM LG_011_02_STLINE WHERE TRCODE = 1 AND CANCELLED = 0 AND LINETYPE = 0
        UNION SELECT STOCKREF FROM LG_011_01_STLINE WHERE TRCODE = 1 AND CANCELLED = 0 AND LINETYPE = 0
    ) T
    GROUP BY STOCKREF
)
, PurchaseAgg AS
(
    SELECT 
        P.STOCKREF,
        MIN(P.PurchaseDate)               AS FirstPurchaseDate2025,
        COUNT(*)                          AS AlimSayisi,
        ISNULL(SUM(P.AMOUNT),0)           AS ToplamAlim
    FROM Purchase2025 P
    WHERE NOT EXISTS (SELECT 1 FROM OldPurchases O WHERE O.STOCKREF = P.STOCKREF)
    GROUP BY P.STOCKREF
)
, SalesAgg AS
(
    SELECT 
        S.STOCKREF,
        ISNULL(SUM(S.AMOUNT),0)           AS SatisAdet,
        ISNULL(COUNT(DISTINCT S.STFICHEREF),0) AS SatisFatura,
        MIN(I.DATE_)                      AS IlkSatis,
        MAX(I.DATE_)                      AS SonSatis
    FROM LG_011_08_STLINE S WITH (NOLOCK)
    LEFT JOIN LG_011_08_STFICHE I WITH (NOLOCK) ON I.LOGICALREF = S.STFICHEREF
    WHERE S.TRCODE   = 8   -- Satis
      AND S.CANCELLED = 0
      AND S.LINETYPE  = 0
    GROUP BY S.STOCKREF
)
, ReturnsAgg AS
(
    SELECT 
        R.STOCKREF,
        ISNULL(SUM(ABS(R.AMOUNT)),0)      AS IadeAdet,
        ISNULL(COUNT(DISTINCT R.STFICHEREF),0) AS IadeFatura,
        MIN(I.DATE_)                      AS IlkIade,
        MAX(I.DATE_)                      AS SonIade
    FROM LG_011_08_STLINE R WITH (NOLOCK)
    LEFT JOIN LG_011_08_STFICHE I WITH (NOLOCK) ON I.LOGICALREF = R.STFICHEREF
    WHERE R.TRCODE   = 3   -- Iade
      AND R.CANCELLED = 0
      AND R.LINETYPE  = 0
    GROUP BY R.STOCKREF
)
, StockAgg AS
(
    SELECT 
        G.STOCKREF,
        ISNULL(SUM(G.ONHAND),0) AS SonStok   -- INVENNO = -1 toplam stok
    FROM LV_011_08_GNTOTST G WITH (NOLOCK)
    WHERE G.INVENNO = -1
    GROUP BY G.STOCKREF
)
SELECT 
    P.STOCKREF,
    P.FirstPurchaseDate2025,
    ISNULL(P.AlimSayisi,0)   AS AlimSayisi,
    ISNULL(P.ToplamAlim,0)   AS ToplamAlim,

    ISNULL(S.SatisAdet,0)    AS SatisAdet,
    ISNULL(S.SatisFatura,0)  AS SatisFatura,
    S.IlkSatis,
    S.SonSatis,

    ISNULL(R.IadeAdet,0)     AS IadeAdet,
    ISNULL(R.IadeFatura,0)   AS IadeFatura,
    R.IlkIade,
    R.SonIade,

    ISNULL(G.SonStok,0)      AS SonStok,

    -- Net satis
    ISNULL(S.SatisAdet,0) - ISNULL(R.IadeAdet,0) AS NetSatis,

    -- Satis orani
    CASE 
        WHEN ISNULL(P.ToplamAlim,0) > 0 
        THEN CAST( (ISNULL(S.SatisAdet,0) - ISNULL(R.IadeAdet,0)) / CAST(P.ToplamAlim AS DECIMAL(18,6)) AS DECIMAL(18,4))
        ELSE 0 
    END AS SatisOrani,

    -- Günlük net satis
    CASE 
        WHEN DATEDIFF(DAY, P.FirstPurchaseDate2025, GETDATE()) > 0 
        THEN CAST( (ISNULL(S.SatisAdet,0) - ISNULL(R.IadeAdet,0)) 
              / CAST(DATEDIFF(DAY, P.FirstPurchaseDate2025, GETDATE()) AS DECIMAL(18,6)) AS DECIMAL(18,6))
        ELSE 0 
    END AS GunlukNetSatis,

    -- Tükenme günü
    CASE 
        WHEN DATEDIFF(DAY, P.FirstPurchaseDate2025, GETDATE()) > 0 
             AND ((ISNULL(S.SatisAdet,0) - ISNULL(R.IadeAdet,0)) 
                  / CAST(DATEDIFF(DAY, P.FirstPurchaseDate2025, GETDATE()) AS DECIMAL(18,6))) > 0
        THEN CAST( ISNULL(G.SonStok,0) / 
              ((ISNULL(S.SatisAdet,0) - ISNULL(R.IadeAdet,0)) 
              / CAST(DATEDIFF(DAY, P.FirstPurchaseDate2025, GETDATE()) AS DECIMAL(18,6))) AS DECIMAL(18,2))
        ELSE NULL
    END AS TukenmeGun
FROM PurchaseAgg P
LEFT JOIN SalesAgg   S ON S.STOCKREF = P.STOCKREF
LEFT JOIN ReturnsAgg R ON R.STOCKREF = P.STOCKREF
LEFT JOIN StockAgg   G ON G.STOCKREF = P.STOCKREF
--ORDER BY P.FirstPurchaseDate2025;