init-db.sql

-- QuantStox Database Initialization
-- This script creates the required tables and indexes

-- Main stock prices table
CREATE TABLE IF NOT EXISTS stock_prices (
    id SERIAL PRIMARY KEY,
    symbol VARCHAR(10) NOT NULL,
    date DATE NOT NULL,
    open FLOAT,
    high FLOAT,
    low FLOAT,
    close FLOAT,
    volume BIGINT,
    daily_return FLOAT,
    volatility FLOAT,
    processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(symbol, date)
);

-- Index for fast lookups by symbol and date
CREATE INDEX IF NOT EXISTS idx_symbol_date ON stock_prices(symbol, date);

-- Index for time-series queries
CREATE INDEX IF NOT EXISTS idx_date ON stock_prices(date DESC);

-- Grant permissions
GRANT ALL PRIVILEGES ON TABLE stock_prices TO quantstox;
GRANT USAGE, SELECT ON SEQUENCE stock_prices_id_seq TO quantstox;

-- API Keys table for authentication
CREATE TABLE IF NOT EXISTS api_keys (
    id SERIAL PRIMARY KEY,
    key_hash VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    tier VARCHAR(50) NOT NULL DEFAULT 'standard', -- 'admin', 'standard', 'free'
    daily_limit INTEGER, -- NULL = unlimited
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_used TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- API usage tracking for rate limiting
CREATE TABLE IF NOT EXISTS api_usage (
    id SERIAL PRIMARY KEY,
    api_key_id INTEGER NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
    endpoint VARCHAR(255),
    date DATE DEFAULT CURRENT_DATE,
    request_count INTEGER DEFAULT 1,
    UNIQUE(api_key_id, date, endpoint)
);

-- Index for fast lookups
CREATE INDEX IF NOT EXISTS idx_api_keys_hash ON api_keys(key_hash);
CREATE INDEX IF NOT EXISTS idx_api_usage_key_date ON api_usage(api_key_id, date);

-- Grant permissions
GRANT ALL PRIVILEGES ON TABLE api_keys TO quantstox;
GRANT ALL PRIVILEGES ON TABLE api_usage TO quantstox;
GRANT USAGE, SELECT ON SEQUENCE api_keys_id_seq TO quantstox;
GRANT USAGE, SELECT ON SEQUENCE api_usage_id_seq TO quantstox;