CREATE VIEW [dbo].[WEBAPI_BYZ010233A]
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,
    ITM.CODE AS SicilKodu,
    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,
    ISNULL(ITM.PRODUCERCODE, '') AS Producercode,
    ITM.GTIPCODE AS Kategori,
    -- Yeni alanlar:
    ISNULL(XT.MODEL_BILGI, '')      AS ModelBilgi,
    ISNULL(XT.ETIKET_ACIKLAMA, '')  AS EtiketAciklama
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
LEFT JOIN dbo.LG_XT1001_011 AS XT
       ON XT.PARLOGREF = ITM.LOGICALREF
WHERE ITM.ACTIVE = 0
  AND TLF.TL IS NOT NULL;