-- What players type into `bug`, `typo` and `idea`.
--
-- The author, 20 August: "Can you pull in a bug/typo/issue command, exactly like you suggested it,
-- and have it written to the SQL database. It can wipe with the database at startup for now."
--
-- SO THIS IS DELIBERATELY NOT DURABLE YET, and that is worth stating in the schema rather than
-- leaving somebody to discover it: the test server drops and recreates its database on every start
-- (see the `mode = test` note in PROGRESS), so this table is a SESSION log. The HTML report written
-- at shutdown is what survives, and it is the thing to point at when somebody asks where last
-- week's bugs went. Making this durable is a decision about the whole database, not about this
-- table, and the author has said it is coming.
--
-- ---------------------------------------------------------------------------------------------
-- WHY SO MANY COLUMNS FOR A ONE-LINE COMMAND
-- ---------------------------------------------------------------------------------------------
--
-- ROM's append_file writes `[%5d] %s: %s` -- room, name, text -- and nothing else. That is enough
-- when a wizard is reading the file while the reporter is still logged in, and useless a week
-- later. Half the bugs in a game like this are class-conditional or level-gated, and "it didn't
-- work" from a level 8 warrior and a level 40 mage are reports about different code.
--
-- THE BUILD COLUMN IS THE ONE ROM CANNOT HAVE and the one that matters most. A report filed against
-- a build from before the fix is not a report, and without this there is no way to tell.
--
-- ---------------------------------------------------------------------------------------------
-- COLUMN NAMES
-- ---------------------------------------------------------------------------------------------
--
-- `char_name` and `class_name` rather than `character` and `class`. Neither is reserved in SQLite
-- and both would work -- CHARACTER is a type name, not a keyword -- but a column called `character`
-- in a schema that also has a `characters` table is a sentence nobody parses correctly on first
-- read, and `class` is reserved in enough other engines that it is not worth the argument.
--
-- `body` IS UNESCAPED PLAYER TEXT, on purpose. It goes into an HTML page, and the escaping happens
-- once at the edge that renders (see ReportPage.Escape). A store that mangled it would make the
-- report unreadable in the database and would still not be a safety property.

CREATE TABLE IF NOT EXISTS reports (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,

    -- ReportKind: 1 bug, 2 typo, 3 idea. Stored as the number, so the numbers may not move.
    kind       INTEGER NOT NULL,

    char_name  TEXT    NOT NULL,
    account    TEXT    NOT NULL DEFAULT '',

    -- Everything that was true at the moment it was typed, and would be gone by the time anybody
    -- wrote the report up afterwards.
    level      INTEGER NOT NULL DEFAULT 0,
    class_name TEXT    NOT NULL DEFAULT '',
    race_name  TEXT    NOT NULL DEFAULT '',
    kingdom    INTEGER NOT NULL DEFAULT 0,

    -- This port's answer to ROM's room vnum. A report about a shop, a fountain or a monster is a
    -- report about a PLACE.
    x          REAL    NOT NULL DEFAULT 0,
    y          REAL    NOT NULL DEFAULT 0,

    build      TEXT    NOT NULL DEFAULT '',
    body       TEXT    NOT NULL,

    -- ISO-8601 UTC, the same round-trip format everything else in this database uses.
    filed      TEXT    NOT NULL
);

-- The shutdown report reads the whole table in order, and the staff `reports` command reads the
-- tail of it. Both want the same index, and AUTOINCREMENT already gives it -- this exists for
-- filtering one kind, which is how the page is grouped.
CREATE INDEX IF NOT EXISTS idx_reports_kind ON reports (kind, id);
