-- =======================
-- 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)
);

-- =======================
-- 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)
);
