CREATE OR ALTER PROCEDURE sp_InvoiceItemMapSync
(
    @InvoiceId INT,
    @VergiNo   NVARCHAR(50),
    @Tab1      NVARCHAR(100),   -- InvoiceLineType veya SendBoxInvoiceLineType
    @Tab2      NVARCHAR(100)    -- TaxSubtotal veya SendBoxTaxSubtotal
)
AS
BEGIN
--sp_InvoiceItemMapSync @InvoiceId = 2209, @VergiNo = '2930506126',@Tab1='InvoiceLineType',@Tab2='TaxSubtotal'
    SET NOCOUNT ON;

    --------------------------------------------------------------------
    -- 1) B key'li map kayıtlarını temizle
    --------------------------------------------------------------------
    DELETE FROM InvoiceItemMap 
    WHERE SenderItemKey = 'B';


    --------------------------------------------------------------------
    -- 2) Aynı OurCode + SenderIdentifier için fazla kayıtları sil
    --------------------------------------------------------------------
    ;WITH D AS 
    (
        SELECT 
            Id,
            OurCode,
            SenderIndentifier,
            ROW_NUMBER() OVER (PARTITION BY OurCode, SenderIndentifier ORDER BY Id DESC) AS RN
        FROM InvoiceItemMap
        WHERE SenderIndentifier = @VergiNo
    )
    DELETE FROM D WHERE RN > 1;


    --------------------------------------------------------------------
    -- 3) InvoiceItem sorgusunu birebir çalıştırıp geçici tabloya alıyoruz
    --------------------------------------------------------------------
    CREATE TABLE #InvoiceItems (
        Id INT,
        OurCode NVARCHAR(200),
        BuyersItemIdentification NVARCHAR(MAX),
        ManufacturersItemIdentification NVARCHAR(MAX),
        Name NVARCHAR(MAX),
        Description NVARCHAR(MAX),
        SerrialId NVARCHAR(MAX),
        SellersItemIdentification NVARCHAR(MAX),
        MId INT NULL
    );

    DECLARE @SQL NVARCHAR(MAX) = N'
        INSERT INTO #InvoiceItems
        SELECT  
            I.Id,
            m.OurCode,
            MAX(I.BuyersItemIdentification),
            MAX(I.ManufacturersItemIdentification),
            MAX(I.Name),
            MAX(I.Description),
            MAX(I.SerrialId),
            MAX(I.SellersItemIdentification),
            m.Id AS MId
        FROM ' + @Tab1 + ' I
        LEFT JOIN InvoiceItemMap m 
            ON m.SenderIndentifier = @VergiNo
            AND m.SenderItemValue =
                CASE m.SenderItemKey
                    WHEN ''S'' THEN I.SellersItemIdentification
                    WHEN ''M'' THEN I.ManufacturersItemIdentification
                    WHEN ''D'' THEN I.Description
                    WHEN ''N'' THEN I.Name
                END
        WHERE I.InvoiceId = @InvoiceId
        GROUP BY 
            I.Id,
            m.OurCode,
            m.Id;
    ';

    EXEC sp_executesql @SQL,
        N'@InvoiceId INT, @VergiNo NVARCHAR(50)',
        @InvoiceId, @VergiNo;


    --------------------------------------------------------------------
    -- 4) MId NULL olanlar → hiç eşleşmemiş ürünler
    --    Bunlara yeni map kaydı ekle
    --------------------------------------------------------------------
	INSERT INTO InvoiceItemMap (SenderIndentifier, SenderItemKey, SenderItemValue, SenderItemName)
	SELECT
    @VergiNo,
    CASE 
        WHEN SellersItemIdentification IS NOT NULL AND LTRIM(RTRIM(SellersItemIdentification)) <> '' THEN 'S'
        WHEN ManufacturersItemIdentification IS NOT NULL AND LTRIM(RTRIM(ManufacturersItemIdentification)) <> '' THEN 'M'
        WHEN Description IS NOT NULL AND LTRIM(RTRIM(Description)) <> '' THEN 'D'
        ELSE 'N'
    END AS SenderItemKey,

    -- SENDER ITEM VALUE
    CASE 
        WHEN SellersItemIdentification IS NOT NULL AND LTRIM(RTRIM(SellersItemIdentification)) <> '' 
            THEN SellersItemIdentification COLLATE Turkish_CI_AS
        WHEN ManufacturersItemIdentification IS NOT NULL AND LTRIM(RTRIM(ManufacturersItemIdentification)) <> '' 
            THEN ManufacturersItemIdentification COLLATE Turkish_CI_AS
        WHEN Description IS NOT NULL AND LTRIM(RTRIM(Description)) <> '' 
            THEN Description COLLATE Turkish_CI_AS
        ELSE Name COLLATE Turkish_CI_AS
    END AS SenderItemValue,

    -- SENDER ITEM NAME
    CASE
        WHEN LEN(Name) < LEN(Description) THEN Description COLLATE Turkish_CI_AS
        ELSE Name COLLATE Turkish_CI_AS
    END AS SenderItemName

FROM #InvoiceItems T
WHERE T.MId IS NULL
  AND NOT EXISTS (
        SELECT 1
        FROM InvoiceItemMap M
        WHERE 
            M.SenderIndentifier = @VergiNo COLLATE Turkish_CI_AS

            AND M.SenderItemKey = 
                CASE 
                    WHEN T.SellersItemIdentification IS NOT NULL AND LTRIM(RTRIM(T.SellersItemIdentification)) <> '' THEN 'S'
                    WHEN T.ManufacturersItemIdentification IS NOT NULL AND LTRIM(RTRIM(T.ManufacturersItemIdentification)) <> '' THEN 'M'
                    WHEN T.Description IS NOT NULL AND LTRIM(RTRIM(T.Description)) <> '' THEN 'D'
                    ELSE 'N'
                END

            AND M.SenderItemValue COLLATE Turkish_CI_AS =
                CASE 
                    WHEN T.SellersItemIdentification IS NOT NULL AND LTRIM(RTRIM(T.SellersItemIdentification)) <> '' 
                        THEN T.SellersItemIdentification COLLATE Turkish_CI_AS
                    WHEN T.ManufacturersItemIdentification IS NOT NULL AND LTRIM(RTRIM(T.ManufacturersItemIdentification)) <> '' 
                        THEN T.ManufacturersItemIdentification COLLATE Turkish_CI_AS
                    WHEN T.Description IS NOT NULL AND LTRIM(RTRIM(T.Description)) <> '' 
                        THEN T.Description COLLATE Turkish_CI_AS
                    ELSE T.Name COLLATE Turkish_CI_AS
                END
      );


    --------------------------------------------------------------------
    -- 5) MId DOLU → isim güncellemesi yapılması gereken kayıtlar
    --------------------------------------------------------------------
    UPDATE M
    SET M.SenderItemName = T.Name + ' - ' + T.Description
    FROM InvoiceItemMap M
    INNER JOIN #InvoiceItems T ON M.Id = T.MId
    WHERE T.MId IS NOT NULL
      AND T.Description IS NOT NULL
      AND T.Description <> ''
      AND T.Name = T.OurCode;

    DROP TABLE #InvoiceItems;
END;