<?php
/**
 * Engine DB Bridge — Query engine_alerts and engine_suggestions
 * from Engine DB (669427) via lantern tunnel (172.28.41.0/30)
 *
 * DB: engine_db @ 172.28.41.2:3306 (Engine node via lantern WG tunnel)
 * Tables: engine_alerts, engine_suggestions, sec_tool_audit
 * Encryption: evidence_json (plain JSON), command_enc (SecureVault)
 */
if (!defined('DASHBOARD_ROOT')) { http_response_code(403); exit; }

class EngineDB {
    private static ?PDO $pdo = null;

    /**
     * Get PDO connection to Engine DB via lantern tunnel
     * Engine runs MariaDB on 172.28.41.2:3306 (lantern WG interface)
     */
    private static function connect(): PDO {
        if (self::$pdo !== null) return self::$pdo;

        // Engine DB credentials from CredentialStore or fallback
        $host = '172.28.41.2';     // Engine via lantern tunnel
        $port = 3306;
        $dbname = 'engine_db';
        $user = 'dashboard';       // Read-only dashboard user
        $pass = '';

        // Try loading from CredentialStore if available
        if (class_exists('CredentialStore')) {
            try {
                $creds = CredentialStore::get('engine_db');
                if ($creds) {
                    $host = $creds['host'] ?? $host;
                    $port = (int)($creds['port'] ?? $port);
                    $user = $creds['user'] ?? $user;
                    $pass = $creds['pass'] ?? $pass;
                }
            } catch (\Throwable $e) {}
        }

        // Fallback: read from env file
        $envFile = '/var/www/private/engine_db.env';
        if ($pass === '' && file_exists($envFile)) {
            $lines = file($envFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
            foreach ($lines as $line) {
                if (str_starts_with($line, '#')) continue;
                [$k, $v] = explode('=', $line, 2) + ['', ''];
                $k = trim($k);
                $v = trim($v, " \t\n\r\"'");
                match($k) {
                    'ENGINE_DB_HOST' => $host = $v,
                    'ENGINE_DB_PORT' => $port = (int)$v,
                    'ENGINE_DB_USER' => $user = $v,
                    'ENGINE_DB_PASS' => $pass = $v,
                    'ENGINE_DB_NAME' => $dbname = $v,
                    default => null,
                };
            }
        }

        $dsn = "mysql:host={$host};port={$port};dbname={$dbname};charset=utf8mb4";
        self::$pdo = new PDO($dsn, $user, $pass, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_TIMEOUT => 5,
        ]);
        return self::$pdo;
    }

    /**
     * Fetch recent AI alerts from engine_alerts table
     */
    public static function getAlerts(int $limit = 20, bool $unresolvedOnly = false): array {
        try {
            $pdo = self::connect();
            $where = $unresolvedOnly ? 'WHERE ea.resolved = 0' : '';
            $stmt = $pdo->prepare("
                SELECT ea.id, ea.alert_uuid, ea.alert_type, ea.severity, ea.source_nid,
                       ea.source_tid, ea.title, ea.description, ea.evidence_json,
                       ea.model_version, ea.confidence, ea.acknowledged, ea.acknowledged_by,
                       ea.acknowledged_at, ea.resolved, ea.resolved_at, ea.created_at
                FROM engine_alerts ea
                {$where}
                ORDER BY ea.created_at DESC
                LIMIT :lim
            ");
            $stmt->bindValue(':lim', $limit, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt->fetchAll();
        } catch (\Throwable $e) {
            return [];
        }
    }

    /**
     * Fetch pending suggestions
     */
    public static function getSuggestions(string $status = 'pending', int $limit = 10): array {
        try {
            $pdo = self::connect();
            $stmt = $pdo->prepare("
                SELECT es.id, es.suggestion_uuid, es.alert_id, es.category,
                       es.action_type, es.target_nid, es.description,
                       es.risk_level, es.model_version, es.confidence,
                       es.status, es.approved_by, es.approved_at,
                       es.executed_at, es.execution_result, es.expires_at, es.created_at,
                       ea.title AS alert_title, ea.severity AS alert_severity
                FROM engine_suggestions es
                LEFT JOIN engine_alerts ea ON ea.id = es.alert_id
                WHERE es.status = :st
                ORDER BY es.created_at DESC
                LIMIT :lim
            ");
            $stmt->bindValue(':st', $status, PDO::PARAM_STR);
            $stmt->bindValue(':lim', $limit, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt->fetchAll();
        } catch (\Throwable $e) {
            return [];
        }
    }

    /**
     * Get alert counts by severity (last 24h)
     */
    public static function getAlertStats(): array {
        try {
            $pdo = self::connect();
            $stmt = $pdo->query("
                SELECT severity, COUNT(*) as cnt
                FROM engine_alerts
                WHERE created_at >= NOW() - INTERVAL 24 HOUR
                GROUP BY severity
            ");
            $rows = $stmt->fetchAll();
            $stats = ['info' => 0, 'low' => 0, 'medium' => 0, 'high' => 0, 'critical' => 0, 'total' => 0];
            foreach ($rows as $r) {
                $stats[$r['severity']] = (int)$r['cnt'];
                $stats['total'] += (int)$r['cnt'];
            }
            // Pending suggestions count
            $stmt2 = $pdo->query("SELECT COUNT(*) FROM engine_suggestions WHERE status = 'pending'");
            $stats['pending_suggestions'] = (int)$stmt2->fetchColumn();
            // Unresolved alerts count
            $stmt3 = $pdo->query("SELECT COUNT(*) FROM engine_alerts WHERE resolved = 0");
            $stats['unresolved'] = (int)$stmt3->fetchColumn();
            return $stats;
        } catch (\Throwable $e) {
            return ['info' => 0, 'low' => 0, 'medium' => 0, 'high' => 0, 'critical' => 0,
                    'total' => 0, 'pending_suggestions' => 0, 'unresolved' => 0, 'error' => $e->getMessage()];
        }
    }

    /**
     * Acknowledge an alert
     */
    public static function acknowledgeAlert(string $alertUuid, string $adminUser): bool {
        try {
            $pdo = self::connect();
            $stmt = $pdo->prepare("
                UPDATE engine_alerts
                SET acknowledged = 1, acknowledged_by = :admin, acknowledged_at = NOW()
                WHERE alert_uuid = :uuid AND acknowledged = 0
            ");
            $stmt->execute([':uuid' => $alertUuid, ':admin' => $adminUser]);
            return $stmt->rowCount() > 0;
        } catch (\Throwable $e) {
            return false;
        }
    }

    /**
     * Resolve an alert
     */
    public static function resolveAlert(string $alertUuid): bool {
        try {
            $pdo = self::connect();
            $stmt = $pdo->prepare("
                UPDATE engine_alerts
                SET resolved = 1, resolved_at = NOW()
                WHERE alert_uuid = :uuid AND resolved = 0
            ");
            $stmt->execute([':uuid' => $alertUuid]);
            return $stmt->rowCount() > 0;
        } catch (\Throwable $e) {
            return false;
        }
    }

    /**
     * Approve/reject a suggestion
     */
    public static function updateSuggestion(string $sugUuid, string $newStatus, string $adminUser): bool {
        try {
            if (!in_array($newStatus, ['approved', 'rejected', 'expired'], true)) return false;
            $pdo = self::connect();
            $stmt = $pdo->prepare("
                UPDATE engine_suggestions
                SET status = :st, approved_by = :admin, approved_at = NOW()
                WHERE suggestion_uuid = :uuid AND status = 'pending'
            ");
            $stmt->execute([':uuid' => $sugUuid, ':st' => $newStatus, ':admin' => $adminUser]);
            return $stmt->rowCount() > 0;
        } catch (\Throwable $e) {
            return false;
        }
    }

    /**
     * Get recent tool audit entries from sec_tool_audit
     * Column names are obfuscated per AUDIT_CODES.md
     */
    public static function getToolAudit(int $limit = 50): array {
        try {
            $pdo = self::connect();
            $stmt = $pdo->prepare("
                SELECT id, ts, nid, tid, act, src, res, phash, sid, dur, xc
                FROM sec_tool_audit
                ORDER BY ts DESC
                LIMIT :lim
            ");
            $stmt->bindValue(':lim', $limit, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt->fetchAll();
        } catch (\Throwable $e) {
            return [];
        }
    }
}
