feat: sql triggers

1 files changed, 75 insertions, 1 deletions

diff --git a/database.sql b/database.sql
index 3f97ea3..035282b 100644
--- a/database.sql
+++ b/database.sql
@@ -114,4 +114,78 @@ CREATE TABLE IF NOT EXISTS mod_actions(
     verdict INTEGER NOT NULL,
     comment TEXT,
     created_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP
-);
\ No newline at end of file
+);
+
+---------------------------
+--       INSERTIONS      --
+---------------------------
+
+-- CREATING A ROLE FOR USERS
+INSERT IGNORE INTO roles(id, name) VALUES (1, 'User');
+
+---------------------------
+--       TRIGGERS        --
+---------------------------
+
+-- CREATE EMOTESET AND ASSIGN ROLE FOR NEW USER
+DELIMITER $$
+CREATE TRIGGER IF NOT EXISTS create_user
+AFTER INSERT ON users
+FOR EACH ROW
+BEGIN
+    INSERT INTO role_assigns(user_id, role_id) VALUES (NEW.id, 1);
+    INSERT INTO emote_sets(owner_id, name) VALUES (NEW.id, CONCAT(NEW.username, '''s emoteset'));
+END$$
+DELIMITER ;
+
+-- NULLIFY EMOTE AUTHORS ON USER DELETION
+DELIMITER $$
+CREATE TRIGGER IF NOT EXISTS user_deletion
+BEFORE DELETE ON users
+FOR EACH ROW
+BEGIN
+    UPDATE emotes SET uploaded_by = NULL WHERE uploaded_by = OLD.id;
+    UPDATE emote_set_contents SET added_by = NULL WHERE added_by = OLD.id;
+    UPDATE reports SET resolved_by = NULL WHERE resolved_by = OLD.id;
+END$$
+DELIMITER ;
+
+-- ONLY ONE EMOTESET CAN BE GLOBAL AND FEATURED
+DELIMITER $$
+CREATE TRIGGER IF NOT EXISTS check_global_and_featured_emote_sets
+BEFORE INSERT ON emote_sets
+FOR EACH ROW
+BEGIN
+    IF NEW.is_global = TRUE THEN
+        IF (SELECT COUNT(*) FROM emote_sets WHERE is_global = TRUE) > 0 THEN
+            SIGNAL SQLSTATE '45000'
+            SET MESSAGE_TEXT = 'Only one emote_set can have is_global = TRUE.';
+        END IF;
+    END IF;
+    IF NEW.is_featured = TRUE THEN
+        IF (SELECT COUNT(*) FROM emote_sets WHERE is_featured = TRUE) > 0 THEN
+            SIGNAL SQLSTATE '45000'
+            SET MESSAGE_TEXT = 'Only one emote_set can have is_featured = TRUE.';
+        END IF;
+    END IF;
+END$$
+DELIMITER ;
+
+-- ASSIGN EMOTESET ON CREATION
+DELIMITER $$
+CREATE TRIGGER IF NOT EXISTS acquire_emote_set
+AFTER INSERT ON emote_sets
+FOR EACH ROW
+BEGIN
+    INSERT INTO acquired_emote_sets(user_id, emote_set_id, is_default)
+    VALUES (
+        NEW.owner_id,
+        NEW.id,
+        IF (
+            (SELECT COUNT(*) FROM emote_sets WHERE owner_id = NEW.owner_id) = 1,
+            TRUE,
+            FALSE
+        )
+    );
+END$$
+DELIMITER ;
\ No newline at end of file