-- 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;


-- In this new plugin I'm adding procedures to shift some of my logic to the DB server.
-- Not all users have permission to CREATE PROCEDURE so those users will have to live
-- with java doing that work.


-- A big procedure to create activity data and create or get FK data
DELIMITER;
CREATE PROCEDURE `createActivity`(
  IN `timestamp` INT,
  IN `x` INT,
  IN `y` INT,
  IN `z` INT,
  IN `action` VARCHAR(25),
  IN `cause` VARCHAR(25),
  IN `player` VARCHAR(16),
  IN `playerUuid` VARCHAR(55),
  IN `entityType` VARCHAR(25),
  IN `material` VARCHAR(45),
  IN `blockData` VARCHAR(155),
  IN `oldMaterial` VARCHAR(45),
  IN `oldBlockData` VARCHAR(155),
  IN `world` VARCHAR(255),
  IN `worldUuid` VARCHAR(55),
  IN `customDataVersion` SMALLINT,
  IN `customData` TEXT,
  IN `descriptor` VARCHAR(255),
  OUT `activityId` INT
) BEGIN
SET
  @entityId = NULL;
SET
  @materialId = NULL;
SET
  @oldMaterialId = NULL;
SET
  @playerId = NULL;
CALL getOrCreateAction(`action`, @actionId);
IF `playerUuid` IS NOT NULL THEN CALL getOrCreatePlayer(
  `player`, `playerUuid`, @playerId
);
END IF;
CALL getOrCreateCause(`cause`, @playerId, @causeId);
CALL getOrCreateWorld(`world`, `worldUuid`, @worldId);
IF `entityType` IS NOT NULL THEN CALL getOrCreateEntityType(entityType, @entityId);
END IF;
IF `material` IS NOT NULL THEN CALL getOrCreateMaterial(material, blockData, @materialId);
END IF;
IF `oldMaterial` IS NOT NULL THEN CALL getOrCreateMaterial(
  oldMaterial, oldBlockData, @oldMaterialId
);
END IF;
INSERT INTO `prism_activities` (
  `timestamp`, `world_id`, `x`, `y`,
  `z`, `action_id`, `material_id`,
  `old_material_id`, `entity_type_id`,
  `cause_id`, `descriptor`
)
VALUES
  (
    `timestamp`, @worldId, `x`, `y`, `z`,
    @actionId, @materialId, @oldMaterialId,
    @entityId, @causeId, `descriptor`
  );
SET
  `activityId` = LAST_INSERT_ID();
IF `customData` IS NOT NULL THEN INSERT INTO `prism_activities_custom_data` (`activity_id`, `version`, `data`)
VALUES
  (
    `activityId`, `customDataVersion`,
    `customData`
  );
END IF;
END;
DELIMITER;


-- A procedure to get or create "actions"
DELIMITER;
CREATE PROCEDURE `getOrCreateAction`(
  IN `actionKey` VARCHAR(25),
  OUT `actionId` TINYINT
) BEGIN
SELECT
  action_id INTO `actionId`
FROM
  prism_actions
WHERE
  action = `actionKey`;
IF `actionId` IS NULL THEN INSERT INTO prism_actions (`action`)
VALUES
  (`actionKey`);
SET
  `actionId` = LAST_INSERT_ID();
END IF;
END;
DELIMITER;


-- A procedure to get or create "causes"
DELIMITER;
CREATE PROCEDURE `getOrCreateCause`(
  IN `causeStr` VARCHAR(25),
  IN `playerId` INT,
  OUT `causeId` INT
) BEGIN IF `playerId` IS NOT NULL THEN
SELECT
  cause_id INTO `causeId`
FROM
  prism_causes
WHERE
  player_id = `playerId`;
ELSEIF `causeStr` IS NOT NULL THEN
SELECT
  cause_id INTO `causeId`
FROM
  prism_causes
WHERE
  cause = `causeStr`;
END IF;
IF `causeId` IS NULL THEN INSERT INTO prism_causes (`cause`, `player_id`)
VALUES
  (`causeStr`, `playerId`);
SET
  `causeId` = LAST_INSERT_ID();
END IF;
END;
DELIMITER;


-- A procedure to get or create "entity types"
DELIMITER;
CREATE PROCEDURE `getOrCreateEntityType`(
  IN `entityType` VARCHAR(25),
  OUT `entityTypeId` SMALLINT
) BEGIN
SELECT
  entity_type_id INTO `entityTypeId`
FROM
  prism_entity_types
WHERE
  entity_type = `entityType`;
IF `entityTypeId` IS NULL THEN INSERT INTO prism_entity_types (`entity_type`)
VALUES
  (`entityType`);
SET
  `entityTypeId` = LAST_INSERT_ID();
END IF;
END;
DELIMITER;


-- A procedure to get or create "materials"
DELIMITER;
CREATE PROCEDURE `getOrCreateMaterial`(
  IN `materialKey` VARCHAR(45),
  IN `blockData` VARCHAR(155),
  OUT `materialId` SMALLINT
) BEGIN IF blockData IS NOT NULL THEN
SELECT
  material_id INTO `materialId`
FROM
  prism_materials
WHERE
  material = `materialKey`
  AND data = `blockData`;
ELSE
SELECT
  material_id INTO `materialId`
FROM
  prism_materials
WHERE
  material = `materialKey`
  AND data IS NULL;
END IF;
IF `materialId` IS NULL THEN INSERT INTO prism_materials (`material`, `data`)
VALUES
  (`materialKey`, `blockData`);
SET
  `materialId` = LAST_INSERT_ID();
END IF;
END;
DELIMITER;


-- A procedure to get or create "players"
DELIMITER;
CREATE PROCEDURE `getOrCreatePlayer`(
  IN `playerName` VARCHAR(16),
  IN `uuid` VARCHAR(55),
  OUT `playerId` INT
) BEGIN
SELECT
  player_id INTO `playerId`
FROM
  prism_players
WHERE
  player_uuid = UNHEX(`uuid`);
IF `playerId` IS NULL THEN INSERT INTO prism_players (`player`, `player_uuid`)
VALUES
  (
    `playerName`,
    UNHEX(`uuid`)
  );
SET
  `playerId` = LAST_INSERT_ID();
END IF;
END;
DELIMITER;


-- A procedure to get or create "worlds"
DELIMITER;
CREATE PROCEDURE `getOrCreateWorld`(
  IN `worldName` VARCHAR(255),
  IN `uuid` VARCHAR(55),
  OUT `worldId` TINYINT
) BEGIN
SELECT
  world_id INTO `worldId`
FROM
  prism_worlds
WHERE
  world_uuid = UNHEX(`uuid`);
IF `worldId` IS NULL THEN INSERT INTO prism_worlds (`world`, `world_uuid`)
VALUES
  (
    `worldName`,
    UNHEX(`uuid`)
  );
SET
  `worldId` = LAST_INSERT_ID();
END IF;
END;
DELIMITER;