 ALTER TABLE cm_article
 	DROP COLUMN modifiedby
 go
-
 -- We don't drop the inserttimestamp and updatetimestamp columns because they have defaults and are 
 -- very hard to drop because of their generated names
+
+-- Drop old article categories
+ALTER TABLE cm_articlecategory
+	DROP CONSTRAINT FK_articlecategory_site_siteid
+go
+
+DROP TABLE cm_articlecategory
+go
 
 /*
  * Version

WHERE a.articleid = ci.articleid
go

-- we can now get rid of the temporary column and clean the old cm_article, cm_ariclecategory and cm_articlecomment tables
ALTER TABLE cuyahoga_contentitem
	DROP COLUMN articleid
go

﻿-- Migrate Articles to ContentItem
-- Move categories to cuyahoga_category table
-- Create a temp table and copy the categories that need to be moved to that table. We need this for proper position generation.
CREATE TABLE #tempcategory(id int identity(1,1), categoryname nvarchar(100))

DECLARE @currentMaxPosition int
SELECT @currentMaxPosition = MAX([position]) FROM cuyahoga_category WHERE parentcategoryid IS NULL 

INSERT INTO #tempcategory(categoryname)
SELECT ac.title
FROM cm_articlecategory ac
WHERE NOT EXISTS (SELECT 1 FROM cuyahoga_category cc WHERE cc.categoryname = ac.title)

INSERT INTO cuyahoga_category(siteid, [position], categoryname, description, path)
SELECT siteid, @currentMaxPosition + tc.id, title, summary, '.' + right('0000' + convert(nvarchar,(@currentMaxPosition + tc.id)), 4)
FROM cm_articlecategory ac
	INNER JOIN #tempcategory tc ON tc.categoryname = ac.title

DROP TABLE #tempcategory
go

-- Add a temporary column to contentitem to store the old articleid's
ALTER TABLE cuyahoga_contentitem
	ADD articleid int NULL
go

-- copy data to contentitem
INSERT INTO cuyahoga_contentitem(articleid, globalid, workflowstatus, title, description, syndicate, version, createdat, modifiedat, createdby, modifiedby, publishedat, publisheduntil, sectionid)
SELECT articleid, newid(), 0, title, summary, syndicate, 1, inserttimestamp, updatetimestamp, createdby, modifiedby, dateonline, dateoffline, sectionid
FROM cm_article
go

