-- LosaDev 2014 testbed: fixes for the 2014 servers on top of the May 2012 scripts, plus test accounts.
USE [LosaGame];
GO

-- 1. The 2014 game server sends its 64-bit server ID (query 3), the 2012 procedure expected the row index.
ALTER PROCEDURE [dbo].[game_server_connection_on]
(
	@serverID bigint
)
AS
	UPDATE define_game_server SET [status] = 1 WHERE serverID = @serverID
GO

-- 2. Second encryption key (query 5, game_encode_get_fix_key). The login key's ID part is encrypted
--    with it. Shorter than 16 characters.
IF NOT EXISTS (SELECT * FROM define_encode_key)
	INSERT INTO define_encode_key (encodeKey, regDate) VALUES ('lsdevhub2014key', getdate());
GO

-- 3. Test accounts. The game server (Korea mode) checks userMemberDB.userID + userLoginDB.encodeKey;
--    there is no password check on the server: the launcher/web normally writes a fresh encodeKey
--    and passes it to the client (tools\start-client.ps1 does that here).
CREATE PROCEDURE [dbo].[lshub_add_test_account]
(
	@userID varchar(12)
,	@nickName varchar(20)
)
AS
	SET NOCOUNT ON
	IF EXISTS (SELECT * FROM userMemberDB WHERE userID = @userID) RETURN
	INSERT INTO userMemberDB (userID, nickName, userPWD, EMail, mailling, userType, joinType, limitType, limitDate, regDate)
	VALUES (@userID, @nickName, '', '', 0, 0, 0, 0, '1999-01-01', getdate())
	DECLARE @accountIDX int = (SELECT accountIDX FROM userMemberDB WHERE userID = @userID)
	INSERT INTO userGameDB (accountIDX, userState, gameMoney, playTime, conn_count, userLevel, userEXP, rencpoint, renspoint, relateLevel, regionType, connDate, regDate)
	VALUES (@accountIDX, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2000-01-01', getdate())
	INSERT INTO userRecordBattleDB (accountIDX, regDate) VALUES (@accountIDX, getdate())
	INSERT INTO userLoginDB (accountIDX, encodeKey, gameServerID, connDate) VALUES (@accountIDX, '111111111111111', 0, getdate())
GO

-- Resets the login key before a client start (what the web login did). Returns the key.
CREATE PROCEDURE [dbo].[lshub_set_login_key]
(
	@userID varchar(12)
,	@encodeKey char(15)
)
AS
	SET NOCOUNT ON
	UPDATE userLoginDB SET encodeKey = @encodeKey, gameServerID = 0, connDate = getdate()
	WHERE accountIDX = (SELECT accountIDX FROM userMemberDB WHERE userID = @userID)
	SELECT encodeKey, (SELECT TOP 1 encodeKey FROM define_encode_key ORDER BY idx DESC) AS secondKey
	FROM userLoginDB WHERE accountIDX = (SELECT accountIDX FROM userMemberDB WHERE userID = @userID)
GO

EXEC lshub_add_test_account 'test1', 'Tester1';
EXEC lshub_add_test_account 'test2', 'Tester2';
-- GM: the game server treats a nickname that starts with an [ID_LIKE] prefix from
-- ls_gamesvr\config\sp2_developerID.ini (here "[GM]") as a developer.
EXEC lshub_add_test_account 'gmtest1', '[GM]Admin';
GO

-- LosaDev 2014 testbed: procedures whose 2012 parameter list is shorter than what the 2014 game server sends
-- ("has too many arguments specified"). The extra parameters get defaults; behaviour otherwise unchanged.
USE [LosaGame];
GO

-- query 22: the 2014 server pages the gear list (last index, account, page size)
ALTER PROCEDURE [dbo].[game_item_extra_get_list]
(
	@lastIndex int
,	@accountIDX int = 0
,	@pageSize int = 0
)
AS
	SET NOCOUNT ON
	SELECT TOP (CASE WHEN @pageSize > 0 THEN @pageSize ELSE 2147483647 END) idx
,			item1_type, item1_reinforce, item1_slotindex, item1_tradetype, item1_limitType, item1_mcustom, item1_fcustom, item1_limitDate
,			item2_type, item2_reinforce, item2_slotindex, item2_tradetype, item2_limitType, item2_mcustom, item2_fcustom, item2_limitDate
,			item3_type, item3_reinforce, item3_slotindex, item3_tradetype, item3_limitType, item3_mcustom, item3_fcustom, item3_limitDate
,			item4_type, item4_reinforce, item4_slotindex, item4_tradetype, item4_limitType, item4_mcustom, item4_fcustom, item4_limitDate
,			item5_type, item5_reinforce, item5_slotindex, item5_tradetype, item5_limitType, item5_mcustom, item5_fcustom, item5_limitDate
,			item6_type, item6_reinforce, item6_slotindex, item6_tradetype, item6_limitType, item6_mcustom, item6_fcustom, item6_limitDate
,			item7_type, item7_reinforce, item7_slotindex, item7_tradetype, item7_limitType, item7_mcustom, item7_fcustom, item7_limitDate
,			item8_type, item8_reinforce, item8_slotindex, item8_tradetype, item8_limitType, item8_mcustom, item8_fcustom, item8_limitDate
,			item9_type, item9_reinforce, item9_slotindex, item9_tradetype, item9_limitType, item9_mcustom, item9_fcustom, item9_limitDate
,			item10_type, item10_reinforce, item10_slotindex, item10_tradetype, item10_limitType, item10_mcustom, item10_fcustom, item10_limitDate
	FROM userItemExtraDB WHERE accountIDX = @accountIDX AND idx > @lastIndex ORDER BY idx
GO

-- query 26: same paging for the decoration item list (last index, account, page size)
ALTER PROCEDURE [dbo].[game_item_get_data_list]
(
	@lastIndex int
,	@accountIDX int = 0
,	@pageSize int = 0
)
AS
	SET NOCOUNT ON
	SELECT TOP (CASE WHEN @pageSize > 0 THEN @pageSize ELSE 2147483647 END)
		idx, item1_type, item1_code, item2_type, item2_code, item3_type, item3_code,
		item4_type, item4_code, item5_type, item5_code, item6_type, item6_code,
		item7_type, item7_code, item8_type, item8_code, item9_type, item9_code,
		item10_type, item10_code, item11_type, item11_code, item12_type, item12_code,
		item13_type, item13_code, item14_type, item14_code, item15_type, item15_code,
		item16_type, item16_code, item17_type, item17_code, item18_type, item18_code,
		item19_type, item19_code, item20_type, item20_code
	FROM userItemDB WHERE accountIDX = @accountIDX AND idx > @lastIndex ORDER BY idx
GO

-- query 12: the 2014 server adds the relative grade init code as 15th value (not stored here)
ALTER PROCEDURE [dbo].[game_game_save]
(
	@accountIDX int
,	@userState int
,	@gameMoney bigint
,	@conn_count int
,	@connDate datetime
,	@userLevel int
,	@userEXP int
,	@playTime int
,	@regionType int
,	@userFishingLevel int
,	@userFishingEXP int
,	@refillData int
,	@userExcavationLevel int
,	@userExcavationEXP int
,	@relativeInitCode int = 0
)
AS
	UPDATE userGameDB SET
	gameMoney = @gameMoney
,	userState = @userState
,	playTime = playTime + Round((@playTime/1000)/60,0)
,	conn_count = @conn_count
,	userLevel = @userLevel
,	userEXP = @userEXP
,	userFishingLevel = @userFishingLevel
,	userFishingEXP = @userFishingEXP
,	regionType = @regionType
,	connDate = @connDate
,	refillData = @refillData
,	userExcavationLevel = @userExcavationLevel
,	userExcavationEXP = @userExcavationEXP
	WHERE accountIDX = @accountIDX
GO

-- Test heroes for the three test accounts. A new account owns no hero, so the game opens with a
-- "no hero available" popup. Six permanent heroes each (limitType 1 = CPT_MORTMAIN). Class 6, Savage
-- Berserker, comes first and is the leader (leaderType 1): the headquarters tutorial is written for
-- that hero and gets stuck with another one. Same columns as game_character_add. Safe to run
-- again: accounts that already own a hero are skipped.
USE [LosaGame]
GO
DECLARE @acc int
DECLARE acc CURSOR LOCAL FAST_FORWARD FOR
	SELECT accountIDX FROM dbo.userMemberDB m
	WHERE m.userID IN ('test1', 'test2', 'gmtest1')
	  AND NOT EXISTS (SELECT 1 FROM dbo.userCharacterDB c WHERE c.accountIDX = m.accountIDX)
OPEN acc
FETCH NEXT FROM acc INTO @acc
WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO dbo.userCharacterDB (accountIDX, ClassType, CI_type, CI_gender, CI_beard, CI_face, CI_hair, CI_skinColor, CI_hairColor, CI_trinket, CI_underwear, CI_position, EI1_type, EI2_type, EI3_type, EI4_type, limitTime, limitType, leaderType, rentalType, rentalTime)
	SELECT @acc, h.class, 1, 1, 1, 1, 1, 1, 1, 1, 1, h.pos, 0, 0, 0, 0, 0, 1, CASE WHEN h.class = 6 THEN 1 ELSE 0 END, 0, 0
	FROM (VALUES (6, 0), (1, 1), (2, 2), (3, 3), (4, 4), (5, 5)) AS h(class, pos)
	ORDER BY h.pos
	-- Tutorial set = class type * 100 (User::_OnLeaderCreate). Skills only count when the hero's gear
	-- set matches it, so the tutorial must be the Savage set.
	UPDATE dbo.userGameDB SET userState = 600 WHERE accountIDX = @acc
	FETCH NEXT FROM acc INTO @acc
END
CLOSE acc
DEALLOCATE acc
SELECT m.userID, COUNT(c.idx) AS heroes FROM dbo.userMemberDB m LEFT JOIN dbo.userCharacterDB c ON c.accountIDX = m.accountIDX GROUP BY m.userID
GO