-- The war: warpoints, the kill counters, and the trophy list.
--
-- AoW's PvP scoring landed as rules on 18 August and had nowhere to live. Without these columns a
-- player's warpoints reset to zero at every logout, which does not merely lose a number -- it makes
-- the whole system pointless, because warpoints exist to be accumulated and compared on a
-- leaderboard. A currency that resets is not a currency.
--
-- SIX COUNTERS ON THE CHARACTER, matching ROM's fields on char_data:
--
--   warpoints       the currency. One per cross-kingdom kill, two for a Legend, one per final blow
--   enemies_killed  gross cross-kingdom kills
--   legends_killed  of those, how many were Legend or above
--   player_killed   times killed BY players
--   final_blows     killing blows landed
--   raids           never incremented by anything, and kept anyway -- see below
--
-- RAIDS IS STORED DESPITE BEING DEAD, deliberately. Nothing in the original increments it and
-- nothing here does either; the author has parked the feature until there are towns to take. A
-- column costs four bytes and keeps the gap visible in the schema rather than only in a comment,
-- and it means the day raids exist there is nothing to migrate.
--
-- THE TROPHY IS ITS OWN TABLE because it is a list, not a field. ROM stores it as two parallel
-- arrays of three hundred on pc_data -- names and counts -- which is a fixed-size C idiom rather
-- than a design. A row per victim is the same data without the ceiling being load-bearing, and the
-- ceiling stays enforced in the rules (TrophyList.MaxEntries) where the refusal is visible.
--
-- ORDER MATTERS AND IS NOT ALPHABETICAL. The trophy is displayed in the order you first killed each
-- enemy, so the rowid ordering is the display order and nothing needs a separate sort key.
--
-- NO DATA REPAIR, for the reason 0009_silver.sql gives: the server wipes its database at startup in
-- test mode, so no stored character predates this. Defaults of 0 are not sentinels -- a character
-- with no warpoints has zero warpoints, which is what a fresh one has too.

ALTER TABLE characters ADD COLUMN warpoints INTEGER NOT NULL DEFAULT 0;
ALTER TABLE characters ADD COLUMN enemies_killed INTEGER NOT NULL DEFAULT 0;
ALTER TABLE characters ADD COLUMN legends_killed INTEGER NOT NULL DEFAULT 0;
ALTER TABLE characters ADD COLUMN player_killed INTEGER NOT NULL DEFAULT 0;
ALTER TABLE characters ADD COLUMN final_blows INTEGER NOT NULL DEFAULT 0;
ALTER TABLE characters ADD COLUMN raids INTEGER NOT NULL DEFAULT 0;

CREATE TABLE IF NOT EXISTS character_trophy (
    -- TEXT referencing the name, and CASCADE, matching every other per-character table. An integer
    -- key would have been the only one in the schema and would outlive the character it belongs to.
    character TEXT    NOT NULL REFERENCES characters (name) ON DELETE CASCADE,
    victim    TEXT    NOT NULL,
    kills     INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_character_trophy_character ON character_trophy (character);
