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