---

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

Usage:
  python3 compare_missing_assets.py \
    "VMName Hypervisor ДОМ/Банк VMState Hostname IPAddress OS VMHost COD CreateDate VMwareToolsState CMDB_ENV CMDB_CRIT - Host-AC.xlsx" \
    "@Host Host.@UpdateTime Host.Fqdn Host.IpAddress - audit.xlsx" \
    missing_assets.xlsx
"""

from __future__ import annotations

import argparse
import ipaddress
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 is_active_state(value: object) -> bool:
    if pd.isna(value):
        return False
    return str(value).strip().lower() in ACTIVE_STATES


def main() -> None:
    parser = argparse.ArgumentParser(
        description="Create an Excel file with active Host-AC assets missing from audit."
    )
    parser.add_argument("host_ac_xlsx", help="Source Excel file: Host-AC.xlsx")
    parser.add_argument("audit_xlsx", help="Audit Excel file: audit.xlsx")
    parser.add_argument(
        "output_xlsx",
        nargs="?",
        default="missing_assets.xlsx",
        help="Output Excel file. Default: missing_assets.xlsx",
    )
    parser.add_argument(
        "--all-states",
        action="store_true",