stonks-oracle/infra/migrations/003_trends_recommendations_orders.sql

-- Stonks Oracle - Trends, Recommendations, Orders Schema

-- ============================================================
-- Trend Windows
-- ============================================================

CREATE TABLE trend_windows (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    entity_type VARCHAR(50) NOT NULL DEFAULT 'company',
    entity_id VARCHAR(100) NOT NULL,
    "window" VARCHAR(20) NOT NULL,
    trend_direction VARCHAR(20) NOT NULL DEFAULT 'neutral',
    trend_strength FLOAT DEFAULT 0.5,
    confidence FLOAT DEFAULT 0.5,
    top_supporting_evidence JSONB DEFAULT '[]',
    top_opposing_evidence JSONB DEFAULT '[]',
    dominant_catalysts JSONB DEFAULT '[]',
    material_risks JSONB DEFAULT '[]',
    contradiction_score FLOAT DEFAULT 0.0,
    market_context JSONB DEFAULT '{}',
    generated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_trends_entity ON trend_windows(entity_type, entity_id, "window");
CREATE INDEX idx_trends_generated ON trend_windows(generated_at DESC);

-- ============================================================
-- Recommendations
-- ============================================================

CREATE TABLE recommendations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ticker VARCHAR(20) NOT NULL,
    company_id UUID REFERENCES companies(id),
    action VARCHAR(20) NOT NULL DEFAULT 'watch',
    mode VARCHAR(30) NOT NULL DEFAULT 'informational',
    confidence FLOAT DEFAULT 0.5,
    time_horizon VARCHAR(50),
    thesis TEXT,
    invalidation_conditions JSONB DEFAULT '[]',
    portfolio_pct FLOAT DEFAULT 0.02,
    max_loss_pct FLOAT DEFAULT 0.005,
    model_version VARCHAR(100),
    generated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_recommendations_ticker ON recommendations(ticker, generated_at DESC);
CREATE INDEX idx_recommendations_mode ON recommendations(mode);

CREATE TABLE recommendation_evidence (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    recommendation_id UUID NOT NULL REFERENCES recommendations(id) ON DELETE CASCADE,
    document_id UUID REFERENCES documents(id),
    intelligence_id UUID REFERENCES document_intelligence(id),
    evidence_type VARCHAR(50) DEFAULT 'supporting',
    weight FLOAT DEFAULT 1.0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_rec_evidence_rec ON recommendation_evidence(recommendation_id);

-- ============================================================
-- Risk Evaluations
-- ============================================================

CREATE TABLE risk_evaluations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    recommendation_id UUID NOT NULL REFERENCES recommendations(id),
    eligible BOOLEAN NOT NULL DEFAULT FALSE,
    allowed_mode VARCHAR(30) DEFAULT 'informational',
    rejection_reasons JSONB DEFAULT '[]',
    risk_checks JSONB DEFAULT '{}',
    evaluated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_risk_eval_rec ON risk_evaluations(recommendation_id);

-- ============================================================
-- Broker Accounts and Orders
-- ============================================================

CREATE TABLE broker_accounts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    provider VARCHAR(100) NOT NULL,
    account_id VARCHAR(200) NOT NULL,
    mode VARCHAR(20) NOT NULL DEFAULT 'paper',
    config JSONB DEFAULT '{}',
    active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    recommendation_id UUID REFERENCES recommendations(id),
    broker_account_id UUID REFERENCES broker_accounts(id),
    ticker VARCHAR(20) NOT NULL,
    side VARCHAR(10) NOT NULL,
    order_type VARCHAR(20) NOT NULL DEFAULT 'market',
    quantity NUMERIC NOT NULL,
    limit_price NUMERIC,
    stop_price NUMERIC,
    status VARCHAR(30) NOT NULL DEFAULT 'pending',
    idempotency_key VARCHAR(200) NOT NULL UNIQUE,
    broker_order_id VARCHAR(200),
    decision_trace JSONB DEFAULT '{}',
    submitted_at TIMESTAMPTZ,
    acknowledged_at TIMESTAMPTZ,
    filled_at TIMESTAMPTZ,
    cancelled_at TIMESTAMPTZ,
    rejected_at TIMESTAMPTZ,
    rejection_reason TEXT,
    fill_price NUMERIC,
    fill_quantity NUMERIC,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_orders_ticker ON orders(ticker, created_at DESC);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_idempotency ON orders(idempotency_key);

CREATE TABLE order_events (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    event_type VARCHAR(50) NOT NULL,
    data JSONB DEFAULT '{}',
    broker_timestamp TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_order_events_order ON order_events(order_id);

-- ============================================================
-- Positions
-- ============================================================

CREATE TABLE positions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    broker_account_id UUID REFERENCES broker_accounts(id),
    ticker VARCHAR(20) NOT NULL,
    quantity NUMERIC NOT NULL DEFAULT 0,
    avg_entry_price NUMERIC,
    current_price NUMERIC,
    unrealized_pnl NUMERIC,
    realized_pnl NUMERIC DEFAULT 0,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_positions_ticker ON positions(ticker);

-- ============================================================
-- Audit Events
-- ============================================================

CREATE TABLE audit_events (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    event_type VARCHAR(100) NOT NULL,
    entity_type VARCHAR(100),
    entity_id UUID,
    actor VARCHAR(200) DEFAULT 'system',
    data JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_audit_events_type ON audit_events(event_type, created_at DESC);
CREATE INDEX idx_audit_events_entity ON audit_events(entity_type, entity_id);