"""SQLite persistente para Aviator Signals.

- crashes: toda rodada observada (fonte única de verdade p/ análise de padrão).
- bets: cada aposta do auto-click com o resultado, linkada ou não à IA.

Thread-safe: check_same_thread=False + lock. Writes são fire-and-forget
(rápidos; SQLite file-based local). Reads grandes só no boot."""
import os
import sqlite3
import threading
import time

try:
    import cloud_sync
except Exception:
    cloud_sync = None

DB_DIR = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'data')
DB_PATH = os.path.join(DB_DIR, 'aviator.db')

_lock = threading.Lock()
_conn: sqlite3.Connection | None = None


def init_db():
    """Cria o diretório e as tabelas se não existirem."""
    global _conn
    os.makedirs(DB_DIR, exist_ok=True)
    _conn = sqlite3.connect(DB_PATH, check_same_thread=False, isolation_level=None)
    _conn.execute('PRAGMA journal_mode=WAL')
    _conn.execute('PRAGMA synchronous=NORMAL')
    _conn.executescript('''
        CREATE TABLE IF NOT EXISTS crashes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            ts REAL NOT NULL,
            value REAL NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_crashes_ts ON crashes(ts);

        CREATE TABLE IF NOT EXISTS bets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            ts REAL NOT NULL,
            panel INTEGER NOT NULL,
            target REAL NOT NULL,
            amount REAL NOT NULL,
            ia_approved INTEGER NOT NULL DEFAULT 0,
            hit INTEGER,
            delta_pnl REAL,
            resolved_ts REAL
        );
        CREATE INDEX IF NOT EXISTS idx_bets_panel_resolved ON bets(panel, resolved_ts);
    ''')
    # Migração: adiciona colunas novas se ainda não existirem
    cols = [r[1] for r in _conn.execute('PRAGMA table_info(bets)').fetchall()]
    if 'pattern' not in cols:
        _conn.execute('ALTER TABLE bets ADD COLUMN pattern TEXT')
    if 'probability' not in cols:
        _conn.execute('ALTER TABLE bets ADD COLUMN probability REAL')
    _conn.execute('CREATE INDEX IF NOT EXISTS idx_bets_pattern ON bets(pattern, target)')
    _conn.execute('CREATE INDEX IF NOT EXISTS idx_bets_panel_target_prob ON bets(panel, target, probability)')
    # 1.26.0 — Gap-fill: marca rodadas inferidas (1.00 invisíveis no .payouts-block)
    crash_cols = [r[1] for r in _conn.execute('PRAGMA table_info(crashes)').fetchall()]
    if 'inferred' not in crash_cols:
        _conn.execute('ALTER TABLE crashes ADD COLUMN inferred INTEGER NOT NULL DEFAULT 0')


def insert_crash(value: float, ts: float | None = None, inferred: bool = False):
    if _conn is None: return
    ts = ts if ts is not None else time.time()
    with _lock:
        _conn.execute('INSERT INTO crashes(ts, value, inferred) VALUES (?, ?, ?)',
                      (ts, value, 1 if inferred else 0))
    if cloud_sync is not None:
        cloud_sync.enqueue_crash(value, ts, inferred=inferred)


def load_recent_crashes(limit: int = 2000):
    """Retorna lista de dicts ordenada do mais antigo → mais recente."""
    if _conn is None: return []
    with _lock:
        rows = _conn.execute(
            'SELECT ts, value FROM crashes ORDER BY id DESC LIMIT ?', (limit,)
        ).fetchall()
    return [{'at': r[0], 'value': r[1]} for r in reversed(rows)]


def count_crashes() -> int:
    if _conn is None: return 0
    with _lock:
        return _conn.execute('SELECT COUNT(*) FROM crashes').fetchone()[0]


def insert_bet(panel: int, target: float, amount: float, ia_approved: bool, pattern: str | None = None, probability: float | None = None, ts: float | None = None) -> int:
    """Insere aposta aberta (sem resolução). Retorna rowid."""
    if _conn is None: return 0
    ts = ts if ts is not None else time.time()
    with _lock:
        cur = _conn.execute(
            'INSERT INTO bets(ts, panel, target, amount, ia_approved, pattern, probability) VALUES (?,?,?,?,?,?,?)',
            (ts, panel, target, amount, 1 if ia_approved else 0, pattern, probability),
        )
        bet_id = cur.lastrowid or 0
    if cloud_sync is not None and bet_id:
        cloud_sync.enqueue_bet_open(bet_id, panel, target, amount, ia_approved, pattern, probability, ts)
    return bet_id


def prob_band_hit_rate(panel: int, target: float, probability: float, tol: float = 0.05, target_tol: float = 0.25, limit: int = 40) -> dict:
    """Histórico de hit rate em apostas resolvidas cuja probabilidade estimada
    no momento ficava ±tol da probabilidade atual, mesmo painel e target similar.
    Revela se a IA é overconfident/underconfident naquela faixa."""
    if _conn is None or probability is None:
        return {'count': 0, 'hits': 0, 'pnl': 0.0, 'avg_prob': probability}
    p_lo = max(0.0, probability - tol)
    p_hi = min(1.0, probability + tol)
    t_lo = target - target_tol
    t_hi = target + target_tol
    with _lock:
        rows = _conn.execute(
            '''SELECT hit, delta_pnl, probability FROM bets
               WHERE panel=? AND target BETWEEN ? AND ?
                 AND probability IS NOT NULL AND probability BETWEEN ? AND ?
                 AND resolved_ts IS NOT NULL
               ORDER BY id DESC LIMIT ?''',
            (panel, t_lo, t_hi, p_lo, p_hi, limit),
        ).fetchall()
    count = len(rows)
    hits = sum(1 for r in rows if r[0] == 1)
    pnl = float(sum(r[1] or 0 for r in rows))
    avg_prob = (sum(r[2] or 0 for r in rows) / count) if count else probability
    return {'count': count, 'hits': hits, 'pnl': pnl, 'avg_prob': avg_prob}


def pattern_bet_history(pattern: str, target: float, tol: float = 0.25, limit: int = 30) -> dict:
    """Retorna histórico de apostas resolvidas com esse padrão e target próximo
    (±tol). Usado pra aprendizado causal."""
    if _conn is None or not pattern:
        return {'count': 0, 'hits': 0, 'pnl': 0.0}
    t_lo = target - tol
    t_hi = target + tol
    with _lock:
        rows = _conn.execute(
            '''SELECT hit, delta_pnl FROM bets
               WHERE pattern=? AND target BETWEEN ? AND ?
                 AND resolved_ts IS NOT NULL
               ORDER BY id DESC LIMIT ?''',
            (pattern, t_lo, t_hi, limit),
        ).fetchall()
    count = len(rows)
    hits = sum(1 for r in rows if r[0] == 1)
    pnl = float(sum(r[1] or 0 for r in rows))
    return {'count': count, 'hits': hits, 'pnl': pnl}


def resolve_last_open_bet(panel: int, hit: bool, delta_pnl: float, ts: float | None = None):
    """Marca a aposta aberta mais recente do painel com o resultado."""
    if _conn is None: return
    ts = ts if ts is not None else time.time()
    with _lock:
        row = _conn.execute(
            'SELECT id FROM bets WHERE panel=? AND resolved_ts IS NULL ORDER BY id DESC LIMIT 1',
            (panel,),
        ).fetchone()
        if not row: return
        bet_id = row[0]
        _conn.execute(
            'UPDATE bets SET hit=?, delta_pnl=?, resolved_ts=? WHERE id=?',
            (1 if hit else 0, float(delta_pnl), ts, bet_id),
        )
    if cloud_sync is not None:
        cloud_sync.enqueue_bet_resolve(bet_id, hit, delta_pnl, ts)


def get_panels_stats() -> dict:
    """Agrega wins/losses/IA por painel do histórico completo."""
    base = {
        0: {'wins': 0, 'losses': 0, 'ia_approves': 0, 'ia_wins': 0, 'ia_losses': 0},
        1: {'wins': 0, 'losses': 0, 'ia_approves': 0, 'ia_wins': 0, 'ia_losses': 0},
    }
    if _conn is None: return base
    with _lock:
        rows = _conn.execute('''
            SELECT panel,
                   SUM(CASE WHEN hit=1 THEN 1 ELSE 0 END) wins,
                   SUM(CASE WHEN hit=0 THEN 1 ELSE 0 END) losses,
                   SUM(CASE WHEN ia_approved=1 THEN 1 ELSE 0 END) ia_approves,
                   SUM(CASE WHEN ia_approved=1 AND hit=1 THEN 1 ELSE 0 END) ia_wins,
                   SUM(CASE WHEN ia_approved=1 AND hit=0 THEN 1 ELSE 0 END) ia_losses
            FROM bets GROUP BY panel
        ''').fetchall()
    for r in rows:
        p = r[0]
        if p in base:
            base[p] = {
                'wins': int(r[1] or 0), 'losses': int(r[2] or 0),
                'ia_approves': int(r[3] or 0),
                'ia_wins': int(r[4] or 0), 'ia_losses': int(r[5] or 0),
            }
    return base


def recent_ia_performance(panel: int, limit: int = 25) -> dict:
    """Retorna as últimas N apostas resolvidas onde a IA aprovou, pra o painel.
    `pnl_per_unit` = pnl / amount_apostado (return por R$ apostado).
    Usado para auto-ajuste adaptativo dos thresholds."""
    out = {'count': 0, 'hits': 0, 'pnl': 0.0, 'amount': 0.0, 'pnl_per_unit': 0.0}
    if _conn is None: return out
    with _lock:
        # Filtra amount > 0 pra ignorar shadow bets (amount=0) — AUTO-AJUSTE
        # se baseia só em dinheiro real.
        rows = _conn.execute(
            '''SELECT hit, delta_pnl, amount FROM bets
               WHERE panel=? AND ia_approved=1 AND resolved_ts IS NOT NULL
                 AND amount > 0
               ORDER BY id DESC LIMIT ?''',
            (panel, limit),
        ).fetchall()
    if not rows: return out
    out['count'] = len(rows)
    out['hits'] = sum(1 for r in rows if r[0] == 1)
    out['pnl'] = float(sum((r[1] or 0) for r in rows))
    out['amount'] = float(sum((r[2] or 0) for r in rows))
    out['pnl_per_unit'] = (out['pnl'] / out['amount']) if out['amount'] > 0 else 0.0
    return out


def panels_stats_since(since_ts: float) -> dict:
    """V/D/IA por painel desde o timestamp (exclui shadow, não-resolvidos).
    Usado como fonte da verdade pra contador V/D/IA do dashboard."""
    base = {
        0: {'wins': 0, 'losses': 0, 'ia_approves': 0, 'ia_wins': 0, 'ia_losses': 0},
        1: {'wins': 0, 'losses': 0, 'ia_approves': 0, 'ia_wins': 0, 'ia_losses': 0},
    }
    if _conn is None:
        return base
    with _lock:
        rows = _conn.execute('''
            SELECT panel,
                   SUM(CASE WHEN hit=1 THEN 1 ELSE 0 END) wins,
                   SUM(CASE WHEN hit=0 THEN 1 ELSE 0 END) losses,
                   SUM(CASE WHEN ia_approved=1 THEN 1 ELSE 0 END) ia_approves,
                   SUM(CASE WHEN ia_approved=1 AND hit=1 THEN 1 ELSE 0 END) ia_wins,
                   SUM(CASE WHEN ia_approved=1 AND hit=0 THEN 1 ELSE 0 END) ia_losses
            FROM bets
            WHERE resolved_ts IS NOT NULL AND amount > 0 AND ts >= ?
            GROUP BY panel
        ''', (float(since_ts or 0),)).fetchall()
    for r in rows:
        p = r[0]
        if p in base:
            base[p] = {
                'wins': int(r[1] or 0), 'losses': int(r[2] or 0),
                'ia_approves': int(r[3] or 0),
                'ia_wins': int(r[4] or 0), 'ia_losses': int(r[5] or 0),
            }
    return base


def recent_pattern_bets(panel: int, pattern: str, target: float, n: int = 3, tol: float = 0.25) -> list:
    """Retorna as últimas N apostas resolvidas com esse padrão+target. Usado
    pra pattern blacklist (3 misses seguidas → timeout 2h)."""
    if _conn is None or not pattern: return []
    t_lo = target - tol; t_hi = target + tol
    with _lock:
        rows = _conn.execute(
            '''SELECT ts, hit FROM bets
               WHERE panel=? AND pattern=? AND target BETWEEN ? AND ?
                 AND resolved_ts IS NOT NULL AND amount > 0
               ORDER BY id DESC LIMIT ?''',
            (panel, pattern, t_lo, t_hi, n),
        ).fetchall()
    return [{'ts': r[0], 'hit': r[1]} for r in rows]


def totals() -> dict:
    if _conn is None: return {'crashes': 0, 'bets': 0}
    with _lock:
        c = _conn.execute('SELECT COUNT(*) FROM crashes').fetchone()[0]
        b = _conn.execute('SELECT COUNT(*) FROM bets').fetchone()[0]
    return {'crashes': int(c), 'bets': int(b)}
