-- SCRIPTS IF THERE ARE NO DATA IN ClientPortalRecentMatter
IF NOT EXISTS (SELECT TOP 1 1 FROM [dbo].[ClientPortalRecentMatter])
BEGIN
-- STEP 1: INCREASE COLUMN LENGTH 
IF NOT EXISTS 
		(SELECT TOP 1 1
		   FROM SYS.COLUMNS
		  WHERE OBJECT_NAME(OBJECT_ID) = 'ClientPortalRecentMatter' 
		    AND [NAME] = '[MatterCode]')
BEGIN
    ALTER TABLE
		[dbo].[ClientPortalRecentMatter] 
    ALTER COLUMN 
		[MatterCode] VARCHAR(20) NOT NULL;
END
END
GO

-- SCRIPTS IF THERE ARE DATA IN ClientPortalRecentMatter
IF  EXISTS (SELECT TOP 1 1 FROM [dbo].[ClientPortalRecentMatter])
BEGIN
-- DROP BACKUP TABLE DATA IF PRESENT
IF OBJECT_ID(N'[dbo].[ClientPortalRecentMatterBackup]', N'U') IS NOT NULL
BEGIN
	DROP TABLE
		[dbo].[ClientPortalRecentMatterBackup]
END

-- TAKE BACKUP OF EXISTING DATA
	SELECT *
	  INTO [dbo].[ClientPortalRecentMatterBackup]
	  FROM [dbo].[ClientPortalRecentMatter]


-- DROP EXISTING TABLE
DROP TABLE [dbo].[ClientPortalRecentMatter]


-- RECREATING TABLE WITH CORRECT TABLE  STRUCUTRE
IF OBJECT_ID(N'[dbo].[ClientPortalRecentMatter]',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].[ClientPortalRecentMatter](		
		[RecentMatterId]	[INT] IDENTITY(1,1)  NOT NULL,
		[ClientLoginId]		[BIGINT]			 NOT NULL,
		[MatterCode]		[VARCHAR](20)		 NOT NULL,
		[LastAccessDate]	[DATETIME]			 NOT NULL,
		CONSTRAINT [PK_ClientPortalRecentMatter] PRIMARY KEY ([RecentMatterId])
	)
END


-- INSERT DATA FROM BAKUP TABLE WITH IDENTITY INSERT ON: STARTS
SET IDENTITY_INSERT [dbo].[ClientPortalRecentMatter] ON


INSERT
  INTO [dbo].[ClientPortalRecentMatter]
      ([RecentMatterId],
       [ClientLoginId],
       [MatterCode],
       [LastAccessDate])
SELECT [RecentMatterId],
       [ClientLoginId],
       [MatterCode],
       [LastAccessDate]
  FROM [dbo].[ClientPortalRecentMatterBackup]


DECLARE @MAX_ClientPortalRecentMatter BIGINT
SELECT @MAX_ClientPortalRecentMatter = MAX([ClientLoginId]) FROM [dbo].[ClientPortalRecentMatter]
SET @MAX_ClientPortalRecentMatter = ISNULL(@MAX_ClientPortalRecentMatter, 0) + 1

DBCC CHECKIDENT('ClientPortalRecentMatter', RESEED, @MAX_ClientPortalRecentMatter) WITH NO_INFOMSGS


SET IDENTITY_INSERT [dbo].[ClientPortalRecentMatter] OFF

-- INSERT DATA FROM BAKUP TABLE WITH IDENTITY INSERT ON: END

-- DROP BACKUP TABLE
IF OBJECT_ID(N'[dbo].[ClientPortalRecentMatterBackup]', N'U') IS NOT NULL
BEGIN
	DROP TABLE
		[dbo].[ClientPortalRecentMatterBackup]
END
END
GO