
CREATE SEQUENCE IF NOT EXISTS users_pk_ai; COMMIT;

CREATE TABLE IF NOT EXISTS users (
	pk INTEGER NOT NULL DEFAULT NEXTVAL('users_pk_ai'),
	pk_roles INTEGER DEFAULT NULL,
	usr VARCHAR(128) NOT NULL UNIQUE,
	pwd VARCHAR(256) NOT NULL,
	created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT users_pk PRIMARY KEY (pk),
	CONSTRAINT users_roles_fk FOREIGN KEY (pk_roles) REFERENCES roles(pk) ON UPDATE RESTRICT ON DELETE RESTRICT
);
COMMIT;

ALTER SEQUENCE users_pk_ai OWNED BY users.pk; COMMIT;

CREATE INDEX IF NOT EXISTS users_usrpwd_index ON users (usr,pwd); COMMIT; /* login */

ALTER TABLE users ALTER COLUMN usr TYPE VARCHAR(128); COMMIT;
ALTER TABLE users ALTER COLUMN pwd TYPE VARCHAR(256); COMMIT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS pk_roles INTEGER DEFAULT NULL;

/* https://stackoverflow.com/a/58954108/3036312 */
DO $exec$ BEGIN
	IF NOT EXISTS(SELECT pk FROM users LIMIT 1)
	THEN
		INSERT INTO users (usr,pwd,pk_roles) VALUES('admin','$usr_pwd_default',1) ON CONFLICT DO NOTHING;
	END IF;
END $exec$;

DO $exec$ BEGIN
	IF NOT EXISTS(SELECT conname FROM pg_constraint WHERE conname='users_roles_fk')
	THEN
		UPDATE users SET pk_roles=1 WHERE pk_roles IS NULL AND usr='admin';

		ALTER TABLE users ADD CONSTRAINT users_roles_fk
		FOREIGN KEY (pk_roles) REFERENCES roles(pk)
		ON DELETE RESTRICT ON UPDATE RESTRICT;
	END IF;
END $exec$;
