# Define ULID_DECODE and ULID_ENCODE which convert a ulid string to a binary and vice versa.

delimiter //
DROP FUNCTION IF EXISTS ULID_DECODE//
CREATE FUNCTION ULID_DECODE (s CHAR(26)) RETURNS BINARY(16) DETERMINISTIC
BEGIN
DECLARE s_base32 CHAR(26);
SET s_base32 = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(UPPER(s), 'J', 'I'), 'K', 'J'), 'M', 'K'), 'N', 'L'), 'P', 'M'), 'Q', 'N'), 'R', 'O'), 'S', 'P'), 'T', 'Q'), 'V', 'R'), 'W', 'S'), 'X', 'T'), 'Y', 'U'), 'Z', 'V');
RETURN UNHEX(CONCAT(LPAD(CONV(SUBSTRING(s_base32, 1, 2), 32, 16), 2, '0'), LPAD(CONV(SUBSTRING(s_base32, 3, 12), 32, 16), 15, '0'), LPAD(CONV(SUBSTRING(s_base32, 15, 12), 32, 16), 15, '0')));
END//

DROP FUNCTION IF EXISTS ULID_ENCODE//
CREATE FUNCTION ULID_ENCODE (b BINARY(16)) RETURNS CHAR(26) DETERMINISTIC
BEGIN
DECLARE s_hex CHAR(32);
SET s_hex = LPAD(HEX(b), 32, '0');
RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT(LPAD(CONV(SUBSTRING(s_hex, 1, 2), 16, 32), 2, '0'), LPAD(CONV(SUBSTRING(s_hex, 3, 15), 16, 32), 12, '0'), LPAD(CONV(SUBSTRING(s_hex, 18, 15), 16, 32), 12, '0')), 'V', 'Z'), 'U', 'Y'), 'T', 'X'), 'S', 'W'), 'R', 'V'), 'Q', 'T'), 'P', 'S'), 'O', 'R'), 'N', 'Q'), 'M', 'P'), 'L', 'N'), 'K', 'M'), 'J', 'K'), 'I', 'J');
END//

DROP FUNCTION IF EXISTS ULID_FROM_DATETIME//
CREATE FUNCTION ULID_FROM_DATETIME (t DATETIME) RETURNS CHAR(26) DETERMINISTIC
BEGIN
RETURN ULID_ENCODE(CONCAT(UNHEX(CONV(UNIX_TIMESTAMP(t) * 1000, 10, 16)), binary(10)));
END//

DROP FUNCTION IF EXISTS ULID_TO_DATETIME//
CREATE FUNCTION ULID_TO_DATETIME (s CHAR(26)) RETURNS DATETIME DETERMINISTIC
BEGIN
RETURN FROM_UNIXTIME(CONV(HEX(LEFT(ULID_DECODE(s), 6)), 16, 10) / 1000);
END//
delimiter ;

# Check the hex output should equal to one from other library 
select HEX(ULID_DECODE('01ARZ3NDEKTSV4RRFFQ69G5FAV'));

# Check the all outputs should equal to '1'(true).
select '01ARZ3NDEKTSV4RRFFQ69G5FAV' = ULID_ENCODE(ULID_DECODE('01ARZ3NDEKTSV4RRFFQ69G5FAV'));
select '00000000000000000000000000' = ULID_ENCODE(ULID_DECODE('00000000000000000000000000'));
select '7F000000000000000000000000' = ULID_ENCODE(ULID_DECODE('7F000000000000000000000000'));
select '0V000000000000000000000000' = ULID_ENCODE(ULID_DECODE('0V000000000000000000000000'));
select '000V0000000000000000000000' = ULID_ENCODE(ULID_DECODE('000V0000000000000000000000'));
select '0000V000000000000000000000' = ULID_ENCODE(ULID_DECODE('0000V000000000000000000000'));
select '00000V00000000000000000000' = ULID_ENCODE(ULID_DECODE('00000V00000000000000000000'));
select '000000V0000000000000000000' = ULID_ENCODE(ULID_DECODE('000000V0000000000000000000'));
select '0000000V000000000000000000' = ULID_ENCODE(ULID_DECODE('0000000V000000000000000000'));
select '00000000V00000000000000000' = ULID_ENCODE(ULID_DECODE('00000000V00000000000000000'));
select '000000000V0000000000000000' = ULID_ENCODE(ULID_DECODE('000000000V0000000000000000'));
select '0000000000V000000000000000' = ULID_ENCODE(ULID_DECODE('0000000000V000000000000000'));
select '00000000000V00000000000000' = ULID_ENCODE(ULID_DECODE('00000000000V00000000000000'));
select '000000000000V0000000000000' = ULID_ENCODE(ULID_DECODE('000000000000V0000000000000'));
select '0000000000000V000000000000' = ULID_ENCODE(ULID_DECODE('0000000000000V000000000000'));
select '00000000000000V00000000000' = ULID_ENCODE(ULID_DECODE('00000000000000V00000000000'));
select '000000000000000V0000000000' = ULID_ENCODE(ULID_DECODE('000000000000000V0000000000'));
select '0000000000000000V000000000' = ULID_ENCODE(ULID_DECODE('0000000000000000V000000000'));
select '00000000000000000V00000000' = ULID_ENCODE(ULID_DECODE('00000000000000000V00000000'));
select '000000000000000000V0000000' = ULID_ENCODE(ULID_DECODE('000000000000000000V0000000'));
select '0000000000000000000V000000' = ULID_ENCODE(ULID_DECODE('0000000000000000000V000000'));
select '00000000000000000000V00000' = ULID_ENCODE(ULID_DECODE('00000000000000000000V00000'));
select '000000000000000000000V0000' = ULID_ENCODE(ULID_DECODE('000000000000000000000V0000'));
select '0000000000000000000000V000' = ULID_ENCODE(ULID_DECODE('0000000000000000000000V000'));
select '00000000000000000000000V00' = ULID_ENCODE(ULID_DECODE('00000000000000000000000V00'));
select '000000000000000000000000V0' = ULID_ENCODE(ULID_DECODE('000000000000000000000000V0'));
select '0000000000000000000000000V' = ULID_ENCODE(ULID_DECODE('0000000000000000000000000V'));
select '0123456789JKMNPQRSTVWXYZ01' = ULID_ENCODE(ULID_DECODE('0123456789JKMNPQRSTVWXYZ01'));
select '00123456789JKMNPQRSTVWXYZ0' = ULID_ENCODE(ULID_DECODE('00123456789JKMNPQRSTVWXYZ0'));
select '000000000000JKMNPQRSTVWXYZ' = ULID_ENCODE(ULID_DECODE('000000000000JKMNPQRSTVWXYZ'));
select '00JKMNPQRSTVWXYZ0000000000' = ULID_ENCODE(ULID_DECODE('00JKMNPQRSTVWXYZ0000000000'));
select '01JKMNPQRSTVWXYZ0000000000' = ULID_ENCODE(ULID_DECODE('01JKMNPQRSTVWXYZ0000000000'));
select '00000JKMNPQRSTVWXYZ0000000' = ULID_ENCODE(ULID_DECODE('00000JKMNPQRSTVWXYZ0000000'));
select '000000000JKMNPQRSTVWXYZ000' = ULID_ENCODE(ULID_DECODE('000000000JKMNPQRSTVWXYZ000'));
select '01234567890123456789012345' = ULID_ENCODE(ULID_DECODE('01234567890123456789012345'));
select '0JKMNPQRSTVWXYZJKMNPQRSTVW' = ULID_ENCODE(ULID_DECODE('0JKMNPQRSTVWXYZJKMNPQRSTVW'));
select '0MNPQRSTVWXYZMNPQRSTVWXYZ0' = ULID_ENCODE(ULID_DECODE('0MNPQRSTVWXYZMNPQRSTVWXYZ0'));
select '0ZYXWVTSRQPNMZYXWVTSRQPNM0' = ULID_ENCODE(ULID_DECODE('0ZYXWVTSRQPNMZYXWVTSRQPNM0'));
select '0WVTSRQPNMKJZYXWVTSRQPNMKJ' = ULID_ENCODE(ULID_DECODE('0WVTSRQPNMKJZYXWVTSRQPNMKJ'));
select '0000004JFGTYNCK4CFM2C8EXXS' = ULID_ENCODE(ULID_DECODE('0000004JFGTYNCK4CFM2C8EXXS'));
select '0000004JFGT6RQ0EYX5PEWWJHD' = ULID_ENCODE(ULID_DECODE('0000004JFGT6RQ0EYX5PEWWJHD'));
select '0000004JFGGMVJGZD53ZCSEV76' = ULID_ENCODE(ULID_DECODE('0000004JFGGMVJGZD53ZCSEV76'));
select '0000004JFGGMVJGZD53ZCSEV7B' = ULID_ENCODE(ULID_DECODE('0000004JFGGMVJGZD53ZCSEV7B'));
select '000XA16S41ACTAV9WEVGEMMVR8' = ULID_ENCODE(ULID_DECODE('000XA16S41ACTAV9WEVGEMMVR8'));
                                                              
select '2019-01-01 00:00:00' = ULID_TO_DATETIME(ULID_FROM_DATETIME('2019-01-01 00:00:00'));
select '2020-02-02 02:02:02' = ULID_TO_DATETIME(ULID_FROM_DATETIME('2020-02-02 02:02:02'));