feat: new migrations

7 files changed, 113 insertions, 112 deletions

diff --git a/luamods/cmd.lua b/luamods/cmd.lua
index dca7913..4826f5a 100644
--- a/luamods/cmd.lua
+++ b/luamods/cmd.lua
@@ -154,7 +154,7 @@ The `!cmd` command gives the ability to create their own chat commands.
 		table.remove(parts, 1)
 
 		local cmds = db_query(
-			'SELECT id, name, array_to_json(aliases) as aliases, message FROM custom_commands WHERE name = $1 AND channel_id = $2',
+			'SELECT id, name, message FROM custom_commands WHERE name = $1 AND channel_id = $2',
 			{ name, request.channel.id })
 
 		if scid == "new" then
@@ -222,11 +222,11 @@ The `!cmd` command gives the ability to create their own chat commands.
 
 			local new_alias = parts[1]
 
-			local cmd_alias = db_query('SELECT name FROM custom_commands WHERE $1 = ANY(aliases) AND channel_id = $2',
-				{ new_alias, request.channel.id })
+			local cmd_alias = db_query('SELECT name FROM custom_command_aliases WHERE name = $1 AND command_id = $2',
+				{ new_alias, cmd.id })
 
 			if #cmd_alias > 0 then
-				return l10n_custom_formatted_line_request(request, lines, "namesake_alias", { cmd_alias[1].name })
+				return l10n_custom_formatted_line_request(request, lines, "namesake_alias", { new_alias })
 			end
 
 			local internal_commands = bot_get_loaded_command_names()
@@ -234,7 +234,7 @@ The `!cmd` command gives the ability to create their own chat commands.
 				return l10n_custom_formatted_line_request(request, lines, "namesake", {})
 			end
 
-			db_execute('UPDATE custom_commands SET aliases = array_append(aliases, $1) WHERE id = $2',
+			db_execute('INSERT INTO custom_command_aliases(name, command_id) VALUES ($1, $2)',
 				{ new_alias, cmd.id })
 
 			return l10n_custom_formatted_line_request(request, lines, "alias_command", { new_alias, name })
@@ -245,19 +245,27 @@ The `!cmd` command gives the ability to create their own chat commands.
 
 			local old_alias = parts[1]
 
-			local cmd_alias = db_query('SELECT name FROM custom_commands WHERE $1 = ANY(aliases) AND channel_id = $2',
-				{ old_alias, request.channel.id })
+			local cmd_alias = db_query('SELECT id FROM custom_command_aliases WHERE name = $1 AND command_id = $2',
+				{ old_alias, cmd.id })
 
 			if #cmd_alias == 0 then
 				return l10n_custom_formatted_line_request(request, lines, "no_cmd_alias", { old_alias })
 			end
 
-			db_execute('UPDATE custom_commands SET aliases = array_remove(aliases, $1) WHERE id = $2',
-				{ old_alias, cmd.id })
+			db_execute('DELETE FROM custom_command_aliases WHERE id = $1',
+				{ cmd_alias[1].id })
 
 			return l10n_custom_formatted_line_request(request, lines, "delalias_command", { old_alias, name })
 		elseif scid == "view" then
-			local aliases = json_parse(cmd.aliases)
+			local aliases_db = db_query('SELECT name FROM custom_command_aliases WHERE command_id = $1',
+				{ cmd.id })
+
+			local aliases = {}
+
+			for i = 1, #aliases_db, 1 do
+				table.insert(aliases, aliases_db[i].name)
+			end
+
 			local aliases_str = table.concat(aliases, ', ')
 
 			if #aliases_str == 0 then
diff --git a/luamods/event.lua b/luamods/event.lua
index c739fa0..71d199c 100644
--- a/luamods/event.lua
+++ b/luamods/event.lua
@@ -56,9 +56,9 @@ local lines = {
         ["off"] = "{sender.alias_name}: Successfully deleted event %s",
         ["edit"] = "{sender.alias_name}: Edited a message for event %s",
         ["settarget"] = "{sender.alias_name}: Changed event target from %s to %s",
-        ["flag_disabled"] = "{sender.alias_name}: Flag %s has been disabled for event %s",
-        ["flag_enabled"] = "{sender.alias_name}: Flag %s has been enabled for event %s",
-        ["view"] = "{sender.alias_name}: ID %s | %s | %s subs | Flags: %s | %s"
+        ["massping_disabled"] = "{sender.alias_name}: Massping has been disabled for event %s",
+        ["massping_enabled"] = "{sender.alias_name}: Massping has been enabled for event %s",
+        ["view"] = "{sender.alias_name}: ID %s | %s | %s subs | Massping: %s | %s"
     },
     russian = {
         ["no_subcommand"] =
@@ -262,7 +262,7 @@ Here are some basic examples to inspire you:
         end
 
         local events = db_query(
-            'SELECT id, message, array_to_json(flags) as flags FROM events WHERE name = $1 AND event_type = $2',
+            'SELECT id, message, is_massping FROM events WHERE name = $1 AND event_type = $2',
             { data_name, data.type })
 
         if scid == "on" then
@@ -332,26 +332,23 @@ Here are some basic examples to inspire you:
 
             return l10n_custom_formatted_line_request(request, lines, "settarget", { data_original, new_data_original })
         elseif scid == "setmassping" then
-            local flags = json_parse(event.flags)
-
             local line_id = ""
             local query = ""
-            if array_contains_int(flags, 0) then
-                line_id = "flag_disabled"
-                query = "UPDATE events SET flags = array_remove(flags, $1) WHERE id = $2"
+            if event.is_massping == "1" then
+                line_id = "massping_disabled"
+                query = "UPDATE events SET is_massping = 0 WHERE id = $1"
             else
-                line_id = "flag_enabled"
-                query = "UPDATE events SET flags = array_append(flags, $1) WHERE id = $2"
+                line_id = "massping_enabled"
+                query = "UPDATE events SET is_massping = 1 WHERE id = $1"
             end
 
-            db_execute(query, { 0, event.id })
+            db_execute(query, { event.id })
 
-            return l10n_custom_formatted_line_request(request, lines, line_id, { event_flag_to_str(0), data_original })
+            return l10n_custom_formatted_line_request(request, lines, line_id, { data_original })
         elseif scid == "call" then
-            local flags = json_parse(event.flags)
             local names = {}
 
-            if array_contains_int(flags, 0) then
+            if event.is_massping == "1" then
                 local chatters = twitch_get_chatters()
                 for i = 1, #chatters, 1 do
                     table.insert(names, chatters[i].login)
@@ -382,17 +379,13 @@ INNER JOIN events e ON e.id = es.event_id
 WHERE e.id = $1
 ]], { event.id })
 
-            local f = json_parse(event.flags)
-            local flags = {}
-            for i = 1, #f, 1 do
-                table.insert(flags, event_type_to_str(f[i]))
-            end
-            if #flags == 0 then
-                table.insert(flags, '-')
+            local massping_flag = "OFF"
+            if event.is_massping == "1" then
+                massping_flag = "ON"
             end
 
             return l10n_custom_formatted_line_request(request, lines, "view",
-                { event.id, data_original, subscription_count[1].count, table.concat(flags, ', '), event.message })
+                { event.id, data_original, subscription_count[1].count, massping_flag, event.message })
         end
     end
 }
diff --git a/luamods/notify.lua b/luamods/notify.lua
index eb02af2..26540e7 100644
--- a/luamods/notify.lua
+++ b/luamods/notify.lua
@@ -50,7 +50,7 @@ local lines = {
         ["namesake"] = "{sender.alias_name}: You have already subscribed to this event.",
         ["list"] =
         "{sender.alias_name}: You can use '{channel.prefix}event list' to find out which events you can subscribe to.",
-        ["subs"] = "{sender.alias_name}: %s",
+        ["subs"] = "{sender.alias_name}: Your subscriptions: %s",
         ["empty_subs"] = "{sender.alias_name}: You do not have any event subscriptions in this channel.",
         ["sub"] =
         "{sender.alias_name}: You have successfully subscribed to event %s",
@@ -69,7 +69,7 @@ local lines = {
         ["namesake"] = "{sender.alias_name}: Такое же событие уже существует.",
         ["list"] =
         "{sender.alias_name}: Вы можете использовать '{channel.prefix}event list', чтобы узнать на какие события Вы можете подписаться.",
-        ["subs"] = "{sender.alias_name}: %s",
+        ["subs"] = "{sender.alias_name}: Ваши подписки: %s",
         ["empty_subs"] = "{sender.alias_name}: Вы не подписаны на какие-либо события в этом канале.",
         ["sub"] =
         "{sender.alias_name}: Вы успешно подписались на событие %s",
@@ -197,10 +197,10 @@ WHERE e.channel_id = $1 AND es.user_id = $2
         local events = db_query([[
 SELECT e.id, es.id AS sub_id
 FROM events e
-LEFT JOIN event_subscriptions es ON es.event_id = e.id AND es.user_id = $3
-WHERE e.name = $1 AND e.event_type = $2
+LEFT JOIN event_subscriptions es ON es.event_id = e.id AND es.user_id = $1
+WHERE e.name = $2 AND e.event_type = $3
 ]],
-            { data_name, data.type, request.sender.id })
+            { request.sender.id, data_name, data.type })
 
         if #events == 0 then
             return l10n_custom_formatted_line_request(request, lines, "not_found", { data_original })
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