-- ────────────────────────────────────────────────────────────
-- 005: Add trade_id for full trade cycle tracing
-- Links entry order → exit order → trade_history record
-- ────────────────────────────────────────────────────────────

-- Add trade_id to orders table (links BUY and SELL of same trade)
ALTER TABLE orders
ADD COLUMN IF NOT EXISTS trade_id text;

-- Add action column to orders (ENTRY or EXIT)
ALTER TABLE orders
ADD COLUMN IF NOT EXISTS action text;

-- Add trade_id to trade_history (links to the orders)
ALTER TABLE trade_history
ADD COLUMN IF NOT EXISTS trade_id text;

-- Index for fast lookups by trade_id
CREATE INDEX IF NOT EXISTS idx_orders_trade_id ON orders(trade_id);
CREATE INDEX IF NOT EXISTS idx_trade_history_trade_id ON trade_history(trade_id);

COMMENT ON COLUMN orders.trade_id IS 'Unique identifier linking entry and exit orders of the same trade cycle (e.g., TRD-1707000000-abc123)';
COMMENT ON COLUMN orders.action IS 'Order action type: ENTRY (opening) or EXIT (closing)';
COMMENT ON COLUMN trade_history.trade_id IS 'Links this trade record to its entry/exit orders in the orders table';
