BEGIN;

-- The email column is now allowed to be null. To alter the table in this way,
-- we have to:
--
-- * create the new table
-- * copy the data over
-- * drop the views using the old table and then drop the old table itself
-- * rename the new table
-- * recreate the indexes and views
--
CREATE TABLE mana_accounts_new
(
   id            INTEGER     PRIMARY KEY,
   username      TEXT        NOT NULL UNIQUE,
   password      TEXT        NOT NULL,
   email         TEXT            NULL,
   level         INTEGER     NOT NULL,
   banned        INTEGER     NOT NULL,
   registration  INTEGER     NOT NULL,
   lastlogin     INTEGER     NOT NULL,
   authorization TEXT            NULL,
   expiration    INTEGER         NULL
);

INSERT INTO mana_accounts_new SELECT * FROM mana_accounts;
DROP VIEW mana_v_transactions;
DROP TABLE mana_accounts;
ALTER TABLE mana_accounts_new RENAME TO mana_accounts;

CREATE UNIQUE INDEX mana_accounts_username ON mana_accounts ( username );
CREATE UNIQUE INDEX mana_accounts_email    ON mana_accounts ( email );

CREATE VIEW mana_v_transactions
AS
   SELECT t.id           as transaction_id,
          t.time         as transacition_time,
          a.id           as user_id,
          a.username     as username,
          c.id           as char_id,
          c.name         as charname,
          tc.id          as action_id,
          tc.description as action,
          tc.category    as category,
          t.message      as message
     FROM mana_transactions t
     JOIN mana_characters c
       ON t.char_id = c.id
     JOIN mana_accounts a
       ON c.user_id = a.id
     JOIN mana_transaction_codes tc
       ON t.action = tc.id;


-- Update the database version, and set date of update
UPDATE mana_world_states
   SET value      = '27',
       moddate    = strftime('%s','now')
   WHERE state_name = 'database_version';

END;