Rozet sistemi, kullanıcının puanına göre otomatik rozetlerini tanımlayan ve bildirim gönderen sistem

rozet.sql

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;


CREATE TABLE `badges` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `point` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `type` int(11) NOT NULL,
  `content` json NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(255) NOT NULL,
  `point` int(11) NOT NULL DEFAULT '0',
  `notifications` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
DELIMITER $$
CREATE TRIGGER `update_user_points` BEFORE UPDATE ON `users` FOR EACH ROW BEGIN
  DECLARE earned_badge_ids TEXT;
  DECLARE badges_message TEXT;
  DECLARE new_notification_id INT;
  DECLARE badge_count INT;

  IF NEW.point <> OLD.point THEN
    -- Yeni kazanılan rozetlerin IDsini bul
    SELECT GROUP_CONCAT(badges.id)
    INTO earned_badge_ids
    FROM badges
    WHERE badges.point <= NEW.point
      AND badges.id NOT IN (SELECT user_badges.badge_id FROM user_badges WHERE user_badges.user_id = NEW.id);

    IF earned_badge_ids IS NOT NULL THEN
      -- Yeni rozetleri user_badges tablosuna ekle
      INSERT INTO user_badges (user_id, badge_id)
      SELECT NEW.id, badges.id
      FROM badges
      WHERE FIND_IN_SET(badges.id, earned_badge_ids);

      -- Rozet sayısını al
      SELECT COUNT(*)
      INTO badge_count
      FROM badges
      WHERE FIND_IN_SET(id, earned_badge_ids);

      -- Bildirim mesajını oluştur
      IF badge_count = 1 THEN
        SET badges_message = (
          SELECT CONCAT(name, ' rozetini kazandınız')
          FROM badges
          WHERE FIND_IN_SET(id, earned_badge_ids)
        );
      ELSE
        SET badges_message = (
          SELECT CONCAT(
            SUBSTRING_INDEX(GROUP_CONCAT(name ORDER BY id ASC SEPARATOR ', '), ',', badge_count - 1),
            ' ve ',
            SUBSTRING_INDEX(GROUP_CONCAT(name ORDER BY id DESC SEPARATOR ', '), ',', 1),
            ' rozetlerini kazandınız'
          )
          FROM badges
          WHERE FIND_IN_SET(id, earned_badge_ids)
        );
      END IF;

      -- Yeni bildirimi ekle
      INSERT INTO notifications (user_id, type, content)
      VALUES (
        NEW.id,
        1,
        JSON_OBJECT('message', badges_message, 'badge_ids', JSON_ARRAY(earned_badge_ids))
      );

      -- Bildirim ID'sini al
      SET new_notification_id = LAST_INSERT_ID();

      -- Kullanıcı bildirim sayısını güncelle
      SET NEW.notifications = NEW.notifications + 1;
    END IF;
  END IF;
END
$$
DELIMITER ;

CREATE TABLE `user_badges` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `badge_id` int(11) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


ALTER TABLE `badges`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `user_badges`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `badges`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `notifications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `user_badges`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;