app/admin/router.py

"""
Admin Panel — Built-in SQLite Browser
──────────────────────────────────────
Like pgAdmin but embedded in your FastAPI app.
Password protected. Runs on the same port (7860).

Features:
  - Dashboard with DB health + stats
  - Browse all tables
  - View/edit/delete rows
  - Run raw SQL queries
  - Manage backups (create/restore/download)
  - Integrity checks

Source inspiration: 
  - sqlite-web: https://github.com/coleifer/sqlite-web
  - Flask-Admin: https://github.com/flask-admin/flask-admin
"""

from fastapi import APIRouter, Request, Depends, HTTPException, Form
from fastapi.responses import HTMLResponse, FileResponse, RedirectResponse
from fastapi.templating import Jinja2Templates
import sqlite3
import os
import secrets

from app.database import get_db, DB_PATH, integrity_check
from app.backup import (
    create_snapshot, create_compressed_snapshot, create_vacuum_snapshot,
    upload_to_hf_bucket, list_backups, restore_from_hf_bucket, diagnose_hf_setup
)

router = APIRouter(prefix="/admin", tags=["admin"])
templates = Jinja2Templates(directory="app/admin/templates")

ADMIN_PASSWORD = os.environ.get("ADMIN_PASSWORD", "change-me-in-production")
ADMIN_TOKEN = secrets.token_hex(32)  # Session token

# ── Simple Auth ──
def verify_admin(request: Request):
    token = request.cookies.get("admin_token")
    if token != ADMIN_TOKEN:
        raise HTTPException(status_code=303, headers={"Location": "/admin/login"})
    return True

# ── LOGIN ──
@router.get("/login", response_class=HTMLResponse)
async def login_page(request: Request):
    return templates.TemplateResponse("login.html", {"request": request})

@router.post("/login")
async def login(request: Request, password: str = Form(...)):
    if password == ADMIN_PASSWORD:
        response = RedirectResponse(url="/admin/dashboard", status_code=303)
        response.set_cookie("admin_token", ADMIN_TOKEN, httponly=True)
        return response
    return templates.TemplateResponse("login.html", {
        "request": request, "error": "Wrong password"
    })

# ── DASHBOARD ──
@router.get("/dashboard", response_class=HTMLResponse)
async def dashboard(request: Request, _=Depends(verify_admin)):
    health = integrity_check()
    conn = get_db()
    
    # Get all tables
    tables = conn.execute(
        "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
    ).fetchall()
    
    table_info = []
    for table in tables:
        name = table[0]
        count = conn.execute(f"SELECT COUNT(*) FROM [{name}]").fetchone()[0]
        table_info.append({"name": name, "row_count": count})
    
    # Recent backup log
    try:
        backups = conn.execute(
            "SELECT * FROM backup_log ORDER BY created_at DESC LIMIT 10"
        ).fetchall()
    except:
        backups = []
    
    return templates.TemplateResponse("dashboard.html", {
        "request": request,
        "health": health,
        "tables": table_info,
        "backups": [dict(b) for b in backups],
        "db_path": DB_PATH,
        "db_exists": os.path.exists(DB_PATH),
    })

# ── TABLE BROWSER ──
@router.get("/table/{table_name}", response_class=HTMLResponse)
async def view_table(request: Request, table_name: str, page: int = 1, _=Depends(verify_admin)):
    conn = get_db()
    per_page = 50
    offset = (page - 1) * per_page
    
    # Get columns
    columns = conn.execute(f"PRAGMA table_info([{table_name}])").fetchall()
    
    # Get rows
    rows = conn.execute(
        f"SELECT * FROM [{table_name}] LIMIT ? OFFSET ?",
        (per_page, offset)
    ).fetchall()
    
    total = conn.execute(f"SELECT COUNT(*) FROM [{table_name}]").fetchone()[0]
    
    # Get indexes
    indexes = conn.execute(f"PRAGMA index_list([{table_name}])").fetchall()
    
    return templates.TemplateResponse("table_view.html", {
        "request": request,
        "table_name": table_name,
        "columns": [dict(c) for c in columns],
        "rows": [dict(r) for r in rows],
        "total": total,
        "page": page,
        "per_page": per_page,
        "total_pages": (total + per_page - 1) // per_page,
        "indexes": [dict(i) for i in indexes],
    })

# ── SQL QUERY RUNNER ──
@router.get("/query", response_class=HTMLResponse)
async def query_page(request: Request, _=Depends(verify_admin)):
    return templates.TemplateResponse("query.html", {
        "request": request, "results": None, "sql": ""
    })

@router.post("/query", response_class=HTMLResponse)
async def run_query(request: Request, sql: str = Form(...), _=Depends(verify_admin)):
    conn = get_db()
    error = None
    results = None
    columns = []
    
    try:
        cursor = conn.execute(sql)
        if sql.strip().upper().startswith("SELECT") or sql.strip().upper().startswith("PRAGMA"):
            results = cursor.fetchall()
            if results:
                columns = results[0].keys()
        else:
            conn.commit()
            results = [{"affected_rows": cursor.rowcount}]
            columns = ["affected_rows"]
    except Exception as e:
        error = str(e)
    
    return templates.TemplateResponse("query.html", {
        "request": request,
        "sql": sql,
        "results": [dict(r) for r in results] if results else None,
        "columns": columns,
        "error": error,
    })

# ── BACKUP MANAGEMENT ──
@router.get("/backups", response_class=HTMLResponse)
async def backups_page(request: Request, _=Depends(verify_admin)):
    backup_info = list_backups()
    health = integrity_check()
    hf_diagnosis = diagnose_hf_setup()
    
    return templates.TemplateResponse("backups.html", {
        "request": request,
        "backups": backup_info,
        "health": health,
        "hf_diagnosis": hf_diagnosis,
    })

@router.post("/backups/create")
async def create_backup(request: Request, backup_type: str = Form("snapshot"), _=Depends(verify_admin)):
    if backup_type == "snapshot":
        result = create_snapshot(label="admin_manual")
    elif backup_type == "compressed":
        result = create_compressed_snapshot()
    elif backup_type == "vacuum":
        result = create_vacuum_snapshot()
    elif backup_type == "hf_upload":
        result = upload_to_hf_bucket()
    else:
        result = {"status": "error", "message": f"Unknown type: {backup_type}"}
    
    # Store result in session/cookie for display
    # For simplicity, we just redirect - the result is logged
    return RedirectResponse(url="/admin/backups", status_code=303)

@router.get("/backups/download/{filename}")
async def download_backup(filename: str, _=Depends(verify_admin)):
    filepath = os.path.join("/tmp/data/snapshots", filename)
    if os.path.exists(filepath):
        return FileResponse(filepath, filename=filename)
    raise HTTPException(status_code=404, detail="Backup not found")

# ── API ENDPOINTS (for programmatic access) ──
@router.get("/api/health")
async def api_health():
    """Public health check — no auth required."""
    return integrity_check()

@router.get("/api/tables")
async def api_tables(_=Depends(verify_admin)):
    conn = get_db()
    tables = conn.execute(
        "SELECT name FROM sqlite_master WHERE type='table'"
    ).fetchall()
    return [dict(t) for t in tables]

@router.get("/api/table/{table_name}/export")
async def export_table(table_name: str, format: str = "json", _=Depends(verify_admin)):
    conn = get_db()
    rows = conn.execute(f"SELECT * FROM [{table_name}]").fetchall()
    return [dict(r) for r in rows]

# ── DIAGNOSTICS ──
@router.get("/api/diagnostics")
async def api_diagnostics(_=Depends(verify_admin)):
    """Run HF diagnostics - check credentials and repo access."""
    return diagnose_hf_setup()

@router.post("/api/backup/upload")
async def api_backup_upload(_=Depends(verify_admin)):
    """Trigger HF bucket upload via API."""
    return upload_to_hf_bucket()

@router.post("/api/backup/test")
async def api_backup_test(_=Depends(verify_admin)):
    """Test backup creation and return result."""
    result = create_compressed_snapshot()
    return result