/*
  Backfill UserWalletHistory for 6 ManualPayment users (history only — NO wallet charge)

  Transaction IDs:
    172, 40182, 40184, 40201, 40264, 110556

  Wallet was already charged when ManualPayment ran; this script only inserts
  the missing UserWalletHistory row (same shape as CreateManualPayment).

  Defaults to ROLLBACK. Uncomment COMMIT after verifying 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 @TargetTx TABLE (TransactionId BIGINT PRIMARY KEY);

INSERT INTO @TargetTx (TransactionId)
VALUES
    (172),
    (40182),
    (40184),
    (40201),
    (40264),
    (110556);

------------------------------------------------------------
-- Preview
------------------------------------------------------------
SELECT
    tx.TransactionId,
    t.Amount AS TxAmount,
    t.PaymentDate,
    t.PaymentStatus,
    t.IsDeleted AS TxDeleted,
    COALESCE(mp.UserId, upp.UserId) AS UserId,
    mp.Id AS ManualPaymentId,
    upp.Id AS UserPackagePurchaseId,
    COALESCE(
        upp.PackageId,
        NULLIF(cm.LastPackageId, 0),
        NULLIF(cm.FirstPackageId, 0),
        @BasePackageId
    ) AS PackageId,
    w.Id AS WalletId,
    h.Id AS ExistingWalletHistoryId,
    CASE
        WHEN t.Id IS NULL THEN N'TX_NOT_FOUND'
        WHEN t.IsDeleted = 1 THEN N'TX_DELETED'
        WHEN t.PaymentStatus <> 0 THEN N'TX_NOT_SUCCESS'
        WHEN COALESCE(mp.UserId, upp.UserId) IS NULL THEN N'USER_NOT_RESOLVED'
        WHEN w.Id IS NULL THEN N'WALLET_MISSING'
        WHEN h.Id IS NOT NULL THEN N'ALREADY_HAS_HISTORY'
        ELSE N'WILL_CREATE_HISTORY'
    END AS PlanAction
FROM @TargetTx tx
LEFT JOIN CMS.Transactions t ON t.Id = tx.TransactionId
LEFT JOIN CMS.ManualPayments mp
    ON mp.TransactionId = tx.TransactionId AND mp.IsDeleted = 0
LEFT JOIN CMS.UserPackagePurchases upp
    ON upp.TransactionId = tx.TransactionId AND upp.IsDeleted = 0
LEFT JOIN CMS.ClubMemberships cm
    ON cm.UserId = COALESCE(mp.UserId, upp.UserId) AND cm.IsDeleted = 0
LEFT JOIN CMS.UserWallets w
    ON w.UserId = COALESCE(mp.UserId, upp.UserId) AND w.IsDeleted = 0
OUTER APPLY (
    SELECT TOP (1) h0.Id
    FROM CMS.UserWalletHistories h0
    WHERE h0.IsDeleted = 0
      AND h0.RefrenceId = tx.TransactionId
      AND (
          w.Id IS NULL
          OR h0.WalletId = w.Id
      )
) h
ORDER BY tx.TransactionId;

SELECT 'Will create WalletHistory' AS CheckName, COUNT(*) AS Cnt
FROM @TargetTx tx
INNER JOIN CMS.Transactions t ON t.Id = tx.TransactionId AND t.IsDeleted = 0 AND t.PaymentStatus = 0
OUTER APPLY (
    SELECT TOP (1) mp.UserId
    FROM CMS.ManualPayments mp
    WHERE mp.TransactionId = tx.TransactionId AND mp.IsDeleted = 0
) mp
OUTER APPLY (
    SELECT TOP (1) upp.UserId, upp.PackageId
    FROM CMS.UserPackagePurchases upp
    WHERE upp.TransactionId = tx.TransactionId AND upp.IsDeleted = 0
    ORDER BY upp.Id DESC
) upp
CROSS APPLY (
    SELECT COALESCE(mp.UserId, upp.UserId) AS UserId
) u
INNER JOIN CMS.UserWallets w ON w.UserId = u.UserId AND w.IsDeleted = 0
WHERE NOT EXISTS (
    SELECT 1
    FROM CMS.UserWalletHistories h
    WHERE h.WalletId = w.Id
      AND h.IsDeleted = 0
      AND h.RefrenceId = tx.TransactionId
);

------------------------------------------------------------
-- Insert missing history
------------------------------------------------------------
;WITH Resolved AS (
    SELECT
        tx.TransactionId AS TxId,
        t.Amount,
        COALESCE(t.PaymentDate, t.Created) AS HistoryAt,
        COALESCE(mp.UserId, upp.UserId) AS UserId,
        COALESCE(
            upp.PackageId,
            NULLIF(cm.LastPackageId, 0),
            NULLIF(cm.FirstPackageId, 0),
            @BasePackageId
        ) AS PackageId,
        w.Id AS WalletId,
        COALESCE(w.NetworkBalance, 0) AS NetworkBalance
    FROM @TargetTx tx
    INNER JOIN CMS.Transactions t
        ON t.Id = tx.TransactionId AND t.IsDeleted = 0 AND t.PaymentStatus = 0
    OUTER APPLY (
        SELECT TOP (1) mp0.UserId, mp0.Id AS ManualPaymentId
        FROM CMS.ManualPayments mp0
        WHERE mp0.TransactionId = tx.TransactionId AND mp0.IsDeleted = 0
    ) mp
    OUTER APPLY (
        SELECT TOP (1) upp0.UserId, upp0.PackageId
        FROM CMS.UserPackagePurchases upp0
        WHERE upp0.TransactionId = tx.TransactionId AND upp0.IsDeleted = 0
        ORDER BY upp0.Id DESC
    ) upp
    LEFT JOIN CMS.ClubMemberships cm
        ON cm.UserId = COALESCE(mp.UserId, upp.UserId) AND cm.IsDeleted = 0
    INNER JOIN CMS.UserWallets w
        ON w.UserId = COALESCE(mp.UserId, upp.UserId) AND w.IsDeleted = 0
    WHERE COALESCE(mp.UserId, upp.UserId) IS NOT NULL
      AND NOT EXISTS (
          SELECT 1
          FROM CMS.UserWalletHistories h
          WHERE h.WalletId = w.Id
            AND h.IsDeleted = 0
            AND h.RefrenceId = tx.TransactionId
      )
)
INSERT INTO CMS.UserWalletHistories
(
    WalletId, CurrentBalance, ChangeValue, CurrentNetworkBalance, ChangeNerworkValue,
    CurrentDiscountBalance, ChangeDiscountValue, IsIncrease, RefrenceId, PackageId,
    Created, CreatedBy, LastModified, LastModifiedBy, IsDeleted
)
SELECT
    r.WalletId,
    0,
    r.Amount,
    r.NetworkBalance,
    0,
    0,
    CAST(ROUND(r.Amount * COALESCE(p.DiscountMultiplier, 2.0), 0) AS BIGINT),
    1,
    r.TxId,
    r.PackageId,
    r.HistoryAt,
    NULL,
    r.HistoryAt,
    NULL,
    0
FROM Resolved r
LEFT JOIN CMS.Packages p ON p.Id = r.PackageId AND p.IsDeleted = 0
WHERE r.PackageId IS NOT NULL
  AND r.Amount > 0;

PRINT CONCAT('UserWalletHistory inserted: ', @@ROWCOUNT);

------------------------------------------------------------
-- Verify
------------------------------------------------------------
SELECT
    tx.TransactionId,
    h.Id AS WalletHistoryId,
    h.ChangeValue,
    h.ChangeDiscountValue,
    h.RefrenceId,
    h.Created AS HistoryCreated,
    w.UserId
FROM @TargetTx tx
LEFT JOIN CMS.UserWalletHistories h
    ON h.RefrenceId = tx.TransactionId AND h.IsDeleted = 0
LEFT JOIN CMS.UserWallets w
    ON w.Id = h.WalletId AND w.IsDeleted = 0
ORDER BY tx.TransactionId;

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