-- Migration: Add trading history and AI learning tables
-- Date: 2024-01-23
-- Description: Adds position_records, learning_sessions, and learning_data_usage tables for trading history tracking and AI learning features

-- Position records table - stores complete trading position history
CREATE TABLE IF NOT EXISTS position_records (
    id TEXT PRIMARY KEY,
    trader_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    symbol TEXT NOT NULL,
    side TEXT NOT NULL CHECK(side IN ('LONG', 'SHORT')),
    
    -- Open position info
    open_time DATETIME NOT NULL,
    open_price REAL NOT NULL,
    open_decision_id TEXT,
    
    -- Close position info
    close_time DATETIME,
    close_price REAL,
    close_decision_id TEXT,
    
    -- Position details
    position_size REAL NOT NULL,
    leverage INTEGER NOT NULL,
    
    -- PnL info
    realized_pnl REAL,
    realized_pnl_pct REAL,
    unrealized_pnl REAL,
    
    -- Status
    status TEXT NOT NULL CHECK(status IN ('OPEN', 'CLOSED')),
    
    -- Learning markers
    marked_for_learning INTEGER DEFAULT 0,
    learning_notes TEXT DEFAULT '',
    
    -- Timestamps
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for position_records
CREATE INDEX IF NOT EXISTS idx_position_records_trader ON position_records(trader_id);
CREATE INDEX IF NOT EXISTS idx_position_records_user ON position_records(user_id);
CREATE INDEX IF NOT EXISTS idx_position_records_symbol ON position_records(symbol);
CREATE INDEX IF NOT EXISTS idx_position_records_open_time ON position_records(open_time);
CREATE INDEX IF NOT EXISTS idx_position_records_status ON position_records(status);
CREATE INDEX IF NOT EXISTS idx_position_records_marked_learning ON position_records(marked_for_learning);

-- Learning sessions table - stores AI learning session data
CREATE TABLE IF NOT EXISTS learning_sessions (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    trader_id TEXT,
    
    -- Session info
    title TEXT DEFAULT '',
    status TEXT NOT NULL CHECK(status IN ('ACTIVE', 'COMPLETED', 'ARCHIVED')),
    
    -- Data used
    position_ids TEXT DEFAULT '[]', -- JSON array of position IDs
    data_count INTEGER DEFAULT 0,
    
    -- Session content
    conversation TEXT DEFAULT '[]', -- JSON array of messages
    insights TEXT DEFAULT '[]', -- JSON array of key insights
    suggestions TEXT DEFAULT '[]', -- JSON array of optimization suggestions
    
    -- Timestamps
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Indexes for learning_sessions
CREATE INDEX IF NOT EXISTS idx_learning_sessions_user ON learning_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_learning_sessions_trader ON learning_sessions(trader_id);
CREATE INDEX IF NOT EXISTS idx_learning_sessions_status ON learning_sessions(status);
CREATE INDEX IF NOT EXISTS idx_learning_sessions_created_at ON learning_sessions(created_at);

-- Learning data usage table - tracks quota usage for AI learning
CREATE TABLE IF NOT EXISTS learning_data_usage (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT NOT NULL,
    session_id TEXT,
    
    -- Usage statistics
    data_points_used INTEGER DEFAULT 0,
    tokens_used INTEGER DEFAULT 0,
    
    -- Timestamp
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (session_id) REFERENCES learning_sessions(id) ON DELETE SET NULL
);

-- Indexes for learning_data_usage
CREATE INDEX IF NOT EXISTS idx_learning_data_usage_user ON learning_data_usage(user_id);
CREATE INDEX IF NOT EXISTS idx_learning_data_usage_session ON learning_data_usage(session_id);
CREATE INDEX IF NOT EXISTS idx_learning_data_usage_created_at ON learning_data_usage(created_at);

-- Triggers for automatic updated_at timestamp
CREATE TRIGGER IF NOT EXISTS update_position_records_updated_at
    AFTER UPDATE ON position_records
    BEGIN
        UPDATE position_records SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
    END;

CREATE TRIGGER IF NOT EXISTS update_learning_sessions_updated_at
    AFTER UPDATE ON learning_sessions
    BEGIN
        UPDATE learning_sessions SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
    END;

-- Verify migration completed
SELECT 'Migration completed: trading history and AI learning tables created' as status;
