---

#!/usr/bin/env python3
"""
Find active assets from Host-AC.xlsx that are missing from audit.xlsx.

Usage, explicit files:
  python3 compare_missing_assets.py "Host-AC.xlsx" "audit.xlsx" "missing_assets.xlsx"

Usage, auto-detect files in the current folder:
  python3 compare_missing_assets.py
"""

from __future__ import annotations

import ipaddress
import argparse
from pathlib import Path

import pandas as pd


HOST_AC_COLUMNS = {
    "vm_name": "VMName",
    "vm_state": "VMState",
    "hostname": "Hostname",
    "ip": "IPAddress",
}

AUDIT_COLUMNS = {
    "host": "@Host",
    "fqdn": "Host.Fqdn",
    "ip": "Host.IpAddress",
}

ACTIVE_STATES = {"active", "poweredon", "powered on", "running", "включена", "включен"}


def normalize_host(value: object) -> str:
    if pd.isna(value):
        return ""
    text = str(value).strip().lower().rstrip(".")
    if not text:
        return ""
    return text.split(".", 1)[0]


def normalize_fqdn(value: object) -> str:
    if pd.isna(value):
        return ""
    return str(value).strip().lower().rstrip(".")


def normalize_ip(value: object) -> str:
    if pd.isna(value):
        return ""

    text = str(value).strip()
    if not text:
        return ""

    # Some exports contain several addresses in one cell.
    for separator in [",", ";", "\n", " "]:
        if separator in text:
            text = text.split(separator, 1)[0].strip()

    try:
        return str(ipaddress.ip_address(text))
    except ValueError:
        return text


def require_columns(df: pd.DataFrame, required: dict[str, str], file_label: str) -> None:
    missing = [col for col in required.values() if col not in df.columns]
    if missing:
        raise SystemExit(
            f"{file_label}: не найдены обязательные колонки: {', '.join(missing)}\n"
            f"Фактические колонки: {', '.join(map(str, df.columns))}"
        )


def find_one_file(patterns: list[str], label: str) -> Path:
    matches: list[Path] = []
    for pattern in patterns:
        matches.extend(Path(".").glob(pattern))

    matches = sorted(
        {
            path
            for path in matches
            if path.is_file() and not path.name.startswith("~$") and path.suffix.lower() in {".xlsx", ".xls"}
        }
    )

    if not matches:
        raise SystemExit(
            f"Не нашел файл {label} в текущей папке.\n"
            "Передайте файлы явно:\n"
            '  python3 compare_missing_assets.py "Host-AC.xlsx" "audit.xlsx"'
        )

    if len(matches) > 1: