ALTER TABLE accounts
  CHARACTER SET UTF8,
  ADD email varchar(20),
  ADD question1 varchar(40),
  ADD answer1 varchar(40),
  ADD question2 varchar(40),
  ADD answer2 varchar(40);

CREATE TABLE l2jbs_bans (
  account_name varchar(45),
  obj_Id INT NOT NULL,
  baned timestamp,
  unban timestamp,
  reason varchar(100),
  GM varchar(35),
  PRIMARY KEY (obj_Id)
) TYPE=MyISAM;

DROP TABLE IF EXISTS tmp_l2jbs_characters, tmp_l2jbs_items, l2jbs_rich;

CREATE TEMPORARY TABLE tmp_l2jbs_characters (
  owner_id INT NOT NULL,
  char_name varchar(35) NOT NULL
) TYPE=MEMORY
SELECT obj_id AS owner_id, char_name FROM characters WHERE !accesslevel;

CREATE TABLE tmp_l2jbs_items (
  owner_id INT NOT NULL,
  item_id INT NOT NULL,
  count INT NOT NULL,
  INDEX (owner_id),
  INDEX (item_id)
) TYPE=MEMORY
SELECT owner_id,item_id,count FROM items WHERE loc != 'CLANWH';

CREATE TABLE l2jbs_rich (
  pos INT SIGNED NULL AUTO_INCREMENT PRIMARY KEY,
  owner_id INT NOT NULL,
  char_name varchar(35) NOT NULL,
  weapon INT UNSIGNED NOT NULL,
  armor INT UNSIGNED NOT NULL,
  etcitem INT UNSIGNED NOT NULL,
  adena INT UNSIGNED NOT NULL,
  summary INT UNSIGNED NOT NULL
) TYPE=MyISAM
SELECT tmp_l2jbs_characters.*, weapon, armor, etcitem, adena, weapon+armor+etcitem+adena AS summary FROM tmp_l2jbs_characters LEFT JOIN (SELECT owner_id,SUM(count*price) AS weapon FROM tmp_l2jbs_items,weapon WHERE tmp_l2jbs_items.item_id=weapon.item_id GROUP BY owner_id) AS t_w USING(owner_id) LEFT JOIN (SELECT owner_id,SUM(count*price) AS armor FROM tmp_l2jbs_items,armor WHERE tmp_l2jbs_items.item_id=armor.item_id GROUP BY owner_id) AS t_ar USING(owner_id) LEFT JOIN (SELECT owner_id,SUM(count*price) AS etcitem FROM tmp_l2jbs_items,etcitem WHERE tmp_l2jbs_items.item_id != 57 AND tmp_l2jbs_items.item_id=etcitem.item_id GROUP BY owner_id) AS t_e USING(owner_id) LEFT JOIN (SELECT owner_id,SUM(count) AS adena FROM tmp_l2jbs_items WHERE tmp_l2jbs_items.item_id=57 GROUP BY owner_id) AS t_ad USING(owner_id) ORDER BY summary DESC;

DROP TABLE tmp_l2jbs_items;

ALTER TABLE l2jbs_rich ADD pos_old INT SIGNED NULL;
