schema.sql

-- WInTheChat Supabase Schema

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    balance NUMERIC(12, 2) NOT NULL DEFAULT 10000.00,
    favorite_assets TEXT[] DEFAULT '{}',
    asset_metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Trade history
CREATE TABLE IF NOT EXISTS trade_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    ticker TEXT NOT NULL,
    action TEXT NOT NULL CHECK (action IN ('buy', 'sell')),
    amount NUMERIC(12, 2) NOT NULL,
    entry_price NUMERIC(12, 4) NOT NULL,
    exit_price NUMERIC(12, 4),
    pnl NUMERIC(12, 2),
    emotional_tag TEXT CHECK (emotional_tag IN ('revenge_trade', 'panic_sell', 'fomo_buy', 'disciplined', NULL)),
    news_context TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Behavioral metrics per user
CREATE TABLE IF NOT EXISTS behavioral_metrics (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID UNIQUE NOT NULL REFERENCES users(id),
    avg_loss_after_news_event NUMERIC(8, 2) DEFAULT 0,
    revenge_trade_probability NUMERIC(4, 2) DEFAULT 0,
    panic_sell_threshold NUMERIC(4, 2) DEFAULT 0,
    drawdown_threshold NUMERIC(4, 2) DEFAULT 0,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Content drafts for social media manager
CREATE TABLE IF NOT EXISTS content_drafts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ticker TEXT NOT NULL,
    event_summary TEXT,
    correlation_score NUMERIC(4, 2),
    correlation_justification TEXT,
    platform TEXT NOT NULL CHECK (platform IN ('x', 'linkedin')),
    persona TEXT NOT NULL CHECK (persona IN ('buffett', 'trump', 'burry')),
    content TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'declined')),
    analysis_context JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Daily podcast summaries
CREATE TABLE IF NOT EXISTS daily_podcasts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    podcast_date DATE NOT NULL DEFAULT CURRENT_DATE,
    script TEXT NOT NULL,
    tickers TEXT[] NOT NULL DEFAULT '{}',
    voice TEXT NOT NULL DEFAULT 'Kore',
    news_data JSONB DEFAULT '[]',
    event_count INTEGER NOT NULL DEFAULT 0,
    duration_estimate TEXT,
    audio_base64 TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Indexes
CREATE INDEX idx_trade_history_user_id ON trade_history(user_id);
CREATE INDEX idx_trade_history_ticker ON trade_history(ticker);
CREATE INDEX idx_content_drafts_status ON content_drafts(status);
CREATE INDEX idx_content_drafts_persona ON content_drafts(persona);
CREATE INDEX idx_users_favorite_assets ON users USING GIN(favorite_assets);
CREATE UNIQUE INDEX idx_daily_podcasts_user_date ON daily_podcasts(user_id, podcast_date);
CREATE INDEX idx_daily_podcasts_date ON daily_podcasts(podcast_date DESC);

-- User Profiles for Onboarding Data
CREATE TABLE IF NOT EXISTS user_profiles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID UNIQUE NOT NULL REFERENCES users(id),
    
    -- Risk Profile & Objectives
    risk_tolerance TEXT CHECK (risk_tolerance IN ('low', 'medium', 'high')),
    investment_objective TEXT CHECK (investment_objective IN ('growth', 'income', 'speculation', 'hedging')),
    investment_horizon TEXT, -- e.g. "Short (< 1 yr)", "Medium (1-5 yrs)", "Long (> 5 yrs)"
    max_acceptable_loss_pct NUMERIC(5, 2), -- Percentage e.g. 10.5 for 10.5%
    
    -- Experience
    investment_experience_years TEXT, -- e.g. "0-1", "1-3", "3-5", "5+"
    products_traded TEXT[], -- Array of strings e.g. ['stocks', 'crypto', 'options']
    trading_frequency_per_month TEXT, -- e.g. "0-5", "6-20", "21+"
    
    -- Financials & Employment
    annual_income_range TEXT,
    liquid_net_worth_usd TEXT,
    total_net_worth_usd TEXT,
    employment_status TEXT,
    
    -- Compliance/Other
    country_residence TEXT,
    state_residence TEXT,
    source_of_funds TEXT,

    -- ML-predicted suitability tier
    suitability_tier TEXT CHECK (suitability_tier IN ('Conservative', 'Balanced', 'Aggressive')),
    
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Index for faster lookups
CREATE INDEX idx_user_profiles_user_id ON user_profiles(user_id);