alter table message
    rename to old_message;

alter table message_deleted
    rename to old_message_deleted;

create table conversation (
    id text
        not null
        primary key,
    created_sequence bigint
        unique
        not null,
    created_at text
        not null,
    last_sequence bigint
        not null
);

insert into
    conversation (id, created_sequence, created_at, last_sequence)
select
    id,
    created_sequence,
    created_at,
    last_sequence
from
    channel;

create table conversation_name (
    id text
        not null
        primary key
        references conversation (id),
    display_name
        not null,
    canonical_name
        not null
        unique
);

insert into
    conversation_name (id, display_name, canonical_name)
select
    id,
    display_name,
    canonical_name
from
    channel_name;

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

insert into
    conversation_deleted (id, deleted_sequence, deleted_at)
select
    id,
    deleted_sequence,
    deleted_at
from
    channel_deleted;

create table message (
    id text
        not null
        primary key,
    conversation text
        not null
        references conversation (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, conversation, sender, sent_sequence, sent_at, body, last_sequence)
select
    id,
    channel,
    sender,
    sent_sequence,
    sent_at,
    body,
    last_sequence
from
    old_message;

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;

drop table old_message_deleted;
drop table old_message;
drop table channel_deleted;
drop table channel_name;
drop table channel;

create index conversation_created_sequence
    on conversation (created_sequence);

create index conversation_created_at
    on conversation (created_at);

create index conversation_last_sequence
    on conversation (last_sequence);

create index message_deleted_deleted_at
    on message_deleted (deleted_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);