/*
  Update DayaLoanContracts.ContractNumber from MerchantCreditList.

  Match: NationalCode (unique cases).
  Special case — same NationalCode 2490371436 on TWO users (two mobiles):
    UserId=50 (09207316560, Created 2025-11-11) → C4_D8815963 (فعال‌سازی 1404/8/24 = 2025-11-15)
    UserId=51 (09366426797, Created 2025-11-12) → C4_G8945289 (فعال‌سازی 1404/8/30 = 2025-11-21)
    User 51 may have no DayaLoanContracts row yet → INSERT.

  Script defaults to ROLLBACK; uncomment COMMIT after verifying.
*/

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRAN;

------------------------------------------------------------
-- 1) Unique NationalCodes → UPDATE ContractNumber
------------------------------------------------------------
;WITH Src AS (
    SELECT *
    FROM (VALUES
        (N'C4_C11231802', N'0312980973'),
        (N'C4_H10706299', N'4060667961'),
        (N'C4_E10490523', N'4072306754'),
        (N'C4_C10337736', N'0054140161'),
        (N'C4_Q10727431', N'5279791253'),
        (N'C4_W10568862', N'1960144804'),
        (N'C4_V10338780', N'4072240567'),
        (N'C4_G10538762', N'6639906902'),
        (N'C4_N10921338', N'0943060761'),
        (N'C4_P9320383',  N'2572729636'),
        (N'C4_Y9276146',  N'0012011061'),
        (N'C4_Q9465813',  N'2480017796'),
        (N'C4_A9107753',  N'0450314103'),
        (N'C4_B9348378',  N'0452314488'),
        (N'C4_U9595222',  N'2062876815'),
        (N'C4_X9630520',  N'0680164286'),
        (N'C4_J9199624',  N'0681699401'),
        (N'C4_B9813553',  N'0055680364'),
        (N'C4_M9994735',  N'0793851572'),
        (N'C4_S9301452',  N'0082242925'),
        (N'C4_V9257168',  N'4459676451'),
        (N'C4_T9961063',  N'0312342993'),
        (N'C4_L9524515',  N'0024757489'),
        (N'C4_Q9947535',  N'2480259668'),
        (N'C4_T9892089',  N'2451477016'),
        (N'C4_N9945163',  N'1289603669'),
        (N'C4_W8785358',  N'0322922720'),
        (N'C4_J8388494',  N'2002637903'),
        (N'C4_V8165679',  N'0081093829'),
        (N'C4_T8579002',  N'5289780335')
        -- 2490371436 handled separately by UserId below
    ) AS v(ContractNumber, NationalCode)
)
UPDATE d
SET
    d.ContractNumber = s.ContractNumber,
    d.LastModified = SYSUTCDATETIME()
FROM CMS.DayaLoanContracts d
INNER JOIN Src s ON s.NationalCode = d.NationalCode
WHERE d.IsDeleted = 0;

PRINT CONCAT('Updated by NationalCode (unique): ', @@ROWCOUNT);

------------------------------------------------------------
-- 2) Shared NC 2490371436 — match by UserId + Excel activation date
------------------------------------------------------------
-- UserId 50 ← earlier activation 1404/8/24 → C4_D8815963
UPDATE CMS.DayaLoanContracts
SET
    ContractNumber = N'C4_D8815963',
    LastModified = SYSUTCDATETIME()
WHERE IsDeleted = 0
  AND UserId = 50
  AND NationalCode = N'2490371436';

PRINT CONCAT('Updated UserId=50 (C4_D8815963): ', @@ROWCOUNT);

-- UserId 51 ← later activation 1404/8/30 → C4_G8945289
IF EXISTS (
    SELECT 1 FROM CMS.DayaLoanContracts
    WHERE IsDeleted = 0 AND UserId = 51 AND NationalCode = N'2490371436'
)
BEGIN
    UPDATE CMS.DayaLoanContracts
    SET
        ContractNumber = N'C4_G8945289',
        LastModified = SYSUTCDATETIME()
    WHERE IsDeleted = 0
      AND UserId = 51
      AND NationalCode = N'2490371436';

    PRINT CONCAT('Updated UserId=51 (C4_G8945289): ', @@ROWCOUNT);
END
ELSE
BEGIN
    INSERT INTO CMS.DayaLoanContracts
    (
        UserId, NationalCode, ContractNumber, Status, IsProcessed,
        LastCheckDate, ProcessedDate, TransactionId,
        Created, CreatedBy, LastModified, LastModifiedBy, IsDeleted
    )
    VALUES
    (
        51,
        N'2490371436',
        N'C4_G8945289',
        0, -- NotRequested (same as sibling row for UserId=50 in dump; adjust if needed)
        0,
        NULL,
        NULL,
        NULL,
        SYSUTCDATETIME(),
        NULL,
        SYSUTCDATETIME(),
        NULL,
        0
    );

    PRINT 'Inserted DayaLoanContracts for UserId=51 (C4_G8945289).';
END

------------------------------------------------------------
-- Verify
------------------------------------------------------------
SELECT
    d.Id,
    d.UserId,
    u.Mobile,
    d.NationalCode,
    d.ContractNumber,
    d.Status,
    d.IsProcessed
FROM CMS.DayaLoanContracts d
INNER JOIN CMS.Users u ON u.Id = d.UserId
WHERE d.IsDeleted = 0
  AND d.NationalCode = N'2490371436'
ORDER BY d.UserId;

-- Uncomment to apply:
-- COMMIT;
ROLLBACK;
PRINT 'Rolled back (safety). Uncomment COMMIT and remove ROLLBACK to apply.';
