/*
 Navicat Premium Data Transfer

 Source Server         : AyalaOdin
 Source Server Type    : SQL Server
 Source Server Version : 12002000
 Source Host           : 172.16.69.1:1433
 Source Catalog        : DF_Account
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 12002000
 File Encoding         : 65001

 Date: 24/07/2018 12:57:22
*/


-- ----------------------------
-- Table structure for Accounts
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[Accounts]') AND type IN ('U'))
	DROP TABLE [dbo].[Accounts]
GO

CREATE TABLE [dbo].[Accounts] (
  [ID] int  IDENTITY(1,1) NOT NULL,
  [Name] nvarchar(16) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL,
  [EMail] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL,
  [Password] nvarchar(32) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL,
  [CreationDate] datetime DEFAULT (getdate()) NOT NULL,
  [CreationIP] nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT (N'127.0.0.1') NOT NULL,
  [IsActivated] bit DEFAULT ((0)) NOT NULL,
  [IsBanned] bit DEFAULT ((0)) NOT NULL,
  [IsOnline] bit DEFAULT ((0)) NOT NULL,
  [LastLogin] datetime DEFAULT (getdate()) NOT NULL,
  [LastIP] nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT (N'127.0.0.1') NOT NULL,
  [RoleID] tinyint DEFAULT ((0)) NOT NULL,
  [RegionID] tinyint DEFAULT ((0)) NOT NULL,
  [BanDate] datetime DEFAULT (getdate()) NOT NULL,
  [BanTime] bigint DEFAULT ((0)) NOT NULL,
  [BanReason] text COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT '' NULL
)
GO

ALTER TABLE [dbo].[Accounts] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Table structure for IPBlock
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[IPBlock]') AND type IN ('U'))
	DROP TABLE [dbo].[IPBlock]
GO

CREATE TABLE [dbo].[IPBlock] (
  [BlockedIP] nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL,
  [BlockDate] datetime  NOT NULL,
  [BlockReason] nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT '' NOT NULL
)
GO

ALTER TABLE [dbo].[IPBlock] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Table structure for RegionServers
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[RegionServers]') AND type IN ('U'))
	DROP TABLE [dbo].[RegionServers]
GO

CREATE TABLE [dbo].[RegionServers] (
  [Region] tinyint  NOT NULL,
  [ListenerIP] varchar(16) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL,
  [Port] int  NOT NULL,
  [WorkThreadCount] int  NOT NULL
)
GO

ALTER TABLE [dbo].[RegionServers] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Table structure for Versions
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[Versions]') AND type IN ('U'))
	DROP TABLE [dbo].[Versions]
GO

CREATE TABLE [dbo].[Versions] (
  [ClientDate] datetime  NOT NULL,
  [ClientHash] varchar(32) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL
)
GO

ALTER TABLE [dbo].[Versions] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Table structure for WorldList
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[WorldList]') AND type IN ('U'))
	DROP TABLE [dbo].[WorldList]
GO

CREATE TABLE [dbo].[WorldList] (
  [ID] int  NOT NULL,
  [WorldName] varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL,
  [TestServer] bit DEFAULT ((0)) NOT NULL,
  [AllowIP] varchar(16) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL,
  [Region] tinyint DEFAULT ((0)) NOT NULL
)
GO

ALTER TABLE [dbo].[WorldList] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Procedure structure for Account_Insert
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[Account_Insert]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[Account_Insert]
GO

CREATE PROCEDURE [dbo].[Account_Insert]
	
	@pName NVARCHAR(16),
	@pEMail NVARCHAR(255),
	@pPassword NVARCHAR(32),
	
	@pDate DATETIME,
	@pUserIP NVARCHAR(15),
	
	@pIsActivated BIT = 0,
	@pIsOnline BIT = 0,
	@pBanDate DATETIME,
  @pBanTime bigint,
	@pRoleID TINYINT = 0,
	
	
	@pID INT OUTPUT
	
AS
BEGIN
	
	IF EXISTS (SELECT ID FROM Accounts WHERE Name = @pName)
	BEGIN
		SELECT -1;
		RETURN;
	END
	


	
	
	
	BEGIN TRAN
	
		DECLARE @pTries INT = 0;
		SET @pID = CAST(CRYPT_GEN_RANDOM(4) AS INT);

		WHILE EXISTS (SELECT ID FROM Accounts WHERE ID = @pID)
		BEGIN
			SET @pID = CAST(CRYPT_GEN_RANDOM(4) AS INT);
			
			SET @pTries = (@pTries + 1);
			IF (@pTries >= 25)
			BEGIN
				ROLLBACK TRAN;
				SELECT -2;
				RETURN;
			END
		END
		
		
		
		
		INSERT INTO Accounts
													(ID,   Name,   EMail,   Password,   CreationDate, CreationIP, IsActivated, IsOnline,   LastLogin, LastIP,   RoleID,    BanDate,  BanTime)
								VALUES
													(@pID, @pName, @pEMail, @pPassword, @pDate,       @pUserIP,   @pIsActivated, @pIsOnline, @pDate,    @pUserIP, @pRoleID,@pBanDate,@pBanTime);
		
		
		
		IF (@@ROWCOUNT = 0)
		BEGIN
			ROLLBACK TRAN;
			SELECT -3;
			RETURN;
		END
		
		
		SELECT 0;
		
		
	
	COMMIT TRAN
	
	
	
	
END
GO


-- ----------------------------
-- Procedure structure for Account_Update
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[Account_Update]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[Account_Update]
GO

CREATE PROCEDURE [dbo].[Account_Update]
	@pID INT,
	@pPassword nvarchar(32),
	@pEMail NVARCHAR(255),
  @pBanDate DATETIME,
	@pBanTime bigint,
  @pBanReason Text,
	@pIP NVARCHAR(15),
	@pIsActivated BIT,
	@pRoleID TINYINT

AS
BEGIN

IF NOT EXISTS(SELECT ID FROM Accounts WHERE ID = @pID)
BEGIN
		SELECT -1;
		RETURN;
END

--Check is Ip Block
IF EXISTS(SELECT BlockedIP FROM IPBlock WHERE BlockedIP = @pIP)
BEGIN
DELETE FROM IPBlock WHERE BlockedIP = @pIP;

END

BEGIN TRAN


UPDATE Accounts SET 


EMail		 		= @pEMail,
Password 		= @pPassword,
BanDate  		= @pBanDate,
BanTime  		= @pBanTime,
BanReason   = @pBanReason,
RoleID	 		= @pRoleID,
IsActivated = @pIsActivated

WHERE ID = @pID;
	

IF (@@ROWCOUNT = 0)
		BEGIN
			ROLLBACK TRAN;
			SELECT -5;
			RETURN;
		END

SELECT 0;
COMMIT TRAN
END
GO


-- ----------------------------
-- Procedure structure for Account_UpdateState
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[Account_UpdateState]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[Account_UpdateState]
GO

CREATE PROCEDURE [dbo].[Account_UpdateState]
	
	@pID INT,
	
	
	@pIsOnline BIT,
	@pLastLogin DATETIME,
	@pLastIP NVARCHAR(15)

AS
BEGIN
	
	
	UPDATE Accounts SET
												IsOnline				= @pIsOnline,
												LastLogin				= @pLastLogin,
												LastIP					= @pLastIP
								  WHERE
												ID							= @pID;
	
	
END
GO


-- ----------------------------
-- Procedure structure for IPBlock_Insert
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[IPBlock_Insert]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[IPBlock_Insert]
GO

CREATE PROCEDURE [dbo].[IPBlock_Insert]
	
	@pBlockedIP NVARCHAR(15),
	@pBlockDate DATETIME,
	@pBlockReason NVARCHAR(MAX)
	
AS
BEGIN
	
	
	IF EXISTS (SELECT BlockedIP FROM IPBlock WHERE BlockedIP = @pBlockedIP)
	BEGIN
		SELECT -1;
		RETURN;
	END
	
	
	BEGIN TRAN
		
		INSERT INTO IPBlock
												(BlockedIP,   BlockDate,   BlockReason)
								VALUES
												(@pBlockedIP, @pBlockDate, @pBlockReason);
		
		
		IF (@@ROWCOUNT = 0)
		BEGIN
			SELECT -2;
			RETURN;
		END
		
		
		SELECT 0;
		
		
	COMMIT TRAN
	
	
END
GO


-- ----------------------------
-- Procedure structure for IPBlock_Remove
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[IPBlock_Remove]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[IPBlock_Remove]
GO

CREATE PROCEDURE [dbo].[IPBlock_Remove]
  @pBlockedIP nvarchar(15)
AS
BEGIN TRAN
		
		DELETE FROM IPBlock WHERE BlockedIP = @pBlockedIP;
		
		IF (@@ROWCOUNT = 0)
		BEGIN
			SELECT -2;
			RETURN;
		END
		
		
		SELECT 0;
		
		
	COMMIT TRAN
GO


-- ----------------------------
-- Indexes structure for table Accounts
-- ----------------------------
CREATE UNIQUE NONCLUSTERED INDEX [INX_Accounts]
ON [dbo].[Accounts] (
  [ID] ASC,
  [Name] ASC,
  [EMail] ASC,
  [CreationIP] ASC
)
GO


-- ----------------------------
-- Uniques structure for table Accounts
-- ----------------------------
ALTER TABLE [dbo].[Accounts] ADD CONSTRAINT [UQ__Accounts__1F959D71164452B1] UNIQUE NONCLUSTERED ([ID] ASC, [Name] ASC, [EMail] ASC, [CreationIP] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table Accounts
-- ----------------------------
ALTER TABLE [dbo].[Accounts] ADD CONSTRAINT [PK__Accounts__3214EC277F60ED59] PRIMARY KEY CLUSTERED ([ID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Indexes structure for table IPBlock
-- ----------------------------
CREATE UNIQUE NONCLUSTERED INDEX [INX_IPBlock]
ON [dbo].[IPBlock] (
  [BlockedIP] ASC
)
GO


-- ----------------------------
-- Uniques structure for table IPBlock
-- ----------------------------
ALTER TABLE [dbo].[IPBlock] ADD CONSTRAINT [UQ__IPBlock__CB61CA471BFD2C07] UNIQUE NONCLUSTERED ([BlockedIP] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table IPBlock
-- ----------------------------
ALTER TABLE [dbo].[IPBlock] ADD CONSTRAINT [PK__IPBlock__CB61CA461920BF5C] PRIMARY KEY CLUSTERED ([BlockedIP])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table RegionServers
-- ----------------------------
ALTER TABLE [dbo].[RegionServers] ADD CONSTRAINT [PK__RegionSe__398A872E1ED998B2] PRIMARY KEY CLUSTERED ([Region], [Port])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table Versions
-- ----------------------------
ALTER TABLE [dbo].[Versions] ADD CONSTRAINT [PK__Versions__B733B20321B6055D] PRIMARY KEY CLUSTERED ([ClientDate], [ClientHash])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO

