hr-portal/backend/cleanup_database.py

"""
清理資料庫中的初始化資料
"""
import psycopg2

# 資料庫連線
conn = psycopg2.connect(
    host="10.1.0.20",
    port=5433,
    database="hr_portal",
    user="admin",
    password="DC1qaz2wsx"
)
conn.autocommit = False
cur = conn.cursor()

try:
    print("=== 檢查現有資料 ===")

    # 檢查 tenants
    cur.execute("SELECT id, code, name, is_initialized FROM tenants;")
    tenants = cur.fetchall()
    print(f"\n現有租戶記錄 ({len(tenants)} 筆):")
    for t in tenants:
        print(f"  ID={t[0]}, code={t[1]}, name={t[2]}, is_initialized={t[3]}")

    # 檢查 installation_sessions
    cur.execute("SELECT id, session_name, status FROM installation_sessions;")
    sessions = cur.fetchall()
    print(f"\n現有 installation_sessions ({len(sessions)} 筆):")
    for s in sessions:
        print(f"  ID={s[0]}, name={s[1]}, status={s[2]}")

    # 檢查 temporary_passwords
    cur.execute("SELECT id, username, tenant_id FROM temporary_passwords;")
    passwords = cur.fetchall()
    print(f"\n現有 temporary_passwords ({len(passwords)} 筆):")
    for p in passwords:
        print(f"  ID={p[0]}, username={p[1]}, tenant_id={p[2]}")

    print("\n" + "="*50)
    print("開始清理資料...")
    print("="*50)

    # 刪除順序很重要 (先刪除有外鍵依賴的表)

    # 1. 刪除 tenant 相關資料
    if tenants:
        tenant_ids = [t[0] for t in tenants]
        print(f"\n刪除 tenant_id IN ({tenant_ids}) 的相關資料...")

        cur.execute("DELETE FROM tenant_emp_settings WHERE tenant_id IN %s;", (tuple(tenant_ids),))
        print(f"  tenant_emp_settings: {cur.rowcount} row(s)")

        cur.execute("DELETE FROM tenant_emp_resumes WHERE tenant_id IN %s;", (tuple(tenant_ids),))
        print(f"  tenant_emp_resumes: {cur.rowcount} row(s)")

        cur.execute("DELETE FROM tenant_departments WHERE tenant_id IN %s;", (tuple(tenant_ids),))
        print(f"  tenant_departments: {cur.rowcount} row(s)")

        cur.execute("DELETE FROM tenant_user_roles WHERE tenant_id IN %s;", (tuple(tenant_ids),))
        print(f"  tenant_user_roles: {cur.rowcount} row(s)")

        cur.execute("DELETE FROM tenant_user_role_assignments WHERE tenant_id IN %s;", (tuple(tenant_ids),))
        print(f"  tenant_user_role_assignments: {cur.rowcount} row(s)")

        cur.execute("DELETE FROM tenant_email_accounts WHERE tenant_id IN %s;", (tuple(tenant_ids),))
        print(f"  tenant_email_accounts: {cur.rowcount} row(s)")

        cur.execute("DELETE FROM tenant_network_drives WHERE tenant_id IN %s;", (tuple(tenant_ids),))
        print(f"  tenant_network_drives: {cur.rowcount} row(s)")

        cur.execute("DELETE FROM tenant_permissions WHERE tenant_id IN %s;", (tuple(tenant_ids),))
        print(f"  tenant_permissions: {cur.rowcount} row(s)")

        cur.execute("DELETE FROM tenant_audit_logs WHERE tenant_id IN %s;", (tuple(tenant_ids),))
        print(f"  tenant_audit_logs: {cur.rowcount} row(s)")

        cur.execute("DELETE FROM tenant_dept_members WHERE tenant_id IN %s;", (tuple(tenant_ids),))
        print(f"  tenant_dept_members: {cur.rowcount} row(s)")

        cur.execute("DELETE FROM tenants WHERE id IN %s;", (tuple(tenant_ids),))
        print(f"  tenants: {cur.rowcount} row(s)")

    # 2. 刪除 installation 相關資料 (有 CASCADE 所以會自動刪除相關表)
    cur.execute("DELETE FROM temporary_passwords;")
    print(f"\ntemporary_passwords: {cur.rowcount} row(s)")

    cur.execute("DELETE FROM installation_department_setup;")
    print(f"installation_department_setup: {cur.rowcount} row(s)")

    cur.execute("DELETE FROM installation_mail_domain_setting;")
    print(f"installation_mail_domain_setting: {cur.rowcount} row(s)")

    cur.execute("DELETE FROM installation_tenant_info;")
    print(f"installation_tenant_info: {cur.rowcount} row(s)")

    cur.execute("DELETE FROM installation_checklist_results;")
    print(f"installation_checklist_results: {cur.rowcount} row(s)")

    cur.execute("DELETE FROM installation_logs;")
    print(f"installation_logs: {cur.rowcount} row(s)")

    cur.execute("DELETE FROM installation_access_logs;")
    print(f"installation_access_logs: {cur.rowcount} row(s)")

    cur.execute("DELETE FROM installation_sessions;")
    print(f"installation_sessions: {cur.rowcount} row(s)")

    # 3. 重置序列
    print("\n重置序列...")
    cur.execute("SELECT setval('tenants_id_seq', 1, false);")
    print("  tenants_id_seq → 1")

    cur.execute("SELECT setval('installation_sessions_id_seq', 1, false);")
    print("  installation_sessions_id_seq → 1")

    # 4. 重置系統狀態
    cur.execute("""
        UPDATE installation_system_status
        SET current_phase = 'initialization',
            initialization_completed = FALSE,
            is_locked = FALSE
        WHERE id = 1;
    """)
    print(f"\ninstallation_system_status: 已重置為 initialization 階段")

    # Commit
    conn.commit()
    print("\n" + "="*50)
    print("SUCCESS: Database cleanup completed!")
    print("="*50)
    print("\nYou can now re-run the initialization process.")

except Exception as e:
    conn.rollback()
    print(f"\nERROR: {e}")
    import traceback
    traceback.print_exc()
finally:
    cur.close()
    conn.close()