-- 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();