Installation

-- =============================================================================
-- REC_Economy schema
--
-- Running this by hand is optional: the resource applies the same statements at
-- start (server/sv_schema.lua) and folds a v1 install into the current shape.
-- It is kept here for operators who prefer to review and apply the DDL first.
--
-- `moneys` is a JSON object keyed by money type, so a server with a fifth
-- currency needs no schema change.
-- =============================================================================

CREATE TABLE IF NOT EXISTS `rec_economy_snapshots` (
  `id` bigint(8) NOT NULL AUTO_INCREMENT,
  `moneys` longtext NOT NULL,
  `societyTotal` bigint(20) NOT NULL,
  `onlineCount` int(11) NOT NULL,
  `characterCount` int(11) NOT NULL DEFAULT 0,
  `dormancy` longtext DEFAULT NULL,
  `takenAt` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `takenAt` (`takenAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

CREATE TABLE IF NOT EXISTS `rec_economy_society_snapshots` (
  `id` bigint(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `money` bigint(20) NOT NULL,
  `takenAt` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `name_takenAt` (`name`, `takenAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

CREATE TABLE IF NOT EXISTS `rec_economy_alerts` (
  `id` bigint(8) NOT NULL AUTO_INCREMENT,
  `kind` varchar(16) NOT NULL,
  `subject` varchar(64) NOT NULL,
  `label` varchar(128) DEFAULT NULL,
  `beforeAmount` bigint(20) NOT NULL,
  `afterAmount` bigint(20) NOT NULL,
  `delta` bigint(20) NOT NULL,
  `sigma` double DEFAULT NULL,
  `detectedAt` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `detectedAt` (`detectedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- Faucet / sink buckets, one row per reason and money type per flush period.
-- The row count follows the number of distinct reasons, not the transaction volume.
CREATE TABLE IF NOT EXISTS `rec_economy_flows` (
  `id` bigint(8) NOT NULL AUTO_INCREMENT,
  `reason` varchar(64) NOT NULL,
  `flowKind` varchar(16) NOT NULL DEFAULT 'unknown',
  `moneyType` varchar(32) NOT NULL,
  `added` bigint(20) NOT NULL,
  `removed` bigint(20) NOT NULL,
  `events` int(11) NOT NULL,
  `periodStart` timestamp NOT NULL DEFAULT current_timestamp(),
  `takenAt` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `takenAt` (`takenAt`),
  KEY `reason` (`reason`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- Carries the live movement feed across a restart only: written on stop and
-- emptied again on start, so it never holds more than one buffer's worth.
CREATE TABLE IF NOT EXISTS `rec_economy_recent_flows` (
  `id` bigint(8) NOT NULL AUTO_INCREMENT,
  `reason` varchar(64) NOT NULL,
  `moneyType` varchar(32) NOT NULL,
  `amount` bigint(20) NOT NULL,
  `isRemove` tinyint(1) NOT NULL,
  `name` varchar(128) DEFAULT NULL,
  `at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- Earnings per job per period. One row per job rather than job x money type, so
-- the row count follows the job list. avgOnline is the time weighted average
-- number of that job online, the denominator for "per player per hour".
CREATE TABLE IF NOT EXISTS `rec_economy_job_flows` (
  `id` bigint(8) NOT NULL AUTO_INCREMENT,
  `job` varchar(64) NOT NULL,
  `added` bigint(20) NOT NULL,
  `removed` bigint(20) NOT NULL,
  `events` int(11) NOT NULL,
  `avgOnline` decimal(8,2) NOT NULL DEFAULT 0.00,
  `playerSeconds` double DEFAULT NULL,
  `periodStart` timestamp NOT NULL DEFAULT current_timestamp(),
  `takenAt` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `takenAt` (`takenAt`),
  KEY `job` (`job`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- Operator notes, drawn as markers on the charts so a change made on a given day
-- can be read against the line that moved.
CREATE TABLE IF NOT EXISTS `rec_economy_notes` (
  `id` bigint(8) NOT NULL AUTO_INCREMENT,
  `text` varchar(255) NOT NULL,
  `author` varchar(64) DEFAULT NULL,
  `at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `at` (`at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Characters whose every movement is logged. Opt in and self expiring, because
-- recording everybody is the heavy design this resource avoids.
CREATE TABLE IF NOT EXISTS `rec_economy_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;

CREATE TABLE IF NOT EXISTS `rec_economy_watch_events` (
  `id` bigint(8) NOT NULL AUTO_INCREMENT,
  `citizenId` varchar(64) NOT NULL,
  `reason` varchar(64) NOT NULL,
  `moneyType` varchar(32) NOT NULL,
  `amount` bigint(20) NOT NULL,
  `isRemove` tinyint(1) NOT NULL,
  `at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `citizenId_at` (`citizenId`, `at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- What one character normally moves, so an unusual movement can be told apart from a
-- large one. A running mean and variance rather than a history, so the row count
-- follows the roster and never the movement count.
CREATE TABLE IF NOT EXISTS `rec_economy_player_baselines` (
  `citizenId` varchar(64) NOT NULL,
  `mean` double NOT NULL DEFAULT 0,
  `variance` double NOT NULL DEFAULT 0,
  `samples` int(11) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`citizenId`),
  KEY `updatedAt` (`updatedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

CREATE TABLE IF NOT EXISTS `rec_economy_analysis_periods` (
`id` bigint NOT NULL AUTO_INCREMENT,
`periodStart` timestamp NOT NULL,
`takenAt` timestamp NOT NULL,
`observedSeconds` double NOT NULL,
`playerSeconds` double NOT NULL,
PRIMARY KEY (`id`), KEY `takenAt` (`takenAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `rec_economy_analysis_snapshots` (
`id` bigint NOT NULL AUTO_INCREMENT,
`payload` longtext NOT NULL,
`takenAt` timestamp NOT NULL,
PRIMARY KEY (`id`), KEY `takenAt` (`takenAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `rec_economy_analysis_players` (
`citizenId` varchar(64) NOT NULL,
`firstSeen` timestamp NOT NULL,
`lastSeen` timestamp NOT NULL,
`playSeconds` double NOT NULL DEFAULT 0,
`initialWealth` bigint NOT NULL,
`wealth` bigint NOT NULL,
`targetWealth` bigint NOT NULL,
`targetSeconds` double DEFAULT NULL,
PRIMARY KEY (`citizenId`), KEY `firstSeen` (`firstSeen`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `rec_economy_analysis_growth` (
`citizenId` varchar(64) NOT NULL,
`day` date NOT NULL,
`playSeconds` double NOT NULL,
`wealth` bigint NOT NULL,
PRIMARY KEY (`citizenId`, `day`), KEY `day` (`day`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `rec_economy_analysis_activities` (
`id` bigint NOT NULL AUTO_INCREMENT,
`resource` varchar(64) NOT NULL,
`activity` varchar(64) NOT NULL,
`success` tinyint NOT NULL,
`participants` int NOT NULL,
`playerSeconds` double NOT NULL,
`earned` bigint NOT NULL,
`spent` bigint NOT NULL,
`startedAt` timestamp NOT NULL,
`takenAt` timestamp NOT NULL,
PRIMARY KEY (`id`), KEY `takenAt` (`takenAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `rec_economy_analysis_trades` (
`day` date NOT NULL,
`item` varchar(64) NOT NULL,
`moneyType` varchar(32) NOT NULL,
`quantity` double NOT NULL,
`paid` double NOT NULL,
`trades` int NOT NULL,
PRIMARY KEY (`day`, `item`, `moneyType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;