-- Paper reference numbers from a sequence instead of a row count.
--
-- They were generated as `papers.count() + 1`, formatted SS-<year>-<00000>.
-- Two things follow from that, and `reference_no` carries a UNIQUE constraint:
--
--   * Remove a paper from the middle of the range and the next submission
--     computes a number that already exists — the insert fails on the
--     constraint and the author cannot submit at all.
--   * Remove one from the end and the next submission silently reuses its
--     number. A reference is what an author quotes in correspondence and, since
--     the payment work, what they type into the UPI transaction note; two
--     papers sharing one makes a payment impossible to match.
--
-- It was also racy: two submissions landing together read the same count and
-- raced for the same reference, one losing to the constraint.
--
-- A sequence has none of those properties. It never goes backwards, never
-- reissues, and nextval is atomic across concurrent transactions.
CREATE SEQUENCE IF NOT EXISTS paper_reference_seq AS BIGINT START WITH 1;

-- Continue from the highest number already issued rather than from 1, so
-- existing references keep their meaning and none is ever handed out twice.
-- Only the trailing digits are read, and rows that do not match the pattern are
-- ignored rather than crashing the migration.
--
-- is_called = false means nextval returns exactly this value, so an empty table
-- starts at 1 rather than skipping it.
SELECT setval(
    'paper_reference_seq',
    COALESCE((SELECT MAX(substring(reference_no FROM '[0-9]+$')::bigint)
              FROM papers
              WHERE reference_no ~ '[0-9]+$'), 0) + 1,
    false
);