"""SQLAlchemy bridge — read tables via reflection.

Works against the fixture's postgres. Reflection means the link does not
need to import the app's model code — it discovers tables at runtime.
"""

import os

from fastapi import APIRouter, HTTPException
from sqlalchemy import MetaData, create_engine, inspect, select

router = APIRouter()


def _db_url() -> str:
    host = os.getenv("DB_HOST", "db")
    port = os.getenv("DB_PORT", "5432")
    name = os.getenv("DB_NAME", "fixture")
    user = os.getenv("DB_USER", "postgres")
    password = os.getenv("DB_PASSWORD", "localdev123")
    return f"postgresql+psycopg2://{user}:{password}@{host}:{port}/{name}"


engine = create_engine(_db_url(), future=True)
metadata = MetaData()


@router.get("/tables")
def list_tables():
    insp = inspect(engine)
    return {"tables": insp.get_table_names()}


@router.get("/tables/{name}")
def table_rows(name: str, limit: int = 50):
    insp = inspect(engine)
    if name not in insp.get_table_names():
        raise HTTPException(404, f"table '{name}' not found")
    metadata.reflect(bind=engine, only=[name])
    table = metadata.tables[name]
    with engine.connect() as conn:
        rows = conn.execute(select(table).limit(limit)).mappings().all()
    return {"table": name, "rows": [dict(r) for r in rows]}