app/mcp/tools.py

"""
MCP Tools — AI Integration Layer
─────────────────────────────────
Tools for AI assistants to interact with the database.
"""

from app.database import get_db

def get_all_tables() -> list:
    """Get all table names in the database."""
    conn = get_db()
    tables = conn.execute(
        "SELECT name FROM sqlite_master WHERE type='table'"
    ).fetchall()
    return [t[0] for t in tables]


def get_table_schema(table_name: str) -> list:
    """Get the schema for a specific table."""
    conn = get_db()
    columns = conn.execute(f"PRAGMA table_info([{table_name}])").fetchall()
    return [dict(c) for c in columns]


def execute_safe_query(sql: str) -> list:
    """
    Execute a SELECT query safely.
    Only allows SELECT and PRAGMA statements.
    """
    sql_upper = sql.strip().upper()
    if not (sql_upper.startswith("SELECT") or sql_upper.startswith("PRAGMA")):
        raise ValueError("Only SELECT and PRAGMA queries are allowed")
    
    conn = get_db()
    rows = conn.execute(sql).fetchall()
    return [dict(r) for r in rows]