import os
import sqlite3

BASE_DIR = "/home/skeversu/skever-portal-v1"
DB_PATH = os.path.join(BASE_DIR, "db.sqlite3")

print("SKEVER PORTAL V1 - READ-ONLY DATABASE INSPECTION")
print("=" * 60)
print("Database:", DB_PATH)

if not os.path.exists(DB_PATH):
    print("ERROR: db.sqlite3 was not found.")
    raise SystemExit(1)

con = sqlite3.connect(DB_PATH)
con.row_factory = sqlite3.Row

try:
    tables = con.execute(
        "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
    ).fetchall()

    print("TABLE COUNT:", len(tables))
    print()

    for row in tables:
        table = row["name"]
        print("TABLE:", table)

        cols = con.execute('PRAGMA table_info("' + table + '")').fetchall()
        for c in cols:
            pk = " PK" if c["pk"] else ""
            nn = " NOT NULL" if c["notnull"] else ""
            default = (" DEFAULT " + str(c["dflt_value"])) if c["dflt_value"] is not None else ""
            print("  -", c["name"], "|", c["type"] or "UNKNOWN", pk + nn + default)

        fks = con.execute('PRAGMA foreign_key_list("' + table + '")').fetchall()
        for fk in fks:
            print("  FK:", fk["from"], "->", fk["table"] + "." + fk["to"])

        print()

    print("=" * 60)
    print("DJANGO MIGRATION HISTORY")
    print("=" * 60)

    try:
        rows = con.execute(
            "SELECT app, name, applied FROM django_migrations ORDER BY applied, app, name"
        ).fetchall()
        for r in rows:
            print(r["app"], "|", r["name"], "|", r["applied"])
    except sqlite3.Error as exc:
        print("Could not read django_migrations:", exc)

    print()
    print("=" * 60)
    print("BUSINESS TABLE SUMMARY")
    print("=" * 60)

    keywords = ("account", "order", "payment", "dispatch", "audit", "customer", "user")
    for row in tables:
        name = row["name"]
        if any(k in name.lower() for k in keywords):
            count = con.execute('SELECT COUNT(*) AS n FROM "' + name + '"').fetchone()["n"]
            print(name, ":", count, "rows")

    print()
    print("READ-ONLY INSPECTION COMPLETE.")
    print("NO INSERT, UPDATE, DELETE, ALTER, DROP, OR MIGRATION OPERATIONS WERE PERFORMED.")

finally:
    con.close()