-- Per-guild override of config.prefix - no row means "use the default
-- prefix", same pattern as command_settings' "no row = enabled". Set only
-- via the dashboard (admin-gated) - see services/guildPrefix.js.
CREATE TABLE IF NOT EXISTS guild_prefixes (
  guild_id TEXT PRIMARY KEY,
  prefix TEXT NOT NULL
);

-- Owner-only. A row here means the bot ignores this user entirely - no
-- commands, no XP/economy gain, no quest progress. Separate from
-- global_bans, which actually bans someone from Discord servers; this is
-- purely "the bot itself refuses to interact with you" and doesn't touch
-- their ability to be in a server at all.
-- expires_at NULL means permanent (the original behavior); set means a temp
-- disable that lapses on its own - isBlacklisted checks it lazily rather
-- than deleting the row on expiry, same pattern as economy_balances'
-- lock_until/jailed_until.
CREATE TABLE IF NOT EXISTS bot_blacklist (
  user_id TEXT PRIMARY KEY,
  reason TEXT,
  blacklisted_by TEXT NOT NULL,
  blacklisted_at TEXT NOT NULL DEFAULT (datetime('now')),
  expires_at TEXT
);

-- Global, self-service opt-out of the bot's DMs (currently just the
-- level-up notification) - set via the Disable button on the DM itself, or
-- ,notifications in chat. No row means DMs are on (the default).
CREATE TABLE IF NOT EXISTS dm_opt_outs (
  user_id TEXT PRIMARY KEY,
  disabled_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Level-up DMs are opt-IN (default off) - the inverse of dm_opt_outs above,
-- which governs punishment/mod-action DMs (default on, opt-out).
CREATE TABLE IF NOT EXISTS level_dm_opt_ins (
  user_id TEXT PRIMARY KEY,
  enabled_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Global (not per-guild), self-service personal prefix - lets one user
-- trigger commands with their own prefix (e.g. to dodge a clash with
-- another bot they run) without touching the server's actual prefix for
-- everyone else. Layers on top of guild_prefixes - messageCreate.js accepts
-- either. Set via ,prefix in chat, no permission needed since it only
-- affects the user setting it.
CREATE TABLE IF NOT EXISTS user_prefixes (
  user_id TEXT PRIMARY KEY,
  prefix TEXT NOT NULL
);

-- Personal command shortcuts ($setalias) - global (not per-guild), same
-- reasoning as user_prefixes above. Purely a display-name remap resolved as
-- a fallback in messageCreate.js when the typed word isn't a real command/
-- alias already - the underlying command's own permission check still
-- applies in full when the alias is actually used, so anyone can set any
-- alias for any command without that granting them anything.
CREATE TABLE IF NOT EXISTS user_command_aliases (
  user_id TEXT NOT NULL,
  alias TEXT NOT NULL,
  command_name TEXT NOT NULL,
  PRIMARY KEY (user_id, alias)
);

-- Personal per-command reply override ($setcustomresponse) - always keyed on
-- the command's real canonical name (never an alias - messageCreate.js
-- resolves whichever one was actually typed back to this before checking),
-- so it applies identically whether the user types the real name or one of
-- their own $setalias shortcuts for it. Purely cosmetic like
-- user_command_aliases above - overrides what the bot's reply LOOKS like,
-- never what the command actually does or who it's allowed to be run by.
-- Content is sent with allowedMentions capped by utils/customVariables.js's
-- buildSafeMentionOptions (at most 3 real users, never a role/@everyone/@here
-- no matter what's saved here) regardless of what's in `message`.
CREATE TABLE IF NOT EXISTS user_custom_messages (
  user_id TEXT NOT NULL,
  command_name TEXT NOT NULL,
  message TEXT NOT NULL,
  PRIMARY KEY (user_id, command_name)
);

-- Guild-wide custom commands ($customcmd) - unlike user_custom_messages
-- above (a personal override only the setter ever sees), these are real new
-- trigger words anyone in the server can run once created, so creating one
-- requires Manage Guild. Same rendering pipeline either way though
-- (utils/customVariables.js's resolveVariables + buildSafeMentionOptions) -
-- the "global, used by all the custom stuff" variable list and the ping-cap
-- safety are shared code, not reimplemented per feature.
CREATE TABLE IF NOT EXISTS custom_commands (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  guild_id TEXT NOT NULL,
  name TEXT NOT NULL,
  response TEXT NOT NULL,
  self_destruct_seconds INTEGER, -- null = off; the bot's own response auto-deletes after this many seconds
  delete_invoking INTEGER NOT NULL DEFAULT 0, -- --delete-cmd - deletes the user's triggering message
  use_embed INTEGER NOT NULL DEFAULT 0, -- --embed - wraps the response in an embed instead of plain content
  react_emoji TEXT, -- --react <emoji> - reacts to the triggering message with this, alongside the normal response
  cooldown_seconds INTEGER, -- --cooldown <seconds> - null = off; per-user, in-memory (services/customCommandCooldown.js), resets on restart same as every other in-memory cooldown in this bot
  dm_only INTEGER NOT NULL DEFAULT 0, -- --dm - sends the response to the runner's DMs instead of the channel
  silent INTEGER NOT NULL DEFAULT 0, -- --silent - suppresses the push notification Discord would otherwise send for it
  created_by TEXT NOT NULL,
  run_count INTEGER NOT NULL DEFAULT 0, -- total across everyone - {totalruncount}
  created_at TEXT NOT NULL,
  UNIQUE(guild_id, name)
);
-- Per-user run counts for the same custom command - {runcount} is "how many
-- times just THIS person has run it", separate from run_count's guild-wide
-- total above.
CREATE TABLE IF NOT EXISTS custom_command_runs (
  custom_command_id INTEGER NOT NULL,
  user_id TEXT NOT NULL,
  count INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (custom_command_id, user_id)
);

-- $selfcustomcmd - same idea and same flag set as custom_commands above,
-- but personal and global instead of guild-wide: no guild_id at all (works
-- identically in every server the creator and the bot share, and in DMs),
-- and only the creator can ever run it (messageCreate.js's trigger check
-- looks this up by the RUNNER's own id, so there's nothing to enforce
-- separately - nobody else's lookup could ever match this row). No
-- Manage Guild requirement to create one, same reasoning as
-- user_custom_messages - it can only ever affect the person who made it.
-- Only one run_count (not a separate per-user table like
-- custom_command_runs) since there's only ever one possible runner -
-- {runcount} and {totalruncount} are always the same value here.
CREATE TABLE IF NOT EXISTS self_custom_commands (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL,
  name TEXT NOT NULL,
  response TEXT NOT NULL,
  self_destruct_seconds INTEGER,
  delete_invoking INTEGER NOT NULL DEFAULT 0,
  use_embed INTEGER NOT NULL DEFAULT 0,
  react_emoji TEXT,
  cooldown_seconds INTEGER,
  dm_only INTEGER NOT NULL DEFAULT 0,
  silent INTEGER NOT NULL DEFAULT 0,
  run_count INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  UNIQUE(user_id, name)
);

-- Master switch + the standalone modules (invite-link deletion, bot-add
-- kicking, bot-strict, lockdown) that aren't part of the per-detection
-- threshold system below. `punish_action` is legacy (pre-engine) and no
-- longer read - kept so old rows don't break, superseded by
-- antinuke_detections' per-type flags.
CREATE TABLE IF NOT EXISTS antinuke_settings (
  guild_id TEXT PRIMARY KEY,
  enabled INTEGER NOT NULL DEFAULT 0,
  punish_action TEXT NOT NULL DEFAULT 'strip_roles',
  log_channel_id TEXT,

  invitelink_enabled INTEGER NOT NULL DEFAULT 0,
  invitelink_strip INTEGER NOT NULL DEFAULT 0,
  invitelink_mute INTEGER NOT NULL DEFAULT 0,
  invitelink_kick INTEGER NOT NULL DEFAULT 0,
  invitelink_ban INTEGER NOT NULL DEFAULT 0,
  invitelink_hardban INTEGER NOT NULL DEFAULT 0,

  botadd_enabled INTEGER NOT NULL DEFAULT 0,
  botadd_strip INTEGER NOT NULL DEFAULT 0,
  botadd_mute INTEGER NOT NULL DEFAULT 0,
  botadd_kick INTEGER NOT NULL DEFAULT 1,
  botadd_ban INTEGER NOT NULL DEFAULT 0,
  botadd_hardban INTEGER NOT NULL DEFAULT 0,

  botstrict INTEGER NOT NULL DEFAULT 0,

  lockdown_active INTEGER NOT NULL DEFAULT 0,
  lockdown_until TEXT,
  lockdown_snapshot TEXT, -- JSON [{channelId, allow, deny}] of @everyone overwrites to restore

  raid_enabled INTEGER NOT NULL DEFAULT 0,
  raid_count INTEGER NOT NULL DEFAULT 5,
  raid_window_seconds INTEGER NOT NULL DEFAULT 10,
  raid_action TEXT NOT NULL DEFAULT 'kick', -- 'kick' | 'lockdown'

  -- $an bleed sync - fakeperms bots (bleed and similar) always show up as
  -- the audit log executor themselves, but stamp the real acting user into
  -- the reason as "User Responsible: <name> / <original reason>". When on,
  -- utils/auditLog.js resolves that name back to the real member and swaps
  -- it in as the entry's executor before any antinuke listener ever sees
  -- it - so thresholds count against the real person (and get punished
  -- accordingly), not the passthrough bot itself.
  bleed_sync_enabled INTEGER NOT NULL DEFAULT 0
);

-- One row per (guild, detection type) - see services/antinukeDetections.js
-- for the 27-type catalog. No row / enabled=0 means that detection is off.
-- Punishment flags are independent and combine in a fixed order (strip ->
-- mute -> kick -> ban/hardban); revert_always reverts every qualifying
-- action immediately instead of only once the threshold is hit.
CREATE TABLE IF NOT EXISTS antinuke_detections (
  guild_id TEXT NOT NULL,
  detection TEXT NOT NULL,
  enabled INTEGER NOT NULL DEFAULT 0,
  count INTEGER NOT NULL DEFAULT 3,
  window_seconds INTEGER NOT NULL DEFAULT 10,
  strip INTEGER NOT NULL DEFAULT 0,
  mute INTEGER NOT NULL DEFAULT 0,
  kick INTEGER NOT NULL DEFAULT 0,
  ban INTEGER NOT NULL DEFAULT 0,
  hardban INTEGER NOT NULL DEFAULT 0,
  jail INTEGER NOT NULL DEFAULT 0,
  revert_always INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (guild_id, detection)
);

-- Rolling-window occurrence log: one row per qualifying action. Counted
-- (not summed) within the trailing window_seconds to check against count.
-- Cleared for that (guild, detection, executor) once a threshold hits, so
-- a single burst doesn't re-punish on every subsequent action.
CREATE TABLE IF NOT EXISTS antinuke_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  guild_id TEXT NOT NULL,
  detection TEXT NOT NULL,
  executor_id TEXT NOT NULL,
  occurred_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_antinuke_events_lookup ON antinuke_events (guild_id, detection, executor_id, occurred_at);

CREATE TABLE IF NOT EXISTS antinuke_whitelist (
  guild_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  PRIMARY KEY (guild_id, user_id)
);

-- Elevated rank, appointed only by the bot owner (config.ownerIds).
-- Whitelist admins can manage antinuke settings and grant/revoke antinuke_whitelist entries.
CREATE TABLE IF NOT EXISTS whitelist_admins (
  guild_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  added_by TEXT NOT NULL,
  added_at TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (guild_id, user_id)
);

-- One custom role per booster per guild ($br create). Cleaned up (row +
-- Discord role both) when the role is deleted directly, or the owner stops
-- boosting - see events/boosterRoleCleanup.js.
CREATE TABLE IF NOT EXISTS booster_roles (
  guild_id TEXT NOT NULL,
  owner_id TEXT NOT NULL,
  role_id TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (guild_id, owner_id)
);
CREATE INDEX IF NOT EXISTS idx_booster_roles_role ON booster_roles (guild_id, role_id);

-- Other members a booster has shared their custom role with ($br share) -
-- cosmetic only, doesn't grant them any $br management rights over it.
CREATE TABLE IF NOT EXISTS booster_role_shares (
  guild_id TEXT NOT NULL,
  owner_id TEXT NOT NULL,
  shared_with_id TEXT NOT NULL,
  PRIMARY KEY (guild_id, owner_id, shared_with_id)
);

-- $br setrole - every role $br create makes gets positioned directly above
-- this one. Null/no row means no anchor configured, new roles just land
-- wherever Discord's API defaults a fresh role to.
CREATE TABLE IF NOT EXISTS booster_role_settings (
  guild_id TEXT PRIMARY KEY,
  anchor_role_id TEXT
);

-- One honeypot channel per guild (,an softban setup) - anyone who isn't
-- antinuke-protected who types in channel_id or clicks its trap button gets
-- softbanned. message_id is the trap embed so it can be re-fetched/edited later.
CREATE TABLE IF NOT EXISTS antinuke_softban_traps (
  guild_id TEXT PRIMARY KEY,
  channel_id TEXT NOT NULL,
  message_id TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Softban trap bans awaiting their 12h auto-unban - see services/ready.js's
-- checkSoftbanExpiry, same "resume on boot, then keep polling" shape as
-- temp_roles.
CREATE TABLE IF NOT EXISTS antinuke_temp_bans (
  guild_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  unban_at TEXT NOT NULL,
  PRIMARY KEY (guild_id, user_id)
);
CREATE INDEX IF NOT EXISTS idx_antinuke_temp_bans_due ON antinuke_temp_bans (unban_at);

-- Global, not per-guild - one balance per user across every server the bot is in.
-- Income sources: ,daily/,weekly/,monthly claims, ,work/,crime, passive VC
-- time payout (services/vcTime.js), and message activity (services/levels.js).
-- `balance` is pocket cash - robbable via ,rob. `bank_balance` is safe from
-- ,rob and only reachable via a successful ,heist; it earns 2%/day interest,
-- applied lazily whenever the account is touched (bank_last_interest tracks
-- the last day interest was compounded through).
CREATE TABLE IF NOT EXISTS economy_balances (
  user_id TEXT PRIMARY KEY,
  balance INTEGER NOT NULL DEFAULT 0,
  bank_balance INTEGER NOT NULL DEFAULT 0,
  bank_last_interest TEXT,
  lock_until TEXT,
  last_daily TEXT,
  last_weekly TEXT,
  last_monthly TEXT,
  last_rob TEXT,
  last_work TEXT,
  last_crime TEXT,
  last_fish TEXT,
  last_hunt TEXT
);

-- Global leveling - XP accrues from message activity (cooldown-limited)
-- regardless of which server the message was sent in.
CREATE TABLE IF NOT EXISTS levels (
  user_id TEXT PRIMARY KEY,
  xp INTEGER NOT NULL DEFAULT 0,
  last_message_at TEXT
);

-- Permanent shop purchases (fishing_rod, hunting_rifle, ...) - see
-- services/shop.js for the catalog. Owning one just gates a command.
-- uses_remaining is NULL for true forever-owned gear; a handful of items
-- (bolt_cutter) are limited-use instead - the row is deleted once it hits 0,
-- so a plain ownership check still works without a separate "still usable"
-- check anywhere else.
CREATE TABLE IF NOT EXISTS user_items (
  user_id TEXT NOT NULL,
  item_id TEXT NOT NULL,
  uses_remaining INTEGER,
  PRIMARY KEY (user_id, item_id)
);

-- Time-limited shop boosts (xp_potion, luck_charm, ...). The actual
-- multiplier/bonus for each boost_type lives in the static catalog in
-- services/shop.js, not here - this table only tracks when it expires.
-- Re-buying the same boost while active extends active_until rather than
-- resetting it.
CREATE TABLE IF NOT EXISTS user_boosts (
  user_id TEXT NOT NULL,
  boost_type TEXT NOT NULL,
  active_until TEXT NOT NULL,
  PRIMARY KEY (user_id, boost_type)
);

-- ,fish and ,hunt add here instead of paying out instantly - ,sell cashes
-- them in. Catalog (names/sell values) lives in services/catches.js.
CREATE TABLE IF NOT EXISTS user_catches (
  user_id TEXT NOT NULL,
  catch_id TEXT NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (user_id, catch_id)
);

-- `active` lets a case be revoked (,unwarn) without deleting the record, so
-- the case id and history stay intact for later review.
CREATE TABLE IF NOT EXISTS moderation_warnings (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  guild_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  moderator_id TEXT NOT NULL,
  active INTEGER NOT NULL DEFAULT 1,
  reason TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Per-guild override for a customizable embed "slot" (services/embedSlots.js
-- has the fixed registry of slot keys and their defaults - this table only
-- ever holds guilds that have actually customized one away from default).
-- config is a JSON blob - see services/embedConfig.js for its shape.
CREATE TABLE IF NOT EXISTS guild_embed_configs (
  guild_id TEXT NOT NULL,
  slot_key TEXT NOT NULL,
  config TEXT NOT NULL,
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (guild_id, slot_key)
);

-- One pet per user, global like the rest of the economy. hunger is stored
-- already-decayed as of last_decay_at (see services/pets.js) rather than
-- computed from adopted_at, so decay math only ever has to account for the
-- time since the last read/write, not the pet's whole lifetime.
CREATE TABLE IF NOT EXISTS user_pets (
  user_id TEXT PRIMARY KEY,
  pet_type TEXT NOT NULL,
  name TEXT NOT NULL,
  hunger REAL NOT NULL DEFAULT 100,
  last_decay_at TEXT NOT NULL,
  last_collected_at TEXT NOT NULL,
  adopted_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Every successful command run, prefix or slash - powers the dashboard's
-- "recent activity" panel AND the owner-only global activity log/search.
-- args/result_summary are best-effort text captures (see utils/captureReply.js)
-- - multi-step/button-driven commands only capture their initial prompt, not
-- the final resolved outcome. Pruned periodically (see services/commandLog.js)
-- rather than on every insert, since this is a hot path.
CREATE TABLE IF NOT EXISTS command_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL,
  guild_id TEXT,
  command_name TEXT NOT NULL,
  args TEXT,
  result_summary TEXT,
  used_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_command_log_user ON command_log(user_id, used_at DESC);

-- Global lottery - one active round at a time. tickets holds this round's
-- purchases only (cleared on draw); state holds the pot/price/draw time as
-- a single row (id always 1). See services/lottery.js.
CREATE TABLE IF NOT EXISTS lottery_state (
  id INTEGER PRIMARY KEY CHECK (id = 1),
  pot INTEGER NOT NULL DEFAULT 0,
  ticket_price INTEGER NOT NULL DEFAULT 100,
  next_draw_at TEXT NOT NULL,
  last_winner_id TEXT,
  last_winner_amount INTEGER,
  last_drawn_at TEXT
);
CREATE TABLE IF NOT EXISTS lottery_tickets (
  user_id TEXT PRIMARY KEY,
  quantity INTEGER NOT NULL DEFAULT 0
);

-- One channel per log category per guild. log_type is one of:
-- mod, members, msg, role, channel, invite, vc, server
CREATE TABLE IF NOT EXISTS log_channels (
  guild_id TEXT NOT NULL,
  log_type TEXT NOT NULL,
  channel_id TEXT NOT NULL,
  PRIMARY KEY (guild_id, log_type)
);

-- Global (no guild_id - one bot-owner-controlled channel per type,
-- regardless of which server the change happened in), configured only from
-- the owner-only /admin dashboard. log_type is one of: avatar, banner,
-- nickname. Deliberately a completely separate table from
-- avatar_history/banner_history/nickname_history - $avatarhistory etc.'s
-- self-wipe only ever touches those, never this, so the owner keeps a
-- permanent record even after a user wipes their own visible history.
CREATE TABLE IF NOT EXISTS admin_log_channels (
  log_type TEXT PRIMARY KEY,
  channel_id TEXT NOT NULL
);

-- Servers the bot is allowed to operate in. Access is whitelist-only,
-- controlled solely by the bot owner (config.ownerIds).
CREATE TABLE IF NOT EXISTS guild_access (
  guild_id TEXT PRIMARY KEY,
  added_by TEXT NOT NULL,
  added_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- One row per completed voice session. Totals/leaderboards/longest-session
-- are all derived from this with SUM/MAX/ORDER BY - no separate running-total
-- table to keep in sync.
CREATE TABLE IF NOT EXISTS vc_sessions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  guild_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  started_at TEXT NOT NULL,
  ended_at TEXT NOT NULL,
  duration_seconds INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_vc_sessions_guild_user ON vc_sessions (guild_id, user_id);
CREATE INDEX IF NOT EXISTS idx_vc_sessions_user ON vc_sessions (user_id);

-- A voice session still in progress. Persisted (not just in-memory) so a
-- bot restart doesn't lose whoever's mid-call - startSession is INSERT OR
-- IGNORE, so re-priming this on `ready` never clobbers a real join time
-- that was already recorded before the restart.
CREATE TABLE IF NOT EXISTS active_vc_sessions (
  guild_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  started_at TEXT NOT NULL,
  PRIMARY KEY (guild_id, user_id)
);

-- Join-to-create voice: joining trigger_channel_id spawns a new temp
-- channel (in category_id, or the trigger's own category if unset) that
-- the joiner owns and controls via the button panel / ,vc commands.
CREATE TABLE IF NOT EXISTS voicemaster_settings (
  guild_id TEXT PRIMARY KEY,
  enabled INTEGER NOT NULL DEFAULT 0,
  trigger_channel_id TEXT,
  category_id TEXT,
  name_template TEXT NOT NULL DEFAULT '{user}''s channel'
);

-- One row per currently-alive temp channel this system created. Deleted
-- (along with the actual channel) once it's empty.
CREATE TABLE IF NOT EXISTS voicemaster_channels (
  channel_id TEXT PRIMARY KEY,
  guild_id TEXT NOT NULL,
  owner_id TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_voicemaster_channels_guild ON voicemaster_channels (guild_id);

-- Global (not per-guild), like economy/levels - "you've hugged them 12
-- times" is a running total across every server, not reset per-server.
CREATE TABLE IF NOT EXISTS roleplay_counts (
  actor_id TEXT NOT NULL,
  target_id TEXT NOT NULL,
  action TEXT NOT NULL,
  count INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (actor_id, target_id, action)
);

-- Per-guild message counts (,msgstats, ,msgleaderboard) - unlike XP/economy
-- message income, this is a raw count with no cooldown, so it reflects
-- actual activity in a given server.
CREATE TABLE IF NOT EXISTS message_counts (
  guild_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  count INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (guild_id, user_id)
);
CREATE INDEX IF NOT EXISTS idx_message_counts_guild ON message_counts (guild_id);

-- Dashboard: anyone can log in via Discord OAuth (identity only). Tracked
-- purely so the owner can spot alt accounts by shared IP before issuing a
-- global ban - not a general permission table.
CREATE TABLE IF NOT EXISTS dashboard_users (
  id TEXT PRIMARY KEY,
  username TEXT,
  avatar TEXT,
  last_ip TEXT,
  last_login TEXT,
  guilds TEXT -- JSON array of {id, name, icon, mutual}, refreshed each login (requires the "guilds" OAuth scope)
);

CREATE TABLE IF NOT EXISTS dashboard_logins (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL,
  ip TEXT NOT NULL,
  user_agent TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_dashboard_logins_user ON dashboard_logins (user_id);

-- Owner-only. A row here means the bot actively bans this user in every
-- guild_access-listed guild (issued at ban time, and re-enforced on any
-- future guildMemberAdd in case they try to evade via a new join).
CREATE TABLE IF NOT EXISTS global_bans (
  user_id TEXT PRIMARY KEY,
  reason TEXT,
  banned_by TEXT NOT NULL,
  banned_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Fallback username cache for dashboard displays (leaderboards, etc.) when
-- someone shows up via bot activity but has never logged into the dashboard
-- itself, so dashboard_users has nothing on them. Fetched via the Discord
-- REST API with the bot token - see services/discordUsers.js.
CREATE TABLE IF NOT EXISTS discord_user_cache (
  id TEXT PRIMARY KEY,
  username TEXT,
  fetched_at TEXT NOT NULL
);

-- Per-guild command toggle. No row (or enabled=1) means on - dashboard only
-- ever writes a row to turn something off or lock it, never to confirm the
-- default "everything's on" state.
CREATE TABLE IF NOT EXISTS command_settings (
  guild_id TEXT NOT NULL,
  command_name TEXT NOT NULL,
  enabled INTEGER NOT NULL DEFAULT 1,
  PRIMARY KEY (guild_id, command_name)
);

-- Any row here for a (guild, command) means that command is restricted -
-- only the listed roles/users (plus the owner/whitelist admin, who always
-- bypass) may run it. No rows = unrestricted, subject to command_settings.
CREATE TABLE IF NOT EXISTS command_locks (
  guild_id TEXT NOT NULL,
  command_name TEXT NOT NULL,
  target_type TEXT NOT NULL, -- 'role' | 'user'
  target_id TEXT NOT NULL,
  PRIMARY KEY (guild_id, command_name, target_type, target_id)
);

-- Roles auto-applied to every human member on join. Bots are never
-- autoroled - the botadd/botstrict antinuke module is what governs bots.
CREATE TABLE IF NOT EXISTS autorole_settings (
  guild_id TEXT PRIMARY KEY,
  enabled INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS autorole_roles (
  guild_id TEXT NOT NULL,
  role_id TEXT NOT NULL,
  PRIMARY KEY (guild_id, role_id)
);

-- Automod: word filter (banned_words is a JSON array of lowercase strings/
-- substrings) and a spam-rate limiter. Spam tracking itself is in-memory
-- (services/automod.js) - losing a few seconds of "messages sent just
-- before a restart" is harmless, unlike antinuke's persistent event log.
CREATE TABLE IF NOT EXISTS automod_settings (
  guild_id TEXT PRIMARY KEY,
  word_filter_enabled INTEGER NOT NULL DEFAULT 0,
  banned_words TEXT NOT NULL DEFAULT '[]',
  spam_enabled INTEGER NOT NULL DEFAULT 0,
  spam_count INTEGER NOT NULL DEFAULT 5,
  spam_window_seconds INTEGER NOT NULL DEFAULT 10,
  mute_minutes INTEGER NOT NULL DEFAULT 10
);

-- Global marketplace for fished/hunted catches - list a catch for sale,
-- anyone (any guild) can buy it. First-come-first-served, no live bidding.
CREATE TABLE IF NOT EXISTS market_listings (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  seller_id TEXT NOT NULL,
  catch_id TEXT NOT NULL,
  quantity INTEGER NOT NULL,
  price_per_unit INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Debt taken against the global economy account - separate from
-- bank_balance/lock_until on economy_balances since it accrues interest the
-- other direction (owed, not earned) and blocks taking a second loan.
-- Columns added via the migration block in db/index.js, not here, since
-- economy_balances already exists on deployed databases.

-- Daily/weekly quest progress. period_key is a date ("2026-07-20") or ISO
-- week ("2026-W29") string, so progress resets naturally each period just
-- by looking up a key that doesn't exist yet - no cron job clearing rows.
CREATE TABLE IF NOT EXISTS quest_progress (
  user_id TEXT NOT NULL,
  quest_id TEXT NOT NULL,
  period_key TEXT NOT NULL,
  progress INTEGER NOT NULL DEFAULT 0,
  completed INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (user_id, quest_id, period_key)
);

-- Owner-triggered global events (e.g. a double-XP weekend) - dashboard
-- /admin only. Multiple rows can exist; only unexpired ones count.
CREATE TABLE IF NOT EXISTS active_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  type TEXT NOT NULL, -- 'xp_multiplier'
  multiplier REAL NOT NULL,
  label TEXT,
  starts_at TEXT NOT NULL,
  ends_at TEXT NOT NULL,
  created_by TEXT NOT NULL
);

-- Provably-fair RNG for every gamble command (services/fairRng.js). The
-- active row's seed is secret but its seed_hash is public (via $fairness) -
-- committed BEFORE any roll it produces, so the seed can't be chosen to fit
-- a result after the fact. Every roll is HMAC-SHA256(seed, "<user>:<nonce>"),
-- so once a seed rotates out (revealed=1, the real seed becomes public too)
-- anyone can recompute every roll it ever produced for their own user id and
-- confirm none were tampered with. Rotates lazily (like bank interest below)
-- rather than on a timer/cron - see fairRng.js.
CREATE TABLE IF NOT EXISTS fairness_seeds (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  seed TEXT NOT NULL,
  seed_hash TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  revealed INTEGER NOT NULL DEFAULT 0,
  revealed_at TEXT
);

-- Per-user HMAC nonce counter under a given seed - incremented once per
-- roll. No row means nonce 0, so a newly-active seed needs no explicit reset
-- for anyone.
CREATE TABLE IF NOT EXISTS fairness_nonces (
  seed_id INTEGER NOT NULL,
  user_id TEXT NOT NULL,
  nonce INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (seed_id, user_id)
);

-- Bot-owner-only GLOBAL kill switch for a command, independent of the
-- per-guild enable/disable in command_settings - a row here disables the
-- command bot-wide, in every server, until removed. Meant for "this is
-- bugging, disable it while I fix it" rather than per-server moderation.
CREATE TABLE IF NOT EXISTS command_maintenance (
  command_name TEXT PRIMARY KEY,
  reason TEXT,
  disabled_by TEXT NOT NULL,
  disabled_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Bot-owner-only GLOBAL presence config - a single-row JSON blob (id is
-- always 1, there's only ever one bot-wide value) holding the rotating
-- status entries and how often to rotate. See services/botActivity.js for
-- the JSON shape and the template-variable rendering.
CREATE TABLE IF NOT EXISTS bot_activity_config (
  id INTEGER PRIMARY KEY CHECK (id = 1),
  config TEXT NOT NULL,
  updated_by TEXT,
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Global, not per-guild - same as economy_balances. `ended_at` NULL means
-- still married; a row is never deleted on divorce (kept for history/the
-- remarriage cooldown), just marked ended. Query "is X married" as
-- (user_a = ? OR user_b = ?) AND ended_at IS NULL - at most one such row
-- can exist per user at a time, enforced in services/marriage.js, not here.
-- Timestamps are always written from JS as new Date().toISOString() (see
-- marriage.js), never SQLite's datetime('now') - that returns a
-- space-separated UTC string with no timezone marker, and Node's Date.parse
-- misreads it as *local* time, silently corrupting every duration computed
-- from it. No DEFAULT here for the same reason - always pass it explicitly.
CREATE TABLE IF NOT EXISTS marriages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_a TEXT NOT NULL,
  user_b TEXT NOT NULL,
  married_at TEXT NOT NULL,
  ended_at TEXT,
  shared_bank INTEGER NOT NULL DEFAULT 0
);

-- Emoji is stored as the raw string Discord gives back on a reaction event
-- (unicode char, or "name:id" for a custom emoji - see reactionRoles.js for
-- exactly how that's normalized both ways). One role per emoji per message;
-- the same emoji can be reused across different messages freely.
CREATE TABLE IF NOT EXISTS reaction_roles (
  guild_id TEXT NOT NULL,
  message_id TEXT NOT NULL,
  emoji TEXT NOT NULL,
  role_id TEXT NOT NULL,
  PRIMARY KEY (message_id, emoji)
);

-- Levels themselves are global (services/levels.js), but the role granted
-- for hitting one is inherently per-guild - a role only exists in one
-- server. Multiple roles can share the same level (PK includes role_id),
-- and hitting level N applies every reward from oldLevel+1 through N, not
-- just the exact level landed on, so crossing several at once (or a level
-- reward added retroactively) doesn't get skipped.
CREATE TABLE IF NOT EXISTS level_roles (
  guild_id TEXT NOT NULL,
  level INTEGER NOT NULL,
  role_id TEXT NOT NULL,
  PRIMARY KEY (guild_id, level, role_id)
);

-- Global, not per-guild - same reasoning as levels/DM preferences. A row
-- existing means that user is currently AFK; cleared (row deleted) the next
-- time they send any message anywhere, not just in the server they went
-- AFK in.
CREATE TABLE IF NOT EXISTS afk_status (
  user_id TEXT PRIMARY KEY,
  reason TEXT,
  since TEXT NOT NULL
);

-- One sticky per channel. message_id is whichever repost is currently the
-- live one - when a new message lands that isn't that repost, the old one
-- gets deleted and a fresh one sent at the bottom, and message_id updates
-- to the new one. last_reposted_at is a small cooldown floor so a very
-- busy channel doesn't delete/resend on every single message.
CREATE TABLE IF NOT EXISTS sticky_messages (
  channel_id TEXT PRIMARY KEY,
  guild_id TEXT NOT NULL,
  content TEXT NOT NULL,
  message_id TEXT,
  last_reposted_at TEXT
);

-- Multiple channels per guild, hence a real join table rather than a single
-- column on some settings row - $mediaonly add/remove just inserts/deletes
-- a row here per channel.
CREATE TABLE IF NOT EXISTS media_only_channels (
  guild_id TEXT NOT NULL,
  channel_id TEXT NOT NULL,
  PRIMARY KEY (guild_id, channel_id)
);

-- Persisted (not in-memory like heist/multiplayerRR) because a giveaway can
-- run for days - a bot restart mid-giveaway must not lose entries or the
-- end time. ready.js resumes/ends overdue ones on boot the same way
-- lockdown.js/lottery.js already do, rather than scheduling a raw setTimeout
-- that would be lost on restart for anything longer than the downtime.
CREATE TABLE IF NOT EXISTS giveaways (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  guild_id TEXT NOT NULL,
  channel_id TEXT NOT NULL,
  message_id TEXT,
  host_id TEXT NOT NULL,
  prize TEXT NOT NULL,
  winner_count INTEGER NOT NULL DEFAULT 1,
  ends_at TEXT NOT NULL,
  ended INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS giveaway_entries (
  giveaway_id INTEGER NOT NULL,
  user_id TEXT NOT NULL,
  PRIMARY KEY (giveaway_id, user_id)
);

-- emoji is stored as the raw display string (a unicode emoji, or the full
-- <:name:id>/<a:name:id> form for a custom one) - see
-- reactionRoles.parseEmojiInput, reused here so the same parsing/matching
-- logic isn't duplicated between reaction roles and starboard.
CREATE TABLE IF NOT EXISTS starboard_settings (
  guild_id TEXT PRIMARY KEY,
  channel_id TEXT NOT NULL,
  threshold INTEGER NOT NULL DEFAULT 3,
  emoji TEXT NOT NULL DEFAULT '⭐',
  block_self_star INTEGER NOT NULL DEFAULT 0,
  min_message_age_seconds INTEGER NOT NULL DEFAULT 0,
  message_template TEXT
);
-- Maps a starred source message to its starboard repost, so re-reacting
-- edits the existing post's star count instead of creating a duplicate.
CREATE TABLE IF NOT EXISTS starboard_posts (
  source_message_id TEXT PRIMARY KEY,
  guild_id TEXT NOT NULL,
  starboard_message_id TEXT NOT NULL
);

-- Per-guild nickname changes over time, for $nickhistory. Global (not
-- per-guild) avatar/banner history lives in the next two tables since those
-- are account-level, not server-level, unlike a nickname.
CREATE TABLE IF NOT EXISTS nickname_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  guild_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  nickname TEXT,
  changed_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS avatar_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL,
  avatar_url TEXT,
  changed_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS banner_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL,
  banner_url TEXT,
  changed_at TEXT NOT NULL
);

-- A rotating (not permanent-once-earned, unlike level_roles) reward: whoever
-- is currently in the top `top_count` of VC time for the given rolling
-- `period` holds `role_id`, recalculated periodically by vcRoleRewards's
-- resync(). vc_role_holders tracks who currently holds the role *because of
-- this system*, so resync only ever adds/removes a role it itself granted -
-- it must never touch the role on someone who has it for an unrelated reason.
CREATE TABLE IF NOT EXISTS vc_role_rewards (
  guild_id TEXT NOT NULL,
  role_id TEXT NOT NULL,
  top_count INTEGER NOT NULL,
  period TEXT NOT NULL,
  PRIMARY KEY (guild_id, role_id)
);
CREATE TABLE IF NOT EXISTS vc_role_holders (
  guild_id TEXT NOT NULL,
  role_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  PRIMARY KEY (guild_id, role_id, user_id)
);

-- Day-bucketed counterpart to message_counts' lifetime total, added solely
-- to power the dashboard activity graph (growth over time) - message_counts
-- itself has no timestamp and can't be reconstructed retroactively, so this
-- only starts accumulating history from whenever it was added, same
-- unavoidable limitation as any "add a new column/table" migration.
CREATE TABLE IF NOT EXISTS message_counts_daily (
  guild_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  day TEXT NOT NULL,
  count INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (guild_id, user_id, day)
);

-- A ticket "type" is a fully separate workflow (its own support roles,
-- target category, channel-name template, button appearance, and opening
-- message) - a single hardcoded ticket flow couldn't cover "moderation
-- report" and "partnership request" wanting completely different staff and
-- presentation on the same server. next_number is per-type (not a single
-- guild-wide counter) so e.g. "mod-report-1" and "partner-1" both start
-- at 1 independently, matching how most ticket bots number things.
-- Dashboard-only now - creation/full customization happens on the Tickets
-- One panel = one postable message with its own set of ticket-type buttons -
-- lets a server run e.g. a single-button "Verify" panel in a verify channel
-- and a separate multi-button "Support" panel (report/staff app/etc)
-- elsewhere, each with its own text/embed and its own posted channel.
-- ticket_types.panel_id scopes which panel a button belongs to. The
-- category/name_template every ticket's channel actually gets created under
-- (the "master ticket channel" config) stays guild-wide on ticket_settings
-- regardless of which panel/button opened it - panels only control what
-- members see and click, not where the resulting channel lands.
CREATE TABLE IF NOT EXISTS ticket_panels (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  guild_id TEXT NOT NULL,
  name TEXT NOT NULL,
  panel_config TEXT, -- JSON {mode, text, embed:{...}} - same rich-message shape as ticket_types.open_config
  channel_id TEXT, -- last channel this panel was posted to - remembered only to pre-select it next time, not auto-updated live
  UNIQUE(guild_id, name)
);
-- page, not via chat command (removed: $tickettype/$ticketpanel/
-- $ticketsetup). $ticket (rename/close/reopen/delete/add/remove/claim/
-- unclaim/open) still manages an already-open ticket channel same as
-- always, regardless of how the type behind it was configured.
-- category_id/name_template/welcome_*/close_message are legacy from the
-- old chat-driven system and unused now - superseded by open_config below
-- (per-type rich message: mode embed/text/v2, live-previewed on the
-- dashboard) and ticket_settings' guild-wide category_id/name_template.
-- Left in place rather than dropped/migrated - SQLite ALTER TABLE DROP
-- COLUMN support is version-dependent and nothing reads these anymore.
CREATE TABLE IF NOT EXISTS ticket_types (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  guild_id TEXT NOT NULL,
  panel_id INTEGER, -- which ticket_panels row this button appears on; backfilled once in src/db/index.js for rows that predate panels (each guild's existing buttons got grouped onto one auto-created "Main" panel)
  name TEXT NOT NULL,
  emoji TEXT,
  category_id TEXT,
  name_template TEXT NOT NULL DEFAULT 'ticket-{username}',
  button_label TEXT,
  button_style TEXT NOT NULL DEFAULT 'Primary',
  welcome_message TEXT,
  welcome_image TEXT,
  welcome_fields TEXT,
  close_message TEXT,
  open_config TEXT, -- JSON {mode:'embed'|'text'|'v2', text, embed:{title,description,color,footer,thumbnail,image,fields}} - what posts (with Claim/Close buttons) when a ticket of this type opens
  next_number INTEGER NOT NULL DEFAULT 1,
  UNIQUE(guild_id, name)
);
-- Support staff for a type - who gets channel access + can claim/close
-- tickets of it, beyond the opener themselves. A join table since a type
-- can have any number of support roles, not just one. `ping` is independent
-- of the role being a support role at all - it just controls whether this
-- role gets @mentioned in the opening message, so an admin can grant
-- access without a ping (or vice versa is NOT possible - only support
-- roles are ever offered as pingable, since pinging someone with no access
-- to the ticket would be pointless).
CREATE TABLE IF NOT EXISTS ticket_type_roles (
  ticket_type_id INTEGER NOT NULL,
  role_id TEXT NOT NULL,
  ping INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (ticket_type_id, role_id)
);
-- One row per opened ticket (channel), independent of its type surviving -
-- if a type is later deleted, existing tickets already created from it keep
-- their own row/history rather than cascading away.
CREATE TABLE IF NOT EXISTS tickets (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  guild_id TEXT NOT NULL,
  ticket_type_id INTEGER NOT NULL,
  channel_id TEXT NOT NULL,
  opener_id TEXT NOT NULL,
  claimed_by TEXT,
  status TEXT NOT NULL DEFAULT 'open',
  number INTEGER NOT NULL,
  created_at TEXT NOT NULL,
  closed_at TEXT,
  closed_by TEXT
);

-- A closed/deleted/force-closed ticket's full transcript, stored here
-- instead of only ever existing as a Discord attachment - Discord's own CDN
-- attachment URLs carry a signed expiry and eventually stop working, so a
-- link that has to survive as a reference is better served by the
-- dashboard's own /guild/:id/tickets/transcripts/:id route reading this
-- table than by linking straight to Discord's CDN. content is the same
-- plain-text `[HH:MM:SS] username: content [attachment urls]` format either
-- transcript builder (tickets.js's gateway version, lockdown.js's REST
-- version for force-close) produces.
CREATE TABLE IF NOT EXISTS ticket_transcripts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  guild_id TEXT NOT NULL,
  ticket_id INTEGER NOT NULL,
  ticket_number INTEGER NOT NULL,
  content TEXT NOT NULL,
  created_at TEXT NOT NULL
);

-- Guild-wide ticket settings, separate from per-type ticket_types and
-- per-panel ticket_panels since these apply across every panel/type (where
-- every ticket's channel gets created, and where/whether closed tickets get
-- transcript-logged) rather than being one panel's or type's own workflow.
-- panel_title/panel_description/panel_image/panel_fields/panel_config are
-- legacy/unused now that a guild can have multiple panels (ticket_panels,
-- each with its own panel_config) instead of one shared one.
CREATE TABLE IF NOT EXISTS ticket_settings (
  guild_id TEXT PRIMARY KEY,
  panel_title TEXT,
  panel_description TEXT,
  panel_image TEXT,
  panel_fields TEXT,
  panel_config TEXT, -- legacy - see comment above; superseded by ticket_panels.panel_config
  category_id TEXT, -- every new ticket channel is created under this category, regardless of which panel/type opened it
  name_template TEXT NOT NULL DEFAULT '{tickettype}-{username}', -- {tickettype}/{username}/{number} - channel name for every new ticket
  log_channel_id TEXT,
  transcripts_enabled INTEGER NOT NULL DEFAULT 1,
  max_panel_buttons INTEGER NOT NULL DEFAULT 25,
  max_open_per_user INTEGER NOT NULL DEFAULT 0 -- 0 = unlimited; caps open tickets across ALL types combined, not per-type
);

-- A birthday is an account-level fact (one per person, not one per server),
-- same reasoning as avatar/banner history being global rather than
-- per-guild - so this is keyed on user_id alone. year is optional (not
-- everyone wants their age known); month/day are required for the actual
-- announcement to work at all.
CREATE TABLE IF NOT EXISTS birthdays (
  user_id TEXT PRIMARY KEY,
  month INTEGER NOT NULL,
  day INTEGER NOT NULL,
  year INTEGER,
  set_at TEXT NOT NULL
);
-- Where a server wants birthdays announced - per-guild, unlike the birthday
-- itself, since two servers a person is in shouldn't be forced to share one
-- announcement channel.
CREATE TABLE IF NOT EXISTS birthday_settings (
  guild_id TEXT PRIMARY KEY,
  channel_id TEXT NOT NULL,
  message TEXT
);
-- Guards against re-announcing the same person's birthday every time the
-- hourly check runs during their birthday's 24 hours - one row per
-- guild+user+year, checked before sending.
CREATE TABLE IF NOT EXISTS birthday_announced (
  guild_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  year INTEGER NOT NULL,
  PRIMARY KEY (guild_id, user_id, year)
);

-- A role menu is a select-menu alternative to reaction roles - defined
-- (name/title/placeholder/mode) before it's ever posted, same
-- create-then-configure-then-post shape as ticket_types/$ticketpanel.
-- message_id is nullable because a menu can exist configured but not yet
-- posted anywhere.
CREATE TABLE IF NOT EXISTS role_menus (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  guild_id TEXT NOT NULL,
  name TEXT NOT NULL,
  channel_id TEXT,
  message_id TEXT,
  title TEXT NOT NULL DEFAULT 'Choose your roles',
  placeholder TEXT NOT NULL DEFAULT 'Select role(s)...',
  mode TEXT NOT NULL DEFAULT 'multi',
  UNIQUE(guild_id, name)
);
-- The options that appear in the select menu - each one maps to a role, in
-- the order they were added (rowid order).
CREATE TABLE IF NOT EXISTS role_menu_options (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  role_menu_id INTEGER NOT NULL,
  role_id TEXT NOT NULL,
  label TEXT NOT NULL,
  emoji TEXT,
  description TEXT,
  UNIQUE(role_menu_id, role_id)
);

-- Message-activity counterpart to vc_role_rewards/vc_role_holders - same
-- rotating top-N-for-a-rolling-period reward, just ranked by
-- message_counts_daily instead of vc_sessions. Kept as its own pair of
-- tables (not a shared "activity_role_rewards" with a metric column) so a
-- server can run a VC reward and a message reward side by side without the
-- two colliding on the same (guild_id, role_id) primary key.
CREATE TABLE IF NOT EXISTS message_role_rewards (
  guild_id TEXT NOT NULL,
  role_id TEXT NOT NULL,
  top_count INTEGER NOT NULL,
  period TEXT NOT NULL,
  PRIMARY KEY (guild_id, role_id)
);
CREATE TABLE IF NOT EXISTS message_role_holders (
  guild_id TEXT NOT NULL,
  role_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  PRIMARY KEY (guild_id, role_id, user_id)
);

-- Where $confess posts anonymous submissions - next_number is a per-guild
-- running counter so each confession gets a stable "#N" label instead of
-- just a timestamp, matching how most confession bots number them.
CREATE TABLE IF NOT EXISTS confession_settings (
  guild_id TEXT PRIMARY KEY,
  channel_id TEXT NOT NULL,
  next_number INTEGER NOT NULL DEFAULT 1
);

-- A role grant with a built-in expiry - generalizes the "auto-expiring
-- grant" pattern lockdown/mutes/giveaways already use to an arbitrary role
-- instead of something purpose-built. UNIQUE so re-running $temprole on the
-- same user+role just updates the expiry instead of creating a duplicate
-- tracked grant.
CREATE TABLE IF NOT EXISTS temp_roles (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  guild_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  role_id TEXT NOT NULL,
  expires_at TEXT NOT NULL,
  UNIQUE(guild_id, user_id, role_id)
);

-- One jail role + one jail channel per guild ($jailsetup). $jailupdate
-- re-syncs every channel's permission overwrites against these whenever a
-- new channel gets added later.
CREATE TABLE IF NOT EXISTS jail_settings (
  guild_id TEXT PRIMARY KEY,
  role_id TEXT NOT NULL,
  channel_id TEXT NOT NULL
);

-- One row per currently-jailed member - role_ids is a JSON array of every
-- role they held right before $jail stripped them, so $unjail can restore
-- them exactly (skipping any that no longer exist by then).
CREATE TABLE IF NOT EXISTS jailed_members (
  guild_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  role_ids TEXT NOT NULL,
  reason TEXT,
  jailed_by TEXT NOT NULL,
  jailed_at TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (guild_id, user_id)
);

-- A snapshot of a guild's role/channel/permission structure, stored as one
-- JSON blob rather than normalized tables - this is written once and read
-- back whole on restore, never queried piecemeal, so normalizing it would
-- only add joins for no benefit.
CREATE TABLE IF NOT EXISTS server_backups (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  guild_id TEXT NOT NULL,
  created_by TEXT NOT NULL,
  created_at TEXT NOT NULL,
  data TEXT NOT NULL
);

-- A user's self-reported IANA timezone (e.g. "America/New_York") - global
-- like avatar/banner history and birthdays, since a timezone is a fact
-- about the person, not about any one server they're in.
CREATE TABLE IF NOT EXISTS user_timezones (
  user_id TEXT PRIMARY KEY,
  timezone TEXT NOT NULL
);

-- Reacting with the configured emoji relays a message to a staff channel -
-- same per-guild settings shape as starboard_settings, but for reports
-- instead of popularity.
CREATE TABLE IF NOT EXISTS report_settings (
  guild_id TEXT PRIMARY KEY,
  channel_id TEXT NOT NULL,
  emoji TEXT NOT NULL DEFAULT '🚩'
);
-- One row per (message, reporter) - stops the same person spamming reports
-- on the same message by removing and re-adding their reaction, without
-- limiting how many *different* people can report the same message (each
-- one is a real, distinct report worth staff seeing).
CREATE TABLE IF NOT EXISTS report_entries (
  message_id TEXT NOT NULL,
  reporter_id TEXT NOT NULL,
  PRIMARY KEY (message_id, reporter_id)
);

-- The auto-created "Image Muted" role's id, cached per guild so $imute
-- doesn't recreate a duplicate role (and re-apply overwrites to every
-- channel) every single time it's used - created once, reused after.
CREATE TABLE IF NOT EXISTS imute_settings (
  guild_id TEXT PRIMARY KEY,
  role_id TEXT NOT NULL
);

-- A recurring $nuke on a timer - next_run_at rather than a raw setTimeout,
-- same "resume/poll" reasoning as every other scheduled feature in this
-- bot, so a bot restart never loses or duplicates a cycle.
CREATE TABLE IF NOT EXISTS autonuke_settings (
  guild_id TEXT NOT NULL,
  channel_id TEXT NOT NULL,
  interval_ms INTEGER NOT NULL,
  next_run_at TEXT NOT NULL,
  PRIMARY KEY (guild_id, channel_id)
);

-- Admin-visibility feed of every real bank movement (services/bankLog.js) -
-- personal bank (economy_balances.bank_balance) and marriage shared bank
-- alike. Only actual events (deposit/withdraw/theft/payout) get a row -
-- deliberately NOT the daily bank-interest tick, which would otherwise
-- flood this with a row per interest-bearing account per day for a passive
-- compounding nobody asked to see. `direction` is relative to the bank
-- itself (in = money entering it, out = leaving), not the user.
CREATE TABLE IF NOT EXISTS bank_transactions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL,
  account_type TEXT NOT NULL, -- 'personal' | 'shared' (marriage)
  direction TEXT NOT NULL, -- 'in' | 'out'
  amount INTEGER NOT NULL,
  reason TEXT NOT NULL, -- 'deposit' | 'withdraw' | 'heist_theft' | 'divorce_split' | 'anniversary_bonus'
  related_user_id TEXT, -- heist starter, or the other spouse, when relevant
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_bank_transactions_created ON bank_transactions (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_bank_transactions_user ON bank_transactions (user_id, created_at DESC);

-- Admin-visibility feed of high-value fish/hunt/dig/scavenge catches
-- (services/notableCatches.js) - only catches clearing the 100k threshold
-- get a row, so this stays a small "notable events" table, not a general
-- catch-history log (user_catches already tracks current inventory).
CREATE TABLE IF NOT EXISTS notable_catches (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL,
  catch_id TEXT NOT NULL,
  name TEXT NOT NULL,
  rarity TEXT NOT NULL,
  activity TEXT NOT NULL, -- 'fish' | 'hunt' | 'dig' | 'scavenge'
  value INTEGER NOT NULL,
  caught_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_notable_catches_caught ON notable_catches (caught_at DESC);

-- Heuristic auto-farm/auto-gamble detections (services/macroDetection.js) -
-- flags a user repeatedly running the SAME command with suspiciously
-- consistent (low-variance) timing between uses, the signature of a script
-- driven by a timer rather than a human's naturally uneven reaction/typing
-- time. A live behavioral heuristic, not proof - false positives are
-- possible, so this is visibility for the owner to review, not an
-- auto-punishment trigger.
CREATE TABLE IF NOT EXISTS macro_detections (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL,
  guild_id TEXT,
  category TEXT NOT NULL, -- 'farm' | 'gamble'
  command_name TEXT NOT NULL,
  sample_count INTEGER NOT NULL,
  avg_interval_ms INTEGER NOT NULL,
  stddev_ms INTEGER NOT NULL,
  detected_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_macro_detections_detected ON macro_detections (detected_at DESC);
CREATE INDEX IF NOT EXISTS idx_macro_detections_user ON macro_detections (user_id, detected_at DESC);

-- Idle/incremental generator economy, ported over from the test-bot
-- prototype - production/generators/dig payouts all pay into the REAL
-- $balance (economy_balances, via services/economy.js), not a separate pool.
-- This table only holds the generator-specific progression state that has
-- no equivalent in economy.js.
--
-- prestige_run_earned is scoped to "earned since the last prestige" (what
-- the next prestige's payout is computed from, then reset to 0 on
-- prestiging) - deliberately a SEPARATE counter from economy_balances'
-- own lifetime_earned (that one is permanent, all-time, dashboard net-worth
-- material, and must never be reset just because someone prestiged their
-- generators). prestige_points/prestige_count never reset - the permanent
-- multiplier and a display stat. prestige_currency is the SPENDABLE twin of
-- prestige_points (same gain on prestige, only currency decreases via
-- $pshop, so spending can't lower the passive multiplier).
-- auto_collect/bulk_confirm_bonus/synergy_range are one-time $pshop unlocks.
-- last_active_at tracks the most recent message/voice-presence activity
-- signal - production only clamps to the offline cap once fully inactive
-- for a while, see services/idle.js's effectiveElapsedMinutes.
CREATE TABLE IF NOT EXISTS idle_state (
  user_id TEXT PRIMARY KEY,
  prestige_run_earned INTEGER NOT NULL DEFAULT 0,
  prestige_points INTEGER NOT NULL DEFAULT 0,
  prestige_count INTEGER NOT NULL DEFAULT 0,
  prestige_currency INTEGER NOT NULL DEFAULT 0,
  auto_collect INTEGER NOT NULL DEFAULT 0,
  bulk_confirm_bonus INTEGER NOT NULL DEFAULT 0,
  synergy_range INTEGER NOT NULL DEFAULT 1,
  last_collected_at INTEGER NOT NULL DEFAULT (unixepoch() * 1000),
  last_active_at INTEGER NOT NULL DEFAULT (unixepoch() * 1000),
  last_dig_at INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS idle_generators (
  user_id TEXT NOT NULL,
  generator_id TEXT NOT NULL,
  owned INTEGER NOT NULL DEFAULT 0,
  upgrade_level INTEGER NOT NULL DEFAULT 1,
  PRIMARY KEY (user_id, generator_id)
);
