DROP TABLE IF EXISTS `grade_grades_history`;
CREATE TABLE `grade_grades_history` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `action` bigint(10) NOT NULL DEFAULT 0,
  `oldid` bigint(10) NOT NULL,
  `source` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `timemodified` bigint(10) DEFAULT NULL,
  `loggeduser` bigint(10) DEFAULT NULL,
  `itemid` bigint(10) NOT NULL,
  `userid` bigint(10) NOT NULL,
  `rawgrade` decimal(10,5) DEFAULT NULL,
  `rawgrademax` decimal(10,5) NOT NULL DEFAULT 100.00000,
  `rawgrademin` decimal(10,5) NOT NULL DEFAULT 0.00000,
  `rawscaleid` bigint(10) DEFAULT NULL,
  `usermodified` bigint(10) DEFAULT NULL,
  `finalgrade` decimal(10,5) DEFAULT NULL,
  `hidden` bigint(10) NOT NULL DEFAULT 0,
  `locked` bigint(10) NOT NULL DEFAULT 0,
  `locktime` bigint(10) NOT NULL DEFAULT 0,
  `exported` bigint(10) NOT NULL DEFAULT 0,
  `overridden` bigint(10) NOT NULL DEFAULT 0,
  `excluded` bigint(10) NOT NULL DEFAULT 0,
  `feedback` longtext COLLATE utf8mb4_bin DEFAULT NULL,
  `feedbackformat` bigint(10) NOT NULL DEFAULT 0,
  `information` longtext COLLATE utf8mb4_bin DEFAULT NULL,
  `informationformat` bigint(10) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `t_gradgradhist_act_ix` (`action`),
  KEY `t_gradgradhist_tim_ix` (`timemodified`),
  KEY `t_gradgradhist_useitetim_ix` (`userid`,`itemid`,`timemodified`),
  KEY `t_gradgradhist_old_ix` (`oldid`),
  KEY `t_gradgradhist_ite_ix` (`itemid`),
  KEY `t_gradgradhist_use_ix` (`userid`),
  KEY `t_gradgradhist_raw_ix` (`rawscaleid`),
  KEY `t_gradgradhist_use2_ix` (`usermodified`),
  KEY `t_gradgradhist_log_ix` (`loggeduser`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED COMMENT='History table';

INSERT INTO `grade_grades_history` (`id`, `action`, `oldid`, `source`, `timemodified`, `loggeduser`, `itemid`, `userid`, `rawgrade`, `rawgrademax`, `rawgrademin`, `rawscaleid`, `usermodified`, `finalgrade`, `hidden`, `locked`, `locktime`, `exported`, `overridden`, `excluded`, `feedback`, `feedbackformat`, `information`, `informationformat`) VALUES
(330000,	1,	325000,	'mod/assign',	1621333499,	131000,	324001,	131000,	50.00000,	100.00000,	0.00000,	NULL,	131000,	50.00000,	0,	0,	0,	0,	0,	0,	NULL,	0,	NULL,	0),
(330001,	1,	325001,	'system',	1621333499,	131000,	324000,	131000,	NULL,	100.00000,	0.00000,	NULL,	NULL,	NULL,	0,	0,	0,	0,	0,	0,	NULL,	0,	NULL,	0),
(330002,	2,	325001,	'aggregation',	1621333499,	131000,	324000,	131000,	NULL,	100.00000,	0.00000,	NULL,	NULL,	50.00000,	0,	0,	0,	0,	0,	0,	NULL,	0,	NULL,	0),
(330003,	3,	325000,	'userdelete',	1621333499,	131000,	324001,	131000,	50.00000,	100.00000,	0.00000,	NULL,	131000,	50.00000,	0,	0,	0,	0,	0,	0,	NULL,	0,	NULL,	0),
(330004,	3,	325001,	'userdelete',	1621333499,	131000,	324000,	131000,	NULL,	100.00000,	0.00000,	NULL,	NULL,	50.00000,	0,	0,	0,	0,	0,	0,	NULL,	0,	NULL,	0);

DROP TABLE IF EXISTS `grade_items`;
CREATE TABLE `grade_items` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `courseid` bigint(10) DEFAULT NULL,
  `categoryid` bigint(10) DEFAULT NULL,
  `itemname` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `itemtype` varchar(30) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `itemmodule` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `iteminstance` bigint(10) DEFAULT NULL,
  `itemnumber` bigint(10) DEFAULT NULL,
  `iteminfo` longtext COLLATE utf8mb4_bin DEFAULT NULL,
  `idnumber` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `calculation` longtext COLLATE utf8mb4_bin DEFAULT NULL,
  `gradetype` smallint(4) NOT NULL DEFAULT 1,
  `grademax` decimal(10,5) NOT NULL DEFAULT 100.00000,
  `grademin` decimal(10,5) NOT NULL DEFAULT 0.00000,
  `scaleid` bigint(10) DEFAULT NULL,
  `outcomeid` bigint(10) DEFAULT NULL,
  `gradepass` decimal(10,5) NOT NULL DEFAULT 0.00000,
  `multfactor` decimal(10,5) NOT NULL DEFAULT 1.00000,
  `plusfactor` decimal(10,5) NOT NULL DEFAULT 0.00000,
  `aggregationcoef` decimal(10,5) NOT NULL DEFAULT 0.00000,
  `aggregationcoef2` decimal(10,5) NOT NULL DEFAULT 0.00000,
  `sortorder` bigint(10) NOT NULL DEFAULT 0,
  `display` bigint(10) NOT NULL DEFAULT 0,
  `decimals` tinyint(1) DEFAULT NULL,
  `hidden` bigint(10) NOT NULL DEFAULT 0,
  `locked` bigint(10) NOT NULL DEFAULT 0,
  `locktime` bigint(10) NOT NULL DEFAULT 0,
  `needsupdate` bigint(10) NOT NULL DEFAULT 0,
  `weightoverride` tinyint(1) NOT NULL DEFAULT 0,
  `timecreated` bigint(10) DEFAULT NULL,
  `timemodified` bigint(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `t_graditem_locloc_ix` (`locked`,`locktime`),
  KEY `t_graditem_itenee_ix` (`itemtype`,`needsupdate`),
  KEY `t_graditem_gra_ix` (`gradetype`),
  KEY `t_graditem_idncou_ix` (`idnumber`,`courseid`),
  KEY `t_graditem_cou_ix` (`courseid`),
  KEY `t_graditem_cat_ix` (`categoryid`),
  KEY `t_graditem_sca_ix` (`scaleid`),
  KEY `t_graditem_out_ix` (`outcomeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED COMMENT='This table keeps information about gradeable items (ie colum';

INSERT INTO `grade_items` (`id`, `courseid`, `categoryid`, `itemname`, `itemtype`, `itemmodule`, `iteminstance`, `itemnumber`, `iteminfo`, `idnumber`, `calculation`, `gradetype`, `grademax`, `grademin`, `scaleid`, `outcomeid`, `gradepass`, `multfactor`, `plusfactor`, `aggregationcoef`, `aggregationcoef2`, `sortorder`, `display`, `decimals`, `hidden`, `locked`, `locktime`, `needsupdate`, `weightoverride`, `timecreated`, `timemodified`) VALUES
(324000,	128000,	NULL,	NULL,	'course',	NULL,	323000,	NULL,	NULL,	NULL,	NULL,	1,	100.00000,	0.00000,	NULL,	NULL,	0.00000,	1.00000,	0.00000,	0.00000,	0.00000,	1,	0,	NULL,	0,	0,	0,	0,	0,	1621333436,	1621333436),
(324001,	128000,	323000,	'Assignment 1',	'mod',	'assign',	485000,	0,	NULL,	'',	NULL,	1,	100.00000,	0.00000,	NULL,	NULL,	0.00000,	1.00000,	0.00000,	0.00000,	1.00000,	2,	0,	NULL,	0,	0,	0,	0,	0,	1621333436,	1621333436);



SELECT h.id, gi.itemtype, gi.itemmodule, h.userid, h.rawgrade
FROM grade_grades_history h
         JOIN (SELECT itemid, MAX(id) AS id
               FROM grade_grades_history
               WHERE userid = 131000
               GROUP BY itemid) maxquery ON h.id = maxquery.id AND h.itemid = maxquery.itemid
         JOIN grade_items gi ON gi.id = h.itemid
WHERE gi.courseid = 128000;

