-- 010: Owner numbering doctrine (2026-10-28)
--  1) membership_no = the OFFICIAL number: a NATURAL number assigned by the party
--     administration, used on official papers. Stored as BIGINT (>= 1), unique.
--  2) The SYSTEM number is members.id (internal serial used by audit/logs/KPIs) —
--     already in place; displayed read-only in profiles.

-- +up
ALTER TABLE members ALTER COLUMN membership_no TYPE BIGINT USING NULLIF(btrim(membership_no), '')::bigint;
ALTER TABLE members ADD CONSTRAINT ck_members_membership_no CHECK (membership_no IS NULL OR membership_no >= 1);

-- +down
ALTER TABLE members DROP CONSTRAINT IF EXISTS ck_members_membership_no;
ALTER TABLE members ALTER COLUMN membership_no TYPE VARCHAR(32) USING membership_no::text;