import sqlite3
conn = sqlite3.connect('/opt/friend-cluster/data/friend-cluster-v1.db')
cur = conn.cursor()
# Find deployments whose status is terminal (deleted/failed) but whose instance still exists & running
cur.execute("""
SELECT d.id, d.status, d.instance_id, d.node_id, i.desired_run_state, i.health_state
FROM instance_deployments d LEFT JOIN friend_instances i ON i.id = d.instance_id
WHERE d.status IN ('deleted','failed') AND d.instance_id != ''
LIMIT 30
""")
rows = cur.fetchall()
print("terminal deployments with instance_id:", len(rows))
orphans = []
for r in rows:
    did, status, iid, nid, drs, hs = r
    if iid:
        cur.execute("SELECT id, desired_run_state, health_state FROM friend_instances WHERE id=?", (iid,))
        inst = cur.fetchone()
        if inst:
            print("deployment", did, status, "-> instance", inst)
            if inst[1] == 'running':
                orphans.append((did, iid, nid))
print("orphan candidates:", len(orphans))
for o in orphans[:20]: print(o)
conn.close()