-- The war ledger: the three-kingdom tally behind the `warscore` screen.
--
-- WITHOUT THIS THE WAR RESTARTS EVERY TIME THE SERVER DOES. Warscore has been a static array since
-- the PvP scoring landed, so kills and losses accumulated for as long as the process lived and were
-- gone the moment it stopped. That is survivable for a counter nobody reads; it is fatal for a
-- SCREEN, and building the warscore command without this would put a number in front of players
-- that silently means "since the last restart" while looking like it means "the war".
--
-- ONE ROW PER KINGDOM rather than one row of twenty-one columns, which is the shape ROM's own file
-- has and the shape the code wants. Warscore is indexed by kingdom and every reader loops the three;
-- a wide row would have to name tier_killed, kael_killed and um_killed separately and put the loop
-- back into the SQL.
--
-- RAIDS AND BEEN_RAIDED ARE STORED AND WILL STAY AT ZERO. Nothing increments them -- see
-- Rules/War/Warscore.cs, and the author has parked the feature until there are towns to take. The
-- columns exist so the day it lands there is nothing to migrate, and so the gap is visible in the
-- schema rather than only in a comment.
--
-- THE TOTAL IS NOT STORED. It is kills less losses, computed on read, so persisting it would create
-- a second copy that could disagree with its own inputs. ROM stores it, never computes it, and
-- displays zero forever; that is the mistake this avoids rather than reproduces.
--
-- KINGDOM IS THE KEY and it is the enum's own value -- Tier 0, Kael 1, Undermountain 2 -- which is
-- also ROM's `side`. Kingdom.None is -1 and deliberately has no row: it is the value a character
-- holds before creation assigns one, and nothing without a side can win or lose a war.

CREATE TABLE IF NOT EXISTS warscore (
    kingdom        INTEGER NOT NULL PRIMARY KEY,
    killed         INTEGER NOT NULL DEFAULT 0,
    lost           INTEGER NOT NULL DEFAULT 0,
    legends_killed INTEGER NOT NULL DEFAULT 0,
    legends_lost   INTEGER NOT NULL DEFAULT 0,
    raids          INTEGER NOT NULL DEFAULT 0,
    been_raided    INTEGER NOT NULL DEFAULT 0
);

-- An index on warpoints, for the leaderboard.
--
-- THE LEADERBOARD IS A QUERY, NOT A MAINTAINED LIST -- see WarReports. ROM keeps main_warlist, a
-- linked list spliced by hand on every kill and saved to its own file; this orders the characters
-- table instead, which is always correct by construction and has no second copy to drift. The index
-- is what keeps that cheap once there are more than a few hundred characters.
CREATE INDEX IF NOT EXISTS idx_characters_warpoints ON characters (warpoints DESC);
