/*
  Fix: Manual payment wrongly stored as DirectPurchase (2) → Manual (3)
  + create missing Tx/UPP/UserWalletHistory (same ledger as CreateManualPayment service)

  Background:
    Before commit 427d2cc, CreateManualPayment set Users.PackagePurchaseMethod = DirectPurchase
    and older rows may lack UserPackagePurchases / wallet history entirely.

  Scope — ALL users present in CMS.ManualPayments (IsDeleted = 0):
    1) Users / UPP / Club / Cycle: PurchaseMethod 2 → 3
    2) Users with NO UPP (~18): create Success Tx + UPP Manual(3)
    3) After that, for EVERY user missing UserWalletHistory on their Tx (~6 existing + ~18 new):
         - INSERT history only (Created=PaymentDate, ChangeValue=Amount)
         - DO NOT re-charge wallet
         - Prefer existing MP/UPP TransactionId (the ~6 already have Tx)

  PackagePurchaseMethod: None=0, DayaLoan=1, DirectPurchase=2, Manual=3
  ManualPaymentStatus: Pending=0, Approved=1, Rejected=2

  Defaults to ROLLBACK. Uncomment COMMIT after verifying PRINT / 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;

------------------------------------------------------------
-- Target: every user that appears in ManualPayments
------------------------------------------------------------
DECLARE @FixUsers TABLE (UserId BIGINT PRIMARY KEY);

INSERT INTO @FixUsers (UserId)
SELECT DISTINCT mp.UserId
FROM CMS.ManualPayments mp
WHERE mp.IsDeleted = 0;

DECLARE @TargetUserCount INT = (SELECT COUNT(*) FROM @FixUsers);
PRINT CONCAT('Target users (all ManualPayments): ', @TargetUserCount);
PRINT CONCAT('BasePackageId=', @BasePackageId, ' Price=', @BasePackagePrice);

------------------------------------------------------------
-- Preview before
-- NOTE: UPP row-count ≠ distinct users (one user can have 2+ UPP rows)
------------------------------------------------------------
SELECT 'Users DirectPurchase (in scope)' AS CheckName, COUNT(*) AS Cnt
FROM CMS.Users u
INNER JOIN @FixUsers f ON f.UserId = u.Id
WHERE u.IsDeleted = 0 AND u.PackagePurchaseMethod = 2
UNION ALL
SELECT 'UPP DirectPurchase ROWS (in scope)', COUNT(*)
FROM CMS.UserPackagePurchases upp
INNER JOIN @FixUsers f ON f.UserId = upp.UserId
WHERE upp.IsDeleted = 0 AND upp.PurchaseMethod = 2
UNION ALL
SELECT 'Users with UPP DirectPurchase (distinct)', COUNT(DISTINCT upp.UserId)
FROM CMS.UserPackagePurchases upp
INNER JOIN @FixUsers f ON f.UserId = upp.UserId
WHERE upp.IsDeleted = 0 AND upp.PurchaseMethod = 2
UNION ALL
SELECT 'ClubMembership DirectPurchase (in scope)', COUNT(*)
FROM CMS.ClubMemberships m
INNER JOIN @FixUsers f ON f.UserId = m.UserId
WHERE m.IsDeleted = 0 AND m.PurchaseMethod = 2
UNION ALL
SELECT 'Cycle DirectPurchase (in scope)', COUNT(*)
FROM CMS.ClubMembershipCycles c
INNER JOIN @FixUsers f ON f.UserId = c.UserId
WHERE c.IsDeleted = 0 AND c.PurchaseMethod = 2
UNION ALL
SELECT 'Users with any UPP (distinct)', COUNT(*)
FROM @FixUsers f
WHERE EXISTS (
    SELECT 1 FROM CMS.UserPackagePurchases upp
    WHERE upp.UserId = f.UserId AND upp.IsDeleted = 0
)
UNION ALL
SELECT 'Missing any UPP (will create Tx+UPP, then WH)', COUNT(*)
FROM @FixUsers f
WHERE NOT EXISTS (
    SELECT 1 FROM CMS.UserPackagePurchases upp
    WHERE upp.UserId = f.UserId AND upp.IsDeleted = 0
)
UNION ALL
SELECT 'Missing WH — have Tx already (create history only)', COUNT(*)
FROM @FixUsers f
OUTER APPLY (
    SELECT TOP (1)
        mp.Id AS ManualPaymentId,
        mp.TransactionId AS MpTxId,
        mp.ReferenceNumber
    FROM CMS.ManualPayments mp
    WHERE mp.UserId = f.UserId AND mp.IsDeleted = 0
    ORDER BY
        CASE WHEN mp.Status = 1 THEN 0 ELSE 1 END,
        CASE WHEN mp.TransactionId IS NOT NULL THEN 0 ELSE 1 END,
        COALESCE(mp.ApprovedAt, mp.Created) DESC,
        mp.Id DESC
) mp
OUTER APPLY (
    SELECT TOP (1) upp.TransactionId AS UppTxId
    FROM CMS.UserPackagePurchases upp
    WHERE upp.UserId = f.UserId AND upp.IsDeleted = 0 AND upp.TransactionId IS NOT NULL
    ORDER BY upp.Id DESC
) upp
CROSS APPLY (
    SELECT COALESCE(
        CASE
            WHEN mp.MpTxId IS NOT NULL
                 AND EXISTS (
                     SELECT 1 FROM CMS.Transactions t
                     WHERE t.Id = mp.MpTxId AND t.IsDeleted = 0 AND t.PaymentStatus = 0
                 )
            THEN mp.MpTxId
        END,
        upp.UppTxId,
        (
            SELECT TOP (1) t.Id
            FROM CMS.Transactions t
            WHERE t.IsDeleted = 0 AND t.PaymentStatus = 0
              AND mp.ReferenceNumber IS NOT NULL
              AND t.RefId = mp.ReferenceNumber
            ORDER BY t.Id DESC
        )
    ) AS TxId
) tx
WHERE tx.TxId IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM CMS.UserWalletHistories h
      INNER JOIN CMS.UserWallets w ON w.Id = h.WalletId AND w.IsDeleted = 0
      WHERE w.UserId = f.UserId
        AND h.IsDeleted = 0
        AND h.RefrenceId = tx.TxId
  );

-- Diagnostic: users who already have UPP (expect ~6) — Tx link vs history
SELECT
    f.UserId,
    mp.ManualPaymentId,
    mp.MpTxId,
    upp.UppTxId,
    tx.TxId AS ResolvedTxId,
    CASE
        WHEN tx.TxId IS NULL THEN N'NO_TX_RESOLVED'
        WHEN h.Id IS NULL THEN N'MISSING_HISTORY'
        ELSE N'HAS_HISTORY'
    END AS HistoryStatus,
    h.Id AS WalletHistoryId
FROM @FixUsers f
OUTER APPLY (
    SELECT TOP (1)
        mp0.Id AS ManualPaymentId,
        mp0.TransactionId AS MpTxId,
        mp0.ReferenceNumber
    FROM CMS.ManualPayments mp0
    WHERE mp0.UserId = f.UserId AND mp0.IsDeleted = 0
    ORDER BY
        CASE WHEN mp0.Status = 1 THEN 0 ELSE 1 END,
        CASE WHEN mp0.TransactionId IS NOT NULL THEN 0 ELSE 1 END,
        COALESCE(mp0.ApprovedAt, mp0.Created) DESC,
        mp0.Id DESC
) mp
OUTER APPLY (
    SELECT TOP (1) upp0.TransactionId AS UppTxId
    FROM CMS.UserPackagePurchases upp0
    WHERE upp0.UserId = f.UserId AND upp0.IsDeleted = 0 AND upp0.TransactionId IS NOT NULL
    ORDER BY upp0.Id DESC
) upp
CROSS APPLY (
    SELECT COALESCE(
        CASE
            WHEN mp.MpTxId IS NOT NULL
                 AND EXISTS (
                     SELECT 1 FROM CMS.Transactions t
                     WHERE t.Id = mp.MpTxId AND t.IsDeleted = 0 AND t.PaymentStatus = 0
                 )
            THEN mp.MpTxId
        END,
        upp.UppTxId,
        (
            SELECT TOP (1) t.Id
            FROM CMS.Transactions t
            WHERE t.IsDeleted = 0 AND t.PaymentStatus = 0
              AND mp.ReferenceNumber IS NOT NULL
              AND t.RefId = mp.ReferenceNumber
            ORDER BY t.Id DESC
        )
    ) AS TxId
) tx
OUTER APPLY (
    SELECT TOP (1) h0.Id
    FROM CMS.UserWalletHistories h0
    INNER JOIN CMS.UserWallets w0 ON w0.Id = h0.WalletId AND w0.IsDeleted = 0
    WHERE w0.UserId = f.UserId AND h0.IsDeleted = 0 AND h0.RefrenceId = tx.TxId
) h
WHERE EXISTS (
    SELECT 1 FROM CMS.UserPackagePurchases uany
    WHERE uany.UserId = f.UserId AND uany.IsDeleted = 0
)
ORDER BY f.UserId;

-- Who has more than one UPP row? (explains ROWS vs users)
SELECT
    upp.UserId,
    COUNT(*) AS UppRowCnt,
    SUM(CASE WHEN upp.PurchaseMethod = 2 THEN 1 ELSE 0 END) AS UppDirectPurchaseCnt,
    SUM(CASE WHEN upp.PurchaseMethod = 3 THEN 1 ELSE 0 END) AS UppManualCnt
FROM CMS.UserPackagePurchases upp
INNER JOIN @FixUsers f ON f.UserId = upp.UserId
WHERE upp.IsDeleted = 0
GROUP BY upp.UserId
HAVING COUNT(*) > 1
ORDER BY UppRowCnt DESC, upp.UserId;

------------------------------------------------------------
-- 1) Users 2 → 3
------------------------------------------------------------
UPDATE u
SET
    u.PackagePurchaseMethod = 3,
    u.LastModified = @Now
FROM CMS.Users u
INNER JOIN @FixUsers f ON f.UserId = u.Id
WHERE u.IsDeleted = 0
  AND u.PackagePurchaseMethod = 2;

PRINT CONCAT('Users updated 2→3: ', @@ROWCOUNT);

------------------------------------------------------------
-- 2) Existing UPP 2 → 3
------------------------------------------------------------
UPDATE upp
SET
    upp.PurchaseMethod = 3,
    upp.LastModified = @Now
FROM CMS.UserPackagePurchases upp
INNER JOIN @FixUsers f ON f.UserId = upp.UserId
WHERE upp.IsDeleted = 0
  AND upp.PurchaseMethod = 2;

PRINT CONCAT('UserPackagePurchases updated 2→3: ', @@ROWCOUNT);

------------------------------------------------------------
-- 3) ClubMemberships 2 → 3
------------------------------------------------------------
UPDATE m
SET
    m.PurchaseMethod = 3,
    m.LastModified = @Now
FROM CMS.ClubMemberships m
INNER JOIN @FixUsers f ON f.UserId = m.UserId
WHERE m.IsDeleted = 0
  AND m.PurchaseMethod = 2;

PRINT CONCAT('ClubMemberships updated 2→3: ', @@ROWCOUNT);

------------------------------------------------------------
-- 4) ClubMembershipCycles 2 → 3
------------------------------------------------------------
UPDATE c
SET
    c.PurchaseMethod = 3,
    c.LastModified = @Now
FROM CMS.ClubMembershipCycles c
INNER JOIN @FixUsers f ON f.UserId = c.UserId
WHERE c.IsDeleted = 0
  AND c.PurchaseMethod = 2;

PRINT CONCAT('ClubMembershipCycles updated 2→3: ', @@ROWCOUNT);

------------------------------------------------------------
-- 5) Create missing Tx + UPP (like CreateManualPayment ledger)
------------------------------------------------------------
;WITH MissingUppUsers AS (
    -- Any non-deleted UPP counts as present (avoid duplicate for method=2 / broken Tx)
    SELECT f.UserId
    FROM @FixUsers f
    WHERE NOT EXISTS (
        SELECT 1
        FROM CMS.UserPackagePurchases upp
        WHERE upp.UserId = f.UserId
          AND upp.IsDeleted = 0
    )
),
BestManual AS (
    SELECT
        mp.UserId,
        mp.Id AS ManualPaymentId,
        mp.Amount,
        mp.TransactionId,
        mp.ReferenceNumber,
        mp.Description,
        COALESCE(mp.ApprovedAt, mp.Created) AS PurchasedAt,
        ROW_NUMBER() OVER (
            PARTITION BY mp.UserId
            ORDER BY
                CASE WHEN mp.Status = 1 THEN 0 ELSE 1 END, -- Approved first
                CASE WHEN mp.TransactionId IS NOT NULL THEN 0 ELSE 1 END,
                COALESCE(mp.ApprovedAt, mp.Created) DESC,
                mp.Id DESC
        ) AS rn
    FROM CMS.ManualPayments mp
    INNER JOIN MissingUppUsers m ON m.UserId = mp.UserId
    WHERE mp.IsDeleted = 0
),
Candidates AS (
    SELECT
        bm.UserId,
        bm.ManualPaymentId,
        bm.TransactionId AS ExistingTxId,
        bm.ReferenceNumber,
        bm.Description,
        bm.PurchasedAt,
        COALESCE(NULLIF(cm.LastPackageId, 0), NULLIF(cm.FirstPackageId, 0), @BasePackageId) AS PackageId,
        CASE
            WHEN bm.Amount IS NOT NULL AND bm.Amount > 0 THEN bm.Amount
            ELSE COALESCE(p.Price, @BasePackagePrice)
        END AS Amount
    FROM BestManual bm
    LEFT JOIN CMS.ClubMemberships cm
        ON cm.UserId = bm.UserId AND cm.IsDeleted = 0
    LEFT JOIN CMS.Packages p
        ON p.Id = COALESCE(NULLIF(cm.LastPackageId, 0), NULLIF(cm.FirstPackageId, 0), @BasePackageId)
       AND p.IsDeleted = 0
    WHERE bm.rn = 1
)
SELECT *
INTO #UppToCreate
FROM Candidates
WHERE PackageId IS NOT NULL AND Amount > 0;

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

SELECT
    UserId,
    ManualPaymentId,
    ExistingTxId,
    PackageId,
    Amount,
    PurchasedAt,
    CASE WHEN ExistingTxId IS NOT NULL THEN N'REUSE_MP_TX' ELSE N'CREATE_NEW_TX' END AS TxPlan
FROM #UppToCreate
ORDER BY UserId;

DECLARE @UserId BIGINT;
DECLARE @ManualPaymentId BIGINT;
DECLARE @ExistingTxId BIGINT;
DECLARE @PackageId BIGINT;
DECLARE @Amount BIGINT;
DECLARE @PurchasedAt DATETIME2;
DECLARE @ReferenceNumber NVARCHAR(200);
DECLARE @Description NVARCHAR(500);
DECLARE @TxId BIGINT;
DECLARE @CreatedUpp INT = 0;
DECLARE @CreatedTx INT = 0;
DECLARE @ReusedTx INT = 0;

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT UserId, ManualPaymentId, ExistingTxId, PackageId, Amount, PurchasedAt, ReferenceNumber, Description
    FROM #UppToCreate;

OPEN cur;
FETCH NEXT FROM cur INTO
    @UserId, @ManualPaymentId, @ExistingTxId, @PackageId, @Amount, @PurchasedAt, @ReferenceNumber, @Description;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @TxId = NULL;

    -- Reuse Success Tx from ManualPayment when possible
    IF @ExistingTxId IS NOT NULL
       AND EXISTS (
           SELECT 1 FROM CMS.Transactions t
           WHERE t.Id = @ExistingTxId AND t.IsDeleted = 0 AND t.PaymentStatus = 0
       )
    BEGIN
        SET @TxId = @ExistingTxId;
        SET @ReusedTx = @ReusedTx + 1;
    END
    ELSE
    BEGIN
        INSERT INTO CMS.Transactions
        (
            Amount, Description, PaymentStatus, PaymentDate, RefId, Type,
            Created, CreatedBy, LastModified, LastModifiedBy, IsDeleted
        )
        VALUES
        (
            @Amount,
            CONCAT(
                N'عضویت دستی باشگاه مشتریان - ',
                ISNULL(@Description, N'backfill UPP'),
                N' - مرجع: ',
                ISNULL(@ReferenceNumber, CONCAT(N'MP-', @ManualPaymentId))
            ),
            0, -- Success
            @PurchasedAt,
            ISNULL(@ReferenceNumber, CONCAT(N'UPP-MP-FIX-', @ManualPaymentId)),
            2, -- DepositExternal1
            @PurchasedAt, NULL, @PurchasedAt, NULL, 0
        );
        SET @TxId = SCOPE_IDENTITY();
        SET @CreatedTx = @CreatedTx + 1;

        UPDATE CMS.ManualPayments
        SET
            TransactionId = @TxId,
            LastModified = @Now
        WHERE Id = @ManualPaymentId
          AND (TransactionId IS NULL OR TransactionId <> @TxId);
    END

    INSERT INTO CMS.UserPackagePurchases
    (
        UserId, PackageId, PurchaseMethod, PurchasedAt, Amount,
        OrderId, TransactionId, Created, CreatedBy, LastModified, LastModifiedBy, IsDeleted
    )
    VALUES
    (
        @UserId, @PackageId, 3, -- Manual
        @PurchasedAt, @Amount,
        NULL, @TxId, @PurchasedAt, NULL, @PurchasedAt, NULL, 0
    );

    -- Align user flag (only None/DirectPurchase → Manual; leave real Daya=1 alone)
    UPDATE CMS.Users
    SET
        PackagePurchaseMethod = 3,
        LastModified = @Now
    WHERE Id = @UserId
      AND IsDeleted = 0
      AND PackagePurchaseMethod IN (0, 2);

    SET @CreatedUpp = @CreatedUpp + 1;

    FETCH NEXT FROM cur INTO
        @UserId, @ManualPaymentId, @ExistingTxId, @PackageId, @Amount, @PurchasedAt, @ReferenceNumber, @Description;
END

CLOSE cur;
DEALLOCATE cur;

DROP TABLE #UppToCreate;

PRINT CONCAT('UPP created: ', @CreatedUpp,
             ' | Tx reused: ', @ReusedTx,
             ' | Tx created: ', @CreatedTx);

------------------------------------------------------------
-- 6) UserWalletHistory for every FixUser that has a Tx but no history
--    Runs AFTER step 5 so the ~18 new Txs are included.
--    The ~6 with existing UPP/Tx only get history (no new Tx if resolved).
--    NO wallet Balance/Discount charge.
------------------------------------------------------------
;WITH BestManualAll AS (
    SELECT
        mp.UserId,
        mp.Id AS ManualPaymentId,
        mp.Amount,
        mp.TransactionId,
        mp.ReferenceNumber,
        mp.Description,
        COALESCE(mp.ApprovedAt, mp.Created) AS PurchasedAt,
        ROW_NUMBER() OVER (
            PARTITION BY mp.UserId
            ORDER BY
                CASE WHEN mp.Status = 1 THEN 0 ELSE 1 END,
                CASE WHEN mp.TransactionId IS NOT NULL THEN 0 ELSE 1 END,
                COALESCE(mp.ApprovedAt, mp.Created) DESC,
                mp.Id DESC
        ) AS rn
    FROM CMS.ManualPayments mp
    INNER JOIN @FixUsers f ON f.UserId = mp.UserId
    WHERE mp.IsDeleted = 0
),
Resolved AS (
    SELECT
        bm.UserId,
        bm.ManualPaymentId,
        bm.ReferenceNumber,
        bm.Description,
        bm.PurchasedAt,
        COALESCE(NULLIF(cm.LastPackageId, 0), NULLIF(cm.FirstPackageId, 0), @BasePackageId) AS PackageId,
        CASE
            WHEN bm.Amount IS NOT NULL AND bm.Amount > 0 THEN bm.Amount
            ELSE COALESCE(p.Price, @BasePackagePrice)
        END AS Amount,
        COALESCE(
            CASE
                WHEN bm.TransactionId IS NOT NULL
                     AND EXISTS (
                         SELECT 1 FROM CMS.Transactions t
                         WHERE t.Id = bm.TransactionId AND t.IsDeleted = 0 AND t.PaymentStatus = 0
                     )
                THEN bm.TransactionId
            END,
            (
                SELECT TOP (1) upp.TransactionId
                FROM CMS.UserPackagePurchases upp
                WHERE upp.UserId = bm.UserId AND upp.IsDeleted = 0 AND upp.TransactionId IS NOT NULL
                ORDER BY upp.Id DESC
            ),
            (
                SELECT TOP (1) t.Id
                FROM CMS.Transactions t
                WHERE t.IsDeleted = 0 AND t.PaymentStatus = 0
                  AND bm.ReferenceNumber IS NOT NULL
                  AND t.RefId = bm.ReferenceNumber
                ORDER BY t.Id DESC
            )
        ) AS TxId
    FROM BestManualAll bm
    LEFT JOIN CMS.ClubMemberships cm
        ON cm.UserId = bm.UserId AND cm.IsDeleted = 0
    LEFT JOIN CMS.Packages p
        ON p.Id = COALESCE(NULLIF(cm.LastPackageId, 0), NULLIF(cm.FirstPackageId, 0), @BasePackageId)
       AND p.IsDeleted = 0
    WHERE bm.rn = 1
)
SELECT
    r.UserId,
    r.ManualPaymentId,
    r.TxId,
    r.PackageId,
    r.Amount,
    COALESCE(t.PaymentDate, r.PurchasedAt) AS HistoryAt,
    CAST(ROUND(r.Amount * COALESCE(p.DiscountMultiplier, 2.0), 0) AS BIGINT) AS DiscountAmount,
    w.Id AS WalletId,
    COALESCE(w.NetworkBalance, 0) AS NetworkBalance
INTO #WalletHistoryToCreate
FROM Resolved r
LEFT JOIN CMS.Transactions t ON t.Id = r.TxId AND t.IsDeleted = 0
LEFT JOIN CMS.Packages p ON p.Id = r.PackageId AND p.IsDeleted = 0
LEFT JOIN CMS.UserWallets w ON w.UserId = r.UserId AND w.IsDeleted = 0
WHERE r.TxId IS NOT NULL
  AND r.Amount > 0
  AND r.PackageId IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM CMS.UserWalletHistories h
      INNER JOIN CMS.UserWallets wh ON wh.Id = h.WalletId AND wh.IsDeleted = 0
      WHERE wh.UserId = r.UserId
        AND h.IsDeleted = 0
        AND h.RefrenceId = r.TxId
  );

DECLARE @MissingWhCount INT = (SELECT COUNT(*) FROM #WalletHistoryToCreate);
PRINT CONCAT('Missing WalletHistory (after UPP/Tx step — expect ~6 old + ~18 new): ', @MissingWhCount);

SELECT
    UserId, ManualPaymentId, TxId, PackageId, Amount, DiscountAmount, HistoryAt, WalletId,
    N'CREATE_HISTORY_ONLY' AS PlanAction
FROM #WalletHistoryToCreate
ORDER BY UserId;

DECLARE @WalletId BIGINT;
DECLARE @DiscountAmount BIGINT;
DECLARE @HistoryAt DATETIME2;
DECLARE @NetworkBalance BIGINT;
DECLARE @CreatedWh INT = 0;

DECLARE curWh CURSOR LOCAL FAST_FORWARD FOR
    SELECT UserId, ManualPaymentId, TxId, PackageId, Amount, DiscountAmount, HistoryAt, WalletId, NetworkBalance
    FROM #WalletHistoryToCreate;

OPEN curWh;
FETCH NEXT FROM curWh INTO
    @UserId, @ManualPaymentId, @TxId, @PackageId, @Amount, @DiscountAmount, @HistoryAt, @WalletId, @NetworkBalance;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Keep MP linked to resolved Tx when it was found via UPP / RefId
    UPDATE CMS.ManualPayments
    SET TransactionId = @TxId, LastModified = @Now
    WHERE Id = @ManualPaymentId
      AND (TransactionId IS NULL OR TransactionId <> @TxId);

    IF @WalletId IS NULL
    BEGIN
        INSERT INTO CMS.UserWallets
            (UserId, Balance, NetworkBalance, DiscountBalance, WalletMode, Created, LastModified, IsDeleted)
        VALUES
            (@UserId, 0, 0, 0, 0, @HistoryAt, @HistoryAt, 0);
        SET @WalletId = SCOPE_IDENTITY();
        SET @NetworkBalance = 0;
    END

    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,
        @TxId,
        @PackageId,
        @HistoryAt,
        NULL,
        @HistoryAt,
        NULL,
        0
    );

    SET @CreatedWh = @CreatedWh + 1;

    FETCH NEXT FROM curWh INTO
        @UserId, @ManualPaymentId, @TxId, @PackageId, @Amount, @DiscountAmount, @HistoryAt, @WalletId, @NetworkBalance;
END

CLOSE curWh;
DEALLOCATE curWh;

DROP TABLE #WalletHistoryToCreate;

PRINT CONCAT('WalletHistory created (no charge): ', @CreatedWh);

------------------------------------------------------------
-- Verify after
------------------------------------------------------------
SELECT 'Users still DirectPurchase (in scope)' AS CheckName, COUNT(*) AS Cnt
FROM CMS.Users u
INNER JOIN @FixUsers f ON f.UserId = u.Id
WHERE u.IsDeleted = 0 AND u.PackagePurchaseMethod = 2
UNION ALL
SELECT 'UPP still DirectPurchase (in scope)', COUNT(*)
FROM CMS.UserPackagePurchases upp
INNER JOIN @FixUsers f ON f.UserId = upp.UserId
WHERE upp.IsDeleted = 0 AND upp.PurchaseMethod = 2
UNION ALL
SELECT 'Club still DirectPurchase (in scope)', COUNT(*)
FROM CMS.ClubMemberships m
INNER JOIN @FixUsers f ON f.UserId = m.UserId
WHERE m.IsDeleted = 0 AND m.PurchaseMethod = 2
UNION ALL
SELECT 'Cycle still DirectPurchase (in scope)', COUNT(*)
FROM CMS.ClubMembershipCycles c
INNER JOIN @FixUsers f ON f.UserId = c.UserId
WHERE c.IsDeleted = 0 AND c.PurchaseMethod = 2
UNION ALL
SELECT 'Still missing any UPP (in scope)', COUNT(*)
FROM @FixUsers f
WHERE NOT EXISTS (
    SELECT 1 FROM CMS.UserPackagePurchases upp
    WHERE upp.UserId = f.UserId AND upp.IsDeleted = 0
)
UNION ALL
SELECT 'Still missing WalletHistory for resolved Tx', COUNT(*)
FROM @FixUsers f
OUTER APPLY (
    SELECT TOP (1)
        mp.TransactionId AS MpTxId,
        mp.ReferenceNumber
    FROM CMS.ManualPayments mp
    WHERE mp.UserId = f.UserId AND mp.IsDeleted = 0
    ORDER BY
        CASE WHEN mp.Status = 1 THEN 0 ELSE 1 END,
        CASE WHEN mp.TransactionId IS NOT NULL THEN 0 ELSE 1 END,
        COALESCE(mp.ApprovedAt, mp.Created) DESC,
        mp.Id DESC
) mp
OUTER APPLY (
    SELECT TOP (1) upp.TransactionId AS UppTxId
    FROM CMS.UserPackagePurchases upp
    WHERE upp.UserId = f.UserId AND upp.IsDeleted = 0 AND upp.TransactionId IS NOT NULL
    ORDER BY upp.Id DESC
) upp
CROSS APPLY (
    SELECT COALESCE(
        CASE
            WHEN mp.MpTxId IS NOT NULL
                 AND EXISTS (
                     SELECT 1 FROM CMS.Transactions t
                     WHERE t.Id = mp.MpTxId AND t.IsDeleted = 0 AND t.PaymentStatus = 0
                 )
            THEN mp.MpTxId
        END,
        upp.UppTxId,
        (
            SELECT TOP (1) t.Id
            FROM CMS.Transactions t
            WHERE t.IsDeleted = 0 AND t.PaymentStatus = 0
              AND mp.ReferenceNumber IS NOT NULL
              AND t.RefId = mp.ReferenceNumber
            ORDER BY t.Id DESC
        )
    ) AS TxId
) tx
WHERE tx.TxId IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM CMS.UserWalletHistories h
      INNER JOIN CMS.UserWallets w ON w.Id = h.WalletId AND w.IsDeleted = 0
      WHERE w.UserId = f.UserId
        AND h.IsDeleted = 0
        AND h.RefrenceId = tx.TxId
  );

SELECT TOP (50)
    u.Id AS UserId,
    u.Mobile,
    u.PackagePurchaseMethod AS UserMethod,
    m.PurchaseMethod AS ClubMethod,
    (SELECT COUNT(*) FROM CMS.UserPackagePurchases upp
     WHERE upp.UserId = u.Id AND upp.IsDeleted = 0 AND upp.PurchaseMethod = 3) AS UppManualCnt
FROM CMS.Users u
INNER JOIN @FixUsers f ON f.UserId = u.Id
LEFT JOIN CMS.ClubMemberships m ON m.UserId = u.Id AND m.IsDeleted = 0
WHERE u.IsDeleted = 0
ORDER BY u.Id;

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