Subversion Repositories SmartDukaan

-- Migration: Add original_wallet_amount to order + order_wallet_adjustment table

-- Purpose: Make PI (Pending Indent) calculation idempotent for historical dates.

--          updatePriceDrop/changeQuantity mutate wallet_amount in place, causing

--          account statement discrepancies when queried for past periods.

-- Date: 2026-05-27

-- ============================================================================

-- STEP 1: Add original_wallet_amount column to order table

-- ============================================================================

ALTER TABLE transaction.`order` ADD COLUMN original_wallet_amount FLOAT DEFAULT NULL;

-- ============================================================================

-- STEP 2: Create order_wallet_adjustment table

-- ============================================================================

CREATE TABLE IF NOT EXISTS transaction.order_wallet_adjustment (

  id INT AUTO_INCREMENT PRIMARY KEY,

  order_id INT NOT NULL,

  delta FLOAT NOT NULL COMMENT 'Change to wallet_amount: positive = increased, negative = decreased',

  adjusted_at DATETIME NOT NULL,

  reason VARCHAR(255),

  KEY idx_owa_order_adjusted (order_id, adjusted_at)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ============================================================================

-- STEP 3: Backfill original_wallet_amount = wallet_amount for ALL orders

--         (correct for orders that never had price drops)

-- ============================================================================

UPDATE transaction.`order` SET original_wallet_amount = wallet_amount;

-- ============================================================================

-- STEP 4: Fix original_wallet_amount for orders that had price drops

--         original = wallet_amount + SUM(price_drop_wallet_entries)

--         because: wallet_entry.amount = quantity*(oldPrice-newPrice)

--                  price increase → negative entry → original < current

--                  price drop → positive entry → original > current

-- ============================================================================

UPDATE transaction.`order` o

JOIN (

  SELECT

    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(description, 'Order #', -1), ',', 1) AS UNSIGNED) as order_id,

    SUM(amount) as total_price_adj

  FROM transaction.userwallethistory

  WHERE reference_type = 'PURCHASE'

  AND (description LIKE 'Price increased%' OR description LIKE 'Price dropped%')

  GROUP BY order_id

) adj ON adj.order_id = o.id

SET o.original_wallet_amount = o.wallet_amount + adj.total_price_adj;

-- ============================================================================

-- STEP 5: Backfill order_wallet_adjustment from historical price drop entries

--         delta = -wallet_entry.amount (wallet_entry is priceDiff, delta is

--         the change to wallet_amount which is the opposite)

-- ============================================================================

INSERT INTO transaction.order_wallet_adjustment (order_id, delta, adjusted_at, reason)

SELECT

  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(description, 'Order #', -1), ',', 1) AS UNSIGNED) as order_id,

  -amount as delta,

  timestamp as adjusted_at,

  LEFT(description, 255) as reason

FROM transaction.userwallethistory

WHERE reference_type = 'PURCHASE'

AND (description LIKE 'Price increased%' OR description LIKE 'Price dropped%');