supabase_schema_all.sql

-- ============================================================
-- Claude Crypto Bot — COMPLETE Schema (all 3 combined)
-- Run this in: Supabase Dashboard → SQL Editor → New query
-- ============================================================

-- ===================== CORE TABLES =====================

-- Trades: every analysis cycle's result
CREATE TABLE IF NOT EXISTS trades (
    id              UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    action          TEXT        NOT NULL,
    amount_usd      NUMERIC(10,2) DEFAULT 0,
    btc_qty         NUMERIC(16,8) DEFAULT 0,
    price           NUMERIC(12,2),
    decision        JSONB,
    market          JSONB,
    success         BOOLEAN   DEFAULT FALSE,
    error           TEXT,
    outcome         TEXT,
    price_after_4h  NUMERIC(12,2),
    lesson_generated BOOLEAN  DEFAULT FALSE
);

-- Portfolio snapshots taken at the start of each cycle
CREATE TABLE IF NOT EXISTS portfolio_snapshots (
    id          UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    usdt        NUMERIC(10,2),
    btc         NUMERIC(16,8),
    price       NUMERIC(12,2),
    total_usd   NUMERIC(10,2)
);

-- Lessons learned from mistakes and weekly reviews
CREATE TABLE IF NOT EXISTS lessons (
    id          UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    lesson      TEXT        NOT NULL,
    source      TEXT,
    active      BOOLEAN     DEFAULT TRUE,
    trade_id    UUID        REFERENCES trades(id) ON DELETE SET NULL
);

-- Weekly Opus deep-review records
CREATE TABLE IF NOT EXISTS weekly_reviews (
    id              UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    period_start    TIMESTAMPTZ,
    period_end      TIMESTAMPTZ,
    total_trades    INTEGER,
    correct_trades  INTEGER,
    wrong_trades    INTEGER,
    pnl_usd         NUMERIC(10,2),
    review_text     TEXT
);

-- Pending live-trade confirmations (Telegram approve/reject)
CREATE TABLE IF NOT EXISTS pending_confirmations (
    id              UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    decision        JSONB,
    market          JSONB,
    portfolio       JSONB,
    status          TEXT        DEFAULT 'pending',
    telegram_msg_id INTEGER
);

-- Core indexes
CREATE INDEX IF NOT EXISTS idx_trades_created_at      ON trades (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_trades_outcome         ON trades (outcome) WHERE outcome IS NULL;
CREATE INDEX IF NOT EXISTS idx_lessons_active         ON lessons (active, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_snapshots_created_at   ON portfolio_snapshots (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_reviews_created_at     ON weekly_reviews (created_at DESC);

-- ===================== COIN RESEARCH =====================

-- Research reports generated by Claude Opus for new/trending coins
CREATE TABLE IF NOT EXISTS coin_research (
    id              UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    coin_id         TEXT        NOT NULL,
    symbol          TEXT        NOT NULL,
    name            TEXT        NOT NULL,
    category        TEXT,
    investment_score    INTEGER,
    team_score          INTEGER,
    technology_score    INTEGER,
    market_score        INTEGER,
    tokenomics_score    INTEGER,
    usecase_score       INTEGER,
    verdict         TEXT,
    suggested_usd   NUMERIC(10,2),
    hold_months     INTEGER,
    risks           JSONB,
    opportunities   JSONB,
    summary         TEXT,
    price_usd       NUMERIC(20,8),
    market_cap_usd  NUMERIC(20,2),
    volume_24h_usd  NUMERIC(20,2),
    price_change_7d NUMERIC(8,2),
    github_commits_4w INTEGER,
    twitter_followers INTEGER,
    raw_data        JSONB,
    on_watchlist    BOOLEAN     DEFAULT FALSE,
    actioned        BOOLEAN     DEFAULT FALSE,
    notes           TEXT
);

-- Watchlist: coins the user has saved for monitoring
CREATE TABLE IF NOT EXISTS coin_watchlist (
    id          UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    coin_id     TEXT        NOT NULL UNIQUE,
    symbol      TEXT        NOT NULL,
    name        TEXT        NOT NULL,
    entry_price NUMERIC(20,8),
    target_usd  NUMERIC(10,2),
    research_id UUID        REFERENCES coin_research(id),
    active      BOOLEAN     DEFAULT TRUE
);

-- Coin research indexes
CREATE INDEX IF NOT EXISTS idx_coin_research_symbol    ON coin_research (symbol);
CREATE INDEX IF NOT EXISTS idx_coin_research_verdict   ON coin_research (verdict, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_coin_research_score     ON coin_research (investment_score DESC);
CREATE INDEX IF NOT EXISTS idx_coin_watchlist_active   ON coin_watchlist (active, created_at DESC);

-- ===================== BOT EVENTS =====================

-- Structured event log — powers the web dashboard activity feed
CREATE TABLE IF NOT EXISTS bot_events (
    id          UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    level       TEXT        DEFAULT 'info',
    event       TEXT        NOT NULL,
    message     TEXT,
    data        JSONB
);

CREATE INDEX IF NOT EXISTS idx_bot_events_time ON bot_events (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_bot_events_level ON bot_events (level, created_at DESC);