schema.sql

-- Trading Bot — Supabase SQL Schema
-- Füge diese Datei im Supabase SQL Editor aus

-- ──────────────────────────────────────────────
-- Tabelle: trades
-- ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS trades (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    symbol TEXT NOT NULL,
    entry_price FLOAT NOT NULL,
    exit_price FLOAT,
    position_size_usd FLOAT NOT NULL,
    stop_loss FLOAT NOT NULL,
    take_profit_1 FLOAT NOT NULL,
    take_profit_2 FLOAT NOT NULL,
    entry_atr FLOAT NOT NULL DEFAULT 0,
    pnl_pct FLOAT,
    pnl_usd FLOAT,
    realized_pnl_usd FLOAT DEFAULT 0,
    risk_reward_ratio FLOAT,
    entry_learning_tags TEXT[],
    exit_learning_tags TEXT[],
    exit_reason TEXT,
    opened_at TIMESTAMPTZ DEFAULT NOW(),
    closed_at TIMESTAMPTZ,
    status TEXT DEFAULT 'open',   -- 'open', 'closed'
    hold_count INT DEFAULT 0,
    tp1_hit BOOLEAN DEFAULT FALSE,
    trailing_active BOOLEAN DEFAULT FALSE,
    trailing_stop FLOAT,
    highest_price FLOAT
);

-- ──────────────────────────────────────────────
-- Tabelle: scan_logs
-- ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS scan_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    scanned_at TIMESTAMPTZ DEFAULT NOW(),
    market_gate_passed BOOLEAN NOT NULL,
    market_gate_reason TEXT,
    candidates_found INT DEFAULT 0,
    trades_opened INT DEFAULT 0,
    trades_closed INT DEFAULT 0,
    errors TEXT[],
    candidates TEXT[],
    ai_decisions JSONB
);

-- ──────────────────────────────────────────────
-- Tabelle: btc_price_log
-- ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS btc_price_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    price FLOAT NOT NULL,
    logged_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index für schnellere Abfragen
CREATE INDEX IF NOT EXISTS idx_btc_price_log_logged_at
    ON btc_price_log (logged_at DESC);

-- ──────────────────────────────────────────────
-- Tabelle: blacklist
-- ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS blacklist (
    symbol TEXT PRIMARY KEY,
    reason TEXT,
    expires_at TIMESTAMPTZ NOT NULL
);

-- Index für schnelle Blacklist-Checks
CREATE INDEX IF NOT EXISTS idx_blacklist_expires
    ON blacklist (expires_at);

-- ──────────────────────────────────────────────
-- Row Level Security deaktivieren (für Bot)
-- ──────────────────────────────────────────────
ALTER TABLE trades DISABLE ROW LEVEL SECURITY;
ALTER TABLE scan_logs DISABLE ROW LEVEL SECURITY;
ALTER TABLE btc_price_log DISABLE ROW LEVEL SECURITY;
ALTER TABLE blacklist DISABLE ROW LEVEL SECURITY;