-- Create demo fund applications table
CREATE TABLE IF NOT EXISTS demo_fund_applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    requested_amount DECIMAL(10,2) NOT NULL,
    purpose ENUM('learning', 'strategy_testing', 'platform_evaluation', 'portfolio_simulation') NOT NULL,
    trading_experience ENUM('beginner', 'intermediate', 'advanced'),
    risk_tolerance ENUM('conservative', 'moderate', 'aggressive'),
    additional_notes TEXT,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    approved_amount DECIMAL(10,2) NULL,
    admin_notes TEXT,
    reviewed_by INT NULL,
    reviewed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- Insert sample demo fund applications for testing
INSERT INTO demo_fund_applications (user_id, requested_amount, purpose, trading_experience, risk_tolerance, additional_notes, status) VALUES
(1, 10000.00, 'learning', 'beginner', 'conservative', 'New to trading, want to learn with demo funds', 'pending'),
(2, 25000.00, 'strategy_testing', 'intermediate', 'moderate', 'Testing new trading strategies', 'pending'),
(3, 50000.00, 'platform_evaluation', 'advanced', 'aggressive', 'Evaluating platform for potential migration', 'approved');
