<?php

require_once __DIR__ . '/functions.php';
require_once __DIR__ . '/admin_backup_status.php';
require_once __DIR__ . '/admin_library_health.php';

function adminDiagnosticReportTableExists($table) {
    if (function_exists('diagnosticTableExists')) {
        return 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 adminDiagnosticReportCount($sql, $params = []) {
    try {
        $stmt = getDBConnection()->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    } catch (Throwable $e) {
        return 0;
    }
}

function adminDiagnosticReportScalar($sql, $params = [], $fallback = 0) {
    try {
        $stmt = getDBConnection()->prepare($sql);
        $stmt->execute($params);
        $value = $stmt->fetchColumn();
        return $value === false || $value === null ? $fallback : $value;
    } catch (Throwable $e) {
        return $fallback;
    }
}

function adminDiagnosticReportStatus($value, $warningAt = 1, $errorAt = null) {
    $value = (int)$value;
    if ($errorAt !== null && $value >= (int)$errorAt) return 'error';
    if ($value >= (int)$warningAt) return 'warning';
    return 'ok';
}

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

function adminDiagnosticReportItem($key, $title, $count, $status, $detail, $href, $icon) {
    return [
        'key' => $key,
        'title' => maskInternalPathText($title),
        'count' => max(0, (int)$count),
        'status' => in_array($status, ['ok', 'warning', 'error'], true) ? $status : 'ok',
        'label' => adminDiagnosticReportStatusLabel($status),
        'detail' => maskInternalPathText($detail),
        'href' => $href,
        'icon' => $icon,
    ];
}

function getAdminAutoDiagnosticReport() {
    $items = [];

    $playbackFailures = 0;
    if (adminDiagnosticReportTableExists('app_playback_failures')) {
        $playbackFailures = adminDiagnosticReportCount("
            SELECT COUNT(*)
            FROM app_playback_failures
            WHERE COALESCE(status, 'new') NOT IN ('resolved', 'ignored')
        ");
    }
    $items[] = adminDiagnosticReportItem(
        'playback_failures',
        '재생 실패',
        $playbackFailures,
        adminDiagnosticReportStatus($playbackFailures, 1, 10),
        '미해결 재생 실패 후보',
        'playback_failures.php',
        'fa-solid fa-circle-exclamation'
    );

    $scanFailures = adminDiagnosticReportCount("
        SELECT COUNT(*)
        FROM scan_logs
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL 14 DAY)
          AND (
              COALESCE(metadata_error_count, 0) > 0
              OR COALESCE(missing_count, 0) > 0
              OR message LIKE :error_en
              OR message LIKE :failed_en
          )
    ", [
        ':error_en' => '%ERROR%',
        ':failed_en' => '%fail%',
    ]);
    $items[] = adminDiagnosticReportItem(
        'scan_failures',
        '스캔 실패',
        $scanFailures,
        adminDiagnosticReportStatus($scanFailures, 1, 5),
        '최근 14일 스캔 오류 또는 실패 기록',
        'scan.php',
        'fa-solid fa-rotate'
    );

    $libraryHealth = function_exists('getAdminLibraryHealthReport') ? getAdminLibraryHealthReport(3) : [];
    $libraryHealthIssueCount = (int)($libraryHealth['summary']['total_issues'] ?? 0);
    $items[] = adminDiagnosticReportItem(
        'library_health',
        '음악 라이브러리 건강검진',
        $libraryHealthIssueCount,
        (string)($libraryHealth['summary']['overall'] ?? 'ok'),
        (string)($libraryHealth['message'] ?? '음악 파일과 DB 상태를 점검합니다.'),
        'library_health.php',
        'fa-solid fa-heart-pulse'
    );

    $getId3Failures = (int)adminDiagnosticReportScalar("
        SELECT COALESCE(SUM(COALESCE(metadata_error_count, 0)), 0)
        FROM scan_logs
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL 14 DAY)
    ");
    $items[] = adminDiagnosticReportItem(
        'getid3_failures',
        'getID3 실패',
        $getId3Failures,
        adminDiagnosticReportStatus($getId3Failures, 1, 20),
        '최근 14일 메타데이터 분석 실패 합계',
        'metadata_quality.php?filter=getid3_error',
        'fa-solid fa-wand-magic-sparkles'
    );

    $missingCovers = adminDiagnosticReportCount("
        SELECT COUNT(*)
        FROM songs
        WHERE COALESCE(is_missing, 0) = 0
          AND COALESCE(is_hidden, 0) = 0
          AND (COALESCE(has_cover, 0) = 0 OR cover_hash IS NULL OR TRIM(cover_hash) = '')
    ");
    $items[] = adminDiagnosticReportItem(
        'missing_covers',
        '앨범아트 없음',
        $missingCovers,
        adminDiagnosticReportStatus($missingCovers, 1, 100),
        '현재 보관함 기준 앨범아트 누락',
        'metadata_quality.php?filter=missing_cover',
        'fa-regular fa-image'
    );

    $missingLyrics = adminDiagnosticReportCount("
        SELECT COUNT(*)
        FROM songs s
        LEFT JOIN lyrics l ON l.song_id = s.id
        WHERE COALESCE(s.is_missing, 0) = 0
          AND COALESCE(s.is_hidden, 0) = 0
          AND (l.id IS NULL OR TRIM(COALESCE(l.lyrics_text, '')) = '')
    ");
    $items[] = adminDiagnosticReportItem(
        'missing_lyrics',
        '가사 없음',
        $missingLyrics,
        adminDiagnosticReportStatus($missingLyrics, 1, 200),
        '현재 보관함 기준 가사 미등록',
        'metadata_quality.php?filter=missing_lyrics',
        'fa-solid fa-align-left'
    );

    $missingFiles = adminDiagnosticReportCount("
        SELECT COUNT(*)
        FROM songs
        WHERE COALESCE(is_missing, 0) = 1
    ");
    $items[] = adminDiagnosticReportItem(
        'missing_files',
        '파일 없음',
        $missingFiles,
        adminDiagnosticReportStatus($missingFiles, 1, 10),
        '스캔 후 파일 없음으로 표시된 곡',
        'missing_files.php',
        'fa-solid fa-file-circle-question'
    );

    $recentAppErrors = 0;
    $apiJsErrors = 0;
    $appLogSurge = 0;
    if (adminDiagnosticReportTableExists('app_logs')) {
        $recentAppErrors = adminDiagnosticReportCount("
            SELECT COUNT(*)
            FROM app_logs
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
              AND log_level IN ('error', 'critical')
        ");
        $apiJsErrors = adminDiagnosticReportCount("
            SELECT COUNT(*)
            FROM app_logs
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
              AND (
                  log_type LIKE :api_type
                  OR log_type LIKE :js_type
                  OR log_type LIKE :pjax_type
                  OR message LIKE :api_message
                  OR message LIKE :js_message
              )
        ", [
            ':api_type' => '%api%',
            ':js_type' => '%javascript%',
            ':pjax_type' => '%pjax%',
            ':api_message' => '%API%',
            ':js_message' => '%JavaScript%',
        ]);
        $appLogSurge = adminDiagnosticReportCount("
            SELECT COUNT(*)
            FROM app_logs
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
        ");
    }
    $items[] = adminDiagnosticReportItem(
        'recent_errors',
        '최근 오류',
        $recentAppErrors,
        adminDiagnosticReportStatus($recentAppErrors, 1, 20),
        '최근 7일 PHP/API/운영 오류 로그',
        'logs.php?level=error',
        'fa-solid fa-bug'
    );
    $items[] = adminDiagnosticReportItem(
        'api_js_errors',
        'API/JS 오류',
        $apiJsErrors,
        adminDiagnosticReportStatus($apiJsErrors, 1, 20),
        '최근 7일 API, PJAX, JavaScript 관련 로그',
        'logs.php?q=api',
        'fa-solid fa-code'
    );
    $items[] = adminDiagnosticReportItem(
        'app_log_surge',
        '로그 급증',
        $appLogSurge,
        adminDiagnosticReportStatus($appLogSurge, 100, 300),
        '최근 24시간 app_logs 기록 수',
        'performance.php',
        'fa-solid fa-chart-line'
    );

    $backupStatus = getAdminBackupStatusSummary();
    $items[] = adminDiagnosticReportItem(
        'backup_status',
        '백업 상태',
        (int)($backupStatus['backup_count'] ?? 0),
        (string)($backupStatus['status'] ?? 'warning'),
        (string)($backupStatus['restore_label'] ?? '복구 준비 확인 필요'),
        'backup_center.php#backup-status-summary',
        'fa-solid fa-database'
    );

    $overall = 'ok';
    $warningCount = 0;
    $errorCount = 0;
    foreach ($items as $item) {
        if (($item['status'] ?? 'ok') === 'error') {
            $overall = 'error';
            $errorCount++;
        } elseif (($item['status'] ?? 'ok') === 'warning') {
            $warningCount++;
            if ($overall !== 'error') $overall = 'warning';
        }
    }

    return [
        'status' => $overall,
        'label' => adminDiagnosticReportStatusLabel($overall),
        'summary' => [
            'warnings' => $warningCount,
            'errors' => $errorCount,
            'total' => count($items),
        ],
        'items' => $items,
    ];
}

function syncAdminAutoDiagnosticNotification($report) {
    if (!function_exists('upsertAdminNotification')) return;
    $status = $report['status'] ?? 'ok';
    if ($status === 'ok') return;

    $summary = $report['summary'] ?? [];
    try {
        upsertAdminNotification([
            'fingerprint' => sha1('auto_diagnostic_report|summary'),
            'category' => 'operation',
            'level' => $status === 'error' ? 'error' : 'warning',
            'status' => 'active',
            'title' => '자동 점검 리포트 확인 필요',
            'message' => '관리자 대시보드에서 확인이 필요한 운영 점검 항목이 있습니다.',
            'detail' => '확인 필요 ' . number_format((int)($summary['errors'] ?? 0)) . '건 / 주의 ' . number_format((int)($summary['warnings'] ?? 0)) . '건',
            'related_type' => 'auto_diagnostic_report',
            'related_url' => 'index.php#auto-diagnostic-report',
            'is_pinned' => $status === 'error' ? 1 : 0,
        ]);
    } catch (Throwable $e) {
        if (function_exists('writeAppLog')) {
            writeAppLog('warning', 'auto_diagnostic_report', 'Auto diagnostic notification sync failed', [
                'error' => limitLogText($e->getMessage(), 300),
            ]);
        }
    }
}
