import streamlit as st
import plotly.graph_objects as go
import sqlite3
import os

DB_PATH = os.getenv("DB_PATH", "orbital_forensics.db")

def get_total_frozen_capital():
    if not os.path.exists(DB_PATH):
        return 0.0
    try:
        conn = sqlite3.connect(f"file:{DB_PATH}?mode=ro", uri=True)
        cursor = conn.cursor()
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name IN ('asset_freezes', 'asset_seizures')")
        tables = [row[0] for row in cursor.fetchall()]
        
        total = 0.0
        if "asset_freezes" in tables:
            cursor.execute("SELECT COALESCE(SUM(amount_usd), 0.0) FROM asset_freezes WHERE status = 'FROZEN'")
            total += cursor.fetchone()[0]
        if "asset_seizures" in tables:
            cursor.execute("SELECT COALESCE(SUM(amount_locked_usd), 0.0) FROM asset_seizures")
            total += cursor.fetchone()[0]
            
        conn.close()
        return total
    except Exception:
        return 0.0

@st.fragment(run_every="5s")
def render_orbital_asset_freeze_gauge():
    current_frozen = get_total_frozen_capital()
    target_threshold = 50_000_000.0

    fig = go.Figure(go.Indicator(
        mode="gauge+number+delta",
        value=current_frozen,
        number={"prefix": "$", "valueformat": ",.0f"},
        delta={"reference": 10_000_000.0, "prefix": "vs Base $", "valueformat": ",.0f"},
        title={"text": "<b>18 U.S.C. § 981 Seized Capital</b><br><span style='font-size:0.8em;color:#94a3b8'>Real-Time Orbital Asset Lock Monitor</span>"},
        gauge={
            "axis": {"range": [0, target_threshold], "tickformat": "$,.0s"},
            "bar": {"color": "#ef4444"},
            "bgcolor": "#1e293b",
            "borderwidth": 1,
            "bordercolor": "#334155",
            "steps": [
                {"range": [0, 15_000_000], "color": "#0f172a"},
                {"range": [15_000_000, 35_000_000], "color": "#1e293b"},
                {"range": [35_000_000, target_threshold], "color": "#450a0a"}
            ],
            "threshold": {
                "line": {"color": "#f87171", "width": 4},
                "thickness": 0.8,
                "value": 40_000_000
            }
        }
    ))

    fig.update_layout(
        paper_bgcolor="rgba(0,0,0,0)",
        plot_bgcolor="rgba(0,0,0,0)",
        font={"color": "#f8fafc", "family": "Courier New, monospace"},
        height=320,
        margin=dict(l=20, r=20, t=50, b=20)
    )

    st.plotly_chart(fig, use_container_width=True)
    st.caption(f"⏱️ Auto-polling SQLite telemetry every 5s • Active Locked Assets: **${current_frozen:,.2f} USD**")