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