From 9533a34322c5ec154860c42af98e0d4e8d44d945 Mon Sep 17 00:00:00 2001
From: ilotterytea <iltsu@alright.party>
Date: Mon, 13 Oct 2025 00:17:36 +0500
Subject: feat: save words into the database

---
 database.sql | 32 ++++++++++++++++++++++++++++++++
 1 file changed, 32 insertions(+)
 create mode 100644 database.sql

(limited to 'database.sql')

diff --git a/database.sql b/database.sql
new file mode 100644
index 0000000..e453239
--- /dev/null
+++ b/database.sql
@@ -0,0 +1,32 @@
+CREATE TABLE IF NOT EXISTS channels (
+  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
+  alias_id INTEGER NOT NULL UNIQUE,
+  alias_name TEXT NOT NULL UNIQUE,
+  joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
+  opted_out_at TIMESTAMP
+);
+
+CREATE TABLE IF NOT EXISTS users (
+  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
+  alias_id INTEGER NOT NULL UNIQUE,
+  alias_name TEXT NOT NULL UNIQUE,
+  joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
+  opted_out_at TIMESTAMP
+);
+
+CREATE TABLE IF NOT EXISTS words (
+    id BIGINT PRIMARY KEY AUTO_INCREMENT,
+    name TEXT NOT NULL UNIQUE
+);
+
+CREATE TABLE IF NOT EXISTS channel_words (
+    id BIGINT PRIMARY KEY AUTO_INCREMENT,
+    channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
+    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    word_id BIGINT NOT NULL REFERENCES words(id) ON DELETE CASCADE,
+    usage_count BIGINT NOT NULL DEFAULT 0,
+    first_used_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
+    last_used_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
+
+    UNIQUE (channel_id, user_id, word_id)
+);
\ No newline at end of file
-- 
cgit v1.2.3

