// base on victor's vip script

/*
CREATE TABLE IF NOT EXISTS `vip_bronze` (
  `account_id` int(11) unsigned NOT NULL default '0',
  `account_name` varchar(255) NOT NULL default 'NULL',
  `start_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `end_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `status` varchar(255) NOT NULL DEFAULT 'NULL',
  PRIMARY KEY  (`account_id`)
) ENGINE=MyISAM;

CREATE TABLE IF NOT EXISTS `vip_silver` (
  `account_id` int(11) unsigned NOT NULL default '0',
  `account_name` varchar(255) NOT NULL default 'NULL',
  `start_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `end_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `status` varchar(255) NOT NULL DEFAULT 'NULL',
  PRIMARY KEY  (`account_id`)
) ENGINE=MyISAM;

CREATE TABLE IF NOT EXISTS `vip_gold` (
  `account_id` int(11) unsigned NOT NULL default '0',
  `account_name` varchar(255) NOT NULL default 'NULL',
  `start_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `end_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `status` varchar(255) NOT NULL DEFAULT 'NULL',
  PRIMARY KEY  (`account_id`)
) ENGINE=MyISAM;

ALTER table `login` add column account_type varchar(255) NOT NULL default 'NULL' after `birthdate`, add index (account_type);

*/

prontera,150,150,5	script	VIP system	56,{
function statusvip;
function expirevip;
function mainmenu;
function showlist;
function deletevip;
function addvip;

	mes .npc_Name$;
	query_sql "SELECT `account_type` FROM login WHERE `account_id` = "+ getcharid(3), .@acc_Type$;
	while( .@i < 3 && strtolower( .@acc_Type$ ) != .type_name$[.@i] ) set .@i, .@i +1;
	if( .@i == 3 )
		mes "Hello "+ strcharinfo(0) +" you have an normal account. Get an "+ getitemname( .item_premium ) +" to get a premium account.";
	else {
		set .@expiration_Date$, statusvip( .type_name$[.@i], .@i +1, 1 );
		mes "Hello "+ strcharinfo(0) +" you have an "+ .type_name$[.@i] +" and it will expire in "+ .@expiration_Date$;
	}
	goto L_player;


OnInit:
// Setting
// -------
	set .npc_Name$,"[VIP System v3.0]";

	set .bronze_AccLvl, 0;		// VIP bronze Account Level
	set .silver_AccLvl, 0;		// 	   silver
	set .gold_AccLvl, 1;		// 	   gold

	set .bronze_ExpRate, 25;	// VIP bronze Exp Boost in % ( 1 = 1% )
	set .silver_ExpRate, 50;	// 	   silver
	set .gold_ExpRate, 100;	//	   gold

	set .bronze_ItemRate, 0;	// VIP bronze Item Drop Boost in % ( 1 = 1% )
	set .silver_ItemRate, 5;	//	 silver
	set .gold_ItemRate, 10;	//	 gold

	set .item_premium, 501; // ID item to upgrade your account to bronze/silver/gold
	set .fcp, 0;			// fcp for gold vip ? (0 : no) / (1 : yes)
	setarray .type_name$, "bronze", "silver", "gold" ;
	end;

// On login
// --------
OnPCLoginEvent:
	query_sql "SELECT `account_type` FROM login WHERE `account_id` = "+ getcharid(3), .@acc_Type$;
	while( .@i < 3 && strtolower( .@acc_Type$ ) != .type_name$[.@i] ) set .@i, .@i +1;
	if( .@i == 3 ) end;
	sc_end sc_expboost;	
	sc_end sc_itemboost;
	set .@expiration_Date$, statusvip( .type_name$[.@i], .@i, 0 );
	dispbottom "----------------------------------------------------------------------";
	dispbottom "Account type:		 "+ .@acc_Type$;
	dispbottom "Expiration date: "+ .@expiration_Date$;
	dispbottom "----------------------------------------------------------------------";
	end;

// Check the VIP status ; bonus VIP
// --------------------------------
function statusvip {
	query_sql "SELECT `status` FROM `vip_"+ getarg(0) +"` WHERE `account_id` = "+ getcharid(3), .@vip_Status$ ;
	query_sql "SELECT DATE_FORMAT(`end_date`, '%Y %m %d') FROM `vip_"+ getarg(0) +"` WHERE `account_id` = "+ getcharid(3), .@expiration_Date$ ;
	if( getarg(2) ) return .@expiration_Date$;
	if ( gettimestr("%Y %m %d",21) >= .@expiration_Date$ )
		expirevip( "vip_"+ getarg(0), getcharid(3) );
	else {
		set .@time, 3600 * 60 * 1000;
		set .@it_Rate, getd( "."+ getarg(0) +"_ItemRate" );
		set .@exp_rate, getd( "."+ getarg(0) +"_ExpRate" );
		if( .@exp_rate )
			sc_start sc_expboost, .@time, .@exp_rate;
		if( .@it_Rate )
			sc_start sc_itemboost, .@time, .@it_Rate;
		if( getarg(1) == 2 && .fcp ) {
			sc_start SC_CP_WEAPON, .@time, 5;
			sc_start SC_CP_SHIELD, .@time, 5;
			sc_start SC_CP_ARMOR, .@time, 5;
			sc_start SC_CP_HELM, .@time, 5;
		}
	}
	return .@expiration_Date$;
}

// VIP expire : updates tables
// ---------------------------
function expirevip {
	set .@T_Name$, getarg(0);
	set .@acc_Id, getarg(1);
	query_sql "UPDATE `login` SET `level` = 0, `account_type` = 'NORMAL' WHERE account_id = "+ .@acc_Id;
	query_sql "DELETE FROM "+ .@T_Name$ +" WHERE account_id = "+ .@acc_Id;
	query_sql "ALTER TABLE "+ .@T_Name$ +" AUTO_INCREMENT = 1";
	sc_end SC_CP_WEAPON;
	sc_end SC_CP_SHIELD;
	sc_end SC_CP_ARMOR;
	sc_end SC_CP_HELM;
	dispbottom "Your VIP expire.";
	end;
}

// GM menu
// -------
OnWhisperGlobal:
	if ( getgmlevel() < 99 ) end;
L_main:
	mes .npc_Name$;
	mes "Welcome, "+ strcharinfo(0) +"!";
	mes "What can I do for you today?";
L_player:
	next;
	set .@s, select( ( getgmlevel() < 99 ? "" : "VIP bronze Menu" ), ( getgmlevel() < 99 ? "" : "VIP silver Menu" ), ( getgmlevel() < 99 ? "" : "VIP gold Menu" ), ( countitem( .item_premium ) ? "Upgrade my account" : "" ), "Quit" ) -1;
	if ( .@s == 4 ) close;
	else if( .@s == 3 )
		callfunc( "VIP_upgrade", 7, .item_premium );
	set .@a$, "[VIP "+ strtoupper( .type_name$[.@s] ) +"]";

	next;
	mes .npc_Name$;
	mes .@a$;
	// if( getgmlevel() < 99 ) {
		// set .@sub_s, select( "List Accounts", "Cancel" );
		// if( .@sub_s == 2 ) close;
		// mainmenu( .@sub_s +2, .@s );
	// }
	next;
	set .@sub_s, select( "Add/Extend Account", "Delete Account", "List Accounts", "Back" );
	if ( .@sub_s == 4 || mainmenu( .@sub_s, .@s, .@a$ ) ) goto L_main;


function mainmenu {
	switch( getarg(0) ) {
		case 1: addvip( getarg(1), getarg(2) ); break;
		case 2: deletevip( getarg(1), getarg(2) ); break;
		case 3: showlist( getarg(1) );
	}
	return 1;
}

function addvip {
	set .@s, getarg(0);
	set .@a$, getarg(1);

	mes .npc_Name$;
	mes .@a$;
	mes "Type the Account Name";
	mes "you wish to add.";
	input .@acc_Name$;
	next;

	mes .npc_Name$;
	mes .@a$;
	query_sql "SELECT `account_id` FROM login WHERE `userid` = '"+ escape_sql( .@acc_Name$ )+"'", .@acc_Id ;
	mes "Identifying account: "+ .@acc_Name$ +".";
	sleep2 500;
	if ( !.@acc_Id ) {
		mes "ERROR: Couldn't find account.";
		mes "Verify if you dind't make a typo.";
		close;
	}

	mes .npc_Name$;
	mes .@a$;
	mes "Type the number of days you";
	mes "wish to add to the account's VIP";
	mes "status.";
	next;

	while( input( .@Days, 1, 999 ) != 0 ) {
		mes .npc_Name$;
		mes .@a$;
		mes "Put a number between 1 and 999.";
		next;
	}
	mes .npc_Name$;
	mes .@a$;
	query_sql "SELECT `account_id` FROM vip_"+ .type_name$[.@s] +" WHERE `account_id` = "+ .@acc_Id, .@vip_accid;
	if ( .@vip_accid == .@acc_Id ) {
		mes "Account ID: "+ .@vip_accid +".";
		sleep2 500;
		query_sql "UPDATE vip_"+ .type_name$[.@s] +" SET end_date = DATE_ADD(end_date, INTERVAL "+ .@Days +" DAY), `status` = 'ACTIVE' WHERE account_id = "+.@vip_accid;
		query_sql "UPDATE login SET `account_type` = '"+ strtoupper( .type_name$[.@s] ) +"' WHERE account_id = "+ .@vip_accid;
		mes "Account "+ .@acc_Id +" added successfuly!";
	}
	else {
		mes "Account ID: "+ .@acc_Id +".";
		sleep2 500;
		query_sql "INSERT INTO vip_"+ .type_name$[.@s] +" (`account_id`,`account_name`,`start_date`,`end_date`,`status`) VALUES ("+.@acc_Id+",'"+ escape_sql( .@acc_Name$ ) +"','"+gettimestr("%Y-%m-%d",21)+"','"+gettimestr("%Y-%m-%d",21)+"','ACTIVE')";
		sleep2 100;
		query_sql "UPDATE vip_"+ .type_name$[.@s] +" SET end_date = DATE_ADD(start_date, INTERVAL "+ .@Days +" DAY) WHERE account_id = "+.@acc_Id;
		query_sql "UPDATE `login` SET `level` = "+ getd( "."+ .type_name$[.@s] +"_AccLvl" ) +", `account_type` = '"+ strtoupper( .type_name$[.@s] ) +"' WHERE `account_id` = "+.@acc_Id;
		mes "Account "+ .@acc_Id +" added successfuly!";
	}
	close;
}

function deletevip {
	set .@s, getarg(0);
	set .@a$, getarg(1);
	set .@size, query_sql( "SELECT account_name FROM vip_"+ .type_name$[.@s] +" LIMIT 127", .@List$ );
		mes .npc_Name$;
		mes .@a$;
	if ( .@size == 0 )
		mes "There are currently no VIP "+ .type_name$[.@s] +" accounts.";
	else {
		mes "Select the account you wish ";
		mes "to remove from VIP system.";
		
		set .@j$, "1. "+ .@List$;
		for ( set .@i, 1; .@i < .@size; set .@i, .@i + 1 )
			set .@j$, .@j$ +":"+ ( .@i +1 ) +". "+ .@List$[.@i];
		set .@j$, .@j$ +":Cancel";
		next;

		mes .npc_Name$;
		mes .@a$;
		set .@menu_del, select( .@j$ ) -1;
		if ( .@menu_del == .@size ) {
			next;
			return;
		}
		mes "Are you sure you want to remove the account ";
		mes .@List$[ .@menu_del ] +" ?";
		if ( select( "Yes", "No" ) -1 ) {
			next;
			return;
		}
		query_sql "UPDATE `login` SET `level` = 0, `account_type` = 'NORMAL' WHERE `userid` = '"+ escape_sql( .@List$[ .@menu_del ] ) +"'";
		query_sql "DELETE FROM vip_"+ .type_name$[.@s] +" WHERE account_name = "+ escape_sql( .@List$[ .@menu_del ] );
		query_sql "ALTER TABLE vip_"+ .type_name$[.@s] +" AUTO_INCREMENT = 1";
		mes "VIP "+ .type_name$[.@s] +" account successfuly deleted!.";
	}
	close;
}

function showlist {
	set .@s, getarg(0);
	mes .npc_Name$;
	mes "VIP "+ .type_name$[.@s] +" accounts list:";
	set .@size, query_sql( "SELECT account_name FROM vip_"+ .type_name$[.@s] +" LIMIT 127", .@List$ );
	if( .@size == 0 ) {
		mes "The list is empty.";
		close;
	}
	for ( set .@i, 0; .@i < .@size; set .@i, .@i + 1 )
		mes ( .@i +1 )+". "+ .@List$[ .@i ];
	close;
}
}

// Upgrade via itemscript
// ----------------------
function	script	VIP_upgrade	{
	if( select( "^777777~ Upgrade my account", "~ Not today^000000" ) -1 ) close;
	set .@acc_id, getcharid(3) ;
	setarray .@type_name$, "bronze", "silver", "gold" ;
	set .@bronze_AccLvl, 0;		// VIP bronze Account Level
	set .@silver_AccLvl, 0;		// 	   silver
	set .@gold_AccLvl, 1;		// 	   gold

	query_sql "SELECT `userid`, `account_type` FROM `login` WHERE `account_id` = '"+ .@acc_id +"'", .@user_id$, .@acc_type$;
	while( .@i < 3 && strtolower( .@acc_Type$ ) != .@type_name$[.@i] ) set .@i, .@i +1;

	if ( .@i == 2 ) {
		dispbottom "Your can't upgrade your account.";
		close;
	}
	else if( .@i == 3 )
		set .@i, -1;
	else {
		query_sql "DELETE FROM vip_"+ .@type_name$[.@i] +" WHERE account_name = '"+ .@user_id$ +"'";
		query_sql "ALTER TABLE vip_"+ .@type_name$[.@i] +" AUTO_INCREMENT = 1";
	}
	query_sql "INSERT INTO vip_"+ .@type_name$[.@i +1] +" (`account_id`,`account_name`,`start_date`,`end_date`,`status`) VALUES ("+ .@acc_id +",'"+ escape_sql( .@user_id$ ) +"','"+gettimestr("%Y-%m-%d",21)+"', ( DATE_ADD('"+ gettimestr("%Y-%m-%d",21) +"', INTERVAL "+ getarg(0) +" DAY) ), 'ACTIVE') ";
	query_sql "UPDATE `login` SET `level` = "+ getd( "."+ .@type_name$[.@i +1] +"_AccLvl" ) +" WHERE `account_id` = '"+ .@acc_id +"'";
	query_sql "SELECT DATE_FORMAT(`end_date`, '%Y %M %d') FROM vip_"+ .@type_name$[.@i +1] +" WHERE `account_id` = '"+ .@acc_id +"'", .@exp_Date$;
	query_sql "UPDATE login SET `account_type` = '"+ strtoupper( .@type_name$[.@i +1] ) +"' WHERE account_id = '"+ .@acc_id +"'";
	message strcharinfo(0), "Account updated. Expiration : "+ .@exp_Date$;
	delitem getarg(1), 1;
	sleep2 1000;
	message strcharinfo(0), "You must relog.";
	sleep2 2000;
	if( playerattached() )
		atcommand "@kick "+ strcharinfo(0);
	close;
}

// 12703,Holy_Egg_2,Holy Egg,11,0,,10,,,,,0xFFFFFFFF,7,2,,,,,,{ callfunc( "VIP_upgrade", 7, 12703 ); /* 7 days, item ID to delete */ },{},{}