<?php

// ---------------------------------------------
// 설정
// ---------------------------------------------
$exclude_ids = ['master','false9']; //제외아이디

// ---------------------------------------------
// 유틸
// ---------------------------------------------
// exclude IN 절
function build_exclude_in($ids){
    $safe = array_map('sql_escape_string', $ids);
    return "'" . implode("','", $safe) . "'";
}

// 닉네임 가져오기
function get_nick_by_id($mb_id){
    $mb_id = sql_escape_string($mb_id);
    $row = sql_fetch("select mb_nick from g5_member where mb_id='{$mb_id}'");
    return ($row && $row['mb_nick']) ? $row['mb_nick'] : $mb_id;
}

// 상위 N 추출: 연관배열 (mb_id => cnt) 를 cnt 내림차순 정렬 후 상위 N 반환
function topNFromAssoc($assoc, $n=5){ // 기본 5위
    arsort($assoc, SORT_NUMERIC);
    return array_slice($assoc, 0, $n, true);
}

// 출력용 공통 렌더러
function render_top_list($title, $rows){ // rows: [['nick'=>..., 'count'=>...], ...]
    echo '<div style="margin:8px 0 16px">';
    echo '<strong>' . htmlspecialchars($title, ENT_QUOTES) . '</strong><br>';
    if (empty($rows)) {
        echo '<span>데이터 없음</span>';
    } else {
        $rank = 1;
        foreach($rows as $r){
            $nick = htmlspecialchars($r['nick'], ENT_QUOTES);
            $cnt  = number_format((int)$r['count']);
            echo $rank . '위: <strong>' . $nick . '</strong> <span style="opacity:.8">(' . $cnt . ')</span><br>';
            $rank++;
        }
    }
    echo '</div>';
}

// ---------------------------------------------
// 1) 포인트 상위 1~5위 (g5_member.mb_point)
// ---------------------------------------------
$exclude_in = build_exclude_in($exclude_ids);

$res_point = sql_query("
    select mb_id, mb_nick, mb_point
    from g5_member
    where mb_id not in ({$exclude_in})
    order by mb_point desc, mb_id asc
    limit 5
");

$tops_point = [];
for($i=0; $row = sql_fetch_array($res_point); $i++){
    $tops_point[] = [
        'nick'  => $row['mb_nick'],
        'count' => (int)$row['mb_point'],
    ];
}

// ---------------------------------------------
// 2) 출석 상위 1~5위 (g5_point.po_content LIKE '%첫로그인%')
//    공백 변형 '첫 로그인' 등도 잡도록 REPLACE 사용
// ---------------------------------------------
$res_att = sql_query("
    select x.mb_id, m.mb_nick, x.cnt
    from (
        select mb_id, count(*) as cnt
        from g5_point
        where REPLACE(REPLACE(po_content,' ',''), CHAR(160), '') like '%첫로그인%'
          and mb_id not in ({$exclude_in})
        group by mb_id
        order by cnt desc, mb_id asc
        limit 5
    ) x
    join g5_member m on m.mb_id = x.mb_id
    order by x.cnt desc, x.mb_id asc
");

$tops_att = [];
for($i=0; $row = sql_fetch_array($res_att); $i++){
    $tops_att[] = [
        'nick'  => $row['mb_nick'],
        'count' => (int)$row['cnt'],
    ];
}

// ---------------------------------------------
// 3) 전체 게시글/댓글/추천 합산 (모든 write_* 테이블)
// ---------------------------------------------
$postCnt = [];   // wr_is_comment = 0
$commCnt = [];   // wr_is_comment = 1
$goodCnt = [];   // wr_is_comment = 0 글들의 wr_good 합계 (작성자별)

$prefix = sql_escape_string($g5['write_prefix']); // 보통 'g5_write_'
$qtbl = sql_query("
    select table_name
    from information_schema.tables
    where table_schema = database()
      and table_name like '{$prefix}%'
");

while($t = sql_fetch_array($qtbl)){
    $tbl = $t['table_name'];

    $res1 = sql_query("
        select mb_id,
               count(*) as cnt,
               sum(coalesce(wr_good,0)) as good_sum
        from {$tbl}
        where wr_is_comment = 0
          and mb_id not in ({$exclude_in})
        group by mb_id
    ");
    while($r = sql_fetch_array($res1)){
        $mb = $r['mb_id'];
        $postCnt[$mb] = ($postCnt[$mb] ?? 0) + (int)$r['cnt'];
        $goodCnt[$mb] = ($goodCnt[$mb] ?? 0) + (int)$r['good_sum'];
    }

    // 댓글
    $res2 = sql_query("
        select mb_id, count(*) as cnt
        from {$tbl}
        where wr_is_comment = 1
          and mb_id not in ({$exclude_in})
        group by mb_id
    ");
    while($r = sql_fetch_array($res2)){
        $mb = $r['mb_id'];
        $commCnt[$mb] = ($commCnt[$mb] ?? 0) + (int)$r['cnt'];
    }
}

// 상위 5 추출
$top_posts = [];
foreach(topNFromAssoc($postCnt, 5) as $mb_id => $cnt){
    $top_posts[] = ['nick'=> get_nick_by_id($mb_id), 'count'=> $cnt];
}

$top_comms = [];
foreach(topNFromAssoc($commCnt, 5) as $mb_id => $cnt){
    $top_comms[] = ['nick'=> get_nick_by_id($mb_id), 'count'=> $cnt];
}

$top_goods = [];
foreach(topNFromAssoc($goodCnt, 5) as $mb_id => $cnt){
    $top_goods[] = ['nick'=> get_nick_by_id($mb_id), 'count'=> $cnt];
}

// ---------------------------------------------
// 출력
// ---------------------------------------------
echo '<div style="line-height:1.9">';
render_top_list('포인트 상위', $tops_point);
render_top_list('추천 상위 (누적 wr_good)', $top_goods);
render_top_list('출석 상위 (첫로그인 카운트)', $tops_att);
render_top_list('게시글 상위', $top_posts);
render_top_list('댓글 상위', $top_comms);
echo '</div>';

?>