-- Query: Add new NPC into database with shop option.


DECLARE @nType tinyint  = 1 -- 1 = npc, 2 = tab, 3 = group
---------------------------------------------------------------------------------------
-- NPC Related
,	@szNpc varchar(129) = 'KAISTERU' -- NPC name without "NPC_" tag (eg. CH_JINJIN)
,	@szNpcModel varchar(129) = 'kisaeng.bsr' -- Full path to .bsr located in data (eg. npc\npc\EastEuropeSystem_HunterUnion.bsr)
,	@szCharname varchar(17) = 'tcpcshow003' -- Character name, the char should be in place, where NPC has to be located.
,	@wInitialDir smallint = 32766 -- The direction of NPC.
,	@nFortressID tinyint = 1 -- FortressID here. 1 - Jangan, 2- Bandit, 3- Hotan 4 - Constantinople

---------------------------------------------------------------------------------------
-- Shop Related

,	@nCreateShops tinyint = 1 -- 1 to enable shops, 0 to create just npc.
,	@szTabName varchar(129) = 'TEXT' -- Tab name without SN* and *TABn (eg. POTIONS_PRO)
,	@nGameWorldIndex int = 1; -- Gameworld index. Use 1 for default, 99 for Jupiter.
---------------------------------------------------------------------------------------

-- Last Ids
DECLARE @nTabIndex tinyint

SELECT TOP 1 @nTabIndex = CAST(RIGHT(CodeName128,1) as tinyint)
FROM _RefShopTab WHERE CodeName128 LIKE 'STORE_'+@szNPC+'_TAB_'
ORDER BY CodeName128 DESC

DECLARE @nGroupIndex tinyint

SELECT TOP 1 @nGroupIndex = CAST(RIGHT(CodeName128,1) as tinyint)
FROM _RefShopTab WHERE CodeName128 LIKE 'STORE_'+@szNPC+'_GROUP_'
ORDER BY CodeName128 DESC

DECLARE @nObjCharIndex int
	,	@nObjCommonIndex int
	,	@nHiveIndex int
	,	@nTacticsIndex int;

---------------------------------------------------------

IF (@nType = 1)
BEGIN
	-- Step 1 - Insert NPC into tables.

	IF EXISTS (SELECT * FROM _RefObjCommon WHERE CodeName128 like '%'+@szNPC+'%')
	BEGIN
		print('Are you gay? This NPC exists already.')
		RETURN
	END

	DECLARE @nRegionID smallint = (SELECT LatestRegion FROM _Char where CharName16 = @szCharname)
		,	@nPosX real = (SELECT PosX FROM _Char WHERE CharName16 = @szCharname)
		,	@nPosY real = (SELECT PosY FROM _Char WHERE CharName16 = @szCharname)
		,	@nPosZ real = (SELECT PosZ FROM _Char WHERE CharName16 = @szCharname);

	INSERT _RefObjChar
	VALUES('0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','336860180','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0');
	SET @nObjCharIndex = SCOPE_IDENTITY();

	INSERT _RefObjCommon
	VALUES('1','NPC_' + @szNPC,'xxx','xxx','SN_NPC_'+@szNpc,'xxx','0','1','1','2','2','0','5000','3','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','-1','0','-1','0','-1','0','-1','0','-1','0','0','0','0','0','0','0','100','0','0','0',+@szNpcModel,'xxx','xxx',@nFortressID,'xxx',@nObjCharIndex);
	SET @nObjCommonIndex = SCOPE_IDENTITY();

	INSERT Tab_RefHive
	VALUES('0','0','0','0','0','0',@nGameWorldIndex,'2','NPC_'+@szNpc);
	SET @nHiveIndex = SCOPE_IDENTITY();

	INSERT Tab_RefTactics
	VALUES(@nObjCommonIndex,'0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','1','0','0','0','0','0','0','0','Npc_Test');
	SET @nTacticsIndex = SCOPE_IDENTITY();

	INSERT Tab_RefNest
	VALUES(@nHiveIndex, @nTacticsIndex, @nRegionID, @nPosX, @nPosY, @nPosZ, @wInitialDir,'0','0','0','0','0','1','0','1','0');

	if(@nCreateShops = 1)
	BEGIN
		-- Step 2 - Insert Shop into tables.
		INSERT _RefShop
		VALUES('1','15','STORE_' + @szNpc,'-1','xxx','-1','xxx','-1','xxx','-1','xxx');

		INSERT _RefShopGroup
		VALUES('1','15','GROUP_' +@szNpc,'NPC_'+@szNpc,'-1','xxx','-1','xxx','-1','xxx','-1','xxx');

		INSERT _RefShopTab
		VALUES ('1','15','STORE_'+@szNpc+'_TAB1','STORE_'+@szNpc+'_GROUP1','SN_'+@szTabName+'_TAB1');

		INSERT _RefShopTabGroup
		VALUES ('1','15','STORE_'+@szNpc+'_GROUP1','SN_STORE_'+@szNpc+'_GROUP1');

		INSERT _RefShopItemGroup
		VALUES('1', 'STORE_'+@szNPC+'_GROUP1', 'SN_STORE_'+@szNPC+'_GROUP1')

		INSERT _RefMappingShopGroup
		VALUES('1','15','GROUP_STORE_'+@szNpc,'STORE_'+@szNpc);

		INSERT _RefMappingShopWithTab
		VALUES('1','15','STORE_'+@szNpc,'STORE_'+@szNpc+'_GROUP1');
	END
END

ELSE IF (@nType = 2)
BEGIN
	IF NOT EXISTS (SELECT * FROM _RefObjCommon WHERE CodeName128 like '%'+@szNPC+'%')
	BEGIN
		print('Are you gay? This NPC does not exist!');
		RETURN
	END

	if (@nTabIndex > 4)
	BEGIN
		print('You have got already 4 tabs for this npc. Please try other one!');
		RETURN

	INSERT _RefShopTab
	VALUES ('1','15','STORE_'+@szNpc+'_TAB'+@nTabIndex,'STORE_'+@szNpc+'_GROUP'+@nGroupIndex,'SN_'+@szTabName+'_TAB'+@nTabIndex);

END

ELSE IF (@nType = 3)
BEGIN
	IF NOT EXISTS (SELECT * FROM _RefObjCommon WHERE CodeName128 like '%'+@szNPC+'%')
	BEGIN
		print('Are you gay? This NPC does not exist!')
		RETURN
	END

	if (@nGroupIndex > 6)
	BEGIN
		print('You have got already 6 groups for this npc. Please try other one!')
		RETURN
	END


	INSERT _RefShopItemGroup
	VALUES('1', 'STORE_'+@szNPC+'_GROUP'+@nGroupIndex, 'SN_STORE_'+@szNPC+'_GROUP'+@nGroupIndex);

	INSERT _RefShopTabGroup
	VALUES ('1','15','STORE_'+@szNpc+'_GROUP'+@nGroupIndex,'SN_STORE_'+@szNpc+'_GROUP'+@nGroupIndex);

	INSERT _RefMappingShopGroup
	VALUES('1','15','GROUP_STORE_'+@szNpc,'STORE_'+@szNpc);

	INSERT _RefMappingShopWithTab
	VALUES('1','15','STORE_'+@szNpc,'STORE_'+@szNpc+'_GROUP'+@nGroupIndex);

END
END