-- The challenge is to make sure that the categoryid is correct - I have not looked to see how the data is getting mapped in the 1.8 to 1.9 upgrade.

-- add all of the grade_events to grade_items
INSERT INTO mdl_grade_items (courseid, categoryid, itemname, itemtype, iteminfo, idnumber, grademax, grademin, timecreated, timemodified) SELECT gi.courseid as courseid, gi.category as categoryid, ge.name as itemname, 'manual' as itemtype, ge.description as iteminfo, concat('GBE-',ge.id) as idnumber, ge.grade as grademax, 0 as grademin, ge.timemodified as timecreated, ge.timemodified as timemodified FROM mdl_grade_item as gi, mdl_grade_events as ge WHERE gi.courseid=ge.course AND gi.modid=0 AND gi.cminstance=ge.id;

-- replace the old categoryid with the new categoryid (where possible)
UPDATE mdl_grade_items as gi LEFT JOIN (mdl_grade_category as gc, mdl_grade_categories as gcs) ON (gi.categoryid=gc.id AND gi.courseid=gcs.courseid AND gc.name=gcs.fullname)
SET gi.categoryid = gcs.id, gi.idnumber=replace(gi.idnumber,'GBE','GBI')
WHERE gi.idnumber LIKE 'GBE%';

-- assign a generic categoryid for any thing that was not categorized (it is presumed that the grade event was not previously categorised)
UPDATE mdl_grade_items as gi 
LEFT JOIN (mdl_grade_categories as gcs) 
ON (gi.courseid=gcs.courseid AND gcs.parent IS NULL)
SET gi.categoryid = gcs.id
WHERE gi.idnumber LIKE 'GBI%' AND gi.categoryid IS NULL;

-- I still had 2 items that were not categorised and would suggest creating the records in mdl_grade_categories and re-running the query above.

INSERT INTO mdl_grade_categories (courseid, depth, fullname, timecreated, timemodified)
SELECT DISTINCT courseid, 1 as depth, 'Course grade category' as fullname, unix_timestamp() as timecreated, unix_timestamp() as timemodified
FROM mdl_grade_items
WHERE idnumber LIKE 'GBI%' AND categoryid IS NULL;

-- add the path - there may be a way to do this above but I do not know how to do it with the auto incrementing id field so I let it first insert the record and then I use the data
UPDATE mdl_grade_categories
SET path = concat('/',id,'/')
WHERE path IS NULL AND depth=1;

-- populate the previously NULL categoryids
UPDATE mdl_grade_items as gi 
LEFT JOIN (mdl_grade_categories as gcs) 
ON (gi.courseid=gcs.courseid AND gcs.parent IS NULL)
SET gi.categoryid = gcs.id
WHERE gi.idnumber LIKE 'GBI%' AND gi.categoryid IS NULL;


-- I have used the grade event (1.8) id field concatenated with the GBE- string as the id number to easily identify the items as GBP items. After assigning a category the idnumber becomes GBI- even if it is NULL. This will also facilitate linking the items (1.9) and assigning the grades from events_grades to grade_grades.

-- OK - now that hopefully all of the grade events have been turned into grade items and the new categories have been found or assigned let us go ahead and attempt to pull the grades over.

INSERT INTO mdl_grade_grades (itemid, userid, usermodified, finalgrade, timecreated, timemodified) 
SELECT gi.id as itemid, geg.userid as userid, geg.teacher as usermodified, geg.grade as finalgrade, geg.timemarked as timecreated, geg.timemarked as timemodified
FROM mdl_grade_events_grades as geg, mdl_grade_items as gi 
WHERE replace(gi.idnumber,'GBI-','')=geg.event;

