-- Crypto AI Trader Database Schema
-- PostgreSQL + TimescaleDB

-- Enable TimescaleDB extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- ==========================================
-- SYMBOLS TABLE
-- ==========================================
CREATE TABLE IF NOT EXISTS symbols (
    id SERIAL PRIMARY KEY,
    exchange VARCHAR(50) NOT NULL,
    symbol VARCHAR(50) NOT NULL,
    base VARCHAR(20) NOT NULL,
    quote VARCHAR(20) NOT NULL,
    tick_size DECIMAL(20, 10),
    lot_size DECIMAL(20, 10),
    min_notional DECIMAL(20, 10),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(exchange, symbol)
);

-- ==========================================
-- CANDLES TABLE (TimescaleDB Hypertable)
-- ==========================================
CREATE TABLE IF NOT EXISTS candles (
    timestamp TIMESTAMPTZ NOT NULL,
    exchange VARCHAR(50) NOT NULL,
    symbol VARCHAR(50) NOT NULL,
    timeframe VARCHAR(10) NOT NULL,
    open DECIMAL(20, 8) NOT NULL,
    high DECIMAL(20, 8) NOT NULL,
    low DECIMAL(20, 8) NOT NULL,
    close DECIMAL(20, 8) NOT NULL,
    volume DECIMAL(20, 8) NOT NULL,
    PRIMARY KEY (timestamp, exchange, symbol, timeframe)
);

-- Convert to hypertable
SELECT create_hypertable('candles', 'timestamp', if_not_exists => TRUE);

-- Create index for faster queries
CREATE INDEX IF NOT EXISTS idx_candles_symbol_time 
    ON candles (exchange, symbol, timeframe, timestamp DESC);

-- ==========================================
-- TRADES TABLE
-- ==========================================
CREATE TABLE IF NOT EXISTS trades (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    exchange VARCHAR(50) NOT NULL,
    symbol VARCHAR(50) NOT NULL,
    side VARCHAR(10) NOT NULL CHECK (side IN ('BUY', 'SELL')),
    type VARCHAR(20) NOT NULL CHECK (type IN ('market', 'limit')),
    quantity DECIMAL(20, 8) NOT NULL,
    price DECIMAL(20, 8) NOT NULL,
    fee DECIMAL(20, 8) NOT NULL DEFAULT 0,
    fee_currency VARCHAR(10),
    pnl_realized DECIMAL(20, 8),
    order_id VARCHAR(100),
    ai_signal_id INTEGER,
    metadata JSONB
);

CREATE INDEX IF NOT EXISTS idx_trades_timestamp ON trades (timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_trades_symbol ON trades (symbol, timestamp DESC);

-- ==========================================
-- POSITIONS TABLE
-- ==========================================
CREATE TABLE IF NOT EXISTS positions (
    id SERIAL PRIMARY KEY,
    symbol VARCHAR(50) NOT NULL UNIQUE,
    exchange VARCHAR(50) NOT NULL,
    side VARCHAR(10) NOT NULL CHECK (side IN ('LONG', 'SHORT')),
    quantity DECIMAL(20, 8) NOT NULL,
    entry_price DECIMAL(20, 8) NOT NULL,
    current_price DECIMAL(20, 8),
    leverage INTEGER DEFAULT 1,
    pnl_unrealized DECIMAL(20, 8) DEFAULT 0,
    pnl_realized DECIMAL(20, 8) DEFAULT 0,
    stop_loss DECIMAL(20, 8),
    take_profit DECIMAL(20, 8),
    liquidation_price DECIMAL(20, 8),
    opened_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ==========================================
-- AI SIGNALS TABLE
-- ==========================================
CREATE TABLE IF NOT EXISTS ai_signals (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    symbol VARCHAR(50) NOT NULL,
    action VARCHAR(10) NOT NULL CHECK (action IN ('BUY', 'SELL', 'HOLD', 'CLOSE')),
    confidence DECIMAL(5, 4) NOT NULL CHECK (confidence >= 0 AND confidence <= 1),
    size_usdt DECIMAL(20, 8),
    reasoning TEXT NOT NULL,
    context JSONB NOT NULL,
    stop_loss DECIMAL(20, 8),
    take_profit DECIMAL(20, 8),
    executed BOOLEAN DEFAULT false,
    execution_id INTEGER
);

CREATE INDEX IF NOT EXISTS idx_ai_signals_timestamp ON ai_signals (timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_ai_signals_symbol ON ai_signals (symbol, timestamp DESC);

-- ==========================================
-- PORTFOLIO SNAPSHOTS TABLE
-- ==========================================
CREATE TABLE IF NOT EXISTS portfolio_snapshots (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    equity_usdt DECIMAL(20, 8) NOT NULL,
    cash_usdt DECIMAL(20, 8) NOT NULL,
    positions_value_usdt DECIMAL(20, 8) NOT NULL,
    pnl_realized_total DECIMAL(20, 8) NOT NULL,
    pnl_unrealized_total DECIMAL(20, 8) NOT NULL,
    pnl_total DECIMAL(20, 8) NOT NULL,
    pnl_pct DECIMAL(10, 6) NOT NULL,
    num_positions INTEGER NOT NULL,
    num_trades INTEGER NOT NULL,
    drawdown_pct DECIMAL(10, 6),
    metadata JSONB
);

CREATE INDEX IF NOT EXISTS idx_portfolio_timestamp ON portfolio_snapshots (timestamp DESC);

-- ==========================================
-- ORDERS TABLE
-- ==========================================
CREATE TABLE IF NOT EXISTS orders (
    id SERIAL PRIMARY KEY,
    order_id VARCHAR(100) NOT NULL UNIQUE,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    exchange VARCHAR(50) NOT NULL,
    symbol VARCHAR(50) NOT NULL,
    side VARCHAR(10) NOT NULL CHECK (side IN ('BUY', 'SELL')),
    type VARCHAR(20) NOT NULL CHECK (type IN ('market', 'limit', 'stop_loss', 'take_profit')),
    quantity DECIMAL(20, 8) NOT NULL,
    price DECIMAL(20, 8),
    status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'filled', 'partially_filled', 'cancelled', 'rejected')),
    filled_quantity DECIMAL(20, 8) DEFAULT 0,
    avg_fill_price DECIMAL(20, 8),
    fee DECIMAL(20, 8) DEFAULT 0,
    ai_signal_id INTEGER,
    metadata JSONB,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_orders_status ON orders (status, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_orders_symbol ON orders (symbol, timestamp DESC);

-- ==========================================
-- MARKET DATA SNAPSHOTS (for indicators cache)
-- ==========================================
CREATE TABLE IF NOT EXISTS market_snapshots (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    exchange VARCHAR(50) NOT NULL,
    symbol VARCHAR(50) NOT NULL,
    indicators JSONB NOT NULL,
    UNIQUE(exchange, symbol)
);

-- ==========================================
-- SYSTEM LOGS TABLE
-- ==========================================
CREATE TABLE IF NOT EXISTS system_logs (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    level VARCHAR(20) NOT NULL CHECK (level IN ('DEBUG', 'INFO', 'WARNING', 'ERROR', 'CRITICAL')),
    component VARCHAR(100) NOT NULL,
    message TEXT NOT NULL,
    metadata JSONB
);

CREATE INDEX IF NOT EXISTS idx_logs_timestamp ON system_logs (timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_logs_level ON system_logs (level, timestamp DESC);

-- ==========================================
-- INITIAL DATA
-- ==========================================

-- Insert default symbols
INSERT INTO symbols (exchange, symbol, base, quote, tick_size, lot_size, min_notional) VALUES
    ('binance', 'BTC/USDT', 'BTC', 'USDT', 0.01, 0.00001, 10.0),
    ('binance', 'ETH/USDT', 'ETH', 'USDT', 0.01, 0.0001, 10.0),
    ('binance', 'BNB/USDT', 'BNB', 'USDT', 0.01, 0.001, 10.0),
    ('coinbase', 'BTC/USDT', 'BTC', 'USDT', 0.01, 0.00001, 10.0),
    ('coinbase', 'ETH/USDT', 'ETH', 'USDT', 0.01, 0.0001, 10.0)
ON CONFLICT (exchange, symbol) DO NOTHING;

-- Initial portfolio snapshot
INSERT INTO portfolio_snapshots (
    equity_usdt, cash_usdt, positions_value_usdt, 
    pnl_realized_total, pnl_unrealized_total, pnl_total, 
    pnl_pct, num_positions, num_trades
) VALUES (
    10000.0, 10000.0, 0.0, 
    0.0, 0.0, 0.0, 
    0.0, 0, 0
);

-- System log
INSERT INTO system_logs (level, component, message) VALUES 
    ('INFO', 'database', 'Database initialized successfully');

-- ==========================================
-- FUNCTIONS & TRIGGERS
-- ==========================================

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Trigger for positions table
CREATE TRIGGER update_positions_updated_at BEFORE UPDATE ON positions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Trigger for orders table
CREATE TRIGGER update_orders_updated_at BEFORE UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ==========================================
-- VIEWS FOR ANALYTICS
-- ==========================================

-- View for daily performance
CREATE OR REPLACE VIEW daily_performance AS
SELECT 
    DATE(timestamp) as date,
    MIN(equity_usdt) as min_equity,
    MAX(equity_usdt) as max_equity,
    FIRST(equity_usdt, timestamp) as open_equity,
    LAST(equity_usdt, timestamp) as close_equity,
    LAST(pnl_pct, timestamp) as pnl_pct,
    COUNT(*) as snapshots
FROM portfolio_snapshots
GROUP BY DATE(timestamp)
ORDER BY date DESC;

-- View for trade statistics
CREATE OR REPLACE VIEW trade_statistics AS
SELECT 
    symbol,
    COUNT(*) as total_trades,
    SUM(CASE WHEN side = 'BUY' THEN 1 ELSE 0 END) as buys,
    SUM(CASE WHEN side = 'SELL' THEN 1 ELSE 0 END) as sells,
    AVG(quantity * price) as avg_trade_size,
    SUM(fee) as total_fees,
    SUM(COALESCE(pnl_realized, 0)) as total_pnl
FROM trades
GROUP BY symbol;

-- View for active positions
CREATE OR REPLACE VIEW active_positions_view AS
SELECT 
    p.*,
    (p.current_price - p.entry_price) / p.entry_price * 100 as pnl_pct,
    EXTRACT(EPOCH FROM (NOW() - p.opened_at)) / 3600 as hours_open
FROM positions p
WHERE p.quantity > 0;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO trader;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO trader;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO trader;
