feat: new migrations

4 files changed, 74 insertions, 74 deletions

diff --git a/migrations/2024-05-11T02-53-05_init/down.sql b/migrations/2024-05-11T02-53-05_init/down.sql
index 463aef2..1d4168b 100644
--- a/migrations/2024-05-11T02-53-05_init/down.sql
+++ b/migrations/2024-05-11T02-53-05_init/down.sql
@@ -1,10 +1,11 @@
 -- This file should undo anything in 'up.sql'
-DROP TABLE IF EXISTS "timers";
-DROP TABLE IF EXISTS "custom_commands";
-DROP TABLE IF EXISTS "event_subscriptions";
-DROP TABLE IF EXISTS "events";
-DROP TABLE IF EXISTS "actions";
-DROP TABLE IF EXISTS "user_rights";
-DROP TABLE IF EXISTS "channel_preferences";
-DROP TABLE IF EXISTS "channels";
-DROP TABLE IF EXISTS "users";
+DROP TABLE IF EXISTS timers;
+DROP TABLE IF EXISTS custom_command_aliases;
+DROP TABLE IF EXISTS custom_commands;
+DROP TABLE IF EXISTS event_subscriptions;
+DROP TABLE IF EXISTS events;
+DROP TABLE IF EXISTS actions;
+DROP TABLE IF EXISTS user_rights;
+DROP TABLE IF EXISTS channel_preferences;
+DROP TABLE IF EXISTS channels;
+DROP TABLE IF EXISTS users;
diff --git a/migrations/2024-05-11T02-53-05_init/up.sql b/migrations/2024-05-11T02-53-05_init/up.sql
index 2d4d8c2..afb26b7 100644
--- a/migrations/2024-05-11T02-53-05_init/up.sql
+++ b/migrations/2024-05-11T02-53-05_init/up.sql
@@ -1,72 +1,83 @@
 -- Your SQL goes here
-CREATE TABLE IF NOT EXISTS "channels"  (
-  "id" SERIAL NOT NULL UNIQUE PRIMARY KEY,
-  "alias_id" INTEGER NOT NULL UNIQUE,
-  "alias_name" VARCHAR NOT NULL UNIQUE,
-  "joined_at" TIMESTAMP NOT NULL DEFAULT timezone('utc', now()),
-  "opted_out_at" TIMESTAMP
+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" SERIAL NOT NULL UNIQUE PRIMARY KEY,
-  "alias_id" INTEGER NOT NULL UNIQUE,
-  "alias_name" VARCHAR NOT NULL UNIQUE,
-  "joined_at" TIMESTAMP NOT NULL DEFAULT timezone('utc', now()),
-  "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 "channel_preferences" (
-  "channel_id" INTEGER NOT NULL UNIQUE PRIMARY KEY REFERENCES "channels"("id") ON DELETE CASCADE,
-  "prefix" VARCHAR,
-  "locale" VARCHAR
+CREATE TABLE IF NOT EXISTS channel_preferences (
+  id INTEGER NOT NULL PRIMARY KEY REFERENCES channels(id) ON DELETE CASCADE,
+  prefix TEXT,
+  locale TEXT,
+  silent_mode BOOLEAN NOT NULL DEFAULT FALSE,
+  markov_responses BOOLEAN NOT NULL DEFAULT FALSE,
+  random_markov_responses BOOLEAN NOT NULL DEFAULT FALSE
 );
 
-CREATE TABLE IF NOT EXISTS "user_rights" (
-  "id" SERIAL NOT NULL UNIQUE PRIMARY KEY,
-  "user_id" INTEGER NOT NULL REFERENCES "users"("id") ON DELETE CASCADE,
-  "channel_id" INTEGER NOT NULL REFERENCES "channels"("id") ON DELETE CASCADE,
-  "level" INTEGER NOT NULL DEFAULT 1,
+CREATE TABLE IF NOT EXISTS user_rights (
+  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
+  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+  channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
+  level INTEGER NOT NULL DEFAULT 1,
 
-  CONSTRAINT "unique_user_channel" UNIQUE ("user_id", "channel_id")
+  CONSTRAINT unique_user_channel UNIQUE (user_id, channel_id)
 );
 
-CREATE TABLE IF NOT EXISTS "actions" (
-  "id" SERIAL NOT NULL UNIQUE PRIMARY KEY,
-  "user_id" INTEGER NOT NULL REFERENCES "users"("id") ON DELETE CASCADE,
-  "channel_id" INTEGER NOT NULL REFERENCES "channels"("id") ON DELETE CASCADE,
-  "command" VARCHAR NOT NULL,
-  "arguments" VARCHAR,
-  "full_message" VARCHAR NOT NULL,
-  "sent_at" TIMESTAMP NOT NULL DEFAULT timezone('utc', now())
+CREATE TABLE IF NOT EXISTS actions (
+  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
+  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+  channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
+  command TEXT NOT NULL,
+  arguments TEXT,
+  full_message TEXT NOT NULL,
+  sent_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP
 );
 
-CREATE TABLE IF NOT EXISTS "events" (
-  "id" SERIAL NOT NULL UNIQUE PRIMARY KEY,
-  "channel_id" INTEGER NOT NULL REFERENCES "channels"("id") ON DELETE CASCADE,
-  "name" VARCHAR NOT NULL,
-  "event_type" INTEGER NOT NULL,
-  "flags" INTEGER[] NOT NULL DEFAULT ARRAY[]::INTEGER[],
-  "message" VARCHAR NOT NULL
+CREATE TABLE IF NOT EXISTS events (
+  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
+  channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
+  name TEXT NOT NULL,
+  event_type INTEGER NOT NULL,
+  is_massping BOOLEAN NOT NULL DEFAULT FALSE,
+  message TEXT NOT NULL
 );
 
-CREATE TABLE IF NOT EXISTS "event_subscriptions" (
-  "id" SERIAL NOT NULL UNIQUE PRIMARY KEY,
-  "event_id" INTEGER NOT NULL REFERENCES "events"("id") ON DELETE CASCADE,
-  "user_id" INTEGER NOT NULL REFERENCES "users"("id") ON DELETE CASCADE
+CREATE TABLE IF NOT EXISTS event_subscriptions (
+  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
+  event_id INTEGER NOT NULL REFERENCES events(id) ON DELETE CASCADE,
+  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE
 );
 
-CREATE TABLE IF NOT EXISTS "custom_commands" (
-  "id" SERIAL NOT NULL UNIQUE PRIMARY KEY,
-  "channel_id" INTEGER NOT NULL REFERENCES "channels"("id") ON DELETE CASCADE,
-  "name" VARCHAR NOT NULL,
-  "message" VARCHAR NOT NULL
+CREATE TABLE IF NOT EXISTS custom_commands (
+  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
+  channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
+  name TEXT NOT NULL,
+  message TEXT NOT NULL
 );
 
-CREATE TABLE IF NOT EXISTS "timers" (
-  "id" SERIAL NOT NULL UNIQUE PRIMARY KEY,
-  "channel_id" INTEGER NOT NULL REFERENCES "channels"("id") ON DELETE CASCADE,
-  "name" VARCHAR NOT NULL,
-  "message" VARCHAR NOT NULL,
-  "interval_sec" INTEGER NOT NULL,
-  "last_executed_at" TIMESTAMP NOT NULL DEFAULT timezone('utc', now())
+CREATE TABLE IF NOT EXISTS custom_command_aliases (
+  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
+  command_id INTEGER NOT NULL REFERENCES custom_commands(id) ON DELETE CASCADE,
+  name TEXT NOT NULL,
+
+  CONSTRAINT unique_command_alias UNIQUE (command_id, name)
+);
+
+CREATE TABLE IF NOT EXISTS timers (
+  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
+  channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
+  name TEXT NOT NULL,
+  message TEXT NOT NULL,
+  interval_sec INTEGER NOT NULL,
+  last_executed_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP
 );
diff --git a/migrations/2024-12-13T21-55-38_github_support/down.sql b/migrations/2024-12-13T21-55-38_github_support/down.sql
deleted file mode 100644
index dd5c51e..0000000
--- a/migrations/2024-12-13T21-55-38_github_support/down.sql
+++ /dev/null
@@ -1,6 +0,0 @@
--- Your SQL goes here
-ALTER TABLE "events" DROP CONSTRAINT check_event_type;
-ALTER TABLE "events" ADD CONSTRAINT check_event_type CHECK (
-    ("target_alias_id" IS NOT NULL AND "event_type" < 99)
-    OR ("custom_alias_id" IS NOT NULL AND "event_type" >= 99)
-);
\ No newline at end of file
diff --git a/migrations/2024-12-13T21-55-38_github_support/up.sql b/migrations/2024-12-13T21-55-38_github_support/up.sql
deleted file mode 100644
index 666d774..0000000
--- a/migrations/2024-12-13T21-55-38_github_support/up.sql
+++ /dev/null
@@ -1,6 +0,0 @@
--- Your SQL goes here
-ALTER TABLE "events" DROP CONSTRAINT check_event_type;
-ALTER TABLE "events" ADD CONSTRAINT check_event_type CHECK (
-    ("target_alias_id" IS NOT NULL AND "event_type" < 10)
-    OR ("custom_alias_id" IS NOT NULL AND "event_type" >= 10)
-);
\ No newline at end of file