/*
  PHASE 2 — Fix broken WH.RefrenceId + create UPP for 5 remaining manualy txs.

  Tx exists; WH either RefrenceId=0 (CreatedBy = Tx Id hint) or missing (Tx 68).

  Hardcoded mapping (UserWalletHistories2.csv + Transactions.csv):

    UserId  WH     Tx   Notes
    ------  -----  ---  -----
      71    118     56   WH Ref=0, CreatedBy=56
      72    124     57   WH Ref=0, CreatedBy=57
      69    119     58   WH Ref=0, CreatedBy=58
      74    120     59   WH Ref=0, CreatedBy=59
     142    —       68   no WH — insert history-only (no wallet charge)

  Steps:
    1) Validate WH hint + Tx manualy% + user/wallet
    2) UPDATE WH.RefrenceId for rows 118,124,119,120
    3) INSERT UserWalletHistory for User 142 / Tx 68 if missing
    4) INSERT UserPackagePurchases Manual(3) for all 5 txs

  Does NOT change Users.PackagePurchaseMethod or wallet Balance.

  Defaults to ROLLBACK. Uncomment COMMIT after preview.
*/

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRAN;

DECLARE @Now DATETIME2 = SYSUTCDATETIME();

DECLARE @BasePackageId BIGINT;
DECLARE @BasePackagePrice BIGINT;

SELECT TOP (1)
    @BasePackageId = Id,
    @BasePackagePrice = Price
FROM CMS.Packages
WHERE IsDeleted = 0 AND IsBasePackage = 1
ORDER BY Id;

IF @BasePackageId IS NULL
    THROW 50001, 'Base package not found.', 1;

DECLARE @Phase2 TABLE (
    UserId BIGINT NOT NULL,
    WalletHistoryId BIGINT NULL,
    TransactionId BIGINT NOT NULL,
    PRIMARY KEY (TransactionId)
);

INSERT INTO @Phase2 (UserId, WalletHistoryId, TransactionId) VALUES
(71,  118, 56),
(72,  124, 57),
(69,  119, 58),
(74,  120, 59),
(142, NULL, 68);

DECLARE @Phase2Count INT = (SELECT COUNT(*) FROM @Phase2);
PRINT CONCAT('Phase2 expected rows: ', @Phase2Count);

------------------------------------------------------------
-- 1) Validate before changes
------------------------------------------------------------
SELECT
    p.UserId,
    u.Mobile AS UserMobile,
    u.FirstName,
    u.LastName,
    p.WalletHistoryId AS ExpectedWhId,
    p.TransactionId AS ExpectedTxId,
    h.Id AS ActualWhId,
    h.RefrenceId AS WhRefrenceId,
    h.CreatedBy AS WhCreatedBy,
    h.ChangeValue AS WhChangeValue,
    w.UserId AS WhWalletUserId,
    t.Id AS ActualTxId,
    t.RefId AS TxRefId,
    t.Amount AS TxAmount,
    t.CreatedBy AS TxCreatedBy,
    t.LastModifiedBy AS TxLastModifiedBy,
    CASE
        WHEN u.Id IS NULL THEN N'USER_MISSING'
        WHEN t.Id IS NULL THEN N'TX_MISSING'
        WHEN t.RefId NOT LIKE N'manualy%' THEN N'TX_NOT_MANUALY'
        WHEN t.PaymentStatus <> 0 THEN N'TX_NOT_SUCCESS'
        WHEN p.WalletHistoryId IS NULL THEN N'NO_WH_EXPECTED_CREATE'
        WHEN h.Id IS NULL THEN N'WH_MISSING'
        WHEN w.UserId <> p.UserId THEN N'WH_WALLET_USER_MISMATCH'
        WHEN TRY_CAST(h.CreatedBy AS BIGINT) <> p.TransactionId THEN N'WH_CREATEDBY_HINT_MISMATCH'
        WHEN h.RefrenceId = p.TransactionId THEN N'WH_ALREADY_LINKED'
        WHEN h.RefrenceId IS NOT NULL AND h.RefrenceId <> 0 AND h.RefrenceId <> p.TransactionId
            THEN N'WH_REF_POINTS_OTHER_TX'
        ELSE N'OK_FIX_REF'
    END AS WhValidation,
    CASE
        WHEN u.Mobile IN (
            NULLIF(LTRIM(RTRIM(t.CreatedBy)), N''),
            NULLIF(LTRIM(RTRIM(t.LastModifiedBy)), N'')
        ) THEN N'MOBILE_OK'
        WHEN NULLIF(LTRIM(RTRIM(t.CreatedBy)), N'') LIKE N'09%' THEN N'TX_MOBILE_USER_MISMATCH'
        ELSE N'TX_NO_MOBILE_ON_ROW'
    END AS MobileCheck
FROM @Phase2 p
LEFT JOIN CMS.Users u ON u.Id = p.UserId AND u.IsDeleted = 0
LEFT JOIN CMS.Transactions t ON t.Id = p.TransactionId AND t.IsDeleted = 0
LEFT JOIN CMS.UserWalletHistories h ON h.Id = p.WalletHistoryId AND h.IsDeleted = 0
LEFT JOIN CMS.UserWallets w ON w.Id = h.WalletId AND w.IsDeleted = 0
ORDER BY p.TransactionId;

DECLARE @BadWh INT = (
    SELECT COUNT(*)
    FROM @Phase2 p
    LEFT JOIN CMS.UserWalletHistories h ON h.Id = p.WalletHistoryId AND h.IsDeleted = 0
    LEFT JOIN CMS.UserWallets w ON w.Id = h.WalletId AND w.IsDeleted = 0
    LEFT JOIN CMS.Transactions t ON t.Id = p.TransactionId AND t.IsDeleted = 0
    LEFT JOIN CMS.Users u ON u.Id = p.UserId AND u.IsDeleted = 0
    WHERE t.Id IS NULL
       OR u.Id IS NULL
       OR t.RefId NOT LIKE N'manualy%'
       OR t.PaymentStatus <> 0
       OR (
            p.WalletHistoryId IS NOT NULL
            AND (
                h.Id IS NULL
             OR w.UserId <> p.UserId
             OR TRY_CAST(h.CreatedBy AS BIGINT) <> p.TransactionId
             OR (h.RefrenceId IS NOT NULL AND h.RefrenceId <> 0 AND h.RefrenceId <> p.TransactionId)
            )
       )
);

IF @BadWh > 0
BEGIN
    DECLARE @BadMsg NVARCHAR(200);
    SET @BadMsg = CONCAT(N'Validation failed for ', @BadWh, N' row(s). Aborting.');
    ROLLBACK;
    RAISERROR(@BadMsg, 16, 1);
    RETURN;
END

PRINT 'Validation OK.';

------------------------------------------------------------
-- 2) Fix WH.RefrenceId (118,124,119,120)
------------------------------------------------------------
SELECT
    p.UserId,
    p.WalletHistoryId,
    p.TransactionId,
    h.RefrenceId AS OldRefrenceId,
    N'UPDATE RefrenceId' AS Action
FROM @Phase2 p
INNER JOIN CMS.UserWalletHistories h ON h.Id = p.WalletHistoryId AND h.IsDeleted = 0
WHERE p.WalletHistoryId IS NOT NULL
  AND (h.RefrenceId IS NULL OR h.RefrenceId = 0 OR h.RefrenceId <> p.TransactionId);

UPDATE h
SET
    h.RefrenceId = p.TransactionId,
    h.LastModified = @Now
FROM @Phase2 p
INNER JOIN CMS.UserWalletHistories h ON h.Id = p.WalletHistoryId AND h.IsDeleted = 0
WHERE p.WalletHistoryId IS NOT NULL
  AND (h.RefrenceId IS NULL OR h.RefrenceId = 0);

PRINT CONCAT('WalletHistory RefrenceId updated: ', @@ROWCOUNT);

------------------------------------------------------------
-- 3) User 142 / Tx 68 — history-only WH if missing
------------------------------------------------------------
SELECT
    p.UserId,
    p.TransactionId,
    w.Id AS WalletId,
    h.Id AS ExistingWhId,
    CASE WHEN h.Id IS NOT NULL THEN N'SKIP_WH_EXISTS' ELSE N'CREATE_WH_HISTORY' END AS WhPlan
FROM @Phase2 p
LEFT JOIN CMS.UserWallets w ON w.UserId = p.UserId AND w.IsDeleted = 0
LEFT JOIN CMS.UserWalletHistories h ON h.IsDeleted = 0 AND h.RefrenceId = p.TransactionId
    AND h.WalletId = w.Id
WHERE p.WalletHistoryId IS NULL;

DECLARE @UserId BIGINT = 142;
DECLARE @TxId BIGINT = 68;
DECLARE @WalletId BIGINT;
DECLARE @PackageId BIGINT;
DECLARE @Amount BIGINT;
DECLARE @HistoryAt DATETIME2;
DECLARE @DiscountAmount BIGINT;
DECLARE @NetworkBalance BIGINT;

IF EXISTS (SELECT 1 FROM @Phase2 WHERE TransactionId = 68 AND WalletHistoryId IS NULL)
BEGIN
    SELECT
        @WalletId = w.Id,
        @PackageId = COALESCE(
            NULLIF(cm.LastPackageId, 0),
            NULLIF(cm.FirstPackageId, 0),
            @BasePackageId
        ),
        @Amount = t.Amount,
        @HistoryAt = COALESCE(t.PaymentDate, @Now),
        @NetworkBalance = COALESCE(w.NetworkBalance, 0),
        @DiscountAmount = CAST(ROUND(t.Amount * COALESCE(pkg.DiscountMultiplier, 2.0), 0) AS BIGINT)
    FROM CMS.Transactions t
    INNER JOIN CMS.Users u ON u.Id = 142 AND u.IsDeleted = 0
    LEFT JOIN CMS.UserWallets w ON w.UserId = u.Id AND w.IsDeleted = 0
    LEFT JOIN CMS.ClubMemberships cm ON cm.UserId = u.Id AND cm.IsDeleted = 0
    LEFT JOIN CMS.Packages pkg ON pkg.Id = COALESCE(
        NULLIF(cm.LastPackageId, 0),
        NULLIF(cm.FirstPackageId, 0),
        @BasePackageId
    ) AND pkg.IsDeleted = 0
    WHERE t.Id = 68 AND t.IsDeleted = 0;

    IF @WalletId IS NULL
    BEGIN
        INSERT INTO CMS.UserWallets
            (UserId, Balance, NetworkBalance, DiscountBalance, WalletMode, Created, LastModified, IsDeleted)
        VALUES (142, 0, 0, 0, 0, @HistoryAt, @HistoryAt, 0);
        SET @WalletId = SCOPE_IDENTITY();
        SET @NetworkBalance = 0;
        PRINT CONCAT('Wallet created for User 142: ', @WalletId);
    END

    IF NOT EXISTS (
        SELECT 1
        FROM CMS.UserWalletHistories h
        WHERE h.WalletId = @WalletId
          AND h.IsDeleted = 0
          AND h.RefrenceId = 68
    )
    BEGIN
        INSERT INTO CMS.UserWalletHistories
        (
            WalletId, CurrentBalance, ChangeValue, CurrentNetworkBalance, ChangeNerworkValue,
            CurrentDiscountBalance, ChangeDiscountValue, IsIncrease, RefrenceId, PackageId,
            Created, CreatedBy, LastModified, LastModifiedBy, IsDeleted
        )
        VALUES
        (
            @WalletId, 0, @Amount, @NetworkBalance, 0,
            0, @DiscountAmount, 1, 68, @PackageId,
            @HistoryAt, NULL, @HistoryAt, NULL, 0
        );
        PRINT CONCAT('WalletHistory created for Tx 68 (no charge): WH Id=', SCOPE_IDENTITY());
    END
    ELSE
        PRINT 'WalletHistory for Tx 68 already exists — skipped.';
END

------------------------------------------------------------
-- 4) UPP preview + insert
------------------------------------------------------------
SELECT
    p.UserId,
    u.Mobile,
    p.TransactionId,
    CASE WHEN upp.Id IS NOT NULL THEN N'SKIP_UPP_EXISTS' ELSE N'CREATE_UPP' END AS UppPlan,
    upp.Id AS ExistingUppId
FROM @Phase2 p
INNER JOIN CMS.Users u ON u.Id = p.UserId AND u.IsDeleted = 0
LEFT JOIN CMS.UserPackagePurchases upp
    ON upp.UserId = p.UserId
   AND upp.IsDeleted = 0
   AND upp.TransactionId = p.TransactionId
ORDER BY p.TransactionId;

SELECT
    p.UserId,
    p.TransactionId,
    COALESCE(
        NULLIF(cm.LastPackageId, 0),
        NULLIF(cm.FirstPackageId, 0),
        @BasePackageId
    ) AS PackageId,
    COALESCE(t.Amount, @BasePackagePrice) AS Amount,
    COALESCE(t.PaymentDate, cm.ActivatedAt, @Now) AS PurchasedAt
INTO #UppPlan
FROM @Phase2 p
INNER JOIN CMS.Transactions t ON t.Id = p.TransactionId AND t.IsDeleted = 0
LEFT JOIN CMS.ClubMemberships cm ON cm.UserId = p.UserId AND cm.IsDeleted = 0
WHERE NOT EXISTS (
    SELECT 1
    FROM CMS.UserPackagePurchases upp
    WHERE upp.UserId = p.UserId
      AND upp.IsDeleted = 0
      AND upp.TransactionId = p.TransactionId
);

DECLARE @UppToCreate INT = (SELECT COUNT(*) FROM #UppPlan);
PRINT CONCAT('UPP to create: ', @UppToCreate);

INSERT INTO CMS.UserPackagePurchases
(
    UserId, PackageId, PurchaseMethod, PurchasedAt, Amount,
    OrderId, TransactionId, Created, CreatedBy, LastModified, LastModifiedBy, IsDeleted
)
SELECT
    pl.UserId,
    pl.PackageId,
    3,
    pl.PurchasedAt,
    pl.Amount,
    NULL,
    pl.TransactionId,
    @Now,
    NULL,
    @Now,
    NULL,
    0
FROM #UppPlan pl
WHERE pl.PackageId IS NOT NULL AND pl.Amount > 0;

PRINT CONCAT('UPP inserted: ', @@ROWCOUNT);
DROP TABLE #UppPlan;

------------------------------------------------------------
-- 5) Verify
------------------------------------------------------------
SELECT
    p.UserId,
    u.Mobile,
    p.TransactionId,
    h.Id AS WalletHistoryId,
    h.RefrenceId AS WhRefrenceId,
    upp.Id AS UppId,
    upp.PurchaseMethod,
    CASE
        WHEN h.Id IS NULL AND p.WalletHistoryId IS NOT NULL THEN N'MISSING_WH'
        WHEN h.RefrenceId <> p.TransactionId AND p.WalletHistoryId IS NOT NULL THEN N'WH_REF_WRONG'
        WHEN NOT EXISTS (
            SELECT 1 FROM CMS.UserWalletHistories hx
            INNER JOIN CMS.UserWallets wx ON wx.Id = hx.WalletId AND wx.UserId = p.UserId
            WHERE hx.IsDeleted = 0 AND hx.RefrenceId = p.TransactionId
        ) THEN N'NO_WH_FOR_TX'
        WHEN upp.Id IS NULL THEN N'MISSING_UPP'
        ELSE N'OK'
    END AS FinalStatus
FROM @Phase2 p
INNER JOIN CMS.Users u ON u.Id = p.UserId AND u.IsDeleted = 0
LEFT JOIN CMS.UserWalletHistories h ON h.Id = p.WalletHistoryId AND h.IsDeleted = 0
LEFT JOIN CMS.UserPackagePurchases upp
    ON upp.UserId = p.UserId
   AND upp.IsDeleted = 0
   AND upp.TransactionId = p.TransactionId
ORDER BY p.TransactionId;

SELECT N'Incomplete after run' AS CheckName, COUNT(*) AS Cnt
FROM @Phase2 p
WHERE NOT EXISTS (
    SELECT 1 FROM CMS.UserPackagePurchases upp
    WHERE upp.UserId = p.UserId AND upp.IsDeleted = 0 AND upp.TransactionId = p.TransactionId
)
OR NOT EXISTS (
    SELECT 1
    FROM CMS.UserWalletHistories h
    INNER JOIN CMS.UserWallets w ON w.Id = h.WalletId AND w.UserId = p.UserId
    WHERE h.IsDeleted = 0 AND h.RefrenceId = p.TransactionId
);

ROLLBACK;
-- COMMIT;

PRINT 'Done (rolled back by default). Uncomment COMMIT when preview looks correct.';
