/**
 * Returns a Time Sortable ID with millisecond precision.
 * 
 * Time component: 42 bits (2^42 = ~69 years)
 * 
 * Random component: 22 bits (2^22 = 4,194,304)
 * 
 * The time component is the count of milliseconds since 2020-01-01T00:00:00Z.
 *
 * Tags: tsid ulid snowflake id-generator generator time sortable sort order id
 */
create or replace function public.fn_tsid_milli() returns bigint as $$
declare
    -- Milliseconds precision
    C_MILLI_PREC bigint := 10^3;
    -- Random component bit length: 22 bits
    C_RANDOM_LEN bigint := 2^22;
    -- TSID epoch: seconds since 2020-01-01Z
    -- extract(epoch from '2020-01-01'::date)
    C_TSID_EPOCH bigint := 1577836800;
begin
    return ((floor((extract('epoch' from clock_timestamp()) - C_TSID_EPOCH) * C_MILLI_PREC) * C_RANDOM_LEN)::bigint) + (floor(random() * C_RANDOM_LEN)::bigint);
end $$ language plpgsql;

-- EXAMPLE 1:
-- select fn_tsid_milli();

-- EXAMPLE 1 OUTPUT:
-- 52930646021833201

----------------------------------------------------------

/*
---------------------
-- LOOP TEST
---------------------
-- Insert many into a test table.
-- FIXME: 
--       It fails when the same TSID is inserted more than once.
--       It's necessary to figure out a solution, maybe an auxiliar state table with exclusive lock.
--       The function 'fn_tsid_MICRO()' is less prone to this fail since it has microsecond precision.
---------------------
do $$
declare
	i int := 0;
	lim int := 1000000;
begin
	WHILE i < lim LOOP
		insert into public.tb_tsid_test(id) values (public.fn_tsid_milli());
		i := i + 1;
	END LOOP;
	
end $$ language plpgsql;
*/

-- CREATE TEST TABLE
-- create table public.tb_tsid_test (id bigint unique);

-- CHECK RECORDS
-- select * from public.tb_tsid_test;

-- CLEAR RECORDS
-- delete from public.tb_tsid_test;