CREATE TABLE IF NOT EXISTS membership_id_settings (
  id integer PRIMARY KEY,
  prefix text NOT NULL DEFAULT 'WWN',
  digits integer NOT NULL DEFAULT 7,
  created_at timestamp NOT NULL DEFAULT now(),
  updated_at timestamp NOT NULL DEFAULT now()
);

INSERT INTO membership_id_settings (id) VALUES (1)
ON CONFLICT (id) DO NOTHING;

CREATE TABLE IF NOT EXISTS organization_membership_id_settings (
  id serial PRIMARY KEY,
  organization_id integer NOT NULL UNIQUE REFERENCES organizations(id) ON DELETE CASCADE,
  prefix text NOT NULL DEFAULT 'WWN',
  digits integer NOT NULL DEFAULT 7,
  created_at timestamp NOT NULL DEFAULT now(),
  updated_at timestamp NOT NULL DEFAULT now()
);

ALTER TABLE users
  ADD COLUMN IF NOT EXISTS membership_registration_id text;

CREATE UNIQUE INDEX IF NOT EXISTS users_membership_registration_id_uidx
  ON users (membership_registration_id);
