"""Backfill native-control findings (MFA / secret / cert / PAT).

Revision ID: 256
Revises: 255
Create Date: 2026-06-05

PR-A (migration 255) backfilled the two *mirrored* controls (public
exposure, sign-in) into ``auditor_findings``. PR-B then made posture for
the 5 binary controls derive from open findings — but the 4 *native*
producers (MFA / secret rotation / cert expiry / PAT) only populate
findings when their daily sync next runs. Without this backfill, between
deploy and the next daily sync those controls read 0 open findings and
report a spurious PASS on a compliance surface. This seeds the current
violations so posture is correct from the first recompute.

Idempotent: ``INSERT IGNORE`` relies on ``UNIQUE(control_key, dedup_key)``
so a re-run (or overlap with a sync that already ran) is a no-op. dedup_key
mirrors ``findings_service.make_dedup_key`` (``|``-joined natural keys);
severity/title are best-effort — the first real sync overwrites them via
``_refresh_facts`` (only dedup_key + status matter for the cutover count).
"""
from __future__ import annotations

from alembic import op

revision = "256"
down_revision = "255"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ── MFA: active workforce member unenrolled on a provider ───────────
    op.execute(
        """
        INSERT IGNORE INTO auditor_findings (
            id, control_key, finding_type, dedup_key, subject_type,
            subject_ref, subject_display, person_id, severity, title,
            status, first_seen_at, last_seen_at, auto_resolved,
            created_at, updated_at
        )
        SELECT
            UUID(), 'mfa_coverage', 'mfa_not_enrolled',
            CONCAT(m.person_id, '|', m.provider),
            'person', COALESCE(m.provider_email, p.work_email),
            p.display_name, m.person_id,
            CASE WHEN m.is_enforced = 1 THEN 'high' ELSE 'medium' END,
            CONCAT('MFA not enrolled — ', m.provider, ' · ', p.display_name),
            'open', NOW(), NOW(), 0, NOW(), NOW()
        FROM auditor_mfa_status m
        JOIN identity_persons p ON p.id = m.person_id
        WHERE m.is_enrolled = 0 AND p.employment_status = 'active'
        """
    )

    # ── Secret rotation: overdue secrets ───────────────────────────────
    op.execute(
        """
        INSERT IGNORE INTO auditor_findings (
            id, control_key, finding_type, dedup_key, subject_type,
            subject_ref, subject_display, person_id, severity, title,
            status, first_seen_at, last_seen_at, auto_resolved,
            created_at, updated_at
        )
        SELECT
            UUID(), 'secret_rotation', 'secret_overdue',
            IF(CHAR_LENGTH(CONCAT(s.source, '|', s.secret_kind, '|',
                                  s.resource_id)) <= 255,
               CONCAT(s.source, '|', s.secret_kind, '|', s.resource_id),
               CONCAT('sha1:', SHA1(CONCAT(s.source, '|', s.secret_kind, '|',
                                           s.resource_id)))),
            'secret', s.resource_id,
            COALESCE(s.resource_name, s.resource_id), s.owner_person_id,
            'high',
            CONCAT('Secret overdue for rotation — ',
                   COALESCE(s.resource_name, s.resource_id)),
            'open', NOW(), NOW(), 0, NOW(), NOW()
        FROM auditor_secret_rotation_records s
        WHERE s.status = 'overdue'
        """
    )

    # ── Cert / domain / ICP: expired or expiring ≤60d ──────────────────
    op.execute(
        """
        INSERT IGNORE INTO auditor_findings (
            id, control_key, finding_type, dedup_key, subject_type,
            subject_ref, subject_display, person_id, severity, title,
            status, first_seen_at, last_seen_at, auto_resolved,
            created_at, updated_at
        )
        SELECT
            UUID(), 'cert_expiry_monitoring', 'cert_expiring',
            IF(CHAR_LENGTH(CONCAT(c.source, '|', c.asset_kind, '|',
                                  c.external_id)) <= 255,
               CONCAT(c.source, '|', c.asset_kind, '|', c.external_id),
               CONCAT('sha1:', SHA1(CONCAT(c.source, '|', c.asset_kind, '|',
                                           c.external_id)))),
            'cert', c.external_id, c.name, c.owner_person_id,
            CASE c.alert_level
                WHEN 'expired' THEN 'critical'
                WHEN '30d' THEN 'high'
                WHEN '60d' THEN 'medium'
            END,
            CASE WHEN c.alert_level = 'expired'
                 THEN CONCAT(c.asset_kind, ' expired — ', c.name)
                 ELSE CONCAT(c.asset_kind, ' expiring (', c.alert_level,
                             ') — ', c.name) END,
            'open', NOW(), NOW(), 0, NOW(), NOW()
        FROM auditor_cert_expiries c
        WHERE c.alert_level IN ('expired', '30d', '60d')
        """
    )

    # ── PAT audit: tokens carrying any risk flag ───────────────────────
    op.execute(
        """
        INSERT IGNORE INTO auditor_findings (
            id, control_key, finding_type, dedup_key, subject_type,
            subject_ref, subject_display, person_id, severity, title,
            status, first_seen_at, last_seen_at, auto_resolved,
            created_at, updated_at
        )
        SELECT
            UUID(), 'pat_audit', 'pat_risk',
            IF(CHAR_LENGTH(CONCAT(t.source, '|', t.kind, '|',
                                  t.token_external_id)) <= 255,
               CONCAT(t.source, '|', t.kind, '|', t.token_external_id),
               CONCAT('sha1:', SHA1(CONCAT(t.source, '|', t.kind, '|',
                                           t.token_external_id)))),
            'token', t.token_external_id,
            COALESCE(t.token_name, t.token_external_id), t.person_id,
            CASE WHEN JSON_CONTAINS(t.risk_flags, '"owner_left"')
                   OR JSON_CONTAINS(t.risk_flags, '"broad_admin_scope"')
                 THEN 'high' ELSE 'medium' END,
            CONCAT('Risky token — ',
                   COALESCE(t.token_name, t.token_external_id)),
            'open', NOW(), NOW(), 0, NOW(), NOW()
        FROM auditor_pat_audits t
        WHERE t.risk_flags IS NOT NULL AND JSON_LENGTH(t.risk_flags) > 0
        """
    )


def downgrade() -> None:
    op.execute(
        """
        DELETE FROM auditor_findings
        WHERE control_key IN (
            'mfa_coverage', 'secret_rotation',
            'cert_expiry_monitoring', 'pat_audit'
        )
        """
    )