-- figure out new event seqeuence.
--
-- The main problem here is ensuring that the resulting stream is _consistent_.
-- For any message, the login it refers to should be created in the event stream
-- before the message does, and the channel it refers to should likewise be
-- created first.
--
-- Messages come after channels by time (clock slew allowing), so we can reuse
-- those timestamps to recover a global ordering between channels and messages.
--
-- We synthesize a login's created_at from the earliest of:
--
-- * the earliest message they sent that we know of, or
-- * right now (no messages in recorded history).
--
-- This produces a consistent sequence, up to clock slew, at the expense of
-- renumbering every event.

create table unsequenced (
	at text
		not null,
	login text
		references login (id),
	channel text
		references channel (id),
	message text
		references message (id),
	check (
		(login is not null and channel is null and message is null)
		or (login is null and channel is not null and message is null)
		or (login is null and channel is null and message is not null)
	)
);

insert into unsequenced (at, login)
select
	coalesce (
		min(message.sent_at),
		strftime('%FT%R:%f+00:00', 'now', 'utc')
	),
	login.id
from login
left join message
	on login.id = message.sender
group by login.id;

insert into unsequenced (at, channel)
select created_at, id
from channel;

insert into unsequenced (at, message)
select sent_at, id
from message;

create table event (
	at text
		not null,
	sequence
		primary key
		not null,
	login text
		references login (id),
	channel text
		references channel (id),
	message text
		references message (id),
	check (
		(login is not null and channel is null and message is null)
		or (login is null and channel is not null and message is null)
		or (login is null and channel is null and message is not null)
	)
);

insert into event (at, sequence, login, channel, message)
select
	at, row_number() over (order by at, login is null, message is null), login, channel, message
from unsequenced;

-- Get this out of memory.
drop table unsequenced;

-- Because of how foundational `login` is, we pretty much have to recreate the
-- whole schema.
alter table message
rename to old_message;

alter table channel
rename to old_channel;

alter table token
rename to old_token;

alter table login
rename to old_login;

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

create table token (
	id text
		not null
		primary key,
	secret text
		not null
		unique,
	login text
		not null
		references login (id),
	issued_at text
		not null,
	last_used_at text
		not null
);

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

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

-- Copy data from the original tables, assigning sequence numbers as we go.
insert into login (id, name, password_hash, created_sequence, created_at)
select
	old.id,
	old.name,
	old.password_hash,
	event.sequence,
	event.at
from old_login as old
join event on old.id = event.login
order by event.sequence;

insert into token (id, secret, login, issued_at, last_used_at)
select id, secret, login, issued_at, last_used_at from old_token;

insert into channel (id, name, created_sequence, created_at)
select
	old.id,
	old.name,
	event.sequence,
	old.created_at
from old_channel as old
join event on old.id = event.channel
order by event.sequence;

insert into message (id, channel, sender, sent_sequence, sent_at, body)
select
	old.id,
	old.channel,
	old.sender,
	event.sequence,
	old.sent_at,
	old.body
from old_message as old
join event on old.id = event.message
order by event.sequence;

-- Restart the event sequence, using the highest sequence number in the new
-- event series.
update event_sequence
set last_value = (select coalesce(max(sequence), 0) from event);

-- Clean up the now-unused original tables, plus the resequencing temp table
drop table event;
drop table old_message;
drop table old_channel;
drop table old_token;
drop table old_login;

-- Reindex, now that the original indices are no longer in the way
create index token_issued_at
on token (issued_at);

create index message_sent_at
on message (sent_at);

create index channel_created_at
on channel (created_at);