DROP SCHEMA IF EXISTS kewilayahan CASCADE;
DROP SCHEMA IF EXISTS kkn CASCADE;
DROP SCHEMA IF EXISTS tematik CASCADE;
--DROP SCHEMA IF EXISTS master_data;
DROP SCHEMA IF EXISTS pivot_master_data;

DROP FUNCTION IF EXISTS public.uuid_generate_v7() CASCADE;
CREATE FUNCTION public.uuid_generate_v7() RETURNS uuid
LANGUAGE plpgsql
AS $$
	declare
		unix_ts_ms bytea;
		uuid_bytes bytea;
		begin
		unix_ts_ms = substring(int8send((extract(epoch from clock_timestamp()) * 1000)::bigint) from 3);
		
		-- use random v4 uuid as starting point (which has the same variant we need)
		uuid_bytes = uuid_send(gen_random_uuid());
		
		-- overlay timestamp
		uuid_bytes = overlay(uuid_bytes placing unix_ts_ms from 1 for 6);
		
		-- set version 7 by flipping the 2 and 1 bit in the version 4 string
		uuid_bytes = set_bit(set_bit(uuid_bytes, 53, 1), 52, 1);
		
		return encode(uuid_bytes, 'hex')::uuid;
	end
$$;