-- Some game objects have UUIDs, like players and worlds.
-- Based on my research I store them as BINARY(16).
-- I would like to use MySQL's UUID_TO_BIN functions but
-- MariaDB doesn't support that. So use HEX/UNHEX and
-- do the rest java-side.

-- "actions" are descriptive words that describe an event I log. "block-break", "item-drop", etc.
-- actions are stored here only once so we can FK them
-- I expect < 100 total
CREATE TABLE `prism_actions` (
  `action_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `action` varchar(25) NOT NULL,
  PRIMARY KEY (`action_id`),
  UNIQUE KEY `action` (`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- this is the big guy
-- "activities" are the actual log of game events. a game event is when a player does
-- anything the server owner wants to track. we do this so server owners can track abuse.
-- 90% of this table is foreign keys
-- NOTE: I have not yet added any indexes for queries. Server OPs can search this data in many ways
-- so it's difficult to make good indexes
-- depending on how our users configure logging and purges this could store tens of millions of rows
CREATE TABLE `prism_activities` (
  `activity_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` int(10) unsigned NOT NULL,
  `world_id` tinyint(3) unsigned NOT NULL,
  `x` int(11) NOT NULL,
  `y` int(11) NOT NULL,
  `z` int(11) NOT NULL,
  `action_id` tinyint(3) unsigned NOT NULL,
  `material_id` smallint(5) unsigned DEFAULT NULL,
  `old_material_id` smallint(5) unsigned DEFAULT NULL,
  `entity_type_id` smallint(5) unsigned DEFAULT NULL,
  `cause_id` int(10) unsigned NOT NULL,
  `descriptor` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`activity_id`),
  KEY `actionId_idx` (`action_id`),
  KEY `causeId_idx` (`cause_id`),
  KEY `entityTypeId_idx` (`entity_type_id`),
  KEY `materialId_idx` (`material_id`),
  KEY `oldMaterialId_idx` (`old_material_id`),
  KEY `worldId_idx` (`world_id`),
  CONSTRAINT `actionId` FOREIGN KEY (`action_id`) REFERENCES `prism_actions` (`action_id`),
  CONSTRAINT `causeId` FOREIGN KEY (`cause_id`) REFERENCES `prism_causes` (`cause_id`),
  CONSTRAINT `entityTypeId` FOREIGN KEY (`entity_type_id`) REFERENCES `prism_entity_types` (`entity_type_id`),
  CONSTRAINT `materialId` FOREIGN KEY (`material_id`) REFERENCES `prism_materials` (`material_id`),
  CONSTRAINT `oldMaterialId` FOREIGN KEY (`old_material_id`) REFERENCES `prism_materials` (`material_id`),
  CONSTRAINT `worldId` FOREIGN KEY (`world_id`) REFERENCES `prism_worlds` (`world_id`)
) ENGINE=InnoDB CHARSET=utf8mb4;

-- this table tracks optional extra/custom data for activities. the data is a serialized string.
-- we don't always know which format something will be serialized as so it might be JSON, but could be a custom json-like format.
CREATE TABLE `prism_activities_custom_data` (
  `extra_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `activity_id` int(10) unsigned NOT NULL,
  `version` smallint(6) DEFAULT NULL,
  `data` text,
  PRIMARY KEY (`extra_id`),
  KEY `activityId_idx` (`activity_id`),
  CONSTRAINT `activityId` FOREIGN KEY (`activity_id`) REFERENCES `prism_activities` (`activity_id`) ON DELETE CASCADE
) ENGINE=InnoDB CHARSET=utf8mb4;

-- "causes" are either a player or a named game object like a creature, explosion, etc.
-- this table will usually have < 1000 rows but for busy servers could be < 100,000
CREATE TABLE `prism_causes` (
  `cause_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cause` varchar(25) DEFAULT NULL,
  `player_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`cause_id`),
  UNIQUE KEY `cause` (`cause`),
  KEY `playerId_idx` (`player_id`),
  CONSTRAINT `playerId` FOREIGN KEY (`player_id`) REFERENCES `prism_players` (`player_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- "entities" is what Minecraft calls creatures. cows, zombies, etc.
-- entities are stored here only once so we can FK them
-- this table will probably have < 100,000 records
CREATE TABLE `prism_entity_types` (
  `entity_type_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `entity_type` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`entity_type_id`),
  UNIQUE KEY `entityType` (`entity_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- "materials" refer to items and blocks. "material" is a base type and "data" is extra for variations.
-- I'm debating storing these together as other databases can't do compound indexes as well
-- and I'm not sure there's a reason to keep them separate.
-- materials are stored here only once so we can FK them
-- this table will probably have < 100,000 records
CREATE TABLE `prism_materials` (
  `material_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `material` varchar(45) DEFAULT NULL,
  `data` varchar(155) DEFAULT NULL,
  PRIMARY KEY (`material_id`),
  UNIQUE KEY `materialData` (`material`,`data`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- simple table to store random plugin-used data like schema versions etc
-- this table will be tiny, likely having like 20 records
CREATE TABLE `prism_meta` (
  `meta_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `k` varchar(25) NOT NULL,
  `v` varchar(155) NOT NULL,
  PRIMARY KEY (`meta_id`),
  UNIQUE KEY `k` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- "players" refer to Minecraft players.
-- The game gives players a UUID. Maybe I should be using that as the PK too and drop a column?
-- this table will usually have < 1000 rows but for busy servers could be < 100,000
CREATE TABLE `prism_players` (
  `player_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `player` varchar(16) NOT NULL,
  `player_uuid` binary(16) NOT NULL,
  PRIMARY KEY (`player_id`),
  UNIQUE KEY `player_uuid` (`player_uuid`),
  KEY `player` (`player`,`player_uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- "worlds" refer to game worlds, dimensions that players play in.
-- the game gives worlds a UUID. Same PK comment here
-- this table will be tiny, usually having < 5 records but maybe < 20 lifetime
CREATE TABLE `prism_worlds` (
  `world_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `world` varchar(255) NOT NULL,
  `world_uuid` binary(16) NOT NULL,
  PRIMARY KEY (`world_id`),
  UNIQUE KEY `world_uuid` (`world_uuid`,`world`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;