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

function statsLimit($limit, $default = 10) {
    $limit = (int)($limit ?: $default);
    return max(1, min(100, $limit));
}

function listeningCountSince($dateExpression) {
    try {
        $stmt = getDBConnection()->prepare("SELECT COUNT(*) FROM play_history WHERE played_at >= $dateExpression");
        $stmt->execute();
        return (int)$stmt->fetchColumn();
    } catch (Throwable $e) {
        if (function_exists('writeAppLog')) writeAppLog('error', 'db_error', 'Listening count query failed', ['date_expression' => $dateExpression, 'error' => $e->getMessage()]);
        return 0;
    }
}

function getListeningSummary() {
    $pdo = getDBConnection();
    try {
        return [
            'today' => (int)$pdo->query("SELECT COUNT(*) FROM play_history WHERE played_at >= CURDATE()")->fetchColumn(),
            'week' => (int)$pdo->query("SELECT COUNT(*) FROM play_history WHERE played_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)")->fetchColumn(),
            'month' => (int)$pdo->query("SELECT COUNT(*) FROM play_history WHERE played_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)")->fetchColumn(),
            'total' => (int)$pdo->query("SELECT COUNT(*) FROM play_history")->fetchColumn(),
        ];
    } catch (Throwable $e) {
        if (function_exists('writeAppLog')) writeAppLog('error', 'db_error', 'Listening summary query failed', ['error' => $e->getMessage()]);
        return ['today' => 0, 'week' => 0, 'month' => 0, 'total' => 0];
    }
}

function getListeningTrend($days = 7) {
    $days = max(1, min(60, (int)$days));
    try {
        $daysBack = $days - 1;
        $stmt = getDBConnection()->prepare("
            SELECT DATE(played_at) AS day, COUNT(*) AS plays
            FROM play_history
            WHERE played_at >= DATE_SUB(CURDATE(), INTERVAL $daysBack DAY)
            GROUP BY DATE(played_at)
            ORDER BY day ASC
        ");
        $stmt->execute();
        $rows = $stmt->fetchAll();
        $map = [];
        foreach ($rows as $row) $map[$row['day']] = (int)$row['plays'];
        $trend = [];
        for ($i = $days - 1; $i >= 0; $i--) {
            $day = date('Y-m-d', strtotime('-' . $i . ' days'));
            $trend[] = ['label' => date('m/d', strtotime($day)), 'value' => $map[$day] ?? 0];
        }
        return $trend;
    } catch (Throwable $e) {
        if (function_exists('writeAppLog')) writeAppLog('error', 'db_error', 'Listening trend query failed', ['days' => $days, 'error' => $e->getMessage()]);
        return [];
    }
}

function getTopSongsByHistory($limit = 10, $where = '1=1', $params = []) {
    $limit = statsLimit($limit);
    try {
        $sql = baseSongSelectSql() . "
            INNER JOIN (
                SELECT song_id, COUNT(*) AS history_plays, MAX(played_at) AS last_played_at
                FROM play_history
                WHERE $where
                GROUP BY song_id
            ) phs ON phs.song_id = s.id
            WHERE s.is_missing = 0
            ORDER BY phs.history_plays DESC, phs.last_played_at DESC
            LIMIT $limit
        ";
        $stmt = getDBConnection()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    } catch (Throwable $e) {
        if (function_exists('writeAppLog')) writeAppLog('error', 'db_error', 'Top songs stats query failed', ['error' => $e->getMessage()]);
        return [];
    }
}

function getTopFieldByHistory($field, $limit = 10) {
    $allowed = ['artist', 'album', 'genre'];
    if (!in_array($field, $allowed, true)) return [];
    $limit = statsLimit($limit);
    try {
        $sql = "
            SELECT COALESCE(NULLIF(TRIM(s.$field), ''), '알 수 없음') AS name, COUNT(*) AS plays
            FROM play_history ph
            INNER JOIN songs s ON s.id = ph.song_id
            WHERE s.is_missing = 0
            GROUP BY COALESCE(NULLIF(TRIM(s.$field), ''), '알 수 없음')
            ORDER BY plays DESC, name ASC
            LIMIT $limit
        ";
        return getDBConnection()->query($sql)->fetchAll();
    } catch (Throwable $e) {
        if (function_exists('writeAppLog')) writeAppLog('error', 'db_error', 'Top field stats query failed', ['field' => $field, 'error' => $e->getMessage()]);
        return [];
    }
}

function listeningReportPeriodOptions() {
    return [
        'recent7' => ['label' => '최근 7일', 'where' => 'ph.played_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)'],
        'week' => ['label' => '이번 주', 'where' => 'YEARWEEK(ph.played_at, 1) = YEARWEEK(CURDATE(), 1)'],
        'month' => ['label' => '이번 달', 'where' => 'ph.played_at >= DATE_FORMAT(CURDATE(), "%Y-%m-01")'],
    ];
}

function normalizeListeningReportPeriod($period) {
    $period = (string)$period;
    return array_key_exists($period, listeningReportPeriodOptions()) ? $period : 'week';
}

function listeningReportWhere($period) {
    $options = listeningReportPeriodOptions();
    $period = normalizeListeningReportPeriod($period);
    return $options[$period]['where'];
}

function getTopFieldByHistoryForPeriod($field, $period = 'week', $limit = 5) {
    $allowed = ['artist', 'album', 'genre'];
    if (!in_array($field, $allowed, true)) return [];
    $limit = statsLimit($limit, 5);
    $where = listeningReportWhere($period);
    try {
        $sql = "
            SELECT COALESCE(NULLIF(TRIM(s.$field), ''), '미분류') AS name, COUNT(*) AS plays
            FROM play_history ph
            INNER JOIN songs s ON s.id = ph.song_id
            WHERE s.is_missing = 0
              AND $where
            GROUP BY COALESCE(NULLIF(TRIM(s.$field), ''), '미분류')
            ORDER BY plays DESC, name ASC
            LIMIT $limit
        ";
        return getDBConnection()->query($sql)->fetchAll();
    } catch (Throwable $e) {
        if (function_exists('writeAppLog')) writeAppLog('error', 'db_error', 'Period top field query failed', ['field' => $field, 'period' => $period, 'error' => $e->getMessage()]);
        return [];
    }
}

function getListeningReportSummary($period = 'week') {
    $period = normalizeListeningReportPeriod($period);
    $where = listeningReportWhere($period);
    $empty = [
        'period' => $period,
        'play_events' => 0,
        'unique_songs' => 0,
        'total_seconds' => 0,
        'top_song' => null,
        'top_artists' => [],
        'top_genres' => [],
    ];
    try {
        $pdo = getDBConnection();
        $summaryStmt = $pdo->query("
            SELECT COUNT(*) AS play_events,
                   COUNT(DISTINCT ph.song_id) AS unique_songs,
                   COALESCE(SUM(CASE WHEN COALESCE(s.duration, 0) > 0 THEN s.duration ELSE 0 END), 0) AS total_seconds
            FROM play_history ph
            INNER JOIN songs s ON s.id = ph.song_id
            WHERE s.is_missing = 0
              AND $where
        ");
        $summary = $summaryStmt->fetch() ?: [];
        $topSongs = getTopSongsByHistory(1, str_replace('ph.', '', $where));
        return [
            'period' => $period,
            'play_events' => (int)($summary['play_events'] ?? 0),
            'unique_songs' => (int)($summary['unique_songs'] ?? 0),
            'total_seconds' => (int)($summary['total_seconds'] ?? 0),
            'top_song' => $topSongs[0] ?? null,
            'top_artists' => getTopFieldByHistoryForPeriod('artist', $period, 5),
            'top_genres' => getTopFieldByHistoryForPeriod('genre', $period, 5),
        ];
    } catch (Throwable $e) {
        if (function_exists('writeAppLog')) writeAppLog('error', 'db_error', 'Listening report summary failed', ['period' => $period, 'error' => $e->getMessage()]);
        return $empty;
    }
}

function formatListeningSeconds($seconds) {
    $seconds = max(0, (int)$seconds);
    if ($seconds < 60) return '1분 미만';
    $minutes = (int)floor($seconds / 60);
    if ($minutes < 60) return number_format($minutes) . '분';
    $hours = (int)floor($minutes / 60);
    $rest = $minutes % 60;
    return $rest > 0 ? number_format($hours) . '시간 ' . $rest . '분' : number_format($hours) . '시간';
}

function getPatternStats($type = 'hour') {
    $expr = $type === 'weekday' ? 'WEEKDAY(played_at)' : 'HOUR(played_at)';
    try {
        $rows = getDBConnection()->query("
            SELECT $expr AS bucket, COUNT(*) AS plays
            FROM play_history
            GROUP BY $expr
            ORDER BY bucket ASC
        ")->fetchAll();
        $map = [];
        foreach ($rows as $row) $map[(int)$row['bucket']] = (int)$row['plays'];
        $labels = $type === 'weekday' ? ['월','화','수','목','금','토','일'] : range(0, 23);
        $result = [];
        foreach ($labels as $idx => $label) {
            $bucket = $type === 'weekday' ? $idx : (int)$label;
            $result[] = ['label' => $type === 'weekday' ? $label : sprintf('%02d시', $bucket), 'value' => $map[$bucket] ?? 0];
        }
        return $result;
    } catch (Throwable $e) {
        if (function_exists('writeAppLog')) writeAppLog('error', 'db_error', 'Pattern stats query failed', ['type' => $type, 'error' => $e->getMessage()]);
        return [];
    }
}

function getRecentlyFrequentSongs($limit = 8) {
    return getTopSongsByHistory($limit, 'played_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)');
}

function getLikedTopSongs($limit = 10) {
    $limit = statsLimit($limit);
    try {
        $sql = baseSongSelectSql() . "
            INNER JOIN liked_songs liked_filter ON liked_filter.song_id = s.id
            LEFT JOIN (
                SELECT song_id, COUNT(*) AS history_plays
                FROM play_history
                GROUP BY song_id
            ) ph ON ph.song_id = s.id
            WHERE s.is_missing = 0
            ORDER BY COALESCE(ph.history_plays, 0) DESC, liked_filter.created_at DESC
            LIMIT $limit
        ";
        return getDBConnection()->query($sql)->fetchAll();
    } catch (Throwable $e) {
        if (function_exists('writeAppLog')) writeAppLog('error', 'db_error', 'Liked top songs query failed', ['error' => $e->getMessage()]);
        return [];
    }
}

function getRecentAddedTopSongs($limit = 10) {
    $limit = statsLimit($limit);
    return fetchSongs('s.is_missing = 0 AND s.created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)', [], 's.play_count DESC, s.created_at DESC', $limit);
}

function getHomePersonalizedData($limit = 8) {
    $limit = statsLimit($limit, 8);
    $summary = getListeningSummary();
    return [
        'summary' => $summary,
        'resume' => getRecentlyPlayedSongs(1),
        'recent_frequent' => getRecentlyFrequentSongs($limit),
        'top_artists' => getTopFieldByHistory('artist', 6),
        'top_genres' => getTopFieldByHistory('genre', 6),
        'old_liked' => getOldLikedSongs($limit),
        'random' => fetchSongs('s.is_missing = 0', [], 'RAND()', $limit),
        'never_played' => getNeverPlayedSongs($limit),
    ];
}

function getAdminListeningStats() {
    $summary = getListeningSummary();
    $pdo = getDBConnection();
    try {
        $unplayedAfterScan = (int)$pdo->query("SELECT COUNT(*) FROM songs WHERE is_missing = 0 AND COALESCE(play_count, 0) = 0")->fetchColumn();
        $recentStreamErrors = $pdo->query("
            SELECT id, log_level, log_type, message, file_path, created_at FROM app_logs
            WHERE log_type = 'stream_error'
            ORDER BY id DESC
            LIMIT 10
        ")->fetchAll();
    } catch (Throwable $e) {
        $unplayedAfterScan = 0;
        $recentStreamErrors = [];
        if (function_exists('writeAppLog')) writeAppLog('error', 'db_error', 'Admin listening stats failed', ['error' => $e->getMessage()]);
    }
    return [
        'summary' => $summary,
        'top_songs' => getTopSongsByHistory(10),
        'recent_stream_errors' => $recentStreamErrors,
        'unplayed_after_scan' => $unplayedAfterScan,
    ];
}

function renderMiniBarChart($items, $labelKey = 'label', $valueKey = 'value') {
    $max = 0;
    foreach ($items as $item) $max = max($max, (int)($item[$valueKey] ?? 0));
    ?>
    <div class="mini-bar-chart">
        <?php foreach ($items as $item): ?>
            <?php $value = (int)($item[$valueKey] ?? 0); $pct = $max > 0 ? max(4, round(($value / $max) * 100)) : 0; ?>
            <div class="mini-bar-row">
                <span class="mini-bar-label"><?php echo h($item[$labelKey] ?? '-'); ?></span>
                <div class="mini-bar-track"><div class="mini-bar-fill" style="width: <?php echo h((string)$pct); ?>%"></div></div>
                <strong><?php echo number_format($value); ?></strong>
            </div>
        <?php endforeach; ?>
    </div>
    <?php
}
