-- This is the main caller for each script
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO
 
--replace the "scdb" with your DB Name 
USE [scdb]
GO

BEGIN TRY
CREATE TABLE #tempChangeNotify (
	id              BIGINT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
	mtable          BIGINT, 
	mid             BIGINT,
	type            BIGINT
	);
PRINT 'preparation: CREATE TABLE #tempChangeNotify'
END TRY
BEGIN CATCH
PRINT 'WARNING: TABLE #tempChangeNotify already exists'
END CATCH
GO

DECLARE @CurrentVersion INT;
DECLARE @LatestVersion INT;
DECLARE @InitVersion INT;
DECLARE @VersionComment VARCHAR(256);
DECLARE @error INT;
DECLARE @SQLString VARCHAR(MAX);

--set by manual
SET @InitVersion = 49;
SET @LatestVersion = 61;
SET @VersionComment = 'db schem version 20150923';
SET @error = 0;

--create STConfig first for db version check
IF OBJECT_ID (N'STConfig', N'U') IS NULL
BEGIN
	SET @error = 1;                                      
	RAISERROR('ERROR: Table[STConfig] not found!', 15, -1);
END
ELSE
BEGIN
	--/****************************************************************************************
	--upgrade tables if any 
	--*****************************************************************************************/
	
	--default version set as 65535. an impossible value indicating exception to abort
	SELECT @CurrentVersion = COALESCE(value, 65535) FROM STConfig WHERE name = 0;
	PRINT 'Current DB version:		' + CONVERT(char(3),@CurrentVersion);

	--upgrade only allowed within [@InitVersion, @LatestVersion)
	IF @CurrentVersion < @LatestVersion AND @CurrentVersion >= @InitVersion 
	BEGIN 
		DECLARE @Counter int;
		SET @Counter = @CurrentVersion;

		PRINT 'Will update to version: ' + CONVERT(char(3),@LatestVersion);

		WHILE @Counter < @LatestVersion
		BEGIN 
			--IF @Counter = N 
			--	GOTO  Version_Upgrade_From_N;
			IF @Counter = 49 
				GOTO  Version_Upgrade_From_49;

			IF @Counter = 60 
				GOTO  Version_Upgrade_From_60;
				
			--upgrade the following version 
			Continue_Upgrade:
			SET @Counter = @Counter + 1
		END
	END
	ELSE
	BEGIN
		SET @error = 1;
		IF @CurrentVersion > @LatestVersion
		BEGIN
			PRINT 'ERROR: cannot upgrade DB newer than ' + CONVERT(char(3),@LatestVersion);
		END
		ELSE IF @CurrentVersion < @InitVersion 
		BEGIN
			PRINT 'ERROR: cannot upgrade DB older than ' + CONVERT(char(3),@InitVersion);
		END
		ELSE
		BEGIN
			PRINT('INFO: same db version, skip upgrade!');
		END
	END
END

GOTO End_all;

--/****************************************************************************************
-- table changes from version N to N+1
--
--Version_Upgrade_From_N:
--	--to add sql statements here for upgrading
--	GOTO Continue_Upgrade;
--*****************************************************************************************/
Version_Upgrade_From_49:
PRINT 'Version_Upgrade_From_49_BEGIN'

PRINT '    - CREATE TABLE STGroupADOU'
Set @SQLString = 
	'CREATE TABLE STGroupADOU (
    gid             BIGINT NOT NULL,
    adouid          BIGINT NOT NULL,
    PRIMARY KEY(gid, adouid),
    FOREIGN KEY(adouid) REFERENCES STADGroupOU(id) ON DELETE CASCADE,
    FOREIGN KEY(gid) REFERENCES STGroup(id) ON DELETE CASCADE 
    );'
EXEC (@SQLString);

PRINT '    - CREATE VIEW STViewGroupADOU'
Set @SQLString = 
	'CREATE VIEW STViewGroupADOU AS 
    SELECT gpadou.gid AS gid, g.groupname AS gname, adou.name AS adouname, adou.id AS adouid FROM STGroupADOU AS gpadou LEFT JOIN STGroup AS g ON g.id=gpadou.gid LEFT JOIN STADGroupOU AS adou ON adou.id=gpadou.adouid;'
EXEC (@SQLString);

PRINT '    - CREATE VIEW STViewGroupUserOverall'
Set @SQLString = 
	'CREATE VIEW STViewGroupUserOverall AS 
    SELECT gpu.gid AS gid, g.groupname AS gname, gpu.uid AS uid, u.loginname AS uname FROM STGroupUser AS gpu LEFT JOIN STGroup AS g ON g.id=gpu.gid LEFT JOIN STUser AS u ON u.id=gpu.uid
    UNION 
    SELECT g.id AS gid, g.groupname AS gname, u.id AS uid, u.loginname AS uname FROM STUser AS u CROSS JOIN STGroup AS g WHERE u.admintype=1
    UNION 
    SELECT gpadou.gid AS gid, g.groupname AS gname, u.id, u.loginname AS uname FROM STGroupADOU AS gpadou LEFT JOIN STGroup AS g ON g.id=gpadou.gid LEFT JOIN STUser AS u ON u.adouid=gpadou.adouid;'
EXEC (@SQLString);

PRINT '    - ALTER TABLE STGroup ADD allowwol'
Set @SQLString = 
	'ALTER TABLE STGroup
	ADD allowwol BIGINT DEFAULT 0 NOT NULL;'
EXEC (@SQLString);

PRINT '    - ALTER TABLE STLogHistory ALTER additional'
Set @SQLString = 
	'ALTER TABLE STLogHistory 
	ALTER COLUMN additional NVARCHAR(MAX);'
EXEC (@SQLString);

PRINT 'Version_Upgrade_From_49_END'
GOTO Continue_Upgrade;

Version_Upgrade_From_60:
PRINT 'Version_Upgrade_From_60_BEGIN'

PRINT '    - CREATE TABLE STKBSetting'
Set @SQLString = 
	'CREATE TABLE STKBSetting (
    id              BIGINT PRIMARY KEY NOT NULL,
    layout          BIGINT NOT NULL,
    type            BIGINT NOT NULL,
    subtype         BIGINT NOT NULL,
    functionkey     BIGINT NOT NULL,
    name            NVARCHAR(256) NOT NULL UNIQUE
    );
	INSERT INTO STKBSetting VALUES(0, 1033, 4, 0, 12, ''English''), (1, 1041, 7, 2, 12, ''Japanese'');'
EXEC (@SQLString);

PRINT '    - ALTER TABLE STRdpAccount ADD kbsettingid'
Set @SQLString = 
	'ALTER TABLE STRdpAccount
	ADD kbsettingid BIGINT;
	ALTER TABLE STRdpAccount
	ADD FOREIGN KEY(kbsettingid) REFERENCES STKBSetting(id) ON DELETE SET NULL;'
EXEC (@SQLString);

PRINT '    - UPDATE STRdpAccount SET kbsettingid DEFAULT 0'
Set @SQLString = 
	'UPDATE STRdpAccount SET kbsettingid=0 WHERE kbsettingid IS NULL;'
EXEC (@SQLString);

PRINT 'Version_Upgrade_From_60_END'
GOTO Continue_Upgrade;

End_all:
IF @error = 0			
MERGE STConfig AS target
	USING (VALUES('0')) AS source(name)
	ON target.name = source.name
	WHEN MATCHED THEN
		UPDATE SET value = @LatestVersion, comm = @VersionComment
	WHEN NOT MATCHED THEN
		INSERT (name, value, comm) VALUES('0', @LatestVersion, @VersionComment);

SET NOCOUNT OFF
GO