"""
Nessus → TrueVuln sync service.

Imports scan findings from Tenable Nessus and merges them onto existing
Wazuh-sourced vulnerabilities using `(cve_id, asset_id)` as the dedup key.
Non-CVE Nessus findings (EOL, compliance, weak ciphers, etc.) are stored
under a pseudo-CVE id `NESSUS-PLUGIN-{plugin_id}` so they show up in the
same Vulns list and inherit the existing workflow.

Asset matching cascade:
  1. assets.nessus_host_uuid (pinned after first match)
  2. assets.hostname  (case-insensitive)
  3. assets.ip_address  (string equality)
  4. If no match and config.auto_create_assets is True → create new asset

Backfill rule:
  When a Nessus scan finishes, any Vuln that previously listed `nessus`
  in `sources` but is missing from this scan run has `nessus` removed.
  If `sources` becomes empty AND status == open, mark as patched.
"""
from __future__ import annotations

import json
import logging
import re
from datetime import datetime
from typing import List, Optional, Tuple

from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Session

from app.integrations.nessus_client import NessusClient
from app.models.asset import Asset, AssetSource, AssetStatus
from app.models.scan import Scan, ScanStatus, ScanType
from app.models.setting import Setting
from app.models.vulnerability import (
    Vulnerability,
    VulnerabilitySeverity,
    VulnerabilityStatus,
)

logger = logging.getLogger(__name__)

SETTING_KEY = "nessus_config"
SOURCE_NAME = "nessus"


# ---------------------------------------------------------------
# Config loader
# ---------------------------------------------------------------
def load_nessus_config(db: Session) -> Optional[dict]:
    from app.auth.setting_crypto import read_setting_value
    raw = read_setting_value(db, SETTING_KEY)
    if not raw:
        return None
    try:
        return json.loads(raw)
    except (json.JSONDecodeError, TypeError):
        logger.warning("nessus_config is not valid JSON")
        return None


def _build_client(config: dict) -> NessusClient:
    return NessusClient(
        base_url=config["base_url"],
        access_key=config["access_key"],
        secret_key=config["secret_key"],
        verify_ssl=bool(config.get("verify_ssl", True)),
    )


# ---------------------------------------------------------------
# Asset matching
# ---------------------------------------------------------------
def _find_or_create_asset(
    db: Session,
    host_info: dict,
    auto_create: bool,
) -> Tuple[Optional[Asset], str]:
    """
    Returns (asset, match_method) where match_method is one of
    'uuid', 'hostname', 'ip', 'created', 'skipped'.
    """
    host_uuid = host_info.get("host_uuid") or host_info.get("uuid")
    hostname = (host_info.get("hostname") or host_info.get("host-fqdn") or "").strip()
    ip = (
        host_info.get("host_ip")
        or host_info.get("host-ip")
        or host_info.get("ip")
        or ""
    ).strip()

    # 1) Pinned UUID
    if host_uuid:
        a = db.query(Asset).filter(Asset.nessus_host_uuid == host_uuid).first()
        if a:
            # Backfill ip_address so the "Launch targeted Nessus scan" button
            # is enabled (gated on asset.ip_address on the frontend).
            if ip and not a.ip_address:
                a.ip_address = ip
            return a, "uuid"

    # 2) Hostname (case-insensitive) — try full + short forms so a
    #    Nessus FQDN like 'host01.umgebung.local' matches a Wazuh
    #    asset registered as just 'host01' (Wazuh-Agent name is
    #    typically the short hostname). Tester reported duplicate
    #    assets created from FQDN/short mismatches.
    short = hostname.split(".")[0] if hostname else ""
    candidates = []
    if hostname:
        candidates.append(hostname)
    if short and short != hostname:
        candidates.append(short)
    for candidate in candidates:
        a = (
            db.query(Asset)
            .filter(Asset.hostname.ilike(candidate))
            .first()
        )
        if a:
            if host_uuid and not a.nessus_host_uuid:
                a.nessus_host_uuid = host_uuid
            if ip and not a.ip_address:
                a.ip_address = ip
            return a, "hostname"

    # 2b) Reverse — asset stored as FQDN, Nessus reports short. Find
    #     assets whose hostname starts with 'short.' so we still
    #     consolidate the right way around.
    if short:
        a = (
            db.query(Asset)
            .filter(Asset.hostname.ilike(f"{short}.%"))
            .first()
        )
        if a:
            if host_uuid and not a.nessus_host_uuid:
                a.nessus_host_uuid = host_uuid
            if ip and not a.ip_address:
                a.ip_address = ip
            return a, "hostname-fqdn-prefix"

    # 3) IP
    if ip:
        a = db.query(Asset).filter(Asset.ip_address == ip).first()
        if a:
            if host_uuid and not a.nessus_host_uuid:
                a.nessus_host_uuid = host_uuid
            return a, "ip"

    # 4) Auto-create if configured. Store the SHORT hostname so a
    #    later Wazuh sync of the same box matches via candidates[].
    if auto_create and (hostname or ip):
        new_hostname = short or hostname or ip
        a = Asset(
            hostname=new_hostname,
            ip_address=ip or None,
            nessus_host_uuid=host_uuid,
            source=AssetSource.NESSUS,  # tagged for sync-driven reconciliation
            status=AssetStatus.ACTIVE,
        )
        db.add(a)
        db.flush()
        logger.info(
            "Nessus sync: auto-created asset %s (from FQDN '%s', ip %s)",
            a.hostname, hostname, a.ip_address,
        )
        return a, "created"

    return None, "skipped"


# ---------------------------------------------------------------
# Severity helpers
# ---------------------------------------------------------------
_SEV_RANK = {
    VulnerabilitySeverity.none: 0,
    VulnerabilitySeverity.low: 1,
    VulnerabilitySeverity.medium: 2,
    VulnerabilitySeverity.high: 3,
    VulnerabilitySeverity.critical: 4,
}


def _max_severity(a: VulnerabilitySeverity, b: VulnerabilitySeverity) -> VulnerabilitySeverity:
    return a if _SEV_RANK.get(a, 0) >= _SEV_RANK.get(b, 0) else b


# --- MS Office EOL consolidation ----------------------------------------
# Nessus reports a separate "Unsupported Version Detection" plugin for
# every Office sub-flavour (OSX MUI, Proofing tools, language packs). To
# match the endoflife.date sweep's naming and dedup the language-pack
# noise into a single base row, Office plugins share one pseudo-CVE per
# suite+year (`EOL-MS-OFFICE-2016`) and one normalised package name
# (`MS Office`).
_OFFICE_RE = re.compile(r"microsoft\s*office", re.I)
_OFFICE_YEAR_RE = re.compile(r"\b(20\d{2})\b")


# Catch-all Nessus solution strings that carry no product-specific fix.
# A cross-confirmed CVE can match several plugins; a generic plugin must
# not overwrite (or block) the specific plugin's real solution.
_GENERIC_REMEDIATION_MARKERS = (
    "install the patches listed below",
    "apply the appropriate patch",
    "apply the patches",
    "there is no known fix",
    "no known solution",
    "n/a",
    "refer to the vendor",
)


def _is_generic_remediation(text: Optional[str]) -> bool:
    """True for empty or catch-all remediation text (no specific fix)."""
    if not text or not text.strip():
        return True
    low = text.strip().lower()
    return any(m in low for m in _GENERIC_REMEDIATION_MARKERS)


def _office_year(plugin_name: str) -> Optional[str]:
    """Extract the 4-digit year from an Office plugin name, or None."""
    m = _OFFICE_YEAR_RE.search(plugin_name or "")
    return m.group(1) if m else None


def _office_pseudo_cve(plugin_name: str, plugin_id) -> str:
    """Return `EOL-MS-OFFICE-YYYY` for Office variants, else `EOL-NESSUS-{pid}`.

    NOTE: prefer `_slug_pseudo_cve(plugin_name, plugin_id, installed_version)`
    for the slug-based naming (`EOL-{SLUG}-{VERSION}`). Kept as a fallback
    when product-name resolution fails.
    """
    if _OFFICE_RE.search(plugin_name or "") and _office_year(plugin_name):
        return f"EOL-MS-OFFICE-{_office_year(plugin_name)}"
    return f"EOL-NESSUS-{plugin_id}"


def _slug_pseudo_cve(plugin_name: str, plugin_id, installed_version: Optional[str] = None) -> str:
    """Return product-name-based pseudo-CVE id (`EOL-MSSQLSERVER-...`) when
    we can resolve a slug, falling back to the legacy `EOL-NESSUS-{pid}`.

    Slug comes from `eol_service.resolve_product_slug(plugin_name)`. The
    trailing token is the installed version (sanitised) or the last 4
    digits of the plugin id when no version is present, so the row is
    still stable across re-syncs.
    """
    # Office is its own special case — keep the year-based id so the
    # language-pack dedup in `run_nessus_sync` keeps working.
    if _OFFICE_RE.search(plugin_name or "") and _office_year(plugin_name):
        return f"EOL-MS-OFFICE-{_office_year(plugin_name)}"
    # Lazy import: eol_service imports from a few places; avoid a hard
    # import cycle at module load.
    try:
        from app.services.eol_service import resolve_product_slug
        slug = resolve_product_slug(plugin_name)
    except Exception:
        slug = None
    if slug:
        if installed_version:
            safe_v = re.sub(r"[^A-Za-z0-9._-]", "_", str(installed_version))[:24] or "x"
            return f"EOL-{slug.upper()}-{safe_v}"[:50]
        # No version → last 4 digits of plugin id keeps it stable
        return f"EOL-{slug.upper()}-P{str(plugin_id)[-4:]}"[:50]
    return f"EOL-NESSUS-{plugin_id}"


def _normalise_office_pkg(pkg: str) -> str:
    """Collapse all Office sub-flavour strings to the unified `MS Office`."""
    if _OFFICE_RE.search(pkg or ""):
        return "MS Office"
    return pkg


def _upsert_nessus_eol(
    db: Session,
    *,
    asset: Asset,
    plugin_id,
    plugin_name: str,
    severity: VulnerabilitySeverity,
    cvss: Optional[float],
    installed_version: Optional[str],
    fixed_version: Optional[str],
    description: Optional[str],
    see_also: List[str],
    newly_created_vuln_ids: List[int],
) -> bool:
    """Create/refresh an EOL pseudo-vuln from a Nessus 'Unsupported Version
    Detection' plugin. Returns True when a new row was created.

    cve_id is `EOL-NESSUS-{plugin_id}` (or `EOL-MS-OFFICE-YYYY` for Office
    variants) so it counts as an EOL finding (Vulnerability.is_eol_finding
    matches the `EOL-` prefix) and shows in the dashboard's Newly EOL/EOS
    widget alongside endoflife.date findings. Dedup key is (cve_id,
    asset_id), stable across re-syncs.
    """
    cve_id = _slug_pseudo_cve(plugin_name, plugin_id, installed_version)
    # Homogenisation: once a plugin resolves to a proper product slug
    # (EOL-ADOBE-ACROBAT-..., EOL-MSSQLSERVER-...), drop any legacy
    # EOL-NESSUS-{plugin_id} row left over from before the slug was known.
    # Without this the old plugin-id row lingers next to the new named one
    # (tester: "nach neuem Scan noch PLUGIN ID UND NESSUS").
    if plugin_id and not cve_id.startswith("EOL-NESSUS-"):
        legacy_id = f"EOL-NESSUS-{plugin_id}"
        if legacy_id != cve_id:
            db.query(Vulnerability).filter(
                Vulnerability.cve_id == legacy_id,
                Vulnerability.asset_id == asset.id,
            ).delete(synchronize_session=False)
    # Strip the "... Unsupported Version Detection" suffix for a clean
    # PACKAGE column ("Microsoft SQL Server"). Office sub-flavours collapse
    # to "MS Office" so the language-pack noise stops multiplying rows.
    pkg = _normalise_office_pkg(
        re.sub(r"\s*Unsupported.*$", "", plugin_name).strip() or plugin_name
    )
    # Office: keep a clean title (no "OSX MUI (German)" suffix).
    title = re.sub(r"\s*OSX\s*MUI.*$", "", plugin_name, flags=re.I).strip() if _OFFICE_RE.search(plugin_name or "") else plugin_name

    existing = (
        db.query(Vulnerability)
        .filter(Vulnerability.cve_id == cve_id, Vulnerability.asset_id == asset.id)
        .first()
    )
    if existing:
        existing.severity = severity
        if cvss is not None:
            existing.cvss_score = cvss
        existing.title = (title[:500] or existing.title)
        existing.package_name = pkg[:255]
        existing.package_version = installed_version
        existing.fixed_version = fixed_version
        existing.description = description
        existing.add_source(SOURCE_NAME)
        existing.nessus_plugin_id = str(plugin_id) if plugin_id else existing.nessus_plugin_id
        from app.services.audit_events import reopen_if_patched
        reopen_if_patched(db, existing, reason="Nessus reports this finding on the host again", source="nessus_sync")
        # Resync bumps detected_at so the Newly EOL/EOS widget ranks the
        # freshest finding first (was stale before this fix).
        existing.detected_at = datetime.now()
        try:
            existing.refresh_scores()
        except Exception:
            pass
        return False

    try:
        new_vuln = Vulnerability(
            cve_id=cve_id,
            asset_id=asset.id,
            cvss_score=cvss,
            severity=severity,
            status=VulnerabilityStatus.open,
            title=title[:500] if title else None,
            package_name=pkg[:255] if pkg else None,
            package_version=installed_version,
            fixed_version=fixed_version,
            description=description,
            references=json.dumps(see_also) if see_also else None,
            detected_at=datetime.now(),
            sources=json.dumps([SOURCE_NAME]),
            first_detected_by=SOURCE_NAME,
            nessus_plugin_id=str(plugin_id) if plugin_id else None,
        )
        with db.begin_nested():
            db.add(new_vuln)
            db.flush()
        try:
            new_vuln.refresh_scores()
        except Exception:
            pass
        newly_created_vuln_ids.append(new_vuln.id)
        return True
    except IntegrityError:
        logger.debug(
            "Nessus EOL: duplicate insert race for %s on asset %s",
            cve_id, asset.id,
        )
        return False


# ---------------------------------------------------------------
# Core sync
# ---------------------------------------------------------------
def run_nessus_sync(
    db: Session,
    scan_ids: Optional[List[int]] = None,
) -> dict:
    """
    Pull findings from Nessus for the requested scans (or all configured
    default_scan_ids) and merge them into TrueVuln.

    Returns a stats dict suitable for logging + API response.
    """
    config = load_nessus_config(db)
    if not config:
        raise RuntimeError("nessus_config not set in settings")

    target_scan_ids: List[int] = (
        scan_ids
        or list(config.get("default_scan_ids") or [])
    )
    auto_create = bool(config.get("auto_create_assets", False))

    stats = {
        "scans_processed": 0,
        "hosts_synced": 0,
        "hosts_skipped": 0,
        "vulns_created": 0,
        "vulns_merged": 0,
        "vulns_unchanged": 0,
        "vulns_marked_patched": 0,
        "scores_overridden": 0,
        "non_cve_skipped": 0,
        "eol_created": 0,
        "unmatched_hosts": [],
        "errors": [],
    }

    newly_created_vuln_ids: List[int] = []
    seen_nessus_uuids: set = set()  # legacy uuid-keyed reconcile
    seen_asset_ids: set = set()     # robust id-keyed reconcile

    with _build_client(config) as client:
        # Auto-discover scans if no explicit IDs and no defaults
        if not target_scan_ids:
            scans = client.list_scans()
            target_scan_ids = [int(s["id"]) for s in scans if s.get("id")]

        for scan_id in target_scan_ids:
            try:
                stats["scans_processed"] += 1
                scan_detail = client.get_scan(scan_id)
            except Exception as e:
                stats["errors"].append(f"scan {scan_id}: {e}")
                logger.warning("Nessus sync: scan %s failed: %s", scan_id, e)
                continue

            hosts = scan_detail.get("hosts") or []
            logger.info("Nessus sync: scan %s has %d hosts", scan_id, len(hosts))

            for host_entry in hosts:
                host_id = host_entry.get("host_id")
                if host_id is None:
                    continue

                # Pull rich host detail
                try:
                    host = client.get_host(scan_id, host_id)
                except Exception as e:
                    stats["errors"].append(f"scan {scan_id} host {host_id}: {e}")
                    continue

                host_info = host.get("info") or {}
                # `info` carries hostname/host-fqdn/host-ip; merge with summary entry
                merged_info = {**host_entry, **host_info}

                asset, match_method = _find_or_create_asset(db, merged_info, auto_create)
                if asset is None:
                    stats["hosts_skipped"] += 1
                    stats["unmatched_hosts"].append({
                        "hostname": merged_info.get("hostname") or merged_info.get("host-fqdn"),
                        "ip": merged_info.get("host_ip") or merged_info.get("host-ip"),
                        "scan_id": scan_id,
                    })
                    continue
                stats["hosts_synced"] += 1
                # Track for sync-driven reconciliation. Two sets:
                #  - seen_nessus_uuids: legacy uuid-keyed path (kept).
                #  - seen_asset_ids: robust id-keyed path. A scan host
                #    whose host_info lacks host_uuid leaves the uuid set
                #    empty → the fail-open guard skipped EVERYTHING and
                #    nothing got inactivated (tester bug). Tracking the
                #    matched asset.id sidesteps the missing-uuid case.
                if asset.nessus_host_uuid:
                    seen_nessus_uuids.add(asset.nessus_host_uuid)
                if asset.id:
                    seen_asset_ids.add(asset.id)

                # OS Identification (Nessus plugin 11936 + host info fields)
                # Only fills when the asset row has nothing — Wazuh-sourced
                # assets already carry structured agent OS data and we do
                # not want to clobber that with Nessus's longer prose form.
                if not asset.operating_system:
                    os_label = (
                        merged_info.get("operating-system")
                        or merged_info.get("operating_system")
                        or merged_info.get("os")
                    )
                    if isinstance(os_label, list):
                        # Nessus sometimes returns multiple guesses — take the first
                        os_label = next((x for x in os_label if x), None)
                    if os_label:
                        os_label = str(os_label).strip().splitlines()[0][:255]
                        if os_label:
                            asset.operating_system = os_label

                # Record one Scan row per host so Nessus runs appear in
                # the Scan-Jobs history (previously only Wazuh autoscan
                # populated this table). Status updated to COMPLETED /
                # FAILED at the end of this host's loop iteration.
                scan_row = Scan(
                    asset_id=asset.id,
                    scan_type=ScanType.NESSUS,
                    status=ScanStatus.RUNNING,
                    started_at=datetime.now(),
                )
                db.add(scan_row)
                host_vulns_found = 0

                # Track cves we observe on THIS asset in THIS sync run.
                # Used for backfill (drop nessus source on disappeared findings).
                seen_cves_for_asset: set = set()

                for vuln_entry in host.get("vulnerabilities") or []:
                    plugin_id = vuln_entry.get("plugin_id")
                    severity_int = vuln_entry.get("severity") or 0
                    severity_label = NessusClient.plugin_severity_to_label(severity_int)
                    try:
                        severity = VulnerabilitySeverity(severity_label)
                    except ValueError:
                        severity = VulnerabilitySeverity.none

                    # Pull detailed plugin payload for CVE list + CVSS
                    try:
                        plugin_payload = client.get_plugin_output(scan_id, host_id, plugin_id)
                    except Exception as e:
                        logger.debug("Nessus plugin %s fetch failed: %s", plugin_id, e)
                        plugin_payload = {}

                    cve_list = NessusClient.extract_cves(plugin_payload) if plugin_payload else []
                    cvss = NessusClient.plugin_cvss(plugin_payload) if plugin_payload else None
                    # Extra enrichment from the Nessus plugin — Tenable VPR,
                    # exploit availability/maturity, prose description and
                    # remediation. All optional; missing fields stay None.
                    vpr_score = NessusClient.plugin_vpr_score(plugin_payload) if plugin_payload else None
                    exploit_avail = NessusClient.plugin_exploit_available(plugin_payload) if plugin_payload else None
                    exploit_mat = NessusClient.plugin_exploit_maturity(plugin_payload) if plugin_payload else None
                    fixed_version = NessusClient.plugin_fixed_version(plugin_payload) if plugin_payload else None
                    installed_version = NessusClient.plugin_installed_version(plugin_payload) if plugin_payload else None
                    n_description = NessusClient.plugin_description(plugin_payload) if plugin_payload else None
                    n_solution = NessusClient.plugin_solution(plugin_payload) if plugin_payload else None
                    n_see_also = NessusClient.plugin_see_also(plugin_payload) if plugin_payload else []

                    # Description = Nessus synopsis/description only. The
                    # solution text goes into the dedicated `remediation`
                    # column (rendered as its own section) instead of being
                    # glued onto the description.
                    full_description = n_description.strip() if n_description else None
                    remediation = n_solution.strip() if n_solution else None

                    if not cve_list:
                        # Most non-CVE plugins (compliance / cipher / info) stay
                        # skipped — pseudo-CVE imports were noisy. EXCEPTION: the
                        # "Unsupported Version Detection" family carries no CVE but
                        # is the only EOL signal on Nessus-only hosts (the
                        # endoflife.date sweep only covers Wazuh agents). Re-admit
                        # those as EOL pseudo-vulns so unsupported software still
                        # surfaces in the Newly EOL/EOS widget.
                        eol_name = vuln_entry.get("plugin_name") or ""
                        is_eol_plugin = (
                            NessusClient.plugin_unsupported_by_vendor(plugin_payload)
                            or "unsupported version" in eol_name.lower()
                            or "unsupported channel version" in eol_name.lower()
                        )
                        if not is_eol_plugin:
                            stats["non_cve_skipped"] += 1
                            continue
                        # Mark as seen THIS run so the source-backfill below
                        # doesn't immediately drop nessus + patch the row we
                        # just upserted (it keys on cve_id membership).
                        seen_cves_for_asset.add(
                            _slug_pseudo_cve(eol_name, plugin_id, installed_version)
                        )
                        if _upsert_nessus_eol(
                            db,
                            asset=asset,
                            plugin_id=plugin_id,
                            plugin_name=eol_name,
                            severity=severity,
                            cvss=cvss,
                            installed_version=installed_version,
                            fixed_version=fixed_version,
                            description=full_description,
                            see_also=n_see_also,
                            newly_created_vuln_ids=newly_created_vuln_ids,
                        ):
                            stats["eol_created"] += 1
                        continue

                    plugin_name = vuln_entry.get("plugin_name") or ""

                    # Catch-all Nessus plugins like "Patch Report" emit the
                    # same generic title + description for every CVE they
                    # touch. A CVE-specific plugin (e.g. "Apache Log4j
                    # 2.0-alpha1 < 2.25.4 ... (CVE-2026-34480)") is far
                    # better. Heuristic: the specific plugin's name contains
                    # the CVE-ID. We use this to decide whether to overwrite
                    # an existing title/description/package_name when merging.
                    def _is_specific(name: Optional[str], cve_id: str) -> bool:
                        return bool(name) and (cve_id.upper() in name.upper())

                    for cve_id in cve_list:
                        seen_cves_for_asset.add(cve_id)
                        new_is_specific = _is_specific(plugin_name, cve_id)
                        existing = (
                            db.query(Vulnerability)
                            .filter(
                                Vulnerability.cve_id == cve_id,
                                Vulnerability.asset_id == asset.id,
                            )
                            .first()
                        )

                        if existing:
                            changed = existing.add_source(SOURCE_NAME)
                            # Nessus wins over Wazuh on CVSS/severity, EXCEPT when
                            # any authoritative per-CVE source has already corrected
                            # the row via the override service. Vulnrichment / NVD /
                            # cvelistV5 are all per-CVE feeds with the real score;
                            # Nessus plugin-level CVSS is per-plugin and can bundle
                            # multiple CVEs (often overstating severity).
                            vulnrichment_locked = (
                                getattr(existing, "exploitation_source", None)
                                in ("vulnrichment", "nvd", "cvelistv5")
                            )
                            # Skip override only when Nessus has no CVSS at all
                            # (would wipe a good Wazuh value) or when Vulnrichment
                            # already pinned the authoritative score.
                            if (
                                cvss is not None
                                and existing.cvss_score != cvss
                                and not vulnrichment_locked
                            ):
                                existing.cvss_score = cvss
                                changed = True
                                stats["scores_overridden"] += 1
                            # Severity: trust Nessus when it provides one (not "none"),
                            # unless Vulnrichment has locked it via the override service.
                            if vulnrichment_locked:
                                pass  # keep Vulnrichment severity
                            elif severity and severity != VulnerabilitySeverity.none:
                                if existing.severity != severity:
                                    existing.severity = severity
                                    changed = True
                            else:
                                new_sev = _max_severity(existing.severity, severity)
                                if new_sev != existing.severity:
                                    existing.severity = new_sev
                                    changed = True
                            # plugin_id: prefer the CVE-specific plugin's ID
                            # (e.g. log4j scanner) over a generic catch-all
                            # like "Patch Report" once we see a better one.
                            existing_is_specific = _is_specific(existing.title, cve_id)
                            should_replace_plugin_meta = (
                                new_is_specific and not existing_is_specific
                            )
                            if plugin_id and (
                                existing.nessus_plugin_id != str(plugin_id)
                                and (not existing.nessus_plugin_id or should_replace_plugin_meta)
                            ):
                                existing.nessus_plugin_id = str(plugin_id)
                                changed = True
                            # Title / package_name / description: write when
                            # empty, OR when a CVE-specific plugin overrides
                            # a generic catch-all that landed first.
                            if plugin_name and (
                                not existing.package_name or should_replace_plugin_meta
                            ):
                                existing.package_name = plugin_name[:255]
                                changed = True
                            if plugin_name and (
                                not existing.title or should_replace_plugin_meta
                            ):
                                existing.title = plugin_name[:500]
                                changed = True
                            # Tenable VPR: always update to the latest score from
                            # this scan — Tenable recomputes it as threat data
                            # changes, so newer wins.
                            if vpr_score is not None and existing.nessus_vpr_score != vpr_score:
                                existing.nessus_vpr_score = vpr_score
                                changed = True
                            # exploit_available: only escalate (False → True),
                            # don't clobber a Wazuh-confirmed True back to False.
                            if exploit_avail is True and not existing.exploit_available:
                                existing.exploit_available = True
                                changed = True
                            # exploit_maturity: fill if empty; Nessus's values
                            # (Unproven / PoC / Functional / High) match ours.
                            if exploit_mat and not existing.exploit_maturity:
                                existing.exploit_maturity = exploit_mat[:50]
                            # fixed_version: fill if empty so the
                            # PATCH AVAILABLE badge can light up on
                            # rows Wazuh saw first without a fix.
                            if fixed_version and not existing.fixed_version:
                                existing.fixed_version = fixed_version
                                changed = True
                            # installed_version: fill from plugin output
                            # ("Installed version : 150.0.3") when empty.
                            # Wazuh usually sets this; Nessus-first rows
                            # have it blank until now.
                            if installed_version and not existing.package_version:
                                existing.package_version = installed_version
                                changed = True
                            # description: write when empty, OR when a CVE-
                            # specific plugin supplants a catch-all's generic
                            # "missing patches" text. Do not clobber a hand-
                            # written description (we cannot detect that, so
                            # only replace if the existing description came
                            # from a non-specific plugin).
                            if full_description and (
                                not existing.description or should_replace_plugin_meta
                            ):
                                existing.description = full_description
                                changed = True
                            # references: append Nessus see_also URLs if the
                            # column is empty.
                            if n_see_also and not existing.references:
                                existing.references = json.dumps(n_see_also)
                                changed = True
                            # Remediation precedence: a SPECIFIC solution
                            # always beats a generic/empty one; a generic
                            # solution never overwrites a specific one. Fixes
                            # cross-confirmed CVEs where a catch-all plugin
                            # ("Install the patches listed below.") clobbered
                            # the real plugin fix ("Upgrade to ... X.Y.Z").
                            if remediation and (
                                not existing.remediation
                                or (_is_generic_remediation(existing.remediation)
                                    and not _is_generic_remediation(remediation))
                            ):
                                existing.remediation = remediation
                                changed = True
                            # If previously marked patched but Nessus sees it again → reopen
                            from app.services.audit_events import reopen_if_patched
                            if reopen_if_patched(
                                    db, existing,
                                    reason="Nessus reports this finding on the host again",
                                    source="nessus_sync"):
                                changed = True
                            if changed:
                                existing.refresh_scores()
                                stats["vulns_merged"] += 1
                            else:
                                stats["vulns_unchanged"] += 1
                            continue

                        # Create new (Nessus-only finding).
                        # Use a savepoint so an IntegrityError (duplicate race) only
                        # rolls back this single insert — not the entire sync transaction.
                        try:
                            new_vuln = Vulnerability(
                                cve_id=cve_id,
                                asset_id=asset.id,
                                cvss_score=cvss,
                                severity=severity,
                                status=VulnerabilityStatus.open,
                                title=plugin_name[:500] if plugin_name else None,
                                # plugin_name is the affected-software label Nessus uses
                                # ("Mozilla Firefox < 150.0.2", "KB5087539: ..."). Mirror
                                # it into package_name so the Vulns table's PACKAGE
                                # column isn't blank for Nessus-only findings.
                                package_name=plugin_name[:255] if plugin_name else None,
                                package_version=installed_version,
                                description=full_description,
                                remediation=remediation,
                                references=json.dumps(n_see_also) if n_see_also else None,
                                exploit_available=bool(exploit_avail) if exploit_avail is not None else False,
                                exploit_maturity=exploit_mat[:50] if exploit_mat else None,
                                fixed_version=fixed_version,
                                nessus_vpr_score=vpr_score,
                                detected_at=datetime.now(),
                                sources=json.dumps([SOURCE_NAME]),
                                first_detected_by=SOURCE_NAME,
                                nessus_plugin_id=str(plugin_id) if plugin_id else None,
                            )
                            with db.begin_nested():
                                db.add(new_vuln)
                                db.flush()
                            # Pull canonical CVE metadata from siblings so
                            # the new Nessus-only row converges with existing
                            # Wazuh + Nessus rows of the same CVE on other
                            # assets — fixes "same CVE, different CVSS"
                            # sort drift.
                            try:
                                from app.services.vuln_override_service import apply_canonical_from_siblings
                                apply_canonical_from_siblings(db, new_vuln)
                            except Exception as e:
                                logger.warning(
                                    "canonical-inherit on new nessus vuln %s failed: %s",
                                    new_vuln.cve_id, e,
                                )
                            new_vuln.refresh_scores()
                            newly_created_vuln_ids.append(new_vuln.id)
                            stats["vulns_created"] += 1
                        except IntegrityError:
                            # Race: another request already inserted the same (cve_id, asset_id).
                            # Savepoint was rolled back; outer transaction is intact.
                            logger.debug(
                                "Nessus sync: duplicate insert race for %s on asset %s",
                                cve_id, asset.id,
                            )
                            continue

                # Backfill: any vuln on this asset that PREVIOUSLY had `nessus`
                # in sources but is not in seen_cves_for_asset → drop nessus.
                # If sources empties out → patched.
                #
                # Safety: if this host had zero vulnerabilities AND zero raw
                # findings reported by Nessus this run, the response is most
                # likely a partial scan or an API hiccup (Nessus rarely
                # reports a host as truly clean). Skip the backfill to avoid
                # mass-patching every prior Nessus finding on this asset.
                raw_findings = host.get("vulnerabilities") or []
                if not seen_cves_for_asset and not raw_findings:
                    logger.warning(
                        "Nessus reported 0 findings for host %s in scan %s — "
                        "skipping backfill to avoid mass-patching from a "
                        "transient empty response.",
                        asset.hostname, scan_id,
                    )
                    asset.last_scan = datetime.now()
                    scan_row.status = ScanStatus.COMPLETED
                    scan_row.completed_at = datetime.now()
                    scan_row.vulnerabilities_found = 0
                    scan_row.error_message = "Host returned 0 findings (skipped backfill)"
                    continue
                stale_nessus_vulns = (
                    db.query(Vulnerability)
                    .filter(
                        Vulnerability.asset_id == asset.id,
                        Vulnerability.status == VulnerabilityStatus.open,
                        Vulnerability.sources.contains('"nessus"'),
                    )
                    .all()
                )
                for v in stale_nessus_vulns:
                    if v.cve_id in seen_cves_for_asset:
                        continue
                    v.remove_source(SOURCE_NAME)
                    if not v.source_list:
                        old_status = v.status
                        v.status = VulnerabilityStatus.patched
                        v.patched_at = datetime.now()
                        stats["vulns_marked_patched"] += 1
                        # Audit trail — revisionssicher: who/when/how.
                        # user_id=None marks the change as automated.
                        try:
                            from app.routers.vulnerabilities import log_vulnerability_change
                            log_vulnerability_change(
                                db, None, v.id, old_status, v.status,
                                reason=f"Nessus rescan {scan_id} no longer reports this CVE on {asset.hostname}",
                                cve_id=v.cve_id,
                                source="nessus_sync",
                            )
                        except Exception as e:
                            logger.warning(
                                "audit log for auto-patch failed (vuln_id=%s): %s",
                                v.id, e,
                            )

                asset.last_scan = datetime.now()
                # Close out the per-host scan row.
                scan_row.status = ScanStatus.COMPLETED
                scan_row.completed_at = datetime.now()
                scan_row.vulnerabilities_found = len(seen_cves_for_asset)

        # Office variant dedup sweep: pre-existing EOL-NESSUS-{plugin_id}
        # rows for Office sub-flavours (OSX MUI / Proofing tools / language
        # packs) get collapsed into the unified EOL-MS-OFFICE-{year} row on
        # the same asset. Marked patched + audit-logged.
        try:
            from app.routers.vulnerabilities import log_vulnerability_change
            office_anchor_ids = {
                (r.asset_id, r.cve_id) for r in (
                    db.query(Vulnerability)
                    .filter(Vulnerability.cve_id.like("EOL-MS-OFFICE-%"))
                    .all()
                )
            }
            office_plugin_rows = (
                db.query(Vulnerability)
                .filter(
                    Vulnerability.cve_id.like("EOL-NESSUS-%"),
                    Vulnerability.title.ilike("%microsoft%office%"),
                )
                .all()
            )
            deduped = 0
            for v in office_plugin_rows:
                # Pick the year from the plugin-id row's title and check
                # if a unified EOL-MS-OFFICE-{year} row exists on the same
                # asset.
                y = _OFFICE_YEAR_RE.search(v.title or "")
                if not y:
                    continue
                anchor = (v.asset_id, f"EOL-MS-OFFICE-{y.group(1)}")
                if anchor in office_anchor_ids and v.status == VulnerabilityStatus.open:
                    old_status = v.status
                    v.status = VulnerabilityStatus.patched
                    v.patched_at = datetime.now()
                    deduped += 1
                    try:
                        log_vulnerability_change(
                            db, None, v.id, old_status, v.status,
                            reason=f"Collapsed Office sub-flavour into {anchor[1]}",
                            cve_id=v.cve_id,
                            source="nessus_sync",
                        )
                    except Exception as e:
                        logger.warning("audit log for office-dedup failed: %s", e)
            if deduped:
                logger.info("Nessus sync: collapsed %d Office sub-flavour EOL rows", deduped)
        except Exception as e:
            logger.warning("Nessus sync: office dedup sweep failed (non-fatal): %s", e)

        # Sync-driven reconciliation: any ACTIVE NESSUS-sourced asset that
        # did NOT appear in this sync (no longer in any scanned scope) is
        # flipped to INACTIVE. Vice-versa: INACTIVE NESSUS assets that
        # re-appeared are flipped back to ACTIVE. Audit-logged.
        try:
            from app.services.asset_lifecycle import reconcile_nessus_by_seen_ids
            recon = reconcile_nessus_by_seen_ids(
                db,
                seen_asset_ids=seen_asset_ids,
                reason=f"not in latest Nessus scan (scans={target_scan_ids})",
            )
            stats["assets_inactivated"] = recon["inactivated"]
            stats["assets_reactivated"] = recon["reactivated"]
            logger.info(
                "Nessus sync reconcile: %d seen, %d inactivated, %d reactivated, %d candidates",
                len(seen_asset_ids), recon["inactivated"], recon["reactivated"],
                recon.get("candidates", 0),
            )
        except Exception as e:
            logger.warning("Nessus sync: asset reconciliation failed (non-fatal): %s", e)

        db.commit()

    # Revisionssicher: initial VULNERABILITY_DETECTED audit event per new
    # finding (previously the audit trail only began at the first status
    # change).
    if newly_created_vuln_ids:
        try:
            from app.services.audit_events import audit_new_vulnerabilities
            audit_new_vulnerabilities(db, newly_created_vuln_ids, source="nessus")
            db.commit()
        except Exception as e:
            logger.warning("Nessus sync: detected-audit failed (non-fatal): %s", e)

    # Best-effort enrichment + notification (re-use Wazuh path)
    if newly_created_vuln_ids:
        try:
            from app.services.email_service import dispatch_new_vuln_notifications
            from app.services.enrichment_service import enrich_vulnerabilities

            fresh = (
                db.query(Vulnerability)
                .filter(Vulnerability.id.in_(newly_created_vuln_ids))
                .all()
            )
            try:
                # NVD date backfill deferred to the nightly enrichment job
                # (rate-limited — would stall the sync).
                enrich_vulnerabilities(db, fresh, use_nvd_dates=False)
            except Exception as e:
                logger.warning("Nessus sync: enrichment failed (non-fatal): %s", e)

            stats["notifications"] = dispatch_new_vuln_notifications(db, fresh)
        except Exception as e:
            logger.warning("Nessus sync: post-processing failed: %s", e)

    logger.info(
        "Nessus sync done: %d hosts, %d created, %d EOL, %d merged, %d scores overridden, %d patched, %d unmatched",
        stats["hosts_synced"], stats["vulns_created"], stats["eol_created"],
        stats["vulns_merged"], stats["scores_overridden"],
        stats["vulns_marked_patched"], len(stats["unmatched_hosts"]),
    )
    return stats


def reconcile_legacy_nessus_assets(db: Session) -> dict:
    """One-shot helper for testers: flip ACTIVE NESSUS-sourced assets that
    have no `nessus_host_uuid` pinned (legacy rows from before the
    reconcile path was hardened) to INACTIVE.

    These rows were created by older Nessus syncs that matched by IP
    only, so they never get a UUID and are silently skipped by
    `reconcile_missing_from_sync`. Without this, a reduced scan leaves
    them all ACTIVE.

    Returns {"inactivated": int, "scanned": int}.

    Safe to run multiple times. Logs an audit entry for each row flipped.
    """
    from app.services.asset_lifecycle import _audit_asset_status
    from app.models.asset import AssetSource, AssetStatus

    legacy = (
        db.query(Asset)
        .filter(
            Asset.source == AssetSource.NESSUS,
            Asset.status == AssetStatus.ACTIVE,
            Asset.nessus_host_uuid.is_(None),
        )
        .all()
    )
    stats = {"scanned": len(legacy), "inactivated": 0}
    for a in legacy:
        a.status = AssetStatus.INACTIVE
        _audit_asset_status(
            db, a, "active", "inactive",
            "legacy Nessus-sourced asset without pinned nessus_host_uuid — "
            "cannot be reconciled event-driven; flipped via reconcile_legacy_nessus_assets",
        )
        stats["inactivated"] += 1
    if stats["inactivated"]:
        db.commit()
    logger.info(
        "nessus legacy reconcile: %d inactivated (of %d legacy ACTIVE rows)",
        stats["inactivated"], stats["scanned"],
    )
    return stats
