alter table channel
rename to old_channel;
alter table channel_name
rename to old_channel_name;
alter table channel_deleted
rename to old_channel_deleted;
alter table message
rename to old_message;
alter table message_deleted
rename to old_message_deleted;

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

insert into channel (id, created_sequence, created_at, last_sequence)
select
	ch.id,
	ch.created_sequence,
	ch.created_at,
	max(ch.created_sequence, coalesce(del.deleted_sequence, ch.created_sequence)) as last_seqeuence
from old_channel as ch
left join old_channel_deleted as del
	using (id);

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

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

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

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

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,
	last_sequence bigint
		not null
);

insert into message (id, channel, sender, sent_sequence, sent_at, body, last_sequence)
select
	msg.id,
	msg.channel,
	msg.sender,
	msg.sent_sequence,
	msg.sent_at,
	msg.body,
	max(msg.sent_sequence, coalesce(del.deleted_sequence, msg.sent_sequence)) as last_sequence
from
	old_message as msg
	left join old_message_deleted as del
		using (id);

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 old_channel_deleted;
drop table old_channel_name;
drop table old_channel;

-- recreate existing indices
create index message_sent_at
on message (sent_at);
create index message_deleted_deleted_at
on message_deleted (deleted_at);
create index message_channel
on message(channel);
create index channel_created_sequence
on channel (created_sequence);
create index channel_created_at
on channel (created_at);

-- new indices
create index channel_last_sequence
on channel (last_sequence);

create index message_last_sequence
on message (last_sequence);