<?php
include_once('./_common.php');
 
// 날짜 간격 설정 (오늘과 비교할 과거 날짜, -1은 하루 전)
$days_ago = -1;
 
// 오늘과 비교 날짜 계산
$today = date('Y-m-d');
$compare_date = date('Y-m-d', strtotime($days_ago.' days'));
 
// 출력 인원수
$limit = 10;
 
// 제외할 아이디
$exclude_ids_array = ['webmaster', 'test3'];
$exclude_ids = implode("','", $exclude_ids_array);
 
// 오늘의 총 포인트와 오늘 획득 포인트를 가져오는 쿼리
$sql_today = "
    SELECT
        m.mb_id,
        m.mb_nick,
        m.mb_point as total_points,
        IFNULL(SUM(CASE WHEN DATE(p.po_datetime) = '{$today}' THEN p.po_point ELSE 0 END), 0) as today_points
    FROM
        {$g5['member_table']} m
    LEFT JOIN
        {$g5['point_table']} p ON m.mb_id = p.mb_id
    WHERE
        m.mb_id NOT IN ('{$exclude_ids}')
    GROUP BY
        m.mb_id
    ORDER BY
        m.mb_point DESC
    LIMIT {$limit}
";
$today_result = sql_query($sql_today);
 
// 어제의 총 포인트를 가져오는 쿼리
$sql_compare = "
    SELECT
        m.mb_id,
        m.mb_point - IFNULL(SUM(CASE WHEN DATE(p.po_datetime) = '{$today}' THEN p.po_point ELSE 0 END), 0) as yesterday_points
    FROM
        {$g5['member_table']} m
    LEFT JOIN
        {$g5['point_table']} p ON m.mb_id = p.mb_id
    WHERE
        m.mb_id NOT IN ('{$exclude_ids}')
    GROUP BY
        m.mb_id
    ORDER BY
        yesterday_points DESC
";
$compare_result = sql_query($sql_compare);
 
// 비교 날짜의 랭킹과 포인트를 계산
$compare_ranking = [];
$compare_points = [];
$rank = 1;
while ($row = sql_fetch_array($compare_result)) {
    $compare_ranking[$row['mb_id']] = $rank++;
    $compare_points[$row['mb_id']] = (float)$row['yesterday_points'];
}
 
// 순위 변동 계산 함수
function get_rank_change($mb_id, $current_rank, $compare_ranking) {
    if (isset($compare_ranking[$mb_id])) {
        $compare_rank = $compare_ranking[$mb_id];
        $change = $compare_rank - $current_rank;
        if ($change > 0) {
            return "<span style='color:green'>▲ {$change}</span>";
        } elseif ($change < 0) {
            return "<span style='color:red'>▼ " . abs($change) . "</span>";
        } else {
            return "-";
        }
    } else {
        return "<span style='color:blue'>New</span>";
    }
}
?>
<style>
    .ranking-container {
        max-width: 800px;
        margin: 0 auto;
        padding: 20px 0;
    }
    .ranking-title {
        background: #6f809a;
        color: #fff;
        text-align: center;
        padding: 12px;
        font-size: 1em;
        margin: 0 0 20px;
        border-radius: 4px;
    }
    .ranking-table {
        background: #fff;
        border-radius: 8px;
        box-shadow: 0 2px 8px rgba(0,0,0,0.1);
        overflow: hidden;
    }
    .ranking-table ul {
        display: flex;
        align-items: center;
        padding: 15px 10px;
        border-bottom: 1px solid #eee;
        margin: 0;
    }
    .ranking-table ul:first-child {
        background: #f8f9fa;
        font-weight: 600;
        border-bottom: 2px solid #e9ecef;
    }
    .ranking-table ul:last-child {
        border-bottom: none;
    }
    .ranking-table ul:hover:not(:first-child) {
        background: #f8f9fa;
    }
    .ranking-table li {
        flex: 1;
        text-align: center;
        white-space: nowrap;
        overflow: hidden;
        text-overflow: ellipsis;
        padding: 0 5px;
    }
    .ranking-table li:first-child {
        flex: 0 0 8%;
    }
    .ranking-table li:last-child {
        flex: 0 0 10%;
    }
    .point-change-positive {
        color: #28a745;
    }
    .point-change-negative {
        color: #dc3545;
    }
</style>
 
<div class="ranking-container">
    <h2 class="ranking-title">포인트 랭킹</h2>
    <div class="ranking-table">
        <ul>
            <li>순위</li>
            <li>닉네임</li>
            <li>어제 포인트</li>
            <li>오늘 포인트</li>
            <li>변동 포인트</li>
            <li>총 포인트</li>
            <li>순위변동</li>
        </ul>
        <?php
        $rank = 1;
        while ($row = sql_fetch_array($today_result)) {
            $mb_id = $row['mb_id'];
            $mb_nick = $row['mb_nick'];
            $total_points = (float)$row['total_points'];
            $today_points = (float)$row['today_points'];
            $yesterday_points = isset($compare_points[$mb_id]) ? $compare_points[$mb_id] : 0;
            $point_change = $today_points;
            $rank_change = get_rank_change($mb_id, $rank, $compare_ranking);
           
            $point_change_class = $point_change >= 0 ? 'point-change-positive' : 'point-change-negative';
            $point_change_symbol = $point_change >= 0 ? '+' : '';
 
            echo "<ul>";
            echo "<li>{$rank}</li>";
            echo "<li>{$mb_nick}</li>";
            echo "<li>" . number_format($yesterday_points) . "</li>";
            echo "<li>" . number_format($total_points) . "</li>";
            echo "<li class='{$point_change_class}'>{$point_change_symbol}" . number_format($point_change) . "</li>";
            echo "<li>" . number_format($total_points) . "</li>";
            echo "<li>{$rank_change}</li>";
            echo "</ul>";
           
            $rank++;
        }
        ?>
    </div>
</div>