-- Fantasy42 Betting Intelligence Schema
-- Advanced analytics for CLV, exposure, sharp detection, and risk management

-- Line movement tracking for CLV calculations
CREATE TABLE IF NOT EXISTS line_movements (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_id TEXT NOT NULL,
    agent_id TEXT NOT NULL,
    sport_type TEXT NOT NULL,
    
    -- Line data
    timestamp INTEGER NOT NULL,
    line_type TEXT NOT NULL, -- SPREAD, TOTAL, MONEYLINE
    team TEXT,
    line_before REAL NOT NULL,
    line_after REAL NOT NULL,
    line_move REAL GENERATED ALWAYS AS (line_after - line_before) STORED,
    
    -- Volume and action
    volume_before REAL,
    volume_after REAL,
    bet_count INTEGER DEFAULT 0,
    
    -- Movement classification
    movement_type TEXT, -- STEAM, REVERSE, GRADUAL, SHARP
    movement_speed TEXT, -- INSTANT, FAST, SLOW
    
    created_at INTEGER DEFAULT (strftime('%s','now') * 1000)
);

-- Sharp bettor indicators and CLV tracking
CREATE TABLE IF NOT EXISTS sharp_indicators (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    agent_id TEXT NOT NULL,
    customer_id TEXT NOT NULL,
    
    -- Performance metrics
    total_bets INTEGER DEFAULT 0,
    win_count INTEGER DEFAULT 0,
    loss_count INTEGER DEFAULT 0,
    push_count INTEGER DEFAULT 0,
    win_rate REAL GENERATED ALWAYS AS (
        CASE 
            WHEN (win_count + loss_count) > 0 
            THEN CAST(win_count AS REAL) / (win_count + loss_count)
            ELSE 0.0
        END
    ) STORED,
    
    -- CLV metrics
    clv_score REAL DEFAULT 0.0, -- Positive = beating closing line
    clv_bets INTEGER DEFAULT 0,
    clv_wins INTEGER DEFAULT 0,
    avg_clv REAL DEFAULT 0.0,
    
    -- Betting patterns
    avg_bet_size REAL DEFAULT 0.0,
    max_bet_size REAL DEFAULT 0.0,
    avg_odds REAL DEFAULT 0.0,
    favorite_sport TEXT,
    
    -- Sharp classification
    sharp_score REAL DEFAULT 0.0, -- 0-100 score
    classification TEXT, -- SHARP, SQUARE, UNKNOWN
    confidence_level REAL DEFAULT 0.0,
    
    -- Timing patterns
    avg_bet_timing INTEGER, -- Minutes before game start
    early_bet_percentage REAL DEFAULT 0.0,
    
    last_updated INTEGER DEFAULT (strftime('%s','now') * 1000),
    created_at INTEGER DEFAULT (strftime('%s','now') * 1000),
    
    UNIQUE(agent_id, customer_id)
);

-- Real-time exposure tracking by event/team
CREATE TABLE IF NOT EXISTS exposure_tracking (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    agent_id TEXT NOT NULL,
    event_id TEXT NOT NULL,
    sport_type TEXT NOT NULL,
    game_datetime INTEGER,
    
    -- Teams/participants
    home_team TEXT,
    away_team TEXT,
    
    -- Exposure metrics
    total_handle REAL DEFAULT 0.0,
    home_risk REAL DEFAULT 0.0,
    away_risk REAL DEFAULT 0.0,
    over_risk REAL DEFAULT 0.0,
    under_risk REAL DEFAULT 0.0,
    
    -- Net position (positive = house advantage)
    net_position REAL GENERATED ALWAYS AS (total_handle - (home_risk + away_risk)) STORED,
    
    -- Risk concentration
    max_single_bet REAL DEFAULT 0.0,
    bet_count INTEGER DEFAULT 0,
    unique_customers INTEGER DEFAULT 0,
    
    -- Liability scenarios
    worst_case_loss REAL DEFAULT 0.0,
    best_case_win REAL DEFAULT 0.0,
    expected_value REAL DEFAULT 0.0,
    
    -- Risk flags
    exposure_level TEXT, -- LOW, MEDIUM, HIGH, CRITICAL
    needs_hedge BOOLEAN DEFAULT FALSE,
    
    last_updated INTEGER DEFAULT (strftime('%s','now') * 1000),
    created_at INTEGER DEFAULT (strftime('%s','now') * 1000),
    
    UNIQUE(agent_id, event_id)
);

-- Steam move detection and tracking
CREATE TABLE IF NOT EXISTS steam_moves (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    agent_id TEXT NOT NULL,
    event_id TEXT NOT NULL,
    
    -- Move details
    detected_at INTEGER NOT NULL,
    sport_type TEXT NOT NULL,
    market_type TEXT NOT NULL, -- SPREAD, TOTAL, ML
    team TEXT,
    
    -- Line movement
    opening_line REAL NOT NULL,
    current_line REAL NOT NULL,
    total_movement REAL NOT NULL,
    movement_velocity REAL, -- Points per minute
    
    -- Volume spike
    normal_volume REAL,
    spike_volume REAL,
    volume_multiplier REAL,
    
    -- Sharp action
    sharp_bet_count INTEGER DEFAULT 0,
    sharp_money_percentage REAL,
    
    -- Alert status
    alert_sent BOOLEAN DEFAULT FALSE,
    alert_level TEXT, -- INFO, WARNING, CRITICAL
    
    created_at INTEGER DEFAULT (strftime('%s','now') * 1000)
);

-- Hold percentage tracking by market
CREATE TABLE IF NOT EXISTS hold_analysis (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    agent_id TEXT NOT NULL,
    sport_type TEXT NOT NULL,
    market_type TEXT NOT NULL,
    period TEXT NOT NULL, -- GAME, 1H, 2H, QUARTER
    
    -- Time period
    analysis_date INTEGER NOT NULL, -- YYYYMMDD format
    hour_of_day INTEGER,
    
    -- Volume and handle
    total_handle REAL DEFAULT 0.0,
    bet_count INTEGER DEFAULT 0,
    
    -- Hold metrics
    theoretical_hold REAL DEFAULT 0.0, -- Based on vig
    actual_hold REAL DEFAULT 0.0, -- Real results
    hold_variance REAL GENERATED ALWAYS AS (actual_hold - theoretical_hold) STORED,
    
    -- Market efficiency
    balanced_percentage REAL DEFAULT 0.0, -- How balanced the action was
    sharp_percentage REAL DEFAULT 0.0,
    
    created_at INTEGER DEFAULT (strftime('%s','now') * 1000),
    
    UNIQUE(agent_id, sport_type, market_type, period, analysis_date, hour_of_day)
);

-- Risk concentration analysis
CREATE TABLE IF NOT EXISTS risk_concentration (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    agent_id TEXT NOT NULL,
    concentration_type TEXT NOT NULL, -- CUSTOMER, EVENT, SPORT, TEAM
    concentration_id TEXT NOT NULL, -- The specific ID being tracked
    
    -- Exposure metrics
    total_exposure REAL DEFAULT 0.0,
    active_risk REAL DEFAULT 0.0,
    settled_pnl REAL DEFAULT 0.0,
    
    -- Concentration metrics
    exposure_percentage REAL DEFAULT 0.0, -- % of total book
    risk_score REAL DEFAULT 0.0, -- 0-100
    
    -- Historical performance
    historical_pnl REAL DEFAULT 0.0,
    historical_roi REAL DEFAULT 0.0,
    
    -- Risk limits
    max_exposure_limit REAL,
    current_utilization REAL DEFAULT 0.0,
    
    last_updated INTEGER DEFAULT (strftime('%s','now') * 1000),
    created_at INTEGER DEFAULT (strftime('%s','now') * 1000),
    
    UNIQUE(agent_id, concentration_type, concentration_id)
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_line_movements_event ON line_movements(event_id, timestamp);
CREATE INDEX IF NOT EXISTS idx_line_movements_steam ON line_movements(movement_type, timestamp) WHERE movement_type = 'STEAM';

CREATE INDEX IF NOT EXISTS idx_sharp_indicators_score ON sharp_indicators(sharp_score DESC);
CREATE INDEX IF NOT EXISTS idx_sharp_indicators_customer ON sharp_indicators(agent_id, customer_id);

CREATE INDEX IF NOT EXISTS idx_exposure_event ON exposure_tracking(agent_id, event_id);
CREATE INDEX IF NOT EXISTS idx_exposure_risk ON exposure_tracking(exposure_level, needs_hedge);

CREATE INDEX IF NOT EXISTS idx_steam_moves_alert ON steam_moves(alert_sent, alert_level);
CREATE INDEX IF NOT EXISTS idx_steam_moves_time ON steam_moves(detected_at DESC);

CREATE INDEX IF NOT EXISTS idx_hold_analysis_date ON hold_analysis(analysis_date, sport_type);

CREATE INDEX IF NOT EXISTS idx_risk_concentration ON risk_concentration(risk_score DESC);