backend/app/db/schema_reader.py

from sqlalchemy import inspect
from .db_connector import get_db_engine
from .db_connector import get_connection

def get_schema():
    """Fetch database schema information (tables & columns)."""
    schema = {}
    conn = get_connection()
    try:
        cursor = conn.cursor()
        
        # Get all tables
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';")
        tables = cursor.fetchall()

        for t in tables:
            table_name = t[0]
            cursor.execute(f"PRAGMA table_info({table_name});")
            columns = cursor.fetchall()
            schema[table_name] = [col[1] for col in columns]

        return schema
    finally:
        cursor.close()
        conn.close()