"""Move vCenter VMs from servers to hypervisors table (kind='vcenter').

Detects vCenter by hostname pattern (*vcs*).

Usage:
    python tools/move_vcenters.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")
    parser.add_argument("--pattern", default="%vcs%",
                        help="ILIKE pattern for vCenter hostnames (default: %%vcs%%)")
    args = parser.parse_args()

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

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

    # Add kind column if missing
    conn.execute(text("ALTER TABLE hypervisors ADD COLUMN IF NOT EXISTS kind varchar(20) DEFAULT 'hypervisor'"))
    conn.execute(text("UPDATE hypervisors SET kind='hypervisor' WHERE kind IS NULL"))

    # Find vCenters in servers
    rows = conn.execute(text(
        "SELECT id, hostname, fqdn, site, domain_ltd, patch_owner_details, responsable_nom "
        "FROM servers WHERE hostname ILIKE :p"
    ), {"p": args.pattern}).fetchall()
    print(f"[INFO] {len(rows)} candidats vCenter trouves dans servers")

    moved = 0
    skipped = 0
    for r in rows:
        if args.dry_run:
            print(f"  DRY: {r.hostname}")
            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, 'vcenter')
                ON CONFLICT (hostname) DO UPDATE SET
                    kind='vcenter', 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 vCenter: {moved} | Ignores: {skipped}")


if __name__ == "__main__":
    main()