
-- rename NB_Comment, NB_Like, NB_Mentio tables and make them common

DECLARE @object_name NVARCHAR(MAX), 
		@object_newname NVARCHAR(MAX);

DECLARE object_cursor CURSOR FOR 
SELECT name AS ObjectName
FROM sys.objects
WHERE name LIKE '%NB_Comment%' or name LIKE '%NB_Like%' or name LIKE '%NB_Mentio%'
UNION ALL
SELECT TABLE_NAME + '.' + COLUMN_NAME AS ObjectName
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%NB_Comment%' or COLUMN_NAME LIKE '%NB_Like%' or COLUMN_NAME LIKE '%NB_Mentio%'

OPEN object_cursor

FETCH NEXT FROM object_cursor 
INTO @object_name

WHILE @@FETCH_STATUS = 0
BEGIN

	if(@object_name LIKE '%NB_Comment%')
		SET @object_newname = REPLACE(PARSENAME(@object_name, 1), 'NB_Comment', 'Comment');
	if(@object_name LIKE '%NB_Like%')
		SET @object_newname = REPLACE(PARSENAME(@object_name, 1), 'NB_Like', 'Like');
	if(@object_name LIKE '%NB_Mentio%')
		SET @object_newname = REPLACE(PARSENAME(@object_name, 1), 'NB_Mentio', 'Mentio');

	EXEC SP_RENAME @object_name, @object_newname;

	FETCH NEXT FROM object_cursor 
	INTO @object_name
END 

CLOSE object_cursor;
DEALLOCATE object_cursor;

-- update comment table

ALTER TABLE [dbo].[Comment]
ADD [TargetID] nvarchar(250) not null DEFAULT(0)

ALTER TABLE [dbo].[Comment]
ADD [TargetType] int not null DEFAULT(1)

UPDATE [dbo].[Comment]
SET [TargetID] = [ItemID]

ALTER TABLE [dbo].[Comment] 
DROP CONSTRAINT [FK_Comment_NB_Item];

DROP INDEX IX_FK_NB_Comment_NB_Item 
    ON [dbo].[Comment];

ALTER TABLE [dbo].[Comment] 
DROP COLUMN [ItemID]

-- update mentio table

ALTER TABLE [dbo].[Mentio]
ADD [TargetID] nvarchar(250) not null DEFAULT(0)

ALTER TABLE [dbo].[Mentio]
ADD [TargetType] int not null DEFAULT(1)

UPDATE [dbo].[Mentio]
SET [TargetID] = [ItemID]
where [ItemID] is not null;

ALTER TABLE [dbo].[Mentio] 
DROP CONSTRAINT [FK_Mentio_NB_Item];

ALTER TABLE [dbo].[Mentio] 
DROP COLUMN [ItemID]

ALTER TABLE [dbo].[Mentio] 
DROP CONSTRAINT [FK_Mentio_NB_Comment];

ALTER TABLE [dbo].[Mentio] 
DROP COLUMN [CommentID]

-- update like table

ALTER TABLE [dbo].[Like]
ADD [TargetID] nvarchar(250) not null DEFAULT(0)

ALTER TABLE [dbo].[Like]
ADD [TargetType] int not null DEFAULT(1)

UPDATE [dbo].[Like]
SET [TargetID] = [ItemID]
where [ItemID] is not null;

UPDATE [dbo].[Like]
SET [TargetID] = [CommentID], [TargetType] = 2
where [CommentID] is not null;

ALTER TABLE [dbo].[Like] 
DROP CONSTRAINT [FK_Like_NB_Item];

DROP INDEX IX_FK_NB_Like_NB_Item
    ON [dbo].[Like];

ALTER TABLE [dbo].[Like] 
DROP COLUMN [ItemID]

ALTER TABLE [dbo].[Like] 
DROP CONSTRAINT [FK_Like_NB_Comment];

DROP INDEX IX_FK_NB_Like_NB_Comment
    ON [dbo].[Like];

ALTER TABLE [dbo].[Like] 
DROP COLUMN [CommentID]

-- add views for NB
--GO
--CREATE VIEW [dbo].[NB_Comment]
--	AS SELECT C.CommentID, C.AuthorID, C.Content, C.DatePosted, C.DateModified, C.Preview, C.[Status], C.Picture, C.PreviewMetadata, I.ItemID FROM Comment as C
--	left join NB_Item as I on Cast(I.ItemID as varchar(20)) = C.TargetID
--	where C.TargetType = 1

--GO
--CREATE VIEW [dbo].[NB_Like]
--	AS SELECT L.LikeID, L.PrincipalID, L.[Timestamp], C.CommentID, I.ItemID FROM [dbo].[Like] as L
--	left join [dbo].[NB_Item] as I on Cast(I.ItemID as varchar(20)) = L.TargetID and L.TargetType = 1
--	left join [dbo].[NB_Comment] as C on Cast(C.CommentID as varchar(20)) = L.TargetID and L.TargetType = 2
--	where L.TargetType = 1 or L.TargetType = 2 

--GO
--CREATE VIEW [dbo].[NB_Mentio]
--	AS SELECT M.MentioID, M.SenderID, M.ReceiverID, M.[Timestamp], C.CommentID, I.ItemID FROM [dbo].[Mentio] as M
--	left join [dbo].[NB_Item] as I on Cast(I.ItemID as varchar(20)) = M.TargetID and M.TargetType = 1
--	left join [dbo].[NB_Comment] as C on Cast(C.CommentID as varchar(20)) = M.TargetID and M.TargetType = 2
--	where M.TargetType = 1 or M.TargetType = 2 