From 1458ff7be5d883444943090cb636e9343487d03e Mon Sep 17 00:00:00 2001
From: Owen Jacobson <owen@grimoire.ca>
Date: Fri, 27 Sep 2024 20:18:50 -0400
Subject: Send created events when channels are added.

---
 migrations/20240928002608_channel_lifecycle.sql | 57 +++++++++++++++++++++++++
 1 file changed, 57 insertions(+)
 create mode 100644 migrations/20240928002608_channel_lifecycle.sql

(limited to 'migrations/20240928002608_channel_lifecycle.sql')

diff --git a/migrations/20240928002608_channel_lifecycle.sql b/migrations/20240928002608_channel_lifecycle.sql
new file mode 100644
index 0000000..bc690d7
--- /dev/null
+++ b/migrations/20240928002608_channel_lifecycle.sql
@@ -0,0 +1,57 @@
+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
+);
+
+-- Transfer data from original table
+insert into channel
+select
+	channel.id,
+	channel.name,
+	coalesce(
+		min(message.sent_at),
+		strftime('%FT%R:%f+00:00', 'now', 'utc')
+	) as created_at
+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;
-- 
cgit v1.2.3

