CREATE PROCEDURE [dbo].[sp_ArgeVeB2BArama]
    @arananKod VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    -----------------------------------------------------------------------
    -- 1) @arananKod'u '/' ile SIRALI sekilde parçaliyoruz (OPENJSON ile)
    --    Örn: '22204-22010/22204-0C020/0L010' -> 3 alt kod (sirali)
    -----------------------------------------------------------------------
    DECLARE @json nvarchar(max);
SET @json = N'["'
          + REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(@arananKod, '"', '\"'), 
                    '/', '","'),       -- '/' ayirici
                CHAR(13), '","'),      -- CR
            CHAR(10), '","')           -- LF
          + N'"]';

    ;WITH SplittedCodes AS
    (
        SELECT 
            ROW_NUMBER() OVER (ORDER BY [key]) AS KodSira,
            LTRIM(RTRIM([value])) AS Kod
        FROM OPENJSON(@json)
        WHERE LTRIM(RTRIM([value])) <> ''
    )
    -----------------------------------------------------------------------
    -- 2) Bir önceki kodu bulmak için LAG (kisa kodlar için prefix türetecegiz)
    -----------------------------------------------------------------------
    ,CodesWithPrev AS
    (
        SELECT
            KodSira,
            Kod,
            LAG(Kod, 1, NULL) OVER (ORDER BY KodSira) AS PrevKod
        FROM SplittedCodes
    )
    -----------------------------------------------------------------------
    -- 3) Kisa kod düzeltmesi (<=5 karakter):
    --    Önceki koddaki '-' öncesini al + '-' + kisaKod
    --    Ör: Prev: '22204-0N010',  Kisa: '0L010' ? '22204-0L010'
    -----------------------------------------------------------------------
    ,FinalCodes AS
    (
        SELECT
            KodSira,
            CASE 
                WHEN LEN(C.Kod) <= 5 
                     AND C.PrevKod IS NOT NULL
                     AND CHARINDEX('-', C.PrevKod) > 0
                THEN 
                    LEFT(C.PrevKod, CHARINDEX('-', C.PrevKod) - 1) 
                    + '-' 
                    + C.Kod
                ELSE 
                    C.Kod
            END AS KodFinal
        FROM CodesWithPrev C
    )
    -----------------------------------------------------------------------
    -- 4) ARGE / KARA LISTE / B2B / YOLDA kontrolleri
    --    ARGE ve KARA LISTE tarafinda '-' ve '/' farklarini tolere ediyoruz.
    --    ST2.OEM ve ST3.OEM içindeki tokenlari da ('/’ ile ayrilmis) tek tek
    --    kontrol ediyoruz (OPENJSON ile).
    -----------------------------------------------------------------------
    ,AramaSonuclari AS
    (
        SELECT
            FC.KodSira,
            FC.KodFinal,

            /* ARGE kontrolü (TYPE_=2) – '-' ve '/' farklarini tolere et + token bazli kontrol */
            ARGE = CASE 
                     WHEN ST2.OEM IS NOT NULL THEN 'VAR' 
                     ELSE 'YOK' 
                   END,

            /* B2B (B2B tablosunda var mi?) */
            B2B = CASE 
                    WHEN BPL.Ref IS NOT NULL THEN 'VAR' 
                    ELSE 'YOK' 
                  END,

            /* B2B hangi sütunda bulundu? (CODE mi PRODUCERCODE mu?)  */
            B2B_KOLONU = CASE
                            WHEN BPL.Ref IS NOT NULL
                                 AND 
                                 (
                                    BPL.SicilKodu LIKE '%'+FC.KodFinal+'%'
                                    OR REPLACE(BPL.SicilKodu, '-', '') LIKE '%'+REPLACE(FC.KodFinal, '-', '')+'%'
                                 )
                            THEN 'CODE'

                            WHEN BPL.Ref IS NOT NULL
                                 AND
                                 (
                                    BPL.ProducerCode LIKE '%'+FC.KodFinal+'%'
                                    OR REPLACE(BPL.ProducerCode, '-', '') LIKE '%'+REPLACE(FC.KodFinal, '-', '')+'%'
                                 )
                            THEN 'PRODUCERCODE'

                            ELSE 'BULUNAMADI'
                         END,
            
            /* Eslesme varsa B2B tablosundaki CODE (SicilKodu) degerini ESDEGER olarak set et */
            ESDEGER = CASE WHEN BPL.Ref IS NOT NULL 
                           THEN BPL.SicilKodu 
                           ELSE NULL 
                      END,
            MALZEME = CASE WHEN BPL.Ref IS NOT NULL 
                           THEN BPL.Aciklama 
                           ELSE NULL 
                      END,

            /* KARA LISTE kontrolü (TYPE_=3) – '-' ve '/' farklarini tolere et + token bazli */
            KARA_LISTE = CASE 
                            WHEN ST3.OEM IS NOT NULL THEN 'VAR'
                            ELSE 'YOK'
                         END,

            /* STOK_RAPOR_YOLDA kontrolü – '-' ve '/' farklarini tolere et */
            YOLDA = CASE
                        WHEN SRW.[OEM1] IS NOT NULL THEN 'VAR'
                        ELSE 'YOK'
                    END

        FROM FinalCodes AS FC

        /* ================= ARGE kontrolü ================= */
        LEFT JOIN IHR.dbo.SablonTablosu AS ST2
               ON ST2.TYPE_ = 2
              AND ST2.DEL   = 0
              AND (
                   -- ham LIKE
                   ST2.OEM LIKE '%'+FC.KodFinal+'%'

                   -- '-' farkini yok say
                   OR REPLACE(ST2.OEM, '-', '') 
                        LIKE '%'+REPLACE(FC.KodFinal, '-', '')+'%'

                   -- hem '-' hem '/' farkini yok say
                   OR REPLACE(REPLACE(ST2.OEM, '-', ''), '/', '') 
                        LIKE '%'+REPLACE(REPLACE(FC.KodFinal, '-', ''), '/', '')+'%'

                   -- ST2.OEM içindeki token'lari ('/') ayirarak tek tek karsilastir
                   OR EXISTS (
                        SELECT 1
                        FROM OPENJSON( N'["' + REPLACE(REPLACE(ST2.OEM, '"','\"'), '/', '","') + N'"]' ) AS tok
                        WHERE tok.[value] LIKE '%'+FC.KodFinal+'%'
                           OR REPLACE(tok.[value], '-', '') 
                                LIKE '%'+REPLACE(FC.KodFinal, '-', '')+'%'
                   )
              )

        /* ================ KARA LISTE kontrolü ================ */
        LEFT JOIN IHR.dbo.SablonTablosu AS ST3
               ON ST3.TYPE_ = 3
              AND ST3.DEL   = 0
              AND (
                   ST3.OEM LIKE '%'+FC.KodFinal+'%'
                   OR REPLACE(ST3.OEM, '-', '') 
                        LIKE '%'+REPLACE(FC.KodFinal, '-', '')+'%'
                   OR REPLACE(REPLACE(ST3.OEM, '-', ''), '/', '') 
                        LIKE '%'+REPLACE(REPLACE(FC.KodFinal, '-', ''), '/', '')+'%'
                   OR EXISTS (
                        SELECT 1
                        FROM OPENJSON( N'["' + REPLACE(REPLACE(ST3.OEM, '"','\"'), '/', '","') + N'"]' ) AS tok
                        WHERE tok.[value] LIKE '%'+FC.KodFinal+'%'
                           OR REPLACE(tok.[value], '-', '') 
                                LIKE '%'+REPLACE(FC.KodFinal, '-', '')+'%'
                   )
              )

        /* =================== B2B kontrolü =================== */
        LEFT JOIN UNIVERSALDB.dbo.B2B_PRODUCT_LISTS AS BPL
               ON  BPL.SicilKodu    LIKE '%'+FC.KodFinal+'%'
                OR BPL.ProducerCode LIKE '%'+FC.KodFinal+'%'
                OR REPLACE(BPL.SicilKodu,    '-', '') LIKE '%'+REPLACE(FC.KodFinal, '-', '')+'%'
                OR REPLACE(BPL.ProducerCode, '-', '') LIKE '%'+REPLACE(FC.KodFinal, '-', '')+'%'

        /* ================== YOLDA kontrolü ================== */
        LEFT JOIN UNIVERSALDB.dbo.STOK_RAPOR_YOLDA AS SRW
               ON  SRW.[OEM1] LIKE '%'+FC.KodFinal+'%'
                OR REPLACE(SRW.[OEM1], '-', '') LIKE '%'+REPLACE(FC.KodFinal, '-', '')+'%'
                OR REPLACE(REPLACE(SRW.[OEM1], '-', ''), '/', '') 
                     LIKE '%'+REPLACE(REPLACE(FC.KodFinal, '-', ''), '/', '')+'%'
    )
    -----------------------------------------------------------------------
    -- 5) Öncelik sirasi:
    --    1) ARGE=VAR ve B2B=VAR
    --    2) ARGE=VAR veya B2B=VAR (ama ikisi birden degil)
    --    3) ARGE=YOK ve B2B=YOK
    -----------------------------------------------------------------------
    ,Oncelikli AS
    (
        SELECT 
            KodSira,
            KodFinal,
            ARGE,
            B2B,
            B2B_KOLONU,
            ESDEGER,
            MALZEME,
            KARA_LISTE,
            YOLDA,
            Priority = CASE 
                         WHEN ARGE='VAR' AND B2B='VAR' THEN 1
                         WHEN ARGE='VAR' OR  B2B='VAR' THEN 2
                         ELSE 3
                       END
        FROM AramaSonuclari
    )
    -----------------------------------------------------------------------
    -- 6) En yüksek öncelikli (Priority ASC) ve esitlikte en erken KodSira
    -----------------------------------------------------------------------
    SELECT TOP 1
         @arananKod AS ARANAN
        ,ARGE
        ,B2B
        ,B2B_KOLONU
        ,ESDEGER
        ,MALZEME
        ,KARA_LISTE
        ,YOLDA
    FROM Oncelikli
    ORDER BY Priority, KodSira;
END