<?php
/**
 * AURA FARMING — Etapa 24
 * Administração, auditoria completa, alertas automáticos, monitoramento de APIs e score de risco.
 */

if (!defined('STAGE24_AUDIT_VERSION')) define('STAGE24_AUDIT_VERSION', '24.0');

function afStage24TableExists(PDO $pdo, string $table): bool
{
    static $cache = [];
    if (isset($cache[$table])) return $cache[$table];
    try {
        $st = $pdo->prepare('SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? LIMIT 1');
        $st->execute([$table]);
        return $cache[$table] = (bool)$st->fetchColumn();
    } catch (Throwable $e) {
        return $cache[$table] = false;
    }
}

function afStage24Json(?array $data): ?string
{
    if (!$data) return null;
    return json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
}

function afStage24Hash(string $value): string
{
    $salt = defined('SECURITY_HASH_SALT') ? (string)SECURITY_HASH_SALT : 'aura-stage24';
    return hash_hmac('sha256', trim($value), $salt);
}

function afStage24IpHash(): ?string
{
    $ip = function_exists('clientIp') ? clientIp() : (string)($_SERVER['REMOTE_ADDR'] ?? '');
    return $ip !== '' ? afStage24Hash($ip) : null;
}

function afStage24UaHash(): ?string
{
    $ua = substr((string)($_SERVER['HTTP_USER_AGENT'] ?? ''), 0, 1000);
    return $ua !== '' ? afStage24Hash($ua) : null;
}

function afStage24DeviceUuid(): ?string
{
    try {
        if (function_exists('deviceUuid')) return deviceUuid();
    } catch (Throwable $e) {}
    $h = (string)($_SERVER['HTTP_X_DEVICE_ID'] ?? ($_COOKIE[defined('DEVICE_COOKIE_NAME') ? DEVICE_COOKIE_NAME : 'af_device'] ?? ''));
    $h = preg_replace('/[^a-zA-Z0-9\-]/', '', $h);
    return $h ?: null;
}

function afStage24Uuid(): string
{
    if (function_exists('uuidV4')) return uuidV4();
    $data = random_bytes(16);
    $data[6] = chr((ord($data[6]) & 0x0f) | 0x40);
    $data[8] = chr((ord($data[8]) & 0x3f) | 0x80);
    return vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($data), 4));
}

function afStage24Config(PDO $pdo, string $key, mixed $default = null): mixed
{
    try {
        if (!afStage24TableExists($pdo, 'af_configuracoes')) return $default;
        $st = $pdo->prepare('SELECT valor,tipo FROM af_configuracoes WHERE chave=? LIMIT 1');
        $st->execute([$key]);
        $row = $st->fetch();
        if (!$row) return $default;
        $value = (string)$row['valor'];
        return match ((string)($row['tipo'] ?? 'string')) {
            'int' => (int)$value,
            'float' => (float)$value,
            'bool' => in_array(strtolower($value), ['1','true','sim','on','yes'], true),
            'json' => json_decode($value, true) ?: $default,
            default => $value,
        };
    } catch (Throwable $e) { return $default; }
}

function afStage24CurrentActor(): array
{
    $actor = ['type' => 'system', 'user_id' => null, 'admin_id' => null];
    if (!empty($_SESSION['admin_usuario_id'])) {
        $actor['type'] = 'admin';
        $actor['admin_id'] = (int)$_SESSION['admin_usuario_id'];
    } elseif (!empty($_SESSION['usuario_id'])) {
        $actor['type'] = 'user';
        $actor['user_id'] = (int)$_SESSION['usuario_id'];
    }
    return $actor;
}

function afStage24AuditLog(PDO $pdo, string $actionKey, string $entityType = '', int|string|null $entityId = null, array $before = [], array $after = [], array $context = []): void
{
    if (!afStage24TableExists($pdo, 'af_audit_logs')) return;
    $actor = afStage24CurrentActor();
    if (!empty($context['actor_type'])) $actor['type'] = (string)$context['actor_type'];
    if (array_key_exists('actor_user_id', $context)) $actor['user_id'] = $context['actor_user_id'] ? (int)$context['actor_user_id'] : null;
    if (array_key_exists('actor_admin_id', $context)) $actor['admin_id'] = $context['actor_admin_id'] ? (int)$context['actor_admin_id'] : null;
    $subjectId = isset($context['subject_user_id']) ? (int)$context['subject_user_id'] : null;
    $severity = (string)($context['severity'] ?? 'info');
    if (!in_array($severity, ['info','low','medium','high','critical'], true)) $severity = 'info';
    try {
        $st = $pdo->prepare('INSERT INTO af_audit_logs
            (event_uuid,actor_type,actor_user_id,actor_admin_id,subject_user_id,action_key,entity_type,entity_id,severity,source,ip_hash,user_agent_hash,device_uuid,before_json,after_json,context_json,created_at)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW())');
        $st->execute([
            afStage24Uuid(),
            substr($actor['type'], 0, 20),
            $actor['user_id'] ?: null,
            $actor['admin_id'] ?: null,
            $subjectId ?: null,
            substr($actionKey, 0, 120),
            $entityType !== '' ? substr($entityType, 0, 80) : null,
            $entityId !== null ? substr((string)$entityId, 0, 80) : null,
            $severity,
            substr((string)($context['source'] ?? ($_SERVER['SCRIPT_NAME'] ?? 'system')), 0, 100),
            afStage24IpHash(),
            afStage24UaHash(),
            afStage24DeviceUuid(),
            afStage24Json($before),
            afStage24Json($after),
            afStage24Json($context),
        ]);
    } catch (Throwable $e) {
        if (defined('APP_DEBUG') && APP_DEBUG) error_log('[AURA-STAGE24-AUDIT] '.$e->getMessage());
    }
}

function afStage24RiskLevel(int $score): string
{
    return match (true) {
        $score >= 81 => 'critical',
        $score >= 51 => 'risk',
        $score >= 21 => 'attention',
        default => 'safe',
    };
}

function afStage24SecurityAlert(PDO $pdo, string $type, string $severity, string $title, string $message = '', ?int $userId = null, ?int $subjectUserId = null, string $source = 'system', ?string $entityType = null, int|string|null $entityId = null, array $details = [], int $riskPoints = 0): int
{
    if (!afStage24TableExists($pdo, 'af_security_alerts')) return 0;
    $severity = in_array($severity, ['info','low','medium','high','critical'], true) ? $severity : 'medium';
    $riskPoints = max(0, min(100, $riskPoints));
    $repeat = max(5, (int)afStage24Config($pdo, 'stage24_alert_repeat_minutes', 60));
    try {
        $st = $pdo->prepare("SELECT id,occurrence_count FROM af_security_alerts
            WHERE alert_type=? AND COALESCE(user_id,0)=? AND COALESCE(subject_user_id,0)=? AND COALESCE(entity_type,'')=? AND COALESCE(entity_id,'')=?
            AND status IN ('open','triage','in_review') AND last_seen_at>=DATE_SUB(NOW(),INTERVAL {$repeat} MINUTE)
            ORDER BY id DESC LIMIT 1");
        $st->execute([$type, $userId ?: 0, $subjectUserId ?: 0, $entityType ?: '', $entityId !== null ? (string)$entityId : '']);
        $existing = $st->fetch();
        if ($existing) {
            $id = (int)$existing['id'];
            $pdo->prepare('UPDATE af_security_alerts SET occurrence_count=occurrence_count+1,last_seen_at=NOW(),severity=?,message=?,risk_points=GREATEST(risk_points,?),details_json=?,updated_at=NOW() WHERE id=?')
                ->execute([$severity, $message ?: null, $riskPoints, afStage24Json($details), $id]);
        } else {
            $st = $pdo->prepare('INSERT INTO af_security_alerts
                (alert_uuid,user_id,subject_user_id,alert_type,severity,status,title,message,source,entity_type,entity_id,risk_points,ip_hash,user_agent_hash,device_uuid,details_json,first_seen_at,last_seen_at,created_at,updated_at)
                VALUES (?,?,?,?,?,\'open\',?,?,?,?,?,?,?,?,?,?,NOW(),NOW(),NOW(),NOW())');
            $st->execute([
                afStage24Uuid(), $userId ?: null, $subjectUserId ?: null, substr($type,0,100), $severity,
                substr($title,0,180), $message ?: null, substr($source,0,100), $entityType ?: null, $entityId !== null ? substr((string)$entityId,0,80) : null,
                $riskPoints, afStage24IpHash(), afStage24UaHash(), afStage24DeviceUuid(), afStage24Json($details),
            ]);
            $id = (int)$pdo->lastInsertId();
        }

        afStage24AuditLog($pdo, 'security_alert_'.$type, $entityType ?: 'security_alert', $entityId ?? $id, [], ['alert_id'=>$id,'severity'=>$severity,'risk_points'=>$riskPoints], ['severity'=>$severity,'source'=>$source,'subject_user_id'=>$subjectUserId ?: $userId]);
        if ($subjectUserId || $userId) afStage24RefreshUserRisk($pdo, (int)($subjectUserId ?: $userId));

        if (($subjectUserId || $userId) && function_exists('afGuardianDashboardCreateAlert') && afStage24Config($pdo, 'stage24_minor_alerts_to_guardian', true)) {
            $minorId = (int)($subjectUserId ?: $userId);
            try {
                $st = $pdo->prepare("SELECT tipo_conta,idade_declarada FROM af_user_age_profiles WHERE usuario_id=? LIMIT 1");
                $st->execute([$minorId]);
                $age = $st->fetch();
                $isMinor = $age && ((string)$age['tipo_conta'] === 'minor' || (int)($age['idade_declarada'] ?? 99) < 18);
                if ($isMinor && in_array($severity, ['high','critical'], true)) {
                    afGuardianDashboardCreateAlert($pdo, $minorId, 'stage24_'.$type, $severity === 'critical' ? 'critical' : 'warning', $title, $message ?: 'Um evento de segurança foi registrado.', $details, $entityType ?: 'security_alert', $entityId !== null ? (int)$entityId : null);
                }
            } catch (Throwable $e) {}
        }
        return $id;
    } catch (Throwable $e) {
        if (defined('APP_DEBUG') && APP_DEBUG) error_log('[AURA-STAGE24-ALERT] '.$e->getMessage());
        return 0;
    }
}

function afStage24RefreshUserRisk(PDO $pdo, int $userId): array
{
    if ($userId <= 0 || !afStage24TableExists($pdo, 'af_user_risk_scores')) return ['score'=>0,'level'=>'safe'];
    try {
        $open = 0; $critical = 0; $apiDenied = 0; $minorDenied = 0; $camera = 0; $baseRisk = 0;
        if (afStage24TableExists($pdo, 'af_security_alerts')) {
            $st = $pdo->prepare("SELECT COUNT(*) total, SUM(severity='critical') critical, COALESCE(SUM(risk_points),0) risk FROM af_security_alerts WHERE (user_id=? OR subject_user_id=?) AND status IN ('open','triage','in_review')");
            $st->execute([$userId,$userId]);
            $r = $st->fetch() ?: [];
            $open = (int)($r['total'] ?? 0); $critical = (int)($r['critical'] ?? 0); $baseRisk = (int)($r['risk'] ?? 0);
        }
        if (afStage24TableExists($pdo, 'af_api_access_logs')) {
            $st = $pdo->prepare("SELECT COUNT(*) FROM af_api_access_logs WHERE user_id=? AND allowed=0 AND created_at>=DATE_SUB(NOW(),INTERVAL 24 HOUR)");
            $st->execute([$userId]); $apiDenied = (int)$st->fetchColumn();
        }
        if (afStage24TableExists($pdo, 'af_minor_policy_decision_logs')) {
            $st = $pdo->prepare("SELECT COUNT(*) FROM af_minor_policy_decision_logs WHERE usuario_id=? AND decision_key='deny' AND criado_em>=DATE_SUB(NOW(),INTERVAL 24 HOUR)");
            $st->execute([$userId]); $minorDenied = (int)$st->fetchColumn();
        }
        if (afStage24TableExists($pdo, 'af_safe_camera_events')) {
            $st = $pdo->prepare("SELECT COUNT(*) FROM af_safe_camera_events WHERE usuario_id=? AND event_type IN ('multiple_people','poor_lighting','camera_lost','privacy_unavailable') AND criado_em>=DATE_SUB(NOW(),INTERVAL 24 HOUR)");
            $st->execute([$userId]); $camera = (int)$st->fetchColumn();
        }
        $score = min(100, $baseRisk + ($critical * 15) + min(20, $apiDenied * 3) + min(20, $minorDenied * 2) + min(25, $camera * 5));
        $level = afStage24RiskLevel($score);
        $flags = ['open_alerts'=>$open,'critical_alerts'=>$critical,'api_denials_24h'=>$apiDenied,'minor_policy_denials_24h'=>$minorDenied,'camera_incidents_24h'=>$camera];
        $enforcement = 'normal';
        $monitor = (int)afStage24Config($pdo, 'stage24_risk_monitor_threshold', 21);
        $restrict = (int)afStage24Config($pdo, 'stage24_risk_restrict_threshold', 65);
        $block = (int)afStage24Config($pdo, 'stage24_risk_block_threshold', 90);
        if ($score >= $block) $enforcement = 'blocked';
        elseif ($score >= $restrict) $enforcement = 'restricted';
        elseif ($score >= $monitor) $enforcement = 'monitor';

        $pdo->prepare('INSERT INTO af_user_risk_scores(user_id,risk_score,risk_level,enforcement_status,open_alerts,critical_alerts,api_denials_24h,minor_policy_denials_24h,camera_incidents_24h,behavior_flags_json,last_evaluated_at,updated_at,created_at)
            VALUES (?,?,?,?,?,?,?,?,?,?,NOW(),NOW(),NOW())
            ON DUPLICATE KEY UPDATE risk_score=VALUES(risk_score),risk_level=VALUES(risk_level),enforcement_status=VALUES(enforcement_status),open_alerts=VALUES(open_alerts),critical_alerts=VALUES(critical_alerts),api_denials_24h=VALUES(api_denials_24h),minor_policy_denials_24h=VALUES(minor_policy_denials_24h),camera_incidents_24h=VALUES(camera_incidents_24h),behavior_flags_json=VALUES(behavior_flags_json),last_evaluated_at=NOW(),updated_at=NOW()')
            ->execute([$userId,$score,$level,$enforcement,$open,$critical,$apiDenied,$minorDenied,$camera,afStage24Json($flags)]);

        if (afStage24TableExists($pdo, 'af_usuarios')) {
            $pdo->prepare("UPDATE af_usuarios SET stage24_risk_score=?,stage24_risk_level=?,stage24_last_audit_at=NOW(),risco_conta=GREATEST(risco_conta,?) WHERE id=?")
                ->execute([$score,$level,$score,$userId]);
            if ($enforcement === 'restricted') {
                $pdo->prepare("UPDATE af_usuarios SET seguranca_status='restrito',suspenso_ate=IF(suspenso_ate IS NULL OR suspenso_ate<NOW(),DATE_ADD(NOW(),INTERVAL 24 HOUR),suspenso_ate) WHERE id=? AND seguranca_status<>'banido'")->execute([$userId]);
            } elseif ($enforcement === 'blocked') {
                $pdo->prepare("UPDATE af_usuarios SET status=2,seguranca_status='restrito',suspenso_ate=DATE_ADD(NOW(),INTERVAL 24 HOUR) WHERE id=? AND seguranca_status<>'banido'")->execute([$userId]);
            } elseif ($enforcement === 'monitor') {
                $pdo->prepare("UPDATE af_usuarios SET seguranca_status='monitorado' WHERE id=? AND seguranca_status='normal'")->execute([$userId]);
            }
        }
        return ['score'=>$score,'level'=>$level,'enforcement'=>$enforcement] + $flags;
    } catch (Throwable $e) {
        if (defined('APP_DEBUG') && APP_DEBUG) error_log('[AURA-STAGE24-RISK] '.$e->getMessage());
        return ['score'=>0,'level'=>'safe'];
    }
}

function afStage24RegisterApiContext(?int $userId = null, ?string $feature = null, ?bool $allowed = null, ?string $denialCode = null, array $details = []): void
{
    $GLOBALS['af_stage24_api_context'] = array_merge($GLOBALS['af_stage24_api_context'] ?? [], [
        'user_id' => $userId,
        'feature_key' => $feature,
        'allowed' => $allowed,
        'denial_code' => $denialCode,
        'details' => $details,
    ]);
}

function afStage24ApiRouteGroup(string $endpoint): string
{
    $parts = array_values(array_filter(explode('/', trim($endpoint, '/'))));
    $idx = array_search('v1', $parts, true);
    if ($idx !== false && isset($parts[$idx+1])) return substr((string)$parts[$idx+1], 0, 80);
    return isset($parts[1]) ? substr((string)$parts[1], 0, 80) : 'api';
}

function afStage24LogApiShutdown(PDO $pdo): void
{
    if (!afStage24Config($pdo, 'stage24_api_logging', true) || !afStage24TableExists($pdo, 'af_api_access_logs')) return;
    $ctx = $GLOBALS['af_stage24_api_context'] ?? [];
    $status = (int)(http_response_code() ?: 200);
    $allowed = array_key_exists('allowed', $ctx) ? (bool)$ctx['allowed'] : ($status < 400);
    $endpoint = substr((string)($_SERVER['REQUEST_URI'] ?? ($_SERVER['SCRIPT_NAME'] ?? 'api')), 0, 255);
    $method = substr((string)($_SERVER['REQUEST_METHOD'] ?? 'GET'), 0, 12);
    $userId = isset($ctx['user_id']) ? (int)$ctx['user_id'] : null;
    $adminId = isset($ctx['admin_user_id']) ? (int)$ctx['admin_user_id'] : null;
    $raw = function_exists('bearerToken') ? bearerToken() : '';
    try {
        if (!$userId && $raw !== '') {
            $st = $pdo->prepare('SELECT usuario_id FROM af_sessoes WHERE token_hash=? LIMIT 1');
            $st->execute([hash('sha256', $raw)]); $userId = (int)($st->fetchColumn() ?: 0) ?: null;
        }
        if (!$adminId && $raw !== '') {
            $st = $pdo->prepare('SELECT admin_user_id FROM af_admin_sessions WHERE token_hash=? LIMIT 1');
            $st->execute([hash('sha256', $raw)]); $adminId = (int)($st->fetchColumn() ?: 0) ?: null;
        }
    } catch (Throwable $e) {}
    $started = (float)($GLOBALS['af_stage24_api_started'] ?? microtime(true));
    $duration = max(0, (int)round((microtime(true) - $started) * 1000));
    $requestHash = afStage24Hash($method.'|'.$endpoint.'|'.(string)($_SERVER['QUERY_STRING'] ?? ''));
    try {
        $pdo->prepare('INSERT INTO af_api_access_logs(request_uuid,user_id,admin_user_id,method,endpoint,route_group,feature_key,http_status,allowed,denial_code,ip_hash,user_agent_hash,device_uuid,request_hash,duration_ms,details_json,created_at)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW())')
            ->execute([
                afStage24Uuid(), $userId ?: null, $adminId ?: null, $method, $endpoint, afStage24ApiRouteGroup($endpoint),
                isset($ctx['feature_key']) ? substr((string)$ctx['feature_key'],0,100) : null,
                $status, $allowed ? 1 : 0, isset($ctx['denial_code']) ? substr((string)$ctx['denial_code'],0,100) : null,
                afStage24IpHash(), afStage24UaHash(), afStage24DeviceUuid(), $requestHash, $duration, afStage24Json($ctx['details'] ?? []),
            ]);
        if (!$allowed && $userId) {
            afStage24SecurityAlert($pdo, 'api_access_denied', $status >= 500 ? 'high' : 'medium', 'Acesso de API bloqueado', 'Uma rota de API foi bloqueada para esta conta.', $userId, $userId, 'api', 'api_endpoint', $endpoint, ['status'=>$status,'feature'=>$ctx['feature_key'] ?? null], 3);
        }
    } catch (Throwable $e) {
        if (defined('APP_DEBUG') && APP_DEBUG) error_log('[AURA-STAGE24-API] '.$e->getMessage());
    }
}

function afStage24Dashboard(PDO $pdo): array
{
    $q = static function(string $sql, array $params = []) use ($pdo): int {
        try { $st = $pdo->prepare($sql); $st->execute($params); return (int)$st->fetchColumn(); } catch (Throwable $e) { return 0; }
    };
    return [
        'alerts_open' => afStage24TableExists($pdo,'af_security_alerts') ? $q("SELECT COUNT(*) FROM af_security_alerts WHERE status IN ('open','triage','in_review')") : 0,
        'alerts_critical' => afStage24TableExists($pdo,'af_security_alerts') ? $q("SELECT COUNT(*) FROM af_security_alerts WHERE severity='critical' AND status IN ('open','triage','in_review')") : 0,
        'audit_24h' => afStage24TableExists($pdo,'af_audit_logs') ? $q("SELECT COUNT(*) FROM af_audit_logs WHERE created_at>=DATE_SUB(NOW(),INTERVAL 24 HOUR)") : 0,
        'api_denied_24h' => afStage24TableExists($pdo,'af_api_access_logs') ? $q("SELECT COUNT(*) FROM af_api_access_logs WHERE allowed=0 AND created_at>=DATE_SUB(NOW(),INTERVAL 24 HOUR)") : 0,
        'risk_users' => afStage24TableExists($pdo,'af_user_risk_scores') ? $q("SELECT COUNT(*) FROM af_user_risk_scores WHERE risk_level IN ('risk','critical')") : 0,
        'minors_active' => afStage24TableExists($pdo,'af_user_age_profiles') ? $q("SELECT COUNT(*) FROM af_user_age_profiles WHERE tipo_conta='minor' OR idade_declarada<18") : 0,
        'guardian_links' => afStage24TableExists($pdo,'af_guardian_minor_links') ? $q("SELECT COUNT(*) FROM af_guardian_minor_links WHERE status='active'") : 0,
        'events_active' => afStage24TableExists($pdo,'af_controlled_events') ? $q("SELECT COUNT(*) FROM af_controlled_events WHERE status IN ('approved','active','running')") : 0,
        'deletions_completed' => afStage24TableExists($pdo,'af_data_deletion_logs') ? $q("SELECT COUNT(*) FROM af_data_deletion_logs WHERE status IN ('completed','executed')") : 0,
    ];
}

function afStage24ListAlerts(PDO $pdo, string $status = 'open', int $limit = 80): array
{
    if (!afStage24TableExists($pdo,'af_security_alerts')) return [];
    $limit = max(1, min(200, $limit));
    $where = $status === 'all' ? '1=1' : "a.status IN ('open','triage','in_review')";
    if (in_array($status, ['resolved','dismissed'], true)) $where = 'a.status=?';
    $params = in_array($status, ['resolved','dismissed'], true) ? [$status] : [];
    $st = $pdo->prepare("SELECT a.*,u.nome,u.username,u.aura_id,su.nome subject_nome,su.username subject_username,su.aura_id subject_aura
        FROM af_security_alerts a
        LEFT JOIN af_usuarios u ON u.id=a.user_id
        LEFT JOIN af_usuarios su ON su.id=a.subject_user_id
        WHERE {$where}
        ORDER BY FIELD(a.severity,'critical','high','medium','low','info'),a.last_seen_at DESC
        LIMIT {$limit}");
    $st->execute($params);
    return $st->fetchAll() ?: [];
}

function afStage24RecentAudit(PDO $pdo, int $limit = 80, ?int $subjectUserId = null): array
{
    if (!afStage24TableExists($pdo,'af_audit_logs')) return [];
    $limit = max(1, min(300, $limit));
    $params = [];
    $where = '1=1';
    if ($subjectUserId) { $where = 'l.subject_user_id=? OR l.actor_user_id=?'; $params = [$subjectUserId,$subjectUserId]; }
    $st = $pdo->prepare("SELECT l.*,u.nome actor_nome,au.nome admin_nome,su.nome subject_nome,su.aura_id subject_aura
        FROM af_audit_logs l
        LEFT JOIN af_usuarios u ON u.id=l.actor_user_id
        LEFT JOIN af_admin_users au ON au.id=l.actor_admin_id
        LEFT JOIN af_usuarios su ON su.id=l.subject_user_id
        WHERE {$where}
        ORDER BY l.created_at DESC,l.id DESC LIMIT {$limit}");
    $st->execute($params);
    return $st->fetchAll() ?: [];
}

function afStage24ResolveAlert(PDO $pdo, int $alertId, int $adminId, string $status, string $note = ''): void
{
    if (!in_array($status, ['resolved','dismissed','triage','in_review'], true)) throw new RuntimeException('Status inválido para o alerta.');
    if (!afStage24TableExists($pdo,'af_security_alerts')) throw new RuntimeException('Tabela de alertas não instalada.');
    $st = $pdo->prepare('SELECT * FROM af_security_alerts WHERE id=? LIMIT 1');
    $st->execute([$alertId]);
    $before = $st->fetch();
    if (!$before) throw new RuntimeException('Alerta não encontrado.');
    $pdo->prepare('UPDATE af_security_alerts SET status=?,assigned_admin_user_id=?,resolved_by_admin_user_id=IF(? IN (\'resolved\',\'dismissed\'),?,resolved_by_admin_user_id),resolved_at=IF(? IN (\'resolved\',\'dismissed\'),NOW(),resolved_at),resolution_note=?,updated_at=NOW() WHERE id=?')
        ->execute([$status,$adminId,$status,$adminId,$status,$note ?: null,$alertId]);
    if (afStage24TableExists($pdo,'af_security_review_notes')) {
        $pdo->prepare('INSERT INTO af_security_review_notes(admin_user_id,user_id,alert_id,decision,note,before_json,after_json,created_at) VALUES (?,?,?,?,?,?,?,NOW())')
            ->execute([$adminId,(int)($before['subject_user_id'] ?: $before['user_id'] ?: 0) ?: null,$alertId,$status,$note ?: null,afStage24Json($before),afStage24Json(['status'=>$status,'note'=>$note])]);
    }
    afStage24AuditLog($pdo, 'security_alert_'.$status, 'security_alert', $alertId, $before, ['status'=>$status,'note'=>$note], ['actor_admin_id'=>$adminId,'severity'=>'medium','subject_user_id'=>(int)($before['subject_user_id'] ?: $before['user_id'] ?: 0)]);
    $uid = (int)($before['subject_user_id'] ?: $before['user_id'] ?: 0);
    if ($uid) afStage24RefreshUserRisk($pdo,$uid);
}

function afStage24UserDossier(PDO $pdo, int $userId): array
{
    $out = ['usuario'=>null,'age'=>null,'risk'=>null,'alerts'=>[],'api'=>[],'audit'=>[],'consents'=>[],'events'=>[]];
    $st = $pdo->prepare('SELECT u.id,u.nome,u.username,u.email,u.aura_id,u.status,u.seguranca_status,u.risco_conta,u.stage24_risk_score,u.stage24_risk_level,u.data_cadastro,u.ultimo_login FROM af_usuarios u WHERE u.id=? LIMIT 1');
    $st->execute([$userId]); $out['usuario'] = $st->fetch() ?: null;
    if (!$out['usuario']) return $out;
    if (afStage24TableExists($pdo,'af_user_age_profiles')) { $st=$pdo->prepare('SELECT * FROM af_user_age_profiles WHERE usuario_id=? LIMIT 1'); $st->execute([$userId]); $out['age']=$st->fetch() ?: null; }
    if (afStage24TableExists($pdo,'af_user_risk_scores')) { $st=$pdo->prepare('SELECT * FROM af_user_risk_scores WHERE user_id=? LIMIT 1'); $st->execute([$userId]); $out['risk']=$st->fetch() ?: null; }
    if (afStage24TableExists($pdo,'af_security_alerts')) { $st=$pdo->prepare('SELECT * FROM af_security_alerts WHERE user_id=? OR subject_user_id=? ORDER BY last_seen_at DESC LIMIT 30'); $st->execute([$userId,$userId]); $out['alerts']=$st->fetchAll()?:[]; }
    if (afStage24TableExists($pdo,'af_api_access_logs')) { $st=$pdo->prepare('SELECT * FROM af_api_access_logs WHERE user_id=? ORDER BY created_at DESC LIMIT 50'); $st->execute([$userId]); $out['api']=$st->fetchAll()?:[]; }
    $out['audit'] = afStage24RecentAudit($pdo, 50, $userId);
    if (afStage24TableExists($pdo,'af_parental_consents')) { $st=$pdo->prepare('SELECT * FROM af_parental_consents WHERE guardian_usuario_id=? OR minor_usuario_id=? ORDER BY criado_em DESC LIMIT 50'); $st->execute([$userId,$userId]); $out['consents']=$st->fetchAll()?:[]; }
    if (afStage24TableExists($pdo,'af_controlled_event_participants')) { $st=$pdo->prepare('SELECT p.*,e.name event_name,e.status event_status,l.guardian_usuario_id FROM af_controlled_event_participants p LEFT JOIN af_controlled_events e ON e.id=p.event_id LEFT JOIN af_guardian_minor_links l ON l.id=p.guardian_link_id WHERE p.minor_user_id=? OR l.guardian_usuario_id=? ORDER BY p.created_at DESC LIMIT 30'); $st->execute([$userId,$userId]); $out['events']=$st->fetchAll()?:[]; }
    return $out;
}

function afStage24AnalyzeUserBehavior(PDO $pdo, int $userId): array
{
    if ($userId <= 0 || !afStage24TableExists($pdo,'af_behavior_analysis')) return [];
    $signals = [];
    try {
        if (afStage24TableExists($pdo,'af_dispositivo_contas')) {
            $st=$pdo->prepare('SELECT MAX(accounts) FROM (SELECT COUNT(DISTINCT usuario_id) accounts FROM af_dispositivo_contas WHERE fingerprint_hash IN (SELECT fingerprint_hash FROM af_dispositivo_contas WHERE usuario_id=? AND fingerprint_hash IS NOT NULL) GROUP BY fingerprint_hash) x');
            $st->execute([$userId]); $accounts=(int)$st->fetchColumn();
            if ($accounts >= 3) $signals['shared_device_accounts']=$accounts;
        }
        if (afStage24TableExists($pdo,'af_minor_policy_decision_logs')) {
            $st=$pdo->prepare("SELECT COUNT(*) FROM af_minor_policy_decision_logs WHERE usuario_id=? AND decision_key='deny' AND criado_em>=DATE_SUB(NOW(),INTERVAL 7 DAY)");
            $st->execute([$userId]); $denials=(int)$st->fetchColumn();
            if ($denials >= 5) $signals['minor_policy_denials_7d']=$denials;
        }
        if (afStage24TableExists($pdo,'af_safety_reports')) {
            $st=$pdo->prepare("SELECT COUNT(*) FROM af_safety_reports WHERE reporter_user_id=? AND created_at>=DATE_SUB(NOW(),INTERVAL 7 DAY)");
            $st->execute([$userId]); $reports=(int)$st->fetchColumn();
            if ($reports >= 5) $signals['reports_created_7d']=$reports;
        }
        $score = min(100, (int)(($signals['shared_device_accounts'] ?? 0) * 8 + ($signals['minor_policy_denials_7d'] ?? 0) * 2 + ($signals['reports_created_7d'] ?? 0) * 3));
        $level = afStage24RiskLevel($score);
        if ($signals) {
            $status = $score >= 51 ? 'review_required' : 'generated';
            $pdo->prepare('INSERT INTO af_behavior_analysis(user_id,analysis_type,status,risk_score,risk_level,signals_json,created_at,updated_at) VALUES (?,\'scheduled_behavior_scan\',?,?,?,?,NOW(),NOW())')
                ->execute([$userId,$status,$score,$level,afStage24Json($signals)]);
            afStage24SecurityAlert($pdo,'behavior_signal',$score>=51?'high':'medium','Comportamento exige análise','A análise automática encontrou sinais que merecem revisão.', $userId,$userId,'stage24_behavior','usuario',$userId,$signals,min(30,$score));
        }
        return ['signals'=>$signals,'score'=>$score,'level'=>$level];
    } catch (Throwable $e) {
        if (defined('APP_DEBUG') && APP_DEBUG) error_log('[AURA-STAGE24-BEHAVIOR] '.$e->getMessage());
        return [];
    }
}

function afStage24ExportAuditRows(PDO $pdo, array $filters = [], int $limit = 5000): array
{
    if (!afStage24TableExists($pdo,'af_audit_logs')) return [];
    $limit = max(1, min(10000, $limit));
    $where = [];$params = [];
    if (!empty($filters['subject_user_id'])) { $where[]='(subject_user_id=? OR actor_user_id=?)'; $params[]=(int)$filters['subject_user_id']; $params[]=(int)$filters['subject_user_id']; }
    if (!empty($filters['action_key'])) { $where[]='action_key LIKE ?'; $params[]='%'.substr((string)$filters['action_key'],0,80).'%'; }
    if (!empty($filters['from'])) { $where[]='created_at>=?'; $params[]=$filters['from']; }
    if (!empty($filters['to'])) { $where[]='created_at<=?'; $params[]=$filters['to']; }
    $sql = 'SELECT id,event_uuid,actor_type,actor_user_id,actor_admin_id,subject_user_id,action_key,entity_type,entity_id,severity,source,created_at FROM af_audit_logs';
    if ($where) $sql .= ' WHERE '.implode(' AND ', $where);
    $sql .= ' ORDER BY created_at DESC,id DESC LIMIT '.$limit;
    $st = $pdo->prepare($sql);$st->execute($params);return $st->fetchAll() ?: [];
}

function afStage24RegisterAuditExport(PDO $pdo, int $adminId, string $type, string $format, array $filters, int $rowCount, string $fileName, string $content): void
{
    if (!afStage24TableExists($pdo,'af_audit_exports')) return;
    try {
        $pdo->prepare('INSERT INTO af_audit_exports(export_uuid,admin_user_id,export_type,format_key,filter_json,row_count,file_name,checksum_sha256,status,created_at) VALUES (?,?,?,?,?,?,?,?,\'generated\',NOW())')
            ->execute([afStage24Uuid(),$adminId,substr($type,0,80),$format==='json'?'json':'csv',afStage24Json($filters),$rowCount,substr($fileName,0,190),hash('sha256',$content)]);
        $pdo->prepare('UPDATE af_admin_users SET last_audit_export_at=NOW() WHERE id=?')->execute([$adminId]);
        afStage24AuditLog($pdo,'audit_export_generated','audit_export',null,[],['type'=>$type,'format'=>$format,'rows'=>$rowCount],['actor_admin_id'=>$adminId,'severity'=>'medium']);
    } catch (Throwable $e) { if (defined('APP_DEBUG') && APP_DEBUG) error_log('[AURA-STAGE24-EXPORT] '.$e->getMessage()); }
}
