-- =======================
-- 1. RATES: Current buy/sell rates from each exchange
-- =======================
CREATE TABLE IF NOT EXISTS rates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    exchange VARCHAR(100) NOT NULL,
    price_buy DECIMAL(18,6) NOT NULL,
    price_sell DECIMAL(18,6) NOT NULL,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_exchange_time (exchange, timestamp)
);

-- =======================
-- 2. TRADES: Every executed trade (manual or simulated)
-- =======================
CREATE TABLE IF NOT EXISTS trades (
    id INT AUTO_INCREMENT PRIMARY KEY,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    exchange VARCHAR(100) NOT NULL,
    buy DECIMAL(18,6) NOT NULL,
    sell DECIMAL(18,6) NOT NULL,
    profit DECIMAL(18,6) NOT NULL,
    roi DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
    trade_type ENUM('simulation','live') NOT NULL DEFAULT 'simulation',
    ai_prediction JSON DEFAULT (JSON_OBJECT()), -- empty JSON object as default
    INDEX idx_timestamp (timestamp),
    INDEX idx_exchange (exchange)
);

-- =======================
-- 3. PROFITS: Periodic profit tracking
-- =======================
CREATE TABLE IF NOT EXISTS profits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    buyAmount DECIMAL(18,6) DEFAULT 100,
    bestBuyEx VARCHAR(100),
    bestBuyRate DECIMAL(18,6),
    bestSellEx VARCHAR(100),
    bestSellRate DECIMAL(18,6),
    totalProfit DECIMAL(18,6),
    roi DECIMAL(10,4) DEFAULT 0.0000,
    INDEX idx_timestamp (timestamp)
);

-- =======================
-- 4. EXCHANGES: Exchange list, status, and API keys
-- =======================
CREATE TABLE IF NOT EXISTS exchanges (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    enabled TINYINT(1) DEFAULT 1,
    api_key TEXT,
    api_secret TEXT,
    passphrase TEXT,
    extra_config JSON DEFAULT (JSON_OBJECT()),
    INDEX idx_enabled (enabled)
);

-- =======================
-- 5. ADMINS: Admin accounts & API tokens
-- =======================
CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','superadmin','viewer') DEFAULT 'admin',
    api_token VARCHAR(255),
    last_login TIMESTAMP NULL,
    ip_whitelist TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_email (email)
);

-- =======================
-- 6. SETTINGS: Arbitrary system settings
-- =======================
CREATE TABLE IF NOT EXISTS settings (
    k VARCHAR(100) PRIMARY KEY,
    v TEXT NOT NULL
);

-- Insert default settings
REPLACE INTO settings (k,v) VALUES
    ('profitThreshold','5000'),
    ('emailAlerts','1'),
    ('defaultTradeAmount','100'),
    ('allowedExchanges','binance,kucoin,gateio,bybit,bpax,luno'),
    ('aiPredictionsEnabled','1'),
    ('ipWhitelistEnabled','0');

-- =======================
-- 7. FETCH_ERRORS: Log failed API calls
-- =======================
CREATE TABLE IF NOT EXISTS fetch_errors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    exchange VARCHAR(100) NOT NULL,
    endpoint TEXT,
    http_code INT,
    error_msg TEXT,
    INDEX idx_exchange_time (exchange, timestamp)
);