-- LosaDev 2019: procedures the 2019 server calls (ls_query.ini ids 1, 136, 137, 139)
-- that no released DB script contains. Written for the testbed from the server code (DBClient.cpp).
-- Still missing on purpose: 197 game_item_extra_get_list_dev, 1500 SC_ERROR_LOG_ADD,
-- 2108 log_item_bonus_gold_set_add, 2207 game_block_hdd_serial_check (dev/log/anti-cheat only).
USE [LosaGame]
GO
IF OBJECT_ID(N'dbo.game_pingpong', N'P') IS NOT NULL DROP PROCEDURE dbo.game_pingpong
GO
-- id 1: DB agent keep-alive; the code says "a query that does not really exist"
CREATE PROCEDURE dbo.game_pingpong
AS
	SET NOCOUNT ON
GO
IF OBJECT_ID(N'dbo.game_member_count', N'P') IS NOT NULL DROP PROCEDURE dbo.game_member_count
GO
-- id 136: OnSelectMemberConut - one LONG back, 0 = new account (the server then calls game_member_add)
CREATE PROCEDURE dbo.game_member_count
	@userID varchar(12)
AS
	SET NOCOUNT ON
	SELECT COUNT(*) FROM dbo.userMemberDB WITH(NOLOCK) WHERE userID = @userID
GO
IF OBJECT_ID(N'dbo.game_member_add', N'P') IS NOT NULL DROP PROCEDURE dbo.game_member_add
GO
-- id 137: OnInsertMember(privateID, publicID) - first login of a publisher account creates the game account
CREATE PROCEDURE dbo.game_member_add
	@userID varchar(12),
	@nickName varchar(20)
AS
	SET NOCOUNT ON
	IF EXISTS (SELECT 1 FROM dbo.userMemberDB WITH(NOLOCK) WHERE userID = @userID) RETURN
	DECLARE @ret int
	EXEC dbo.web_member_add @userID, @nickName, '', '', 0, 0, 0, 0, '127.0.0.1', @ret OUTPUT
GO
IF OBJECT_ID(N'dbo.game_create_nickname', N'P') IS NOT NULL DROP PROCEDURE dbo.game_create_nickname
GO
-- id 139: OnUpdateFirstPublicID(accountIDX, newNick)
CREATE PROCEDURE dbo.game_create_nickname
	@accountIDX int,
	@nickName varchar(20)
AS
	SET NOCOUNT ON
	UPDATE dbo.userMemberDB SET nickName = @nickName WHERE accountIDX = @accountIDX
GO
-- id 11 game_game_get_data: the released script returns cpID/cpUNIQ (channeling columns), but the game server only
-- reads them when the locale uses channeling IDs (Korea). The Indonesia locale expects the same row without them.
ALTER PROCEDURE [dbo].[game_game_get_data]
(
	@userID varchar(12)
)
As
SET NOCOUNT ON
DECLARE @CURRENTDATE DATETIME
DECLARE @CONNECTDATE DATETIME
SET @CURRENTDATE = GETDATE();
SET @CONNECTDATE = ( SELECT conndate FROM dbo.userGameDB WHERE accountIDX IN ( SELECT accountIDX FROM dbo.userMemberDB WHERE userID = @userID ))
IF( @CURRENTDATE < @CONNECTDATE )
	SET @CONNECTDATE = @CURRENTDATE
SELECT a.accountIDX, b.userID, b.nickName, a.userState, a.gameMoney
  , a.conn_count, @CONNECTDATE, a.userLevel, a.userEXP
  , b.eventType, isNull(c.class,0) as 'class', b.joinType, b.regDate, a.regionType
  , a.rencpoint, a.renspoint, isNull(c.region,0) as 'region', b.cpType
  , b.limitType, b.limitDate, a.userFishingLevel, a.userFishingEXP
  , a.refillData, a.userExcavationLevel, a.userExcavationEXP, a.userHerocEXP, a.userHerosEXP, a.practice
  FROM userGameDB a WITH( NOLOCK )
  JOIN userMemberDB b WITH( NOLOCK ) ON a.accountIDX = b.accountIDX
  LEFT JOIN userRankingDB c WITH( NOLOCK ) ON a.accountIDX = c.accountIDX
 WHERE b.userID = @userID
GO