alter table channel
rename to old_channel;

alter table message
rename to old_message;

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

insert into channel (id, name, created_sequence, created_at)
select id, name, created_sequence, created_at from old_channel;

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

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

insert into message (id, channel, sender, sent_sequence, sent_at, body)
select id, channel, sender, sent_sequence, sent_at, body 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
);

drop table old_message;
drop table old_channel;

create index message_sent_at
on message (sent_at);

create index channel_created_at
on channel (created_at);