upgrade-20210712.sql

SET ROLE 'mo_osmo';

CREATE TYPE enroll_mode AS ENUM (	-- režim přihlašování účastníků
	'manual',			-- přihlašuje organizátor
	'register',			-- účastník se registruje
	'confirm'			-- účastník se registruje, ale org ho musí potvrdit
);

ALTER TABLE rounds ADD COLUMN enroll_mode enroll_mode NOT NULL DEFAULT 'manual';
ALTER TABLE rounds ADD COLUMN enroll_advert varchar(255) NOT NULL DEFAULT '';

ALTER TYPE part_state ADD VALUE 'active' AFTER 'invited';

ALTER TABLE participants ADD COLUMN registered_on timestamp with time zone DEFAULT NULL;

UPDATE participations SET state='active' WHERE state IN ('registered', 'invited');

CREATE TYPE reg_req_type AS ENUM (
	'register',
	'change_email',
	'reset_passwd'
);

CREATE TABLE reg_requests (
	reg_id		serial		PRIMARY KEY,
	type		reg_req_type	NOT NULL,
	created_at	timestamp with time zone NOT NULL,
	expires_at	timestamp with time zone NOT NULL,
	used_at		timestamp with time zone DEFAULT NULL,
	email		varchar(255)	DEFAULT NULL,
	captcha_token	varchar(255)	DEFAULT NULL,
	email_token	varchar(255)	UNIQUE NOT NULL,
	user_id		int		DEFAULT NULL REFERENCES users(user_id) ON DELETE CASCADE,
	client		varchar(255)	NOT NULL
);