-- PIPPO.CUTTALO.COM Database Schema
-- Multi-Bot Trading Platform

-- Grant schema permissions
GRANT ALL ON SCHEMA public TO pippo;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO pippo;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO pippo;

-- =====================================================
-- TRADERS TABLE
-- Configurazioni dei singoli trader/bot
-- =====================================================
CREATE TABLE IF NOT EXISTS traders (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    status VARCHAR(20) DEFAULT 'stopped', -- stopped, paper, live

    -- Configurazione capitale
    initial_capital DECIMAL(20,2) NOT NULL DEFAULT 10000,
    current_capital DECIMAL(20,2) DEFAULT 10000,
    position_size DECIMAL(5,4) DEFAULT 0.20, -- 20%

    -- Configurazione rischio
    stop_loss DECIMAL(5,4) DEFAULT 0.02,     -- 2%
    take_profit DECIMAL(5,4) DEFAULT 0.03,   -- 3%
    max_positions INT DEFAULT 1,

    -- Configurazione modello
    model_type VARCHAR(50) DEFAULT 'v9',
    model_config JSONB DEFAULT '{}',
    confidence_threshold DECIMAL(5,4) DEFAULT 0.70,

    -- Configurazione trading
    pair VARCHAR(20) DEFAULT 'BTC/EUR',
    timeframe VARCHAR(10) DEFAULT '4h',
    fee_rate DECIMAL(5,4) DEFAULT 0.005,

    -- Timestamps
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- =====================================================
-- TRADES TABLE
-- Storico di tutti i trade eseguiti
-- =====================================================
CREATE TABLE IF NOT EXISTS trades (
    id SERIAL PRIMARY KEY,
    trader_id INT REFERENCES traders(id) ON DELETE CASCADE,
    mode VARCHAR(20) NOT NULL, -- paper, live, backtest
    backtest_id INT, -- riferimento se backtest

    -- Trade info
    pair VARCHAR(20),
    direction VARCHAR(10), -- LONG, SHORT
    action VARCHAR(10),    -- OPEN, CLOSE

    -- Prezzi
    entry_price DECIMAL(20,8),
    exit_price DECIMAL(20,8),
    amount DECIMAL(20,8),

    -- Costi dettagliati
    fee_entry DECIMAL(20,8) DEFAULT 0,
    fee_exit DECIMAL(20,8) DEFAULT 0,
    spread_cost DECIMAL(20,8) DEFAULT 0,
    slippage_cost DECIMAL(20,8) DEFAULT 0,

    -- Profitto
    gross_pnl DECIMAL(20,8),
    net_pnl DECIMAL(20,8),
    pnl_percent DECIMAL(10,4),

    -- Exit info
    exit_reason VARCHAR(50), -- model, stop_loss, take_profit, timeout, manual

    -- Timing
    entry_time TIMESTAMP,
    exit_time TIMESTAMP,
    duration_minutes INT,

    -- State
    capital_before DECIMAL(20,2),
    capital_after DECIMAL(20,2),

    created_at TIMESTAMP DEFAULT NOW()
);

-- =====================================================
-- BACKTESTS TABLE
-- Sessioni di backtest
-- =====================================================
CREATE TABLE IF NOT EXISTS backtests (
    id SERIAL PRIMARY KEY,
    trader_id INT REFERENCES traders(id) ON DELETE CASCADE,
    name VARCHAR(100),

    -- Periodo
    start_date TIMESTAMP,
    end_date TIMESTAMP,

    -- Configurazione usata (snapshot)
    initial_capital DECIMAL(20,2),
    config JSONB,

    -- Risultati
    final_capital DECIMAL(20,2),
    total_trades INT DEFAULT 0,
    winning_trades INT DEFAULT 0,
    losing_trades INT DEFAULT 0,
    gross_profit DECIMAL(20,8) DEFAULT 0,
    gross_loss DECIMAL(20,8) DEFAULT 0,
    total_fees DECIMAL(20,8) DEFAULT 0,
    max_drawdown DECIMAL(10,4) DEFAULT 0,
    sharpe_ratio DECIMAL(10,4),
    profit_factor DECIMAL(10,4),

    -- Status
    status VARCHAR(20) DEFAULT 'pending', -- pending, running, completed, error
    progress DECIMAL(5,2) DEFAULT 0,
    error_message TEXT,

    -- Timing
    created_at TIMESTAMP DEFAULT NOW(),
    started_at TIMESTAMP,
    completed_at TIMESTAMP
);

-- =====================================================
-- TRADER_STATES TABLE
-- Stato real-time dei trader attivi
-- =====================================================
CREATE TABLE IF NOT EXISTS trader_states (
    id SERIAL PRIMARY KEY,
    trader_id INT REFERENCES traders(id) ON DELETE CASCADE UNIQUE,

    -- Posizione corrente
    position INT DEFAULT 0, -- 0=flat, 1=long, -1=short
    position_amount DECIMAL(20,8) DEFAULT 0,
    entry_price DECIMAL(20,8) DEFAULT 0,
    entry_time TIMESTAMP,

    -- Metriche correnti
    current_price DECIMAL(20,8) DEFAULT 0,
    unrealized_pnl DECIMAL(20,8) DEFAULT 0,
    unrealized_pnl_pct DECIMAL(10,4) DEFAULT 0,

    -- Stop/Take livelli
    stop_loss_price DECIMAL(20,8),
    take_profit_price DECIMAL(20,8),

    -- Sessione
    session_start TIMESTAMP,
    session_trades INT DEFAULT 0,
    session_pnl DECIMAL(20,8) DEFAULT 0,

    -- ML State
    candles_loaded INT DEFAULT 0,
    last_signal VARCHAR(20),
    last_signal_confidence DECIMAL(5,4),

    updated_at TIMESTAMP DEFAULT NOW()
);

-- =====================================================
-- TRADER_SCORES TABLE
-- Performance e punteggi calcolati
-- =====================================================
CREATE TABLE IF NOT EXISTS trader_scores (
    id SERIAL PRIMARY KEY,
    trader_id INT REFERENCES traders(id) ON DELETE CASCADE,
    period VARCHAR(20), -- daily, weekly, monthly, all_time
    period_start TIMESTAMP,
    period_end TIMESTAMP,

    -- Metriche base
    total_return DECIMAL(10,4),
    total_pnl DECIMAL(20,8),
    total_trades INT DEFAULT 0,
    winning_trades INT DEFAULT 0,
    losing_trades INT DEFAULT 0,
    win_rate DECIMAL(5,4),
    profit_factor DECIMAL(10,4),

    -- Risk metrics
    max_drawdown DECIMAL(10,4),
    avg_drawdown DECIMAL(10,4),
    sharpe_ratio DECIMAL(10,4),
    sortino_ratio DECIMAL(10,4),
    calmar_ratio DECIMAL(10,4),

    -- Trading metrics
    avg_trade_pnl DECIMAL(20,8),
    avg_win DECIMAL(20,8),
    avg_loss DECIMAL(20,8),
    largest_win DECIMAL(20,8),
    largest_loss DECIMAL(20,8),
    avg_duration_minutes INT,

    -- Fees analysis
    total_fees DECIMAL(20,8),
    fees_as_pct DECIMAL(5,4),

    -- Score finale (0-100)
    score DECIMAL(5,2),
    rank INT,

    calculated_at TIMESTAMP DEFAULT NOW(),

    UNIQUE(trader_id, period, period_start)
);

-- =====================================================
-- PRICE_HISTORY TABLE
-- Dati storici per backtest
-- =====================================================
CREATE TABLE IF NOT EXISTS price_history (
    id SERIAL PRIMARY KEY,
    pair VARCHAR(20) NOT NULL,
    timeframe VARCHAR(10) NOT NULL,

    open_time TIMESTAMP NOT NULL,
    open DECIMAL(20,8),
    high DECIMAL(20,8),
    low DECIMAL(20,8),
    close DECIMAL(20,8),
    volume DECIMAL(30,8),

    UNIQUE(pair, timeframe, open_time)
);

-- =====================================================
-- EQUITY_SNAPSHOTS TABLE
-- Snapshot periodici equity per grafici
-- =====================================================
CREATE TABLE IF NOT EXISTS equity_snapshots (
    id SERIAL PRIMARY KEY,
    trader_id INT REFERENCES traders(id) ON DELETE CASCADE,
    mode VARCHAR(20), -- paper, live

    timestamp TIMESTAMP NOT NULL,
    equity DECIMAL(20,2),
    capital DECIMAL(20,2),
    unrealized_pnl DECIMAL(20,8),
    btc_price DECIMAL(20,8),

    UNIQUE(trader_id, mode, timestamp)
);

-- =====================================================
-- INDEXES
-- =====================================================
CREATE INDEX IF NOT EXISTS idx_trades_trader ON trades(trader_id);
CREATE INDEX IF NOT EXISTS idx_trades_mode ON trades(mode);
CREATE INDEX IF NOT EXISTS idx_trades_entry_time ON trades(entry_time);
CREATE INDEX IF NOT EXISTS idx_trades_backtest ON trades(backtest_id);
CREATE INDEX IF NOT EXISTS idx_backtests_trader ON backtests(trader_id);
CREATE INDEX IF NOT EXISTS idx_backtests_status ON backtests(status);
CREATE INDEX IF NOT EXISTS idx_price_history_lookup ON price_history(pair, timeframe, open_time);
CREATE INDEX IF NOT EXISTS idx_equity_snapshots_lookup ON equity_snapshots(trader_id, mode, timestamp);
CREATE INDEX IF NOT EXISTS idx_trader_scores_lookup ON trader_scores(trader_id, period);

-- =====================================================
-- INSERT DEFAULT TRADERS
-- =====================================================
INSERT INTO traders (name, description, initial_capital, position_size, stop_loss, take_profit, confidence_threshold, model_config) VALUES
('V9 Conservative', 'Strategia conservativa con rischio ridotto', 10000, 0.15, 0.015, 0.025, 0.75, '{"risk_level": "low"}'),
('V9 Balanced', 'Strategia bilanciata rischio/rendimento', 10000, 0.20, 0.02, 0.03, 0.70, '{"risk_level": "medium"}'),
('V9 Aggressive', 'Strategia aggressiva per massimizzare rendimenti', 10000, 0.30, 0.025, 0.04, 0.65, '{"risk_level": "high"}')
ON CONFLICT DO NOTHING;
