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

function adminNotificationAllowedCategories() {
    return [
        'operation',
        'error',
        'playback_failure',
        'feedback',
        'metadata_request',
        'update',
        'maintenance',
        'security',
        'scan',
        'backup',
        'api',
        'pwa',
        'player',
    ];
}

function adminNotificationAllowedLevels() {
    return ['info', 'warning', 'error', 'critical'];
}

function adminNotificationLevelFromStatus($status) {
    if ($status === 'error') return 'error';
    if ($status === 'warning' || $status === 'unknown') return 'warning';
    return 'info';
}

function adminNotificationNormalizeCategory($category) {
    $category = preg_replace('/[^a-z0-9_\-]/i', '', (string)$category) ?: 'operation';
    return in_array($category, adminNotificationAllowedCategories(), true) ? $category : 'operation';
}

function adminNotificationNormalizeLevel($level) {
    $level = (string)$level;
    return in_array($level, adminNotificationAllowedLevels(), true) ? $level : 'info';
}

function adminNotificationSanitizeText($value) {
    $text = diagnosticMaskSensitiveText(ensureUtf8String((string)$value));
    $text = preg_replace('/[A-Z]:\\\\[^\s<>"\']+/u', '[경로 숨김]', $text);
    $text = preg_replace('/SQLSTATE\[[^\]]+\][^\r\n]*/iu', '데이터 처리 오류', $text);
    return trim((string)$text);
}

function adminNotificationCategoryFromText($title, $message = '') {
    $text = ensureUtf8String($title . ' ' . $message);
    $lower = function_exists('mb_strtolower') ? mb_strtolower($text, 'UTF-8') : strtolower($text);

    if (strpos($lower, 'feedback') !== false || strpos($text, '피드백') !== false || strpos($text, '오류 신고') !== false) return 'feedback';
    if (strpos($lower, 'metadata request') !== false || strpos($text, '수정 요청') !== false || strpos($text, '보정 요청') !== false) return 'metadata_request';
    if (strpos($lower, 'playback failure') !== false || strpos($lower, 'stream failure') !== false || strpos($text, '재생 실패') !== false) return 'playback_failure';
    if (strpos($lower, 'patch') !== false || strpos($lower, 'update') !== false || strpos($text, '패치') !== false || strpos($text, '업데이트') !== false) return 'update';
    if (strpos($lower, 'maintenance') !== false || strpos($text, '점검') !== false) return 'maintenance';
    if (strpos($lower, 'csrf') !== false || strpos($lower, 'auth') !== false || strpos($lower, 'permission') !== false || strpos($text, '권한') !== false || strpos($text, '보안') !== false) return 'security';
    if (strpos($lower, 'backup') !== false || strpos($lower, 'restore') !== false || strpos($lower, 'import') !== false || strpos($lower, 'export') !== false || strpos($text, '백업') !== false || strpos($text, '복원') !== false) return 'backup';
    if (strpos($lower, 'scan') !== false || strpos($lower, 'getid3') !== false || strpos($lower, 'metadata') !== false || strpos($lower, 'fallback') !== false || strpos($text, '스캔') !== false || strpos($text, '메타데이터') !== false) return 'scan';
    if (strpos($lower, 'api') !== false || strpos($lower, 'pjax') !== false) return 'api';
    if (strpos($lower, 'pwa') !== false || strpos($lower, 'service worker') !== false || strpos($lower, 'cache') !== false || strpos($text, '캐시') !== false) return 'pwa';
    if (strpos($lower, 'stream') !== false || strpos($lower, 'cover') !== false || strpos($lower, 'player') !== false || strpos($text, '플레이어') !== false) return 'player';
    if (strpos($lower, 'error') !== false || strpos($lower, 'fatal') !== false || strpos($text, '오류') !== false || strpos($text, '실패') !== false) return 'error';

    return 'operation';
}

function adminNotificationRelatedUrl($category, $title = '') {
    $map = [
        'security' => 'audit_logs.php',
        'backup' => 'backup_center.php',
        'scan' => 'metadata_quality.php',
        'api' => 'logs.php?q=api',
        'pwa' => 'diagnostics.php',
        'player' => 'logs.php?q=stream',
        'playback_failure' => 'playback_failures.php',
        'feedback' => 'feedback.php',
        'metadata_request' => 'metadata_requests.php',
        'update' => 'patch_notes.php',
        'maintenance' => 'maintenance.php',
        'error' => 'logs.php?level=error',
    ];
    return $map[$category] ?? 'diagnostics.php';
}

function adminNotificationFingerprint($category, $level, $title, $relatedType = null, $relatedId = null) {
    return sha1($category . '|' . $level . '|' . adminNotificationSanitizeText($title) . '|' . (string)$relatedType . '|' . (string)$relatedId);
}

function upsertAdminNotification($data) {
    $category = adminNotificationNormalizeCategory($data['category'] ?? 'operation');
    $level = adminNotificationNormalizeLevel($data['level'] ?? 'info');
    $status = (string)($data['status'] ?? 'active');
    if (!in_array($status, ['active', 'resolved', 'archived'], true)) $status = 'active';

    $title = adminNotificationSanitizeText($data['title'] ?? '운영 알림');
    $message = adminNotificationSanitizeText($data['message'] ?? '');
    $detail = adminNotificationSanitizeText($data['detail'] ?? '');
    $relatedType = adminNotificationSanitizeText($data['related_type'] ?? $category);
    $relatedId = isset($data['related_id']) && $data['related_id'] !== '' ? (int)$data['related_id'] : null;
    $relatedUrl = adminNotificationSanitizeText($data['related_url'] ?? adminNotificationRelatedUrl($category, $title));
    $expiresAt = $data['expires_at'] ?? null;
    $isPinned = !empty($data['is_pinned']) ? 1 : 0;
    $fingerprint = (string)($data['fingerprint'] ?? adminNotificationFingerprint($category, $level, $title, $relatedType, $relatedId));

    $stmt = getDBConnection()->prepare("
        INSERT INTO admin_notifications
            (fingerprint, category, level, status, title, message, detail, related_type, related_id, related_url, is_read, is_pinned, is_hidden, first_seen_at, last_seen_at, expires_at)
        VALUES
            (:fingerprint, :category, :level, :status, :title, :message, :detail, :related_type, :related_id, :related_url, 0, :is_pinned, 0, NOW(), NOW(), :expires_at)
        ON DUPLICATE KEY UPDATE
            category = VALUES(category),
            level = VALUES(level),
            status = VALUES(status),
            title = VALUES(title),
            message = VALUES(message),
            detail = VALUES(detail),
            related_type = VALUES(related_type),
            related_id = VALUES(related_id),
            related_url = VALUES(related_url),
            is_hidden = 0,
            last_seen_at = NOW(),
            updated_at = NOW(),
            expires_at = VALUES(expires_at)
    ");
    $stmt->execute([
        ':fingerprint' => $fingerprint,
        ':category' => $category,
        ':level' => $level,
        ':status' => $status,
        ':title' => $title,
        ':message' => $message,
        ':detail' => $detail,
        ':related_type' => $relatedType,
        ':related_id' => $relatedId,
        ':related_url' => $relatedUrl,
        ':is_pinned' => $isPinned,
        ':expires_at' => $expiresAt,
    ]);
}

function syncAdminOperationalNotifications() {
    try {
        foreach (getOperationalAlerts(12) as $alert) {
            $title = $alert['title'] ?? '운영 알림';
            $message = $alert['message'] ?? '';
            $category = adminNotificationCategoryFromText($title, $message);
            upsertAdminNotification([
                'category' => $category,
                'level' => adminNotificationLevelFromStatus($alert['status'] ?? 'unknown'),
                'status' => 'active',
                'title' => $title,
                'message' => $message,
                'detail' => $alert['detail'] ?? '',
                'related_type' => $category,
                'related_url' => adminNotificationRelatedUrl($category, $title),
            ]);
        }

        $report = getWeeklyOperationReport();
        if (($report['summary']['warnings'] ?? 0) > 0 || ($report['summary']['errors'] ?? 0) > 0) {
            upsertAdminNotification([
                'category' => 'operation',
                'level' => ($report['summary']['errors'] ?? 0) > 0 ? 'error' : 'warning',
                'status' => 'active',
                'title' => '최근 7일 운영 리포트 확인 필요',
                'message' => '최근 7일 기준 오류 또는 주의 항목이 있습니다.',
                'detail' => '오류 ' . number_format((int)($report['summary']['errors'] ?? 0)) . '건, 주의 ' . number_format((int)($report['summary']['warnings'] ?? 0)) . '건',
                'related_type' => 'weekly_report',
                'related_url' => 'alerts.php#weekly-report',
            ]);
        }
    } catch (Throwable $e) {
        writeAppLog('warning', 'notification_sync_error', 'Admin notification sync failed', ['message' => $e->getMessage()]);
    }
}

function getAdminNotificationSummary() {
    try {
        $row = getDBConnection()->query("
            SELECT
                COUNT(*) AS total_count,
                COALESCE(SUM(CASE WHEN is_read = 0 THEN 1 ELSE 0 END), 0) AS unread_count,
                COALESCE(SUM(CASE WHEN is_read = 0 AND level IN ('error','critical') THEN 1 ELSE 0 END), 0) AS unread_critical_count,
                COALESCE(SUM(CASE WHEN is_read = 0 AND category = 'security' THEN 1 ELSE 0 END), 0) AS unread_security_count,
                COALESCE(SUM(CASE WHEN is_read = 0 AND category = 'backup' THEN 1 ELSE 0 END), 0) AS unread_backup_count,
                COALESCE(SUM(CASE WHEN is_pinned = 1 THEN 1 ELSE 0 END), 0) AS pinned_count
            FROM admin_notifications
            WHERE status = 'active'
              AND is_hidden = 0
              AND (expires_at IS NULL OR expires_at > NOW())
        ")->fetch() ?: [];
        return [
            'total' => (int)($row['total_count'] ?? 0),
            'unread' => (int)($row['unread_count'] ?? 0),
            'critical' => (int)($row['unread_critical_count'] ?? 0),
            'security' => (int)($row['unread_security_count'] ?? 0),
            'backup' => (int)($row['unread_backup_count'] ?? 0),
            'pinned' => (int)($row['pinned_count'] ?? 0),
        ];
    } catch (Throwable $e) {
        return ['total' => 0, 'unread' => 0, 'critical' => 0, 'security' => 0, 'backup' => 0, 'pinned' => 0];
    }
}

function getAdminNotifications($filters = []) {
    $page = max(1, (int)($filters['page'] ?? 1));
    $limit = max(10, min(80, (int)($filters['limit'] ?? 24)));
    $offset = ($page - 1) * $limit;
    $where = ["status = 'active'", "(expires_at IS NULL OR expires_at > NOW())"];
    $params = [];

    $category = adminNotificationNormalizeCategory($filters['category'] ?? '');
    if (!empty($filters['category']) && $category !== '') {
        $where[] = 'category = :category';
        $params[':category'] = $category;
    }

    $level = (string)($filters['level'] ?? '');
    if ($level !== '' && in_array($level, adminNotificationAllowedLevels(), true)) {
        $where[] = 'level = :level';
        $params[':level'] = $level;
    }

    if (($filters['read'] ?? '') === 'unread') {
        $where[] = 'is_read = 0';
    } elseif (($filters['read'] ?? '') === 'read') {
        $where[] = 'is_read = 1';
    }

    if (($filters['pinned'] ?? '') === 'pinned') {
        $where[] = 'is_pinned = 1';
    }

    if (($filters['hidden'] ?? '') === 'hidden') {
        $where[] = 'is_hidden = 1';
    } else {
        $where[] = 'is_hidden = 0';
    }

    $whereSql = ' WHERE ' . implode(' AND ', $where);
    $countStmt = getDBConnection()->prepare("SELECT COUNT(*) FROM admin_notifications" . $whereSql);
    $countStmt->execute($params);
    $total = (int)$countStmt->fetchColumn();
    $pages = max(1, (int)ceil($total / $limit));
    $page = min($page, $pages);
    $offset = ($page - 1) * $limit;

    $stmt = getDBConnection()->prepare("
        SELECT id, category, level, title, message, detail, related_type, related_id, related_url,
               is_read, is_pinned, is_hidden, read_at, hidden_at, first_seen_at, last_seen_at
        FROM admin_notifications
        $whereSql
        ORDER BY is_pinned DESC,
                 is_read ASC,
                 CASE level WHEN 'critical' THEN 0 WHEN 'error' THEN 1 WHEN 'warning' THEN 2 ELSE 3 END,
                 last_seen_at DESC,
                 id DESC
        LIMIT " . (int)$limit . " OFFSET " . (int)$offset
    );
    $stmt->execute($params);
    $rows = $stmt->fetchAll();
    foreach ($rows as &$row) {
        $row['title'] = adminNotificationSanitizeText($row['title'] ?? '');
        $row['message'] = adminNotificationSanitizeText($row['message'] ?? '');
        $row['detail'] = adminNotificationSanitizeText($row['detail'] ?? '');
        $row['related_url'] = adminNotificationSanitizeText($row['related_url'] ?? '');
    }
    unset($row);

    return ['rows' => $rows, 'total' => $total, 'pages' => $pages, 'page' => $page, 'limit' => $limit];
}

function markAdminNotificationRead($id) {
    $id = (int)$id;
    if ($id <= 0) return 0;
    $stmt = getDBConnection()->prepare("
        UPDATE admin_notifications
        SET is_read = 1, read_at = NOW(), updated_at = NOW()
        WHERE id = :id
    ");
    $stmt->execute([':id' => $id]);
    return $stmt->rowCount();
}

function markAdminNotificationsRead($ids) {
    $ids = array_values(array_unique(array_filter(array_map('intval', (array)$ids))));
    if (!$ids) return 0;
    $placeholders = [];
    $params = [];
    foreach ($ids as $idx => $id) {
        $key = ':id_' . $idx;
        $placeholders[] = $key;
        $params[$key] = $id;
    }
    $stmt = getDBConnection()->prepare("
        UPDATE admin_notifications
        SET is_read = 1, read_at = NOW(), updated_at = NOW()
        WHERE id IN (" . implode(',', $placeholders) . ")
    ");
    $stmt->execute($params);
    return $stmt->rowCount();
}

function markAllAdminNotificationsRead($category = '') {
    $params = [];
    $sql = "
        UPDATE admin_notifications
        SET is_read = 1, read_at = NOW(), updated_at = NOW()
        WHERE is_read = 0
          AND is_hidden = 0
          AND status = 'active'
    ";
    if ($category !== '') {
        $sql .= " AND category = :category";
        $params[':category'] = adminNotificationNormalizeCategory($category);
    }
    $stmt = getDBConnection()->prepare($sql);
    $stmt->execute($params);
    return $stmt->rowCount();
}

function toggleAdminNotificationPin($id) {
    $id = (int)$id;
    if ($id <= 0) return 0;
    $stmt = getDBConnection()->prepare("
        UPDATE admin_notifications
        SET is_pinned = CASE WHEN is_pinned = 1 THEN 0 ELSE 1 END,
            updated_at = NOW()
        WHERE id = :id
    ");
    $stmt->execute([':id' => $id]);
    return $stmt->rowCount();
}

function hideAdminNotification($id) {
    $id = (int)$id;
    if ($id <= 0) return 0;
    $stmt = getDBConnection()->prepare("
        UPDATE admin_notifications
        SET is_hidden = 1, hidden_at = NOW(), updated_at = NOW()
        WHERE id = :id
    ");
    $stmt->execute([':id' => $id]);
    return $stmt->rowCount();
}

function archiveOldAdminNotifications($days = 90) {
    $days = max(7, min(365, (int)$days));
    $before = date('Y-m-d H:i:s', time() - ($days * 86400));
    $stmt = getDBConnection()->prepare("
        UPDATE admin_notifications
        SET is_hidden = 1, hidden_at = NOW(), updated_at = NOW()
        WHERE is_hidden = 0
          AND is_read = 1
          AND is_pinned = 0
          AND last_seen_at < :before
    ");
    $stmt->execute([':before' => $before]);
    return $stmt->rowCount();
}

function cleanupReadAdminNotifications($days = 30) {
    return archiveOldAdminNotifications($days);
}

function adminNotificationCategoryLabel($category) {
    $labels = [
        'operation' => '운영',
        'error' => '오류',
        'playback_failure' => '재생 실패',
        'feedback' => '피드백',
        'metadata_request' => '수정 요청',
        'update' => '업데이트',
        'maintenance' => '점검',
        'security' => '보안',
        'scan' => '스캔 / 메타데이터',
        'backup' => '백업 / 복원',
        'api' => 'API / PJAX',
        'pwa' => 'PWA / 캐시',
        'player' => '플레이어',
    ];
    return $labels[$category] ?? '운영';
}

function adminNotificationLevelLabel($level) {
    $labels = ['info' => '정보', 'warning' => '주의', 'error' => '오류', 'critical' => '긴급'];
    return $labels[$level] ?? '정보';
}

function getWeeklyOperationReport() {
    $since = date('Y-m-d H:i:s', time() - (7 * 86400));
    $pdo = getDBConnection();
    $cards = [];
    $errors = 0;
    $warnings = 0;

    $playCount = diagnosticCount("SELECT COUNT(*) FROM play_history WHERE played_at >= :since", [':since' => $since]);
    $newSongCount = diagnosticCount("SELECT COUNT(*) FROM songs WHERE created_at >= :since", [':since' => $since]);
    $missingCount = diagnosticCount("SELECT COUNT(*) FROM songs WHERE is_missing = 1");
    $metadataMissing = diagnosticCount("SELECT COUNT(*) FROM songs WHERE is_missing = 0 AND (title = '' OR title IS NULL OR artist IS NULL OR artist = '' OR album IS NULL OR album = '' OR genre IS NULL OR genre = '' OR genre = 'Unknown')");
    $coverMissing = diagnosticCount("SELECT COUNT(*) FROM songs WHERE is_missing = 0 AND (has_cover = 0 OR has_cover IS NULL)");
    $duplicates = countDuplicateSuspects();

    $scanRow = [];
    try {
        $stmt = $pdo->prepare("
            SELECT
                COUNT(*) AS scan_runs,
                SUM(CASE WHEN scan_finished_at IS NOT NULL THEN 1 ELSE 0 END) AS scan_finished,
                SUM(CASE WHEN metadata_error_count > 0 OR message LIKE :error_like THEN 1 ELSE 0 END) AS scan_failed,
                SUM(COALESCE(tag_count, 0)) AS tag_count,
                SUM(COALESCE(filename_count, 0)) AS fallback_count,
                SUM(COALESCE(metadata_error_count, 0)) AS metadata_error_count,
                SUM(COALESCE(cover_missing_count, 0)) AS cover_missing_count
            FROM scan_logs
            WHERE created_at >= :since
        ");
        $stmt->execute([':since' => $since, ':error_like' => '%ERROR%']);
        $scanRow = $stmt->fetch() ?: [];
    } catch (Throwable $e) {
        $scanRow = [];
    }

    $backupErrors = diagnosticRecentAppLogCount(7, [
        "log_type LIKE :backup_type",
        "log_type LIKE :restore_type",
        "log_type LIKE :import_type",
        "log_type LIKE :export_type",
    ], [
        ':backup_type' => '%backup%',
        ':restore_type' => '%restore%',
        ':import_type' => '%import%',
        ':export_type' => '%export%',
    ]);

    foreach (getOperationalLogBreakdown(7) as $row) {
        if (($row['status'] ?? 'ok') === 'error') $errors += (int)$row['count'];
        elseif (($row['status'] ?? 'ok') === 'warning') $warnings += (int)$row['count'];
    }
    if ($missingCount > 0) $warnings++;
    if ($metadataMissing > 0) $warnings++;
    if ($coverMissing > 0) $warnings++;
    if ($backupErrors > 0) $warnings += $backupErrors;

    $cards[] = ['label' => '최근 7일 재생', 'value' => number_format($playCount), 'status' => 'ok', 'detail' => 'play_history 기준'];
    $cards[] = ['label' => '신규 등록 곡', 'value' => number_format($newSongCount), 'status' => 'ok', 'detail' => 'songs.created_at 기준'];
    $cards[] = ['label' => '스캔 실행', 'value' => number_format((int)($scanRow['scan_runs'] ?? 0)), 'status' => ((int)($scanRow['scan_failed'] ?? 0) > 0 ? 'warning' : 'ok'), 'detail' => '실패 의심 ' . number_format((int)($scanRow['scan_failed'] ?? 0)) . '건'];
    $cards[] = ['label' => 'getID3 / fallback', 'value' => number_format((int)($scanRow['tag_count'] ?? 0)) . ' / ' . number_format((int)($scanRow['fallback_count'] ?? 0)), 'status' => ((int)($scanRow['metadata_error_count'] ?? 0) > 0 ? 'warning' : 'ok'), 'detail' => '메타데이터 오류 ' . number_format((int)($scanRow['metadata_error_count'] ?? 0)) . '건'];
    $cards[] = ['label' => '파일 없음', 'value' => number_format($missingCount), 'status' => $missingCount > 0 ? 'warning' : 'ok', 'detail' => '현재 보관함 기준'];
    $cards[] = ['label' => '앨범아트 없음', 'value' => number_format($coverMissing), 'status' => $coverMissing > 0 ? 'warning' : 'ok', 'detail' => '현재 보관함 기준'];
    $cards[] = ['label' => '메타데이터 경고', 'value' => number_format($metadataMissing), 'status' => $metadataMissing > 0 ? 'warning' : 'ok', 'detail' => '제목/가수/앨범/장르 누락'];
    $cards[] = ['label' => '중복 후보', 'value' => number_format($duplicates), 'status' => $duplicates > 0 ? 'warning' : 'ok', 'detail' => 'file_hash 기준'];

    $topSongs = [];
    try {
        $stmt = $pdo->prepare("
            SELECT s.id, s.title, s.artist, COUNT(*) AS play_count
            FROM play_history h
            INNER JOIN songs s ON s.id = h.song_id
            WHERE h.played_at >= :since
            GROUP BY s.id, s.title, s.artist
            ORDER BY play_count DESC, s.id DESC
            LIMIT 5
        ");
        $stmt->execute([':since' => $since]);
        $topSongs = $stmt->fetchAll();
    } catch (Throwable $e) {
        $topSongs = [];
    }

    return [
        'since' => $since,
        'generated_at' => date('Y-m-d H:i:s'),
        'summary' => ['errors' => $errors, 'warnings' => $warnings],
        'cards' => $cards,
        'top_songs' => $topSongs,
        'log_breakdown' => getOperationalLogBreakdown(7),
    ];
}
