-- Who is shut out, why, by whom, and until when.
--
-- The author, 20 August: "The original ROM code has infrastructure for administrative commands. We
-- need to start porting those in. The one major outlier is the ban command. Can you look at the
-- logic behind it and optimize how it would work with current best practices." This table is most of
-- that answer; Rules/Admin/Ban.cs is the rest.
--
-- WHAT ROM STORES, FOR COMPARISON. A BAN_DATA is a name, a level and six flag bits, written to a
-- flat file that is rewritten whole on every change -- and only the entries flagged BAN_PERMANENT
-- are written at all, so a "temporary" ban exists solely in memory and lasts until somebody restarts
-- the process. There is no reason, no author and no timestamp anywhere in it. Six months on, a ROM
-- ban list is a column of hostnames nobody can account for.
--
-- SO EVERY ROW HERE CARRIES ITS OWN PROVENANCE, and the columns are chosen so that the question
-- "why is this here?" can always be answered by the row itself.
--
-- KIND AND SUBJECT ARE THE KEY, COMPOSITE. Re-banning something replaces it rather than stacking a
-- second row on top -- which is ROM's behaviour too (ban_site frees a matching entry before adding
-- the new one) and is right for a reason ROM does not state: two live bans on one subject raise a
-- question with no good answer, namely whether lifting one lifts the other.
--
-- ADDRESSES ARE STORED IN CANONICAL CIDR FORM -- '192.0.2.7/32', never '192.0.2.7'. Always carrying
-- the prefix is uglier and means the stored text can be compared as text, which is what `allow`
-- does. See AddressMask.ToString.
--
-- THERE IS NO CHARACTER KIND, deliberately. ROM shuts out an individual character with `deny`, which
-- sets PLR_DENY on the character rather than adding to the ban list, and that is the right shape: a
-- character is already a persisted row and can carry its own flag. A third kind here would give the
-- game two ways to say the same thing that could disagree with each other.
--
-- NO FOREIGN KEY ON THE SUBJECT, and this one is load-bearing rather than lazy. An account ban must
-- be settable for an account that does not exist yet -- that is precisely how you stop somebody
-- coming back under a name they have already announced -- and an address obviously references
-- nothing. A REFERENCES clause here would make the useful half of the feature impossible.
--
-- EXPIRY IS A TIMESTAMP, NOT A FLAG. NULL means permanent, which is ROM's BAN_PERMANENT. Anything
-- else is an ISO-8601 UTC instant, the same format every other timestamp in this schema uses, so it
-- sorts correctly as text and is unambiguous to read.

CREATE TABLE IF NOT EXISTS bans (
    -- A BanSubject: 1 account, 2 address. 0 (none) is never written.
    kind       INTEGER NOT NULL,
    -- An account name, or an address/network in canonical CIDR form.
    subject    TEXT    NOT NULL,

    -- Free text, shown to the person it shuts out. The author's call, 20 August: somebody told only
    -- "banned" either files a bug report or makes a second account to come and ask, and both cost
    -- more than the sentence does.
    reason     TEXT    NOT NULL DEFAULT '',

    -- The staff character who typed it, and their trust AT THE TIME. The trust is ROM's one good
    -- idea in ban.c -- `if (pban->level > get_trust(ch))` stops a junior immortal quietly undoing an
    -- implementor's decision -- and it is stored rather than looked up because the setter may be
    -- demoted, or gone, by the time somebody tries to lift it.
    set_by     TEXT    NOT NULL DEFAULT '',
    authority  INTEGER NOT NULL DEFAULT 0,

    set_at     TEXT    NOT NULL,
    -- NULL is permanent. Otherwise ISO-8601 UTC.
    expires_at TEXT,

    PRIMARY KEY (kind, subject)
);

-- Expired rows are swept rather than left, so the list a staff member reads is the list that is
-- actually in force. The index is for the sweep, which runs on the save interval rather than on
-- every connection -- see the header on BanList for why the CHECK is done in memory.
CREATE INDEX IF NOT EXISTS idx_bans_expires ON bans (expires_at);
