-- Up

create table users_dg_tmp
(
    id                   TEXT                not null primary key,
    username             TEXT                not null,
    email                TEXT                not null,
    roles                TEXT                not null,
    avatarSource         TEXT,
    bannedReason         TEXT,
    suspiciousChecked    TINYINT default 0   not null,
    usernameNorm         TEXT,
    bannedTerms          TEXT    default NULL,
    bannedBy             TEXT    default NULL,
    lastActive           INTEGER,
    banSnapshot          TEXT,
    inactiveWarning      INTEGER,
    adminNotifications   INTEGER default 7   not null,
    loginAttempts        TEXT,
    timesheets           TEXT,
    socialLookup         INTEGER default '0' not null,
    personally           TEXT    default NULL
);

insert into users_dg_tmp(id, username, email, roles, avatarSource, bannedReason, suspiciousChecked, usernameNorm,
                         bannedTerms, bannedBy, lastActive, banSnapshot, inactiveWarning, adminNotifications,
                         loginAttempts, timesheets, socialLookup, personally)
select id,
       username,
       email,
       roles,
       avatarSource,
       bannedReason,
       suspiciousChecked,
       usernameNorm,
       bannedTerms,
       bannedBy,
       lastActive,
       banSnapshot,
       inactiveWarning,
       adminNotifications,
       loginAttempts,
       timesheets,
       socialLookup,
       null
from users;

drop table users;

alter table users_dg_tmp
    rename to users;

create unique index users_email
    on users (email);

create unique index users_username
    on users (username);

create unique index users_usernameNorm
    on users (usernameNorm);

-- Down