db-setup.sql

-- Hub database setup — forestbot_hub (MariaDB)
-- Safe to re-run: all statements use IF NOT EXISTS / INSERT IGNORE.
-- Add new tables/columns at the bottom with a dated comment.
-- Schema sourced from prod 2026-06-28.

-- ── Auth ──────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS api_keys (
    Api_key         VARCHAR(255) NOT NULL PRIMARY KEY,
    OwnerEmail      VARCHAR(255) NOT NULL,
    CreatedAt       BIGINT(20)   NOT NULL,
    UpdatedAt       BIGINT(20)   NOT NULL,
    ReadPermission  TINYINT(4)   NOT NULL,
    WritePermission TINYINT(4)   NOT NULL,
    RateLimit       INT(11)      NOT NULL,
    TokenType       VARCHAR(255) NOT NULL
);

-- ── Minecraft stats ───────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS users (
    username               VARCHAR(50)          DEFAULT NULL,
    kills                  INT(11) UNSIGNED     DEFAULT 0,
    deaths                 INT(11) UNSIGNED     DEFAULT 0,
    joindate               VARCHAR(50)          DEFAULT '0',
    lastseen               VARCHAR(50)          DEFAULT NULL,
    uuid                   VARCHAR(120)         DEFAULT NULL,
    playtime               BIGINT(255) UNSIGNED DEFAULT 0,
    joins                  INT(11) UNSIGNED     DEFAULT 1,
    leaves                 INT(11) UNSIGNED     DEFAULT 0,
    lastdeathTime          BIGINT(20)           DEFAULT 0,
    lastdeathString        VARCHAR(120)         DEFAULT NULL,
    mc_server              VARCHAR(50)          DEFAULT NULL,
    greeting               TEXT                 DEFAULT NULL,
    greeting_last_fired_at DATETIME             DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS messages (
    name      VARCHAR(256) DEFAULT NULL,
    message   VARCHAR(256) DEFAULT NULL,
    date      VARCHAR(50)  DEFAULT NULL,
    mc_server VARCHAR(50)  DEFAULT NULL,
    uuid      VARCHAR(156) DEFAULT '0',
    INDEX idx_name      (name),
    INDEX idx_mc_server (mc_server)
);

CREATE TABLE IF NOT EXISTS advancements (
    id         INT(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username   VARCHAR(50)  DEFAULT NULL,
    advancement VARCHAR(255) DEFAULT NULL,
    time       BIGINT(20)   DEFAULT NULL,
    mc_server  VARCHAR(50)  DEFAULT NULL,
    uuid       VARCHAR(300) DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS deaths (
    id           INT(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    victim       VARCHAR(50)  DEFAULT NULL,
    death_message VARCHAR(150) DEFAULT NULL,
    murderer     VARCHAR(150) DEFAULT NULL,
    time         BIGINT(20)   DEFAULT NULL,
    type         VARCHAR(50)  DEFAULT NULL,
    mc_server    VARCHAR(50)  DEFAULT NULL,
    victimUUID   VARCHAR(300) DEFAULT NULL,
    murdererUUID VARCHAR(300) DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS sessions (
    username             VARCHAR(50) DEFAULT NULL,
    mc_server            VARCHAR(50) DEFAULT NULL,
    join_time            VARCHAR(50) DEFAULT NULL,
    leave_time           VARCHAR(50) DEFAULT NULL,
    playtime             INT(11)     DEFAULT NULL,
    kills                INT(11)     DEFAULT NULL,
    deaths               INT(11)     DEFAULT NULL,
    advancements_gained  INT(11)     DEFAULT NULL,
    messages_sent        INT(11)     DEFAULT NULL,
    uuid                 VARCHAR(50) DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS server_player_counts (
    count     INT(11)     DEFAULT NULL,
    mc_server VARCHAR(50) DEFAULT NULL,
    timestamp VARCHAR(50) DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS fadv_awards (
    uuid       VARCHAR(36) NOT NULL,
    mc_server  VARCHAR(64) NOT NULL,
    fadv_id    VARCHAR(64) NOT NULL,
    awarded_at DATETIME    DEFAULT current_timestamp(),
    PRIMARY KEY (uuid, mc_server, fadv_id)
);

-- ── Minecraft content ─────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS faq (
    id        INT(11)      NOT NULL AUTO_INCREMENT,
    username  VARCHAR(50)  DEFAULT NULL,
    uuid      VARCHAR(100) DEFAULT NULL,
    server    VARCHAR(100) DEFAULT NULL,
    faq       VARCHAR(255) DEFAULT NULL,
    timestamp VARCHAR(255) DEFAULT NULL,
    INDEX idx_id (id)
);

CREATE TABLE IF NOT EXISTS whois (
    username    VARCHAR(50)  NOT NULL DEFAULT '' PRIMARY KEY,
    description VARCHAR(255) DEFAULT NULL,
    timestamp   BIGINT(50)   DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS facts (
    id        INT(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username  VARCHAR(50)  DEFAULT NULL,
    fact      VARCHAR(255) DEFAULT NULL,
    date      BIGINT(20)   DEFAULT NULL,
    mc_server VARCHAR(50)  DEFAULT NULL
);

-- ── Discord integration ───────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS guilds (
    guild_id   VARCHAR(50)    NOT NULL DEFAULT '0' PRIMARY KEY,
    channel_id VARCHAR(50)    DEFAULT NULL,
    mc_server  VARCHAR(50)    DEFAULT NULL,
    setup_by   VARCHAR(50)    DEFAULT NULL,
    created_at BIGINT(233)    DEFAULT NULL,
    guild_name VARCHAR(50)    DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS livechats (
    channelID VARCHAR(100) NOT NULL DEFAULT '0' PRIMARY KEY,
    guildName VARCHAR(100) NOT NULL DEFAULT '0',
    guildID   VARCHAR(100) NOT NULL DEFAULT '0',
    setupBy   VARCHAR(100) DEFAULT NULL,
    date      VARCHAR(200) DEFAULT NULL,
    mc_server VARCHAR(200) DEFAULT NULL
);

-- ── Cache ─────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS top_cache (
    cache_key     VARCHAR(128) NOT NULL PRIMARY KEY,
    result        LONGTEXT     NOT NULL,
    calculated_at BIGINT(20)   NOT NULL
);

-- ── Tradebot ──────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS trades (
    id                  INT(11)     NOT NULL AUTO_INCREMENT PRIMARY KEY,
    initiator_id        VARCHAR(36) NOT NULL,
    recipient_id        VARCHAR(36) NOT NULL,
    description         TEXT        NOT NULL,
    status              VARCHAR(16) DEFAULT 'pending',
    guild_id            VARCHAR(32) NOT NULL,
    channel_id          VARCHAR(32) NOT NULL,
    initiator_confirmed TINYINT(1)  DEFAULT 0,
    recipient_confirmed TINYINT(1)  DEFAULT 0,
    created_at          BIGINT(20)  NOT NULL DEFAULT 0,
    confirmed_at        BIGINT(20)  DEFAULT NULL,
    expires_at          BIGINT(20)  DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS trade_reports (
    id               INT(11)     NOT NULL AUTO_INCREMENT PRIMARY KEY,
    trade_id         INT(11)     DEFAULT NULL,
    reporter_id      VARCHAR(64) NOT NULL,
    reported_user_id VARCHAR(64) NOT NULL,
    reason           VARCHAR(64) NOT NULL,
    description      TEXT        DEFAULT NULL,
    status           VARCHAR(16) DEFAULT 'pending',
    resolved_by      VARCHAR(32) DEFAULT NULL,
    guild_id         VARCHAR(32) NOT NULL,
    created_at       BIGINT(20)  NOT NULL DEFAULT 0,
    resolved_at      BIGINT(20)  DEFAULT NULL,
    INDEX idx_trade_id (trade_id)
);

CREATE TABLE IF NOT EXISTS user_links (
    id         INT(11)     NOT NULL AUTO_INCREMENT PRIMARY KEY,
    discord_id VARCHAR(30) NOT NULL UNIQUE,
    mc_uuid    VARCHAR(36) NOT NULL UNIQUE,
    linked_at  BIGINT(20)  NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS mod_actions (
    id           INT(11)     NOT NULL AUTO_INCREMENT PRIMARY KEY,
    type         VARCHAR(16) NOT NULL,
    user_id      VARCHAR(64) NOT NULL,
    moderator_id VARCHAR(64) NOT NULL,
    reason       TEXT        NOT NULL,
    guild_id     VARCHAR(32) NOT NULL,
    created_at   BIGINT(20)  NOT NULL DEFAULT 0,
    INDEX idx_user_id (user_id)
);

CREATE TABLE IF NOT EXISTS tradebot_config (
    `key`  VARCHAR(64) NOT NULL PRIMARY KEY,
    value  TEXT        NOT NULL
);

CREATE TABLE IF NOT EXISTS discord_usernames (
    user_id    VARCHAR(32) NOT NULL PRIMARY KEY,
    username   VARCHAR(64) NOT NULL,
    updated_at BIGINT(20)  NOT NULL DEFAULT 0
);

-- ── Casino ────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS casino_balance (
    player_uuid  VARCHAR(36) NOT NULL PRIMARY KEY,
    chips        INT(11)     NOT NULL DEFAULT 1000,
    streak       INT(11)     NOT NULL DEFAULT 0,
    last_claim   BIGINT(20)  DEFAULT NULL,
    last_scratch BIGINT(20)  DEFAULT NULL,
    updated_at   BIGINT(20)  NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS casino_jackpot (
    id        INT(11)   NOT NULL PRIMARY KEY,
    pot       INT(11)   NOT NULL DEFAULT 0,
    last_draw BIGINT(20) DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS casino_jackpot_tickets (
    player_uuid  VARCHAR(36) NOT NULL PRIMARY KEY,
    ticket_count INT(11)     NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS casino_lotto_pot (
    id  INT(11)    NOT NULL PRIMARY KEY,
    pot BIGINT(20) NOT NULL DEFAULT 1000
);

CREATE TABLE IF NOT EXISTS casino_lotto_tickets (
    id          INT(11)     NOT NULL AUTO_INCREMENT PRIMARY KEY,
    player_uuid VARCHAR(36) NOT NULL,
    numbers     VARCHAR(32) NOT NULL,
    draw_date   BIGINT(20)  NOT NULL DEFAULT 0,
    created_at  BIGINT(20)  NOT NULL DEFAULT 0,
    INDEX idx_player_uuid (player_uuid),
    INDEX idx_draw_date   (draw_date)
);

CREATE TABLE IF NOT EXISTS casino_lotto_draws (
    id        INT(11)    NOT NULL AUTO_INCREMENT PRIMARY KEY,
    draw_date BIGINT(20) NOT NULL DEFAULT 0,
    numbers   VARCHAR(32) NOT NULL,
    draw_time BIGINT(20) NOT NULL DEFAULT 0,
    INDEX idx_draw_date (draw_date)
);

CREATE TABLE IF NOT EXISTS casino_notifications (
    id          INT(11)     NOT NULL AUTO_INCREMENT PRIMARY KEY,
    player_uuid VARCHAR(36) NOT NULL,
    message     TEXT        NOT NULL,
    created_at  BIGINT(20)  NOT NULL DEFAULT 0,
    INDEX idx_player_uuid (player_uuid)
);

-- ── Market bets (2026-06-28) ──────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS market_bets (
    id             INT(11)                NOT NULL AUTO_INCREMENT PRIMARY KEY,
    player_uuid    VARCHAR(36)            NOT NULL,
    symbol         VARCHAR(16)            NOT NULL,
    market         ENUM('stock','crypto') NOT NULL,
    direction      ENUM('long','short')   NOT NULL,
    entry_price    DOUBLE                 NOT NULL,
    stake          BIGINT(20)             NOT NULL,
    closes_unix    BIGINT(20) UNSIGNED    NOT NULL,
    duration_label VARCHAR(8)             NOT NULL,
    created_at     BIGINT(20)             NOT NULL DEFAULT 0,
    INDEX idx_player_uuid (player_uuid)
);

-- ── Weather bets (2026-07-01) ────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS weather_bets (
    id             INT(11)              NOT NULL AUTO_INCREMENT PRIMARY KEY,
    player_uuid    VARCHAR(36)          NOT NULL,
    bet_type       VARCHAR(8)           NOT NULL DEFAULT 'rain',
    city           VARCHAR(128)         NOT NULL,
    latitude       DOUBLE               NOT NULL,
    longitude      DOUBLE               NOT NULL,
    direction      VARCHAR(8)           NOT NULL,
    threshold      DOUBLE               DEFAULT NULL,
    unit           VARCHAR(16)          DEFAULT NULL,
    forecast_prob  TINYINT UNSIGNED     NOT NULL,
    payout_mult    DOUBLE               NOT NULL,
    stake          BIGINT(20)           NOT NULL,
    closes_unix    BIGINT(20) UNSIGNED  NOT NULL,
    duration_label VARCHAR(8)           NOT NULL,
    created_at     BIGINT(20)           NOT NULL DEFAULT 0,
    INDEX idx_player_uuid (player_uuid)
);

-- ── Portfolio positions (2026-06-28) ─────────────────────────────────────────

CREATE TABLE IF NOT EXISTS portfolio_positions (
    id          INT(11)                NOT NULL AUTO_INCREMENT PRIMARY KEY,
    player_uuid VARCHAR(36)            NOT NULL,
    symbol      VARCHAR(16)            NOT NULL,
    market      ENUM('stock','crypto') NOT NULL,
    entry_price DOUBLE                 NOT NULL,
    stake       BIGINT(20)             NOT NULL,
    opened_unix BIGINT(20) UNSIGNED    NOT NULL,
    created_at  BIGINT(20)             NOT NULL DEFAULT 0,
    INDEX idx_player_uuid (player_uuid)
);

-- ── Weather bets temp/wind columns (2026-07-01) ──────────────────────────────
-- Adds bet_type, threshold, unit; widens direction from ENUM to VARCHAR(8).
-- Safe on existing DBs (IF NOT EXISTS).

ALTER TABLE weather_bets MODIFY COLUMN direction VARCHAR(8) NOT NULL;
ALTER TABLE weather_bets ADD COLUMN IF NOT EXISTS bet_type VARCHAR(8) NOT NULL DEFAULT 'rain' AFTER player_uuid;
ALTER TABLE weather_bets ADD COLUMN IF NOT EXISTS threshold DOUBLE DEFAULT NULL AFTER direction;
ALTER TABLE weather_bets ADD COLUMN IF NOT EXISTS unit VARCHAR(16) DEFAULT NULL AFTER threshold;

-- ── Duel wins column (2026-06-28) ────────────────────────────────────────────
-- Added to users table for duel win tracking. Safe on existing DBs.

ALTER TABLE users ADD COLUMN IF NOT EXISTS duel_wins INT(11) UNSIGNED NOT NULL DEFAULT 0;

-- ── Seed rows ─────────────────────────────────────────────────────────────────
-- Singleton tables need exactly one row to function.

INSERT IGNORE INTO casino_jackpot (id, pot) VALUES (1, 7000);
INSERT IGNORE INTO casino_lotto_pot (id, pot) VALUES (1, 1000);

-- ── Event bets (2026-07-04) ──────────────────────────────────────────────────
-- Unified table for all async-settle event bets.
-- bet_type: "kalshi" | "faa" | "noaa" | "train" | "nasa" | "sports"
-- Type-specific columns are NULL when not applicable to that bet_type.

CREATE TABLE IF NOT EXISTS casino_event_bets (
    id           INT(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    uuid         VARCHAR(36)  NOT NULL,
    bet_type     VARCHAR(32)  NOT NULL,
    side         VARCHAR(16)           DEFAULT NULL,
    price        DOUBLE       NOT NULL,
    stake        INT(11)      NOT NULL,
    close_at     BIGINT(20)   NOT NULL,
    created_at   BIGINT(20)   NOT NULL,
    settled_at   BIGINT(20)            DEFAULT NULL,
    -- kalshi
    ticker       VARCHAR(128)          DEFAULT NULL,
    title        VARCHAR(256)          DEFAULT NULL,
    -- faa
    airport_code VARCHAR(8)            DEFAULT NULL,
    airport_name VARCHAR(128)          DEFAULT NULL,
    -- noaa
    location     VARCHAR(64)           DEFAULT NULL,
    latitude     DOUBLE                DEFAULT NULL,
    longitude    DOUBLE                DEFAULT NULL,
    -- train
    country      VARCHAR(256)          DEFAULT NULL,
    train_code   VARCHAR(64)           DEFAULT NULL,
    train_name   VARCHAR(256)          DEFAULT NULL,
    -- nasa (nasa_subtype = CME | FLR | GST)
    nasa_subtype VARCHAR(32)           DEFAULT NULL,
    -- sports
    event_id     VARCHAR(128)          DEFAULT NULL,
    sport        VARCHAR(64)           DEFAULT NULL,
    home_team    VARCHAR(128)          DEFAULT NULL,
    away_team    VARCHAR(128)          DEFAULT NULL,
    won          TINYINT(1)            DEFAULT NULL,
    INDEX idx_uuid     (uuid),
    INDEX idx_bet_type (bet_type),
    INDEX idx_settled  (settled_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- 2026-07-04: quake + volcano bets (routes: /casino/quake-bet, /casino/volcano-bet)
-- No schema change - reuse existing casino_event_bets columns:
--   quake:   bet_type='quake' | country=region_slug | train_code=region_slug | train_name=display | nasa_subtype=mag(str)
--   volcano: bet_type='volcano' | country=vnum | train_code=vnum | train_name=vname

-- 2026-07-05: AQI bets (routes: /casino/aqi-bet, /casino/aqi-bets, /casino/aqi-bet/:id)
-- No schema change - reuse existing casino_event_bets columns:
--   aqi: bet_type='aqi' | location=zip | train_name=area_name

-- 2026-07-05: Launch bets (routes: /casino/launch-bet, /casino/launch-bets, /casino/launch-bet/:id)
-- No schema change - reuse existing casino_event_bets columns:
--   launch: bet_type='launch' | location=launch_uuid | train_name=launch_name
--           country=lsp_name | train_code=lsp_id | nasa_subtype=window_start (unix)

-- 2026-07-05: Gas price bets (routes: /casino/gas-bet, /casino/gas-bets, /casino/gas-bet/:id)
-- No schema change - reuse existing casino_event_bets columns:
--   gas: bet_type='gas' | location=zip | train_name=region_display | latitude=baseline_price ($/gal)

-- ── Marriage / alimony (2026-07-17) ──────────────────────────────────────────
-- Alimony: 1% of gross win per active ex, capped at 90% of that win, shortfall
-- carries into `alimony_debt.owed` and drains from future wins until alimony_until.
-- Marriage is poly-safe: a player can appear in multiple concurrent active rows.

CREATE TABLE IF NOT EXISTS marriages (
    id                       INT(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    player_uuid              VARCHAR(36)  NOT NULL,
    spouse_uuid              VARCHAR(36)  NOT NULL,
    married_at               BIGINT(20)   NOT NULL,
    divorced_at              BIGINT(20)            DEFAULT NULL,
    divorce_type             VARCHAR(8)            DEFAULT NULL,  -- 'mutual' | 'forced'
    -- Set to the initiating player's uuid while waiting on the other party to also
    -- call !divorce for a mutual split; cleared once both sides have confirmed.
    mutual_divorce_requester VARCHAR(36)           DEFAULT NULL,
    INDEX idx_player (player_uuid),
    INDEX idx_spouse (spouse_uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS marriage_proposals (
    id            INT(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    proposer_uuid VARCHAR(36)  NOT NULL,
    target_uuid   VARCHAR(36)  NOT NULL,
    dowry         BIGINT(20)   NOT NULL DEFAULT 0,
    state         VARCHAR(8)   NOT NULL DEFAULT 'pending',  -- 'pending' | 'counter'
    created_at    BIGINT(20)   NOT NULL,
    expires_at    BIGINT(20)   NOT NULL,
    INDEX idx_proposer (proposer_uuid),
    INDEX idx_target   (target_uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS alimony_debt (
    id            INT(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    debtor_uuid   VARCHAR(36)  NOT NULL,
    payee_uuid    VARCHAR(36)  NOT NULL,
    owed          BIGINT(20)   NOT NULL DEFAULT 0,
    alimony_until BIGINT(20)   NOT NULL,
    created_at    BIGINT(20)   NOT NULL,
    updated_at    BIGINT(20)   NOT NULL,
    UNIQUE KEY unique_pair (debtor_uuid, payee_uuid),
    INDEX idx_debtor (debtor_uuid),
    INDEX idx_until  (alimony_until)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 2026-07-18: marriages/marriage_proposals merged down to one table (2 tables
-- total for the marriage/alimony feature, was 3) — a proposal and a marriage
-- are just different lifecycle states of the same (player, spouse) pair.
-- alimony_debt deliberately NOT merged in: it's a directional debt ledger
-- (debtor -> payee) that outlives/spans marriages, not marriage-record shaped.
-- Safe to re-run: ADD COLUMN IF NOT EXISTS / DROP TABLE IF EXISTS are no-ops
-- once already applied, and this never drops `marriages` itself.

DROP TABLE IF EXISTS marriage_proposals;

ALTER TABLE marriages
    ADD COLUMN IF NOT EXISTS status      VARCHAR(8)  NOT NULL DEFAULT 'married' AFTER spouse_uuid,
    ADD COLUMN IF NOT EXISTS dowry       BIGINT(20)  NOT NULL DEFAULT 0          AFTER status,
    ADD COLUMN IF NOT EXISTS proposed_at BIGINT(20)  NOT NULL DEFAULT 0          AFTER dowry,
    ADD COLUMN IF NOT EXISTS expires_at  BIGINT(20)           DEFAULT NULL       AFTER proposed_at,
    MODIFY COLUMN married_at BIGINT(20) DEFAULT NULL;  -- was NOT NULL; pending/counter rows have no married_at yet
-- status: 'pending' | 'counter' | 'married' | 'divorced'

-- One-time incident, already fixed manually on the live DB, NOT something this
-- script handles: a stale, unrelated `marriages` table (player1_uuid/
-- player2_uuid, alimony baked onto the row, origin predates this feature,
-- unknown) already occupied the name, so the original `CREATE TABLE IF NOT
-- EXISTS marriages` above silently no-op'd instead of creating the real one —
-- IF NOT EXISTS means "don't error if present," not "match this definition."
-- Confirmed empty and manually DROPPED + recreated with the schema above. If
-- you ever see "ALTER TABLE marriages fails, unknown column" again, that's
-- the symptom — check `SHOW CREATE TABLE marriages` before assuming this
-- script itself is broken.

-- 2026-07-21 incident: `alimony_debt` (defined above since 2026-07-17) was
-- never actually created on the live NUC DB — code/schema were committed and
-- the universal casino_win() swap shipped across every game, but this script
-- was never re-run against prod after that point. Every real-money win Hub-
-- side (`casinoWin.ts`, all games) started 500ing with `ER_NO_SUCH_TABLE:
-- Table 'forestbot_hub.alimony_debt' doesn't exist` — first surfaced via a
-- train bet payout failure. No fix needed here; the CREATE TABLE above is
-- already correct and IF NOT EXISTS-safe, this script just needed to actually
-- run. Lesson: a schema change landing in this file is not the same as it
-- being applied — confirm this script re-ran against prod after each commit
-- that adds/alters a table here, don't assume the migration happened.

-- 2026-07-21: join-window event futures (route: /casino/bet/join_window, via
-- the consolidated betTypeConfig.ts, not a dedicated route file).
-- No schema change - reuse existing casino_event_bets columns:
--   join_window: bet_type='join_window' | location=subject_uuid | train_name=subject_name
--                | price=1/multiplier | stake | close_at=bet-placement-time + window_h (fixed 12h)
-- `side`/`ticker`/etc. unused. Odds computed Hub-side (hazard-survival estimate
-- over deduped `sessions` gaps, conditioned on how long the subject's already been
-- gone) — see GET /casino/event/join-odds. First bet type where the bettor's target
-- is a *different player* (the subject), not the bettor themself.

-- 2026-07-22: death-timing event futures (route: /casino/bet/death_window, via
-- betTypeConfig.ts). No schema change - reuse existing casino_event_bets columns:
--   death_window: bet_type='death_window' | location=subject_uuid | train_name=subject_name
--                 | latitude=placement_playtime_ms (subject's users.playtime at bet
--                 placement, ms) | price=1/multiplier | stake
--                 | close_at=informational estimate only (placement + window_h wall-clock) —
--                 real resolution is PLAYTIME-driven, not wall-clock: settle task polls
--                 live users.playtime vs placement_playtime_ms + window, see death_market.rs.
-- Odds computed Hub-side over historical gaps between deaths, measured in playtime-ms
-- reconstructed from `sessions` (deaths carry no playtime snapshot of their own) —
-- see GET /casino/event/death-odds. Settlement WIN check (any death since placement)
-- is GET /casino/event/died-since; LOSS/timeout is evaluated Rust-side against
-- live playtime, no dedicated Hub endpoint needed for that half.