"""
Comprehensive fix for all database foreign key constraints.
"""
import sqlite3

# Connect to SQLite database
db_path = 'db.sqlite3'
conn = sqlite3.connect(db_path)
cursor = conn.cursor()

print("Fixing all database constraints...")

# Disable foreign key checks
cursor.execute("PRAGMA foreign_keys = OFF;")

# Get all tables with tenant_id that have violations
tables_with_violations = [
    'radiology_imaging_study',
    'accounts_user',
    'hr_department',
    'hr_training_program',
    'operating_theatre_operating_room',
    'patients_patient_profile',
]

for table in tables_with_violations:
    try:
        # Delete rows with invalid tenant_id
        cursor.execute(f"""
            DELETE FROM {table}
            WHERE tenant_id NOT IN (SELECT id FROM core_tenant)
        """)
        affected = cursor.rowcount
        if affected > 0:
            print(f"  Deleted {affected} rows from {table} with invalid tenant_id")
    except sqlite3.OperationalError as e:
        print(f"  Error fixing {table}: {e}")

# Commit changes
conn.commit()

# Re-enable foreign key checks
cursor.execute("PRAGMA foreign_keys = ON;")

# Verify constraints
cursor.execute("PRAGMA foreign_key_check;")
violations = cursor.fetchall()

if violations:
    print(f"\n⚠ Still have {len(violations)} foreign key violations")
    print("First 10 violations:")
    for violation in violations[:10]:
        print(f"  {violation}")
else:
    print("\n✓ All foreign key constraints are now valid!")

conn.close()
print("\nDatabase fix completed!")