alter table channel
rename to old_channel;

-- Add new columns
create table channel (
	id text
		not null
		primary key,
	name text
		not null
		unique,
	created_at text
		not null,
	last_sequence bigint
		not null
);

-- Transfer data from original table
insert into channel
select
	channel.id,
	channel.name,
	channel.created_at,
	coalesce(
		max(message.sequence),
		0
	) as last_sequence
from old_channel as channel
	left join message on channel.id = message.channel
group by channel.id, channel.name;

-- Fix up `message` foreign keys
alter table message
rename to old_message;

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

insert into message
select * from old_message;

-- Bury the bodies respectfully
drop table old_message;
drop table old_channel;