import sqlite3, json
conn = sqlite3.connect('/opt/friend-cluster/data/friend-cluster-v1.db')
cur = conn.cursor()

pairs = [
    ('deploy-f23e64e7-979','friend-fada02e6','node-2c6966ac'),
    ('deploy-78b3ef6e-9f3','friend-33953b97','node-028b8ed4'),
    ('deploy-d5562f8f-c05','friend-cf8a5bba','node-77bfde78'),
    ('deploy-e96da620-50e','friend-24654384','node-9817e013'),
    ('deploy-1f970fe1-14e','friend-c2c95ebc','node-a2ff9fee'),
    ('deploy-b42e2b1d-469','friend-6368a2e7','node-55a470cc'),
    ('deploy-e9c89778-862','friend-58aedcb6','node-809f9469'),
    ('deploy-62080a6d-024','friend-7013d01f','node-0fff14de'),
    ('deploy-ae9e10b0-f7b','friend-c7c66057','node-1e3e064e'),
    ('deploy-69a540b6-0ca','friend-c2ffba03','node-1d17fd19'),
    ('deploy-54015964-c87','friend-2d8eba0b','node-563a8fe9'),
    ('deploy-2cba04fe-e0f','friend-dc86d8f3','node-c11d7e6f'),
    ('deploy-74ba1c5f-d21','friend-b45a4c1b','node-f48dd44d'),
]

import datetime
now = datetime.datetime.utcnow().isoformat() + 'Z'

for dep, iid, nid in pairs:
    # Mark all decommission commands for this instance as succeeded
    cur.execute("UPDATE commands SET status='succeeded', result='instance decommissioned (force-cleanup)', completed_at=? WHERE action='instance.decommission' AND instance_id=? AND status='accepted'", (now, iid))
    print(f"commands updated: {cur.rowcount} for {iid}")
    
    # Delete instance records
    for table in ['config_resources', 'config_transactions', 'extension_assignments', 'friend_instances']:
        cur.execute(f"DELETE FROM {table} WHERE instance_id=?" if table != 'friend_instances' else f"DELETE FROM {table} WHERE id=?", (iid,))
        if cur.rowcount > 0:
            print(f"  deleted {cur.rowcount} from {table}")
    
    # Mark deployment as deleted
    cur.execute("UPDATE instance_deployments SET status='deleted', deleted_at=?, updated_at=? WHERE id=?", (now, now, dep))
    print(f"  deployment {dep} -> deleted")

conn.commit()

# Verify
cur.execute("SELECT COUNT(*) FROM friend_instances WHERE id IN ({})".format(','.join(['?']*len(pairs))), [p[1] for p in pairs])
print(f"\nremaining orphan instances: {cur.fetchone()[0]}")
conn.close()