Installation

CREATE TABLE IF NOT EXISTS `rec_manager_items` (
  `id` bigint(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `cost` bigint(8) NOT NULL,
  `circulation` bigint(20) NOT NULL,
  `markup` decimal(6,3) NOT NULL DEFAULT 2.000,
  `marketFactor` decimal(6,4) NOT NULL DEFAULT 1.0000,
  `marketCirculation` bigint(20) DEFAULT NULL,
  `marketAt` timestamp NULL DEFAULT NULL,
  `excludeFromMarket` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `createdAt` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- Who changed what, and what the market revision did.
-- `source` is "admin" for a panel edit and "market" for a revision.
CREATE TABLE IF NOT EXISTS `rec_manager_item_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `source` varchar(16) NOT NULL,
  `field` varchar(32) NOT NULL,
  `oldValue` decimal(16,4) DEFAULT NULL,
  `newValue` decimal(16,4) DEFAULT NULL,
  `actorCitizenId` varchar(64) DEFAULT NULL,
  `actorName` varchar(64) DEFAULT NULL,
  `createdAt` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `createdAt` (`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- One row per item per snapshot, written on a recount and on a market revision.
-- The unique key keeps a day's snapshots from piling up when the recount is spammed.
CREATE TABLE IF NOT EXISTS `rec_manager_item_history` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `circulation` bigint(20) NOT NULL,
  `price` bigint(8) NOT NULL,
  `cost` bigint(8) NOT NULL,
  `marketFactor` decimal(6,4) NOT NULL DEFAULT 1.0000,
  `takenAt` timestamp NOT NULL DEFAULT current_timestamp(),
  `takenOn` date NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_takenOn` (`name`, `takenOn`),
  KEY `takenAt` (`takenAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- Characters watched by hand, so a hoard shows up without reading the whole roster.
CREATE TABLE IF NOT EXISTS `rec_manager_watchlist` (
  `citizenId` varchar(64) NOT NULL,
  `label` varchar(128) DEFAULT NULL,
  `note` varchar(255) DEFAULT NULL,
  `addedBy` varchar(64) DEFAULT NULL,
  `expiresAt` timestamp NULL DEFAULT NULL,
  `createdAt` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`citizenId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- What a watched character gained or lost between two samples.
-- There is no item move event to hook, so this is a difference and not a movement:
-- an amount that went up and back down inside one interval leaves no row.
CREATE TABLE IF NOT EXISTS `rec_manager_watch_events` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `citizenId` varchar(64) NOT NULL,
  `name` varchar(50) NOT NULL,
  -- inventory / stash / vehicle
  `source` varchar(16) NOT NULL,
  `delta` int(11) NOT NULL,
  `total` int(11) NOT NULL,
  `at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `citizenId_at` (`citizenId`, `at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Migration from v1 (run ONCE by hand, only on a table that still has the price column):
-- ALTER TABLE `rec_manager_items`
--   ADD COLUMN `markup` decimal(6,3) NOT NULL DEFAULT 2.000 AFTER `circulation`,
--   ADD COLUMN `marketFactor` decimal(6,4) NOT NULL DEFAULT 1.0000 AFTER `markup`,
--   ADD COLUMN `marketCirculation` bigint(20) DEFAULT NULL AFTER `marketFactor`,
--   ADD COLUMN `marketAt` timestamp NULL DEFAULT NULL AFTER `marketCirculation`;
-- UPDATE `rec_manager_items` SET `markup` = `price` / GREATEST(`cost`, 1);
-- ALTER TABLE `rec_manager_items` DROP COLUMN `price`;

-- Migration from v2 (run ONCE by hand, on a table that has no excludeFromMarket column):
-- ALTER TABLE `rec_manager_items`
--   ADD COLUMN `excludeFromMarket` tinyint(1) NOT NULL DEFAULT 0 AFTER `marketAt`;