/*
alter table `char` add column custom_mvp_rank int(11) default 0 after hotkey_rowshift, add index (custom_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,176,3  script  MVP Ladder  1_F_MARIA,{
    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, custom_mvp_rank from `char` where custom_mvp_rank > 0 order by custom_mvp_rank desc limit 5", .@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 custom_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(MONTH) > 10 )
            .@store_date$ = gettime(YEAR) +"-"+( gettime(MONTH) -1 )+"-00 00:00:00";
        else if ( gettime(MONTH) > 1 )
            .@store_date$ = gettime(YEAR) +"-0"+( gettime(MONTH) -1 )+"-00 00:00:00";
        else
            .@store_date$ = ( gettime(YEAR) -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(YEAR) +""+ gettime(MONTH) ) ) end;
    goto L_give;
OnClock0000:
    if ( gettime(DAYOFMONTH) != 1 ) end;
L_give:
    .@nb = query_sql( "select char_id, name, custom_mvp_rank from `char` where custom_mvp_rank > 0 order by custom_mvp_rank desc limit 5", .@cid, .@name$, .@killed );
    if ( !.@nb ) end;
    setarray .@reward, 30316, 7227, 7227, 7227, 7227; // <1st place>, <2nd place>, <3rd place> ...
    setarray .@amount, 1, 50, 25, 10, 5;
    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 killing Custom MVPs. Here is your reward.', "+ .@reward[.@i] +", "+ .@amount[.@i] +", 1, 0, unix_timestamp( now() ) )";
    query_sql "update `char` set custom_mvp_rank = 0"; // reset the ladder
    $mvp_ladder_last_given = atoi( gettime(YEAR) +""+ gettime(MONTH) );
    end;
OnNPCKillEvent:
    if ( killedrid == PORING ) // 1002
        query_sql "update `char` set custom_mvp_rank = custom_mvp_rank +1 where char_id = "+ getcharid(0);
    end;
}