From eff7e73e18bb1083744542ecadfe6ce6ca5f1207 Mon Sep 17 00:00:00 2001
From: ilotterytea <iltsu@alright.party>
Date: Tue, 5 Aug 2025 16:02:00 +0500
Subject: feat: database schema

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

diff --git a/database.sql b/database.sql
new file mode 100644
index 0000000..c15b2f2
--- /dev/null
+++ b/database.sql
@@ -0,0 +1,45 @@
+CREATE TABLE IF NOT EXISTS servers (
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    host TEXT NOT NULL UNIQUE,
+    port INTEGER NOT NULL,
+    nick TEXT NOT NULL,
+    pass TEXT NOT NULL,
+    capabilities TEXT NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS rooms (
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    server_id INTEGER NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
+    `name` TEXT NOT NULL UNIQUE,
+    joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP(),
+    departed_at TIMESTAMP,
+    INDEX (`name`),
+    INDEX (id, server_id)
+);
+
+CREATE TABLE IF NOT EXISTS users (
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    server_id INTEGER NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
+    nick TEXT NOT NULL,
+    joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP(),
+    departed_at TIMESTAMP,
+    INDEX (nick),
+    INDEX (server_id)
+);
+
+CREATE TABLE IF NOT EXISTS messages (
+    id BIGINT PRIMARY KEY AUTO_INCREMENT,
+    room_id INTEGER NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
+    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    command VARCHAR(32) NOT NULL,
+    
+    params TEXT,
+    tags TEXT,
+
+    sent_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP(),
+    INDEX (room_id),
+    INDEX (user_id),
+    INDEX (command),
+    INDEX (room_id, sent_at),
+    FULLTEXT INDEX ft_message (params)
+);
\ No newline at end of file
-- 
cgit v1.2.3