-- 012: Standardized 14-digit TECHNICAL identifier (owner release order 2026-10-29)
-- system_no = '20' + YY + 10-digit zero-padded internal id  (e.g. 20260000000001).
-- Distinct by design from the OFFICIAL membership_no (natural number, admin-assigned).
-- Backfilled for existing members; generated transactionally for new ones.

-- +up
ALTER TABLE members ADD COLUMN system_no CHAR(14) NULL;
UPDATE members
   SET system_no = '20' || to_char(created_at, 'YY') || LPAD(id::text, 10, '0')
 WHERE system_no IS NULL;
ALTER TABLE members ALTER COLUMN system_no SET NOT NULL;
CREATE UNIQUE INDEX uq_members_system_no ON members(system_no);
CREATE INDEX idx_members_system_no ON members(system_no);

-- +down
DROP INDEX IF EXISTS idx_members_system_no;
DROP INDEX IF EXISTS uq_members_system_no;
ALTER TABLE members DROP COLUMN IF EXISTS system_no;