From 48a2255bfc45ffcfb0b39ffefdd57cbacf8b36df Mon Sep 17 00:00:00 2001
From: dujinkim <dujin.kim@dtsolution.co.kr>
Date: Fri, 18 Jul 2025 07:52:02 +0000
Subject: (대표님) 파일관리변경, 클라IP추적, 실시간알림, 미들웨어변경, 알림API
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 db/notification.sql | 57 +++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 57 insertions(+)
 create mode 100644 db/notification.sql

(limited to 'db/notification.sql')

diff --git a/db/notification.sql b/db/notification.sql
new file mode 100644
index 00000000..1a6ae269
--- /dev/null
+++ b/db/notification.sql
@@ -0,0 +1,57 @@
+-- migrations/001_notification_triggers.sql
+
+-- 1. 알림 생성 시 실시간 NOTIFY 함수
+CREATE OR REPLACE FUNCTION notify_new_notification()
+RETURNS TRIGGER AS $$
+BEGIN
+  -- 새 알림이 생성될 때 NOTIFY 전송
+  PERFORM pg_notify(
+    'new_notification',
+    json_build_object(
+      'id', NEW.id,
+      'user_id', NEW.user_id,
+      'title', NEW.title,
+      'message', NEW.message,
+      'type', NEW.type,
+      'related_record_id', NEW.related_record_id,
+      'related_record_type', NEW.related_record_type,
+      'is_read', NEW.is_read,
+      'created_at', NEW.created_at
+    )::text
+  );
+  
+  RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+-- 2. 알림 읽음 상태 변경 시 NOTIFY 함수
+CREATE OR REPLACE FUNCTION notify_notification_read()
+RETURNS TRIGGER AS $$
+BEGIN
+  -- 읽음 상태가 변경될 때 NOTIFY 전송
+  IF OLD.is_read != NEW.is_read THEN
+    PERFORM pg_notify(
+      'notification_read',
+      json_build_object(
+        'id', NEW.id,
+        'user_id', NEW.user_id,
+        'is_read', NEW.is_read,
+        'read_at', NEW.read_at
+      )::text
+    );
+  END IF;
+  
+  RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+-- 트리거 생성 (알림 테이블에만)
+CREATE OR REPLACE TRIGGER trigger_notify_new_notification
+  AFTER INSERT ON notifications
+  FOR EACH ROW
+  EXECUTE FUNCTION notify_new_notification();
+
+CREATE OR REPLACE TRIGGER trigger_notify_notification_read
+  AFTER UPDATE ON notifications
+  FOR EACH ROW
+  EXECUTE FUNCTION notify_notification_read();
\ No newline at end of file
-- 
cgit v1.2.3