-- ============================================================
-- Migration 006: Trade Source Normalization
-- Date: 2026-02-14
-- Purpose: Separate trade source semantics from trade side.
-- ============================================================

ALTER TABLE trade_history
ADD COLUMN IF NOT EXISTS source text DEFAULT 'BOT';

-- Normalize legacy rows where side was overloaded with MANUAL.
UPDATE trade_history
SET side = 'BUY'
WHERE upper(side) = 'MANUAL';

-- Backfill source for historical/manual records where possible.
UPDATE trade_history
SET source = 'MANUAL'
WHERE source IS NULL
  OR upper(source) NOT IN ('BOT', 'MANUAL');

ALTER TABLE trade_history
ALTER COLUMN source SET NOT NULL;

CREATE INDEX IF NOT EXISTS idx_trade_history_source ON trade_history(source);