<?php
require_once __DIR__ . '/functions.php';
require_once __DIR__ . '/functions_backup.php';

function diagnosticMaskSensitiveText($value) {
    if ($value === null) return null;
    $text = ensureUtf8String($value);
    $text = preg_replace('/[A-Z]:[\\\\\/][^\s"\']+/i', '[path]', $text);
    $text = preg_replace('/(?:Bearer\s+)?[A-Za-z0-9_\-]{32,}/', '[secret]', $text);
    $text = preg_replace('/(token|csrf|session|password)\s*[=:]\s*[^\s,;]+/i', '$1=[redacted]', $text);
    return trim($text);
}

function diagnosticResult($status, $title, $message, $detail = null) {
    return [
        'status' => $status,
        'title' => diagnosticMaskSensitiveText($title),
        'message' => diagnosticMaskSensitiveText($message),
        'detail' => diagnosticMaskSensitiveText($detail),
    ];
}

function diagnosticTableExists($table) {
    try {
        $stmt = getDBConnection()->prepare("
            SELECT COUNT(*)
            FROM information_schema.tables
            WHERE table_schema = DATABASE()
              AND table_name = :table_name
        ");
        $stmt->execute([':table_name' => (string)$table]);
        return ((int)$stmt->fetchColumn()) > 0;
    } catch (Throwable $e) {
        return false;
    }
}

function diagnosticCount($sql, $params = []) {
    try {
        $stmt = getDBConnection()->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    } catch (Throwable $e) {
        return 0;
    }
}

function diagnosticLatestValue($sql, $params = [], $fallback = '-') {
    try {
        $stmt = getDBConnection()->prepare($sql);
        $stmt->execute($params);
        $value = $stmt->fetchColumn();
        return ($value !== false && $value !== null && $value !== '') ? ensureUtf8String($value) : $fallback;
    } catch (Throwable $e) {
        return $fallback;
    }
}

function checkPhpStatus() {
    return diagnosticResult('ok', 'PHP 실행 상태', 'PHP가 정상 실행 중입니다.', PHP_VERSION . ' / ' . PHP_OS);
}

function checkDatabaseConnection() {
    try {
        $pdo = getDBConnection();
        $pdo->query('SELECT 1')->fetchColumn();
        return diagnosticResult('ok', 'MySQL 연결', 'DB 연결이 정상입니다.', DB_NAME);
    } catch (Throwable $e) {
        return diagnosticResult('error', 'MySQL 연결', 'DB 연결에 실패했습니다.', $e->getMessage());
    }
}

function checkRequiredTables() {
    $required = ['songs', 'liked_songs', 'play_history', 'playlists', 'playlist_songs', 'lyrics', 'settings', 'scan_logs', 'maintenance_logs', 'admin_users', 'admin_logs', 'app_logs', 'admin_notifications'];
    try {
        $pdo = getDBConnection();
        $missing = [];
        foreach ($required as $table) {
            $stmt = $pdo->prepare("SHOW TABLES LIKE :table_name");
            $stmt->execute([':table_name' => $table]);
            if (!$stmt->fetchColumn()) $missing[] = $table;
        }
        if ($missing) return diagnosticResult('error', '주요 테이블', '누락된 테이블이 있습니다.', implode(', ', $missing));
        return diagnosticResult('ok', '주요 테이블', '필수 테이블이 모두 존재합니다.', implode(', ', $required));
    } catch (Throwable $e) {
        return diagnosticResult('warning', '주요 테이블', '테이블 점검 중 오류가 발생했습니다.', $e->getMessage());
    }
}

function checkScanPath() {
    $scanPath = getScanPathSetting();
    $winPath = resolveFilesystemPath($scanPath, 'dir') ?? toWinPath($scanPath);
    $detail = diagnosticMaskSensitiveText($scanPath);
    if (!is_dir($winPath)) return diagnosticResult('error', '음악 스캔 경로', '스캔 경로를 찾을 수 없습니다.', $detail);
    if (!is_readable($winPath)) return diagnosticResult('error', '음악 스캔 경로', '스캔 경로를 읽을 수 없습니다.', $detail);
    return diagnosticResult('ok', '음악 스캔 경로', '스캔 경로가 존재하고 읽을 수 있습니다.', $detail);
}

function checkStorageWritable() {
    $paths = [
        'storage' => dirname(__DIR__) . '/storage',
        'storage/logs' => dirname(__DIR__) . '/storage/logs',
        'storage/backups' => getBackupDir(),
        'storage/covers' => getCoverCacheDir(),
    ];
    $bad = [];
    foreach ($paths as $label => $path) {
        if (!is_dir($path)) @mkdir($path, 0777, true);
        if (!is_dir($path) || !is_writable($path)) $bad[] = $label;
    }
    if ($bad) return diagnosticResult('error', 'storage 쓰기 권한', '쓰기 불가능한 폴더가 있습니다.', implode(', ', $bad));
    return diagnosticResult('ok', 'storage 쓰기 권한', '필요한 storage 폴더에 쓸 수 있습니다.', implode(', ', array_keys($paths)));
}

function checkGetId3Status() {
    $available = initGetID3();
    $detail = 'fallback 분석 유지';
    if ($available && class_exists('getID3')) {
        try {
            $getId3 = new getID3();
            $version = $getId3->version() ?: '확인됨';
            return diagnosticResult('ok', 'getID3', 'getID3를 사용할 수 있습니다.', '버전 ' . $version . ' / ' . $detail);
        } catch (Throwable $e) {
            return diagnosticResult('ok', 'getID3', 'getID3를 사용할 수 있습니다.', $detail);
        }
    }
    return diagnosticResult('warning', 'getID3', 'getID3가 없어 내부 태그 분석은 제한됩니다. 파일명 fallback은 계속 동작합니다.', $detail);
}

function checkPwaFiles() {
    $missing = [];
    foreach (['manifest.json', 'service-worker.js', 'offline.php'] as $file) {
        if (!is_file(dirname(__DIR__) . '/' . $file)) $missing[] = $file;
    }
    if ($missing) return diagnosticResult('warning', 'PWA 파일', '일부 PWA 파일이 없습니다.', implode(', ', $missing));
    return diagnosticResult('ok', 'PWA 파일', 'manifest와 service worker 파일이 있습니다.', null);
}

function checkPwaPlayerStatus() {
    try {
        $root = dirname(__DIR__);
        $manifestPath = $root . '/manifest.json';
        $serviceWorkerPath = $root . '/service-worker.js';
        $layoutPath = __DIR__ . '/layout.php';
        $requiredFiles = [
            'manifest.json',
            'service-worker.js',
            'offline.php',
            'stream.php',
            'cover.php',
            'assets/img/icons/icon-192.png',
            'assets/img/icons/icon-512.png',
            'assets/img/default-cover.svg',
        ];
        $missing = [];
        foreach ($requiredFiles as $file) {
            if (!is_file($root . '/' . $file)) $missing[] = $file;
        }

        $manifestIssues = [];
        $manifest = null;
        if (is_file($manifestPath)) {
            $manifest = json_decode((string)file_get_contents($manifestPath), true);
            if (!is_array($manifest)) {
                $manifestIssues[] = 'manifest JSON 오류';
            } else {
                foreach (['name', 'short_name', 'start_url', 'scope', 'display', 'theme_color', 'background_color', 'icons'] as $key) {
                    if (!array_key_exists($key, $manifest) || $manifest[$key] === '' || $manifest[$key] === []) {
                        $manifestIssues[] = 'manifest.' . $key . ' 누락';
                    }
                }
            }
        }

        $layout = is_file($layoutPath) ? (string)file_get_contents($layoutPath) : '';
        $metaIssues = [];
        if (strpos($layout, 'apple-touch-icon') === false) $metaIssues[] = 'apple-touch-icon 누락';
        if (strpos($layout, 'theme-color') === false) $metaIssues[] = 'theme-color 누락';
        if (strpos($layout, 'apple-mobile-web-app-capable') === false) $metaIssues[] = 'iOS standalone meta 누락';
        if (strpos($layout, 'assets/js/pwa.js') === false) $metaIssues[] = 'PWA 설치 JS 로드 누락';

        $streamPath = $root . '/stream.php';
        $streamSource = is_file($streamPath) ? (string)file_get_contents($streamPath) : '';
        if (strpos($streamSource, 'X-MelonLite-Preview') === false) $metaIssues[] = '비로그인 30초 미리듣기 헤더 누락';
        if (strpos($streamSource, 'no-store') === false || strpos($streamSource, 'Vary: Cookie') === false) $metaIssues[] = 'stream no-cache 헤더 확인 필요';

        $cacheVersion = '확인 필요';
        if (is_file($serviceWorkerPath)) {
            $sw = (string)file_get_contents($serviceWorkerPath);
            if (preg_match("/CACHE_VERSION\\s*=\\s*['\\\"]([^'\\\"]+)/", $sw, $matches)) {
                $cacheVersion = $matches[1];
            }
        }

        $stmt = getDBConnection()->prepare("
            SELECT
                SUM(CASE WHEN log_type IN ('stream_error','stream') OR message LIKE :stream_like THEN 1 ELSE 0 END) AS stream_errors,
                SUM(CASE WHEN log_type = 'pwa_error' OR message LIKE :pwa_like THEN 1 ELSE 0 END) AS pwa_errors,
                SUM(CASE WHEN log_type = 'player_error' OR message LIKE :player_like THEN 1 ELSE 0 END) AS player_errors
            FROM app_logs
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
        ");
        $stmt->execute([
            ':stream_like' => '%stream%',
            ':pwa_like' => '%service worker%',
            ':player_like' => '%player%',
        ]);
        $row = $stmt->fetch() ?: [];
        $streamErrors = (int)($row['stream_errors'] ?? 0);
        $pwaErrors = (int)($row['pwa_errors'] ?? 0);
        $playerErrors = (int)($row['player_errors'] ?? 0);
        $detail = 'stream ' . number_format($streamErrors) . '건, PWA ' . number_format($pwaErrors) . '건, player ' . number_format($playerErrors) . '건';
        $issueParts = array_merge($missing, $manifestIssues, $metaIssues);
        $detail .= ' / cache ' . $cacheVersion . ' / guest preview 30초 / install keys localStorage';
        if ($issueParts) {
            return diagnosticResult('warning', 'PWA/플레이어 점검', '모바일/PWA에 필요한 일부 설정을 확인해야 합니다.', implode(', ', $issueParts) . ' / ' . $detail);
        }
        $status = ($streamErrors + $pwaErrors + $playerErrors) > 0 ? 'warning' : 'ok';
        return diagnosticResult($status, 'PWA/플레이어 점검', 'PWA 파일, manifest, iOS 메타, 최근 플레이어 오류를 확인했습니다.', $detail);
    } catch (Throwable $e) {
        return diagnosticResult('warning', 'PWA/플레이어 점검', 'PWA/플레이어 상태를 확인하지 못했습니다.', $e->getMessage());
    }
}

function countDuplicateSuspects() {
    try {
        return (int)getDBConnection()->query("
            SELECT COUNT(*) FROM (
                SELECT file_hash FROM songs
                WHERE file_hash IS NOT NULL AND file_hash != ''
                GROUP BY file_hash
                HAVING COUNT(*) > 1
            ) d
        ")->fetchColumn();
    } catch (Throwable $e) {
        return 0;
    }
}

function checkRecentErrors() {
    try {
        $count = (int)getDBConnection()->query("SELECT COUNT(*) FROM app_logs WHERE log_level IN ('error','critical') AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)")->fetchColumn();
        if ($count > 0) return diagnosticResult('warning', '최근 오류', '최근 7일 오류 로그가 있습니다.', $count . '건');
        return diagnosticResult('ok', '최근 오류', '최근 7일 오류 로그가 없습니다.', null);
    } catch (Throwable $e) {
        return diagnosticResult('warning', '최근 오류', '오류 로그를 확인할 수 없습니다.', $e->getMessage());
    }
}

function checkMusicStats() {
    try {
        $stats = getLibraryStats();
        $detail = '파일 없음 ' . number_format($stats['missing']) . '곡, 메타데이터 누락 ' . number_format($stats['metadata_missing']) . '곡, 중복 의심 ' . number_format(countDuplicateSuspects()) . '그룹';
        $status = ($stats['missing'] > 0 || $stats['metadata_missing'] > 0) ? 'warning' : 'ok';
        return diagnosticResult($status, '보관함 상태', '음악 보관함 통계를 확인했습니다.', $detail);
    } catch (Throwable $e) {
        return diagnosticResult('warning', '보관함 상태', '보관함 통계를 확인할 수 없습니다.', $e->getMessage());
    }
}

function checkRecommendationStatus() {
    try {
        $pdo = getDBConnection();
        $row = $pdo->query("
            SELECT
                (SELECT COUNT(*) FROM play_history) AS play_count,
                (SELECT COUNT(*) FROM liked_songs) AS liked_count,
                (SELECT COUNT(*) FROM track_ratings WHERE rating IS NOT NULL AND rating > 0) AS rated_count,
                (SELECT COUNT(DISTINCT song_id) FROM song_tags) AS tagged_count,
                (SELECT COUNT(*) FROM songs WHERE is_missing = 1) AS missing_count,
                (SELECT COUNT(*) FROM songs WHERE is_missing = 0 AND (genre IS NULL OR genre = '' OR genre = 'Unknown')) AS missing_genre_count
        ")->fetch() ?: [];
        $playCount = (int)($row['play_count'] ?? 0);
        $likedCount = (int)($row['liked_count'] ?? 0);
        $ratedCount = (int)($row['rated_count'] ?? 0);
        $taggedCount = (int)($row['tagged_count'] ?? 0);
        $missingCount = (int)($row['missing_count'] ?? 0);
        $missingGenreCount = (int)($row['missing_genre_count'] ?? 0);
        $candidateScore = $playCount + $likedCount + $ratedCount + $taggedCount;
        $detail = '재생 기록 ' . number_format($playCount) . '건, 좋아요 ' . number_format($likedCount) . '곡, 별점 ' . number_format($ratedCount) . '곡, 태그 ' . number_format($taggedCount) . '곡, 추천 제외 파일 없음 ' . number_format($missingCount) . '곡, 장르 누락 ' . number_format($missingGenreCount) . '곡';
        if ($candidateScore < 10) {
            return diagnosticResult('warning', '추천 데이터 상태', '추천에 활용할 취향 데이터가 아직 적습니다.', $detail);
        }
        return diagnosticResult('ok', '추천 데이터 상태', '자동 플레이리스트와 추천에 필요한 기본 데이터가 있습니다.', $detail);
    } catch (Throwable $e) {
        return diagnosticResult('warning', '추천 데이터 상태', '추천 데이터 상태를 확인하지 못했습니다.', $e->getMessage());
    }
}

function checkAdminSecurityStatus() {
    try {
        $pdo = getDBConnection();
        $admins = (int)$pdo->query("SELECT COUNT(*) FROM admin_users WHERE is_active = 1")->fetchColumn();
        if ($admins <= 0) return diagnosticResult('error', '관리자 보안', '활성 관리자 계정이 없습니다.', null);
        return diagnosticResult('ok', '관리자 보안', '활성 관리자 계정이 존재합니다.', $admins . '명');
    } catch (Throwable $e) {
        return diagnosticResult('warning', '관리자 보안', '관리자 계정 상태를 확인할 수 없습니다.', $e->getMessage());
    }
}

function checkEndpointFilesStatus() {
    $root = dirname(__DIR__);
    $items = [
        'stream.php' => ['file' => $root . '/stream.php', 'required' => ['no-store', 'X-MelonLite-Preview']],
        'cover.php' => ['file' => $root . '/cover.php', 'required' => ['default-cover']],
        'manifest.json' => ['file' => $root . '/manifest.json', 'required' => []],
        'service-worker.js' => ['file' => $root . '/service-worker.js', 'required' => ['stream.php', 'api']],
        'offline.php' => ['file' => $root . '/offline.php', 'required' => []],
    ];
    $issues = [];
    $ok = [];
    foreach ($items as $label => $config) {
        if (!is_file($config['file'])) {
            $issues[] = $label . ' 없음';
            continue;
        }
        $ok[] = $label;
        $source = (string)@file_get_contents($config['file']);
        foreach ($config['required'] as $needle) {
            if ($needle !== '' && stripos($source, $needle) === false) {
                $issues[] = $label . ' 확인 필요: ' . $needle;
            }
        }
    }
    if ($issues) {
        return diagnosticResult('warning', '핵심 엔드포인트', '스트리밍, 앨범아트, PWA 파일 중 확인할 항목이 있습니다.', implode(', ', $issues));
    }
    return diagnosticResult('ok', '핵심 엔드포인트', 'stream.php, cover.php, PWA 파일이 존재하고 기본 정책을 확인했습니다.', implode(', ', $ok));
}

function checkApiStatus() {
    $root = dirname(__DIR__);
    $apiFiles = ['auth_status.php', 'songs.php', 'song.php', 'get_lyrics.php', 'search.php', 'search_suggest.php', 'liked.php', 'recent.php'];
    $missing = [];
    foreach ($apiFiles as $file) {
        if (!is_file($root . '/api/' . $file)) $missing[] = $file;
    }
    if ($missing) {
        return diagnosticResult('warning', '내부 API', '일부 API 파일이 없습니다.', implode(', ', $missing));
    }
    $apiHelper = is_file(__DIR__ . '/api.php') ? (string)file_get_contents(__DIR__ . '/api.php') : '';
    $issues = [];
    if (stripos($apiHelper, 'application/json; charset=utf-8') === false) $issues[] = 'JSON Content-Type 확인 필요';
    if (stripos($apiHelper, 'JSON_UNESCAPED_UNICODE') === false) $issues[] = 'JSON_UNESCAPED_UNICODE 확인 필요';
    if (stripos($apiHelper, 'apiAuthContext') === false) $issues[] = 'API 인증 helper 확인 필요';
    if ($issues) {
        return diagnosticResult('warning', '내부 API', 'API 응답 표준 중 확인할 항목이 있습니다.', implode(', ', $issues));
    }
    return diagnosticResult('ok', '내부 API', '주요 API 파일과 JSON 응답 helper를 확인했습니다.', '민감 API service-worker 캐시 제외');
}

function checkBackupStatus() {
    try {
        $dir = ensureBackupStorage();
        $files = glob($dir . '/music_*.json') ?: [];
        $count = count($files);
        $latest = null;
        foreach ($files as $path) {
            $mtime = filemtime($path) ?: 0;
            if ($mtime > (int)($latest['mtime'] ?? 0)) {
                $latest = ['mtime' => $mtime, 'name' => basename($path), 'size' => filesize($path) ?: 0];
            }
        }
        $writable = is_writable($dir);
        $detail = '파일 ' . number_format($count) . '개';
        if ($latest) {
            $detail .= ' / 마지막 ' . date('Y-m-d H:i', (int)$latest['mtime']) . ' / ' . number_format((int)$latest['size']) . ' bytes';
        }
        $detail .= ' / 위치 storage/backups';
        if (!$writable) return diagnosticResult('error', '백업 상태', '백업 저장 위치에 쓸 수 없습니다.', $detail);
        if ($count === 0) return diagnosticResult('warning', '백업 상태', '생성된 백업 파일이 없습니다.', $detail);
        return diagnosticResult('ok', '백업 상태', '백업 파일과 저장 위치를 확인했습니다.', $detail);
    } catch (Throwable $e) {
        return diagnosticResult('warning', '백업 상태', '백업 상태를 확인하지 못했습니다.', $e->getMessage());
    }
}

function getOperationalDashboardSummary() {
    $pdo = getDBConnection();
    $stats = getLibraryStats();
    $lastScanAt = diagnosticLatestValue('SELECT scan_finished_at FROM scan_logs WHERE scan_finished_at IS NOT NULL ORDER BY id DESC LIMIT 1');
    $lastScanMessage = scanMessageToKorean(diagnosticLatestValue('SELECT message FROM scan_logs ORDER BY id DESC LIMIT 1', [], '기록 없음'));
    $lastBackupAt = '-';
    $backupCount = 0;
    try {
        $backupDir = ensureBackupStorage();
        $backupFiles = glob($backupDir . '/music_*.json') ?: [];
        $backupCount = count($backupFiles);
        $latestBackupMtime = 0;
        foreach ($backupFiles as $file) {
            $latestBackupMtime = max($latestBackupMtime, filemtime($file) ?: 0);
        }
        if ($latestBackupMtime > 0) $lastBackupAt = date('Y-m-d H:i', $latestBackupMtime);
    } catch (Throwable $e) {
        $lastBackupAt = '확인 필요';
    }

    $apiErrors = diagnosticCount("
        SELECT COUNT(*)
        FROM app_logs
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
          AND (log_type LIKE :api_type OR message LIKE :api_message)
    ", [':api_type' => '%api%', ':api_message' => '%api%']);
    $authFailures = diagnosticCount("
        SELECT COUNT(*)
        FROM app_logs
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
          AND (log_type IN ('login_error','audit') OR message LIKE :auth_like OR message LIKE :csrf_like)
    ", [':auth_like' => '%permission%', ':csrf_like' => '%CSRF%']);

    $lastScanRaw = [];
    try {
        $lastScanRaw = $pdo->query("
            SELECT tag_count, filename_count, metadata_error_count, cover_success_count, cover_missing_count
            FROM scan_logs
            ORDER BY id DESC
            LIMIT 1
        ")->fetch() ?: [];
    } catch (Throwable $e) {
        $lastScanRaw = [];
    }

    $fallbackCount = diagnosticCount("SELECT COUNT(*) FROM songs WHERE metadata_source IN ('filename','folder','fallback')");
    if ($fallbackCount <= 0 && isset($lastScanRaw['filename_count'])) {
        $fallbackCount = (int)$lastScanRaw['filename_count'];
    }

    $cards = [
        ['label' => '전체 곡', 'value' => number_format($stats['songs']), 'status' => 'ok', 'detail' => '파일 없음 ' . number_format($stats['missing']) . '곡'],
        ['label' => '최근 스캔', 'value' => $lastScanAt, 'status' => $lastScanAt === '-' ? 'warning' : 'ok', 'detail' => $lastScanMessage],
        ['label' => 'getID3', 'value' => initGetID3() ? '사용 가능' : 'fallback', 'status' => initGetID3() ? 'ok' : 'warning', 'detail' => 'fallback ' . number_format($fallbackCount) . '곡'],
        ['label' => '품질 경고', 'value' => number_format((int)$stats['metadata_missing']), 'status' => $stats['metadata_missing'] > 0 ? 'warning' : 'ok', 'detail' => '메타데이터 누락/아트 누락 포함'],
        ['label' => '중복 후보', 'value' => number_format(countDuplicateSuspects()), 'status' => countDuplicateSuspects() > 0 ? 'warning' : 'ok', 'detail' => '해시 기준 후보'],
        ['label' => '가사 없음', 'value' => number_format(diagnosticCount("
            SELECT COUNT(*)
            FROM songs s
            LEFT JOIN lyrics l ON l.song_id = s.id
            WHERE s.is_missing = 0
              AND (l.lyrics_text IS NULL OR TRIM(l.lyrics_text) = '')
        ")), 'status' => 'unknown', 'detail' => '등록 가능'],
        ['label' => '최근 재생', 'value' => number_format(diagnosticCount("SELECT COUNT(*) FROM play_history WHERE played_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)")), 'status' => 'ok', 'detail' => '최근 7일 기준'],
        ['label' => '최근 오류', 'value' => number_format(diagnosticCount("SELECT COUNT(*) FROM app_logs WHERE log_level IN ('error','critical') AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)")), 'status' => diagnosticCount("SELECT COUNT(*) FROM app_logs WHERE log_level IN ('error','critical') AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)") > 0 ? 'warning' : 'ok', 'detail' => '최근 7일 앱 로그'],
        ['label' => 'API 오류', 'value' => number_format($apiErrors), 'status' => $apiErrors > 0 ? 'warning' : 'ok', 'detail' => '최근 7일 기준'],
        ['label' => '권한/CSRF', 'value' => number_format($authFailures), 'status' => $authFailures > 0 ? 'warning' : 'ok', 'detail' => '최근 7일 의심 이벤트'],
        ['label' => '백업', 'value' => $lastBackupAt, 'status' => $backupCount > 0 ? 'ok' : 'warning', 'detail' => '파일 ' . number_format($backupCount) . '개'],
        ['label' => 'PWA 캐시', 'value' => diagnosticServiceWorkerVersion(), 'status' => 'ok', 'detail' => 'stream/API 캐시 제외'],
    ];

    return array_map(function ($card) {
        $card['detail'] = diagnosticMaskSensitiveText($card['detail'] ?? '');
        return $card;
    }, $cards);
}

function diagnosticServiceWorkerVersion() {
    $path = dirname(__DIR__) . '/service-worker.js';
    if (!is_file($path)) return '확인 필요';
    $source = (string)file_get_contents($path);
    if (preg_match("/CACHE_VERSION\s*=\s*['\"]([^'\"]+)/", $source, $matches)) {
        return ensureUtf8String($matches[1]);
    }
    return '확인 필요';
}

function getRecentOperationalLogs($limit = 5) {
    $limit = max(1, min(10, (int)$limit));
    try {
        $stmt = getDBConnection()->prepare("
            SELECT id, log_level, log_type, message, created_at
            FROM app_logs
            WHERE log_level IN ('warning','error','critical')
               OR log_type IN ('stream_error','pwa_error','api_error','json_encode_error','db_error')
            ORDER BY id DESC
            LIMIT " . (int)$limit
        );
        $stmt->execute();
        $rows = $stmt->fetchAll();
        foreach ($rows as &$row) {
            $row['message'] = diagnosticMaskSensitiveText($row['message'] ?? '');
        }
        unset($row);
        return $rows;
    } catch (Throwable $e) {
        return [];
    }
}

function getRecentAuditSummary($limit = 5) {
    $limit = max(1, min(10, (int)$limit));
    try {
        $stmt = getDBConnection()->prepare("
            SELECT l.action_type, l.message, l.created_at, u.username, u.display_name
            FROM admin_logs l
            LEFT JOIN admin_users u ON u.id = l.admin_user_id
            ORDER BY l.id DESC
            LIMIT " . (int)$limit
        );
        $stmt->execute();
        $rows = $stmt->fetchAll();
        foreach ($rows as &$row) {
            $row['message'] = diagnosticMaskSensitiveText($row['message'] ?? '');
        }
        unset($row);
        return $rows;
    } catch (Throwable $e) {
        return [];
    }
}

function checkEncodingStatus() {
    try {
        $pdo = getDBConnection();
        $phpCharset = (string)ini_get('default_charset');
        $connectionCharset = (string)$pdo->query("SELECT @@character_set_connection")->fetchColumn();
        $connectionCollation = (string)$pdo->query("SELECT @@collation_connection")->fetchColumn();

        $schemaStmt = $pdo->prepare("
            SELECT DEFAULT_CHARACTER_SET_NAME AS charset_name, DEFAULT_COLLATION_NAME AS collation_name
            FROM information_schema.SCHEMATA
            WHERE SCHEMA_NAME = DATABASE()
            LIMIT 1
        ");
        $schemaStmt->execute();
        $schema = $schemaStmt->fetch() ?: [];

        $tables = ['songs', 'lyrics', 'track_notes', 'tags', 'song_tags', 'playlists'];
        $placeholders = [];
        $params = [];
        foreach ($tables as $idx => $table) {
            $key = ':table_' . $idx;
            $placeholders[] = $key;
            $params[$key] = $table;
        }
        $tableStmt = $pdo->prepare("
            SELECT TABLE_NAME, TABLE_COLLATION
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME IN (" . implode(',', $placeholders) . ")
            ORDER BY TABLE_NAME
        ");
        $tableStmt->execute($params);
        $tableRows = $tableStmt->fetchAll();

        $nonUtf8Tables = [];
        $tableDetails = [];
        foreach ($tableRows as $row) {
            $name = (string)($row['TABLE_NAME'] ?? '');
            $collation = (string)($row['TABLE_COLLATION'] ?? '');
            $tableDetails[] = $name . '=' . $collation;
            if (stripos($collation, 'utf8mb4') !== 0) {
                $nonUtf8Tables[] = $name . '(' . $collation . ')';
            }
        }

        $playerPath = dirname(__DIR__) . '/assets/js/player.js';
        $layoutPath = __DIR__ . '/layout.php';
        $playerLoaded = is_file($playerPath);
        $layout = is_file($layoutPath) ? (string)file_get_contents($layoutPath) : '';
        $hasMetaCharset = stripos($layout, 'charset="UTF-8"') !== false || stripos($layout, "charset='UTF-8'") !== false || stripos($layout, 'charset=UTF-8') !== false;

        $detail = 'PHP default_charset=' . ($phpCharset ?: '미설정')
            . ' / connection=' . $connectionCharset . '/' . $connectionCollation
            . ' / database=' . (($schema['charset_name'] ?? 'unknown') . '/' . ($schema['collation_name'] ?? 'unknown'))
            . ' / tables=' . implode(', ', $tableDetails)
            . ' / meta charset=' . ($hasMetaCharset ? 'ok' : 'missing')
            . ' / player.js=' . ($playerLoaded ? 'ok' : 'missing')
            . ' / JSON_UNESCAPED_UNICODE 적용';

        $issues = [];
        if (strtolower($connectionCharset) !== 'utf8mb4') $issues[] = 'DB 연결 charset 확인 필요';
        if (($schema['charset_name'] ?? '') !== 'utf8mb4') $issues[] = 'DB 기본 charset 확인 필요';
        if (!$hasMetaCharset) $issues[] = 'HTML meta charset 누락';
        if (!$playerLoaded) $issues[] = 'player.js 누락';
        if ($nonUtf8Tables) $issues[] = 'utf8mb4 외 table: ' . implode(', ', $nonUtf8Tables);

        if ($issues) {
            return diagnosticResult('warning', '인코딩/UTF-8 점검', 'UTF-8 설정 중 확인이 필요한 항목이 있습니다.', implode(' / ', $issues) . ' / ' . $detail);
        }
        return diagnosticResult('ok', '인코딩/UTF-8 점검', 'PHP, DB 연결, 주요 테이블, HTML/JSON 출력 기준을 점검했습니다.', $detail);
    } catch (Throwable $e) {
        return diagnosticResult('warning', '인코딩/UTF-8 점검', '인코딩 상태를 확인하지 못했습니다.', $e->getMessage());
    }
}

function diagnosticStatusWeight($status) {
    $weights = ['ok' => 0, 'unknown' => 1, 'warning' => 2, 'error' => 3];
    return $weights[$status] ?? 1;
}

function diagnosticStatusLabel($status) {
    $labels = ['ok' => '정상', 'warning' => '주의', 'error' => '오류', 'unknown' => '확인 필요'];
    return $labels[$status] ?? '확인 필요';
}

function diagnosticSummarizeItems($items) {
    $summary = ['ok' => 0, 'warning' => 0, 'error' => 0, 'unknown' => 0, 'total' => 0, 'overall' => 'ok'];
    foreach ($items as $item) {
        $status = $item['status'] ?? 'unknown';
        if (!isset($summary[$status])) $status = 'unknown';
        $summary[$status]++;
        $summary['total']++;
        if (diagnosticStatusWeight($status) > diagnosticStatusWeight($summary['overall'])) {
            $summary['overall'] = $status;
        }
    }
    return $summary;
}

function diagnosticBuildGroup($title, $items, $message = '') {
    return [
        'title' => $title,
        'message' => $message,
        'items' => $items,
        'summary' => diagnosticSummarizeItems($items),
    ];
}

function getSelfDiagnosticReport() {
    $groups = [
        diagnosticBuildGroup('서버 / DB', [
            checkPhpStatus(),
            checkDatabaseConnection(),
            checkRequiredTables(),
            checkStorageWritable(),
            checkEncodingStatus(),
        ], 'PHP, DB 연결, 필수 테이블, 쓰기 권한, UTF-8 상태를 확인합니다.'),
        diagnosticBuildGroup('음악 보관함 / 스캔', [
            checkScanPath(),
            checkGetId3Status(),
            checkMusicStats(),
            checkRecommendationStatus(),
        ], '스캔 경로, getID3, 누락 메타데이터, 추천 기반 데이터를 확인합니다.'),
        diagnosticBuildGroup('플레이어 / PWA / API', [
            checkPwaFiles(),
            checkEndpointFilesStatus(),
            checkPwaPlayerStatus(),
            checkApiStatus(),
        ], 'stream.php, cover.php, 서비스워커, 내부 API, 캐시 제외 정책을 확인합니다.'),
        diagnosticBuildGroup('운영 / 보안', [
            checkBackupStatus(),
            checkRecentErrors(),
            checkAdminSecurityStatus(),
        ], '백업, 최근 오류, 관리자 계정 상태를 확인합니다.'),
    ];

    $all = [];
    foreach ($groups as $group) {
        foreach ($group['items'] as $item) $all[] = $item;
    }

    return [
        'generated_at' => date('Y-m-d H:i:s'),
        'service_worker_version' => diagnosticServiceWorkerVersion(),
        'groups' => $groups,
        'summary' => diagnosticSummarizeItems($all),
    ];
}

function diagnosticRecentAppLogCount($days, $conditions = [], $params = []) {
    $days = max(1, min(90, (int)$days));
    $since = date('Y-m-d H:i:s', time() - ($days * 86400));
    $sql = "SELECT COUNT(*) FROM app_logs WHERE created_at >= :since";
    if ($conditions) {
        $sql .= " AND (" . implode(' OR ', $conditions) . ")";
    }
    $params = array_merge([':since' => $since], $params);
    return diagnosticCount($sql, $params);
}

function getOperationalLogBreakdown($days = 7) {
    $days = max(1, min(90, (int)$days));
    $definitions = [
        [
            'key' => 'fatal_sql',
            'label' => 'Fatal / SQL',
            'conditions' => [
                "log_level = 'critical'",
                "log_type LIKE :fatal_type",
                "log_type LIKE :sql_type",
                "message LIKE :fatal_msg",
                "message LIKE :sql_msg",
                "message LIKE :hy093_msg",
            ],
            'params' => [
                ':fatal_type' => '%fatal%',
                ':sql_type' => '%sql%',
                ':fatal_msg' => '%Fatal%',
                ':sql_msg' => '%SQLSTATE%',
                ':hy093_msg' => '%HY093%',
            ],
            'error_threshold' => 1,
        ],
        [
            'key' => 'stream_cover',
            'label' => 'Stream / Cover',
            'conditions' => [
                "log_type LIKE :stream_type",
                "log_type LIKE :cover_type",
                "message LIKE :stream_msg",
                "message LIKE :cover_msg",
            ],
            'params' => [
                ':stream_type' => '%stream%',
                ':cover_type' => '%cover%',
                ':stream_msg' => '%stream.php%',
                ':cover_msg' => '%cover.php%',
            ],
        ],
        [
            'key' => 'api_pjax',
            'label' => 'API / PJAX',
            'conditions' => [
                "log_type LIKE :api_type",
                "log_type LIKE :pjax_type",
                "message LIKE :api_msg",
                "message LIKE :pjax_msg",
                "message LIKE :json_msg",
            ],
            'params' => [
                ':api_type' => '%api%',
                ':pjax_type' => '%pjax%',
                ':api_msg' => '%api%',
                ':pjax_msg' => '%pjax%',
                ':json_msg' => '%json%',
            ],
        ],
        [
            'key' => 'pwa_cache',
            'label' => 'PWA / Cache',
            'conditions' => [
                "log_type LIKE :pwa_type",
                "log_type LIKE :sw_type",
                "message LIKE :pwa_msg",
                "message LIKE :sw_msg",
                "message LIKE :cache_msg",
            ],
            'params' => [
                ':pwa_type' => '%pwa%',
                ':sw_type' => '%service_worker%',
                ':pwa_msg' => '%PWA%',
                ':sw_msg' => '%service worker%',
                ':cache_msg' => '%cache%',
            ],
        ],
        [
            'key' => 'auth_security',
            'label' => 'Auth / CSRF',
            'conditions' => [
                "log_type LIKE :auth_type",
                "log_type LIKE :csrf_type",
                "log_type LIKE :permission_type",
                "message LIKE :auth_msg",
                "message LIKE :csrf_msg",
                "message LIKE :permission_msg",
            ],
            'params' => [
                ':auth_type' => '%auth%',
                ':csrf_type' => '%csrf%',
                ':permission_type' => '%permission%',
                ':auth_msg' => '%auth%',
                ':csrf_msg' => '%CSRF%',
                ':permission_msg' => '%permission%',
            ],
        ],
        [
            'key' => 'scan_getid3',
            'label' => 'Scan / getID3',
            'conditions' => [
                "log_type LIKE :scan_type",
                "log_type LIKE :getid3_type",
                "log_type LIKE :metadata_type",
                "message LIKE :scan_msg",
                "message LIKE :getid3_msg",
                "message LIKE :metadata_msg",
            ],
            'params' => [
                ':scan_type' => '%scan%',
                ':getid3_type' => '%getid3%',
                ':metadata_type' => '%metadata%',
                ':scan_msg' => '%scan%',
                ':getid3_msg' => '%getID3%',
                ':metadata_msg' => '%metadata%',
            ],
        ],
        [
            'key' => 'backup_io',
            'label' => 'Backup / Import',
            'conditions' => [
                "log_type LIKE :backup_type",
                "log_type LIKE :restore_type",
                "log_type LIKE :import_type",
                "log_type LIKE :export_type",
                "message LIKE :backup_msg",
            ],
            'params' => [
                ':backup_type' => '%backup%',
                ':restore_type' => '%restore%',
                ':import_type' => '%import%',
                ':export_type' => '%export%',
                ':backup_msg' => '%backup%',
            ],
        ],
    ];

    $rows = [];
    foreach ($definitions as $definition) {
        $count = diagnosticRecentAppLogCount($days, $definition['conditions'], $definition['params']);
        $status = 'ok';
        if ($count > 0) $status = !empty($definition['error_threshold']) ? 'error' : 'warning';
        $rows[] = [
            'key' => $definition['key'],
            'label' => $definition['label'],
            'count' => $count,
            'status' => $status,
            'detail' => '최근 ' . $days . '일 기준',
        ];
    }
    return $rows;
}

function getOperationalAlerts($limit = 8) {
    $limit = max(1, min(20, (int)$limit));
    $alerts = [];
    foreach (getOperationalLogBreakdown(7) as $row) {
        if ($row['status'] === 'ok') continue;
        $alerts[] = diagnosticResult(
            $row['status'],
            $row['label'] . ' 로그 확인',
            number_format((int)$row['count']) . '건의 운영 로그가 최근 7일 안에 기록되었습니다.',
            $row['detail']
        );
    }

    try {
        $stats = getLibraryStats();
        if ((int)($stats['missing'] ?? 0) > 0) {
            $alerts[] = diagnosticResult('warning', '파일 접근 불가 의심', '음악 파일 없음 상태의 곡이 있습니다.', number_format((int)$stats['missing']) . '곡');
        }
        if ((int)($stats['metadata_missing'] ?? 0) > 0) {
            $alerts[] = diagnosticResult('warning', '메타데이터 누락', '제목, 가수, 앨범, 장르 등 누락 메타데이터가 있습니다.', number_format((int)$stats['metadata_missing']) . '곡');
        }
    } catch (Throwable $e) {
        $alerts[] = diagnosticResult('unknown', '보관함 통계 확인 필요', '보관함 통계를 읽지 못했습니다.', $e->getMessage());
    }

    $fallbackCount = diagnosticCount("SELECT COUNT(*) FROM songs WHERE metadata_source IN ('filename','folder','fallback')");
    if ($fallbackCount > 0) {
        $alerts[] = diagnosticResult('warning', 'Fallback 메타데이터', 'getID3 대신 파일명/폴더명 기반 분석값을 사용하는 곡이 있습니다.', number_format($fallbackCount) . '곡');
    }

    $backupCheck = checkBackupStatus();
    if (($backupCheck['status'] ?? 'unknown') !== 'ok') {
        $alerts[] = $backupCheck;
    }

    usort($alerts, function ($a, $b) {
        return diagnosticStatusWeight($b['status'] ?? 'unknown') <=> diagnosticStatusWeight($a['status'] ?? 'unknown');
    });
    return array_slice($alerts, 0, $limit);
}

function getDiagnosticsResults() {
    return [
        checkPhpStatus(),
        checkDatabaseConnection(),
        checkRequiredTables(),
        checkScanPath(),
        checkStorageWritable(),
        checkGetId3Status(),
        checkPwaFiles(),
        checkEndpointFilesStatus(),
        checkPwaPlayerStatus(),
        checkApiStatus(),
        checkBackupStatus(),
        checkEncodingStatus(),
        checkRecentErrors(),
        checkMusicStats(),
        checkRecommendationStatus(),
        checkAdminSecurityStatus(),
    ];
}

function getDiagnosticAlerts() {
    $alerts = [];
    foreach (getDiagnosticsResults() as $row) {
        if ($row['status'] !== 'ok') $alerts[] = $row;
    }
    foreach (getOperationalAlerts(5) as $row) {
        $alerts[] = $row;
    }
    usort($alerts, function ($a, $b) {
        return diagnosticStatusWeight($b['status'] ?? 'unknown') <=> diagnosticStatusWeight($a['status'] ?? 'unknown');
    });
    return $alerts;
}
