// https://rathena.org/board/topic/107660-r-sql-mall/

-	script	all_in_one_shop_main	-1,{
	
	// Shop NPC Name
	function	func_ShopTypeName	{
		switch (getarg(0, -1)) {
			case IT_HEALING: return "HEALING";
			case IT_USABLE: return "USABLE";
			case IT_ETC: return "ETC";
			case IT_WEAPON: return "WEAPON";
			case IT_ARMOR: return "ARMOR";
			case IT_CARD: return "CARD";
			case IT_PETEGG: return "PETEGG";
			case IT_PETARMOR: return "PETARMOR";
			case IT_AMMO: return "AMMO";
			case IT_DELAYCONSUME: return "DELAYCONSUME";
			case IT_CASH: return "CASH";
			default: return "MISC";
		}
	}
	
	// Shop NPC Sprite ID
	function	func_ShopClassID	{
		switch (getarg(0, -1)) {
			case IT_HEALING: return 4_F_KAFRA1;
			case IT_USABLE: return 4_F_KAFRA2;
			case IT_ETC: return 4_F_KAFRA3;
			case IT_WEAPON: return 4_F_KAFRA4;
			case IT_ARMOR: return 4_F_KAFRA5;
			case IT_CARD: return 4_F_KAFRA6;
			case IT_PETEGG: return 4_F_KAFRA7;
			case IT_PETARMOR: return 4_F_KAFRA8;
			case IT_AMMO: return 4_F_KAFRA9;
			case IT_DELAYCONSUME: return 4_F_KAFRA2;
			case IT_CASH: return 4_F_KAFRA1;
			default: return rand(4_F_KAFRA1, 4_F_KAFRA9);
		}
	}
	
	OnInit:
		.is_renewal_db = 1;
		.max_shop_item = 100;
		.blacklist_item$ = "5001,5002,5003";
		
		sleep 1000; // delays
		callsub(OnLoad, IT_HEALING);
		callsub(OnLoad, IT_USABLE);
		callsub(OnLoad, IT_ETC);
		callsub(OnLoad, IT_WEAPON);
		callsub(OnLoad, IT_ARMOR);
		callsub(OnLoad, IT_CARD);
		callsub(OnLoad, IT_PETEGG);
		callsub(OnLoad, IT_PETARMOR);
		callsub(OnLoad, IT_AMMO);
		callsub(OnLoad, IT_DELAYCONSUME);
		callsub(OnLoad, IT_CASH);
		end;

	OnLoad:
		.@type = getarg(0, -1);
		.@npc = 1;
		
		.@sql$ = "SELECT `id`, `weight`, `" + (.is_renewal_db ? "atk:matk" : "attack") + "`, `slots`, `weapon_level`, `defence`, `equip_level`, `script`, `equip_script`, `unequip_script` "
			+ "FROM `" + (.is_renewal_db ? "item_db_re" : "item_db") + "` "
			+ "WHERE `type` = "+.@type;
		if (.blacklist_item$ != "")
			.@sql$ = .@sql$ + " AND `id` NOT IN ("+escape_sql(.blacklist_item$)+")";
		.@sql$ = .@sql$ + " ORDER BY `name_english`";
		
		.@size = query_sql(.@sql$, .@id, .@weight, .@atk, .@slot, .@weapon_level, .@defence, .@equip_level, .@script$, .@equip_script$, .@unequip_script$);
		
		if (.@size) {
			.@npc_count = (.@size / .max_shop_item);
		
			freeloop(1);
			do {
				.@x = 0;
				
				.@npc_shop_name$ = "Shop#t_"+.@type+"_"+.@npc;
				if (getmapxy(.@map$, .@map_x, .@map_y, 1, .@npc_shop_name$)) {
					debugmes "SQL Mall - NPC not found - " + .@npc_shop_name$;
				}
				else {
					npcshopdelitem .@npc_shop_name$, 909;
						
					do {
						.@index = (.@npc * .max_shop_item + .@x);
						.@total_cost = 0;
						
						// cost calculations
						.@total_cost += ((.@weight[.@index] / 10) * 1000);
						.@total_cost += ((.@atk[.@index] / 100) * 5000);
						.@total_cost += (.@slots[.@index] * 3000);
						.@total_cost += (.@weapon_level[.@index] * 2000);
						.@total_cost += ((.@defence[.@index] / 10) * 1000);
						.@total_cost += ((.@equip_level[.@index] / 10) * 10000);
				
						// effect count.
						.@total_cost += (countstr(.@script$[.@index], "bonus") * 1000);
						.@total_cost += (countstr(.@equip_script$[.@index], "bonus") * 1000);
						.@total_cost += (countstr(.@unequip_script$[.@index], "bonus") * 1000);
						.@total_cost += (countstr(.@unequip_script$[.@index], "heal") * 100);
						.@total_cost += (countstr(.@unequip_script$[.@index], "warp") * 1000);
						
						if (.@total_cost > 0)
							npcshopadditem .@npc_shop_name$, .@id[.@index], .@total_cost;
						.@x++;
						.@total_item_count++;
					} while (.@x < .max_shop_item && .@index < .@size);
					setnpcdisplay(.@npc_shop_name$, func_ShopTypeName(.@type)+"#t_"+.@type+"_"+.@npc, func_ShopClassID(.@type));
				}
				.@npc++;
			} while (.@npc <= .@npc_count);
			freeloop(0);
			
		}
		
		// Remove excessive NPC
		while (!getmapxy(.@map$, .@map_x, .@map_y, 1, "Shop#t_"+.@type+"_"+.@npc) || .@npc <= .@npc_count) {
			disablenpc "Shop#t_"+.@type+"_"+.@npc;
			.@npc++;
		}
		
		debugmes "SQL Mall - " + func_ShopTypeName(.@type) + " - Loaded " + F_InsertComma(.@total_item_count) + " Item(s)";
		return;

}


// Add more if needed (follow the name and numbering)

prt_fild08,203,72,5	shop	Shop#t_0_1	4_F_KAFRA1,909:-1
prt_fild08,205,72,5	shop	Shop#t_0_2	4_F_KAFRA1,909:-1
prt_fild08,207,72,5	shop	Shop#t_0_3	4_F_KAFRA1,909:-1
prt_fild08,209,72,5	shop	Shop#t_0_4	4_F_KAFRA1,909:-1

prt_fild08,203,74,5	shop	Shop#t_2_1	4_F_KAFRA1,909:-1
prt_fild08,205,74,5	shop	Shop#t_2_2	4_F_KAFRA1,909:-1
prt_fild08,207,74,5	shop	Shop#t_2_3	4_F_KAFRA1,909:-1
prt_fild08,209,74,5	shop	Shop#t_2_4	4_F_KAFRA1,909:-1
prt_fild08,211,74,5	shop	Shop#t_2_5	4_F_KAFRA1,909:-1
prt_fild08,213,74,5	shop	Shop#t_2_6	4_F_KAFRA1,909:-1
prt_fild08,215,74,5	shop	Shop#t_2_7	4_F_KAFRA1,909:-1
prt_fild08,217,74,5	shop	Shop#t_2_8	4_F_KAFRA1,909:-1
prt_fild08,219,74,5	shop	Shop#t_2_9	4_F_KAFRA1,909:-1
prt_fild08,221,74,5	shop	Shop#t_2_10	4_F_KAFRA1,909:-1

prt_fild08,203,76,5	shop	Shop#t_3_1	4_F_KAFRA1,909:-1
prt_fild08,205,76,5	shop	Shop#t_3_2	4_F_KAFRA1,909:-1
prt_fild08,207,76,5	shop	Shop#t_3_3	4_F_KAFRA1,909:-1
prt_fild08,209,76,5	shop	Shop#t_3_4	4_F_KAFRA1,909:-1
prt_fild08,211,76,5	shop	Shop#t_3_5	4_F_KAFRA1,909:-1
prt_fild08,213,76,5	shop	Shop#t_3_6	4_F_KAFRA1,909:-1
prt_fild08,215,76,5	shop	Shop#t_3_7	4_F_KAFRA1,909:-1
prt_fild08,217,76,5	shop	Shop#t_3_8	4_F_KAFRA1,909:-1
prt_fild08,219,76,5	shop	Shop#t_3_9	4_F_KAFRA1,909:-1

prt_fild08,203,78,5	shop	Shop#t_3_10	4_F_KAFRA1,909:-1
prt_fild08,205,78,5	shop	Shop#t_3_11	4_F_KAFRA1,909:-1
prt_fild08,207,78,5	shop	Shop#t_3_12	4_F_KAFRA1,909:-1
prt_fild08,209,78,5	shop	Shop#t_3_13	4_F_KAFRA1,909:-1
prt_fild08,211,78,5	shop	Shop#t_3_14	4_F_KAFRA1,909:-1
prt_fild08,213,78,5	shop	Shop#t_3_15	4_F_KAFRA1,909:-1
prt_fild08,215,78,5	shop	Shop#t_3_16	4_F_KAFRA1,909:-1
prt_fild08,217,78,5	shop	Shop#t_3_17	4_F_KAFRA1,909:-1
prt_fild08,219,78,5	shop	Shop#t_3_18	4_F_KAFRA1,909:-1
prt_fild08,221,78,5	shop	Shop#t_3_19	4_F_KAFRA1,909:-1

prt_fild08,203,80,5	shop	Shop#t_4_1	4_F_KAFRA1,909:-1
prt_fild08,205,80,5	shop	Shop#t_4_2	4_F_KAFRA1,909:-1
prt_fild08,207,80,5	shop	Shop#t_4_3	4_F_KAFRA1,909:-1
prt_fild08,209,80,5	shop	Shop#t_4_4	4_F_KAFRA1,909:-1
prt_fild08,211,80,5	shop	Shop#t_4_5	4_F_KAFRA1,909:-1
prt_fild08,213,80,5	shop	Shop#t_4_6	4_F_KAFRA1,909:-1
prt_fild08,215,80,5	shop	Shop#t_4_7	4_F_KAFRA1,909:-1
prt_fild08,217,80,5	shop	Shop#t_4_8	4_F_KAFRA1,909:-1
prt_fild08,219,80,5	shop	Shop#t_4_9	4_F_KAFRA1,909:-1
prt_fild08,221,80,5	shop	Shop#t_4_10	4_F_KAFRA1,909:-1
prt_fild08,223,80,5	shop	Shop#t_4_11	4_F_KAFRA1,909:-1

prt_fild08,203,82,5	shop	Shop#t_5_1	4_F_KAFRA1,909:-1
prt_fild08,205,82,5	shop	Shop#t_5_2	4_F_KAFRA1,909:-1
prt_fild08,207,82,5	shop	Shop#t_5_3	4_F_KAFRA1,909:-1
prt_fild08,209,82,5	shop	Shop#t_5_4	4_F_KAFRA1,909:-1
prt_fild08,211,82,5	shop	Shop#t_5_5	4_F_KAFRA1,909:-1
prt_fild08,213,82,5	shop	Shop#t_5_6	4_F_KAFRA1,909:-1
prt_fild08,215,82,5	shop	Shop#t_5_7	4_F_KAFRA1,909:-1
prt_fild08,217,82,5	shop	Shop#t_5_8	4_F_KAFRA1,909:-1
prt_fild08,219,82,5	shop	Shop#t_5_9	4_F_KAFRA1,909:-1

prt_fild08,203,84,5	shop	Shop#t_5_10	4_F_KAFRA1,909:-1
prt_fild08,205,84,5	shop	Shop#t_5_11	4_F_KAFRA1,909:-1
prt_fild08,207,84,5	shop	Shop#t_5_12	4_F_KAFRA1,909:-1
prt_fild08,209,84,5	shop	Shop#t_5_13	4_F_KAFRA1,909:-1
prt_fild08,211,84,5	shop	Shop#t_5_14	4_F_KAFRA1,909:-1
prt_fild08,213,84,5	shop	Shop#t_5_15	4_F_KAFRA1,909:-1
prt_fild08,215,84,5	shop	Shop#t_5_16	4_F_KAFRA1,909:-1
prt_fild08,217,84,5	shop	Shop#t_5_17	4_F_KAFRA1,909:-1
prt_fild08,219,84,5	shop	Shop#t_5_18	4_F_KAFRA1,909:-1
prt_fild08,221,84,5	shop	Shop#t_5_19	4_F_KAFRA1,909:-1

prt_fild08,203,86,5	shop	Shop#t_5_20	4_F_KAFRA1,909:-1
prt_fild08,205,86,5	shop	Shop#t_5_21	4_F_KAFRA1,909:-1
prt_fild08,207,86,5	shop	Shop#t_5_22	4_F_KAFRA1,909:-1
prt_fild08,209,86,5	shop	Shop#t_5_23	4_F_KAFRA1,909:-1
prt_fild08,211,86,5	shop	Shop#t_5_24	4_F_KAFRA1,909:-1
prt_fild08,213,86,5	shop	Shop#t_5_25	4_F_KAFRA1,909:-1
prt_fild08,215,86,5	shop	Shop#t_5_26	4_F_KAFRA1,909:-1
prt_fild08,217,86,5	shop	Shop#t_5_27	4_F_KAFRA1,909:-1
prt_fild08,219,86,5	shop	Shop#t_5_28	4_F_KAFRA1,909:-1
prt_fild08,221,86,5	shop	Shop#t_5_29	4_F_KAFRA1,909:-1

prt_fild08,203,88,5	shop	Shop#t_5_30	4_F_KAFRA1,909:-1
prt_fild08,205,88,5	shop	Shop#t_5_31	4_F_KAFRA1,909:-1
prt_fild08,207,88,5	shop	Shop#t_5_32	4_F_KAFRA1,909:-1
prt_fild08,209,88,5	shop	Shop#t_5_33	4_F_KAFRA1,909:-1

prt_fild08,203,90,5	shop	Shop#t_6_1	4_F_KAFRA1,909:-1
prt_fild08,205,90,5	shop	Shop#t_6_2	4_F_KAFRA1,909:-1
prt_fild08,207,90,5	shop	Shop#t_6_3	4_F_KAFRA1,909:-1
prt_fild08,209,90,5	shop	Shop#t_6_4	4_F_KAFRA1,909:-1
prt_fild08,211,90,5	shop	Shop#t_6_5	4_F_KAFRA1,909:-1
prt_fild08,213,90,5	shop	Shop#t_6_6	4_F_KAFRA1,909:-1
prt_fild08,215,90,5	shop	Shop#t_6_7	4_F_KAFRA1,909:-1
prt_fild08,217,90,5	shop	Shop#t_6_8	4_F_KAFRA1,909:-1
prt_fild08,219,90,5	shop	Shop#t_6_9	4_F_KAFRA1,909:-1

prt_fild08,203,92,5	shop	Shop#t_6_10	4_F_KAFRA1,909:-1
prt_fild08,205,92,5	shop	Shop#t_6_11	4_F_KAFRA1,909:-1

prt_fild08,203,94,5	shop	Shop#t_7_1	4_F_KAFRA1,909:-1

prt_fild08,203,96,5	shop	Shop#t_8_1	4_F_KAFRA1,909:-1

prt_fild08,203,98,5	shop	Shop#t_10_1	4_F_KAFRA1,909:-1
prt_fild08,205,98,5	shop	Shop#t_10_2	4_F_KAFRA1,909:-1

prt_fild08,203,100,5	shop	Shop#t_11_1	4_F_KAFRA1,909:-1
prt_fild08,205,100,5	shop	Shop#t_11_2	4_F_KAFRA1,909:-1

prt_fild08,203,102,5	shop	Shop#t_18_1	4_F_KAFRA1,909:-1
prt_fild08,205,102,5	shop	Shop#t_18_2	4_F_KAFRA1,909:-1
prt_fild08,207,102,5	shop	Shop#t_18_3	4_F_KAFRA1,909:-1
prt_fild08,209,102,5	shop	Shop#t_18_4	4_F_KAFRA1,909:-1
prt_fild08,211,102,5	shop	Shop#t_18_5	4_F_KAFRA1,909:-1
prt_fild08,213,102,5	shop	Shop#t_18_6	4_F_KAFRA1,909:-1
prt_fild08,215,102,5	shop	Shop#t_18_7	4_F_KAFRA1,909:-1
prt_fild08,217,102,5	shop	Shop#t_18_8	4_F_KAFRA1,909:-1
prt_fild08,219,102,5	shop	Shop#t_18_9	4_F_KAFRA1,909:-1

prt_fild08,203,104,5	shop	Shop#t_18_10	4_F_KAFRA1,909:-1
prt_fild08,205,104,5	shop	Shop#t_18_11	4_F_KAFRA1,909:-1