invest-copilot/init.sql

-- TimescaleDB initialization script
-- This script creates the necessary extensions and hypertables

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

-- Create stock profiles table
CREATE TABLE IF NOT EXISTS stock_profiles (
    ticker VARCHAR(20) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    exchange VARCHAR(50),
    sector VARCHAR(100),
    industry VARCHAR(100),
    market_cap NUMERIC(20, 2),
    description TEXT,
    website VARCHAR(500),
    ceo VARCHAR(255),
    employees INTEGER,
    pe_ratio NUMERIC(10, 2),
    eps NUMERIC(10, 2),
    dividend_yield NUMERIC(8, 4),
    beta NUMERIC(8, 4),
    last_updated TIMESTAMP WITH TIME ZONE 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) NOT NULL,
    name VARCHAR(255),
    timezone VARCHAR(50) DEFAULT 'UTC',
    settings JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create prices table (hypertable for TimescaleDB)
CREATE TABLE IF NOT EXISTS prices (
    ticker VARCHAR(20) NOT NULL,
    date DATE NOT NULL,
    open NUMERIC(12, 2),
    high NUMERIC(12, 2),
    low NUMERIC(12, 2),
    close NUMERIC(12, 2),
    volume BIGINT,
    adjusted_close NUMERIC(12, 2),
    PRIMARY KEY (ticker, date)
);

SELECT create_hypertable('prices', 'date', if_not_exists => TRUE);

-- 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 TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create watchlist items table
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) DEFAULT 'stock',
    custom_notes TEXT,
    added_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    price_at_addition NUMERIC(12, 2),
    UNIQUE (watchlist_id, ticker)
);

-- Create strategies table
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(50) NOT NULL,
    conditions JSONB NOT NULL,
    backtest_results JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create screeners table
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,
    filters JSONB,
    last_run_at TIMESTAMP WITH TIME ZONE,
    results_count INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create screener runs table
CREATE TABLE IF NOT EXISTS screener_runs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    screener_id UUID NOT NULL REFERENCES screeners(id) ON DELETE CASCADE,
    results JSONB NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create alerts table
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,
    type VARCHAR(50) NOT NULL,
    trigger_type VARCHAR(50),
    message TEXT NOT NULL,
    severity VARCHAR(20) DEFAULT 'info',
    status VARCHAR(20) DEFAULT 'active',
    ticker VARCHAR(20),
    triggered_at TIMESTAMP WITH TIME ZONE,
    resolved_at TIMESTAMP WITH TIME ZONE,
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create sector rotations table
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) NOT NULL,
    rank_now INTEGER,
    rank_previous INTEGER,
    rank_change INTEGER,
    momentum_20d NUMERIC(10, 4),
    momentum_50d NUMERIC(10, 4),
    momentum_200d NUMERIC(10, 4),
    relative_strength NUMERIC(10, 4),
    rotation_signal VARCHAR(20),
    macro_context TEXT,
    analysis_summary TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create SEC filings table
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(20),
    filing_date DATE,
    report_date DATE,
    accession_number VARCHAR(50),
    url TEXT,
    content_summary TEXT,
    key_metrics JSONB,
    sentiment_score NUMERIC(5, 2),
    tags TEXT[],
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create insider trades table
CREATE TABLE IF NOT EXISTS insider_trades (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ticker VARCHAR(20) NOT NULL,
    insider_name VARCHAR(255) NOT NULL,
    insider_title VARCHAR(255),
    transaction_date DATE NOT NULL,
    transaction_type VARCHAR(20),
    shares INTEGER,
    price_per_share NUMERIC(12, 2),
    total_value NUMERIC(15, 2),
    shares_owned_after INTEGER,
    filing_date DATE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create peer groups table
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 NUMERIC(5, 2),
    UNIQUE (ticker, peer_ticker)
);

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_prices_ticker_date ON prices (ticker, date DESC);
CREATE INDEX IF NOT EXISTS idx_watchlists_user_id ON watchlists (user_id);
CREATE INDEX IF NOT EXISTS idx_strategies_user_id ON strategies (user_id);
CREATE INDEX IF NOT EXISTS idx_screeners_user_id ON screeners (user_id);
CREATE INDEX IF NOT EXISTS idx_alerts_watchlist_id ON alerts (watchlist_id);
CREATE INDEX IF NOT EXISTS idx_sec_filings_ticker ON sec_filings (ticker);
CREATE INDEX IF NOT EXISTS idx_insider_trades_ticker ON insider_trades (ticker);
CREATE INDEX IF NOT EXISTS idx_sector_rotations_date ON sector_rotations (detection_date DESC);

-- Financials table (income statement / balance sheet / cash flow snapshots)
CREATE TABLE IF NOT EXISTS financials (
    ticker             VARCHAR(20)     NOT NULL,
    filing_date        DATE            NOT NULL,
    period             VARCHAR(10)     NOT NULL,
    period_end         DATE,
    revenue            NUMERIC(18,2),
    cost_of_revenue    NUMERIC(18,2),
    gross_profit       NUMERIC(18,2),
    operating_expense  NUMERIC(18,2),
    operating_income   NUMERIC(18,2),
    net_income         NUMERIC(18,2),
    eps_basic          NUMERIC(12,4),
    eps_diluted        NUMERIC(12,4),
    total_assets       NUMERIC(20,2),
    total_liabilities  NUMERIC(20,2),
    total_equity       NUMERIC(20,2),
    operating_cashflow NUMERIC(18,2),
    free_cashflow      NUMERIC(18,2),
    debt_to_equity     NUMERIC(8,4),
    roe                NUMERIC(8,4),
    roa                NUMERIC(8,4),
    created_at         TIMESTAMPTZ     DEFAULT now(),
    PRIMARY KEY (ticker, filing_date, period)
);
CREATE INDEX IF NOT EXISTS idx_financials_ticker ON financials (ticker);
CREATE INDEX IF NOT EXISTS idx_financials_period ON financials (period);