-- Orders table (for all trades)
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    stock_id INT NOT NULL,
    type ENUM('buy','sell') NOT NULL,
    invest_in ENUM('shares','dollars') NOT NULL,
    order_type ENUM('market','limit') NOT NULL,
    quantity DECIMAL(16,4) DEFAULT NULL,
    amount DECIMAL(16,2) DEFAULT NULL,
    price DECIMAL(16,4) NOT NULL,
    total_cost DECIMAL(16,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'filled',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL
);

-- Portfolios table
CREATE TABLE portfolios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    stock_id INT NOT NULL,
    total_shares DECIMAL(16,4) NOT NULL DEFAULT 0,
    avg_price DECIMAL(16,4) NOT NULL DEFAULT 0,
    total_invested DECIMAL(16,2) NOT NULL DEFAULT 0,
    total_profit DECIMAL(16,2) NOT NULL DEFAULT 0,
    today_profit DECIMAL(16,2) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL
);