/*
alter table `char` add column mvp_rank int(11) default 0 after delete_date, add index (mvp_rank);
 
create table mvp_rank_archive (
`date` datetime,
pos tinyint(4) unsigned,
cid int(11) unsigned default 0,
name varchar(23) default '',
points int(11) unsigned not null
primary key ( `date`, pos )
) engine innodb;
*/
prontera,158,183,3  script  MVP Ladder  100,{
    mes "[MVP Ladder]";
    mes "Hello!";
    mes "What are you doing here?";
    next;
    switch(select("Check rankings.:My stats.:Previous Month ranking")) {
    case 1:
        mes "[MVP Ladder]";
        mes "Rankings :->";
        .@nb = query_sql( "select name, mvp_rank from `char` where mvp_rank > 0 order by mvp_rank desc limit 10", .@name$, .@count );
        if ( !.@nb ) {
            mes "no entry";
            close;
        }
        for ( .@i = 0; .@i < .@nb; .@i++ )
            mes ( .@i +1 )+". "+ .@name$[.@i] +" -> "+ .@count[.@i] +" points";
        close;
    case 2:
        query_sql "select mvp_rank from `char` where char_id = "+ getcharid(0), .@killed;
        mes "[MVP Ladder]";
        mes "You have killed "+ .@killed +" MVPs";
        close;
    case 3:
        mes "[MVP Ladder]";
        mes "Last month ranking";
        if ( gettime(6) > 10 )
            .@store_date$ = gettime(7) +"-"+( gettime(6) -1 )+"-00 00:00:00";
        else if ( gettime(6) > 1 )
            .@store_date$ = gettime(7) +"-0"+( gettime(6) -1 )+"-00 00:00:00";
        else
            .@store_date$ = ( gettime(7) -1 ) +"-12-00 00:00:00";
        .@nb = query_sql( "select name, points from mvp_rank_archive where `date` = '"+ .@store_date$ +"' order by pos", .@name$, .@count );
        if ( !.@nb ) {
            mes "no entry";
            close;
        }
        for ( .@i = 0; .@i < .@nb; .@i++ )
            mes ( .@i +1 )+". "+ .@name$[.@i] +" -> "+ .@count[.@i] +" points";
        close;
    }
    close;
OnInit:
    if ( $mvp_ladder_last_given == atoi( gettime(7) +""+ gettime(6) ) ) end;
    goto L_give;
OnClock0000:
    if ( gettime(5) != 1 ) end;
L_give:
    .@nb = query_sql( "select char_id, name, mvp_rank from `char` where mvp_rank > 0 order by mvp_rank desc limit 10", .@cid, .@name$, .@killed );
    if ( !.@nb ) end;
    setarray .@reward, 501, 502, 503, 504, 505, 506, 507, 508, 509, 510;
    setarray .@amount, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1;
    for ( .@i = 0; .@i < .@nb; .@i++ )
        query_sql "insert into mail ( send_name, dest_id, title, message, nameid, amount, identify, zeny, time ) values ( '"+ escape_sql( .@name$[.@i] ) +"', "+ .@cid[.@i] +", '[MVP RANK]', 'Congratulations for earning No. "+( .@i +1 )+" position in MVP rank. Here is your reward.', "+ .@reward[.@i] +", "+ .@amount[.@i] +", 1, 0, unix_timestamp( now() ) )";
    if ( gettime(6) > 10 )
        .@store_date$ = gettime(7) +"-"+( gettime(6) -1 )+"-00 00:00:00";
    else if ( gettime(6) > 1 )
        .@store_date$ = gettime(7) +"-0"+( gettime(6) -1 )+"-00 00:00:00";
    else
        .@store_date$ = ( gettime(7) -1 ) +"-12-00 00:00:00";
    for ( .@i = 0; .@i < .@nb; .@i++ )
        .@store$[.@i] = "( '"+ .@store_date$ +"', "+( .@i +1 )+", "+ .@cid[.@i] +", '"+ escape_sql( .@name$[.@i] ) +"', "+ .@killed[.@i] +" )";
    query_sql "insert into mvp_rank_archive values "+ implode( .@store$, "," );
    query_sql "update `char` set mvp_rank = 0"; // reset the ladder
    $mvp_ladder_last_given = atoi( gettime(7) +""+ gettime(6) );
    end;
OnNPCKillEvent:
    if ( getmonsterinfo( killedrid, MOB_MVPEXP ) ) {
        query_sql "update `char` set mvp_rank = mvp_rank +1 where char_id = "+ getcharid(0);
        query_sql "select mvp_rank from `char` where char_id = "+ getcharid(0), .@killed;
        dispbottom "~ You've killed "+ .@killed +" MVP. ~";
        specialeffect2 EF_HEAL2;
    }
    end;
}