invest-copilot/docker/init.sql

-- Invest Copilot database initialization
-- This script runs on first container start

-- Enable TimescaleDB extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Create the prices hypertable
CREATE TABLE IF NOT EXISTS prices (
    ticker VARCHAR(20) NOT NULL,
    date TIMESTAMPTZ NOT NULL,
    open DECIMAL(15,4),
    high DECIMAL(15,4),
    low DECIMAL(15,4),
    close DECIMAL(15,4),
    volume BIGINT,
    adjusted_close DECIMAL(15,4),
    PRIMARY KEY (ticker, date)
);

-- Convert to hypertable
SELECT create_hypertable('prices', 'date', if_not_exists => TRUE);

-- Enable columnstore for compression
ALTER TABLE prices SET (timescaledb.compress, timescaledb.compress_segmentby = 'ticker');

-- Create compression policy (compress data older than 90 days)
SELECT add_compression_policy('prices', INTERVAL '90 days');

-- Create stock profiles table
CREATE TABLE IF NOT EXISTS stock_profiles (
    ticker VARCHAR(20) PRIMARY KEY,
    name VARCHAR(500),
    exchange VARCHAR(20),
    sector VARCHAR(100),
    industry VARCHAR(200),
    market_cap BIGINT,
    description TEXT,
    website TEXT,
    ceo VARCHAR(255),
    employees INTEGER,
    pe_ratio DECIMAL(10,2),
    eps DECIMAL(10,4),
    dividend_yield DECIMAL(8,4),
    beta DECIMAL(6,4),
    last_updated TIMESTAMPTZ DEFAULT NOW()
);

-- Create users table
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255),
    name VARCHAR(255),
    avatar_url TEXT,
    timezone VARCHAR(50) DEFAULT 'UTC',
    settings JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create watchlists table
CREATE TABLE IF NOT EXISTS watchlists (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_watchlists_user ON watchlists(user_id);

-- Create watchlist items
CREATE TABLE IF NOT EXISTS watchlist_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    watchlist_id UUID NOT NULL REFERENCES watchlists(id) ON DELETE CASCADE,
    ticker VARCHAR(20) NOT NULL,
    type VARCHAR(20) NOT NULL CHECK (type IN ('stock', 'etf', 'index')),
    notes TEXT,
    price_at_addition DECIMAL(15,4),
    added_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_watchlist_items_unique ON watchlist_items(watchlist_id, ticker);

-- Create strategies
CREATE TABLE IF NOT EXISTS strategies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    type VARCHAR(20) NOT NULL CHECK (type IN ('technical', 'fundamental', 'hybrid')),
    conditions JSONB NOT NULL,
    backtest_results JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create watchlist-strategy associations
CREATE TABLE IF NOT EXISTS watchlist_strategies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    watchlist_id UUID NOT NULL REFERENCES watchlists(id) ON DELETE CASCADE,
    strategy_id UUID NOT NULL REFERENCES strategies(id) ON DELETE CASCADE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_watchlist_strategies_unique ON watchlist_strategies(watchlist_id, strategy_id);

-- Create screeners
CREATE TABLE IF NOT EXISTS screeners (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    conditions JSONB NOT NULL,
    results_count INTEGER DEFAULT 0,
    last_run_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create screener results
CREATE TABLE IF NOT EXISTS screener_results (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    screener_id UUID NOT NULL REFERENCES screeners(id) ON DELETE CASCADE,
    ticker VARCHAR(20) NOT NULL,
    match_score DECIMAL(5,4),
    ranked_position INTEGER,
    result_data JSONB,
    generated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_screener_results_screener ON screener_results(screener_id);

-- Create SEC filings
CREATE TABLE IF NOT EXISTS sec_filings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ticker VARCHAR(20) NOT NULL,
    cik VARCHAR(20),
    form_type VARCHAR(10) NOT NULL,
    filing_date DATE NOT NULL,
    report_date DATE,
    accession_number VARCHAR(50),
    url TEXT,
    content_summary TEXT,
    key_metrics JSONB,
    sentiment_score DECIMAL(5,4),
    tags TEXT[],
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_sec_filings_ticker ON sec_filings(ticker);
CREATE INDEX IF NOT EXISTS idx_sec_filings_form ON sec_filings(form_type);
CREATE INDEX IF NOT EXISTS idx_sec_filings_date ON sec_filings(filing_date);

-- Create insider trades
CREATE TABLE IF NOT EXISTS insider_trades (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ticker VARCHAR(20) NOT NULL,
    insider_name VARCHAR(500),
    insider_title VARCHAR(500),
    transaction_date DATE NOT NULL,
    transaction_type VARCHAR(10),
    shares INTEGER,
    price_per_share DECIMAL(10,4),
    total_value DECIMAL(15,4),
    shares_owned_after INTEGER,
    filing_date DATE,
    source_url TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_insider_trades_ticker ON insider_trades(ticker);
CREATE INDEX IF NOT EXISTS idx_insider_trades_type ON insider_trades(transaction_type);

-- Create alerts
CREATE TABLE IF NOT EXISTS alerts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    watchlist_id UUID NOT NULL REFERENCES watchlists(id) ON DELETE CASCADE,
    strategy_id UUID REFERENCES strategies(id),
    ticker VARCHAR(20),
    type VARCHAR(30) NOT NULL,
    trigger_type VARCHAR(50),
    message TEXT NOT NULL,
    severity VARCHAR(10) DEFAULT 'info' CHECK (severity IN ('info', 'warning', 'critical')),
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'resolved', 'dismissed')),
    triggered_at TIMESTAMPTZ DEFAULT NOW(),
    resolved_at TIMESTAMPTZ,
    metadata JSONB DEFAULT '{}'
);

CREATE INDEX IF NOT EXISTS idx_alerts_watchlist ON alerts(watchlist_id);
CREATE INDEX IF NOT EXISTS idx_alerts_status ON alerts(status);
CREATE INDEX IF NOT EXISTS idx_alerts_triggered ON alerts(triggered_at);

-- Create sector rotations
CREATE TABLE IF NOT EXISTS sector_rotations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    detection_date DATE NOT NULL,
    sector_ticker VARCHAR(20) NOT NULL,
    sector_name VARCHAR(100),
    rank_now INTEGER,
    rank_previous INTEGER,
    rank_change INTEGER,
    momentum_20d DECIMAL(8,4),
    momentum_50d DECIMAL(8,4),
    momentum_200d DECIMAL(8,4),
    relative_strength DECIMAL(8,4),
    rotation_signal VARCHAR(20),
    macro_context JSONB,
    analysis_summary TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_sector_rotations_date ON sector_rotations(detection_date);
CREATE INDEX IF NOT EXISTS idx_sector_rotations_signal ON sector_rotations(rotation_signal);

-- Create peer groups
CREATE TABLE IF NOT EXISTS peer_groups (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ticker VARCHAR(20) NOT NULL,
    peer_ticker VARCHAR(20) NOT NULL,
    similarity_score DECIMAL(5,4),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (ticker, peer_ticker)
);

CREATE INDEX IF NOT EXISTS idx_peer_groups_ticker ON peer_groups(ticker);

-- Create rotation alert preferences
CREATE TABLE IF NOT EXISTS rotation_alert_preferences (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    sectors TEXT[] NOT NULL DEFAULT '{}',
    min_rank_change INTEGER DEFAULT 2,
    email_enabled BOOLEAN DEFAULT TRUE,
    push_enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create continuous aggregates for common queries
CREATE MATERIALIZED VIEW IF NOT EXISTS prices_daily
WITH (timescaledb.continuous) AS
SELECT ticker,
       time_bucket('1 day', date) AS bucket,
       first(open, date) AS open,
       max(high) AS high,
       min(low) AS low,
       last(close, date) AS close,
       sum(volume) AS volume
FROM prices
GROUP BY ticker, bucket;

-- Create hourly aggregates for intraday analysis
CREATE MATERIALIZED VIEW IF NOT EXISTS prices_hourly
WITH (timescaledb.continuous) AS
SELECT ticker,
       time_bucket('1 hour', date) AS bucket,
       first(open, date) AS open,
       max(high) AS high,
       min(low) AS low,
       last(close, date) AS close,
       sum(volume) AS volume
FROM prices
GROUP BY ticker, bucket;

-- Seed data for sector ETFs (for rotation tracking)
INSERT INTO stock_profiles (ticker, name, exchange, sector, industry, market_cap, last_updated) VALUES
    ('XLK', 'Technology Select Sector SPDR Fund', 'NYSEARCA', 'Technology', 'ETF', 72000000000, NOW()),
    ('XLF', 'Financial Select Sector SPDR Fund', 'NYSEARCA', 'Financials', 'ETF', 45000000000, NOW()),
    ('XLE', 'Energy Select Sector SPDR Fund', 'NYSEARCA', 'Energy', 'ETF', 32000000000, NOW()),
    ('XLV', 'Health Care Select Sector SPDR Fund', 'NYSEARCA', 'Health Care', 'ETF', 38000000000, NOW()),
    ('XLI', 'Industrial Select Sector SPDR Fund', 'NYSEARCA', 'Industrials', 'ETF', 18000000000, NOW()),
    ('XLY', 'Consumer Discretionary Select Sector SPDR Fund', 'NYSEARCA', 'Consumer Discretionary', 'ETF', 20000000000, NOW()),
    ('XLP', 'Consumer Staples Select Sector SPDR Fund', 'NYSEARCA', 'Consumer Staples', 'ETF', 15000000000, NOW()),
    ('XLU', 'Utilities Select Sector SPDR Fund', 'NYSEARCA', 'Utilities', 'ETF', 16000000000, NOW()),
    ('XLRE', 'Real Estate Select Sector SPDR Fund', 'NYSEARCA', 'Real Estate', 'ETF', 5000000000, NOW()),
    ('XLB', 'Materials Select Sector SPDR Fund', 'NYSEARCA', 'Materials', 'ETF', 4000000000, NOW()),
    ('SPY', 'SPDR S&P 500 ETF Trust', 'NYSEARCA', 'Broad Market', 'ETF', 520000000000, NOW())
ON CONFLICT (ticker) DO NOTHING;