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)

UPDATE transaction.userwallethistory uwh

JOIN (

    SELECT id,

           SUM(amount) OVER (PARTITION BY wallet_id ORDER BY id) AS cumulative_balance

    FROM transaction.userwallethistory

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