-- Disabled and deleted accounts (oxygenhub#81). A disabled account is

-- refused like a banned one, with its own message, and can be enabled

-- again. A deleted account's row goes (its tokens, keys and memberships

-- with it); its username is retired so nobody can take it over.

ALTER TABLE accounts ADD COLUMN disabled INTEGER NOT NULL DEFAULT 0;

CREATE TABLE retired_usernames (

    username   TEXT PRIMARY KEY COLLATE NOCASE,

    retired_ms INTEGER NOT NULL

);

-- A review keeps its author's name when the account is deleted, as issues

-- and comments do, rather than going with it.

CREATE TABLE proposal_reviews_kept (

    id          INTEGER PRIMARY KEY,

    proposal_id INTEGER NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,

    author_id   INTEGER REFERENCES accounts(id) ON DELETE SET NULL,

    author      TEXT NOT NULL DEFAULT '',

    verdict     TEXT NOT NULL CHECK (verdict IN ('comment', 'changes', 'approve')),

    body        TEXT NOT NULL DEFAULT '',

    created_ms  INTEGER NOT NULL

);

INSERT INTO proposal_reviews_kept (id, proposal_id, author_id, author, verdict, body, created_ms)

    SELECT id, proposal_id, author_id, author, verdict, body, created_ms FROM proposal_reviews;

DROP TABLE proposal_reviews;

ALTER TABLE proposal_reviews_kept RENAME TO proposal_reviews;

CREATE INDEX proposal_reviews_by_proposal ON proposal_reviews (proposal_id, created_ms);