create table users
(
	id uuid default uuid_generate_v4() not null
		constraint node_users_pkey
			primary key,
	fname varchar(30) not null,
	lname varchar(30) not null,
	username varchar(30) not null
		constraint username_key
			unique,
	email varchar(128) not null
		constraint node_users_email_key
			unique,
	password varchar(128),
	photo varchar,
	last_login timestamp,
	json json default '{   "notificationPreferences": [     "releaseNotes",     "systemOutages",     "dataSharing"   ] }'::json,
	updatedatabaseusers boolean default false,
	remarks varchar(255),
	createdby varchar(100) default "current_user"() not null,
	createddate timestamp default ('now'::text)::timestamp without time zone not null,
	lastmodified timestamp default ('now'::text)::timestamp without time zone not null,
	modifiedby varchar(100) default "current_user"() not null,
	eula json default '{}'::json not null
);

comment on column users.id is 'the unigue ID used for relationships with other tables.';

comment on column users.fname is 'users last name';

comment on column users.lname is 'users last name';

comment on column users.username is 'users user name. Generaly first initial, last name such as vgreen for Vera Green';

comment on column users.email is 'users email address';

comment on column users.password is 'users hashed password where hash is controled from nodeJS';

comment on column users.last_login is 'The last time the user logged in. ';

comment on column users.updatedatabaseusers is 'If true, the next time the user logs in their user is updated on the databases the user requires access to.';

comment on column users.createdby is 'Standard editor tracing field populated by the editor_tracking trigger: the name of the user who created the record.';

comment on column users.createddate is 'Standard editor tracing populated by the editor_tracking trigger: the date the record was created.';

comment on column users.lastmodified is 'Standard editor tracing populated by the editor_tracking trigger: the date the record was last modified.';

comment on column users.modifiedby is 'Standard editor tracing populated by the editor_tracking trigger: the user who last modified the record.';

comment on column users.eula is 'End User License Agreement (EULA) metadata';

alter table users owner to geoadmin;

create trigger editor_tracking
	before update
	on users
	for each row
	execute procedure vgeo_update_editor_tracking();

grant delete, insert, references, select, trigger, truncate, update on users to public;

CREATE ROLE r_planmap_view WITH
  NOLOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION;
  comment on ROLE r_planmap_view is 'a group role which can view and select from all planmap admin tables';
  
  CREATE ROLE r_planmap_edit WITH
  NOLOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION;
  comment ON ROLE r_planmap_edit is 'a group role which can edit all planmap admin tables';
  GRANT r_planmap_view TO r_planmap_edit;
  
  
    CREATE ROLE r_planmap_jen_auth WITH
  NOLOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION;
comment on ROLE r_planmap_jan_authis 'a group role which can authenticate against plan-map using jwt auth node';

CREATE ROLE jan_carbon WITH
  LOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION;

GRANT r_planmap_jan_auth TO jan_carbon;

COMMENT ON ROLE jan_carbon IS 'user is used for jwt-auth-node for the carbon project authentication configuration. Vera knows the PW. DO NOT REMOVE, DO NOT RESET';

ALTER ROLE jan_carbon PASSWORD 'xxxxxx';