-- Investments Table (Stocks, Crypto, Real Estate, Fixed Income, Commodities)
CREATE TABLE IF NOT EXISTS investments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('stock', 'crypto', 'real_estate', 'fixed_income', 'commodity') NOT NULL,
    symbol VARCHAR(50) NOT NULL, -- e.g., AAPL, BTC, property_id
    name VARCHAR(255) NOT NULL,
    quantity DECIMAL(20, 8) NOT NULL,
    purchase_price DECIMAL(20, 2) NOT NULL,
    current_value DECIMAL(20, 2) NOT NULL, -- Cached value, updated periodically
    purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('active', 'sold', 'leased') DEFAULT 'active',
    meta_data JSON NULL, -- Store extra details like real estate location/lease info
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Wallets Table (Crypto)
CREATE TABLE IF NOT EXISTS wallets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    currency VARCHAR(10) NOT NULL, -- e.g., BTC, ETH
    address VARCHAR(255) NOT NULL,
    private_key VARCHAR(512) NOT NULL, -- Encrypted
    balance DECIMAL(20, 8) DEFAULT 0.00000000,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Transactions Table (History)
CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('deposit', 'withdrawal', 'buy_asset', 'sell_asset', 'lease_income', 'auto_invest_return', 'hourly_growth') NOT NULL,
    amount DECIMAL(20, 2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'USD',
    description TEXT,
    reference_id VARCHAR(100) NULL, -- e.g., investment_id or tx_hash
    status ENUM('pending', 'completed', 'failed') DEFAULT 'completed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);


-- Insert default Admin Wallet Key (Mock for demo)
-- Ideally this would be set via ENV or a secure setup script
