-- symbols table
-- Stores metadata for each trading symbol
CREATE TABLE symbols (
    id SERIAL PRIMARY KEY,
    symbol VARCHAR(50) NOT NULL UNIQUE,
    base_asset VARCHAR(50),
    quote_asset VARCHAR(50),
    exchange VARCHAR(50),
    instrument_type VARCHAR(20) CHECK (instrument_type IN ('crypto', 'stock', 'forex', 'commodity', 'index')),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_symbols_exchange ON symbols(exchange);
CREATE INDEX idx_symbols_type ON symbols(instrument_type);

-- candles table
-- Stores multi-timeframe OHLCV data for each symbol
CREATE TABLE candles (
    id BIGSERIAL PRIMARY KEY,
    symbol_id INT NOT NULL REFERENCES symbols(id) ON DELETE CASCADE,
    timeframe ENUM('15m', '30m', '1h', '1D', '1W', '1M') NOT NULL DEFAULT '1h',
    open_time TIMESTAMPTZ NOT NULL,
    close_time TIMESTAMPTZ NOT NULL,
    open NUMERIC(18,8) NOT NULL,
    high NUMERIC(18,8) NOT NULL,
    low NUMERIC(18,8) NOT NULL,
    close NUMERIC(18,8) NOT NULL,
    volume NUMERIC(20,8),
    trades_count INT,
    quote_volume NUMERIC(20,8),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_candles_symbol_time_timeframe ON candles(symbol_id, open_time, timeframe);
CREATE INDEX idx_candles_open_time_timeframe ON candles(open_time, timeframe);

-- live_prices table
-- Stores the latest live market prices per symbol
CREATE TABLE live_prices (
    symbol_id INT PRIMARY KEY REFERENCES symbols(id) ON DELETE CASCADE,
    price NUMERIC(18,8) NOT NULL,
    bid NUMERIC(18,8),
    ask NUMERIC(18,8),
    bid_size NUMERIC(18,8),
    ask_size NUMERIC(18,8),
    last_updated TIMESTAMPTZ DEFAULT NOW(),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_live_prices_price ON live_prices(price);