"""Reconcile legacy Nessus-sourced assets without a pinned nessus_host_uuid

Revision ID: 028
Revises: 027
Create Date: 2026-06-02 14:00:00.000000

Tester feedback round 2026-06-02 (#3 INACTIVE not flipping on reduced
Nessus scan): the event-driven reconciliation in
`app.services.asset_lifecycle.reconcile_missing_from_sync` only inactivates
assets whose `nessus_host_uuid` is in `seen_ids` of a recent sync. Assets
created by older Nessus syncs that matched by IP or hostname (before the
UUID-backfill path was added) have `nessus_host_uuid IS NULL` and are
silently skipped. After a reduced scan they stay ACTIVE forever, which
contradicts the "sync-driven INACTIVE" promise.

This migration is the one-shot cleanup for the existing backlog (33 rows
in the test instance). New rows created after the 0006 commit (which
adds the diagnostic log + the `reconcile_legacy_nessus_assets` runtime
helper) are handled in code.

Idempotent: a row already INACTIVE matches the filter only when the
status check is omitted, so the body re-checks status before flipping.
Audit-logged via the same `_audit_asset_status` helper as the runtime
path so the audit trail is consistent.

Downgrade is a no-op — restoring a row to ACTIVE would require operator
intent, not a migration reversal.
"""
from alembic import op


revision = "028"
down_revision = "027"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # Use raw SQL through the migration's session_bind so the connection
    # is the same one alembic manages — no extra pool, no second engine.
    bind = op.get_bind()
    # Re-import the model in the migration context. Alembic env has
    # already imported Base.metadata via app.models.base; this import
    # pulls in the Asset / AuditLog / AssetSource / AssetStatus enums
    # we need for the audit insert.
    from app.models.asset import Asset, AssetSource, AssetStatus
    from app.services.asset_lifecycle import _audit_asset_status
    from sqlalchemy.orm import Session

    with Session(bind=bind) as db:
        legacy = (
            db.query(Asset)
            .filter(
                Asset.source == AssetSource.NESSUS,
                Asset.status == AssetStatus.ACTIVE,
                Asset.nessus_host_uuid.is_(None),
            )
            .all()
        )
        if not legacy:
            # Nothing to do — migration is a no-op on already-clean DBs.
            return
        for a in legacy:
            a.status = AssetStatus.INACTIVE
            _audit_asset_status(
                db,
                a,
                "active",
                "inactive",
                "legacy Nessus-sourced asset without pinned nessus_host_uuid — "
                "flipped by alembic migration 028 (reconcile_legacy_nessus_assets)",
            )
        db.commit()


def downgrade() -> None:
    # No-op. Restoring INACTIVE -> ACTIVE is an operator decision, not a
    # migration reversal. The legacy rows can be re-activated by a fresh
    # Nessus sync that reports them (event-driven revive in
    # reconcile_missing_from_sync).
    pass
