-- Act of War -- initial schema.
--
-- Applied once, then never edited. Changing an applied migration is caught at startup by its
-- checksum: any database that already ran the old text will never run the new text, so the two
-- silently diverge. Add a new numbered file instead.
--
-- CONVENTIONS, and the reasons for them.
--
--   snake_case columns. Postgres folds unquoted identifiers to lower case, so camelCase there
--   means quoting every identifier forever. Reads alias back to the C# property names.
--
--   REAL COMPOSITE PRIMARY KEYS on every child table. The old schema declared none at all and
--   relied on deleting a character's rows and reinserting them wholesale, which is why it could
--   use INSERT OR REPLACE. That is a bad trade: without a key the database cannot tell you that
--   your writer produced two rows for one slot, and delete-then-insert rewrites rows that did not
--   change on every save interval, for every character.
--
--   NO AUTOINCREMENT. It is SQLite-specific, costs a second table, and nothing here needs a
--   surrogate key -- every row is identified by what it actually is.
--
--   Explicit types on every column, so the schema reads the same to a human as it does to SQLite.
--   SQLite's affinity rules are lenient; Postgres's are not, and this file is meant to survive
--   the move.
--
--   ON DELETE CASCADE on every child table. Character deletion is soft (see characters.deleted),
--   so this is for hard deletion by an admin tool -- at which point orphaned child rows keyed by a
--   name that could be reused later are exactly the wrong thing to leave behind.
--
-- Booleans are INTEGER 0/1: SQLite has no boolean type, and Postgres accepts the same literals.
-- Timestamps are TEXT in ISO-8601 UTC, which sorts correctly as text and is unambiguous to read.

-- ---------------------------------------------------------------------------------------------
-- accounts
-- ---------------------------------------------------------------------------------------------

CREATE TABLE accounts (
    name           TEXT    NOT NULL,
    -- A hash. The store has no opinion on the algorithm; whatever writes this owns that choice.
    password_hash  TEXT    NOT NULL,
    created        TEXT    NOT NULL,
    last_login     TEXT    NOT NULL,
    banned         INTEGER NOT NULL DEFAULT 0,

    PRIMARY KEY (name)
);

-- ---------------------------------------------------------------------------------------------
-- characters
--
-- race, sex, the three added attributes and strategy are COLUMNS HERE, not four separate 1:1 side
-- tables as in the old schema. They were only split out to avoid altering uMMORPG's stock
-- `characters` table, a constraint that no longer exists.
--
-- That is not tidying. In the old design a missing side table threw inside the save transaction,
-- which did not merely lose that table's data -- it aborted the entire save, so inventory,
-- experience and position were lost too, for every character, on every interval. One table cannot
-- go missing relative to itself.
-- ---------------------------------------------------------------------------------------------

CREATE TABLE characters (
    name           TEXT    NOT NULL,
    account        TEXT    NOT NULL REFERENCES accounts (name),

    class          TEXT    NOT NULL,
    race           TEXT    NOT NULL DEFAULT '',
    -- AoW.Rules.Sex as an int. original_sex is what the character was created as, kept so a
    -- sex-change effect can be reversed without losing it.
    sex            INTEGER NOT NULL DEFAULT 0,
    original_sex   INTEGER NOT NULL DEFAULT 0,

    x              REAL    NOT NULL DEFAULT 0,
    y              REAL    NOT NULL DEFAULT 0,

    level          INTEGER NOT NULL DEFAULT 1,
    experience     INTEGER NOT NULL DEFAULT 0,
    -- ROM pcdata->perm_hit / perm_mana: accumulated per-level rolls. Stored rather than derived
    -- because the rolls are random -- recomputing them would reroll the character's history.
    perm_hit       INTEGER NOT NULL DEFAULT 0,
    perm_mana      INTEGER NOT NULL DEFAULT 0,
    -- ROM ch->train: unspent attribute training sessions.
    trains         INTEGER NOT NULL DEFAULT 0,

    health         INTEGER NOT NULL DEFAULT 0,
    mana           INTEGER NOT NULL DEFAULT 0,

    -- BASE attribute values only, i.e. what the player spent points on. The effective value is
    -- base + gear + affects and is derived at load. Storing the effective value would double-count
    -- the moment gear is re-applied.
    strength       INTEGER NOT NULL DEFAULT 0,
    intelligence   INTEGER NOT NULL DEFAULT 0,
    wisdom         INTEGER NOT NULL DEFAULT 0,
    dexterity      INTEGER NOT NULL DEFAULT 0,
    constitution   INTEGER NOT NULL DEFAULT 0,

    -- AoW.Rules.Strategy as an int: Flee / Defend / Engage / Assault.
    strategy       INTEGER NOT NULL DEFAULT 0,

    gold           INTEGER NOT NULL DEFAULT 0,

    -- Guild NAME only. Guild rows are shared state, not this character's.
    guild          TEXT    NOT NULL DEFAULT '',

    online         INTEGER NOT NULL DEFAULT 0,
    last_saved     TEXT    NOT NULL,

    -- Soft deletion. A MUD character is months of play, and "deleted by mistake" has to be an
    -- answerable support request. It also keeps the name reserved: every child table is keyed by
    -- character name, so reusing a dead character's name would let a new one inherit its rows.
    deleted        INTEGER NOT NULL DEFAULT 0,

    PRIMARY KEY (name)
);

-- The character-select screen asks exactly this, on every login.
CREATE INDEX idx_characters_account ON characters (account);

-- ---------------------------------------------------------------------------------------------
-- inventory and equipment
--
-- Identical shape, so they stay identical tables. Keyed by SLOT, because position is meaningful:
-- an item is in bag slot 4, and slot 3 may legitimately be empty.
--
-- Empty slots are not rows. amount = 0 is skipped on save and restored as empty by position.
-- ---------------------------------------------------------------------------------------------

CREATE TABLE character_inventory (
    character            TEXT    NOT NULL REFERENCES characters (name) ON DELETE CASCADE,
    slot                 INTEGER NOT NULL,
    name                 TEXT    NOT NULL,
    amount               INTEGER NOT NULL,
    summoned_health      INTEGER NOT NULL DEFAULT 0,
    summoned_level       INTEGER NOT NULL DEFAULT 0,
    summoned_experience  INTEGER NOT NULL DEFAULT 0,

    PRIMARY KEY (character, slot)
);

CREATE TABLE character_equipment (
    character            TEXT    NOT NULL REFERENCES characters (name) ON DELETE CASCADE,
    slot                 INTEGER NOT NULL,
    name                 TEXT    NOT NULL,
    amount               INTEGER NOT NULL,
    summoned_health      INTEGER NOT NULL DEFAULT 0,
    summoned_level       INTEGER NOT NULL DEFAULT 0,
    summoned_experience  INTEGER NOT NULL DEFAULT 0,

    PRIMARY KEY (character, slot)
);

-- ---------------------------------------------------------------------------------------------
-- skills, buffs, quests, cooldowns
--
-- All keyed by NAME rather than slot: a character cannot hold two of the same skill, and their
-- order carries no meaning.
--
-- Every *_end column is REMAINING SECONDS, not an absolute time. The network clock restarts with
-- the server, so an absolute timestamp is meaningless across the restart these tables exist to
-- survive. The consequence is that they are wall-clock dependent -- two saves a second apart
-- legitimately differ -- so anything diffing two saves has to normalise them.
-- ---------------------------------------------------------------------------------------------

CREATE TABLE character_skills (
    character      TEXT    NOT NULL REFERENCES characters (name) ON DELETE CASCADE,
    name           TEXT    NOT NULL,
    -- 0 means unlearned, and unlearned skills are not rows.
    level          INTEGER NOT NULL,
    -- ROM-flavoured proficiency: 85 on unlock, improving toward 100 through use.
    success_rate   REAL    NOT NULL DEFAULT 0,
    cast_time_end  REAL    NOT NULL DEFAULT 0,
    cooldown_end   REAL    NOT NULL DEFAULT 0,

    PRIMARY KEY (character, name)
);

CREATE TABLE character_buffs (
    character      TEXT    NOT NULL REFERENCES characters (name) ON DELETE CASCADE,
    name           TEXT    NOT NULL,
    level          INTEGER NOT NULL,
    buff_time_end  REAL    NOT NULL DEFAULT 0,

    PRIMARY KEY (character, name)
);

CREATE TABLE character_quests (
    character  TEXT    NOT NULL REFERENCES characters (name) ON DELETE CASCADE,
    name       TEXT    NOT NULL,
    progress   INTEGER NOT NULL DEFAULT 0,
    completed  INTEGER NOT NULL DEFAULT 0,

    PRIMARY KEY (character, name)
);

CREATE TABLE character_item_cooldowns (
    character     TEXT NOT NULL REFERENCES characters (name) ON DELETE CASCADE,
    category      TEXT NOT NULL,
    cooldown_end  REAL NOT NULL DEFAULT 0,

    PRIMARY KEY (character, category)
);

-- ---------------------------------------------------------------------------------------------
-- affects -- ROM's char_data.affected list
--
-- Keyed by (character, slot), NOT by name like buffs above. Affects have no natural unique key:
-- two castings of the same spell are two distinct affects with the same type, and both have to
-- survive a round trip.
--
-- Enums are stored as ints rather than names. ROM stores raw numbers too, and an int survives an
-- enum member being renamed -- which matters here, because AffFlags bit C has been renamed twice
-- already (see doc/PORT-STATUS.md).
-- ---------------------------------------------------------------------------------------------

CREATE TABLE character_affects (
    character       TEXT    NOT NULL REFERENCES characters (name) ON DELETE CASCADE,
    slot            INTEGER NOT NULL,

    -- AffectWhere. NOT called `where`, which is a SQL keyword and would need quoting everywhere.
    affect_where    INTEGER NOT NULL,
    type            INTEGER NOT NULL,
    level           INTEGER NOT NULL,

    -- REMAINING seconds. -1 is ROM's "never expires", which is why this is signed and why load
    -- must test for it before treating the number as a duration.
    time_remaining  REAL    NOT NULL,

    location        INTEGER NOT NULL,
    modifier        INTEGER NOT NULL,
    bitvector       INTEGER NOT NULL,

    PRIMARY KEY (character, slot)
);
