"""Move ESXi/Hyperviseurs identifiés par leur description (pas dans le CSV Hyperviseur iTop).

Cherche les serveurs dont la description mentionne 'ESXi' ou 'hebergeant'
et les déplace vers la table hypervisors.

Usage:
    python tools/move_esxi_extras.py [--dry-run]
"""
import os
import argparse
from sqlalchemy import create_engine, text

DATABASE_URL = os.getenv("DATABASE_URL_DEMO") or os.getenv("DATABASE_URL") \
    or "postgresql://patchcenter:PatchCenter2026!@localhost:5432/patchcenter_demo"


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--dry-run", action="store_true")
    args = parser.parse_args()

    engine = create_engine(DATABASE_URL)
    print(f"[INFO] DB: {DATABASE_URL.split('@')[-1]}")

    conn = engine.connect().execution_options(isolation_level="AUTOCOMMIT")

    rows = conn.execute(text("""
        SELECT id, hostname, fqdn, site, domain_ltd, patch_owner_details, responsable_nom
        FROM servers
        WHERE patch_owner_details ILIKE '%ESXi%'
           OR patch_owner_details ILIKE '%hebergeant%'
           OR patch_owner_details ILIKE '%hyperviseur%'
    """)).fetchall()

    print(f"[INFO] {len(rows)} candidats trouves")

    moved = 0
    skipped = 0
    for r in rows:
        if args.dry_run:
            print(f"  DRY: {r.hostname}  --  {(r.patch_owner_details or '')[:80]}")
            continue
        try:
            conn.execute(text("""
                INSERT INTO hypervisors
                (hostname, fqdn, site, domain_ltd, description, responsable_nom, moved_from_server_id, kind)
                VALUES (:h, :fqdn, :site, :domain, :desc, :resp, :sid, 'hypervisor')
                ON CONFLICT (hostname) DO UPDATE SET
                    description=EXCLUDED.description,
                    moved_from_server_id=EXCLUDED.moved_from_server_id
            """), {
                "h": r.hostname, "fqdn": r.fqdn, "site": r.site,
                "domain": r.domain_ltd, "desc": r.patch_owner_details,
                "resp": r.responsable_nom, "sid": r.id,
            })
            for tbl, col in [("server_ips", "server_id"), ("qualys_assets", "server_id"),
                             ("server_audit", "server_id"), ("server_audit_full", "server_id"),
                             ("server_correspondance", "server_id"), ("cluster_members", "server_id"),
                             ("patch_history", "server_id"), ("patch_sessions", "server_id"),
                             ("patch_validation", "server_id"), ("quickwin_entries", "server_id"),
                             ("quickwin_server_config", "server_id"), ("server_specifics", "server_id"),
                             ("server_pairs", "server_a_id"), ("server_pairs", "server_b_id")]:
                try:
                    conn.execute(text(f"DELETE FROM {tbl} WHERE {col}=:sid"), {"sid": r.id})
                except Exception:
                    pass
            conn.execute(text("DELETE FROM servers WHERE id=:sid"), {"sid": r.id})
            moved += 1
        except Exception as e:
            print(f"  [ERR] {r.hostname}: {str(e)[:150]}")
            skipped += 1

    conn.close()
    print(f"[DONE] Deplaces: {moved} | Ignores: {skipped}")


if __name__ == "__main__":
    main()