CREATE   VIEW [dbo].[WEBAPI_SEN160130M] AS

WITH SlideRN AS (
    SELECT 
        S.LOGICALREF,
        ROW_NUMBER() OVER (PARTITION BY S.LOGICALREF ORDER BY S.[REF]) AS rn,
        'https://slide.universaloto.com/slides/' + LTRIM(RTRIM(S.IMGNAME)) AS SlideUrl
    FROM dbo.SlaytResimleri AS S
    WHERE S.LOGICALREF IS NOT NULL
      AND S.IMGNAME IS NOT NULL
      AND LTRIM(RTRIM(S.IMGNAME)) <> ''
),
SlidePivot AS (
    SELECT 
        LOGICALREF,
        MAX(CASE WHEN rn = 1 THEN SlideUrl END) AS Resim2,
        MAX(CASE WHEN rn = 2 THEN SlideUrl END) AS Resim3,
        MAX(CASE WHEN rn = 3 THEN SlideUrl END) AS Resim4,
        MAX(CASE WHEN rn = 4 THEN SlideUrl END) AS Resim5,
        MAX(CASE WHEN rn = 5 THEN SlideUrl END) AS Resim6
    FROM SlideRN
    WHERE rn <= 5
    GROUP BY LOGICALREF
)

SELECT
    ITM.LOGICALREF AS Ref,

    ---- *** YENI CASE WHEN SICIL KODU *** ----
    CASE 
        WHEN ITM.CODE LIKE 'UN%' AND LEN(ITM.CODE) = 9 THEN 
            CASE 
                WHEN ITM.PRODUCERCODE NOT LIKE '%/%' THEN ITM.PRODUCERCODE
                ELSE RIGHT(ITM.PRODUCERCODE, CHARINDEX('/', REVERSE(ITM.PRODUCERCODE)) - 1)
            END
        ELSE ITM.CODE
    END AS SicilKodu, 
    ISNULL(ITM.PRODUCERCODE, '') AS Producercode,
    --------------------------------------------

    LTRIM(RTRIM(REPLACE(dbo.RepOzel(dbo.InitCap({ fn CONCAT({ fn CONCAT({ fn CONCAT(ITM.SPECODE2, ' ') }, dbo.RepLhRh(ITM.NAME)) }, dbo.RepLhRh(ITM.NAME3)) })),
    '(' + dbo.RepLhRh(ITM.NAME3) + ')', ''))) AS SicilAdi,

    ITM.SPECODE4 AS Marka,
    ITM.STGRPCODE AS UrunTipi,
    '' AS KampanyaFiyati,
    CASE WHEN (GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED) > 0 THEN 'VAR' ELSE 'YOK' END AS StokDurumu,
    TLF.TL * 0.7 * 1.2 AS ListeFiyati,
    'TL' AS Kur,
    { fn CONCAT({ fn CONCAT('https://universaloto.com/foto/photos/', dbo.formatNumber(ITM.LOGICALREF)) }, '.jpg') } AS Resim,

    SP.Resim2, SP.Resim3, SP.Resim4, SP.Resim5, SP.Resim6,
    
    ITM.SPECODE2 AS Arac,
    CASE WHEN (GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED) > 10 THEN 10 ELSE ISNULL((GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED), 0) END AS StokAdeti,

    ISNULL(MAM.KAT1, '') AS Kategori1, ISNULL(MAM.KAT2, '') AS Kategori2, ISNULL(MAM.KAT3, '') AS Kategori3, ISNULL(MAM.KAT4, '') AS Kategori4,
    ISNULL(MAM.KAT5, '') AS Kategori5, ISNULL(MAM.KAT6, '') AS Kategori6, ISNULL(MAM.KAT7, '') AS Kategori7, ISNULL(MAM.KAT8, '') AS Kategori8,
    ISNULL(MAM.KAT9, '') AS Kategori9, ISNULL(MAM.KAT10, '') AS Kategori10, ISNULL(MAM.KAT11, '') AS Kategori11, ISNULL(MAM.KAT12, '') AS Kategori12,
    ISNULL(MAM.KAT13, '') AS Kategori13, ISNULL(MAM.KAT14, '') AS Kategori14,

    ITM.CAPIBLOCK_CREADEDDATE AS Eklenme,
    ITM.CAPIBLOCK_MODIFIEDDATE AS Guncellenme,
    ITM.GTIPCODE AS Kategori

FROM dbo.LG_011_ITEMS AS ITM
LEFT JOIN dbo.LV_011_08_GNTOTST AS GNT ON GNT.STOCKREF = ITM.LOGICALREF AND GNT.INVENNO = -1
LEFT JOIN dbo.UNI_TL_FIYAT AS TLF ON TLF.CARDREF = ITM.LOGICALREF
LEFT JOIN dbo.MALZEME_ARAC_MODELLERI AS MAM ON MAM.LOGREF = ITM.LOGICALREF
LEFT JOIN SlidePivot AS SP ON SP.LOGICALREF = ITM.LOGICALREF
WHERE 
    ITM.ACTIVE = 0 
    AND TLF.TL IS NOT NULL
    AND ITM.SPECODE2 IN ('MITSUBISHI','HONDA','ISUZU','SUBARU','SUZUKI','KIA','NISSAN','TOYOTA','MAZDA','DAIHATSU','HYUNDAI','DAEWOO','CHERY','TESLA','SSANGYONG')