import sqlite3
import sys
import os


def drop_all_tables(db_path: str) -> None:
    if not os.path.exists(db_path):
        print(f"Database not found: {db_path}")
        return

    conn = sqlite3.connect(db_path)
    try:
        conn.isolation_level = None  # autocommit mode for DDL
        cur = conn.cursor()

        # Disable foreign key enforcement to allow dropping in any order
        cur.execute("PRAGMA foreign_keys = OFF;")

        # Collect tables and views
        cur.execute("SELECT name, type FROM sqlite_master WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%';")
        objects = cur.fetchall()
        print(f"DB: {db_path}")
        if not objects:
            print("No user tables or views found.")
            return

        # Drop views first, then tables
        views = [name for name, t in objects if t == 'view']
        tables = [name for name, t in objects if t == 'table']

        print(f"Found {len(tables)} tables and {len(views)} views.")
        for v in views:
            print(f"Dropping view: {v}")
            cur.execute(f"DROP VIEW IF EXISTS \"{v}\";")

        for t in tables:
            print(f"Dropping table: {t}")
            cur.execute(f"DROP TABLE IF EXISTS \"{t}\";")

        # Vacuum to clean up
        cur.execute("VACUUM;")
        print("Done.")
    finally:
        conn.close()


def list_tables(db_path: str) -> None:
    if not os.path.exists(db_path):
        print(f"Database not found: {db_path}")
        return
    conn = sqlite3.connect(db_path)
    try:
        cur = conn.cursor()
        cur.execute("SELECT name, type FROM sqlite_master WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' ORDER BY type, name;")
        objects = cur.fetchall()
        print(f"DB: {db_path}")
        if not objects:
            print("No user tables or views found.")
            return
        for name, t in objects:
            print(f"- {t}: {name}")
    finally:
        conn.close()


if __name__ == "__main__":
    # Usage: python drop_all_tables.py <db1> [<db2> ...]
    paths = sys.argv[1:]
    if not paths:
        base = os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))
        paths = [os.path.join(base, 'photos.db'), os.path.join(base, 'data', 'photos.db')]

    for p in paths:
        list_tables(p)
    for p in paths:
        drop_all_tables(p)
    for p in paths:
        list_tables(p)
