57gMdzND

1CREATE TABLE IF NOT EXISTS mole_pool
2(
3    id         INT AUTO_INCREMENT PRIMARY KEY,
4    uuid       CHAR(36)                           NOT NULL,
5    name       VARCHAR(255)                       NOT NULL,
6    created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
7    CONSTRAINT mole_pool_id_uindex
8    UNIQUE (id),
9    CONSTRAINT mole_pool_name_uindex
10    UNIQUE (name),
11    CONSTRAINT mole_pool_uuid_uindex
12    UNIQUE (uuid)
13);
14
15create table if not exists leaderboard
16(
17    mole_id    INT PRIMARY KEY                    NOT NULL,
18    depth      INT                                NOT NULL,
19    created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
20    updated_at DATETIME                           NULL ,
21    CONSTRAINT leaderboard_mole_id_uindex
22    UNIQUE (mole_id),
23    CONSTRAINT leaderboard__mole_fk
24    foreign key (mole_id) references mole_pool (id)
25    on update cascade on delete cascade
26);
27
28DELIMITER //;
29
30DROP PROCEDURE IF EXISTS InsertLeaderboardItem;
31
32CREATE PROCEDURE InsertLeaderboardItem(IN moleId INT, IN newDepth INT)
33BEGIN
34    DECLARE rowCount INT;
35    DECLARE currentDepth INT;
36
37    SELECT depth, COUNT(*)
38    INTO currentDepth, rowCount
39    FROM leaderboard
40    WHERE mole_id = moleId
41    GROUP BY depth;
42
43    IF rowCount > 0 THEN
44        IF newDepth > currentDepth THEN
45            UPDATE leaderboard
46            SET depth = newDepth, updated_at = NOW()
47            WHERE mole_id = moleId;
48        END IF;
49    ELSE
50        INSERT INTO leaderboard(mole_id, depth)
51        VALUES (moleId, newDepth);
52    END IF;
53END //;