-- Create stock_suggestions table
CREATE TABLE IF NOT EXISTS stock_suggestions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    symbol VARCHAR(10) NOT NULL,
    name VARCHAR(255) NOT NULL,
    reason TEXT NOT NULL,
    target_price DECIMAL(10,2) NOT NULL,
    current_price DECIMAL(10,2) NOT NULL,
    risk_level ENUM('low', 'medium', 'high') DEFAULT 'medium',
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_symbol (symbol),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- Create user_adoptions table to track which users adopted suggestions
CREATE TABLE IF NOT EXISTS user_adoptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    suggestion_id INT NOT NULL,
    adopted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (suggestion_id) REFERENCES stock_suggestions(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_suggestion (user_id, suggestion_id),
    INDEX idx_user_id (user_id),
    INDEX idx_suggestion_id (suggestion_id)
);

-- Insert some sample stock suggestions
INSERT INTO stock_suggestions (symbol, name, reason, target_price, current_price, risk_level) VALUES
('AAPL', 'Apple Inc.', 'Strong fundamentals with continued innovation in services and hardware. iPhone sales remain robust.', 175.00, 150.00, 'low'),
('TSLA', 'Tesla Inc.', 'Leading electric vehicle market with expanding global presence and energy storage solutions.', 250.00, 200.00, 'medium'),
('MSFT', 'Microsoft Corporation', 'Cloud computing leader with strong enterprise software business and AI integration.', 400.00, 350.00, 'low'),
('GOOGL', 'Alphabet Inc.', 'Dominant in search and digital advertising with growing cloud and AI capabilities.', 150.00, 130.00, 'medium'),
('NVDA', 'NVIDIA Corporation', 'AI and gaming chip leader with strong growth in data center and automotive markets.', 500.00, 450.00, 'high');
