-- Whisper's tables, in CS2-SimpleAdmin's database. Applied once at load, tracked in wh_migrations.
-- All times are UTC.

-- Every chat message, private message and staff message (while History is on in Whisper.json).
-- channel: all, team (normal chat), pm (/pm and /r), staff (@ team chat from staff), to_staff
--   (@ team chat from a player), or the SimpleAdmin command used: css_say, css_asay, css_psay,
--   css_csay, css_hsay, css_cssay.
-- result: sent; gagged (blocked by a gag or silence); command (a ! or / chat command); and for pm,
--   blocked, cooldown, too_long, no_target, ambiguous or group_target. Staff commands are logged
--   as typed, so one the sender wasn't allowed to use is still "sent".
-- The sender columns are NULL for the server console (RCON, the web panel). The recipient columns
-- are for pm only, NULL when no single player was found. team: 0 none, 1 spectator, 2 T, 3 CT.
-- alive: 1 when on T/CT with a living pawn. target is what the sender typed for it (pm, css_psay).
CREATE TABLE IF NOT EXISTS `wh_messages` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `message_key` char(32) NOT NULL,
    `sent_at` datetime NOT NULL,
    `server_id` int NULL,
    `map` varchar(64) NOT NULL,
    `channel` varchar(16) NOT NULL,
    `sender_steamid64` bigint unsigned NULL,
    `sender_name` varchar(128) NOT NULL,
    `sender_team` tinyint NULL,
    `sender_alive` tinyint NULL,
    `recipient_steamid64` bigint unsigned NULL,
    `recipient_name` varchar(128) NULL,
    `recipient_team` tinyint NULL,
    `recipient_alive` tinyint NULL,
    `target` varchar(128) NULL,
    `message` varchar(512) NOT NULL,
    `result` varchar(16) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_message_key` (`message_key`),
    KEY `ix_sent_at` (`sent_at`),
    KEY `ix_channel` (`channel`, `sent_at`),
    KEY `ix_sender` (`sender_steamid64`, `sent_at`),
    KEY `ix_recipient` (`recipient_steamid64`, `sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Who each player has blocked from messaging them (/block). blocked_name is their name when blocked,
-- empty when blocked by SteamID64 while offline.
CREATE TABLE IF NOT EXISTS `wh_blocks` (
    `blocker_steamid64` bigint unsigned NOT NULL,
    `blocked_steamid64` bigint unsigned NOT NULL,
    `blocked_name` varchar(128) NOT NULL DEFAULT '',
    `created_at` datetime NOT NULL,
    PRIMARY KEY (`blocker_steamid64`, `blocked_steamid64`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;