-- Meacher's "Create Account and Character" SQL Script -- v1 -- 11.08.2024
-- For use on AVA Alpha Server SQL DB Only!!

-- ClassID's:  1 = warrior, 2 = sorcerer, 3 = taoist, 4 = Arbalist, 5 = Lancer
-- Newly created Characters will be located in Bicheon

-- Change below values between apostrophes as needed
SET @username = 'Username';
SET @password = 'password';
SET @charname = 'Char_Name';
SET @classid = '1';
SET @email = 'YourMum@MyHouse.Orgy';
SET @lev = '140'; 


-- Inserts below, don't alter unless you know what you are doing!! 
-- (or at least wise enough backup before-hand)

-- Hash Password
SET @hashed_password = UPPER(SHA2(@password, 256));

-- Use user DB
USE mm_user_db;
INSERT INTO mm_user_db.user_tb (`AccountUID`, `Username`, `Email`, `PasswordHash`, `Status`) VALUES (NULL, @username, @email, @hashed_password, 0);

-- Get Account UID from last insert
SET @last_id = LAST_INSERT_ID();

-- switch to front DB
USE mm_front_db;

-- Add Account to Front DB
INSERT INTO mm_front_db.account_tb (`AccountUID`, `AccountType`, `AccountName`, `SDKUID`, `NationUID`, `CreateTime`, `LoginTime`) VALUES (@last_id, '2', @username, '1', '3', NOW(), NOW());

-- Switch to Game DB
USE mm_game_db_release;

-- Add Account to Game
INSERT INTO mm_game_db_release.account_tb (AccountUID, AccountName, Cash) VALUES (@last_id, @username, '0');

-- Add Character to Game (in bicheon)
INSERT INTO mm_game_db_release.character_tb 
(`CharacterName`, `AccountUID`, `Class`, `Lev`, `Exp`, `CurrentHP`, `CurrentMP`, `StageIdx`, `PositionX`, `PositionY`, `PositionZ`, `Wonbo`, `Gold`, `CreateTime`, `LoginTime`,
 `LogoutTime`, `PlaytimeSec`, `CombatPoint`, `EnergyPoint`, `BlackIron`, `ActionPoint`, `LastActionPointUpdateTime`, `AwakenGrade`, `InvenExtendCnt`, `PKPoint`, `CostumeIdx`) 
VALUES 
(@charname, @last_id, @classid, @lev, 0, 35000, 1000, 101001010, -2025.47, -9780.93, 508.538, 1000, 50000, NOW(), NOW(), NOW(), 1, 1, 1, 1, 1, NOW(), 0, 0, 0, 0);

-- Get Character UID from last insert
SET @last_id = LAST_INSERT_ID();

-- Get Class Ultimate Skill ID
SET @class_ultimate = CASE 
  WHEN @classid = 1 THEN 1403
  WHEN @classid = 2 THEN 2403
  WHEN @classid = 3 THEN 3303
  WHEN @classid = 4 THEN 4113
  WHEN @classid = 5 THEN 5203
  ELSE ''
END;

-- Add Lvl 1 Ultimate Skill to Character
INSERT INTO mm_game_db_release.skill_active_tb (CharacterUID, ActiveIdx, ActiveLev) VALUES (@last_id, @class_ultimate, 1);

-- Add Quest to Character (jump/airwalk/"Standard issue korean fantasy bullshit flying thrust non-sense")
INSERT INTO mm_game_db_release.quest_main_tb (CharacterUID, QuestIdx, State, QuestValue) VALUES (@last_id, 107004100, 1, 5);