출처 : http://woowabros.github.io/experience/2018/10/12/new_point_story_1.html
위 출처를 보면 우아한형제들에서 포인트 시스템을 구축할 때 생각했던 점들이 나온다.
나도 어느 순간 포인트 시스템을 구현해야 했는데 많은 도움을 받았다.
point.sql
-- 사용자
CREATE TABLE IF NOT EXISTS `users` (
  `Id` binary(16) NOT NULL,
  `Name` varchar(50) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 포인트 이벤트
CREATE TABLE IF NOT EXISTS `point_event` (
  `Id` binary(16) NOT NULL,
  `UserId` binary(16) NOT NULL,
  `Type` char(1) NOT NULL DEFAULT 'A',
  `Point` int(11) NOT NULL DEFAULT 0,
  `Comment` varchar(64) NOT NULL DEFAULT '',
  `ExpireDate` date DEFAULT NULL,
  `CreateDate` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`Id`),
  KEY `FK_point_event_userId_users_id` (`UserId`),
  CONSTRAINT `FK_point_event_userId_users_id` FOREIGN KEY (`UserId`) 
REFERENCES `users` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 포인트 상세
CREATE TABLE IF NOT EXISTS `point_detail` (
  `Id` binary(16) NOT NULL,
  `PointId` binary(16) NOT NULL,
  `GroupId` binary(16) NOT NULL,
  `Type` char(1) NOT NULL DEFAULT 'A',
  `Point` int(11) NOT NULL DEFAULT 0,
  `CreateDate` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`Id`),
  KEY `FK_point_detail_PointId_reward_point_id` (`PointId`),
  CONSTRAINT `FK_point_detail_PointId_reward_point_id` FOREIGN KEY (`PointId`) 
REFERENCES `point_event` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 사용한 포인트를 취소하는 프로시저
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_point_use_cancel`(
    IN iPointId BINARY(16),
    IN iComment VARCHAR(64)
)
    COMMENT '포인트 사용 취소'
BEGIN
    DECLARE mNewPointId BINARY(16);
    DECLARE mType char(1);

    SET mNewPointId = fn_create_key();

    -- U 타입만 취소 가능함
    select `Type` into mType
    FROM point_event
    WHERE Id = iPointId;

    if mType = 'U' then

        INSERT INTO point_event(Id, UserId, `Type`, `Point`, `Comment`)
        select mNewPointId, UserId, 'C', (`Point` * -1), iComment
        from point_event
        where Id = iPointId;

        INSERT INTO point_detail(Id, PointId, `Type`, `Point`, GroupId)
        select fn_create_key(), mNewPointId, 'C', (`Point` * -1), GroupId
        from point_detail
        WHERE PointId = iPointId;

    end if;

END//
DELIMITER ;

-- 만료일이 지난 포인트 정리하는 프로시저
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_point_expire`(
    IN `iWorkDate` DATE
)
    COMMENT '포인트 만료 정리'
BEGIN

    DECLARE mDone INT DEFAULT 0;
    DECLARE mUserId BINARY(16);
    DECLARE mGroupId BINARY(16);
    DECLARE mPoint INT;
    DECLARE mPointId BINARY(16);
    DECLARE mDetailId BINARY(16);

    DECLARE cur CURSOR FOR
        SELECT t.GroupId, t.`Point`
        FROM (
            SELECT d.GroupId, SUM(d.`Point`) AS `Point`
            FROM point_event AS p
                JOIN point_detail AS d ON p.Id = d.PointId
            WHERE p.ExpireDate < iWorkDate OR p.ExpireDate IS NULL
            GROUP BY d.GroupId
        ) AS t
        WHERE `Point` > 0;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET mDone = 1;


    OPEN cur;
    read_loop : LOOP
        FETCH cur INTO mGroupId, mPoint;

        IF mDone THEN
          LEAVE read_loop;
        END IF;

        SELECT p.UserId INTO mUserId
        FROM point_detail AS d
            JOIN point_event AS p ON d.PointId = p.Id
        WHERE d.GroupId = mGroupId
        LIMIT 1;

        SET mPointId = fn_create_key();

        INSERT INTO point_event(Id, UserId, `Type`, `Point`, `Comment`)
        VALUES(mPointId, mUserId, 'E', (mPoint * -1), '유효기간만료');

        INSERT INTO point_detail(Id, PointId, `Type`, `Point`, GroupId)
        VALUES(fn_create_key(), mPointId, 'E', (mPoint * -1), mGroupId);

    END LOOP;
    CLOSE cur;


END//
DELIMITER ;

-- 포인트 적립하는 프로시저
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_point_add`(
    IN iUserId binary(16),
    IN iPoint Int,
    IN iExpireDate Date,
    IN iComment varchar(64)
)
    COMMENT '포인트 적립'
BEGIN

    declare mPointId binary(16);
    DECLARE mDetailId BINARY(16);

    set mPointId = fn_create_key();
    SET mDetailId = fn_create_key();

    insert into point_event(Id, UserId, `Point`, `Comment`, ExpireDate)
    values(mPointId, iUserId, iPoint, iComment, iExpireDate);

    INSERT INTO point_detail(Id, PointId, `Point`, GroupId)
    values(mDetailId, mPointId, iPoint, mDetailId);


END//
DELIMITER ;

-- 포인트 사용하는 프로시저
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_point_use`(
    IN iUserId BINARY(16),
    IN iPoint INT,
    IN iComment VARCHAR(64),
    OUT oResultCode char(1),
    out oPointId BINARY(16)
)
    COMMENT '포인트 사용'
BEGIN

    /*
    * oResultCode : A-승인, F-잔여포인트 부족, E-Exception
    */
    DECLARE mDone INT DEFAULT 0;
    DECLARE mPointId BINARY(16);
    DECLARE mDetailId BINARY(16);
    DECLARE mGroupId BINARY(16);
    Declare mTotalPoint INT;
    declare mCurrentPoint INT;
    DECLARE mPoint INT;

    DECLARE cur CURSOR FOR
        SELECT t.GroupId, t.`Point`
        FROM (
            SELECT d.GroupId, SUM(d.`Point`) AS `Point`
            FROM point_event AS p
                JOIN point_detail AS d ON p.Id = d.PointId
            WHERE p.UserId = iUserId
                -- AND p.ExpireDate >= CURRENT_DATE
            GROUP BY d.GroupId
            ORDER BY p.ExpireDate
        ) AS t
        WHERE `Point` > 0;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET mDone = 1;

    /* 만약 SQL에러라면 ROLLBACK 처리한다. */
    DECLARE exit handler for SQLEXCEPTION
    BEGIN
        SET oResultCode = 'E';
        ROLLBACK;
    END;

    set mCurrentPoint = iPoint;

    START TRANSACTION;


    -- 총 포인트를 구해본다
    SELECT IFNULL(SUM(`point`), 0) INTO mTotalPoint
    FROM users AS u
        left join point_event AS p ON u.Id = p.UserId
    WHERE u.Id = iUserId
    GROUP BY p.UserId;


    if(mTotalPoint < iPoint) then
        set oResultCode = 'F';
    else
        -- point 를 먼저 넣고
        SET mPointId = fn_create_key();
        INSERT INTO point_event(Id, UserId, `Type`, `Point`, `Comment`)
        VALUES(mPointId, iUserId, 'U', (iPoint * -1), iComment);

        OPEN cur;
        read_loop : LOOP
            FETCH cur INTO mGroupId, mPoint;

            IF mDone OR mCurrentPoint < 1 THEN
              LEAVE read_loop;
            END IF;

            SET mDetailId = fn_create_key();

            if mCurrentPoint > mPoint then
                -- 빼야하는게 더 크면 통째로 뺀다
                INSERT INTO point_detail(Id, PointId, `Type`, `Point`, GroupId)
                VALUES(mDetailId, mPointId, 'U', (mPoint * -1), mGroupId);

                set mCurrentPoint = mCurrentPoint - mPoint;
            else
                -- 빼야하는게 같거나 작다면 일부만 뺀다
                INSERT INTO point_detail(Id, PointId, `Type`, `Point`, GroupId)
                VALUES(mDetailId, mPointId, 'U', (mCurrentPoint * -1), mGroupId);

                SET mCurrentPoint = 0;
            end if;

        END LOOP;
        CLOSE cur;

        -- 결과값 반환
        SET oResultCode = 'A';
        set oPointId = mPointId;

    end If;

    COMMIT;

END//
DELIMITER ;

-- binary key 생성
DELIMITER //
CREATE DEFINER=`root`@`%` FUNCTION `fn_create_key`() RETURNS binary(16)
    COMMENT '순차적 BINARY ID 생성'
BEGIN

    SET @UUID = UUID();

    RETURN
        UNHEX(CONCAT(
        SUBSTR(@UUID, 15, 4),
        SUBSTR(@UUID, 10, 4),
        SUBSTR(@UUID,  1, 8),
        SUBSTR(@UUID, 20, 4),
        SUBSTR(@UUID, 25) ));

END//
DELIMITER ;
[MySQL & MariaDB] 포인트 시스템 구현
