-- First, migrate the table we're actually interested in - `user`. This splits it into two parts:
--
-- * login, which is concerned with authentication data (and is non-synchronized,
--   though at the DB that doesn't matter much), and
-- * user, which is concerned with chat data (and is synchronized, ditto).

alter table user
    rename to old_user;

create table login (
    id text
        not null
        primary key,
    display_name text
        not null,
    canonical_name text
        not null
        unique,
    password text
        not null
);

insert into
    login (id, display_name, canonical_name, password)
select
    id,
    display_name,
    canonical_name,
    password_hash as password
from
    old_user;

create table user (
    id text
        not null
        primary key
        references login (id),
    created_at text
        not null,
    created_sequence bigint
        not null
        unique
);

insert into
    user (id, created_at, created_sequence)
select
    id,
    created_at,
    created_sequence
from
    old_user;

-- Now, recreate the entire rest of the owl. Everything that referenced the original `user` table _except tokens_
-- references the user table. Tokens, which are authentication data, reference the login (authn) table.

alter table token
    rename to old_token;

create table token (
    id text
        not null
        primary key,
    secret text
        not null
        unique,
    login text
        not null
        references login (id),
    issued_at text
        not null,
    last_used_at text
        not null
);

insert into
    token (id, secret, login, issued_at, last_used_at)
select
    id,
    secret,
    user as login,
    issued_at,
    last_used_at
from
    old_token;

alter table invite
    rename to old_invite;

create table invite (
    id text
        primary key
        not null,
    issuer text
        not null
        references user (id),
    issued_at text
        not null
);

insert into
    invite (id, issuer, issued_at)
select
    id,
    issuer,
    issued_at
from
    old_invite;

alter table message
    rename to old_message;

create table message (
    id text
        not null
        primary key,
    conversation text
        not null
        references conversation (id),
    sender text
        not null
        references user (id),
    body text
        null,
    sent_at text
        not null,
    sent_sequence bigint
        unique
        not null,
    last_sequence bigint
        not null
);

insert into
    message (id, conversation, sender, body, sent_at, sent_sequence, last_sequence)
select
    id,
    conversation,
    sender,
    body,
    sent_at,
    sent_sequence,
    last_sequence
from
    old_message;

alter table message_deleted
    rename to old_message_deleted;

create table message_deleted (
    id text
        not null
        primary key
        references message (id),
    deleted_at text
        not null,
    deleted_sequence bigint
        unique
        not null
);

insert into
    message_deleted (id, deleted_at, deleted_sequence)
select
    id,
    deleted_at,
    deleted_sequence
from
    message_deleted;

drop table old_message_deleted;
drop table old_message;
drop table old_invite;
drop table old_token;
drop table old_user;

create index token_issued_at
    on token (issued_at);
create index token_last_used_at
    on token (last_used_at);
create index token_login
    on token (login);

create index invite_issued_at
    on invite (issued_at);

create index message_sent_at
    on message (sent_at);
create index message_conversation
    on message (conversation);
create index message_last_sequence
    on message (last_sequence);

create index message_deleted_deleted_at
    on message_deleted (deleted_at);