CREATE   PROCEDURE dbo.usp_GetItemRefs_ByCodes
    @Codes NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    IF OBJECT_ID('tempdb..#Codes') IS NOT NULL DROP TABLE #Codes;
    CREATE TABLE #Codes (Code NVARCHAR(255) NOT NULL PRIMARY KEY);

    INSERT INTO #Codes(Code)
    SELECT DISTINCT LTRIM(RTRIM(value))
    FROM STRING_SPLIT(@Codes, ',')
    WHERE value IS NOT NULL AND LTRIM(RTRIM(value)) <> '';

    IF OBJECT_ID('tempdb..#UDB') IS NOT NULL DROP TABLE #UDB;
    CREATE TABLE #UDB (
        InputCode  NVARCHAR(255) NOT NULL PRIMARY KEY,
        LOGICALREF INT NULL
    );

    INSERT INTO #UDB(InputCode, LOGICALREF)
    SELECT c.Code, u.LOGICALREF
    FROM #Codes c
    LEFT JOIN [UNIVERSALDB].[dbo].[LG_011_ITEMS] u
           ON u.CODE COLLATE Turkish_CI_AS = c.Code COLLATE Turkish_CI_AS;

    IF OBJECT_ID('tempdb..#Matched') IS NOT NULL DROP TABLE #Matched;
    CREATE TABLE #Matched (
        InputCode  NVARCHAR(255) NOT NULL,
        IHRCode    NVARCHAR(255) NOT NULL,
        REF        NVARCHAR(255) NULL,
        LOGICALREF INT NULL
    );

    -- Koda göre eslesenler
    INSERT INTO #Matched(InputCode, IHRCode, REF, LOGICALREF)
    SELECT c.Code,
           i.CODE,
           CAST(i.REF AS NVARCHAR(255)),
           i.LOGICALREF
    FROM #Codes c
    JOIN [IHR].[dbo].[ITEMS] i
      ON i.CODE COLLATE Turkish_CI_AS = c.Code COLLATE Turkish_CI_AS
    WHERE i.CODE NOT LIKE '%+%';

    -- LOGICALREF'e göre eslesenler
    INSERT INTO #Matched(InputCode, IHRCode, REF, LOGICALREF)
    SELECT u.InputCode,
           i.CODE,
           CAST(i.REF AS NVARCHAR(255)),
           i.LOGICALREF
    FROM #UDB u
    JOIN [IHR].[dbo].[ITEMS] i
      ON u.LOGICALREF IS NOT NULL
     AND i.LOGICALREF = u.LOGICALREF
    WHERE i.CODE NOT LIKE '%+%';

    -- 1. sonuç kümesi
    SELECT DISTINCT
           m.InputCode AS Code,
           m.REF,
           m.IHRCode
    FROM #Matched m
    ORDER BY m.InputCode, m.IHRCode;

    -- 2. sonuç kümesi
    ;WITH g AS (
        SELECT REF
        FROM #Matched
        GROUP BY REF
        HAVING COUNT(DISTINCT IHRCode) > 1
    )
    SELECT
        g.REF,
        (SELECT COUNT(DISTINCT IHRCode) FROM #Matched m WHERE m.REF = g.REF) AS ItemCount,
        STUFF((
            SELECT ', ' + m2.IHRCode
            FROM (SELECT DISTINCT IHRCode FROM #Matched WHERE REF = g.REF) m2
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'') AS IHR_Codes,
        STUFF((
            SELECT ', ' + m3.InputCode
            FROM (SELECT DISTINCT InputCode FROM #Matched WHERE REF = g.REF) m3
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'') AS InputCodes
    FROM g
    ORDER BY g.REF;

    -- 3. sonuç kümesi (opsiyonel)
    SELECT c.Code AS UnmatchedCode
    FROM #Codes c
    WHERE NOT EXISTS (SELECT 1 FROM #Matched m WHERE m.InputCode = c.Code);
END