create table event_sequence (
	last_value bigint
		not null
);

create unique index event_sequence_singleton
on event_sequence (0);

-- Attempt to assign events sent so far a globally-unique sequence number,
-- maintaining an approximation of the order they were sent in. This can
-- introduce small ordering anomalies (where the resulting sequence differs
-- from the order they were sent in) for events that were sent close in time;
-- I've gone with chronological order here as it's the closest thing we have to
-- a global ordering, and because the results will be intuitive to most users.
create temporary table raw_event (
	type text
		not null,
	at text
		not null,
	channel text
		unique,
	message text
		unique,
	check ((channel is not null and message is null) or (message is not null and channel is null))
);

insert into raw_event (type, at, channel)
select
	'channel' as type,
	created_at as at,
	id as channel
from channel;

insert into raw_event (type, at, message)
select
	'message' as type,
	sent_at as at,
	id as message
from message;

create temporary table event (
	type text
		not null,
	sequence
		unique
		not null,
	at text
		not null,
	channel text
		unique,
	message text
		unique,
	check ((channel is not null and message is null) or (message is not null and channel is null))
);

insert into event
select
	type,
	rank() over (order by at) - 1 as sequence,
	at,
	channel,
	message
from raw_event;

drop table raw_event;

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
		unique
		not null,
	created_sequence bigint
		unique
		not null,
	created_at text
		not null
);

insert into channel
select
	c.id,
	c.name,
	e.sequence,
	c.created_at
from old_channel as c join event as e
	on e.channel = c.id;

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

insert into message
select
	m.id,
	m.channel,
	m.sender,
	e.sequence,
	m.sent_at,
	m.body
from old_message as m join event as e
	on e.message = m.id;

insert into event_sequence
select coalesce(max(sequence), 0) from event;

drop table event;