<?php

require_once __DIR__ . '/functions.php';

function adminLibraryHealthTableExists($table) {
    static $cache = [];
    $table = (string)$table;
    if ($table === '') return false;
    if (array_key_exists($table, $cache)) return $cache[$table];

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

    return $cache[$table];
}

function adminLibraryHealthScalar($table, $sql, $params = [], $fallback = 0) {
    if (!adminLibraryHealthTableExists($table)) return $fallback;

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

function adminLibraryHealthRows($table, $sql, $params = [], $limit = 5) {
    if (!adminLibraryHealthTableExists($table)) return [];

    try {
        $stmt = getDBConnection()->prepare($sql . ' LIMIT ' . max(1, min(20, (int)$limit)));
        $stmt->execute($params);
        return $stmt->fetchAll() ?: [];
    } catch (Throwable $e) {
        return [];
    }
}

function adminLibraryHealthStatus($count, $warningAt = 1, $errorAt = 50) {
    $count = (int)$count;
    if ($count >= (int)$errorAt) return 'error';
    if ($count >= (int)$warningAt) return 'warning';
    return 'ok';
}

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

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

function adminLibraryHealthLatestScanTime() {
    return (string)adminLibraryHealthScalar(
        'scan_logs',
        "SELECT MAX(COALESCE(scan_finished_at, created_at)) FROM scan_logs",
        [],
        ''
    );
}

function adminLibraryHealthBuildSamples($limit = 5) {
    $limit = max(1, min(10, (int)$limit));
    $samples = [];

    $appendSongRows = function ($issue, $href, $rows) use (&$samples) {
        foreach ($rows as $row) {
            $samples[] = [
                'issue' => $issue,
                'song_id' => (int)($row['id'] ?? 0),
                'title' => trim((string)($row['title'] ?? '')) ?: '제목 없음',
                'artist' => trim((string)($row['artist'] ?? '')) ?: '아티스트 없음',
                'checked_at' => (string)($row['checked_at'] ?? ''),
                'href' => $href,
            ];
        }
    };

    $appendSongRows('파일 없음', 'missing_files.php', adminLibraryHealthRows('songs', "
        SELECT id, title, artist, COALESCE(missing_checked_at, updated_at, metadata_updated_at) AS checked_at
        FROM songs
        WHERE COALESCE(is_missing, 0) = 1
        ORDER BY COALESCE(missing_checked_at, updated_at, metadata_updated_at) DESC, id DESC
    ", [], $limit));

    $appendSongRows('getID3 / 메타데이터 오류', 'metadata_quality.php?filter=getid3_error', adminLibraryHealthRows('songs', "
        SELECT id, title, artist, COALESCE(last_metadata_scan_at, metadata_updated_at, updated_at) AS checked_at
        FROM songs
        WHERE COALESCE(is_missing, 0) = 0
          AND metadata_error IS NOT NULL
          AND TRIM(metadata_error) <> ''
        ORDER BY COALESCE(last_metadata_scan_at, metadata_updated_at, updated_at) DESC, id DESC
    ", [], $limit));

    $appendSongRows('앨범아트 없음', 'metadata_quality.php?filter=missing_cover', adminLibraryHealthRows('songs', "
        SELECT id, title, artist, COALESCE(metadata_updated_at, updated_at) AS checked_at
        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) = '')
        ORDER BY id DESC
    ", [], $limit));

    $appendSongRows('메타데이터 누락', 'metadata_quality.php?filter=needs_review', adminLibraryHealthRows('songs', "
        SELECT id, title, artist, COALESCE(metadata_updated_at, updated_at) AS checked_at
        FROM songs
        WHERE COALESCE(is_missing, 0) = 0
          AND COALESCE(is_hidden, 0) = 0
          AND (
              title IS NULL OR TRIM(title) = ''
              OR artist IS NULL OR TRIM(artist) = ''
              OR album IS NULL OR TRIM(album) = ''
              OR genre IS NULL OR TRIM(genre) = ''
              OR LOWER(TRIM(COALESCE(genre, ''))) = 'unknown'
          )
        ORDER BY id DESC
    ", [], $limit));

    return array_slice($samples, 0, $limit);
}

function getAdminLibraryHealthReport($sampleLimit = 5) {
    $latestScanTime = adminLibraryHealthLatestScanTime();

    $missingFiles = (int)adminLibraryHealthScalar('songs', "
        SELECT COUNT(*)
        FROM songs
        WHERE COALESCE(is_missing, 0) = 1
    ");

    $latestScanNewCandidates = (int)adminLibraryHealthScalar('scan_logs', "
        SELECT COALESCE(new_count, 0)
        FROM scan_logs
        WHERE scan_finished_at IS NOT NULL
        ORDER BY id DESC
        LIMIT 1
    ");

    $playbackRisk = (int)adminLibraryHealthScalar('app_playback_failures', "
        SELECT COUNT(*)
        FROM app_playback_failures
        WHERE COALESCE(status, 'new') NOT IN ('resolved', 'ignored')
    ");

    $getId3Failures = (int)adminLibraryHealthScalar('songs', "
        SELECT COUNT(*)
        FROM songs
        WHERE COALESCE(is_missing, 0) = 0
          AND metadata_error IS NOT NULL
          AND TRIM(metadata_error) <> ''
    ");

    $missingCovers = (int)adminLibraryHealthScalar('songs', "
        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) = '')
    ");

    $missingLyrics = 0;
    if (adminLibraryHealthTableExists('lyrics')) {
        $missingLyrics = (int)adminLibraryHealthScalar('songs', "
            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, '')) = '')
        ");
    }

    $metadataMissing = (int)adminLibraryHealthScalar('songs', "
        SELECT COUNT(*)
        FROM songs
        WHERE COALESCE(is_missing, 0) = 0
          AND COALESCE(is_hidden, 0) = 0
          AND (
              title IS NULL OR TRIM(title) = ''
              OR artist IS NULL OR TRIM(artist) = ''
              OR album IS NULL OR TRIM(album) = ''
              OR genre IS NULL OR TRIM(genre) = ''
          )
    ");

    $uncategorizedGenre = (int)adminLibraryHealthScalar('songs', "
        SELECT COUNT(*)
        FROM songs
        WHERE COALESCE(is_missing, 0) = 0
          AND COALESCE(is_hidden, 0) = 0
          AND LOWER(TRIM(COALESCE(genre, ''))) = 'unknown'
    ");

    $duplicates = (int)adminLibraryHealthScalar('songs', "
        SELECT COUNT(*)
        FROM (
            SELECT file_hash
            FROM songs
            WHERE COALESCE(is_missing, 0) = 0
              AND file_hash IS NOT NULL
              AND TRIM(file_hash) <> ''
            GROUP BY file_hash
            HAVING COUNT(*) > 1
        ) d
    ");

    $mojibakeCandidates = (int)adminLibraryHealthScalar('songs', "
        SELECT COUNT(*)
        FROM songs
        WHERE COALESCE(is_missing, 0) = 0
          AND (
              title LIKE :broken
              OR artist LIKE :broken
              OR album LIKE :broken
              OR genre LIKE :broken
              OR title LIKE :array_text
              OR artist LIKE :array_text
              OR album LIKE :array_text
          )
    ", [
        ':broken' => '%占%',
        ':array_text' => '%Array%',
    ]);

    $recentScanFailures = (int)adminLibraryHealthScalar('scan_logs', "
        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 = [
        adminLibraryHealthItem('missing_files', 'DB 등록 곡 중 파일 없음', $missingFiles, adminLibraryHealthStatus($missingFiles, 1, 10), '스캔 결과 파일 없음으로 표시된 곡', 'missing_files.php', 'fa-solid fa-file-circle-question', $latestScanTime),
        adminLibraryHealthItem('unregistered_candidates', 'DB 미등록 후보', $latestScanNewCandidates, adminLibraryHealthStatus($latestScanNewCandidates, 1, 50), '최근 스캔에서 신규 감지된 파일', 'scan.php', 'fa-solid fa-folder-plus', $latestScanTime),
        adminLibraryHealthItem('playback_risk', '재생 실패 가능 곡', $playbackRisk, adminLibraryHealthStatus($playbackRisk, 1, 10), '미해결 재생 실패 기록', 'playback_failures.php', 'fa-solid fa-triangle-exclamation', $latestScanTime),
        adminLibraryHealthItem('getid3_failures', 'getID3 / 메타데이터 오류', $getId3Failures, adminLibraryHealthStatus($getId3Failures, 1, 20), '분석 오류가 저장된 곡', 'metadata_quality.php?filter=getid3_error', 'fa-solid fa-wand-magic-sparkles', $latestScanTime),
        adminLibraryHealthItem('missing_covers', '앨범아트 없음', $missingCovers, adminLibraryHealthStatus($missingCovers, 1, 100), 'cover.php 대체 커버가 필요한 곡', 'metadata_quality.php?filter=missing_cover', 'fa-regular fa-image', $latestScanTime),
        adminLibraryHealthItem('missing_lyrics', '가사 없음', $missingLyrics, adminLibraryHealthStatus($missingLyrics, 1, 200), '가사 미등록 곡', 'metadata_quality.php?filter=missing_lyrics', 'fa-solid fa-align-left', $latestScanTime),
        adminLibraryHealthItem('metadata_missing', '주요 메타데이터 누락', $metadataMissing, adminLibraryHealthStatus($metadataMissing, 1, 50), '제목, 아티스트, 앨범, 장르 누락', 'metadata_quality.php?filter=needs_review', 'fa-solid fa-tags', $latestScanTime),
        adminLibraryHealthItem('uncategorized_genre', '장르 미분류', $uncategorizedGenre, adminLibraryHealthStatus($uncategorizedGenre, 1, 100), 'Unknown 장르로 분류된 곡', 'metadata_quality.php?filter=missing_genre', 'fa-solid fa-layer-group', $latestScanTime),
        adminLibraryHealthItem('duplicates', '중복 곡 후보', $duplicates, adminLibraryHealthStatus($duplicates, 1, 20), 'file_hash 기준 중복 후보', 'duplicates.php', 'fa-solid fa-clone', $latestScanTime),
        adminLibraryHealthItem('mojibake', '깨진 태그 후보', $mojibakeCandidates, adminLibraryHealthStatus($mojibakeCandidates, 1, 20), '깨진 문자 또는 배열 문자열 후보', 'metadata_quality.php?filter=mojibake', 'fa-solid fa-language', $latestScanTime),
        adminLibraryHealthItem('recent_scan_failures', '최근 스캔 실패 파일', $recentScanFailures, adminLibraryHealthStatus($recentScanFailures, 1, 5), '최근 14일 스캔 오류 또는 누락 집계', 'scan.php', 'fa-solid fa-rotate', $latestScanTime),
    ];

    $summary = ['ok' => 0, 'warning' => 0, 'error' => 0, 'total_issues' => 0, 'overall' => 'ok'];
    foreach ($items as $item) {
        $status = $item['status'];
        $summary[$status] = ($summary[$status] ?? 0) + 1;
        if ($status !== 'ok') $summary['total_issues'] += (int)$item['count'];
        if ($status === 'error') $summary['overall'] = 'error';
        elseif ($status === 'warning' && $summary['overall'] !== 'error') $summary['overall'] = 'warning';
    }

    return [
        'generated_at' => date('Y-m-d H:i:s'),
        'latest_scan_at' => $latestScanTime,
        'summary' => $summary,
        'label' => adminLibraryHealthStatusLabel($summary['overall']),
        'message' => $summary['overall'] === 'ok'
            ? '현재 확인 필요한 라이브러리 문제가 없습니다.'
            : '확인이 필요한 라이브러리 항목이 있습니다.',
        'items' => $items,
        'samples' => adminLibraryHealthBuildSamples($sampleLimit),
    ];
}
