\set DIGITS_BIGINT 19
-- 9 decimal digits for timestamp, good till 2051-01-01
\set RND_TS_MUL 1000000000

SELECT _id_template(
  $sql$
    DROP FUNCTION IF EXISTS id_gen_timestampic();

    CREATE OR REPLACE FUNCTION id_gen_timestampic(seq_name regclass = NULL) RETURNS bigint
    LANGUAGE plpgsql
    SET search_path FROM CURRENT
    AS $$
    -- Generates next globally-unique id prefixed with timestamp.
    --   EssssTTTTnnnn...
    -- where decimal positions are:
    -- a) E is environment number (e.g. 1..8)
    -- b) s is microshard number (e.g. 0..9999)
    -- c) T is timestamp in seconds since 2010-01-01 UTC (e.g. 9 decimal digits, +17 years from 2023)
    -- c) n is monotonic sequence ring (e.g. up to 5 decimal digits, so up to 100k inserts/sec)
    DECLARE
      seq regclass;
      plain_id bigint;
      id bigint;
    BEGIN
      seq := COALESCE(seq_name, (quote_ident({current_schema}) || '.id_timestampic_seq')::regclass);
      plain_id := nextval(seq);
      id := {I:current_schema}.id_env_no();
      id := id * {CONST_SHARD_MUL} + {I:current_schema}.id_shard_no();
      id := id * {rnd_ts_mul} + trunc(EXTRACT(EPOCH FROM clock_timestamp())) - {CONST_RND_TS_START};
      id := id * {rnd_seq_mul} + (plain_id % {rnd_seq_mul});
      RETURN id;
    END
    $$;

    COMMENT ON FUNCTION id_gen_timestampic(regclass) IS
      'Generates the next globally-unique id prefixed with timestamp. Uses '
      'the full bigint range. Increasing ids are more friendly to heavy '
      'INSERTs since they maximize the chance for btree index to reuse the '
      'newly created leaf pages.';
  $sql$,
  'CONST_SHARD_MUL', :'CONST_SHARD_MUL',
  'CONST_RND_TS_START', :'CONST_RND_TS_START',
  'rnd_ts_mul', :'RND_TS_MUL',
  'rnd_seq_mul', round(10::numeric ^ :'DIGITS_BIGINT' / :'CONST_ENV_MUL' / :'CONST_SHARD_MUL' / :'RND_TS_MUL')::text,
  'current_schema', current_schema
) \gexec
