-- The bank: banked coin, and a box of things.
--
-- The author, 21 August: "I would like to have the rom style bank implemented so a player can store
-- gold and items in the bank. The major deviation from ROM is I want the bank tied to the account,
-- so each player on the account has access."
--
-- THIS FORK HAS A BANK ALREADY. do_store, do_withdraw and do_listbox are all in the source, with
-- ch->banked_gold, ch->banked_silver, ch->storage, MAX_STORE_OBJ at 50, the ROOM_BANK flag and
-- ITEM_NOBANK -- which this schema has been carrying the effects of since the item tables were
-- written. So the tables below store something ROM stores; only the KEY is new.
--
-- KEYED ON THE ACCOUNT, NOT THE CHARACTER, which is the author's deviation and the only one.
-- ROM writes banked_gold into the character's own save file (save.c:241) and hangs storage off
-- CHAR_DATA. Here every character on an account reaches one box.
--
-- IT IS THE SECOND THING TO SIT ON THAT LINE and the line is the same one 0013_window_layout.sql
-- drew: a toolbar is gameplay and belongs to a character, a window position is a fact about the
-- person at the desk. A bank falls on the person's side by the author's call -- and the practical
-- argument is that moving a sword between your own characters is something players do anyway, by
-- dropping it on the floor and logging over, so the character-keyed version only adds the dance.
--
-- TWO CONSEQUENCES, BOTH GAMEPLAY, recorded so nobody has to rediscover them:
--   Two characters on one account share one box, live. Banks.Of hands out one object per account
--   precisely so there is no second copy to diverge.
--   Characters on one account may be on different SIDES, so the box can move a Wilderness sword to
--   an Undermountain character. That follows from the request rather than from this schema.
--
-- FIFTY ITEMS IS NOW PER ACCOUNT rather than per character, so somebody with four characters has
-- fewer slots than ROM would have given them, not more. Deliberate: a cap that scales with how many
-- characters you rolled is a reason to roll characters. The cap is enforced in Rules/Items/Bank.cs
-- and NOT here -- there is no CHECK constraint, for the same reason the toolbar has none: a rule the
-- database also holds an opinion about is a rule with two places to change.
--
-- ON DELETE CASCADE, so deleting an account takes its box with it. That is the same clause every
-- account-keyed and character-keyed table in this schema carries, and it is the reason a wipe does
-- not leave orphan rows keyed to a name somebody can register again.

CREATE TABLE IF NOT EXISTS account_bank (
    account  TEXT    NOT NULL REFERENCES accounts (name) ON DELETE CASCADE,
    -- ROM's banked_gold and banked_silver. TWO denominations rather than one purse value, because
    -- ROM's own withdrawal checks the denomination asked for: a box holding 3 gold refuses a
    -- request for 100 silver though it plainly holds the value. Collapsing them here would make
    -- that behaviour unimplementable above.
    gold     INTEGER NOT NULL DEFAULT 0,
    silver   INTEGER NOT NULL DEFAULT 0,

    PRIMARY KEY (account)
);

-- The storage box. Same shape as character_inventory and deliberately a SIMPLER one.
--
-- NO container_slot AND NO container_flags, unlike 0007_containers.sql's tables, because the box
-- cannot nest: ROM's do_store refuses an item with contents outright ("Empty its contents first.")
-- and stores a flat list. Adding the columns to match the character tables would be adding two
-- columns that are always -1 and 0 to make two schemas look alike.
--
-- KEYED BY SLOT, like the inventory, and for the same reason: position is meaningful and a gap is
-- legitimate. The slot here is just the row's index in the box.
--
-- vnum IS THE IDENTITY AND name IS DIAGNOSTIC, which 0005_item_vnum.sql settled at length: 97 item
-- names in the loaded areas are shared by two or more vnums, covering 15% of every object in the
-- game. The name is kept because "vnum 41207 is missing" says nothing and "vnum 41207, `a jewelled
-- crown`, is missing" names the area somebody has to go and find.
CREATE TABLE IF NOT EXISTS account_bank_items (
    account  TEXT    NOT NULL REFERENCES accounts (name) ON DELETE CASCADE,
    slot     INTEGER NOT NULL,
    vnum     INTEGER NOT NULL,
    name     TEXT    NOT NULL,
    -- The item's own condition, which repair and wear move and which a box must not reset. An item
    -- banked at 40% comes out at 40%.
    condition INTEGER NOT NULL DEFAULT 100,

    PRIMARY KEY (account, slot)
);
