import sqlite3
import os

def inspect(db_path):
    print(f"\n=== Inspecting {db_path} ===")
    if not os.path.exists(db_path):
        print("File not found")
        return
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        # Get tables
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
        tables = cursor.fetchall()
        print("Tables:")
        for t in tables:
            print(f"  - {t[0]}")
            # Get schema for each table
            cursor.execute(f"PRAGMA table_info({t[0]});")
            cols = cursor.fetchall()
            for c in cols:
                print(f"    {c[1]} {c[2]} {'NOTNULL' if c[3] else ''} {'PK' if c[5] else ''}")
            # Foreign keys
            cursor.execute(f"PRAGMA foreign_key_list({t[0]});")
            fks = cursor.fetchall()
            if fks:
                print("    FKs:")
                for fk in fks:
                    print(f"      {fk[3]} -> {fk[2]}({fk[4]})")
            # Indexes
            cursor.execute(f"PRAGMA index_list({t[0]});")
            idxs = cursor.fetchall()
            if idxs:
                print("    Indexes:")
                for idx in idxs:
                    print(f"      {idx[1]} {'unique' if idx[2] else ''}")
        # Integrity check
        cursor.execute("PRAGMA integrity_check;")
        integrity = cursor.fetchone()
        print(f"Integrity: {integrity[0] if integrity else 'unknown'}")
        # Foreign key check
        cursor.execute("PRAGMA foreign_key_check;")
        fk_issues = cursor.fetchall()
        print(f"Foreign key issues: {len(fk_issues)}")
        if fk_issues:
            for issue in fk_issues[:5]:
                print(f"  {issue}")
        conn.close()
    except Exception as e:
        print(f"Error: {e}")

if __name__ == "__main__":
    dbs = [
        "/opt/struktur/social-media-agent/cases.db",
        "/opt/struktur/social-media-agent/db/posts.db",
        "/opt/struktur/social-media-radar/signals/radar.db",
        "/opt/struktur/knowledge.db",
    ]
    for db in dbs:
        inspect(db)