Subversion Repositories SmartDukaan

-- Migration: Add running_balance column to userwallethistory
-- Purpose: Store wallet balance after each transaction for instant ledger display
-- Date: 2026-05-10

-- Step 1: Add the column
ALTER TABLE transaction.userwallethistory
    ADD COLUMN running_balance BIGINT NOT NULL DEFAULT 0;

-- Step 2: Backfill running_balance for all existing rows
-- Computes cumulative sum of amount per wallet_id ordered by id (chronological)
-- Uses session variables (MySQL 5.7 compatible, no window functions)
SET @running := 0;
SET @prev_wallet := 0;

UPDATE transaction.userwallethistory uwh
JOIN (
    SELECT id,
           @running := IF(@prev_wallet = wallet_id, @running + amount, amount) AS cumulative_balance,
           @prev_wallet := wallet_id
    FROM transaction.userwallethistory
    ORDER BY wallet_id, id
) calc ON uwh.id = calc.id
SET uwh.running_balance = calc.cumulative_balance;

-- Step 3: Add index for efficient lookups by wallet_id + timestamp (used by wallet statement)
-- Not strictly required since the existing query already works, but helps if you ever
-- query running_balance directly
-- ALTER TABLE transaction.userwallethistory ADD INDEX idx_uwh_wallet_timestamp (wallet_id, timestamp);