import sqlite3
from pathlib import Path
from typing import Iterable

BASE_DIR = Path(__file__).resolve().parent.parent
DATA_DIR = BASE_DIR / "data"


def iter_db_files() -> Iterable[Path]:
    for p in sorted(DATA_DIR.glob("*.db")):
        yield p


def inspect_db(db_path: Path) -> None:
    print("\n=== DB:", db_path.name, "===")
    try:
        conn = sqlite3.connect(str(db_path))
        conn.row_factory = sqlite3.Row
    except Exception as exc:
        print(f"[ERROR] No es pot obrir la BD: {exc}")
        return

    try:
        cur = conn.cursor()
        # Llistar taules
        cur.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;")
        tables = [r[0] for r in cur.fetchall()]
        if not tables:
            print("(sense taules)")
            return

        for table in tables:
            print(f"\n  Taula: {table}")
            # Esquema
            cur.execute(f"PRAGMA table_info({table});")
            cols = cur.fetchall()
            col_names = [c[1] for c in cols]
            print("    Columnes:", ", ".join(col_names))

            # Nombre de registres
            cur.execute(f"SELECT COUNT(*) FROM {table};")
            n = cur.fetchone()[0]
            print("    Nombre de registres:", n)

            # Tres primers registres
            cur.execute(f"SELECT * FROM {table} LIMIT 3;")
            rows = cur.fetchall()
            if not rows:
                print("    (sense registres)")
            else:
                for i, row in enumerate(rows, start=1):
                    as_dict = {k: row[k] for k in row.keys()}
                    print(f"    Registre {i}:", as_dict)
    finally:
        conn.close()


def main() -> None:
    print(f"Inspeccionant BDs a {DATA_DIR}...")
    for db_path in iter_db_files():
        inspect_db(db_path)


if __name__ == "__main__":
    main()