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);