-- message
-- message_deleted

-- Set up the new `user` table, and copy existing logins into it
create table user
(
    id               text
        not null
        primary key,
    display_name     text
        not null,
    canonical_name   text
        not null
        unique,
    password_hash    text
        not null,
    created_sequence bigint
        unique
        not null,
    created_at       text
        not null
);

insert into user (id, display_name, canonical_name, password_hash, created_sequence, created_at)
select id, display_name, canonical_name, password_hash, created_sequence, created_at
from login;

-- Carry the rename through `token`, preserving data…
alter table token
    rename to old_token;

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

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

-- Carry the rename through `invite`, preserving data…
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;

-- Carry the rename through `message`, preserving data…
alter table message
    rename to old_message;

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

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

-- Recreating `message` entails recreating `message_deleted`
alter table message_deleted
    rename to old_message_deleted;

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

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

-- Delete old tables (which will take old indices with them)
drop table old_message_deleted;
drop table old_message;
drop table old_invite;
drop table old_token;
drop table login;

-- Recreate indices
create index message_deleted_deleted_at
    on message_deleted (deleted_at);

create index message_sent_at
    on message (sent_at);
create index message_channel
    on message (channel);
create index message_last_sequence
    on message (last_sequence);

create index invite_issued_at
    on invite (issued_at);

create index token_issued_at
    on token (issued_at);
create index token_last_used_at
    on token (last_used_at);
create index token_user
    on token (user);